AI 助理
备案控制台
开发者社区 老叶茶馆 文章 正文

[MySQL优化案例]系列 — 分页优化

2021-11-18 135
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： [MySQL优化案例]系列 — 分页优化

通常，我们会采用ORDER BY LIMIT start, offset 的方式来进行分页查询。例如下面这个SQL：

SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10;

或者像下面这个不带任何条件的分页SQL：

SELECT * FROM `t1` ORDER BY id DESC LIMIT 100, 10;

一般而言，分页SQL的耗时随着 start 值的增加而急剧增加，我们来看下面这2个不同起始值的分页SQL执行耗时：

yejr@imysql.com> SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 500, 10;
…

10 rows in set (0.05 sec)


yejr@imysql.com> SELECT * FROM `t1` WHERE ftype=6 ORDER BY id DESC LIMIT 935500, 10;
…

10 rows in set (2.39 sec)

可以看到，随着分页数量的增加，SQL查询耗时也有数十倍增加，显然不科学。今天我们就来分析下，如何能优化这个分页方案。

一般滴，想要优化分页的终极方案就是：没有分页，哈哈哈~~~，不要说我讲废话，确实如此，可以把分页算法交给Sphinx、Lucence等第三方解决方案，没必要让MySQL来做它不擅长的事情。

当然了，有小伙伴说，用第三方太麻烦了，我们就想用MySQL来做这个分页，咋办呢？莫急，且待我们慢慢分析，先看下表DDL、数据量、查询SQL的执行计划等信息：

yejr@imysql.com> SHOW CREATE TABLE `t1`;
CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...
`ftype` tinyint(3) unsigned NOT NULL,
...
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

yejr@imysql.com> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 994584 |
+----------+

yejr@imysql.com> EXPLAIN SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 500, 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL rows: 510
Extra: Using where

yejr@imysql.com> EXPLAIN SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500, 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL rows: 935510
Extra: Using where

可以看到，虽然通过主键索引进行扫描了，但第二个SQL需要扫描的记录数太大了，而且需要先扫描约935510条记录，然后再根据排序结果取10条记录，这肯定是非常慢了。

针对这种情况，我们的优化思路就比较清晰了，有两点：

1、尽可能从索引中直接获取数据，避免或减少直接扫描行数据的频率
2、尽可能减少扫描的记录数，也就是先确定起始的范围，再往后取N条记录即可

据此，我们有两种相应的改写方法：子查询、表连接，即下面这样的：

#采用子查询的方式优化，在子查询里先从索引获取到最大id，然后倒序排，再取10行结果集
#注意这里采用了2次倒序排，因此在取LIMIT的start值时，比原来的值加了10，即935510，否则结果将和原来的不一致
yejr@imysql.com> EXPLAIN SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: <derived2>

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 10

Extra: Using filesort

*************************** 2. row ***************************

id: 2

select_type: DERIVED

table: t1

type: ALL

possible_keys: PRIMARY

key: NULL

key_len: NULL

ref: NULL

rows: 973192

Extra: Using where

*************************** 3. row ***************************

id: 3

select_type: SUBQUERY

table: t1

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 935511

Extra: Using where

#采用INNER JOIN优化，JOIN子句里也优先从索引获取ID列表，然后直接关联查询获得最终结果，这里不需要加10
yejr@imysql.com> EXPLAIN SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500,10) t2 USING (id)\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: <derived2>

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 935510

Extra: NULL

*************************** 2. row ***************************

id: 1

select_type: PRIMARY

table: t1

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: t2.id

rows: 1

Extra: NULL

*************************** 3. row ***************************

id: 2

select_type: DERIVED

table: t1

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 973192

Extra: Using where


然后我们来对比下这2个优化后的新SQL执行时间：


yejr@imysql.com> SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) T ORDER BY id DESC;
...
rows in set (1.86 sec)
#采用子查询优化，从profiling的结果来看，相比原来的那个SQL快了：28.2%

yejr@imysql.com> SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500,10) t2 USING (id);
...
10 rows in set (1.83 sec)
#采用INNER JOIN优化，从profiling的结果来看，相比原来的那个SQL快了：30.8%


