Bagaimana untuk mereka bentuk jadual MySQL berprestasi tinggi

Reka bentuk logik dan reka bentuk fizikal yang baik adalah asas prestasi tinggi Skema harus direka bentuk mengikut pernyataan pertanyaan yang akan dilaksanakan oleh sistem, yang selalunya memerlukan penimbangan pelbagai faktor.

1. Pilih jenis data yang dioptimumkan

MySQL menyokong banyak jenis data Memilih jenis data yang betul adalah penting untuk mencapai prestasi tinggi.

Lebih kecil selalunya lebih baik

Jenis data yang lebih kecil biasanya lebih pantas kerana ia menduduki lebih sedikit cakera, memori dan cache CPU serta memerlukan lebih sedikit kitaran CPU untuk diproses.

Sederhana sahaja

Operasi pada jenis data ringkas biasanya memerlukan lebih sedikit kitaran CPU. Contohnya, operasi integer adalah lebih murah daripada operasi aksara kerana set aksara dan peraturan penyusunan (collation) menjadikan perbandingan aksara lebih kompleks daripada perbandingan integer.

Cuba elakkan NULL

Jika pertanyaan mengandungi lajur NULL, MySQL lebih sukar untuk dioptimumkan kerana lajur NULL menjadikan indeks, statistik indeks dan perbandingan nilai lebih kompleks. Lajur yang boleh NULL menggunakan lebih banyak ruang storan dan memerlukan pengendalian khas dalam MySQL. Apabila lajur NULLable diindeks, setiap rekod indeks memerlukan bait tambahan, yang dalam MyISAM malah boleh menyebabkan indeks saiz tetap (seperti indeks dengan hanya satu lajur integer) menjadi indeks saiz berubah-ubah.

Sudah tentu terdapat pengecualian Sebagai contoh, InnoDB menggunakan bit yang berasingan untuk menyimpan nilai NULL, jadi ia mempunyai kecekapan ruang yang baik untuk data yang jarang.

1. Jenis integer

Terdapat dua jenis nombor: nombor bulat dan nombor nyata. Jika anda menyimpan integer, anda boleh menggunakan jenis integer ini: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. Gunakan ruang storan 8, 16, 24, 32 dan 64-bit masing-masing.

Jenis integer mempunyai atribut **UNSIGNED** pilihan, yang bermaksud nilai negatif tidak dibenarkan, yang secara kasarnya menggandakan had atas nombor positif. Contohnya, TINYINT.UNSIGNED boleh menyimpan julat 0 - 255, manakala julat storan TINYINT ialah -128 -127.

Jenis yang ditandatangani dan yang tidak ditandatangani menggunakan ruang storan yang sama dan mempunyai prestasi yang sama, jadi anda boleh memilih jenis yang sesuai mengikut situasi sebenar.

Pilihan anda menentukan cara MySQL menyimpan data dalam memori dan cakera. Walau bagaimanapun, pengiraan integer biasanya menggunakan integer BIGINT 64-bit, walaupun dalam persekitaran 32-bit. (Pengecualian ialah beberapa fungsi agregat, yang menggunakan PERPULUHAN atau GANDA untuk pengiraan).

MySQL boleh menentukan lebar untuk jenis integer, seperti INT(11), yang tidak bermakna untuk kebanyakan aplikasi: ia tidak mengehadkan julat nilai yang sah, tetapi hanya menentukan beberapa alat interaktif MySQL (seperti pelanggan baris arahan MySQL ) Digunakan untuk memaparkan bilangan aksara. Untuk tujuan penyimpanan dan pengiraan, INT(1) dan INT(20) adalah sama.

2.Jenis nombor sebenar

Nombor sebenar ialah nombor dengan bahagian perpuluhan. Walau bagaimanapun, ia bukan sahaja untuk menyimpan bahagian perpuluhan, DECIMAL juga boleh digunakan untuk menyimpan integer yang lebih besar daripada BIGINT.

Jenis FLOAT dan DOUBLE menyokong pengiraan anggaran menggunakan operasi titik terapung standard.

Jenis PERPULUHAN digunakan untuk menyimpan perpuluhan yang tepat.

Kedua-dua jenis titik terapung dan DECIMAL boleh menentukan ketepatan. Untuk lajur DECIMAL, anda boleh menentukan bilangan maksimum digit yang dibenarkan sebelum dan selepas titik perpuluhan. Ini menjejaskan penggunaan ruang lajur.

Terdapat pelbagai cara untuk menentukan ketepatan yang diperlukan untuk lajur titik terapung, yang akan menyebabkan MySQL memilih jenis data yang berbeza, atau untuk membulatkan nilai semasa menyimpan. Takrifan ketepatan ini bukan standard, jadi kami mengesyorkan untuk menentukan jenis data sahaja dan bukan ketepatannya.

