MySQL - 分页查询优化的两个案例解析

简介: MySQL - 分页查询优化的两个案例解析

20200808090311433.png

生猛干货

带你搞定MySQL实战,轻松对应海量业务处理及高并发需求,从容应对大场面试


Table

还是我们那个老表

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';


有个主键索引和二级联合索引 idx_name_age_position


日常场景


任何一个系统,分页查询都是必不可少的吧 ,MySQL中的分页查询 就是 limit呗 ,你有没有感觉到 越往后翻页越慢 ,常见的SQL如下

mysql> select * from employees limit 10000,10;


就是从 employees 中取出从 10001 行开始的 10 行记录。


MySQL是怎么处理这个SQL的呢?


先读取 10010 条记录,然后抛弃前 10000 条记录,仅保留10 条想要的数据 。 可想而知,如果要查询一张大表比较靠后的数据,这效率是非常低的。


那有没有优化的办法呢?


Case1 根据自增且连续的主键排序的分页查询


我们先来看一个 【根据自增且连续主键排序的分页查询】的优化案例

select * from employees limit 10000, 10

从第1万条数据开始,获取10条数据


20200808153928790.png


我们来看下执行计划

mysql> explain select * from employees limit 10000, 10 ;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100175 |      100 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set
mysql> 


因为没有添加单独 order by字段,所以表示通过主键排序 。 执行计划显示全表扫描


优化

如何优化下呢?

既然是按照id排序,结合B+Tree 的特性 ,如果能从 10000这个数据位置往后扫描,是不是就会比扫描全部理论上更快一些呢?

改造如下

select * from employees where id> 10000 limit 10;

20200808154254457.png


来看下执行计划

mysql> explain  select * from employees where id> 10000 limit 10;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 50087 |      100 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set


比一比这两个,是不是下面那个更快一些



20200808154424344.png


数据可删除的场景


还有个问题,我们知道我们业务系统有些数据是可以被删除的,如果有些数据被删除了,还是按照id来排序,上面这种优化方式,会存在问题吗?

假设8888 这条业务数据被删除了

delete from employees where id = 8888 ;


那我们来看下


20200808155331287.png


20200808155401150.png


如果允许删除,那这种优化方式是不是就不正确了?


limit 10000, 10 : 就是全部数据排好序后 取第10000个开始后的10个,我们刚才删除了8888, 所以 第一条数据就变成了 10002


id> 10000 limit 10 : 这个就很好理解了,删除了8888 ,不影响 id>10000的排序 ,所以第一条数据还是 10001


适用条件


如果主键不连续,不能使用上面描述的优化方法。

如果原 SQL 是 order by 非主键的字段,按照上的方法改写会导致两条 SQL 的结果不一致。

所以这种优化方式必须同时满足以下两个条件:


  • 主键自增且连续
  • 结果是按照主键排序的

Case2 根据非主键字段排序的分页查询

来看第二个案例,实际工作中可能比第一种用的比较多

select * from employees  ORDER BY name limit 10000, 10  ;


2020080816071648.png

来看下执行计划

mysql> explain select * from employees  ORDER BY name limit 10000, 10  ;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100175 |      100 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set
mysql> 


按照B+Tree的结构,应该会走name字段索引,wtf , 操作的结果集太多,又要回表等等原因 , MySQL可能不选name 字段的索引 , key 字段对应的值为 null ,从而走了全表扫描 。。。。


还有 Using filesort

这部分就属于MySQL内部的优化了,可以使用Trace来追踪下MySQL是如何选择的 ,


MySQL - 使用trace工具来窥探MySQL是如何选择执行计划的


MySQL认为扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高,所以优化器放弃使用索引。


那既然知道不走索引的原因,那么怎么优化呢?


关键是让排序时返回的字段尽可能少,所以可以让排序和分页操作先查出主键,然后根据主键查到对应的记录.


让排序时返回的字段尽可能少–》 只返回id , 然后用返回的特定范围的id ,再和原表关联,只取特定范围内的数据 ,肯定比全表扫描要快。

改造如下


 select * from employees a inner join (select id from employees order by name limit 10000,10) b on a.id = b.id;


先找到id (select id 使用覆盖索引),然后用这个结果集 (这个案例中就只有10条结果)去和 employees 关联

看看执行计划


2020080816320129.png

原 SQL 使用的是 filesort 排序,优化后的 SQL 使用的是索引排序。

当然了,结果集也是和优化前是一致的


20200808162926852.png


搞定MySQL

