MySQL 分页查询的坑

近日我行自研配置中心出现了一个较为诡异的问题。表现在配置列表页面中,第一页和第二页出现了同名的项目,而有项目未被显示在列表页面中。当用户误认为该配置创建失败而重新创建配置时,系统提示“该配置已存在”。如下图所示:

第一页,可见存在workflowConf.properties
第二页,同样存在workflowConf.properties

由图可知,两页均存在workflowConf.properties配置项。

ftpConf.properties未出现在列表项,但新建时提示已存在

而ftpConf.properties项并未出现在列表页中,但新增配置时显示该项已存在。

问题分析:

出现该问题第一反应是前端显示有问题。通过Postman调用该前端接口发现其查询结果并无问题。继续检查数据库,使用SQL查询发现库内数据也无异常。

共8条数据

用开发者工具显示该页面的网络请求信息,猛然想起该页面向后台请求数据时,使用了分页查询接口。难道问题出现在分页上?

我行自研配置中心使用了Tesla提供的分页插件,该分页插件在执行分页时会拦截SQL语句,并根据数据库方言拼接分页子句。对于我行自研配置中心使用的MySQL数据库而言,分页一般使用limit子句完成。

尝试在数据库中直接使用分页SQL查询数据。我行自研配置中心配置列表页面的查询条件可简化为如下SQL

select * from tacc_app_config where app_id = ‘xxx’ and env_id = ‘xxx’ and status= ‘normal’ order by create_time desc

则已知查询第一页的SQL语句应为

select * from tacc_app_config where app_id = ‘xxx’ and env_id = ‘xxx’ and status= ‘normal’ order by create_time desc limit 0, 5

查询得到的结果如下。

第一页,包括workflowConf.properties

查询第二页的SQL语句应为

select * from tacc_app_config where app_id = ‘xxx’ and env_id = ‘xxx’ and status= ‘normal’ order by createtime desc limit 5, 5

查询得到的结果如下。

第二页,同样包括workflowConf.properties

此时问题已被复现,在第一页和第二页中,均出现了workflowConf.properties条目,对比前文中未分页的SQL查询结果,ftpConf.properties条目始终未出现在分页查询结果中。问题被成功定位。

问题原因:

考虑到我行自研配置中心近期仅进行了一项操作,即将数据库由MySQL 5.5升级至8.0。考虑该问题由数据库升级所引发。在升级前的老库中使用同样的分页SQL查询验证,发现分页结果并无问题。

第一页
第二页,无重复项

猜想问题由高版本MySQL更改了分页查询机制所致。遇事不决问谷歌,一番搜索后果然有所收获。从MySQL官方文档中可知,MySQL在涉及排序时一般会有几种算法:

  • order by不使用索引时,使用排序算法进行排序。
  • 若排序内容内存可全部容纳,仅在内存中使用快速排序。
  • 若排序内容内部不可全部容纳,分批次将排序后内容放入文件,对文件进行归并排序。
  • 包含limit语句时,使用堆排序优化排序过程。

由此问题原因便很容易得出。由于我行自研配置中心的分页查询同时使用了order by和limit,且order by字段create_time又未在索引上,故使用堆排序优化结果。而堆排序本身是不稳定排序,在排序结果不唯一时,多次排序的结果可能不同。观察被排序数据发现,其中有四项的create_time值相同,则排序结果显然不唯一。故在分页查询时,易出现结果重复现象,所以导致了workflowConf.properties同时出现在两页。我行自研配置中心又恰好无法更改每页的条目数,故导致ftpConf.properties始终未被显示。而使用堆排序优化结果自MySQL 5.6版本开始。故升级后才出现该现象。

解决方案:

  1. 排序字段增加索引。MySQL在存在索引时,使用索引进行排序,问题解决。
  2. 保证排序字段唯一性,从而得到稳定的排序结果。例如将主键加入到排序项中。

更改先前第一页查询SQL为

select * from tacc_app_config where app_id = ‘xxx’ and env_id = ‘xxx’ and status= ‘normal’ order by create_time, id desc limit 0, 5

结果如下

排序项添加主键后第一页结果,排序具有唯一性

相应的,第二页查询SQL为

select * from tacc_app_config where app_id = ‘xxx’ and env_id = ‘xxx’ and status= ‘normal’ order by create_time, id desc limit 5, 5

结果如下

排序项添加主键后第二页结果

可见排序结果是稳定的,并未出现重复项目,问题解决。

后记

其实我最觉得不可思议的是,这样一个看起来很容器跌坑的问题,在我咨询DBA时得到的信息是我是第一个反馈该问题的。后来想了想大概是因为,其他系统在分页时,每页的条目数大概率是可调整的,就算遇到了重复项或者无法被显示出的项,大概率更改一下每页的条目数就可以解决,所以没有人深究这个问题吧。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注