In welchen Situationen verursacht MySQL einen Indexfehler?

1. Vorbereitung

Bereiten Sie zunächst zwei Tische für die Demonstration vor:

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. Index Invalidierungsregeln

1. Priorisieren Sie die Verwendung gemeinsamer Indizes

Die folgende SQL-Anweisung hat keinen Index:

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

Wir erstellen einen Index, um ihn zu optimieren Für die Abfrageeffizienz gibt es mehrere Optionen wie folgt:

①Erstellen Sie einen normalen Index:

#建立普通索引
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;

②Im Normalfall Auf der Basis des Indexes wird ein gemeinsamer Index hinzugefügt:

#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;

Sie können sehen, dass in mehreren Indizes verwendet werden kann Das System gibt im Allgemeinen der Verwendung längerer Gelenkindizes Vorrang, da Gelenkindizes im Vergleich schneller sind. 🎜🎜#. Wenn Sie einen gemeinsamen Index erstellen, der aus Name, Kurs-ID und Klassen-ID besteht, verwendet die obige SQL-Anweisung diesen gemeinsamen Index wie erwartet mit einer längeren key_len (unerwarteterweise wählt der Optimierer möglicherweise andere bessere Lösungen). wenn es schneller wäre).

Wenn beispielsweise die erste Bedingung alle Datensätze filtert, besteht keine Notwendigkeit, a zu verwenden Nachbestellungsindex. #? 🎜#

Jede Feldbedingung entspricht dem Union-Index, daher folgt diese SQL-Anweisung der Präfixregel ganz links. Die Verwendung eines gemeinsamen Index ermöglicht schnelle Suchvorgänge und vermeidet zusätzliche Abfragen. Dies ist also die optimale Situation.

②Die Situation, in der der ganz rechte Teil des gemeinsamen Index fehlt:

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

Die SQL-Anweisungsbedingung enthält nicht alle Bedingungen des gemeinsamen Index, sondern löscht die rechte Hälfte. Der von dieser Anweisung verwendete Index ist immer noch die zugehörige Abfrage, aber Sie können dies anhand von key_len erkennen dass 5 Bytes fehlen, was der Klassen-ID entspricht, was beweist, dass die Klassen-ID nicht wirksam ist (sie befindet sich nicht dort, daher wird sie natürlich nicht verwendet).

③Die Situation des Fehlens im gemeinsamen Index:

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

④Die Situation, in der der ganz linke Teil des gemeinsamen Index fehlt:

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

Schlussfolgerung: Das Prinzip der ganz linken Übereinstimmung bedeutet, dass die Abfrage in der Spalte ganz links im Index beginnt und keine Spalten im Index übersprungen werden können. Wenn eine Spalte übersprungen wird, ist der Index teilweise ungültig . (Alle nachfolgenden Feldindizes sind ungültig).

Hinweis: Beim Erstellen eines gemeinsamen Index ist die Reihenfolge der Felder festgelegt und die Übereinstimmung ganz links wird gemäß dieser Reihenfolge verglichen Felder in der Where-Bedingung Die Reihenfolge ist variabel, was bedeutet, dass es nicht erforderlich ist, die Reihenfolge der zugehörigen Indexfelder einzuhalten, solange es eines in der Where-Bedingung gibt.

3. Der Spaltenindex auf der rechten Seite der Bereichsbedingung ist ungültig

Übernehmen Sie den obigen gemeinsamen Index und verwenden Sie die folgende SQL-Abfrage: #🎜 🎜#

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

key_len ist nur 68, was bedeutet, dass class_id nicht im zugehörigen Index verwendet wird, obwohl es dem Leftmost-Matching-Prinzip entspricht, ist > Das Symbol ; macht den Index rechts vom Bedingungsfeld im zugehörigen Index ungültig

Aber wenn Sie das >=-Zeichen verwenden:

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

Der Index auf der rechten Seite ist nicht ungültig, key_len schon 73, alle Die Indizes der Felder werden alle verwendet.

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

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子句中更多字段的索引

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

Das obige ist der detaillierte Inhalt vonIn welchen Situationen verursacht MySQL einen Indexfehler?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!