SQL进阶理论篇（六）：索引的使用原则_sql表索引

简介

如何通过索引让查询效率最大化呢？本节主要考虑以下几个问题：

• 什么样的情况下需要创建索引？
• 什么样的情况下不需要创建索引？
• 索引在哪些情况下会失效，如何避免？

什么时候创建索引？

• 字段的数值有唯一性的限制，比如说用户名；

这种情况非常适合创建索引，可以直接创建唯一性索引或者主键索引。

• 频繁作为where查询条件的字段，可以创建索引；

在数据量大的情况下，如果一个字段在SQL查询的where条件里被经常使用到，那么就需要给这个字段创建索引。即使是普通索引也能大幅度提升查询的效率。

• 经常作为group by或者order by的字段，可以创建索引；

这里有个有意思的点。

对于group by user_id order by comment_time desc，我对user_id和comment_time字段分别建索引，查询速度要比创建联合索引（user_id, comment_time）的速度慢。

这是因为多个单列索引在多条件查询时，一般只会生效一个索引，MySQL会选择其中一个限制最严格的作为索引。

因此，在多条件联合查询的时候，最好创建联合索引。

而且联合索引的创建顺序也是有讲究的。在上个例子里，（user_id, comment_time）的联合索引，速度要比（comment_time，user_id）的联合索引，查的更快，二者同时比两个单列索引都快。

这主要是因为进行select查询的时候，是先进行group by，后执行order by，因此按照这个顺序的联合索引的效率是最高的。

这时候就有疑问了，既然是先进行group by，后order by，那根据索引最左原则，我如果声明的是（comment_time，user_id）的联合索引，那岂不是索引就失效了？

但其实并没有失效，只是使用的效率稍微低了些。这是因为当语句中只用了一部分索引字段的时候，才需要考虑最左原则，如果语句中使用了联合索引中的全部字段，那就不需要考虑最左匹配原则了，不会失效。

2023-10-31 22:43:42 在MySQL8.0里测试了下，确实是这样，explain里显示，两种情况下其实都用了索引。这块有点杂，暂时不深入了，有兴趣之后可以再看吧。

• update、delete的where条件列，也可以创建索引；

这是因为update和delete的时候，都得先根据where条件列检索出符合情况的数据，只要涉及检索了，用索引都可以在一定程度上提速。

• distinct的字段，也可以创建索引；

如果我们经常需要对某个字段做distinct，那么为这个字段创建索引，也可以大大加快distinct处理的速度。这是因为索引会对数据按照某种顺序进行排序，对有序数据的去重自然比对无序数据的去重快得多。

• 在做多表join的时候，对用于连接的字段，也可以创建索引。注意两边字段类型要一致。

什么时候不需要创建索引

• where条件、group by和order by、或者是on连接里不经常用到的字段不需要创建索引；

因为索引的价值在于快速定位数据，至于起不到定位作用的字段，没有创建索引的价值。即使它们会出现在select里。

• 表记录太少，比如小于1000个，没有创建索引的必要。因为即使创建了索引，提升也不大，而且如果数据太少，优化器会认为使用索引还不如直接全表扫描（索引检索后还得回表），这时候就会强制进行全表扫描，忽略索引；
• 如果字段中有大量重复数据，一般也不用创建索引。除非是数据比重偏差很大的情况，比如100w数据里，有10个男性，这时候可以对性别字段创建索引。
• 频繁更新的字段，不需要创建索引。因为更新数据的时候，也需要同步更新索引，频繁的更新会带来负担，从而影响效率。

索引在什么情况下会失效

一些常见的索引失效的例子，主要实际生产中可以考虑避免这些问题。

• 如果对索引的字段进行了表达式计算，该索引会失效。

如where comment_id+1=70000，这时字段comment_id的索引会失效。

这是因为我们需要把这个字段的每个值取出来，进行表达式的计算，因此实际上是进行了一次全表扫描，索引用不上。

这种情况下，如果想要使用索引，我们可以把代码重写成where comment_id=69999。

• 如果对索引的字段使用函数，该索引会失效。

比如where substring(comment_text, 1, 3) = 'abc'，这时字段comment_text的索引会失效。

失效的原因跟上面是一样的，都是得做一次全表扫描，依次取出每个值来做函数计算。

可以重写成where comment_text like 'abc%'。

• 在where子句里，如果在or前的条件列进行了索引，而没有对or后面的条件列进行索引，那么索引会失效。

这个原理其实很好理解。

因为or的含义是满足一个即可，因此只对一个条件列做索引是没有意义的，其他条件列仍然需要通过全表扫描来完成计算。

像and就没有这种顾虑。如果and里只有一个条件列做了索引，那么可以先使用这个条件列快速检索出一个符合该条件的子集，然后再对这个子集，基于另一个条件列，做扫描。这样就可以避免全表扫描了，索引正常生效。

• 当使用like进行模糊查询时，前面不能是%，否则索引失效。

同样很好理解，像是like '%ab'这种，肯定是用不了索引的。

因为索引在匹配的时候，是从首位开始进行匹配，不会是对中间位置进行匹配的。

• 索引列尽量设置成Not Null约束。
• 在使用联合索引时需要注意最左原则。
• 联合索引最多作用于一个范围列，范围列之后的字段无法使用索引。比如说我定义了联合索引(x,y,z)，对于where x=1 and y>2 and z=3，只能使用索引(x,y)

一条SQL语句可以只使用联合索引的一部分，但是比如从联合索引声明时的最左侧字段开始，否则就会失效。

