MySQL order by 排序使用的字段有重复值导致排序随机的情况

灰太狼 2021-09-20 23:02 239阅读 0赞

生产环境一个列表页的分页查询sql:

SELECT *
    FROM ap_clue a LEFT JOIN ap_dealer d ON a.intention_dealer_id = d.dealer_id
    ORDER BY a.date_create DESC limit 90,5;

根据ap\_clue表中的date\_create字段倒叙排列，但是ap\_clue表中很多记录date\_create字段是相同的，此时的分页结果是MySQL根据date\_create随机返回的。(生产环境MySQL版本：5.7.26-log)

查了一下MySQL官网，对这种情况做了介绍：

[https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html][https_dev.mysql.com_doc_refman_5.7_en_limit-optimization.html]

*“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列中有多个行具有相同的值，则服务器可以自由以任何顺序返回这些行，并且根据整体执行计划的不同，返回值可能会有所不同。 换句话说，这些行的排序顺序相对于无序列是不确定的。*

基于这个，就知道为什么会出现这个问题了。这个SQL主要问题是会可能导致分页查询查不出一些记录，如果date\_create字段相同的记录多于分页每页记录数，就会导致一些记录上一页出现了，下一页又查出了（MySQL随机返回导致的），而一些数据可能就查不出来了。怎么解决这个问题，想必大家页想到了，MySQL官网上也给出了一种方案：

*“If it is important to ensure the same row order with and without `LIMIT`, include additional columns in the `ORDER BY` clause to make the order deterministic. For example, if `id` values are unique, you can make rows for a given `category` value appear in `id` order by sorting like this:”*

在ORDER BY子句中包括其他列以使顺序确定，比如可以使用主键id等。上面的SQL可以修改下：

SELECT *
    FROM ap_clue a LEFT JOIN ap_dealer d ON a.intention_dealer_id = d.dealer_id
    ORDER BY a.date_create DESC ,a.clue_id limit 90,5;

以上，问题得到解决。

我在测试环境造了一些date\_create相同的记录，分页查询，一样的sql查询，但是却没有出现随机返回的情况。使用explain命令查看各自的执行计划：

1、生产环境MySQL版本信息：5.7.26-log

上面那个SQL的执行计划：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDc2MzQ1_size_16_color_FFFFFF_t_70][]

2、测试环境MySQL版本信息：5.7.26-29-log

上面那个SQL的执行计划：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDc2MzQ1_size_16_color_FFFFFF_t_70 1][]

两个环境的表结构是一致的，但是执行计划确实不一样，生产环境的还用到了临时表（Using temporary），为什么会出现这样的差异，一时没找到答案，后续还要再研究下。

[https_dev.mysql.com_doc_refman_5.7_en_limit-optimization.html]: https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDc2MzQ1_size_16_color_FFFFFF_t_70]: /images/20210920/bd8f11b6538f4f9383aa6cd8927217fc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDc2MzQ1_size_16_color_FFFFFF_t_70 1]: /images/20210920/c9cc756f34724d818bbe8e6e6062983c.png