Apakah mata pengetahuan tentang pengindeksan dan pengoptimuman MySQL?

Apakah itu indeks?

• Indeks ialah struktur data yang membantu MySQL melaksanakan pertanyaan yang cekap. Seperti jadual kandungan buku, bolehkah ia mempercepatkan pertanyaan?

Indeks boleh mempunyai indeks B-Tree dan indeks Hash. Indeks dilaksanakan dalam enjin storan

InnoDB/MyISAM hanya menyokong indeks B-Tree

Memory/Heap menyokong indeks B-Tree dan indeks Hash

B-Tree

B-Tree ialah struktur data yang sangat sesuai untuk operasi cakera. Ia ialah pepohon carian seimbang pelbagai hala. Ketinggiannya biasanya 2-4, dan nod bukan daun dan nod daunnya akan menyimpan data. Semua nod daunnya berada pada lapisan yang sama. Gambar di bawah ialah B-Tree

B+ Tree: B+ Tree ialah jenis berdasarkan pengoptimuman B-Tree. Perbezaan utama antara ia dan pokok B ialah semua data pokok B+ disimpan dalam nod daun, dan nod daun dirangkai bersama oleh senarai terpaut. Gambar di bawah ialah pokok B+

Saiz halaman dalam InnoDB ialah 16KB (halaman ialah nod pada pokok B+). jadual Kekunci utama ialah INT dan saiznya ialah 4 bait Nod itu juga boleh menyimpan nilai kunci 4K Dengan mengandaikan bahawa nilai penunjuk dan kunci menempati saiz yang sama, maka pokok B+ dengan ketinggian 3 mempunyai 2048 nod. lapisan kedua dan 2048 nod dalam lapisan ketiga Bilangan nod daun dalam lapisan ialah 2048*2048 = 4194304, dan satu nod ialah 16KB, jadi sejumlah 67108864KB, atau 65536MB, atau 64G data boleh ditampung.

Memandangkan nod daun dirangkai bersama oleh senarai terpaut, jika disusun mengikut lajur indeks, ia akan diisih secara lalai, jadi kecekapan akan menjadi sangat tinggi.

Indeks MyISAM

Indeks dan data MyISAM disimpan secara berasingan. Dalam indeks kunci utama MyISAM, alamat rekod disimpan dalam nod daun pokok B+, jadi MyISAM perlu melalui 2 kali IO

• 
  

Indeks sekunder MyISAM adalah sama dengan indeks kunci primer Satu-satunya perbezaan ialah kunci dalam indeks sekunder boleh diulang, tetapi kunci dalam indeks kunci primer tidak boleh diulang

Indeks InnoDB

Data dan indeks InnoDB disimpan bersama, juga dipanggil indeks berkelompok. Data diindeks oleh kunci utama dan disimpan pada nod daun pokok indeks kunci utama B+.

Indeks kunci utama InnoDB, data sudah disertakan dalam nod daun, iaitu indeks dan data disimpan bersama, iaitu indeks berkelompok.

• 
  
  

Indeks tambahan InnoDB, nod daun menyimpan nilai kunci utama, bukan alamat. Menggunakan indeks tambahan memerlukan dua carian.

Perbezaan antara indeks InnoDB dan MyISAM:

InnoDB menggunakan indeks berkelompok dan nod daun indeks kunci utamanya menyimpan data secara langsung, Nod daun dalam indeks tambahannya menyimpan nilai kunci utama

• MyISAM menggunakan indeks bukan berkelompok Data dan indeks tidak berada dalam fail yang sama dalam stor indeks kunci utamanya Alamat rekod baris disimpan pada nod daun dalam indeks tambahan, yang juga merupakan alamat rekod Walau bagaimanapun, kunci indeks tambahan boleh diulang, tetapi kunci utama indeks kunci tidak boleh diulang

• 
  Soalan

  :

Mengapa InnoDB tidak menggunakan terlalu banyak medan panjang sebagai kunci utama

?

Kunci utama yang terlalu panjang akan menjadikan indeks tambahan mengambil banyak ruang

• 
  Mengapakah InnoDB disyorkan menggunakan kunci utama penambahan automatik

  ?
Jika kunci utama yang meningkat secara automatik digunakan, setiap kali rekod baharu dimasukkan, rekod baharu akan ditambah secara berurutan ke kedudukan seterusnya nod indeks semasa Apabila satu halaman penuh, halaman baharu akan dibuka , supaya Struktur indeks sangat padat, dan tidak perlu memindahkan data sedia ada setiap kali ia dimasukkan, yang sangat cekap. Jika anda tidak menggunakan kunci utama penambahan automatik, anda perlu memilih kedudukan sisipan setiap kali anda memasukkan rekod baharu dan anda mungkin perlu mengalihkan data, yang menjadikan kecekapan tidak cekap dan struktur indeks tidak padat

• 
  Mengapa menggunakan pokok B+ dan bukannya pokok B

• Di manakah indeks itu wujud?

Indeks itu sendiri juga agak besar dan biasanya disimpan pada cakera Indeks dan data mungkin disimpan secara berasingan (indeks bukan berkelompok MyISAM) atau ia mungkin disimpan bersama (Berkelompok InnoDB. indeks)

• Kebaikan dan keburukan indeks?

Kelebihan

• Kurangkan IO Kos, tingkatkan kecekapan pertanyaan data

