200w数据分析+实测深入理解sql索引使用

mysql 深入理解索引使用

简单聊聊下面这条简单sql 的执行与优化
SQL: select * from user where age>20 and weight<70 order by add_time limit 500000 100;

环境 mysql 5.6 2核2g 阿里云服务器
user表：age,name,weight add_time desc 200w数据 ,单条数据0.5k左右
age>20 数据量100w
weight<70 数据量110w
age>20 && weight <70 数据量80w

sql可能执行路径理论分析(分析只是理论,实际情况根据后面的测试判断):
1: 无索引情况
执行流程：全表扫描 就是加载整个表的数据 根据条件逐条判断是否符合，最后将符合条件的数据根据时间排序取100条
时间消耗:读取整个表+扫描200w数据+排序80w数据

2:添加单列索引 age 或者weight
执行流程：选择age 或者weiht索引 从索引去掉不符合的数据剩下的数据回表查找另一个字段后筛选 然后根据时间排序 取出100条数据
时间消耗:回表100w数据+排序100w数据

3使用 add_time 索引
执行流程：根据add_time 索引 全部回表 查找到数据 进行筛选最后直接取出 100条数据 （使用索引排序）
时间消耗：回表200w数据+扫描200w 数据

4使用联合索引 age,weight
执行流程：从索引上直接找到age>20的数据后，扫描索引里面所有weight 筛选出大于70的数据， 回表取数据排序后取出100条数据
时间消耗 回表80w数据+排序80w筛选数据

5:创建 add_time,age,weight 联合索引 ，并且sql 修改成 select * from user where add_time>0 and age>20 and weight<70 order by add_time limit 500000 100;(假如add_time>0)
执行流程：这样会直接扫描整个联合索引，根据age,weight 筛选数据 最后直接取出100条数据 进行回表
时间消耗:扫描200w索引数据+回表100数据

时间消耗：
1: 读取整个表+扫描200w数据+排序100w数据
2：回表100w数据+排序100w数据
3：回表200w数据+扫描200w数据
4：回表80w数据+排序80w筛选数据
5：扫描200w数据+回表100数据

上面看起来好像 4跟5 是时间最少的 尤其是5，看看实际执行情况

实操数据下面实际操作不同环境可能有所差别
1：数据库

2：数据量 200w
根据上面情况分别测试
1：无所引，耗时 8.3s

2单列索引：age,消耗时间8.3 跟无所引一样，查看explain 并没有使用索引，强制使用索引 耗时16s左右（这就懵逼了）

3单列索引：add_time,消耗时间8.3 跟无所引一样，查看explain 不能使用索引！！

4：使用联合索引 age,weight,消耗时间8.3 跟无所引一样，查看explain 有可用索引并没有使用,跟第2一样

5：使用add_time,age,weight,联合索引 并修改查询语句，消耗时间10.8s,有使用索引,Using index condition 使用索引下推

测试分析
A:情况3不能使用索引,连possible key 都没有
说明相同数据mysql 回表的速度肯定比排序来的慢,起码大部分情况是这样的
B:情况2,4 有可用索引,但是实际没有使用索引,测试强制使用索引也确实比全表扫描慢
再次证明大量数据回表速度是很慢的,还不如直接读取全表筛选

通过Profiling查看情况1跟情况5的具体时间消耗
情况1无索引情况

情况5用联合索引(add_time,age,weight)

这里就有差异了
没用索引主要时间花在 Creating sort index 就是排序;
有索引的情况主要Sending data 简单的说就是mysql 服务层跟 innodb 存储引擎的数据交互,主要是回表时间;
这里不太明白使用了索引下推后为何还需要大量回表,我的理解应该只需要回表100条数据,但时间用了8s多这显然不对

既然情况5 回表是最消耗时间的,那就考虑只取一个ID字段,做到索引覆盖,则不需要回表
测试SQL SELECT id FROM sent_test_u WHERE age>20 and weight<70 ORDER BY add_time LIMIT 500000,100

执行时间结果为0.4s也是符合预期

最终优化 建立索引(add_tiem,age,weight),首先查找id 然后根据id 查找最后数据,
SELECT * FROM sent_test_u WHERE id in (SELECT id FROM (SELECT id FROM sent_test_u WHERE add_time>0 and age>20 and weight<70 ORDER BY add_time LIMIT 500000,100)as t)=

这样总耗时0.6s