关于mysql:新特性解读-MySQL-80-对-limit-的优化

3次阅读

共计 2371 个字符，预计需要花费 6 分钟才能阅读完成。

作者：杨奇龙

网名“北在北方”，资深 DBA，次要负责数据库架构设计和运维平台开发工作，善于数据库性能调优、故障诊断。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

提到 limit 优化，大多数 MySQL DBA 都不会生疏，能想到各种应答策略，比方提早关联，书签式查问等等，之前我也写过一篇优化的文章：https://mp.weixin.qq.com/s/2n…，有趣味的敌人能够温习一下。

对于 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";

测试环境 MySQL 社区版 8.0.30

结构测试数据

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');

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)

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)

通过调整之后，查看执行打算发现优化器抉择 id2 索引字段找到记录做过滤，并且应用了 ICP 个性，缩小物理 io 申请，而不是抉择应用主键 id1 遍历索引而后回表查问。

显然通过人为染指参数调整优化器的行为能带来更好的优化成果。

从不同版本的 MySQL 倒退轨迹来看 MySQL 的优化器越来越智能 (比方大家期待已久的直方图个性)，能更多的缩小人为干涉，晋升执行打算的准确性。

have fun with MySQL 8.0 ^_^

正文完