如果您只需要结果集中指定数量的行,LIMIT
请在查询中使用子句,而不是获取整个结果集并丢弃额外的数据。
MySQL 有时会优化有子句和无
子句的查询:
LIMIT
row_count
HAVING
如果您使用 只选择几行
LIMIT
,MySQL 在某些情况下会使用索引,而通常情况下它更愿意进行全表扫描。如果结合使用 ,MySQL 会在找到排序结果的第一行后立即停止排序 ,而不是对整个结果进行排序。如果使用索引进行排序,速度会非常快。如果必须进行文件排序,则选择所有与不带子句的查询匹配的行,并且在找到第一行之前对其中的大部分或全部进行排序 。找到初始行后,MySQL 不会对结果集中的任何剩余部分进行排序。
LIMIT
row_count
ORDER BY
row_count
LIMIT
row_count
这种行为的一种表现是,
ORDER BY
有和没有的查询LIMIT
可能会以不同的顺序返回行,如本节后面所述。如果与 结合使用 ,MySQL 会在找到唯一行 后立即停止。
LIMIT
row_count
DISTINCT
row_count
在某些情况下,
GROUP BY
可以通过按顺序读取索引(或对索引进行排序)来解决问题,然后计算摘要直到索引值发生变化。在这种情况下,不计算任何不必要 的值。LIMIT
row_count
GROUP BY
一旦 MySQL 向客户端发送了所需数量的行,它就会中止查询,除非您使用
SQL_CALC_FOUND_ROWS
. 在这种情况下,可以使用 检索行数SELECT FOUND_ROWS()
。请参阅 第 12.16 节,“信息功能”。LIMIT 0
快速返回一个空集。这对于检查查询的有效性很有用。它还可以用于在使用使结果集元数据可用的 MySQL API 的应用程序中获取结果列的类型。配合 mysql客户端程序,可以使用--column-type-info
选项显示结果列类型。如果服务器使用临时表来解析查询,它会使用该子句来计算需要多少空间。
LIMIT
row_count
如果索引不用于
ORDER BY
但LIMIT
也存在子句,优化器可以避免使用合并文件并使用内存中filesort
操作对内存中的行进行排序。
如果多行在列中具有相同的值ORDER
BY
,服务器可以自由地以任何顺序返回这些行,并且可能会根据整体执行计划以不同的方式返回。换句话说,这些行的排序顺序相对于未排序的列是不确定的。
影响执行计划的一个因素是
LIMIT
,因此ORDER BY
有和没有的查询LIMIT
可能会以不同的顺序返回行。考虑这个查询,它按列排序但关于和
列是category
不确定的:
id
rating
Press CTRL+C to copymysql> 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
值内的行顺序。例如,这是一个有效的查询结果:
Press CTRL+C to copymysql> 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
顺序显示:
Press CTRL+C to copymysql> 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
和LIMIT
子句的查询,优化器会在默认情况下尝试选择有序索引,这样做会加快查询执行速度。在 MySQL 8.0.21 之前,没有办法覆盖此行为,即使在使用其他一些优化可能更快的情况下也是如此。optimizer_switch
从 MySQL 8.0.21 开始,可以通过将系统变量的
prefer_ordering_index
标志设置为 来关闭此优化
off
。
示例:首先,我们创建并填充一个表t
,如下所示:
Press CTRL+C to copy# 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
标志是否已启用:
Press CTRL+C to copymysql> SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%'; +------------------------------------------------------+ | @@optimizer_switch LIKE '%prefer_ordering_index=on%' | +------------------------------------------------------+ | 1 | +------------------------------------------------------+
由于以下查询有一个LIMIT
子句,我们希望它尽可能使用有序索引。在这种情况下,正如我们从
EXPLAIN
输出中看到的那样,它使用了表的主键。
Press CTRL+C to copymysql> 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
)和文件排序:
Press CTRL+C to copymysql> 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