• Kurangkan kos pengisihan (lajur yang diindeks akan diisih secara automatik, menggunakan tertib mengikut akan meningkatkan kecekapan dengan banyak)

• Keburukan

• Indeks akan menduduki ruang storan tambahan

• Indeks akan mengurangkan kecekapan mengemas kini data jadual. Apabila menambah, memadam atau mengubah suai operasi, anda bukan sahaja mesti menyimpan data, tetapi juga mengemas kini indeks yang sepadan

Klasifikasi indeks

• Indeks lajur tunggal

• Indeks kunci utama

• Indeks unik

• Indeks biasa

• Indeks gabungan

Indeks menggunakan

• untuk mencipta indeks

 CREATE INDEX index_name ON table_name(col_name);
-- 或者
ALTER TABLE table_name ADD INDEX index_name(col_name)

• Padam indeks

DROP INDEX index_name ON table_name;

• Senario yang memerlukan pengindeksan

• Selalunya Lajur yang digunakan sebagai syarat pertanyaan perlu diindeks

• Dalam persatuan berbilang jadual, medan yang berkaitan perlu diindeks

• Medan yang diisih dalam pertanyaan perlu diindeks Bangunan indeks

• Senario di mana pengindeksan tidak sesuai

• Jadual dengan lebih banyak tulisan dan kurang bacaan tidak sesuai untuk bangunan indeks

• Medan yang kerap dikemas kini tidak sesuai untuk membina indeks

terangkan rancangan pelaksanaan

Terdapat jadual pengguna, di mana indeksnya adalah seperti berikut



Tiga medan nama, umur dan alamat digunakan sebagai indeks gabungan

Anda boleh menggunakan explain untuk melakukan analisis prestasi pada pernyataan SQL tertentu

explain select * from user where name = 'am';



kekunci_mungkin
Indeks yang mungkin digunakan
kunci
Indeks yang digunakan sebenarnya
key_len
Panjang indeks yang digunakan untuk pertanyaan
ref
Jika ia adalah pertanyaan yang setara, ia akan menjadi const
baris
Anggaran bilangan baris untuk diimbas (bukan nilai yang tepat)
tambahan

Maklumat tambahan, seperti

• menggunakan tempat
  menunjukkan bahawa hasil yang dikembalikan oleh enjin storan perlu ditapis pada lapisan SQL Layer

• menggunakan indeks
  menunjukkan bahawa tidak perlu membuat pertanyaan kembali ke jadual, ia akan berlaku apabila indeks penutup digunakan. Meliputi indeks bermakna bahawa lajur dalam pilihan adalah semua lajur indeks. Pertanyaan yang tidak perlu dikembalikan ke jadual bermakna anda boleh mendapatkan nilai lajur indeks secara langsung dengan melalui indeks tambahan dan tidak perlu mengambil rekod daripada indeks kunci utama

• menggunakan keadaan indeks
  MySQL 5.6.x dan kemudiannya menyokong ciri ICP (Tekankan Keadaan Indeks), yang boleh menolak keadaan semakan ke lapisan enjin storan Rekod yang tidak memenuhi syarat tidak dibaca secara langsung, bukannya dibaca terlebih dahulu dan kemudian dalam penapisan lapisan SQL Layer, yang mengurangkan bilangan baris yang diimbas oleh lapisan enjin storan



• menggunakan filesort
  tidak boleh diisih Indeks

taip

• sistem digunakan: ada hanya 1 baris data dalam jadual, atau jadual kosong

• const: Gunakan indeks unik atau indeks kunci utama, dan pertanyaan dengan nilai di mana dan rekod lain ialah 1 baris, juga dipanggil imbasan indeks unik



• ref: Untuk indeks bukan unik, pertanyaan menggunakan setara dengan keadaan atau awalan paling kiri peraturan.

Berikut ialah peraturan awalan paling kiri yang dipenuhi, iaitu, untuk idx_name_age_add, awalan paling kiri adalah memuaskan dan indeks pertama ialah nama



• julat: Imbasan julat indeks, biasa dalam >,





Perhatikan bahawa apabila suka, aksara kad bebas % tidak boleh diletakkan pada permulaan, jika tidak, ia akan menyebabkan imbasan jadual penuh



• indeks :





• semua yang tidak betul-betul sepadan dengan indeks tetapi tidak perlu ditanya kembali ke jadual : Imbas seluruh jadual, dan kemudian tapis rekod yang memenuhi keperluan dalam Lapisan SQL

索引使用规范（索引失效分析）

1. 全值匹配
   在索引列上使用等值查询

explain select * from user where name = 'y' and age = 15;

2. 最左前缀

组合索引中，查询条件要从组合索引的最左列开始，如上述example中组合索引idx_name_age_add，是建立在三个列name，age，address的，若跳过name，直接用age查询，则会变为全表扫描

explain select * from user where age = 15;

3. 不要在索引列上做计算

4. 范围条件右侧的索引列会失效

看到第一个SQL语句，没有用上addresss索引

5. 尽量使用覆盖索引

explain select name,age from user where name = 'y' and age = 1;

可以避免回表查询

6. 索引字段不要使用不等（!= 或 ）,不要判断null（is null/ is not null）
会导致索引失效，转为全表扫描

7. 索引字段上使用like时，不要以%开头

8. 索引字段如果是字符串，记得加单引号

9. 索引字段不要用or

例子总结：

Atas ialah kandungan terperinci Apakah mata pengetahuan tentang pengindeksan dan pengoptimuman MySQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!