当前位置：首页 > 数据库 > MySQL教程 > MySQL哪些情况需要添加索引？ > 正文

MySQL哪些情况需要添加索引？

来源：这里教程网 时间：2026-03-01 17:30:33 作者：

来源：MySQL数据库联盟

这篇文章+执行计划，都掌握的话，我们可以优化80%的SQL了。

这一篇文章，就来聊一下，MySQL在哪些情况下，需要添加索引（根据小编以往的工作经验，绝大多数SQL，只要添加合适的索引，基本就完成了优化）。
特别是最后一种场景，如果没添加合适的索引，很可能长时间获取不到结果。
我们就一一分析这些需要创建索引的场景：
1 数据检索
首先创建测试表并写入数据：

use martin; /* 使用martin这个database */
drop table if exists index_test_04; /* 如果表index_test_04存在则删除表index_test_04 */
CREATE TABLE `index_test_04` (
`id` int NOT NULL AUTO_INCREMENT,
`a` int DEFAULT NULL,
`b` int DEFAULT NULL,
`c` int DEFAULT NULL,
`d` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`),
KEY `idx_b_c` (`b`,`c`)
) ENGINE=InnoDB  CHARSET=utf8mb4;


drop procedure if exists insert_index_test_04; /* 如果存在存储过程insert_index_test_04，则删除 */
delimiter ;;
create procedure insert_index_test_04() /* 创建存储过程insert_index_test_04 */
begin
declare i int; /* 声明变量i */
set i=1; /* 设置i的初始值为1 */
while(i<=100000)do /* 对满足i<=100000的值进行while循环 */
insert into index_test_04(a,b,c,d) values(i,i,i,i); /* 写入表index_test_04中a、b两个字段，值都为i当前的值 */
set i=i+1; /* 将i加1 */
end while;
end;;
delimiter ; /* 创建批量写入100000条数据到表index_test_04的存储过程insert_index_test_04 */


call insert_index_test_04(); /* 运行存储过程insert_index_test_04 */


insert into index_test_04(a,b,c,d) select a,b,c,d from index_test_04;
insert into index_test_04(a,b,c,d) select a,b,c,d from index_test_04;


/*  把index_test_04的数据量扩大到40万 */


create table index_test_05 like index_test_04;
insert into index_test_05 select * from index_test_04;

‍

用上面的表 index_test_04 做测试，首先把没有索引的字段 d 作为条件进行查询：

select * from index_test_04 where d = 1000;

图片描述发现查询时间需要2.39秒。
再把有索引的字段 a 作为条件进行查询

select * from index_test_04 where a = 1000;

发现查询时间为 0.08 sec，比前面的条件字段没索引快多了。
再对比两条 SQL 的执行计划：

explain select * from index_test_04 where d = 1000;explain select * from index_test_04 where a = 1000;

前者 type 字段为 ALL，表示全表扫描；
后者 type 字段为 ref，表示基于普通索引的等值查询，或者表间等值连接。
前者key这一部分也是null，表示没走索引；
后者key这一部分是idx_a，表示使用了索引idx_a，也就是a字段上的索引。
前者rows为1600000，表示扫描了1600000万行；
后者rows 是16，很明显，有索引的情况扫描行数大大降低。
当然，关于MySQL执行计划分析，可以查看：一文搞懂MySQL执行计划。
因此建议数据检索时，在条件字段添加索引。
2 聚合函数
求无索引字段的最大值
在测试表 index_test_04 中，如果要求出无索引字段 d 的最大值，SQL 如下：

select max(d) from index_test_04;

求有索引字段的最大值
再看下求有索引的字段 a 的最大值：

select max(a) from index_test_04;

执行时间为 0.01 秒，相比对没有索引的字段 d 求最大值（花费0.32秒），显然索引能提升 max() 函数的效率，同理也能提升 min() 函数的效率。
提高select(*)的执行效率
另外，某张表有合适的二级索引，执行select(*)也会被优化。
 从 MySQL 5.7.18 开始，通过遍历最小的可用二级索引来处理 count(*) 语句，如果不存在二级索引，则扫描聚簇索引。
原因是：InnoDB 二级索引树的叶子节点上存放的是主键，而主键索引树的叶子节点上存放的是整行数据，所以二级索引树比主键索引树小。因此优化器基于成本的考虑，优先选择的是二级索引。
我们可以执行count(*)试一下

select count(*) from index_test_04;
再来看下执行计划

explain select count(*) from index_test_04;

可以看到，使用了索引idx_a。
因此索引对聚合函数 count(*) 也有优化作用。
3 排序
排序字段有索引和无索引的查询时间对比
如果对单个字段排序，则可以在这个排序字段上添加索引来优化排序语句；
我们来对比一下，排序字段有索引和没索引查询时间的区别。
首先是排序字段有索引：

select * from index_test_04 order by a limit 10 ;

然后是排序字段没索引

select * from index_test_04 order by d limit 10 ;

排序字段有索引和无索引的执行计划对比
再来看下两条语句的执行计划

explain select * from index_test_04 order by a limit 10;


explain select * from index_test_04 order by d limit 10;

可以看到，前者type是index，表示全索引扫描，key里面提示了走a字段的索引，扫描行数是10行，因为返回了10行数据。
后者type是all，表示全表扫描，rows直接扫描了160万行，并且使用了filesort，表示使用了外部排序而不是索引排序。数据较小时从内存排序，否则需要在磁盘完成排序。
多个字段一起排序的优化
如果是多个字段排序，可以在多个排序字段上添加联合索引来优化排序语句；
比如b和c字段上有联合索引，对这两个字段进行排序，可以这样写SQL：

select * from index_test_04 order by b,c limit 10;
来查看一下执行计划：

explain select * from index_test_04 order by b,c limit 10;

key这一列为idx_b_c，表示走了b和c的联合索引；
因为b和c两个字段都是可以为null的int类型，根据我们上周写的：一文搞懂MySQL执行计划，讲到了各种类型的字段对应的key_len。可以知道，b和c两个字段的key_len都是5。
而这个执行计划的key_len为10，说明完整走了b和c两个字段的联合索引。
4 避免回表
什么是回表？
如果通过普通索引查询数据时，并不能通过索引树获取到全部需要的字段，需要回到主键索引树中查找所需要的其他字段，这个回到主键索引树查询的过程，就称为回表。
回表的例子
比如下面这条 SQL：

explain select a,d from index_test_04 where a=900;

看执行计划，可以走 a 字段的索引，如果通过辅助索引来寻找数据，InnoDB 存储引擎会遍历辅助索引树查找到对应记录的主键，然后通过主键索引回表去找对应的行数据。
注意观察Extra这个字段，是NULL，等会会对比覆盖索引的执行计划。
避免回表的例子
但是，如果条件字段和需要查询的字段有联合索引的话，其实回表这一步就省了，因为联合索引中包含了这两个字段的值。像这种索引就已经覆盖了我们的查询需求的场景，我们称为：覆盖索引。比如下面这条 SQL：

explain select b,c from index_test_04 where b=1000;

注意观察Extra这个字段，是Using index，我们在执行计划那一节的第6点，也提到了，表示使用覆盖索引。
也就是可以直接通过联合索引 idx_b_c 找到 c 的值，不需要回表，从而减少树的搜索次数，让查询更快地返回结果。
5 关联查询
关联字段有索引的执行计划
比如index_test_04和index_test_05，如果关联字段有索引，就是类似下面这条语句

explain select * from index_test_04 tb1  inner join index_test_05 tb2 on tb1.a = tb2.a;

因为驱动表tb1需要每条记录都跟被驱动表进行关联，所以驱动表全表扫描避免不了的（rows为399360）。
而每次取出驱动表的关联字段在被驱动表查找对应的数据时，都会通过索引在被驱动表找到对应的记录，效率自然也高。
关联字段无索引的执行计划
再把关联字段换成d字段，模拟关联字段没索引的查询：

explain select * from index_test_04 tb1  inner join index_test_05 tb2 on tb1.d = tb2.d;

因为关联字段没索引，所以每次取出驱动表的关联字段在被驱动表查找对应的数据时，都会对被驱动表做一次全表扫描，那总共扫描的行数大概是40万 x 40万=1600亿行，效率会非常低，很可能就长时间返回不了结果。

编辑推荐：

相关推荐

最新软件资讯

MySQL哪些情况需要添加索引？
MySQL哪些情况需要添加索引？

热文推荐

天极热推