MySQL이 인덱스 실패를 일으키는 상황은 무엇입니까?

1. 준비

먼저 시연용 테이블 두 개를 준비합니다.

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. 조인트 인덱스 사용 우선순위

다음 SQL 문에는 인덱스가 없습니다.

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

최적화합니다. 인덱스를 구축하여 쿼리 효율성을 높이는 방법은 다음과 같습니다.

①일반 인덱스 만들기:

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

②일반 인덱스를 기반으로 공동 인덱스 추가:

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

를 보면 알 수 있습니다. 여러 인덱스를 사용할 수 있는 경우 시스템은 일반적으로 조인트 인덱스가 더 빠르기 때문에 긴 조인트 인덱스를 사용하는 데 우선 순위를 둡니다. 이는 이해하기 쉽습니다. 전제는 가장 왼쪽 일치 원칙을 준수해야 한다는 것입니다. 색인 .

name,course_id,class_id로 구성된 공동 인덱스를 생성하면 위의 SQL 문은 예상대로 더 긴 key_len과 함께 이 공동 인덱스를 사용합니다. ).

공동 인덱스의 속도가 반드시 일반 인덱스보다 빠르지는 않습니다. 예를 들어 첫 번째 조건이 모든 레코드를 필터링하면 후속 인덱스를 사용할 필요가 없습니다.

2. 왼쪽 일치 원칙

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

① 결합 인덱스가 모두 일치하는 상황:

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

각 필드 조건이 결합 인덱스와 일치하므로 SQL 문은 가장 왼쪽 접두사 규칙을 따릅니다. 조인트 인덱스를 사용하면 빠른 조회가 가능하고 추가 쿼리를 피할 수 있으므로 이것이 최적의 상황입니다.

②조인트 인덱스의 가장 오른쪽 부분이 누락된 상황:

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

이 SQL 문의 조건에는 조인트 인덱스의 조건이 모두 포함되어 있지는 않지만, 인덱스의 오른쪽 절반이 지워져 있습니다. 이 상관 쿼리는 key_len을 보면 5바이트가 누락되었음을 알 수 있습니다. 이는 class_id가 유효하지 않음을 증명합니다. 물론 사용하지 않습니다.)

마찬가지로 where에서 Course_id 필드를 삭제하면 공동 인덱스는 계속 적용되지만 key_len은 줄어듭니다.

3조인트 인덱스 누락 상황:

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

위의 SQL 문은 여전히 ​​조인트 인덱스를 사용하지만, name 필드만 인덱스를 사용하고 class_id 필드는 더 작아집니다. 조인트 인덱스 이지만 가장 왼쪽 매칭 원칙을 충족하지 않기 때문에 GG입니다.

전체 SQL 문의 실행 흐름은 다음과 같습니다. 먼저 조인트 인덱스의 B-트리에서 이름이 11111인 모든 레코드를 찾은 다음 class_id가 10154가 아닌 이러한 레코드의 전체 텍스트를 필터링합니다. 전체 텍스트 검색을 한 단계만 더 수행하면 ①, ②보다 성능이 저하됩니다.

4결합 인덱스의 가장 왼쪽 필드가 누락된 경우:

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

이번 상황은 이전 상황의 특수한 경우이므로 다른 항목이 있음에도 불구하고 조인트 인덱스의 가장 왼쪽 필드를 찾을 수 없습니다. 부분은 모두 유효하지 않습니다. 전체 텍스트 검색입니다.

결론: 가장 왼쪽 일치 원칙은 쿼리가 인덱스의 가장 왼쪽 열에서 시작하고 인덱스의 열을 건너뛸 수 없음을 의미합니다. 열을 건너뛰면 인덱스가 부분적으로 유효하지 않습니다(모든 후속 필드 인덱스가 유효하지 않습니다). .

참고: 공동 인덱스를 생성할 때 필드 순서는 고정되어 있으며 이 순서에 따라 가장 왼쪽 일치 항목이 비교되지만 쿼리 문에서는 where 조건의 필드 순서가 가변적입니다. 이는 관련 인덱스 필드의 순서를 따를 필요가 없다는 것을 의미하며 where 조건에만 포함하면 됩니다.

3. 범위 조건 오른쪽의 열 인덱스가 잘못되었습니다.

위의 조인트 인덱스를 인수하고 다음 SQL 쿼리를 사용하세요.

#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이 68밖에 되지 않습니다. 연관 인덱스의 는 가장 왼쪽 일치 원칙을 따르지만 사용되지 않지만

> 기호는 연관 인덱스의 조건 필드 오른쪽에 있는 인덱스를 무효화하기 때문입니다 .

그러나 >= 기호를 사용하는 경우:

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

오른쪽의 인덱스는 유효하지 않으며 key_len은 73이며 모든 필드의 인덱스가 사용됩니다.

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

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

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

위 내용은 MySQL이 인덱스 실패를 일으키는 상황은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!