https://artisan.blog.csdn.net/article/details/107874528?spm=1001.2014.3001.5502

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
SQL 关系型数据库 MySQL
Mysql数据恢复—Mysql数据库delete删除后数据恢复案例
本地服务器,操作系统为windows server。服务器上部署mysql单实例,innodb引擎,独立表空间。未进行数据库备份,未开启binlog。 人为误操作使用Delete命令删除数据时未添加where子句,导致全表数据被删除。删除后未对该表进行任何操作。需要恢复误删除的数据。 在本案例中的mysql数据库未进行备份,也未开启binlog日志,无法直接还原数据库。
1114 2
|
存储 SQL 关系型数据库
MySQL中binlog、redolog与undolog的不同之处解析
每个都扮演回答回溯与错误修正机构角色: BinLog像历史记载员详细记载每件大大小小事件; RedoLog则像紧急救援队伍遇见突發情況追踪最后活动轨迹尽力补救; UndoLog就类似时间机器可倒带历史让一切归位原始样貌同时兼具平行宇宙观察能让多人同时看见各自期望看见历程而互不干扰.
721 9
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
438 2
|
存储 SQL 关系型数据库
MySQL 核心知识与性能优化全解析
我整理的这份内容涵盖了 MySQL 诸多核心知识。包括查询语句的书写与执行顺序,多表查询的连接方式及内、外连接的区别。还讲了 CHAR 和 VARCHAR 的差异,索引的类型、底层结构、聚簇与非聚簇之分,以及回表查询、覆盖索引、左前缀原则和索引失效情形,还有建索引的取舍。对比了 MyISAM 和 InnoDB 存储引擎的不同,提及性能优化的多方面方法,以及超大分页处理、慢查询定位与分析等,最后提到了锁和分库分表可参考相关资料。
346 0
|
SQL 运维 关系型数据库
MySQL Binlog 日志查看方法及查看内容解析
本文介绍了 MySQL 的 Binlog(二进制日志)功能及其使用方法。Binlog 记录了数据库的所有数据变更操作,如 INSERT、UPDATE 和 DELETE,对数据恢复、主从复制和审计至关重要。文章详细说明了如何开启 Binlog 功能、查看当前日志文件及内容,并解析了常见的事件类型,包括 Format_desc、Query、Table_map、Write_rows、Update_rows 和 Delete_rows 等,帮助用户掌握数据库变化历史,提升维护和排障能力。
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
关系型数据库 MySQL
MySQL字符串拼接方法全解析
本文介绍了四种常用的字符串处理函数及其用法。方法一:CONCAT,用于基础拼接,参数含NULL时返回NULL;方法二:CONCAT_WS,带分隔符拼接,自动忽略NULL值;方法三:GROUP_CONCAT,适用于分组拼接,支持去重、排序和自定义分隔符;方法四:算术运算符拼接,仅适用于数值类型,字符串会尝试转为数值处理。通过示例展示了各函数的特点与应用场景。
|
机器学习/深度学习 人工智能 搜索推荐
技术革新下的培训新趋势:案例解析
从最初的“试试看”,到如今的“非做不可”,企业培训已经成为央国企和上市公司不可或缺的战略环节。无论是AI与大模型的赋能,DeepSeek,还是具身智能、智算技术和数据科学的实战应用,这些课程都在为企业打开新的可能性。
|
设计模式 存储 安全
【23种设计模式·全精解析 | 创建型模式篇】5种创建型模式的结构概述、实现、优缺点、扩展、使用场景、源码解析
结构型模式描述如何将类或对象按某种布局组成更大的结构。它分为类结构型模式和对象结构型模式,前者采用继承机制来组织接口和类,后者釆用组合或聚合来组合对象。由于组合关系或聚合关系比继承关系耦合度低,满足“合成复用原则”,所以对象结构型模式比类结构型模式具有更大的灵活性。 结构型模式分为以下 7 种: • 代理模式 • 适配器模式 • 装饰者模式 • 桥接模式 • 外观模式 • 组合模式 • 享元模式
1154 140
【23种设计模式·全精解析 | 创建型模式篇】5种创建型模式的结构概述、实现、优缺点、扩展、使用场景、源码解析
|
算法 测试技术 C语言
深入理解HTTP/2:nghttp2库源码解析及客户端实现示例
通过解析nghttp2库的源码和实现一个简单的HTTP/2客户端示例,本文详细介绍了HTTP/2的关键特性和nghttp2的核心实现。了解这些内容可以帮助开发者更好地理解HTTP/2协议,提高Web应用的性能和用户体验。对于实际开发中的应用,可以根据需要进一步优化和扩展代码,以满足具体需求。
1805 29

推荐镜像

更多