我们再来看一个不带过滤条件的分页SQL对比：


#原始SQL
yejr@imysql.com> EXPLAIN SELECT * FROM `t1` ORDER BY id DESC LIMIT 935500, 10\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: t1
        type: index
possible_keys: NULL
         key: PRIMARY
     key_len: 4
         ref: NULL
        rows: 935510
       Extra: NULL

yejr@imysql.com> SELECT * FROM `t1` ORDER BY id DESC LIMIT 935500, 10;
...
10 rows in set (2.22 sec)


#采用子查询优化
yejr@imysql.com> EXPLAIN SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC;
*************************** 1. row ***************************
          id: 1
 select_type: PRIMARY
       table: <derived2>
        type: ALL
possible_keys: NULL
         key: NULL
     key_len: NULL
         ref: NULL
        rows: 10
       Extra: Using filesort
*************************** 2. row ***************************
          id: 2
 select_type: DERIVED
       table: t1
        type: ALL
possible_keys: PRIMARY
         key: NULL
     key_len: NULL
         ref: NULL
        rows: 973192
       Extra: Using where
*************************** 3. row ***************************
          id: 3
 select_type: SUBQUERY
       table: t1
        type: index
possible_keys: NULL
         key: PRIMARY
     key_len: 4
         ref: NULL
        rows: 935511
       Extra: Using index


yejr@imysql.com> SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC;
…
10 rows in set (2.01 sec)
#采用子查询优化，从profiling的结果来看，相比原来的那个SQL快了：10.6%



#采用INNER JOIN优化
yejr@imysql.com> EXPLAIN SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1`ORDER BY id DESC LIMIT 935500,10) t2 USING (id)\G
*************************** 1. row ***************************
          id: 1
 select_type: PRIMARY
       table:
        type: ALL
possible_keys: NULL
         key: NULL
     key_len: NULL
         ref: NULL
        rows: 935510
       Extra: NULL
*************************** 2. row ***************************
          id: 1
 select_type: PRIMARY
       table: t1
        type: eq_ref
possible_keys: PRIMARY
         key: PRIMARY
     key_len: 4
         ref: t1.id
        rows: 1
       Extra: NULL
*************************** 3. row ***************************
          id: 2
 select_type: DERIVED
       table: t1
        type: index
possible_keys: NULL
         key: PRIMARY
     key_len: 4
         ref: NULL
        rows: 973192
       Extra: Using index


yejr@imysql.com> SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1`ORDER BY id DESC LIMIT 935500,10) t2 USING (id);
…
10 rows in set (1.70 sec)
#采用INNER JOIN优化，从profiling的结果来看，相比原来的那个SQL快了：30.2%


至此，我们看到采用子查询或者INNER JOIN进行优化后，都有大幅度的提升，这个方法也同样适用于较小的分页，虽然LIMIT开始的 start 位置小了很多，SQL执行时间也快了很多，但采用这种方法后，带WHERE条件的分页分别能提高查询效率：24.9%、156.5%，不带WHERE条件的分页分别提高查询效率：554.5%、11.7%，各位可以自行进行测试验证。单从提升比例说，还是挺可观的，确保这些优化方法可以适用于各种分页模式，就可以从一开始就是用。


我们来看下各种场景相应的提升比例是多少：


 大分页，带WHERE 大分页，不带WHERE 大分页平均提升比例 小分页，带WHERE 小分页，不带WHERE 总体平均提升比例
子查询优化 28.20% 10.60% 19.40% 24.90% 554.40% 154.53%
INNER JOIN优化 30.80% 30.20% 30.50% 156.50% 11.70% 57.30%

结论：这样看就和明显了，尤其是针对大分页的情况，因此我们优先推荐使用INNER JOIN方式优化分页算法。


上述每次测试都重启mysqld实例，并且加了SQL_NO_CACHE，以保证每次都是直接数据文件或索引文件中读取。如果数据经过预热后，查询效率会一定程度提升，但但上述相应的效率提升比例还是基本一致的。

