新特性解读 | MySQL 8.0 对 limit 的优化

一、前言

二、MySQL 8.0 对limit 的改进

对于 limit N 带有 group by ，order by 的 SQL 语句 (order by 和 group by 的字段有索引可以使用)，MySQL 优化器会尽可能选择利用现有索引的有序性，减少排序--这看起来是 SQL 的执行计划的最优解，但是实际上效果其实是南辕北辙，相信很多 DBA 遇到的相关案例中 sql 执行计划选择 order by id 的索引进而导致全表扫描，而不是利用 where 条件中的索引查找过滤数据。MySQL 8.0.21 版本之前，并没有什么参数来控制这种行为，但是自 MySQL 8.0.21 之后提供一个优化器参数 prefer_ordering_index
，通过设置  optimizer_switch
来开启或者关闭该特性  。比如:

SET  optimizer_switch  = "prefer_ordering_index=off";

SET  optimizer_switch = "prefer_ordering_index=on";

复制

三、实践出真知

构造测试数据

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','xfvs'),(2,'bbbb','xfvs'),(3,'cdddd','xfvs'),(4,'dfdf','xfvs'),(12,'bbbb','xfvs'),(23,'cdddd','xfvs'),(14,'dfdf','xfvs'),
(11,'bbbb','xfvs'),(13,'cdddd','xfvs'),(44,'dfdf','xfvs'),(31,'bbbb','xfvs'),(33,'cdddd','xfvs'),(34,'dfdf','xfvs');

复制

3.1 默认开启参数

mysql  (test) >  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 where 过滤结果集。这个是优化器的自以为的最优选择，但是实际上遇到数据集合比较大的表，该执行计划就不是最优解，反而导致慢查。

mysql  (test) > explain select c2 from t where id2>8 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: 69.23
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

复制

3.2 关闭该参数

mysql  (test) > SET optimizer_switch = "prefer_ordering_index=off";

mysql  (test) > explain select c2 from t where id2>8 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: 9
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)


复制

四、总结

本文关键字：#8.0 新特性# #MySQL# #优化#

更多关于 SQLE 的信息和交流，请加入官方QQ交流群：637150065...