Indeks Pangkalan Data Mysql Dijelaskan untuk Pemula

Konsep Teras

• Indeks Kunci Utama / Indeks Sekunder
• Indeks Berkelompok / Indeks Tidak Berkelompok
• Cari Jadual / Meliputi Indeks
• Tekan Turun Indeks
• Indeks Komposit / Padanan Awalan Paling Kiri
• Indeks Awalan
• Terangkan

1. [Definisi Indeks]

1. Definisi Indeks

Selain data itu sendiri, sistem pangkalan data juga mengekalkan struktur data yang memenuhi algoritma carian tertentu. Struktur ini merujuk (menunjuk kepada) data dengan cara tertentu, membolehkan algoritma carian lanjutan dilaksanakan padanya. Struktur data ini ialah indeks.

2. Struktur Data Indeks

• B-tree / B+ tree (enjin InnoDB MySQL menggunakan pepohon B+ sebagai struktur indeks lalai)
• Jadual HASH
• Susun atur diisih

3. Kenapa Pilih Pokok B+ Daripada Pokok B

• Struktur B-tree: Rekod disimpan dalam nod pokok.

• Struktur pokok B+: Rekod disimpan hanya dalam nod daun pokok.

• Dengan mengandaikan saiz data 1KB dan saiz indeks 16B, dengan pangkalan data menggunakan halaman data cakera dan saiz halaman cakera lalai 16K, tiga operasi I/O yang sama akan menghasilkan:

1. B-tree boleh mengambil 16*16*16=4096 rekod.

2. Pokok B+ boleh memperoleh 1000*1000*1000=1 bilion rekod.

2. [Jenis Indeks]

1. Indeks Kunci Utama dan Indeks Sekunder

• Indeks Kunci Utama: Nod daun indeks ialah baris data.
• Indeks Kedua: Nod daun indeks ialah medan KEY ditambah indeks kunci primer. Oleh itu, apabila membuat pertanyaan melalui indeks sekunder, ia mula-mula mencari nilai kunci primer, dan kemudian InnoDB mencari blok data yang sepadan melalui indeks kunci primer.
• Dalam InnoDB, fail indeks utama menyimpan terus baris data, dipanggil indeks berkelompok, manakala indeks sekunder menghala ke rujukan kunci utama.
• Dalam MyISAM, kedua-dua indeks primer dan sekunder menghala ke baris fizikal (kedudukan cakera).

2. Indeks Berkelompok dan Indeks Tidak Berkelompok

• Indeks berkelompok menyusun semula data sebenar pada cakera untuk diisih mengikut satu atau lebih nilai lajur yang ditentukan. Cirinya ialah susunan penyimpanan data dan susunan indeks adalah konsisten. Secara umumnya, kunci utama akan lalai untuk mencipta indeks berkelompok, dan jadual hanya membenarkan satu indeks berkelompok (sebab: data hanya boleh disimpan dalam satu susunan). Seperti yang ditunjukkan dalam imej, indeks primer dan sekunder InnoDB ialah indeks berkelompok.
• Berbanding dengan nod daun bagi indeks berkelompok sebagai rekod data, nod daun bagi indeks bukan berkelompok ialah penunjuk kepada rekod data. Perbezaan terbesar ialah susunan rekod data tidak sepadan dengan susunan indeks.

3. Kelebihan dan Kekurangan Indeks Berkelompok

• Kelebihan: Apabila menanyakan entri mengikut kunci primer, ia tidak perlu melakukan carian jadual (data berada di bawah nod kunci primer).
• Kelemahan: Pemisahan halaman yang kerap boleh berlaku dengan sisipan data yang tidak teratur.

3. [Konsep Indeks Lanjutan]

1. Carian Jadual

Konsep carian jadual melibatkan perbezaan antara indeks kunci utama dan pertanyaan indeks bukan kunci utama.

• Jika pertanyaan dipilih * daripada T dengan ID=500, pertanyaan kunci utama hanya perlu mencari pepohon ID.
• Jika pertanyaan dipilih * daripada T di mana k=5, pertanyaan indeks bukan kunci utama perlu pertama mencari pepohon indeks k untuk mendapatkan nilai ID 500, kemudian cari pepohon indeks ID sekali lagi.
• Proses peralihan daripada indeks bukan kunci utama kembali ke indeks kunci utama dipanggil carian jadual.

