前言

• MySQL8.0中在 ORDER BY 语句后使用和不使用 Limit 时为什么查询出来的数据顺序不一样？
• Limit 0 有什么作用？

如果从结果集中只需要指定数量的行，请LIMIT在查询中使用子句，而不是获取整个结果集并丢弃多余的数据。

MySQL有时会优化带有 LIMIT row_count 的语句

如果仅使用来选择几行 LIMIT，则在通常情况下，MySQL倾向于使用全表扫描，因此在某些情况下会使用索引。

• 如果与结合使用 ，则MySQL在找到排序结果的第一行后即停止排序 ，而不是对整个结果进行排序。如果通过使用索引进行排序，这将非常快。如果必须执行文件排序，则在找到第一个之前，选择所有与查询匹配的不带子句的行，并对其中的大多数或全部进行排序 。找到初始行后，MySQL不会对结果集的其余部分进行排序。LIMIT row_count 此行为的一种体现是，ORDER BY 带有和不带有查询的查询 LIMIT 可能以不同的顺序返回行
• 如果与结合使用，MySQL将在 找到唯一行后立即停止。 LIMIT row_count DISTINCT row_count
• 在某些情况下，GROUP BY可以通过按顺序读取索引（或对索引进行排序），然后计算汇总直到索引值更改来解决a。在这种情况下，不计算任何不必要的 值。 LIMIT row_count GROUP BY
• MySQL一旦向客户端发送了所需的行数，它将立即终止查询，除非您正在使用 SQL_CALC_FOUND_ROWS。在这种情况下，可以使用检索行数SELECT FOUND_ROWS()。
• LIMIT 0 快速返回一个空集。这对于检查查询的有效性很有用。它还可以用于获取使用MySQL API的应用程序中结果列元数据的类型的结果列的类型。在 mysql客户端程序中，您可以使用该 --column-type-info选项显示结果列类型。
• 如果服务器使用临时表来解析查询，则它将使用该子句来计算所需的空间。 LIMIT row_count
• 如果未使用索引，ORDER BY 但LIMIT也存在子句，则优化器可能能够避免使用合并文件，并使用内存中filesort操作对内存中的行进行排序 。

如果多行在列中具有相同的值ORDER BY，则服务器可以自由以任何顺序返回这些行，并且根据整体执行计划，这样做的方式可能有所不同。换句话说，相对于无序列，那些行的排序顺序是不确定的。

影响执行计划的一个因素是 LIMIT，因此ORDER BY 带有和不带有查询的查询LIMIT可能以不同顺序返回行。考虑以下查询，该查询按category列排序，但对于id和 rating列不确定：

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 |
+----+----------+--------+

包含LIMIT可能会影响每个category值中的行顺序。例如，这是一个有效的查询结果：

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 |
+----+----------+--------+

在每种情况下，行均按ORDER BY列排序，这是SQL标准所需的全部。

如果重要的是要确保使用和不使用相同的行顺序，则LIMIT在ORDER BY子句中包括其他列以使顺序确定。例如，如果id值是唯一的，则可以通过如下排序使给定category值的行 按id顺序显示 ：

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

对于带有ORDER BY or GROUP BY 和 and LIMIT 子句的查询，优化器会在默认情况下尝试选择有序索引，因为这样做会加快查询的执行速度。在MySQL 8.0.21之前，没有办法重写此行为，即使在使用某些其他优化可能更快的情况下也是如此。从MySQL 8.0.21开始，可以通过将optimizer_switch系统变量的 prefer_ordering_index标志设置为来关闭此优化 off。

示例：首先，我们创建并填充一个表格t，如下所示：

# Create and populate a table t:

mysql> CREATE TABLE t (
    ->     id1 BIGINT NOT NULL,
    ->     id2 BIGINT NOT NULL,
    ->     c1 VARCHAR(50) NOT NULL,
    ->     c2 VARCHAR(50) NOT NULL,
    ->  PRIMARY KEY (id1),
    ->  INDEX i (id2, c1)
    -> );

# [Insert some rows into table t - not shown]

验证该 prefer_ordering_index标志已启用：

mysql> SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';
+------------------------------------------------------+
| @@optimizer_switch LIKE '%prefer_ordering_index=on%' |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+

由于以下查询具有一个LIMIT 子句，因此，我们希望它尽可能使用有序索引。在这种情况下，正如我们从EXPLAIN输出中看到的那样 ，它使用表的主键。

mysql> EXPLAIN SELECT c2 FROM t
    ->     WHERE id2 > 3
    ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: index
possible_keys: i
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2
     filtered: 70.00
        Extra: Using where

现在，我们禁用该 prefer_ordering_index标志，然后重新运行相同的查询；这次使用索引 i（包括id2该WHERE子句中使用的 列 ）和一个文件排序：

mysql> SET optimizer_switch = "prefer_ordering_index=off";

mysql> EXPLAIN SELECT c2 FROM t
    ->     WHERE id2 > 3
    ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 8
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using index condition; Using filesort

参考：https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html