记录一次奇怪的sql查询,查询数据量较多的最后几个数据的分页查询

本文链接：https://blog.youkuaiyun.com/XHgroup/article/details/138188847

文章探讨了一个关于MySQL查询的问题，当使用JSON_CONTAINS函数进行分页查询时，数据量不足导致索引失效，查询时间显著增加。作者尝试了explain和去除索引的测试，发现数据量对查询策略有重大影响。解决方案是针对小数据量情况，强制使用非索引查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

问题复现

表结构:

(与该sql查询有关的核心字段,还有很多其他字段,这里不在展示,请不要在意json,因为一些原因改成这个了,并且我尝试过使用其他正常字段查询,依旧会出现问题.)

create table media
(
    id                   varchar(255)  not null
        primary key,
    dataset_id           json          null,
    created_at           datetime      null,
    -- 省略其他无关字段,本身还有很多字段
);

数据库数据量:

大概有100w左右,虽然dataset_id为json,但为了方便排查,目前假定就只有两类1和2,并且它们的数据量差距也极大,dataset_id为2的数据就只有910条.其它全为dataset_id为1的.

sql语句:

select id from media where JSON_CONTAINS(dataset_id, '2', '$') order by created_at desc limit 905,10;

为created_at字段添加了索引,由于2的数据量不够,这种分页查询会导致不能查满10条.由于索引的原因,反而导致查询极其缓慢,耗时13s,这是不正常的,因为即便全表扫描也不可能这么慢

排查

1.首先尝试使用explain,结果如下

发现确实会走索引,但在正常查询(即在分页时数据量是够的时候)时又是正常的.sql语句与耗时如下

select id from media where JSON_CONTAINS(dataset_id, '2', '$') order by created_at desc limit 900,10;

耗时45ms,可以发现查询是很快的(但是本质上没有这么快,因为这是我最新上传的一批数据,它的创建日期很新,但即便排除这个问题,查询速度依然是比较快的).

2.接着尝试把索引去掉,耗时如下

此时走全表扫描,耗时3s多.

3.尝试查询另一个有大量数据的类似分页查询,数据量88w,sql如下

select id from media where JSON_CONTAINS(dataset_id, '1', '$') order by created_at desc,limit 886960,10;

耗时3s

使用 explain发现它是没有走索引的(应该是大数据量下,且分页为最后面的一些数据,mysql认为走全表扫描更快的原因)

至此,基本确定就是因为数据量不够,导致分页查询走索引时有异常,但并不知道为什么.

解决方案

其实没有解决方案.正如我上面说的,我排查到了出问题的地方,但不知道为什么.最后我的解决方案是在数据量不大的时候,强制走非索引的sql.如果有大佬知道原因的话,请告诉我,非常感谢