Pertanyaan berdasarkan indeks bukan kunci utama memerlukan pengimbasan pepohon indeks tambahan. Oleh itu, kita harus cuba menggunakan pertanyaan kunci utama dalam aplikasi. Dari perspektif ruang storan, memandangkan nod daun pokok indeks kunci bukan utama menyimpan nilai kunci utama, adalah dinasihatkan untuk mengekalkan medan kunci utama sesingkat mungkin. Dengan cara ini, nod daun pokok indeks bukan kunci utama adalah lebih kecil, dan indeks kunci bukan kunci utama menduduki ruang yang lebih sedikit. Secara amnya, adalah disyorkan untuk mencipta kunci utama autokenaikan untuk meminimumkan ruang yang diduduki oleh indeks kunci bukan utama.

2. Penutup Indeks

• Jika syarat klausa WHERE ialah indeks bukan kunci utama, pertanyaan akan mula-mula mencari indeks kunci utama melalui indeks kunci bukan utama (kunci utama terletak di nod daun bukan kunci utama pokok carian indeks kunci), dan kemudian cari kandungan pertanyaan melalui indeks kunci utama. Dalam proses ini, bergerak kembali ke pokok indeks kunci utama dipanggil carian jadual.
• Walau bagaimanapun, apabila kandungan pertanyaan kami ialah nilai kunci utama, kami boleh terus memberikan hasil pertanyaan tanpa carian jadual. Dalam erti kata lain, indeks bukan kunci utama telah "meliputi" keperluan pertanyaan kami dalam pertanyaan ini, oleh itu ia dipanggil indeks penutup.
• Indeks penutup boleh terus mendapatkan hasil pertanyaan daripada indeks tambahan tanpa carian jadual ke indeks utama, dengan itu mengurangkan bilangan carian (tidak perlu beralih dari pokok indeks tambahan ke pokok indeks berkelompok) atau mengurangkan Operasi IO (pokok indeks tambahan boleh memuatkan lebih banyak nod daripada cakera sekali gus), dengan itu meningkatkan prestasi.

3. Indeks Komposit

Indeks komposit merujuk kepada pengindeksan berbilang lajur jadual.

Senario 1:

Indeks komposit (a, b) diisih oleh a, b (diisih dahulu dengan a, jika a adalah sama kemudian diisih oleh b). Oleh itu, pernyataan berikut boleh terus menggunakan indeks komposit untuk mendapatkan hasil (sebenarnya, ia menggunakan prinsip awalan paling kiri):

• pilih … daripada xxx di mana a=xxx;
• pilih … daripada xxx di mana a=xxx pesanan oleh b;

Pernyataan berikut tidak boleh menggunakan pertanyaan komposit:

• pilih … daripada xxx di mana b=xxx;

Senario 2:

Untuk indeks komposit (a, b, c), pernyataan berikut boleh terus mendapatkan hasil melalui indeks komposit:

• pilih … daripada xxx di mana a=xxx pesanan oleh b;
• pilih … daripada xxx di mana a=xxx dan b=xxx pesanan oleh c;

Pernyataan berikut tidak boleh menggunakan indeks komposit dan memerlukan operasi isihan fail:

• pilih … daripada xxx di mana a=xxx pesanan oleh c;

Ringkasan:

Menggunakan indeks komposit (a, b, c) sebagai contoh, mencipta indeks sedemikian adalah bersamaan dengan mencipta indeks a, ab dan abc. Mempunyai satu indeks menggantikan tiga indeks sememangnya bermanfaat, kerana setiap indeks tambahan meningkatkan overhed operasi tulis dan penggunaan ruang cakera.

4. Prinsip Awalan Paling Kiri

• Daripada contoh indeks komposit di atas, kita boleh memahami prinsip awalan paling kiri.
• Bukan hanya definisi penuh indeks, selagi ia memenuhi awalan paling kiri, ia boleh digunakan untuk mempercepatkan perolehan semula. Awalan paling kiri ini boleh menjadi medan N paling kiri bagi indeks komposit atau aksara M paling kiri bagi indeks rentetan. Gunakan prinsip "awalan paling kiri" indeks untuk mencari rekod dan mengelakkan definisi indeks berlebihan.
• Oleh itu, berdasarkan prinsip awalan paling kiri, adalah penting untuk mempertimbangkan susunan medan dalam indeks apabila mentakrifkan indeks komposit! Kriteria penilaian ialah kebolehgunaan semula indeks. Contohnya, apabila sudah ada indeks pada (a, b), secara amnya tidak perlu membuat indeks berasingan pada a.

