MYSQL百万数据查询优化

1. 查询现象

---

在查询第100000条记录之后的10条时，耗费时间：0.227s

在查询第200000条记录之后的10条时，耗费时间：0.251s

在查询第500000条记录之后的10条时，耗费时间：0.916s

在查询第800000条记录之后的10条时，耗费时间：1.639s

在查询第1000000条记录之后的10条时，耗费时间：2.038s

以上叫做深度分页，即查询偏移量过大的场景，这会导致查询性能较低

2. 优化建议

---

范围查询

当Id保持连续性时，可以通过对id进行范围分页，这种优化方式限制比较大，一般项目的ID没办法保证完全连续

SELECT * FROM confirm_order where id >1772450156306894849 LIMIT 10

当在查询1000000条数据之后的数据时，根据id进行范围分页，耗费时间为0s

子查询
由于我们需要第1000000条数据之后的10条数据，那么我们可以先找到第1000000条数据对应的id，根据这个id再去过滤并limit
这种方法只适用于id是正序的

SELECT * FROM confirm_order where id >(SELECT id FROM confirm_order ORDER BY id LIMIT 1000000,1) LIMIT 10

当在查询1000000条数据之后的数据时，根据id进行范围分页，耗费时间为1.632s，时间上比直接查询会快一点

延迟关联
延迟关联的优化思路和子查询的优化思路差不多，都是把条件转移到主键索引树上，减少回表的次数。不同的是，延迟关联使用了INNER JOIN（内连接）

SELECT c.* FROM confirm_order c,
(SELECT id FROM confirm_order LIMIT 1000000,10) d
WHERE c.id=d.id;

另外一种写法;(SELECT id FROM confirm_order LIMIT 1000000,10) d 相当于子查询

SELECT c.* FROM confirm_order c
INNER JOIN (SELECT id FROM confirm_order LIMIT 1000000,10) d
WHERE c.id=d.id;

当在查询1000000条数据之后的数据时，耗费时间为0.496s

覆盖索引
索引中已经保存了所有要查询的字段

覆盖索引的好处：

• 避免InnoDB表进行索引的二次查询，也就是回表操作：InnoDB是以聚集索引的顺寻来存储的，对于InnoDB来说，二级索引在叶子节点中所保存的是行的主键信息，也就是说，在第一次索引时，我们找到了包含了主键信息的叶子节点，但是在该叶子节点中只保存了主键信息，所以需要根据主键信息进行第二次索引，这种索引也叫做二级索引/辅助索引，所以当索引中已经有了我们需要查询的值时，就不需要进行第二次回表查询，减少了IO操作，提高了查询效率。
• 可以把随机IO变成顺序IO加快查询效率: 由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 IO 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。

SELECT id,member_id,train_code FROM confirm_order ORDER BY id LIMIT 1000000,10

当在查询1000000条数据之后的数据时，耗费时间为1.61s，当索引中属性越少，查询时间越少，不过差距并不是很大