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

本文介绍了在使用MySQL进行数据归档时遇到的主键查询乱序问题,分析了MySQL选择二级索引而非主键索引的原因,并提出了解决方案。问题源于全表扫描时,由于where条件限制不强且使用limit,MySQL选择了更小的二级索引。解决方法包括使用orderby、force index或增加查询字段以避免二级索引的索引覆盖。
摘要由CSDN通过智能技术生成

前言

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

正文

背景

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

类型
idbigint
store_idbigint
goods_idbigint
goods_namevarchar
create_timetimestamp
update_timetimestamp

        然后在测试环境使用的时候就发现一个问题,先查询,再进行筛选归档的时候经常会漏掉一些数据,导致一些数据无法正常归档。最开始的时候以为是归档条件的配置问题,于是排查错了方向,最后才发现是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查询优化中的刻板印象,多学习分析底层原因。

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值