索引使用举例（兴趣篇）

教程里并没有这一节，出于兴趣自己补了一小节。

首先，是建表工作，我建了三张表：

• users表，没有索引；
• users_2表，联合索引(user_id, create_time)
• users_3表，联合索引(create_time, user_id)

其次通过一个存储过程，往users表里插入10w条数据，然后再将users表里的数据insert进其他两个表中，保证三个表的数据是一致的。

建表逻辑和插数逻辑如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_many_user`(IN start INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2017-01-01 00:00:00');
DECLARE date_temp DATETIME;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = date_add(date_temp, interval RAND()*60 second);
INSERT INTO nba.users(user_id, user_name, create_time)
VALUES((start+i), CONCAT('user_',i), date_temp);
UNTIL i = max_num
END REPEAT;
COMMIT;
END


call insert_many_user(100, 100000);

create table users(
	user_id int,
	user_name varchar(100),
	create_time timestamp
);

drop table if exists users_2;
create table users_2(
	user_id int,
	user_name varchar(100),
	create_time timestamp,
	index(user_id, create_time)
);
insert into nba.users_2 select * from nba.users;


create table users_3(
	user_id int,
	user_name varchar(100),
	create_time timestamp,
	index(create_time, user_id)
);
insert into nba.users_3 select * from nba.users;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

接下来我们仿照教程里讲的案例，分别用explain看一下以下SQL的执行过程。

先介绍一下explain里几个重要参数的意思：

rows：表示找到所需的记录大概要读取的行数，是一个大概估计值。

type参数的一些取值的定义：

• type=ref，表示查询使用了非唯一行索引扫描，属于是实打实的利用索引。

• type=index，表示遍历了索引树，虽然跟ALL一样还是全表扫描，但是比ALL快，也算是利用到了索引。

extra的一些取值的定义：

• using filesort：使用外部的索引排序，而不是按照表内的索引顺序进行读取。
• using temporary：使用了临时表来保存中间结果。常见于order by和group by时。
• using index：表示select语句中使用了覆盖索引，即直接从索引中取值，而不需要回表（从磁盘中取数据）；
• using where：使用了where进行过滤
• using index condition：表示查询的列有非索引列，先判断索引的条件，以减少IO。

上一下教程里的几个例子：

set sql_mode=0 ;
-- 未使用索引, type=ALL, extra=Using temporary; Using filesort
explain select a.user_id, count(*) from nba.users a group by a.user_id order by a.create_time;

-- 使用索引，type=index, key=user_id, extra=Using index; Using temporary; Using filesort
explain select a.user_id, count(*) from nba.users_2 a group by a.user_id order by a.create_time;

-- 使用索引，type=index, key=create_time, extra=Using index; Using temporary; Using filesort
explain select a.user_id, count(*) from nba.users_3 a group by a.user_id order by a.create_time;

-- 使用索引，type=index, key=create_time, extra=Using index; Using temporary; Using filesort
explain select a.create_time, count(*) from nba.users_3 a group by a.create_time order by a.user_id;
1
2
3
4
5
6
7
8
9
10
11
12

可以看到，除了第一个表是走了全表扫描之外，其他的表都是间接利用到了索引来做加速（type=index）。

下面我们以第三张表为例，取消order by语句，看查询是否还能利用索引：

-- 使用索引，type=index, key=create_time，extra=Using index; Using temporary
explain select a.user_id, count(*) from nba.users_3 a group by a.user_id;
-- 使用了索引，key=create_time，extra=Using index;
explain select create_time, count(*) from nba.users_3 a group by a.create_time ;
1
2
3
4

可以看到仍然走了索引。

我们尝试保留order by语句，取消group by语句，同时改变select体，看查询是否还会利用索引：

-- 使用索引，type=index, key=create_time，extra=Using index; Using filesort
explain select a.user_id from nba.users_3 a order by a.user_id;
-- 未使用索引, type=ALL，extra=Using filesort
explain select a.user_id, a.create_time ,a.user_name  from nba.users_3 a order by a.user_id;
1
2
3
4

可以看到，第二个SQL没有走索引，这是因为select体里出现了非索引字段user_name，在没有任何where条件的情况下，查询只能走全表扫描，取出所有值。

那我如果加上where条件呢，下面这几个例子是我觉得很经典的：

-- type=ref，key=create_time, extra=Using index condition, rows=1
explain select create_time, user_id, user_name  from nba.users_3 a where a.create_time = 100; -- order by a.user_id  ;
-- type=index, key=create_time, rows=104341, extra=Using where; Using index
explain select create_time, user_id from nba.users_3 a where a.user_id = 100; 
-- type=ALL, rows=104341, extra=Using where
explain select create_time, user_id, user_name  from nba.users_3 a where a.user_id  = 100; 
1
2
3
4
5
6

可以看到，第一个查询是确确实实的使用了联合索引，rows=1，属于是一击命中。

第二个查询，其实不符合索引的最左原则（该表的联合索引是user_id + create_time），但由于select的字段都是索引列，所以查询是直接遍历了索引树来提取需要的数据，并没有回表去读磁盘，所以也算是变相利用了索引。

第三个查询，属于是一点儿都没有利用到索引了。其不满足索引的最左原则，而且又因为它的select体里有一个非索引列字段，也没法只遍历索引树，所以最终在磁盘里全表扫描了。

参考文献

1. 26丨索引的使用原则：如何通过索引让SQL查询效率最大化？