MYSQL8.0.21对limit 的优化

1.MYSQL8.0对limit 的优化 
对于limit N 带有group by ,order by 的SQL语句(order by 和group by 的字段有索引可以使用)
MYSQL优化器会尽可能选择利用现有索引的有序性，减少排序。但是有时候会出现，SQL执行计划 
选择 order by id 的索引而导致全表扫描，而不是利用where条件中的索引查找过滤数据。
MYSQL8.0.21版本之前，没有参数控制这种行为。MYSQL8.0.21之后提供了一个参数  
prefer_ordering_index ,通过设置 optimizer_switch 来开启或者关闭。
set optimizer_switch='prefer_ordering_index=off';
set optimizer_switch='prefer_ordering_index=on';

2.测试 

create table t(id1 bigint not null primary key auto_increment,id2 bigint not null,c1 varchar(50) not null,c2 varchar(50) not null,
index i(id2,c1));
insert into t (id2,c1,c2) values(1,'a','sdfs'),(2,'bb','fewf'),(3,'sdg0','wefwef'),(14,'db','tgewt'),(20,'eastg','weri');

select @@optimizer_switch like '%prefer_ordering_index=on%';
mysql> select @@optimizer_switch like '%prefer_ordering_index=on%';
+------------------------------------------------------+
| @@optimizer_switch like '%prefer_ordering_index=on%' |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+
1 row in set (0.00 sec)
#默认是开启的。
查询非索引字段， id2上有索引，order by id1,explain查看执行计划 type_index 说明使用索引扫描，
使用using index condition 过来结果集。这个是优化器的自以为最优的选择，但是实际上遇到数据集合比较大的表，
该执行计划不是最优，反而导致慢查询。
mysql> explain select c2 from t where id2>8 order by id1 asc limit 2;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | i             | i    | 8       | NULL |    2 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

#关闭参数 。
set optimizer_switch='prefer_ordering_index=off';
explain select c2 from t where id2>8 order by id1 asc limit 2;
mysql> explain select c2 from t where id2>8 order by id1 asc limit 2;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | i             | i    | 8       | NULL |    2 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
#没有变化。不同的版本可能表现不同，我的当前版本是MySQL 8.0.34