Apakah situasi di mana MySQL menyebabkan kegagalan indeks?

1. Kerja penyediaan

Pertama sediakan dua meja untuk demonstrasi:

CREATE TABLE `student_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `course_id` int NOT NULL,
  `class_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;

CREATE TABLE `course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `course_id` int NOT NULL,
  `course_name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

#准备数据
select count(*) from student_info;#1000000
select count(*) from course;      #100

2 1. Utamakan penggunaan indeks bersama

Pernyataan SQL berikut tidak mempunyai indeks:

#平均耗时291毫秒
select * from student_info where name='123' and course_id=1 and class_id=1;

Kami mengoptimumkan kecekapan pertanyaannya dengan mencipta indeks Terdapat beberapa pilihan:

①Buat indeks biasa:

#建立普通索引
create index idx_name on student_info(name);
#平均耗时25毫秒，查看explain执行计划，使用到的是idx_name索引查询
select * from student_info where name='MOKiKb' and course_id=1 and class_id=1;

②Berdasarkan indeks biasa, tambah indeks bersama:

#name，course_id组成的联合索引
create index idx_name_courseId on student_info(name,course_id);
#该查询语句一般使用的是联合索引，而不是普通索引，具体看优化器决策
#平均耗时20ms
select * from student_info where name='zhangsan' and course_id=1 and class_id=1;

Anda dapat melihat bahawa

apabila berbilang indeks boleh digunakan, sistem secara amnya memberi keutamaan untuk menggunakan indeks bersama yang lebih panjang, kerana indeks bersama lebih pantas

berbanding, ini juga sepatutnya Ianya mudah difahami, dengan syarat prinsip padanan paling kiri bagi indeks sendi dipatuhi . Jika anda mencipta indeks bersama yang terdiri daripada nama, course_id, class_id, maka pernyataan sql di atas akan menggunakan indeks bersama ini dengan key_len yang lebih panjang seperti yang diharapkan (tanpa diduga, pengoptimum boleh memilih penyelesaian lain yang lebih baik, Jika ia lebih cepat).

Kelajuan indeks bersama tidak semestinya lebih baik daripada indeks biasa

Contohnya, jika syarat pertama menapis semua rekod, maka tidak perlu menggunakan yang berikutnya indeks.

2. Prinsip pemadanan paling kiri

#删除前例创建的索引，新创建三个字段的联合索引，name-course_id-cass_id
create index idx_name_cou_cls on student_info(name,course_id,class_id);

①Kes di mana semua indeks bersama sepadan:

#关联字段的索引比较完整
explain select * from student_info where name='11111' and course_id=10068 and class_id=10154;

Setiap keadaan medan sepadan dengan indeks kesatuan, jadi pernyataan SQL ini mengikut peraturan awalan paling kiri. Penggunaan indeks bersama membolehkan carian pantas dan mengelakkan pertanyaan tambahan, jadi ini adalah situasi yang optimum.

②Bahagian paling kanan indeks bersama tiada:

explain select * from student_info where name='11111' and course_id=10068;

Keadaan pernyataan sql tidak mengandungi semua indeks bersama syarat, tetapi memadamkan separuh kanan Indeks yang digunakan oleh pernyataan ini masih merupakan pertanyaan yang berkaitan, tetapi hanya sebahagian daripadanya digunakan Dengan melihat key_len, kita boleh mengetahui bahawa 5 bait ini tidak sesuai dengan class_id membuktikan class_id tidak berkuat kuasa (ia tidak di mana, jadi sudah tentu ia tidak digunakan).

Begitu juga, jika anda memadam medan course_id di mana, indeks bersama masih akan berkuat kuasa, tetapi key_len akan dikurangkan.

③Situasi hilang dalam indeks bersama:

#联合索引中间的字段未使用，而左边和右边的都存在
explain select * from student_info where name='11111' and class_id=10154;;

Pernyataan sql di atas masih menggunakan indeks bersama, tetapi key_lennya Ia telah menjadi lebih kecil. Hanya medan nama yang menggunakan indeks Walaupun medan class_id berada dalam indeks bersama, ia adalah GG kerana ia tidak mematuhi prinsip padanan paling kiri.

Aliran pelaksanaan keseluruhan pernyataan sql ialah: mula-mula cari semua rekod dengan nama 11111 dalam pepohon B indeks bersama, dan kemudian tapis rekod dengan class_id selain 10154 dalam teks penuh. Dengan satu lagi langkah carian teks penuh, prestasi akan menjadi lebih teruk daripada dalam ① dan ②.

④Situasi apabila bahagian paling kiri indeks sendi hilang:

explain select * from student_info where class_id=10154 and course_id=10068;

Situasi ini adalah kes khas dari sebelumnya situasi, yang terakhir dalam indeks bersama Medan di sebelah kiri tidak ditemui, jadi walaupun terdapat bahagian lain, semuanya tidak sah, dan carian teks penuh digunakan.

