小弟新写了一个功能,自测和测试环境测试都没问题,但在生产环境会出现偶发问题。于是,加班到12点一直排查问题,终于定位了的问题原因:Mysql Limit查询优化导致。现抽象出问题模型及解决方案,分析给大家,避免大家踩坑。 问题场景
新上线一个交易记录导出功能,逻辑很简单:根据查询条件,导出对应的数据。由于数据量比较大,在查询数据库时采用了分页查询,每次查询1000条数据。
自测正常,测试环境正常,上线之后运营反馈导出的数据有重复记录。
原本是以为业务逻辑问题,重新Review了一遍代码,依旧未找到问题原因。最后只好把SQL语句拿出来单独执行,导出数据,对比发现竟然是SQL语句查询结果乱序导致的。 原因分析
查询语句以create_time进行倒序排序,通过limit进行分页,在正常情况下不会出现问题。但当业务并发量比较大,导致create_time存在大量相同值时,再基于limit进行分页,就会出现乱序问题。
出现的场景是:以create_time排序,当create_time存在相同值,通过limit分页,导致分页数据乱序。
比如,查询1000条数据,其中有一批create_time记录值都为”2021-10-28 12:12:12“,当创建时间相同的这些数据,一部分出现在第一页,一部分出现在第二页,在查询第二页的数据时,可能会出现第一页已经查过的数据。
也就是说,数据会来回跳动,一会儿出现在第一页,一会儿出现在第二页,这就导致导出的数据一部分重复,一部分缺失。
查看了Mysql 5.7和8.0的官方文档,描述如下:
If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.
上述内容概述:在使用ORDER BY对列进行排序时,如果对应(ORDER BY的列)列存在多行相同数据,(Mysql)服务器会按照任意顺序返回这些行,并且可能会根据整体执行计划以不同的方式返回。
简单来说就是:ORDER BY查询的数据,如果ORDER BY列存在多行相同数据,Mysql会随机返回。这就会导致虽然使用了排序,但也会发生乱序的状况。 解决方案
针对上述问题,基本的解决思路是:避免ORDER BY列的值出现重复。因此,可以加入其他维度,比如ID等其他排序列。