Jenis titik terapung biasanya menggunakan ruang yang kurang daripada PERPULUHAN apabila menyimpan nilai dalam julat yang sama. FLOAT menggunakan 4 bait storan. DOUBLE menduduki 8 bait dan mempunyai ketepatan yang lebih tinggi dan julat yang lebih besar daripada FLOAT. Seperti jenis integer, semua yang anda boleh pilih ialah jenis storan menggunakan DOUBLE sebagai jenis pengiraan titik terapung dalaman.

Oleh kerana ruang tambahan dan overhed pengiraan diperlukan, anda harus cuba menggunakan PERPULUHAN sahaja apabila melakukan pengiraan tepat pada perpuluhan. Tetapi apabila data agak besar, anda boleh mempertimbangkan untuk menggunakan BIGINT dan bukannya PERPULUHAN Hanya darabkan unit mata wang untuk disimpan dengan gandaan yang sepadan mengikut bilangan tempat perpuluhan.

3. Jenis rentetan

VARCHAR

• Digunakan untuk menyimpan rentetan berubah-ubah, panjangnya disokong hingga 65535
• Memerlukan 1 atau 2 bait tambahan untuk merekodkan panjang rentetan
• Sesuai untuk: Panjang maksimum rentetan adalah lebih besar daripada panjang purata yang jarang berlaku

CHAR

• Panjang tetap, julat panjang ialah 1~255
• Sesuai untuk: menyimpan rentetan yang sangat pendek, atau semua nilai yang hampir sama panjangnya

Pemurah itu tidak bijak

Ruang atas untuk menyimpan 'hello' menggunakan VARCHAR(5) dan VARCHAR(200) adalah sama. Jadi adakah terdapat sebarang kelebihan untuk menggunakan lajur yang lebih pendek?

Ternyata mempunyai kelebihan yang besar. Lajur yang lebih panjang menggunakan lebih banyak memori kerana MySQL biasanya memperuntukkan blok memori bersaiz tetap untuk menyimpan nilai dalaman. Ini amat teruk apabila menggunakan jadual sementara dalam ingatan untuk pengisihan atau operasi. Ia sama buruk apabila mengisih menggunakan jadual sementara cakera.

Jadi strategi terbaik ialah memperuntukkan hanya ruang yang anda perlukan sahaja.

4.Jenis BLOB dan TEKS

BLOB dan TEXT ialah kedua-dua jenis data rentetan yang direka untuk menyimpan data yang besar, dan masing-masing disimpan dalam mod binari dan aksara.

Tidak seperti jenis lain, MySQL menganggap setiap nilai BLOB dan TEKS sebagai objek bebas. Enjin storan biasanya melakukan pemprosesan khas semasa menyimpan. Apabila nilai BLOB dan TEXT terlalu besar, InnoDB akan menggunakan kawasan storan "luaran" khusus untuk storan Pada masa ini, setiap nilai memerlukan 1 - 4 bait untuk disimpan dalam baris .

Satu-satunya perbezaan antara BLOB dan TEXT ialah jenis BLOB menyimpan data binari dan tidak mempunyai himpunan atau set aksara, manakala jenis TEXT mempunyai set aksara dan pengumpulan

5.Tarikh dan masa jenis

Selalunya tiada alternatif kepada jenis tersebut, jadi tidak timbul persoalan apakah pilihan terbaik. Satu-satunya masalah ialah apa yang perlu dilakukan apabila menyimpan tarikh dan masa. MySQL menyediakan dua jenis tarikh yang serupa: DATE TIME dan TIMESTAMP.

Tetapi pada masa ini kami lebih suka kaedah menyimpan cap waktu, jadi DATE TIME dan TIMESTAMP tidak akan dijelaskan di sini.

6.Lain-lain jenis

6.1 Pilih Pengecam

Jenis data terkecil harus dipilih atas premis bahawa ia boleh memenuhi keperluan julat nilai dan memberi ruang untuk pertumbuhan masa hadapan.

• Jenis integer

Integer biasanya merupakan pilihan terbaik untuk lajur identiti kerana ia pantas dan boleh menggunakan AUTO_INCREMENT.

• Jenis ENUM dan SET

Jenis EMUM dan SET biasanya merupakan pilihan yang tidak baik untuk lajur identiti, walaupun ia mungkin baik untuk sesetengah "jadual definisi" statik yang hanya mengandungi keadaan atau jenis tetap. Lajur ENUM dan SET sesuai untuk menyimpan maklumat tetap, seperti status pesanan, jenis produk dan jantina seseorang.

• Jenis rentetan

Jika boleh, jenis rentetan harus dielakkan sebagai lajur identiti, kerana ia memakan ruang dan biasanya lebih perlahan daripada jenis angka.

Anda juga perlu memberi lebih perhatian kepada rentetan "rawak" sepenuhnya, seperti rentetan yang dijana oleh MDS(), SHAl() atau UUID(). Nilai baharu yang dijana oleh fungsi ini diedarkan secara sewenang-wenangnya pada ruang yang besar, yang boleh menyebabkan INSERT dan beberapa pernyataan SELECT menjadi sangat perlahan. Jika nilai UUID disimpan, tanda "-" harus dialih keluar.

