8.2.19 LIMIT 查询优化

独上西楼影三人

已于 2023-01-04 20:02:02 修改

阅读量914

点赞数

分类专栏： # 第 8 章优化《MySQL 8.0 参考手册》中文翻译文章标签： mysql 数据库 dba

于 2022-08-25 10:49:33 首次发布

本文链接：https://blog.csdn.net/wudi53433927/article/details/126519685

版权

《MySQL 8.0 参考手册》中文翻译同时被 2 个专栏收录

40 篇文章 9 订阅

订阅专栏

第 8 章优化

13 篇文章 0 订阅

订阅专栏

翻译原文：MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.19 LIMIT Query Optimization

如果你只需要返回结果集中指定数量的记录行，在查询中使用LIMIT子句，而不是提取整个结果集，然后丢弃多余数据。

MySQL有时这样优化一个包含LIMIT row_count子句、不包含HAVING子句的的查询：

如果你只用LIMIT选择很少的行，MySQL在某些情况下使用索引，而通常情况下它会做一次全表扫描。
组合使用LIMIT row_count和ORDER BY时,MySQL在找到排序结果集中第一批row_count行后立刻终止排序，而不是排序整个结果集。使用索引进行排序会很快。如果必须做filesort，会查询出所有查询去掉LIMIT子句时查到的所有行，然后排序它们直到找到第一批row_count行立刻终止排序。

这种操作的一种表现是是否包含LIMIT子句的ORDER BY子句会以不同的顺序返回行，如本文中后续所述。

组合使用LIMIT row_count和DISTINCT时，MySQL一找到row_count唯一行就立刻终止排序。
某些情况下，GROUP BY可能被解析为有序读取索引（或在索引上做排序），然后直到索引值改变时计算总计值。此时，LIMIT row_count不计算任何非必要的GROUP BY值。
一旦将需要的行传给客户端，MySQL立刻终止查询，除非你在使用SQL_CALC_FOUND_ROWS 。在那种情况下，由SELECT FOUND_ROWS()返回行数。详见Section 12.16, “Information Functions”.
LIMIT 0迅速返回一个空集。这对于检验一个查询是否有效十分有用。这也用于应用程序通过支持结果元数据的MySQL API获取返回字段的类型。使用 mysql 客户端程序，你可以使用 --column-type-info 选项来显示结果字段类型。
如果服务器用临时表来处理查询，它会使用LIMIT row_count 子句来计算需要多少空间。
如果ORDER BY没有使用索引且存在LIMIT子句，优化器可能避免使用合并文件，而是使用in-memory filesort 操作在内存中排序行。

如果在ORDER BY字段上的多行有相同的值，服务器可能以任何顺序返回这些行，可能依总体执行计划而不同。换言之，对非有序字段这些行的顺序是非确定性的。

影响执行计划的一个因素是LIMIT，所以一个ORDER BY查询会因是否包含LIMIT而以不同顺序返回行。

此处为个人整理后的表述，与原文不同，因为原文中例子太长又过于简单，所以并没有写入本文。

所以在进行排序时应尽量保证ORDER BY的字段可以唯一确定一行，这样才能保证结果的顺序为确定性的。

永远记住一点，要想保证结果按指定字段有序排列，必须以指定字段排序，即指定字段必须出现在ORDER BY子句中。

对于一个使用ORDER BY或GROUP BY，和LIMIT子句的查询，当优化器发现使用一个有序索引会加速查询执行时，会尝试使用它。MySQL 8.0.21之前，无法修改这个行为，即使某些情况下使用其他优化手段更快。从MySQL 8.0.21开始，可以设置 optimizer_switch 系统变量的 prefer_ordering_index开关为off来关闭该优化。

# 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 (它包含WHERE 子句使用的id2字段)和一次filesort :

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

参阅 Section 8.9.2, “Switchable Optimizations”.