Mysql索引：表在哪些情况下需要加索引，应该加哪种索引，你懂吗
王者小哆啦2019-09-14 09:58
Mysql为什么添加索引可以提高查询速度，看完这篇就够了
本章适合学习完成上一章内容的同学学习。在前面两篇文章中我们了解到了mysql索引的重要性，那么我们的数据表到底在哪种情况下才适合添加索引呢？索引有好多种我们应该添加哪种呢？今天我们就来一起聊聊这个问题。
千学不如一看，千看不如一练
首先我们先创建一张test_table数据表
创建一个存储过程插入256万测试数据
执行5次：insert into test_table (a,b,c,d) select a,b,c,d from test_table;
目前比较常见需要创建索引的场景有
数据检索时在条件字段添加索引聚合函数对聚合字段添加索引对排序字段添加索引为了防止非聚簇索引回表查询添加索引关联查询在关联字段添加索引等下面针对这几点我们一一来分析。
NO.1 索引对数据检索的影响
题外话：索引一定要注意最左侧匹配原则，我会有专门一章为大家讲解。请关注我。
用上图创建的表做测试，首先把没有索引的字段 d 作为条件进行查询：
select * from test_table where d = 90000; /* 执行结果 32 rows in set (1.41 sec) */
然后我们在使用索引字段作为查询条件
select * from test_table where a = 90000; /* 执行结果 32 rows in set (0.00 sec) */
接下来我们分析一下两条sql执行计划
我们发现前者 type 字段为 ALL，后者 type 字段为 ref，显然后者性能更好（explain 的 type 字段解释如果忘记了，可以复习之前发布的explain详解章节）
rows 这个字段前者是 5107746，而后者是 32，有索引的情况扫描行数大大降低。
因此建议数据检索时，在条件字段添加索引。
NO.2 索引对聚合函数是否有提速
假如我们要在test_table中利用max函数找出无索引字段d的最大值
执行结果我们可以看到：用时1.21秒
接下来我们在查询test_table中带索引a字段的最大值
执行时间为 0.00 秒，表示执行时间不超过 10 毫秒
从这个测试可以得出一个结果，mysql的索引可以有效地为聚合函数提高效率。
重点讲解在MySQL 5.7.18 之后版本的 count(*) 特点：
从 MySQL 5.7.18 开始，通过遍历最小的可用二级索引来处理 count(*) 语句，如果不存在二级索引，则扫描聚簇索引。原因是：InnoDB 二级索引树的叶子节点上存放的是主键，而主键索引树的叶子节点上存放的是整行数据，所以二级索引树比主键索引树小。因此优化器基于成本的考虑，优先选择的是二级索引。（这个面试也常问）
所以索引在5.7以后对count(*)也具有优化作用。
NO.3 索引对排序有哪些影响
如果对这一点不清楚的可以查看我的其他文章：如何添加合适的索引优化order by排序
要点强调：
如果对单个字段排序，则可以在这个排序字段上添加索引来优化排序语句。如果是多个字段排序，可以在多个排序字段上添加联合索引来优化排序语句。如果是先等值查询再排序，可以通过在条件字段和排序字段添加联合索引来优化排序语句。NO.4 避免查询回表
假设我们要取出表中a（带索引）,d（不带索引）两列。
select a,d from test_table where a=90000;
这条sql可以走 a 字段的索引，但是在学了Mysql为什么添加索引可以提高查询速度，看完这篇就够了后，我们知道了辅助索引的结构，如果通过辅助索引来寻找数据，InnoDB 存储引擎会遍历辅助索引树查找到对应记录的主键，然后通过主键索引回表去找对应的行数据。
但是，如果条件字段和需要查询的字段有联合索引的话，其实回表这一步就省了，因为联合索引中包含了这两个字段的值。像这种索引就已经覆盖了我们的查询需求的场景，我们称为：覆盖索引。比如下面这条 SQL：
select b,c from test_table where b=90000;
可直接通过联合索引 idx_b_c 找到 b、c 的值（联合索引详细讲解将放在下一章）。
所以可以通过添加覆盖索引让 SQL 不需要回表，从而减少树的搜索次数，让查询更快地返回结果。
总结
本章主要讲解了一下几点内容
数据检索时在条件字段添加索引聚合函数对聚合字段添加索引对排序字段添加索引为了防止回表添加索引最后我给大家留一个思考题吧。欢迎大家在评论区积极讨论，我会把大家的问题在下章做出解答！
我们在前面学习了mysql在什么情况下添加什么索引，那么在多表联合查询的时候，索引如和建立呢？多表关联查询又有哪些优化技巧呢？
感谢你们的观看和支持，也欢迎你吧这篇文章分享给更多的朋友一起阅读。
下章透露：讲解普通索引和唯一索引有哪些区别。记得提前预习哦。
举报/反馈
0
0
收藏
分享
