索引与慢sql剖析

一、使用explain语句查看索引使用情况

CREATE TABLE `test_user` (

`user_id` int(9) NOT NULL AUTO_INCREMENT,

`age` int(4) DEFAULT '0',

`phone` varchar(16) DEFAULT '',

`score` int(4) DEFAULT '0',

PRIMARY KEY (`user_id`),

KEY `idx_score` (`score`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建表testUser，并建立user_id - 主键id、age - 年龄、phone - 手机号、score - 分数字段，并给score建立普通索引。

在表中插入三条语句（1,16,1358,85）、(2,15,1236,88)、(3,16,1387,89)

· EXPLAIN select * from test_user - 查看索引使用情况

运行结果如上图，发现未使用索引，并且sql扫描行数为3行，为全表扫描。说明，不使用索引的情况下数据库会进行全表扫描来查询你所需要的数据。

· EXPLAIN select * from test_user where user_id = 2

运行结果如上图，发现使用了主键索引，并且sql扫描行数为1行。说明在使用索引的情况下，数据库会有效减少扫描行数。

· EXPLAIN select * from test_user where score = 88

运行结果如上图，发现使用了普通索引，并且扫描行数也为1行，有效减少了扫描行数。

· EXPLAIN select * from test_user where user_id != 0

运行结果如上图，虽然使用了主键索引，但是扫描行数为4行，属于全表扫描。

· 结论

虽然加索引可以帮助你减少数据库的扫描行数，但是一些不当的sql语句即使命中了索引，也会导致全表扫描，发生慢sql。索引和慢sql的产生不代表有必然的联系。

在数据库配置中存在long_query_time参数，用于设置sql执行时间，当执行时间超过了设置的该值，则说明执行的sql为慢sql，会被记录到慢sql日志中，在生产环境也有可能会被kill掉。long_query_time参数的默认时间为10s。

二、索引结构

· 主键索引B+树结构

当插入语句时，索引也会被保存起来。索引中也使用了B+树结构来增加查询性能，如上图画了一个简单版本的B+树用来存放主键索引。

当执行select * from test_user where user_id = 2语句时，会根据主键索引定位到id等于2的数据，并且扫描索引树后扫描行数只有1行。

· 普通索引B+数结构

执行select * from test_user where score = 88语句时，根据普通索引idx_score定位到score=88。

执行select * from test_user where score != 0语句时，根据普通索引执行过程，会定位到第一个score不为0的叶节点，然后像右开始扫描索引树，虽然这里用到了索引，但是仍扫描了整个索引树。

三、索引过程中的回表和下推

· 回表

上图为执行select * from test_user where score = 88语句时回表的过程。根据普通索引，会定位到score=88的叶子节点，得到score=88的主键id为2，并根据主键id去主键索引中获取该主键id为2的数据信息并返回。

回表的基本过程就是这样，但是实际上在sql执行中，最耗费时间的就是回表，假设我的这张学生表有几千万的数据，并且分数为88的有1000w人，那么上述的查询sql就会回表1000w次，导致慢sql。

所以在我们平时的sql使用中，也要减少回表的次数，比如可以使用分页等来减少查询行数，减少回表次数。

· 下推

增加用户表的name - 姓名字段，并创建联合索引（name+age）

CREATE TABLE `test_user` (

`user_id` int(9) NOT NULL AUTO_INCREMENT,

`age` int(4) DEFAULT '0',

`phone` varchar(16) DEFAULT '',

`score` int(4) DEFAULT '0',

`name` varchar(16) DEFAULT '',

PRIMARY KEY (`user_id`),

KEY `idx_score` (`score`) USING BTREE,

KEY `idx_name_and_age` (`name`,`age`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

联合索引的索引结构B+树也类似，如下图：

现在需要查询出姓为张，并且年龄为15岁的用户。sql语句为:

select * from test_user where name like '张%' and age = 15;

在mySql5.5之前的执行过程如下：

首先会在联合索引中找到第一个姓张的叶子节点，查询出主键id为1，然后回表去主键索引中找到主键id为1的用户数据。判断该主键为1的用户的年龄是否为15，如果是则作为结果集的一行返回，如果不是则查询下一个数据。

如果姓张的用户有1000w数据，那么他们不管年龄是不是15，都需要经过1000w次的回表。

在mySql5.6版本之后，引入了索引下推的优化，提升了联合索引查询的性能。执行过程如下：

首先在联合索引树上找到第一个姓张的叶子节点，并判断该联合索引中的age的值是否为15，如果是则取出id，进行回表操作，并作为结果集返回。如果不是则通过当前叶子节点进行向右遍历进行下一个查询。

如果姓张的用户有1000w，年龄为15的有200人。那么mysql5.5则需要经过1000w次回表查询，而mysql5.6只需要经过200次回表查询。有了下推的优化概念，大大的减少了回表次数，提升了查询性能。

四、总结

· 使用索引了的sql语句也会产生慢查询，慢查询与索引是否使用没有必然联系。

· 上文中导致慢查询发生的情况全表扫描、全索引扫描、频繁回表的开销，如果发生慢sql，优化思路可以考虑如何减少这三种情况的发生，提高索引的过滤性。