文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
索引
MySQL
算法
关键词：
云数据库 RDS MySQL 版优化
云数据库 RDS MySQL 版分页
云数据库 RDS MySQL 版案例
云数据库 RDS MySQL 版优化分页
云数据库 RDS MySQL 版案例分页
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
开发者小达人
目录
相关文章
mikechen的互联网架构
|
1天前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案，包括索引优化、SQL慢查询优化和数据库表优化，帮助提升数据库性能。关注【mikechen的互联网架构】，10年+BAT架构经验倾囊相授。
mikechen的互联网架构
21 9
MySQL慢查询优化、索引优化、以及表等优化详解
vohelon
|
5天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度？
如何优化MySQL查询速度？【10月更文挑战第31天】
vohelon
20 3
肾透侧视攻城狮
|
11天前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列（5）作者——LJS[含MySQL DQL基本查询:select；简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法
肾透侧视攻城狮
37 5
mao29955
|
8天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能？
【10月更文挑战第28天】
mao29955
28 1
最好zzz
|
15天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战：百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时，传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案，通过将数据库操作与高速缓存相结合，可以显著提升数据处理的性能。本文将分享一次实际的优化案例，探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
最好zzz
46 9
潇洒洒
|
15天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战：优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时，传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率，我们可以结合使用MySQL和Redis，利用两者的优势来优化数据查询。本文将分享一次实战经验，探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
潇洒洒
42 5
mzlogin
|
15天前
|
关系型数据库 MySQL 数据库
一个 MySQL 数据库死锁的案例和解决方案
本文介绍了一个 MySQL 数据库死锁的案例和解决方案。
mzlogin
19 3
小王老师呀
|
18天前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则，包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限，后者又优先于表级别权限。在权限冲突时，更严格的权限将被优先执行，确保数据库的安全性与资源合理分配。
小王老师呀
42 4
土木林森
|
9天前
|
监控 关系型数据库 MySQL
数据库优化：MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型，以及如何创建和维护索引，结合实战案例分析查询执行计划，帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
土木林森
46 0
土木林森
|
10天前
|
监控 关系型数据库 MySQL
数据库优化：MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件，其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引（如B-Tree、复合索引）和优化查询语句（如使用EXPLAIN、优化分页查询），可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志，持续优化性能。
土木林森
41 0

老叶茶馆

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    【MySQL专题】MySQL百万级数据插入效率优化
  • 3
    mysql修改用户密码
  • 4
    一台mysql管理多个实例
  • 5
    MySQL-5.5.33主从复制
  • 6
    删除MySQL重复数据
  • 7
    ELK搭建（四）：监控mysql慢查询、错误日志日志
  • 8
    应用MySQL MGR
  • 9
    干货分享 ｜ 揭秘下一代云数据库引擎MyBase for MySQL
  • 10
    SSH框架+mysql+tomcat 服务器 中文乱码解决方案
  • 1
    《MySQL 入门教程》第 05 篇 账户和权限，Java高并发编程详解深入理解pdf
    52
  • 2
    实时计算 Flink版产品使用合集之支持 MySQL 的并行复制吗
    71
  • 3
    实时计算 Flink版产品使用合集之flink-connector-mysql-cdc 和 flink-sql-connector-mysql-cdc有什么区别
    355
  • 4
    Mysql优化提高笔记整理，来自于一位鹅厂大佬的笔记，阿里P7亲自教你
    58
  • 5
    Echarts+vue+java+mysql实现数据可视化
    181
  • 6
    MYSQL--JSON_OBJECT 和 JSON_ARRAYAGG
    159
  • 7
    实时计算 Flink版产品使用合集之flink-connector-mysql-cdc 和 flink-connector-sqlserver-cdc 这两个CDC连接器只能选择其中一个使用吗
    156
  • 8
    MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
    124
  • 9
    MySQL 实现数据分片如何进行数据查询
    248
  • 10
    MySQL各字符集、排序规则的由来、用法，区别和联系
    129

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（Alibaba Cloud Linux 2）
  • 手动部署MySQL数据库（CentOS 7）

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    无影云桌面