新特性解读 | MySQL 8.0 对 limit 的优化
作者:杨奇龙
网名“北在南方”,资深 DBA,主要负责数据库架构设计和运维平台开发工作,擅长数据库性能调优、故障诊断。
本文来源:原创投稿
*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。
一、前言
提到 limit 优化,大多数 MySQL DBA 都不会陌生,能想到各种应对策略,比如延迟关联,书签式查询等等,之前我也写过一篇优化的文章:https://mp.weixin.qq.com/s/2nSdHiGsu35Yf3fRStzumQ ,有兴趣的朋友可以复习一下。
二、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";
三、实践出真知
测试环境 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');
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)
经过调整之后,查看执行计划发现优化器选择 id2 索引字段找到记录做过滤,并且使用了ICP 特性,减少物理 io 请求,而不是选择使用主键 id1 遍历索引然后回表查询。
显然 通过人为介入参数调整优化器的行为能带来更好的优化效果。
四、总结
从不同版本的 MySQL 发展轨迹来看 MySQL 的优化器越来越智能 (比如大家期待已久的直方图特性) ,能更多的减少人为干预,提升执行计划的准确性。
have fun with MySQL 8.0 ^_^
相关文章
- Redis与MySQL:两全其美的特性比较(redis和mysql的区别)
- 解决MySQL服务器关闭的实用命令(关闭mysql服务命令)
- 从Oracle到MySQL:一次安全转移的体验(oracle转mysql)
- MySQL数据库:命名华丽又高效(mysql的数据库名)
- 处理MySQL优化:优化几万条数据处理(mysql几万条数据)
- MySQL字符转换为数字的技巧(mysql字符转换数字)
- MySQL数据文件导入指南(mysql导入文件)
- 配置文件MySQL 配置文件 .cnf 优化指南(mysql.cnf)
- MySQL配置文件:.cnf 的使用(mysql.cnf)
- MySQL联合查询实现效率突飞猛进(联合查询mysql)
- 提升MySQL数据表性能的索引优化技巧(mysql数据表索引)
- 在线提升MySQL 性能——使用MySQL在线优化工具(mysql在线优化工具)
- MySQL查询优化:提升TPS(mysql查询tps)
- 深入了解MySQL的安装目录,助力数据库管理和维护(mysql的安装目录)
- 性能优化MySQL分区:实现系统性能优化(mysql分区实现)
- MySQL命令行执行简介(mysql执行命令)
- MySQL数据库如何实现ID重新排序(mysql中id重新排序)
- MySQL数据库性能优化的两个阶段体检方法(mysql两阶段体检)
- 在CMD中如何安装MySQL(cmd里面安装mysql)
- AMH管理面板修改MySQL文件实现数据库优化(amh修改mysql文件)
- 如何从零开始学习MySQL(0基础学习mysql)
- MySQL页面存储结构优化MySQL性能的最佳实践(mysql一页存储结构)
- MySQL 数据库不执行事务前滚操作(mysql不执行前滚)
- MySQL使用SUM函数实现一行相加(mysql一行相加)