8.2.1.19-LIMIT查询优化

如果你只需要一个结果集中的特定数量的行，你可以在查询中添加limit条件，而不是获取整个结果集，然后丢弃那些多余的数据。

mysql有时会优化带limit且没有having条件的查询：

如果你使用limit，只是想获取一少部分记录，mysql有时会使用索引。而通常它更喜欢做全表扫描。
如果既有limit row_count也有order by，mysql在查找到钱row_count条记录后会立即停止，而不是对整个结果集进行排序。如果必须做文件排序，所有符合查询条件的记录都被选中，其中大部分甚至是全部记录会被排序，直到前row_count条记录被找到。找到结果集后，mysql不会对剩下的数据进行排序。

此问题的证据之一是一个有limit和没有limit的语句，返回的顺序可能不同，下面将会讨论。

如果既有limit row_count和distinct，mysql 在找到前row_count个唯一值后就会停止查找。
在某些情况下，我们可以通过按顺序读取索引来解决group by的问题，然后直到索引值变化后再次查询统计信息。在这种情况下，limit row_count不会机选任何不必要group by值。
mysql按要求将特定数量的记录返回给客户端后，会立即终止查询。除非，你使用了SQL_CALC_FOUND_ROWS。如果是，，可以使用SELECT FOUND_ROWS()获取对应数量的记录。请查阅章节12.16 ，“Information Functions”。
limit 0 会快速返回一个空结果集。这在检查一个查询的有效性时有用。这也可以被用来获取结果集列的类型信息。利用mysql客户端程序，你可以添加–columen-type-info选项来展示结果列的类型。
如果服务器使用临时表来解决查询，它会使用limit row_count因子来评估需要多少空间。
如果order by没有用到索引，但是又有limit 条件，优化器可能会为了避免文件合并而在内存中对记录进行排序。

如果order by相关列某个特征值有多条记录，数据库服务器可能会以任意顺序返回这些记录，这可能会因执行计划的不同而不同。换句话说，这些记录的顺序是不确定的。

limit是影响执行计划的因子之一，索引一个有limit和没有limit的order by，可能会以不同的顺序返回记录。看一下下面的这个查询，用category排序，但因为有id和rating字段，结果集是无序的：

select * from ratings order by category;

包含limit条件可能会影响每个category对应记录的顺序。例如，下面是一个可行的结果：

select * from ratings order by category limit 5;

在每一个例子中，记录都根据order by排序，这是符合sql标准要求的。

不管有没有limit条件，确保顺序一致都很重要，可以在order by 条件中添加更多的列来保证这一点。例如，如果id列值是唯一的，你可以在category后面添加id列：

select * from ratings order by category, id;

select * from ratings order by category, id limit 5;

如果一个查询使用了order by或group by ，且有limit条件，优化器会尝试选择一个有序索引，这可能会加快查询执行速度。在MySQL 8.0.21版以前，没有办法跳过，即使其它优化措施可能更快。从8.0.21开始，可以通过将系统变量optimizer_switch的prefer_ordering_index标志更改为off来关闭这个优化。

例如：我们先创建一张一张表t： create and pipulate a table t;

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 1 (id2,c1)
);

然后插入一些语句。

确认prefer_ordering_index flag 是开启的：

select @@optimizer_switch like '%prefer_ordering_index=on%';

因为下面的查询有limit条件，如果可能的话，优化器会使用有序索引。在这个例子中，我们从explain输出中可以看到，它使用了primary key。

explain select c2 from t where i2>3 order by id1 asc limit 2\G

现在我们关闭prefer_ordering_index标志，重新执行这个查询。这次我们看到，它使用了索引i(包含了where条件中的id2)，并使用了文件排序：

set optimizer_switch = "prefer_ordering_index=off";

explain select c2 from t where id2>3 order by id1 asc limit 2\G

可以查阅章节8.9.2，“Switchable Optimizations”。