MySQL 8.0 对 limit 的优化技巧
提到 limit 优化,大多数 MySQL DBA 都不会陌生,能想到各种应对策略,比如延迟关联,书签式查询等等,之前我也写过一篇优化的文章:# ,有兴趣的朋友可以复习一下。
二、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 来开启或者关闭该特性 。 比如:
三、实践出真知测试环境 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 );
+ +
| @@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 关闭该参数
经过调整之后,查看执行计划发现优化器选择 id2 索引字段找到记录做过滤,并且使用了ICP 特性,减少物理 io 请求,而不是选择使用主键 id1 遍历索引然后回表查询。
显然 通过人为介入参数调整优化器的行为能带来更好的优化效果。
从不同版本的 MySQL 发展轨迹来看 MySQL 的优化器越来越智能 (比如大家期待已久的直方图特性) ,能更多的减少人为干预,提升执行计划的准确性。
到此这篇关于MySQL 8.0 对 limit 的优化的文章就介绍到这了,更多相关MySQL 8.0 limit优化内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 MySQL 8.0 对 limit 的优化技巧
相关文章
- MySQL左右值使用技巧(mysql左右值)
- Linux下操作MySQL数据库的技巧(linux操作mysql)
- MySQL事务处理:实现安全的并发操作(mysql事务并发)
- MySQL中修改数据名称的方法(mysql修改数据名称)
- 优化MySQL库表优化:加快数据库性能运行(mysql库表)
- 如何在MySQL中查询用户信息(mysql怎么查询用户)
- MySQL跨库查询:解锁数据库的新方式(mysql跨库查询)
- 大而不倒:优化MySQL表以防止过大(mysql表过大)
- MySQL 双机热备:为数据不间断保驾护航(mysql的双机热备)
- MySQL聚簇索引:构建优化性能的基础(mysql聚簇索引创建)
- 存储MySQL:通过二进制存储数据实现最佳性能(mysql二进制数据)
- 如何搭建独立的MySQL数据库来优化网站性能?(独立mysql数据库)
- 学习MySQL技巧:如何使用分组来获取数据(mysql分组取数据)
- MySQL字段保留字:应该知晓的基本规则(mysql字段保留字)
- MySQL技巧:灵活使用大小写表名(mysql表大小写)
- MySQL表锁行锁优化:最佳实践(mysql表锁行锁)
- MySQL 优雅查找重复记录(mysql 查看重复记录)
- MySQL数据库:普遍用到的还是不多?(mysql数据库用的多吗)
- MySQL 慢查询优化之路 如何进行分析(mysql慢查询分析)
- 深陷MySQL何处寻时空真意(mysql 时间不对)
- MySQL中QPS的意义及如何优化(mysql中QPS)
- 优化数据管理MySQL在企业数据存储中的应用场景(mysql一试用场景)
- MySQL与Yorn开源关系数据库选手之间的较量(mysql yorn)
- 如何正确安装MySQL并配置设置(mysql下完后)