从一次主键查询乱序来看Mysql查询优化原理

前言

        Mysql作为目前相对流行的关系型数据库，以各种演化形式存在于目前的it公司中，以B+树为基础的存储结构能够带来很高的查询效率，但是也随着单表存储行数递增以及存储空间增大，受制于B+树层数以及缓存页空间，查询效率会急剧下降。因此数据归档便是一个很正常技术需求，归档一般是需要全表扫描来对数据进行筛选，今天想记录的场景便是在进行数据归档时的一些sql查询主键乱序问题。

正文

背景

        下面是我们的goods_info表结构(还创建了一个goods_id的二级索引)，我们要对这张表进行全表扫描归档，使用的sql是 select id from goods_info where id > {index} limit {num}，然后每次的index都是上一次id列表中的最大值，多次循环使用后最终查询不到值完整整个归档查询。

列	类型
id	bigint
store_id	bigint
goods_id	bigint
goods_name	varchar
create_time	timestamp
update_time	timestamp

        然后在测试环境使用的时候就发现一个问题，先查询，再进行筛选归档的时候经常会漏掉一些数据，导致一些数据无法正常归档。最开始的时候以为是归档条件的配置问题，于是排查错了方向，最后才发现是sql语句查询出来的id列表不是顺序的，导致我们中间会漏掉一些数据。

我们认为的首次id查询列表：

id
1
3
5
8
14

真正的第一次查询id列表：

id
56
3
5
1
22

        返回结果不仅乱序，而且导致我们的归档规则中会漏掉一些数据。

问题定位

        我们第一次执行的sql是这样的select id from goods_info where id > -1 limit 100，index赋予为-1就是为了要从第一条数据开始（因为我们表中的id都大于0）。在我对于Mysql的主观理解中，where条件中附带了某个字段筛选，就一定会用该字段、或者该字段为前缀的联合索引来进行查询优化，以避免全表扫描的情况。这条sql的where中有id就一定会用主键索引来查询，那么熟知B+树的底层叶子节点结构的伙伴也知道，主键索引的底层排序一定是有序的，怎么可能会出现上面那种情况？

        经过对这条Sql的Explain后，我们发现了问题，这条sql居然使用了goods_id->id的二级索引，导致了查询结果的乱序。

分析原因

        为何Mysql会选择使用goods_id->id的二级索引呢，问题主要出自我们的这条sql中。select id from goods_info where id > -1 limit 100。

1. 首先查询的数据只有id，二级索引能够完成索引覆盖，满足需求；
2. 其次虽然使用where条件，但是mysql中有记录主键索引的最小值，最小值大于0，所有的数据都满足条件不再具有约束力，Mysql就选择性无视了这个where条件；
3. 最后limit的限制是需要批量查询出具体数据。

        因此站在上面的角度分析过后，Mysql在查询优化时就选择了占用空间最小的二级索引goods_id->id来遍历首次的sql数据，返回对应结果，这样是没有任何问题的。

解决问题

        既然选错了索引，我们就强制让这条sql走主键索引即可。

1. order by id（order by在查询优化时首选索引）
2. force {index}（强制索引，不必多说）
3. 3.select多一些字段，让二级索引无法利用索引覆盖的优势。

结语

        通过这次问题定位，在以后的使用中都要尽量改变对于一些Mysql查询优化中的刻板印象，多学习分析底层原因。