Bagaimana untuk menyelesaikan kegagalan indeks MySQL

1. Kata Pengantar

Apabila mengindeks pernyataan SQL, anda akan menghadapi kegagalan indeks, yang mempunyai kesan penting pada kebolehlaksanaan dan kecekapan prestasi artikel ini menganalisisnya索引为何失效, apakah itu situasi di mana 导致索引失效 dan 优化解决方案 akan berlaku apabila indeks gagal, memfokus pada 最左前缀匹配原则, MySQL逻辑架构和优化器 dan 索引失效场景以及为何会失效.

2. Prinsip pemadanan awalan paling kiri

Sebelum ini saya telah menulis artikel tentang ciri-ciri dan isu pengoptimuman penambahan indeks pada MySQL.

Mula-mula perkenalkan prinsip yang akan digunakan dalam punca kegagalan indeks berikutnya: 最左前缀匹配原则.

Prinsip asas awalan paling kiri: Apabila MySQL membina indeks bersama, ia akan mematuhi prinsip padanan awalan paling kiri, iaitu, keutamaan paling kiri Apabila mendapatkan data, pemadanan bermula dari paling kiri indeks bersama.

Apakah prinsip padanan awalan paling kiri? Untuk memahami prinsip padanan paling kiri bagi indeks sendi, fahami prinsip asas indeks terlebih dahulu: lapisan bawah indeks ialah pokok B+, kemudian lapisan bawah indeks sendi juga ialah pokok B+, tetapi dalam pokok B+ nod indeks bersama Nilai kunci disimpan. Pangkalan data perlu dibina bergantung pada medan paling kiri dalam indeks bersama, kerana pepohon B+ hanya boleh menentukan hubungan indeks berdasarkan satu nilai.

Contoh: Buat indeks bersama (a, b), maka pokok indeksnya akan kelihatan seperti rajah di bawah.

Nilai a adalah mengikut tertib, dan susunan penampilan ialah 1, 1, 2, 2, 3, 3. Nilai b tidak tertib, dan nombor yang muncul ialah 1, 2, 1, 4, 1, dan 2. Apabila nilai-nilai a adalah sama, kita boleh melihat bahawa nilai-nilai b disusun dalam susunan tertentu, tetapi perlu diperhatikan bahawa susunan ini adalah relatif. Ini kerana peraturan MySQL untuk mencipta indeks bersama adalah terlebih dahulu mengisih medan paling kiri indeks bersama, berdasarkan pengisihan medan pertama, dan kemudian mengisih medan kedua. Oleh itu, tiada cara untuk menggunakan indeks untuk keadaan pertanyaan seperti b=2.

Memandangkan keseluruhan proses adalah berdasarkan analisis hasil explain, maka mari belajar tentang medan jenis dan medan key_lef dalam explain.

1.jenis: jenis sambungan .

