本文转载自微信公众号《程序新视野》，作者为二哥。转载本文请联系程序新视界公众号。小弟新写了一个功能，自测和测试环境测试都没有问题，但是生产环境偶尔会出问题。于是，我加班到12点排查问题，最终定位到问题的原因：MysqlLimit查询优化。现在把问题模型和解决方案抽象出来，给大家分析一下，避免大家踩坑。问题场景中，新上线了一个交易记录导出功能。逻辑很简单：根据查询条件导出相应的数据。由于数据量比较大，在查询数据库时采用分页查询，每次查询1000条数据。自测正常，测试环境正常，上线后运行反馈导出的数据有重复记录。本来以为是业务逻辑的问题，又review了代码，还是没有找到问题的原因。最后只好把SQL语句拿出来单独执行，导出数据，对比了一下，发现是SQL语句的查询结果乱序造成的。原因分析查询语句按create_time倒序排序，按limit分页。在正常情况下，不会有问题。但是当业务的并发量比较大的时候，导致create_time有大量相同的值，然后根据limit进行分页，就会出现乱序的问题。出现的场景是：按create_time排序，当create_time有相同值时，按limit进行分页，导致分页数据乱序。比如查询1000条数据时，一批create_time记录值都是“2021-10-2812:12:12”，当创建时间相同的数据时，有的出现在首页，有的出现在首页出现在第二页，查询第二页的数据时，可能会出现第一页已经查过的数据。也就是说，数据会来回跳动，一会儿出现在第一页，一会儿出现在第二页，导致导出的数据有的重复，有的缺失。查了Mysql5.7和8.0的官方文档，是这样描述的：如果多行在ORDERBY列中有相同的值，服务器可以随意返回这些行，顺序不限，也可能根据不同的顺序返回关于总体执行计划。换句话说，这些行的排序顺序相对于未排序的列是不确定的。以上内容概述：在使用ORDERBY对列进行排序时，如果对应的（ORDERBY列）列中存在多行相同的数据，（Mysql）服务器会以任意顺序返回这些行，并且可能返回取决于整体执行计划。简单来说：对于ORDERBY查询的数据，如果ORDERBY列中有多行相同的数据，Mysql会随机返回。这将导致乱序的情况，即使使用了排序。解决方案针对以上问题，基本的解决方案是避免ORDERBY列中的值重复。所以可以加入其他维度，比如ID等排序列。select*fromtb_orderorderbycreate_time,iddesc;这样当create_time相同的时候，会按照id排序，id一定是不同的，就不会再出现上面的问题了。扩展知识其实上面的内容在Mysql官网上已经说的很清楚了，并且还给出了例子。下面简单总结一下官网的内容和例子。限制查询优化如果我们只查询一个结果集的一部分，那么不查询所有的数据，然后丢弃不需要的数据，而是通过限制条件进行限制。不使用having条件时，Mysql可能会优化limit条件：如果只查询几条数据，建议使用limit，这样Mysql可能会用到索引，通常Mysql是全表扫描；如果limitrow_count和order结合使用，Mysql会在找到第一个row_count结果集后立即停止排序，而不是对整个结果集进行排序。如果此时基于索引进行操作，速度会更快。如果需要进行文件排序，则在找到row_count结果集之前对部分或所有匹配结果进行排序。但是当找到row_count结果后，剩下的就不排序了。这个特性的一个体现是，在有和没有我们前面提到的限制条件下进行查询时，返回结果的顺序可能会有所不同。如果limitrow_count和distinct一起使用，Mysql会在找到row_count结果集中唯一的一行后立即停止。在某些情况下，groupby可以通过顺序读取索引（或对索引进行排序），然后计算摘要直到索引发生变化来实现。在这种情况下，limitrow_count不会计算任何不必要的分组值。一旦MySQL向客户端发送了所需数量的行，它就会中止查询，除非使用SQL_CALC_FOUND_ROWS。在这种情况下，可以使用SELECTFOUND_ROWS()检索行计数。LIMIT0会很快返回一个空集合，常用于检查SQL的合法性。也可用于获取应用程序中结果集的类型。在Mysql客户端中，可以使用--column-type-in??fo来显示结果列类型。如果使用临时表来解析查询，Mysql会使用limitrow_count来计算需要多少空间。如果orderby没有使用索引并且有限制条件，优化器可能会避免使用合并后的文件，而使用内存文件排序操作对内存中的行进行排序。了解了limit的一些特性之后，我们再回到本文的重点，limitrow_count和orderby特性的结合。limit和orderby的结合上面第二项提到，limitrow_count和orderby的结合所呈现的一个特点就是返回结果的顺序是不确定的。影响执行计划的一个因素就是limit，所以同一条查询语句，有limit和没有limit执行，返回结果的顺序可能不一样。下面的例子是根据category列进行排序查询，但是id和rating是不确定的：mysql>SELECT*FROMratingsORDERBYcategory;+----+----------+--------+|id|category|rating|+----+--------+------+|1|1|4.5||5|1|3.2||3|2|3.7||4|2|3.5||6|2|3.5||2|3|5.0||7|3|2.7|+----+----------+--------+当查询语句包含limit时，可能会影响同类别值的数据：mysql>SELECT*FROMratingsORDERBYcategoryLIMIT5;+----+----------+------+|id|类别|评级|+----+--------+--------+|1|1|4.5||5|1|3.2||4|2|3.5||3|2|3.7||6|2|3.5|+----+--------+--------+id为3和4的结果的位置发生了变化。在实践中，维护查询结果的顺序往往非常重要。这时候就需要引入其他的列来保证结果的顺序。上面的例子引入id后，查询语句和结果如下：mysql>SELECT*FROMratingsORDERBYcategory,id;+----+---------+--------+|编号|类别|评级|+----+------------+------+|1|1|4.5||5|1|3.2||3|2|3.7||4|2|3.5||6|2|3.5||2|3|5.0||7|3|2.7|+----+----------+--------+mysql>SELECT*FROMratingsORDERBYcategory,idLIMIT5;+----+--------+--------+|id|category|rating|+----+----------+--------+|1|1|4.5||5|1|3.2||3|2|3.7||4|2|3.5||6|2|3.5|+----+---------+--------+可以看出，当加入了id列的排序后，即使category是一样的，不会有乱序的问题。这与我们原来的解决方案是一致的。总结通过在实践中偶尔犯的一个坑，我讲了Mysql的limit查询语句的优化，同时提供了一个解决方案，既满足了业务需求，又避免了业务逻辑错误。很多朋友都在使用orderby和limit语句来查询，但是如果你不知道Mysql的这些优化特性，你可能已经入坑了，只是数据量不触发展示而已。如果本文对您有帮助，请关注一波，追踪分享更多实用干货。Mysql官方文档：https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html