5. Tekan Turun Indeks

MySQL 5.6 memperkenalkan pengoptimuman tekan bawah indeks, yang boleh menapis rekod yang tidak memenuhi syarat berdasarkan medan yang disertakan dalam indeks semasa traversal indeks, mengurangkan bilangan carian jadual.

• Buat jadual

CREATE TABLE `test` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
   `age` int(11) NOT NULL DEFAULT '0',
   `name` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
   PRIMARY KEY (`id`),
   KEY `idx_name_age` (`name`,`age`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

• PILIH * daripada pengguna dengan nama seperti 'Chen%' prinsip awalan paling kiri, menekan idx_name_age index
• PILIH * daripada pengguna dengan nama seperti 'Chen%' dan umur=20
  • Sebelum versi 5.6, ia akan memadankan 2 rekod terlebih dahulu berdasarkan indeks nama (mengabaikan syarat umur=20 pada ketika ini), cari 2 ID yang sepadan, lakukan carian jadual dan kemudian tapis berdasarkan umur=20.
  • Selepas versi 5.6, tekan turun indeks diperkenalkan. Selepas memadankan 2 rekod berdasarkan nama, ia tidak akan mengabaikan syarat umur=20 sebelum melakukan carian jadual, menapis berdasarkan umur sebelum carian jadual. Tekan turun indeks ini boleh mengurangkan bilangan carian jadual dan meningkatkan prestasi pertanyaan.

6. Indeks Awalan

Apabila indeks ialah jujukan aksara yang panjang, ia boleh mengambil banyak memori dan menjadi perlahan. Dalam kes ini, indeks awalan boleh digunakan. Daripada mengindeks keseluruhan nilai, kami mengindeks beberapa aksara pertama untuk menjimatkan ruang dan mencapai prestasi yang baik. Indeks awalan menggunakan beberapa huruf pertama indeks. Walau bagaimanapun, untuk mengurangkan kadar pertindihan indeks, kita mesti menilai keunikan indeks awalan.

• Pertama, kira nisbah keunikan medan rentetan semasa: pilih 1.0*kiraan(nama yang berbeza)/kiraan(*) daripada ujian
• Kemudian, kira nisbah keunikan untuk awalan yang berbeza:
  • pilih 1.0*count(distinct left(name,1))/count(*) daripada ujian untuk aksara pertama nama sebagai indeks awalan
  • pilih 1.0*count(distinct left(name,2))/count(*) daripada ujian untuk dua aksara pertama nama sebagai indeks awalan
  • ...
• Apabila kiri(str, n) tidak meningkat dengan ketara, pilih n sebagai nilai potong indeks awalan.
• Buat indeks tukar jadual ujian tambah kunci(nama(n));

4. [Indeks Melihat]

Selepas menambah indeks, bagaimana kita melihatnya? Atau, jika kenyataan lambat dilaksanakan, bagaimanakah kita menyelesaikan masalah?

Terangkan biasanya digunakan untuk menyemak sama ada indeks berkesan.

Selepas memperoleh log pertanyaan yang perlahan, perhatikan pernyataan yang lambat. Tambah terangkan sebelum pernyataan dan laksanakannya semula. Terangkan menetapkan bendera pada pertanyaan, menyebabkan ia mengembalikan maklumat tentang setiap langkah dalam pelan pelaksanaan dan bukannya melaksanakan pernyataan. Ia mengembalikan satu atau lebih baris maklumat yang menunjukkan setiap bahagian pelan pelaksanaan dan pelaksanaan pesanan.

Medan penting dikembalikan dengan menerangkan:

• jenis: Menunjukkan kaedah carian (imbasan jadual penuh atau imbasan indeks)
• kunci: Medan indeks yang digunakan, batal jika tidak digunakan

Terangkan medan jenis:

• SEMUA: Imbasan meja penuh
• indeks: Imbasan indeks penuh
• julat: Imbasan julat indeks
• rujuk: Imbasan indeks bukan unik
• eq_ref: Imbasan indeks unik

Atas ialah kandungan terperinci Indeks Pangkalan Data Mysql Dijelaskan untuk Pemula. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!