6.2 Data jenis khas

Sesetengah jenis telaga data tidak sepadan secara langsung dengan jenis terbina dalam. Cap masa dengan ketepatan kilosaat yang rendah ialah satu contoh contoh lain ialah alamat 1Pv4 Orang sering menggunakan lajur VARCHAR(15) untuk menyimpan alamat IP, ia sebenarnya 32-bit integer, bukan rentetan. Perwakilan alamat dibahagikan kepada empat segmen menggunakan titik perpuluhan hanyalah untuk memudahkan orang ramai membaca. Jadi alamat IP harus disimpan sebagai integer tidak bertanda. MySQL menyediakan fungsi INET_ATON() dan INET_NTOA() untuk menukar antara dua kaedah perwakilan ini.

2. Reka bentuk struktur meja

1. Paradigma dan anti-paradigma

Biasanya terdapat banyak cara untuk mewakili mana-mana data yang diberikan, daripada dinormalisasi sepenuhnya kepada dinyahnormalkan sepenuhnya dan kompromi antara kedua-duanya. Dalam pangkalan data yang dinormalkan, setiap fakta muncul tepat sekali. Sebaliknya, dalam pangkalan data yang tidak normal, maklumat adalah berlebihan dan mungkin disimpan di beberapa tempat.

Kebaikan dan keburukan paradigma

Apabila mempertimbangkan peningkatan prestasi, selalunya disyorkan untuk menormalkan skema, terutamanya dalam senario intensif tulis.

• Operasi kemas kini yang dinormalkan biasanya lebih cepat daripada yang dinyahnormalkan.
• Apabila data dinormalisasi dengan baik, terdapat sedikit atau tiada data pendua, jadi kurang data yang perlu diubah suai.
• Jadual biasa biasanya lebih kecil dan lebih sesuai dalam ingatan, jadi operasi akan dilakukan dengan lebih cepat.
• Kurang data berlebihan bermakna lebih sedikit penyataan DISTINCT atau GROUP BY diperlukan semasa mendapatkan data senarai.

Kebaikan dan keburukan anti-paradigma

Tiada keperluan untuk jadual yang berkaitan, jadi senario kes terburuk untuk kebanyakan pertanyaan—walaupun jadual tidak menggunakan indeks—adalah imbasan jadual penuh. Ini boleh menjadi lebih pantas daripada bersekutu apabila data lebih besar daripada memori kerana I/0 rawak dielakkan.

Jadual individu juga boleh menggunakan strategi pengindeksan yang lebih cekap.

Mencampurkan normalisasi dan denormalisasi

Dalam aplikasi praktikal, ia selalunya perlu dicampur, dan sebahagian skema yang dinormalkan, jadual cache dan teknik lain boleh digunakan.

Tambahkan medan berlebihan pada jadual dengan sewajarnya, seperti keutamaan prestasi, tetapi ia akan meningkatkan kerumitan. Pertanyaan menyertai jadual boleh dielakkan.

Mudah dan biasa dengan paradigma pangkalan data

<br> Bentuk normal pertama (1NF): Nilai medan adalah atom dan tidak boleh dibahagikan (semua sistem pangkalan data hubungan memenuhi bentuk normal pertama);<br> Contohnya: medan nama, di mana nama keluarga dan nama pertama adalah keseluruhan Jika nama keluarga dan nama pertama dibezakan, dua medan bebas mesti disediakan;

.

Borang Normal Kedua (2NF): Jadual mesti mempunyai kunci utama, iaitu setiap baris data boleh dibezakan secara unik;
Nota: Bentuk normal pertama mesti dipenuhi dahulu;

Borang Normal Ketiga (3NF): Jadual tidak boleh mengandungi maklumat tentang medan bukan kunci dalam jadual lain yang berkaitan, iaitu jadual data tidak boleh mempunyai medan berlebihan;
Nota: Bentuk normal kedua mesti dipenuhi dahulu;

2. Medan jadual kurang halus

• I/O cekap
• Ladang diasingkan dan diselenggara dengan mudah
• Jadual tunggal 1G volum 500W penilaian baris
• Satu baris tidak boleh melebihi 200Byte
• Tidak lebih daripada 50 medan INT dalam satu jadual
• Tidak lebih daripada 20 CHAR(10) medan dalam satu jadual
• Adalah disyorkan bahawa bilangan medan dalam satu jadual dikawal dalam masa 20
• Split TEXT/BLOB, prestasi pemprosesan jenis TEXT jauh lebih rendah daripada VARCHAR, memaksa penjanaan jadual sementara cakera keras membazir lebih banyak ruang.

Atas ialah kandungan terperinci Bagaimana untuk mereka bentuk jadual MySQL berprestasi tinggi. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!