Analisis kes kegagalan indeks terperinci MySQL

Struktur storan indeks

Pertama sekali, mari kita fahami struktur storan indeks Hanya dengan mengetahui struktur storan indeks kita boleh lebih memahami masalah kegagalan indeks.

Struktur storan indeks adalah berkaitan dengan enjin storan MySQL Enjin storan yang berbeza menggunakan struktur yang berbeza.

Enjin storan lalai MySQL InnoDB menggunakan B+Tree sebagai struktur data indeks Apabila membuat jadual, InnoDB akan mencipta indeks kunci utama secara lalai, iaitu indeks berkelompok dan indeks lain ialah indeks sekunder.

Apabila enjin storan MyISAM mencipta jadual, ia menggunakan indeks pepohon B+ secara lalai.

Walaupun kedua-duanya menyokong indeks pepohon B+ seperti InnoDB, mereka menyimpan data dalam cara yang berbeza

InnoDB ialah indeks berkelompok (nod daun indeks pepohon B+ menyimpan data itu sendiri)

MyISAM ialah indeks bukan berkelompok (alamat fizikal tempat nod daun bagi pokok B+ menyimpan data)

Seperti yang ditunjukkan dalam rajah di bawah:

Enjin storan InnoDB boleh dibahagikan kepada [indeks berkelompok] dan [indeks sekunder]. disimpan dalam indeks berkelompok Nod daun indeks sekunder menyimpan nilai kunci primer.

Apabila menggunakan medan indeks sekunder sebagai syarat pertanyaan dan menanyakan data pada indeks berkelompok,

mula-mula akan mencari nod daun yang sepadan pada indeks sekunder mengikut syarat untuk mendapatkan primer nilai kunci,

dan kemudian cari nod daun yang sepadan pada indeks berkelompok berdasarkan nilai kunci utama dan kemudian tanya data yang sepadan

Proses ini dipanggil semula ke jadual

Gunakan indeks sekunder sebagai syarat pertanyaan Apabila data pertanyaan berada pada nod daun indeks sekunder, maka anda hanya perlu mencari nod daun yang sepadan dengan pepohon B+. indeks sekunder dan baca data. Proses ini dipanggil indeks meliputi

Syarat pertanyaan di atas semuanya menggunakan lajur indeks, tetapi ini tidak bermakna indeks itu pasti akan berkuat kuasa jika lajur indeks digunakan. Mari kita lihat indeks sekali lagi

Keadaan pertanyaan kabur yang tidak munasabah

Apabila menggunakan pertanyaan kabur kiri atau kiri, iaitu like "%张" atau <.>kedua-dua kaedah pertanyaan kabur ini akan menyebabkan kegagalan indekslike "%张%"

Oleh kerana pokok B+ disusun mengikut nilai indeks, apabila awalan tidak pasti, ia mungkin "kepingan kecil", "kepingan kedua" dan seterusnya, jadi ia hanya boleh diselesaikan dengan imbasan jadual penuh Pertanyaan

menggunakan fungsi

sebagai contoh:

SELECT * FROM sys_user WHERE LENGTH(user_id) = 3 ;

kerana indeks menyimpan nilai asal medan indeks, bukan Nilai yang dikira oleh fungsi, jadi indeks tidak akan digunakan apabila menggunakan fungsi

Walau bagaimanapun, bermula dari MySQL 8.0, ciri indeks menambah indeks fungsi, yang bermaksud mencipta indeks untuk nilai yang dikira oleh fungsi , supaya data boleh disoal dengan mengimbas indeks

alter table t_user add key idx_name_length ((length(name)));

Lakukan pengiraan ungkapan pada indeks

.

Contohnya:

select * from sys_user where user_id+1 =3;

Tetapi jika

tidak dikira pada medan indeks, indeks akan digunakan semula SELECT * FROM sys_user WHERE user_id = 1+1 ;

Sebabnya sama seperti menggunakan fungsi untuk indeks, indeks menyimpan indeks Nilai asal medan, bukan nilai yang dikira, jadi indeks tidak boleh digunakan

Gunakan penukaran tersirat untuk indeks

Medan

di sini ialah indeks sekunder dan jenis varchar phone

Apabila menggunakan integer sebagai parameter pertanyaan, jenis dalam pelan pelaksanaan ialah SEMUA, yang disoal melalui imbasan jadual penuh, tetapi Jika ia adalah jenis rentetan, pertanyaan indeks masih digunakan

Mari kita lihat contoh lain

Di sini

adalah jenis besar, tetapi menggunakan rentetan sebagai parameter pertanyaan masih menggunakan indeksuser_id

为什么第一个例子导致了索引失效，而第二个不会呢？

这里就要了解一下MySQL的字符转换规则了，看是数字转字符串，还是字符串转数字

我们可以用select "10">9来测试一下

如果是数字转字符串，那么就相当于select "10">"9"结果应该是0

如果是字符串转数字，那么就相当于select 10>9，结果是1

在MySQL中的执行结果如下：

这就说明，MySQL在遇到数字与字符串的比较的时候，会自动把字符串转换为数字，然后进行比较

也就是说，在第一个例子中

SELECT * FROM sys_user WHERE phone = 18200000000 ;

相当于

SELECT * FROM sys_user WHERE CAST(phone AS UNSIGNED) = 18200000000 ;

这就在索引字段上使用了函数，所以导致索引失效

而在第二个例子中

SELECT * FROM sys_user WHERE user_id = "1" ;

相当于

SELECT * FROM sys_user WHERE user_id = CAST("1" AS UNSIGNED) ;

函数式作用在查询参数上的，并没有作用在索引字段上，所以还是走索引的

联合索引非最左匹配

多个普通字段组合在一起创建的索引叫做联合索引（组合索引）

在使用联合索引的时候，一定要注意顺序问题，联合索引的使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引匹配。

例如，创建了一个(a,b,c)联合索引，那么如果查询条件是一下几种，就可以匹配上联合索引

where a = 1
where a = 1 and b = 2
where a = 1 and b = 2 and c = 3

需要注意的是，因为有查询优化器，所以a字段在where子句中的顺序不重要

若缺少a字段，则以下几种情况由于不符合最左匹配原则将无法匹配联合索引，导致该联合索引失效

where b = 2
where c = 3
where b = 2 and c = 3

还有一个比较特殊的查询条件：where a = 1 and c = 3

在MySQL5.5的话，前面的a 会走索引，在联合索引找到主键值，然后回表，到主键索引读取数据行，然后在比对c字段的值

在MySQL5.6之后，有一个索引下推的功能，

下推就是将部分上层(服务层)负责的事情，交给了下层(引擎层)处理

存储引擎直接在联合索引里按照c=3过滤，按照过滤后的数据在进行回表扫描，减少了回表的次数，从而提升了性能

在执行计划中Extra = Using index condition就表示使用了索引下推

联合索引不遵循最左匹配原则的原因：在联合索引中，数据按照第一列索引进行排序，第一列数据相同时，才会按照第二列进行排序，以此类推，所以直接使用第二列进行查询的时候，联合索引就会失效

where子句中的or

where子句中or的条件列有不是索引列会导致索引失效

例如：下图中id是索引列，email不是索引列，从执行计划来看，进行了全文扫描并没有使用到索引

因为or关键字只满足一个条件就可以，因此只要有一个列不是索引列，其他索引列也就没有意义了，就会进行全表扫描

在email列上建立索引之后，可以看到执行计划中使用到了两个索引

type = index_merge表示对id 和email都进行了扫描，然后进行了合并

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