【MySQL】官网学习 order by limit 中的乱序问题

前言

对于同一句sql语句：order by … limit …，可能会出现不同的返回结果。通过官网能了解到，至少在5.7.32版本中这是存在的，需要我们编写正确的sql语句避免返回结果的乱序问题。前置知识：

• 索引的顺序特性
• 文件排序 （filesort）
• 排序的稳定性
  简单来说，排序的稳定可以这么表达：
  1. cheng[age: 18, class: A]，han[age: 18, class: A] 固定这个先后组合且视为已按age排序
  2. 在不同的数据集中都保持这个先后组合，即
     liiu[age: 1class: A]....... cheng[age: 18, class: A]，han[age: 18, class: A] ........yang[age: 99, class: B]
  3. 不论数据集大小如何，对age排序后的返回值中 cheng[age: 18, class: A]，han[age: 18, class: A] 不会产生颠倒的情况

官网指明的学习路线

1. limit 优化
2. order by 优化

点明问题

官网原文：

One manifestation of this behavior is that an ORDER BY query with and without LIMIT may return rows in different order, as described later in this section.

limit 与其他语句结合使用的情况

使用 limit 的优化思想是，希望查询优化器不要扫描自己认为没必要返回的值，如果能够借助索引能确认row_count 的结果集，则可以直接返回。反之可能要用到文件排序甚至是临时表。官网举得例子要满足的条件：没有having参与的sql语句。
针对不同的情况，查询优化器做出的反应如下：

• 只有少量数据
  查询优化器认为全表扫描更快

• limit 与 order by 结合使用
  不论是利用索引完成了 order by 任务，还是使用了文件排序，当 row_count 能够被确认时，就不再遍历其他数据了。简单来说：

  1. 使用了索引，如果可以利用索引顺序的特性，那么row_count 个数据被找到后就会立即返回结果，速度很快。
  2. 如果使用了文件排序，可能会比使用索引对更多的数据进行排序，但是收集完 row_count 个数据后，也会立即返回

• limit 与 distinct 结合使用
  distinct 只是让limit 变得更加严格了，但是现象是一致的，但是收集完 row_count 个数据后，也会立即返回。

• limit 与 group by 结合使用

  • group by xxx,yyy 的遍历过程：
    1. 对数据先排序
    2. 遍历数据行 hash（xxx,yyy）[注：这里使用伪代码]
    3. 感知到 hash (xxx,yyy) 变化就能产生多个分组
  • limit 语句选举出 row_count 个结果集，同样也只关注 xxx,yyy 列

乱序问题根源

官网原文：

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.

MySQL 出于效率考虑，order by col1, col2 语句用于排序时，并不负责关心 col3, col4 … 的值。也就是同样的 col1, col2 的行数据，在limit row_count 的结果集中不负责 col3, col4 … 的排序（且返回值不确定），这是一个很自然的事情。侧面也反映了MySQL 会使用不稳定的排序算法。（网上查的用的是快排，目前没在官方文档上明确看到这个说法）
官网的例子加以说明：

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| 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 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

解决问题

• 思路：让返回值稳定，前提是让Mysql能找到一个多次查询依旧稳定的序列。
  1. 选一个业务需求的排序列 如：last_update_time
  2. 如果last_update_time由于批量更新出现重复值，则使用 id 加以固定排序
• 结论：通过在排序字段后面追加id字段，既可以保证返回值稳定

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+