• sistem: Jadual hanya mempunyai satu baris rekod (sama dengan jadual sistem Ini adalah kes khas jenis const Ia biasanya tidak muncul dan boleh diabaikan.

• const: Menunjukkan bahawa ia ditemui melalui indeks sekali digunakan untuk membandingkan kunci utama atau indeks unik. Kerana anda hanya perlu memadankan satu baris data, ia sangat pantas. Letakkan kunci utama dalam keadaan WHERE dan MySQL akan menukar pertanyaan kepada pertanyaan const.

• eq_ref: imbasan indeks unik, untuk setiap kunci indeks, hanya satu rekod dalam jadual sepadan dengannya. Biasa dilihat dalam kunci utama atau imbasan indeks unik. Nota: SEMUA imbasan jadual penuh jadual dengan rekod paling sedikit, seperti jadual t1 ref: imbasan indeks bukan unik, mengembalikan semua baris yang sepadan dengan nilai tunggal. Pada asasnya, ia adalah akses indeks yang mengembalikan semua baris yang sepadan dengan satu nilai, bagaimanapun, ia mungkin menemui berbilang baris yang sepadan, jadi ia harus merupakan gabungan carian dan imbasan.

• range: Dapatkan hanya julat baris yang diberikan, menggunakan indeks untuk memilih baris. Lajur utama menunjukkan indeks yang digunakan. Secara amnya, pertanyaan seperti antara, , dalam, dsb. muncul dalam pernyataan where. Imbasan julat pada lajur indeks ini lebih baik daripada imbasan indeks penuh. Ia hanya perlu bermula pada titik tertentu dan berakhir pada titik lain, tanpa mengimbas keseluruhan indeks.

• index: Imbasan Indeks Penuh, perbezaan antara indeks dan SEMUA ialah jenis indeks hanya melintasi pokok indeks. Ini biasanya SEMUA blok, kerana fail indeks biasanya lebih kecil daripada fail data. (Walaupun Indeks dan SEMUA kedua-duanya membaca keseluruhan jadual, indeks dibaca daripada indeks, manakala SEMUA dibaca daripada cakera keras)

• SEMUA: Imbasan Jadual Penuh, lintasi seluruh jadual ke Cari baris yang sepadan

2.key_len: Memaparkan panjang indeks yang MySQL benar-benar memutuskan untuk digunakan. Jika indeks adalah NULL, panjangnya ialah NULL. Jika tidak NULL, panjang indeks yang digunakan. Jadi medan ini boleh digunakan untuk membuat kesimpulan indeks yang digunakan.

Peraturan pengiraan:

• 1 Medan panjang tetap, int menduduki 4 bait, tarikh menduduki 3 bait, char(n ) mengambil alih. n watak.

• 2 Medan panjang berubah-ubah varchar(n) menduduki n aksara + dua bait.

• 3 Set aksara yang berbeza, bilangan bait yang diduduki oleh aksara adalah berbeza. Dalam pengekodan Latin1, satu aksara menduduki satu bait, dalam pengekodan gdk, satu aksara menduduki dua bait, dan dalam pengekodan UTF-8, satu aksara menduduki tiga bait.

(Memandangkan pangkalan data saya menggunakan format pengekodan Latin1, dalam pengiraan seterusnya, satu aksara dikira sebagai satu bait)

• 4 medan indeks, jika ditetapkan kepada NULL, 1 bait diperlukan.

Setelah memahami prinsip padanan awalan paling kiri, mari kita lihat senario kegagalan indeks dan analisis mengapa ia gagal.

3. Seni bina logik dan pengoptimum MySQL

MySQL逻辑架构:

Seni bina mysql boleh dibahagikan kepada kira-kira 4 lapisan, masing-masing Ya:

1. 1.Pelanggan: Pelbagai bahasa menyediakan kaedah untuk menyambung ke pangkalan data mysql, seperti jdbc, php, go, dll., anda boleh Bahasa pembangunan bahagian belakang yang dipilih memilih kaedah atau rangka kerja yang sepadan untuk menyambung ke mysql

2. 2.lapisan pelayan: termasuk penyambung, cache pertanyaan, penganalisis, pengoptimum, Pelaksana, dsb., meliputi kebanyakan fungsi perkhidmatan teras MySQL, serta semua fungsi terbina dalam (seperti tarikh, keluarga, fungsi matematik dan penyulitan, dsb.), semua fungsi enjin storan silang dilaksanakan dalam lapisan ini , seperti prosedur tersimpan, pencetus , paparan, dsb.

3. 3.Lapisan enjin storan: Bertanggungjawab untuk penyimpanan dan mendapatkan semula data, komponen yang sebenarnya berurusan dengan fail fizikal asas. Intipati data disimpan pada cakera Data disimpan secara teratur melalui enjin storan tertentu dan diekstrak mengikut keperluan perniagaan. Model seni bina enjin storan adalah pemalam dan menyokong berbilang enjin storan seperti Innodb, MyIASM dan Memory. Enjin storan yang paling biasa digunakan sekarang ialah Innodb, yang telah menjadi enjin storan lalai sejak mysql5.5.5.

4. 4.Lapisan fail fizikal: Menyimpan data jadual sebenar, log, dsb. pangkalan data. Fail fizikal termasuk: redolog, undolog, binlog, errorlog, querylog, slowlog, data, indeks, dsb.

Pengenalan kepada komponen penting lapisan pelayan:

1. Penyambung

Penyambung bertanggungjawab untuk datang daripada sambungan Pelanggan, mendapatkan kebenaran pengguna, mengekalkan dan mengurus sambungan.

Selepas pengguna berjaya mewujudkan sambungan, walaupun anda menggunakan akaun pentadbir untuk mengubah suai kebenaran pengguna, ia tidak akan menjejaskan kebenaran sambungan sedia ada. Selepas pengubahsuaian selesai, hanya sambungan baharu akan menggunakan tetapan kebenaran baharu.

2. Cache pertanyaan

Selepas mysql mendapat permintaan pertanyaan, ia akan pergi ke cache pertanyaan terlebih dahulu untuk melihat sama ada pernyataan ini telah dilaksanakan sebelum ini. Penyata yang dijalankan sebelum ini dan outputnya boleh disimpan terus dalam ingatan, dicache sebagai pasangan nilai kunci. Kuncinya ialah pernyataan pertanyaan, dan nilainya ialah hasil pertanyaan. Apabila kata kunci pertanyaan SQL boleh dipadankan terus dalam cache pertanyaan, hasil pertanyaan (nilai) akan dikembalikan terus kepada klien.

Malah, adalah disyorkan untuk tidak menggunakan cache pertanyaan dalam kebanyakan kes Mengapa? Kerana caching pertanyaan selalunya mendatangkan lebih banyak kemudaratan daripada kebaikan. Selagi operasi kemas kini jadual terlibat, semua cache pertanyaan yang berkaitan dengan jadual boleh menjadi tidak sah dan dikosongkan dengan mudah. Oleh itu, berkemungkinan besar selepas menyimpan hasilnya dengan bersungguh-sungguh, keputusan itu akan dikosongkan oleh operasi kemas kini baharu sebelum ia boleh digunakan. Untuk pangkalan data dengan banyak operasi kemas kini, kadar hit bagi cache pertanyaan akan menjadi sangat rendah. Melainkan perniagaan memerlukan jadual statik, ia hanya akan dikemas kini sekali untuk masa yang lama. Sebagai contoh, jika ia adalah jadual konfigurasi sistem, maka pertanyaan jadual ini sesuai untuk menggunakan cache pertanyaan.

3. Penganalisis

Analisis leksikal (mengenal pasti kata kunci, operasi, nama jadual, nama lajur)
Analisis sintaksis (tentukan sama ada ia mematuhi tatabahasa)

4. Pengoptimum

Pengoptimum menentukan indeks yang hendak digunakan apabila terdapat berbilang indeks dalam jadual atau apabila terdapat berbilang perkaitan jadual (cantuman) dalam satu masa penyata; tentukan susunan sambungan setiap jadual. Selepas fasa pengoptimum selesai, pelan pelaksanaan pernyataan ini ditentukan, dan kemudian memasuki fasa pelaksana.

5 Apabila pelaksana

memulakan pelaksanaan, ia mesti terlebih dahulu menentukan sama ada pengguna mempunyai kebenaran untuk melaksanakan pertanyaan pada jadual T ini. Jika tidak, ralat tiada kebenaran akan dikembalikan. Jika cache pertanyaan dipukul, pengesahan kebenaran akan dilakukan apabila cache pertanyaan mengembalikan keputusan. Pertanyaan juga memanggil prasemak untuk mengesahkan kebenaran sebelum pengoptimum. Jika anda mempunyai kebenaran, buka jadual dan teruskan pelaksanaan. Apabila jadual dibuka, pelaksana akan memanggil antara muka yang disediakan oleh enjin berdasarkan definisi enjin jadual. Dalam sesetengah senario, pelaksana dipanggil sekali dan berbilang baris diimbas di dalam enjin, jadi bilangan baris yang diimbas oleh enjin ditentukan oleh baris_diperiksa并不是完全相同的.

MySQL优化器:

Pengoptimum MySQL menggunakan pengoptimuman berasaskan kos (Pengoptimuman berasaskan Kos), mengambil penyata SQL sebagai input dan menggunakan model kos terbina dalam dan maklumat kamus data serta enjin storan Maklumat statistik menentukan langkah mana yang digunakan untuk melaksanakan pernyataan pertanyaan, iaitu pelan pertanyaan.

Dari peringkat tinggi, pelayan MySQL dibahagikan kepada dua komponen: lapisan pelayan dan lapisan enjin storan. Antaranya, pengoptimum berfungsi pada lapisan pelayan, yang terletak di atas API enjin storan.

Proses kerja pengoptimum boleh dibahagikan kepada empat peringkat secara semantik:

1.Transformasi logik, termasuk penyingkiran penolakan, pemindahan nilai sama dan pemindahan berterusan, penilaian ekspresi berterusan, penukaran gabungan luar kepada cantuman dalam, penukaran subkueri, penggabungan paparan, dll.;
Persediaan pengoptimuman, seperti analisis kaedah capaian rujukan indeks dan julat, nilai kipas keadaan pertanyaan (kipas keluar, bilangan rekod selepas penapisan) analisis, pengesanan jadual berterusan 3.
Berdasarkan mengenai Pengoptimuman kos , termasuk pemilihan kaedah akses dan susunan sambungan; -bawah.
4. Senario kegagalan indeks dan mengapa ia gagal 1.

Di atas memperkenalkan prinsip asas padanan awalan paling kiri Kita tahu bahawa struktur data indeks yang biasa digunakan ialah pepohon B+. dan Indeks diisih. Jika jenis kata kunci indeks ialah

, susunan

indeks adalah seperti berikut:

like以通配符%开头索引失效。Int 类型

Data hanya disimpan dalam

, dan ia adalah Pelepasan.

Jika jenis kata kunci indeks ialah 叶子节点, susunan isihan 有序 adalah seperti berikut:

String类型

Ia boleh dilihat bahawa susunan isihan indeks Ia diisih mengikut

rentetan perbandingan. Apabila kami melakukan pertanyaan kabur, jika kami meletakkan % di hadapan, huruf n paling kiri akan menjadi kabur dan tidak pasti untuk mencari data yang memenuhi syarat.

首字母 juga sama apabila menggunakan
Jika peraturan pesanan indeks dilanggar, indeks juga akan menjadi tidak sah dan (最左前缀底层原理) imbasan akan dilakukan.

Contoh: Terdapat indeks gabungan dalam contoh jadual: (A, B, C)

PILIH * DARI contoh DI MANA A=1 dan B =1 dan C=1 联合索引Telusuri indeks; 全表 PILIH A DARI contoh DI MANA C =1 dan B=1 PESANAN OLEH A; Go Index

可以
Indeks penutup: 可以Indeks mengandungi semua data yang memenuhi keperluan pertanyaan, yang dipanggil indeks penutup (Indeks Penutup)
不可以

Boleh ada dua Terdapat dua cara

:

Satu ialah , dan yang kedua ialah .

2.

Medan dalam jadual adalah daripada jenis rentetan dan merupakan indeks biasa pepohon B+ Jika syarat pertanyaan melepasi nombor, ia tidak akan diindeks. 优化Contoh: Terdapat medan dalam contoh jadual yang dipanggil pid yang jenis varchar.

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE pid = 1

rrree使用覆盖索引Mengapa pernyataan pertama tidak diindeks tanpa petikan tunggal? Ini kerana apabila petikan tunggal tidak ditambah, perbandingan adalah antara rentetan dan nombor Jenisnya tidak sepadan dengan MySQL akan melakukan penukaran jenis 把%放后面 dan menukarnya kepada nombor titik terapung sebelum perbandingan.

3.字段类型是字符串，where时没有用引号括起来。Syarat pertanyaan mengandungi atau, yang boleh menyebabkan kegagalan indeks.
Contoh: Terdapat medan dalam contoh jadual di mana pid adalah jenis int dan skor adalah jenis int.

//此时执行语句type为ref索引查询
explain SELECT * FROM example WHERE pid = '1'

//此时执行语句type为ref索引查询
explain SELECT * FROM example WHERE pid = 1

Untuk kes menambah skor tanpa indeks selepas ATAU, dengan mengandaikan bahawa ia memerlukan indeks p_id, tetapi apabila ia datang kepada keadaan pertanyaan skor, ia masih perlu mengimbas keseluruhan jadual, yang memerlukan tiga -langkah proses : 隐式

MySQL mempunyai pengoptimum dengan mengambil kira kecekapan dan kos, adalah munasabah bahawa indeks mungkin gagal apabila menghadapi keadaan ATAU.

OR 前后只要存在非索引的列，都会导致索引失效。
: Jika lajur atau syarat diindeks, indeks mungkin hilang.

4.全表扫描+索引扫描+合并。Dalam indeks bersama, apabila syarat pertanyaan memenuhi prinsip padanan paling kiri, indeks akan berkuat kuasa seperti biasa.
Apabila kita mencipta indeks bersama, seperti (k1,k2,k3), ia bersamaan dengan mencipta tiga indeks (k1), (k1,k2) dan (k1,k2,k3) Ini adalah padanan paling kiri prinsip.

Contoh: Terdapat indeks bersama idx_pid_score, dengan pid pertama dan skor kedua.

//把or条件加没有索引的score，并不会走索引，为ALL全表查询
explain SELECT * FROM example WHERE pid = 1 OR score = 10

//此时执行语句type为ref索引查询,idx_pid_score索引
explain SELECT * FROM example WHERE pid = 1 OR score = 10

//此时执行语句type为ref索引查询,idx_pid_score索引
explain SELECT * FROM example WHERE pid = 1

注意Indeks bersama tidak memenuhi prinsip paling kiri, dan indeks biasanya akan gagal, tetapi ini juga berkaitan dengan pengoptimum Mysql.

5.联合索引(组合索引)，查询时的条件列不是联合索引中的第一个列，索引失效。
Masa lahir diindeks, tetapi kerana ia menggunakan fungsi terbina dalam mysql Date_ADD(), ia tidak diindeks.
Contoh: Dalam contoh jadual, indeks idx_birth_time ialah medan masa lahir jenis datetime

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE score = 10

Terdapat juga operasi pada lajur indeks (seperti +, -, *, /), dan indeks menjadi tidak sah.

Contoh: Terdapat indeks medan skor jenis int idx_score

dalam contoh jadual

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE score-1=5

还有不等于(!= 或者<>)导致索引失效。
例子：在表example中有int类型的score字段索引idx_score

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE score != 2

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE score <> 3

虽然score 加了索引，但是使用了！= 或者 < >，not in这些时，索引如同虚设。
6. is null可以使用索引，is not null无法使用索引。
例子:在表example中有varchar类型的name字段索引idx_name，varchar类型的card字段索引idx_card。

//此时执行语句type为range索引查询
explain SELECT * FROM example WHERE name is not null

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE name is not null OR  card is not null

7.左连接查询或者右连接查询查询关联的字段编码格式不一样。两张表相同字段外连接查询时字段编码格式不同则会不走索引查询。
例子:在表example中有varchar类型的name字段编码是utf8mb4,索引为idx_name
在表example_two中有varchar类型的name字段编码为utf8,索引为idx_name。

//此时执行语句example表会走type为index类型索引，example_two则为ALL全表搜索不走索引
explain SELECT e.name,et.name FROM example e LEFT JOIN example_two et on e.name = et.name

当把两表的字段类型改为一致时:

//此时执行语句example表会走type为index类型索引，example_two会走type为ref类型索引
explain SELECT e.name,et.name FROM example e LEFT JOIN example_two et on e.name = et.name

所以字段类型也会导致索引失效
8.mysql估计使用全表扫描要比使用索引快,则不使用索引。当表的索引被查询，会使用最好的索引，除非优化器使用全表扫描更有效。优化器优化成全表扫描取决与使用最好索引查出来的数据是否超过表的30%的数据。
建议:不要给’性别’等增加索引。如果某个数据列里包含了均是"0/1"或“Y/N”等值，即包含着许多重复的值，就算为它建立了索引，索引效果不会太好，还可能导致全表扫描。
Mysql出于效率与成本考虑，估算全表扫描与使用索引，哪个执行快，这跟它的优化器有关。

Atas ialah kandungan terperinci Bagaimana untuk menyelesaikan kegagalan indeks MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!