Kesimpulan: Prinsip padanan paling kiri bermakna pertanyaan bermula dari lajur paling kiri indeks dan lajur dalam indeks tidak boleh dilangkau Jika lajur dilangkau, indeks akan menjadi tidak sah sebahagiannya (. berikut Semua indeks medan tidak sah).

Nota: Apabila membuat indeks bersama, susunan medan ditetapkan, dan padanan paling kiri dibandingkan mengikut susunan ini tetapi dalam pernyataan pertanyaan, susunan medan dalam keadaan di mana adalah pilihan Mengubah bermakna bahawa tidak perlu mengikut susunan medan indeks yang berkaitan, selagi terdapat satu dalam keadaan di mana.

3. Indeks lajur di sebelah kanan keadaan julat tidak sah

mengambil alih indeks bersama di atas dan menggunakan pertanyaan sql berikut:

#key_len=> name:63,course_id:5,class_id:5
explain select * from student_info where name='11111' and course_id>1 and class_id=1;

key_len hanya 68, yang bermaksud class_id tidak digunakan dalam indeks yang berkaitan Walaupun ia mematuhi prinsip padanan paling kiri, simbol

> medan keadaan dalam indeks yang berkaitan tidak sah

. Tetapi jika anda menggunakan tanda >=:

#不是>、<,而是>=、<=
explain select * from student_info where name=&#39;11111&#39; and course_id>=20 and course_id<=40 and class_id=1;

Indeks di sebelah kanan tidak sah, key_len ialah 73, dan indeks semua medan digunakan.

结论：为了充分利用索引，我们有时候可以将>、<等价转为>=、<=的形式，或者将可能会有<、>的条件的字段尽量放在关联索引靠后的位置。

4.计算、函数导致索引失效

#删除前面的索引，新创建name字段的索引，方便演示
create index idx_name on student_info(name);

现有一个需求，找出name为li开头的学生信息：

#使用到了索引
explain select * from student_info where name like 'li%';
#未使用索引，花费时间更久
explain select * from student_info where LEFT(name,2)='li';

上面的两条sql语句都可以满足需求，然而第一条语句用了索引，第二条没有，一点点的改变真是天差地别。

结论：字段使用函数会让优化器无从下手，B树中的值和函数的结果可能不搭边，所以不会使用索引，即索引失效。字段能不用就不用函数。

类似：

#也不会使用索引
explain select * from student_info where name+''='lisi';

类似的对字段的运算也会导致索引失效。

5.类型转换导致索引失效

#不会使用name的索引
explain select * from student_info where name=123;
#使用到索引
explain select * from student_info where name='123';

如上，name字段是VARCAHR类型的，但是比较的值是INT类型的，name的值会被隐式的转换为INT类型再比较，中间相当于有一个将字符串转为INT类型的函数。

6.不等于(!= 或者<>)索引失效

#创建索引
create index idx_name on student_info(name);
#索引失效
explain select * from student_info where name<>&#39;zhangsan&#39;;
explain select * from student_info where name!=&#39;zhangsan&#39;;

不等于的情况是不会使用索引的。因为!=代表着要进行全文的查找，用不上索引。

7.is null可以使用索引，is not null无法使用索引

#可以使用索引
explain select * from student_info where name is null;
#索引失效
explain select * from student_info where name is not null;

和前一个规则类似的，!=null。同理not like也无法使用索引。

最好在设计表时设置NOT NULL约束，比如将INT类型的默认值设为0，将字符串默认值设为''。

8.like以%开头，索引失效

#使用到了索引
explain select * from student_info where name like 'li%';
#索引失效
explain select * from student_info where name like '%li';

只要以%开头就无法使用索引，因为如果以%开头，在B树排序的数据中并不好找。

9.OR前后存在非索引的列，索引失效

#创建好索引
create index idx_name on student_info(name);
create index idx_courseId on student_info(course_id);

如果or前后都是索引：

#使用索引
explain select * from student_info where name like 'li%' or course_id=200;

如果其中一个没有索引：

explain select * from student_info where name like 'li%' or class_id=1;

那么索引就失效了，假设还是使用索引，那就变成了先通过索引查，然后再根据没有的索引的字段进行全表查询，这种方式还不如直接全表查询来的快。

10.字符集不统一

字符集如果不同，会存在隐式的转换，索引也会失效，所有应该使用相同的字符集，防止这种情况发生。

三、建议

• 对于单列索引，尽量选择针对当前query过滤性更好的索引

• 在选择组合索引时，query过滤性最好的字段应该越靠前越好

• 在选择组合索引时，尽量选择能包含当前query中where子句中更多字段的索引

• 在选择组合索引时，如果某个字段可能出现范围查询，尽量将它往后放

Atas ialah kandungan terperinci Apakah situasi di mana MySQL menyebabkan kegagalan indeks?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!