mysql

sql_miss_index

1. 准备工作

1.11 创建数据表 user

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int DEFAULT '0',
  `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `height` int DEFAULT '0',
  `address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code_age_name` (`code`,`age`,`name`),
  KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

此外，还创建了三个索引：

  • id：数据库的主键。
  • idx_code_age_name ：由code、age和name三个字段组成的联合索引。
  • idx_height ：普通索引。

插入一些数据

1
2
3
INSERT INTO sue.user (id, code, age, name, height, address) VALUES (1, '101', 21, 'Lucy', 175,'香港');
INSERT INTO sue.user (id, code, age, name, height, address) VALUES (2, '102', 18, 'Jack', 173,'台湾');
INSERT INTO sue.user (id, code, age, name, height, address) VALUES (3, '103', 23, 'Anddy', 174,'成都');

1.12 测试

1
2
explain select * from user where id=1;

执行可以看到结果,用到主键索引

primary key

2. 最左匹配原则

2.1 哪些情况索引有效

之前已经给code、age和name这3个字段建好联合索引：idx_code_age_name,

索引顺序是:

  1. code
  2. age
  3. name

==如果在使用联合索引时，没注意最左前缀原则，很有可能导致索引失效。==

1
2
3
4
5

explain select * from user where code='101';
explain select * from user where code='101' and age=21;
explain select * from user where code='101' and age=21 and name="anndy";
explain select * from user where code='101' and name="anndy";

img

结论:

这4条sql中都有code字段，它是索引字段中的第一个字段，也就是最左边的字段。只要有这个字段在，该sql已经就能走索引。

也就是 ==最左匹配原则==

2.2 哪些情况索引失效

1
2
3
4

explain select * from user where age=21;
explain select * from user where name='Lucy';
explain select * from user where age=21 and  name='Lucy';

img

从图中看出这3种情况下索引失效了。因为查询条件中，没有包含给定字段最左边的索引字段，即字段code。

3. 使用了select *

1
2
explain select * from user where name='苏三';

img

在该sql中用了 select * ，从执行结果看，走了全表扫描，没有用到任何索引，查询效率是非常低的。

如果改成只查code 和 name 列

1
2
3

explain select code,name from user where name='苏三';

从图中执行结果不难看出，该sql语句这次走了全索引扫描，比全表扫描效率更高。

其实这里用到了：==覆盖索引==。

4. 索引列上有计算

如果id列上面有计算，比如：

1
2
3

explain select * from users where id+1=2;

从上图中的执行结果，能够非常清楚地看出，该id字段的主键索引，在有计算的情况下失效了。

5. 索引列用来函数

1
2
3
4
5
6
7
8

# ok
explain select * from user  where height=17;


# lost
explain select * from user  where SUBSTR(height,1,2)=17;

在使用函数后，该sql语句竟然走了全表扫描，索引失效了。

6. 字段类型不同

1
2
3
4
5
6
7

# ok
explain select * from user where code="101";

# lost
explain select * from user where code=101;

第二个sql语句变成了全表扫描。因为少写了引号，这种小小的失误，竟然让code字段上的索引失效了。

问：为什么索引会失效呢？

答：因为code字段的类型是varchar，而传参的类型是int，两种类型不同。

如果 int类型，不管有咩加冒号，都可以正常走索引

1
2
3
4
5
6
#ok
explain select * from user where height=175;

#ok
explain select * from user where height='175';

说明: int类型字段作为查询条件时，它会自动将该字段的传参进行隐式转换，把字符串转换成int类型。

7. like左边包含%

1
2
3
4
5
6
7
8
9
10

#ok 
select * from user where name like '李%';

#lost
explain select * from user where code like '%1';

#lost
explain select * from user where code like '%1%';

从图中看出这种%在1左边时，code字段上索引失效了，该sql变成了全表扫描。

==索引就像字典中的目录。一般目录是按字母或者拼音从小到大，从左到右排序，是有顺序的。==

8. 列对比

假如我们现在有这样一个需求：

过滤出表中某两列值相同的记录。比如user表中id字段和height字段，查询出这两个字段中值相同的记录。

1
explain select * from user where id=height

意不意外，惊不惊喜？索引失效了。

id字段本身是有主键索引的，同时height字段也建了普通索引的，并且两个字段都是int类型，类型是一样的。

但如果把两个单独建了索引的列，用来做列对比时，索引会失效。

9. 使用or关键字

一个需求：想查一下id=1或者height=175的用户。

1
2
3
4
5
6
7
#ok
explain select * from user where id=1 or height='175';

#lost

explain select * from user where id=1 or height='175' or address='成都';

因为最后加的address字段没有加索引，从而导致其他字段的索引都失效了。

10. not in和not exists

在我们日常工作中用得也比较多的，还有范围查询，常见的有：

  • in
  • exists
  • not in
  • not exists
  • between and

10.1 not in

假如我们想查出height在某些范围之内的用户，这时sql语句可以这样写：

1
2
3
4

#ok
explain select * from user where height in (173,174,175,176);
explain select * from user  t1  where  exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)

关键字是走了索引的。

1
2
3
4
5
6
7
#ok
explain select * from user
where id  not in (173,174,175,176);

#lost
explain select * from user
where height not in (173,174,175,176);

==主键字段中使用not in关键字查询数据范围，仍然可以走索引。而普通索引字段使用了not in关键字查询数据范围，索引会失效。==

10.2 not exists

除此之外，如果sql语句中使用not exists时，索引也会失效。具体sql语句如下：

1
2
3
4

explain select * from user  t1
where  not exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)

从图中看出sql语句中使用not exists关键后，t1表走了全表扫描，并没有走索引。

10.3 order by

1
2
3
4
5
6
7


#ok
explain select * from user order by code limit 100;
explain select * from user order by code,age limit 100;
explain select * from user order by code,age,name limit 100;

order by后面的条件，也要遵循联合索引的最左匹配原则。

除了遵循最左匹配原则之外，有个非常关键的地方是，==后面还是加了limit关键字，如果不加它，索引会失效。==

10.4 配合where一起使用

1
2
3
4
5
6

#ok
explain select * from user where code='101' order by age;
explain select * from user where code='101' order by name;


虽说name是联合索引的第三个字段，但根据最左匹配原则，该sql语句依然能走索引，因为最左边的第一个字段code，在where中使用了。只不过order by的时候，排序效率比较低，需要走一次filesort排序罢了。

1
2
3
explain select * from user order by code desc,age desc limit 100;
explain select * from user where code='101' order by code, name;

依然走了索引。

11. 哪种情况不走索引

11.1 没加where或limit

1
2
explain select * from user order by code, name;

order by语句中没有加where或limit关键字，该sql语句将不会走索引。

11.2 对不同的索引做order by

前面基本都是联合索引，这一个索引的情况。但如果对多个索引进行order by，结果会怎么样呢？

1
2
3
explain select * from user
order by code, height limit 100;


索引失效了。

11.3 不满足最左匹配原则

1
2
explain select * from user order by name limit 100;

name字段是联合索引的第三个字段，从图中看出如果order by不满足最左匹配原则，确实不会走索引。

11.4 不同的排序

1
2
3
4

explain select * from user
order by code asc,age desc limit 100;

从图中看出，尽管order by后面的code和age字段遵循了最左匹配原则，但由于一个字段是用的升序，另一个字段用的降序，最终会导致索引失效。