MySQL分页查询实例讲解

最新推荐文章于 2024-07-25 16:06:56 发布
最新推荐文章于 2024-07-25 16:06:56 发布
阅读量876 收藏
点赞数
分类专栏： mysql 文章标签： mysql

http://www.cnblogs.com/skylerjiang/p/6594171.html

1. 前言

本文描述了团队在工作中遇到的一个MySQL分页查询问题，顺带讲解相关知识点，为后来者鉴。本文的重点不是"怎样"优化表结构和SQL语句，而是探索不同查询方式"为什么"会有显著差异。在本文中，涉及下列知识点：

  1. MySQL 延迟关联
  2. MySQL Optimizer Trace使用
  3. MySQL 排序原理

2. 问题

工作中用到了一张表，字段比较多，每行大概500字节，总行数大概80万。场景中，需要根据某个非索引字段排序，然后进行分页读取。先把脱敏之后的表结构奉上（只修改了字段名）：

 

复制代码 
CREATE TABLE `t` (

  `a0` varchar(16) NOT NULL,

  `a1` bigint(20) NOT NULL,

  `a2` decimal(27,9) NOT NULL DEFAULT '0', `a3` decimal(27,9) NOT NULL DEFAULT '0',

  `a4` decimal(27,9) NOT NULL DEFAULT '0', `a5` decimal(27,9) NOT NULL DEFAULT '0',

  `a6` decimal(27,9) NOT NULL DEFAULT '0', `a7` decimal(27,9) NOT NULL DEFAULT '0',

  `a8` decimal(18,9) NOT NULL DEFAULT '0', `a9` decimal(18,9) NOT NULL DEFAULT '0',

  `b1` decimal(27,9) NOT NULL DEFAULT '0', `b2` decimal(27,9) NOT NULL DEFAULT '0',

  `b3` decimal(18,9) NOT NULL DEFAULT '0', `b4` decimal(18,9) NOT NULL DEFAULT '0',

  `b5` decimal(18,9) NOT NULL DEFAULT '0', `b6` decimal(18,9) NOT NULL DEFAULT '0',

  `b7` decimal(18,9) NOT NULL DEFAULT '0', `b8` decimal(18,9) NOT NULL DEFAULT '0',

  `b9` decimal(18,9) NOT NULL DEFAULT '0', `c1` decimal(18,9) NOT NULL DEFAULT '0',

  `c2` decimal(18,9) NOT NULL DEFAULT '0', `c3` decimal(18,9) NOT NULL DEFAULT '0',

  `c4` decimal(18,9) NOT NULL DEFAULT '0', `c5` decimal(18,9) NOT NULL DEFAULT '0',

  `c6` decimal(18,9) NOT NULL DEFAULT '0', `c7` int(11) NOT NULL DEFAULT '0',

  `c8` int(11) NOT NULL DEFAULT '0', `c9` int(11) NOT NULL DEFAULT '0',

  `d1` int(11) NOT NULL DEFAULT '0', `d2` int(11) NOT NULL DEFAULT '0',

  `d3` decimal(18,9) NOT NULL DEFAULT '0', `d4` decimal(18,9) NOT NULL DEFAULT '0',

  `d5` decimal(18,9) NOT NULL DEFAULT '0', `d6` decimal(18,9) NOT NULL DEFAULT '0',

  `d7` decimal(18,9) NOT NULL DEFAULT '0', `d8` decimal(18,9) NOT NULL DEFAULT '0',

  `d9` int(11) NOT NULL DEFAULT '0', `e1` decimal(27,9) NOT NULL DEFAULT '0',

  `e2` decimal(27,9) NOT NULL DEFAULT '0', `e3` decimal(27,9) NOT NULL DEFAULT '0',

  `e4` decimal(18,9) NOT NULL DEFAULT '0', `e5` decimal(18,9) NOT NULL DEFAULT '0',

  `e6` decimal(18,9) NOT NULL DEFAULT '0', `e7` decimal(18,9) NOT NULL DEFAULT '0',

  `e8` decimal(18,9) NOT NULL DEFAULT '0', `e9` decimal(18,9) NOT NULL DEFAULT '0',

  `f1` decimal(18,9) NOT NULL DEFAULT '0', `f2` decimal(18,9) NOT NULL DEFAULT '0',

  PRIMARY KEY (`a0`,`a1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码

 

重点字段是a0和a1。再看一下表信息：

 

一开始的时候，使用最直接的语句，耗时6.67秒：

SQL1: SELECT * FROM t ORDER BY a1 DESC LIMIT 100000,1;

 

随后，使用"延迟关联"，耗时0.90秒：

SQL2: SELECT * FROM t INNER JOIN (SELECT a0, a1 FROM t  ORDER BY a1 DESC LIMIT 100000,1) AS A USING (a0, a1);

 

TIP：延迟关联，先根据条件查询需要的主键，再根据主键关联原表获得需要的数据。

问题来了： 延迟关联为什么更快？

首先，我们需要找到合适的工具，观察MySQL执行这些查询的时候做了什么。这个工具就是"MySQL Optimizer Trace"。 其次，场景中用到了排序（Order By），还需要了解MySQL排序的基本知识。

3. 背景知识

3.1 Optimizer Trace 使用简介

Optimizer Trace 是MySQL 5.6.3里新加的一个特性，可以把MySQL Optimizer的决策和执行过程输出成文本。输出使用JSON格式，便于程序分析和人类阅读。

【使用方法】

  • 启用Optimizer Trace，它默认是关闭的:  SET optimizer_trace="enabled=on"; 
  • 设置Trace使用的内存，默认内存比较小，有时候不够用:  SEToptimizer_trace_max_mem_size=1024000; 
  • 执行SQL语句:  SELECT XXX FROM YYY; 
  • 查看Trace输出:  select trace from information_schema.optimizer_trace\G 

Trace输出分为3大部分，如下，分别对应到mysql中的三个函数： JOIN::prepare(), JOIN::optimize(), JOIN::exec()  。

复制代码 
{
    trace: {
        steps: [
            {
                join_preparation: {}
            },
            {
                join_optimization: {}
            },
            {
                join_execution: {}
            }
        ]
    }
}
复制代码

 

【系统参数】

追踪行为完全由OPTIMIZER_TRACE系列参数控制，关于这些参数的详细说明参考mysql的在线文档。

#  show variables like '%optimizer_trace%';

 

Variable_name

 Value                                                                     

optimizer_trace

enabled=off,one_line=off

optimizer_trace_features

greedy_search=on,range_optimizer=on, dynamic_range=on,repeated_subselect=on

optimizer_trace_limit   

1

optimizer_trace_max_mem_size

16384

optimizer_trace_offset

-1

我们一般只要关心optimizer_trace/optimizer_trace_max_mem_size这两个参数。

复制代码 
optimizer_trace enabled=on  启用追踪

                   enabled=off 不启用追踪

                   one_line=on TRACE输出在一行里面，便于程序处理

                   one_line=off TRACE输出在多行，便于阅读
复制代码

 

optimizer_trace_max_mem_size   追踪时最多允许使用多少内存，内存太小可能输出不完整。

这些参数是基于SESSION的，optimizer_trace默认情况下没有开启。

3.2 MySQL排序简介

对于MySQL排序有篇文章写的不错：MySQL排序内部原理探秘, 也可以阅读MySQL源代码的filesort.cc。这里从中挑选重要的几点列举下：

  1. MySQL会把需要排序的数据从磁盘读取到"Sort_buffer"。放到Sort_buffer的字段由 max_length_for_sort_data 决定：
    • 字段总长度 > max_length_for_sort_data，读取"排序字段+RowID"。这种方式称为回表模式，记为： < sort_key, rowid >
    • 字段总长度 ≤ max_length_for_sort_data，读取"排序字段+SELECT字段+WHERE字段"。这种方式称为不回表模式，记为： < sort_key,additional_fields >
  2. Sort_buffer有大小限制，在我们的场景中，是8MB。
  3. 数据量超过Sort_buffer时，会初步排序，然后写入外部临时表。
  4. 若使用了临时表，通过"多路归并排序"逐步合并，直到最后输出有序结果。
  5. 使用了临时表时，查询性能一般来说会急剧下降。
  6. 对于带Order By+Limit的语句，会进行"优先队列"评估，如果适用，可以只取若干元素，加速排序。

优先队列评估过程：

  • 估算数据表的数据总量上限N
  • 计算需要返回的数据总量M=(LIMIT+OFFSET)
  • 计算Sort_buffer容量X
  • Case 1: 如果X > N
    • Case 1.1: 如果M < N/PQ_slowness，启用优先队列
    • Case 1.2: 否则，不启用优先队列，而是直接用快速排序。
  • Case 2: 如果X > M+1，启用优先队列
  • Case 3: 如果当前是不回表模式，尝试去除非排序字段重新计算Sort_buffer容量Y，
    • Case 3.1如果Y > M+1，估算启用优先队列+回表模式的代价C1，估算使用临时表多路归并+不回表模式的代价C2
      • Case 3.1.1: 如果C1 < C2，启用优先队列，并修改为回表模式；
      • Case 3.1.2: 否则不启用优先队列。
  • Final: 各条件不满足，不启用优先队列。

 

 

 

4. 观察案例中SQL的执行

准备环境：

复制代码 
#  SHOW variables like '%sort%';

+--------------------------------+---------------------+

| Variable_name                  | Value               |

+--------------------------------+---------------------+

| max_length_for_sort_data       | 1024                |

| sort_buffer_size               | 8388608             |

+--------------------------------+---------------------+

 

#  SET optimizer_trace="enabled=on";

#  SET optimizer_trace_max_mem_size=1000000;

#  SELECT trace FROM information_schema.optimizer_trace\G
复制代码

 

注意两个重要变量

max_length_for_sort_data: 1024

sort_buffer_size: 8388608

【SQL1执行过程】

SELECT * FROM t ORDER BY a1 DESC LIMIT 100000,1;

 

   

 解析

  • SELECT *， 读取数据的时候，需要读取所有的字段。
  • SELECT字段+排序字段+WHERE字段长度=454，454 < max_length_for_sort_data(1024)，使用不回表模式。
  • a1没有索引，将使用全表扫描。
  • Order By+Limit，进行优先队列评估
    • Case 1不满足: 1850799*454 > 8388608
    • Case 2不满足: 100001*454 > 8388608
    • Case 3.1满足: 74*(100001+1) < 8388608
      • 计算PQ代价C1=1.18e9
      • 计算外排代价C2=3.04e6
      • C1 > C2，不启用优先队列。
  • 结果：无优先队列，不回表模式，使用了46个临时表。

【SQL2执行过程】

SELECT * FROM t INNER JOIN (SELECT a0, a1 FROM t ORDER BY a1 DESC LIMIT 100000,1) AS A USING (a0, a1);

 

 

 

解析

  • 首先执行子查询 SELECT a0, a1 FROM t ORDER BY a1 DESC LIMIT 100000,1
    • 只需要读取a0和a1两个字段
    • a1没有索引，将使用全表扫描。
    • SELECT字段+排序字段+WHERE字段长度=66，66 < max_length_for_sort_data(1024)，使用不回表模式。
    • Order By+Limit，进行优先队列评估
      • Case 1不满足: 1850799*66 > 8388608
      • Case 2满足: 100001*66 > 8388608
      • 结果：启用优先队列，不回表模式，没有使用临时表。
      • 执行完毕，结果存储在临时表A里面
  • 临时表A只有1行，连表查询时，可以使用 t 的主键，非常快速。

【结论】

全表扫描时，SQL1需要读取所有字段（大约500字节），SQL2只需要读取2个字段（小于100字节）。

  • SQL1需要使用外部排序，临时表数量又比较多（46个），所以比较慢。
  • SQL2可以启用优先队列优化，数据存放在sort_buffer，加速明显。　　

5. 场景扩展

5.1 Limit对执行过程的影响

稍微修改下SQL语句，把 LIMIT 100000,1 修改为 LIMIT 600000,1，看看执行过程。

【SQL1执行过程】

SELECT * FROM t ORDER BY a1 DESC LIMIT 600000,1;
1 row in set (8.82 sec)

 

 

 

这次的执行过程完全一样，但决策依据稍有不同：(600001+1)*74 > 8388608，Case 3.1不满足，而之前是3.1.1不满足。

【SQL2执行过程】

SELECT * FROM t INNER JOIN (SELECT a0, a1 FROM t ORDER BY a1 DESC LIMIT 600000,1) AS A USING (a0, a1);
1 row in set (1.05 sec)

 

 

 

这次Case 2和Case 3.1都不满足，使用"无优先队列+不回表模式"，但由于数据量变少了，只使用了8个临时表。

5.2 回表排序的影响

以上测试，都是不回表模式，如果是回表模式，会怎么样呢？

SET max_length_for_sort_data=100;

【SQL1执行过程】

SELECT * FROM t ORDER BY a1 DESC LIMIT 600000,1;
1 row in set (4.16 sec)

因为(SELECT字段+排序字段+WHERE字段)(454字节) > max_length_for_sort_data(100)，这次使用了回表模式，但还是无法启用优先队列优化。因为排序时只需要读取排序字段和rowid，临时表数量减少到8个。

【SQL2执行过程】

SELECT * FROM t INNER JOIN (SELECT a0, a1 FROM t ORDER BY a1 DESC LIMIT 600000,1) AS A USING (a0, a1);

1 row in set (1.05 sec)

由于(SELECT字段+排序字段+WHERE字段)(66字节) < max_length_for_sort_data(100)，依然还是"无优先队列+不回表模式""，使用了8个临时表。

6. 参考资料

  1. https://dev.mysql.com/doc/internals/en/optimizer-tracing.html
  2. http://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_OPT_TRACE.html
  3. https://dev.mysql.com/doc/refman/5.5/en/select-optimization.html
  4. https://www.percona.com/blog/2007/04/06/using-delayed-join-to-optimize-count-and-limit-queries/
  5. MySQL排序内部原理探秘 (http://geek.csdn.net/news/detail/105891)
  6. MySQL Server 5.6.34 Source Code
  7. 高性能MySQL 3rd Edition
zdy0_2004
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql分页查询实例_mysql分页查询实例讲解
weixin_40001924的博客
01-19 350
LIMIT子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。下面，我们针对特例对mysql分页查询进行总结。mysql提供分页的功能：SELECT * FROM table LIMIT [offset...
mysql分页讲解_MySQL分页查询实例讲解及性能优化技巧分析
weixin_28710999的博客
02-08 118
当需要从数据库查询的表有上万条记录的时候，一次性查询所有结果会变得很慢，特别是随着数据量的增加特别明显，这时需要使用分页查询。对于数据库分页查询，也有很多种方法和优化的点。下面简单说一下我知道的一些方法。准备工作为了对下面列举的一些优化进行测试，下面针对已有的一张表进行说明。表名：order_history描述：某个业务的订单历史表主要字段：unsigned int id，tinyint(4) i...
MySQL数据库【从入门到入土】：第五篇——MySQL排序和分页【含代码示例】
一个做过前端开发的产品经理，经历过睿智产品的折磨导致脱发之后，励志要翻身"农奴"把歌唱，一边打入敌人内部一边持续提升自己，为我们广大开发同胞谋福祉，坚决抵制睿智产品折磨我们码农兄弟！
06-24 733
排序和分页是数据库查询中常见的需求，正确使用ORDER BY和LIMIT可以极大提升应用性能和用户体验。通过理解排序和分页背后的机制，结合索引策略和最佳实践，开发者能更高效地处理大数据集。随着MySQL版本的迭代，探索如窗口函数等新特性，将为数据处理带来更多可能性。希望本文能成为你掌握MySQL排序和分页技能的坚实基石。欢迎来到我的博客，很高兴能够在这里和您见面！希望您在这里可以感受到一份轻松愉快的氛围，不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。DTcode7的博客首页。
php mysql 分页查询_PHP+MySQL分页查询实现
weixin_28802509的博客
01-19 926
重要：本文最后更新于2018-11-13 20:33:49，某些文章具有时效性，若有错误或已失效，请在下方留言或联系代码狗。随着数据库逐日增大，数据也不能一下子全部加载出来。于是，分页查询数据库里的内容显示出来就显得很有必要。特别是在APP上，若是全部查询出并加载，用户消耗的流量就非常的大。下面介绍一种分页查询的方法，亲测可用！SQL语法：SELECT*FROMtableLIMIT[of...
oracle mysql分页查询_Oracle与MySQL的分页查询sql语句格式实例讲解
weixin_36099614的博客
02-08 120
Oracle使用rownum进行分页：SELECT *FROM (SELECT a.*, ROWNUM rnFROM (SELECT * FROM table_name) aWHERE ROWNUM <= pageIndex * pageSize)WHERE rn > (pageIndex - 1) * pageSize其中，pageIndex是页码，pageSize是每页的条数MyS...
jdbc mysql 分页查询_Java基础开发之JDBC操作数据库增删改查，分页查询实例详解...
weixin_35545563的博客
01-27 186
对数据库的操作无非就是增删改查，其中数查询操作最为复杂，所以将查询单独讲解，我这里用的Mysql数据库增删改查操作分页查询操作1.查询结果以list返回2.查询结果以jsonArray返回3.查询总记录条数先看一下相关的配置信息public static final String USER_NAME = "root";public static final String PWD = "123456...
oracle mysql排序分页查询语句_MySQL数据库之Oracle与MySQL的分页查询sql语句格式实例讲解...
weixin_39898550的博客
02-02 106
本文主要向大家介绍了MySQL数据库之Oracle与MySQL的分页查询sql语句格式实例讲解 ，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。OracleOracle使用rownum进行分页：SELECT*FROM(SELECTa.*,ROWNUMrnFROM(SELECT*FROMtable_name)aWHEREROWNUM<=pageIn...
如果面试官问你如何优化mysql分页查询，请把这篇文章甩给他！
2401_84447362的博客
04-21 1021
在面试前我整理归纳了一些面试学习资料，文中结合我的朋友同学面试美团滴滴这类大厂的资料及案例由于篇幅限制，文档的详解资料太全面，细节内容太多，所以只把部分知识点截图出来粗略的介绍，每个小节点里面都有更细化的内容！大家看完有什么不懂的可以在下方留言讨论也可以关注。网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Java）一个人可以走的很快，但一群人才能走的更远！
mysql 翻页查询数据重复_Oracle和mysql的分页查询、过滤重复数据实例讲解
weixin_34596480的博客
02-04 211
上周三面试题有两道涉及Oracle的分页查询，没有意外地凉了，现在总结一下。· Mysqlmysql的分页可以直接使用关键字limit，句子写起来比较方便。语法：① limit m,n-- (m+1)为取出行的起始序号-- n 为取出的全部行数select * from table where...limit m,n-- 上述语句等价于select * from table shere...lim...
演示mysql分页查询_数据库表格的查询操作-分页查询
weixin_33522643的博客
02-12 71
MySQL是开源免费和功能多面的小型数据库，MySQL也是目前流行通用的关系型数据库，已经被 Oracle 收购了。随着版本更新升级，加入一些高级功能，MySQL6.x 版本也开始收费。不过本教程将使用免费开源的mysql5.5版本。本教程将通过多个sql数据库管理操作小示例,让大家直观了解mysql开发步骤和实现过程，并在实际操作中逐步学习mysql语法规则和脚本开发的一些重要知识点及技巧。课程...
php5 mysql分页实例代码
10-30
【PHP5 MySQL分页实例代码详解】 在网页开发中，数据量较大的情况下，为了提高用户体验，通常会采用分页技术来展示数据。本实例将详细讲解如何在PHP5环境中使用MySQL进行分页处理。 首先，我们需要连接到MySQL...
jQuery+Ajax+PHP+Mysql实现分页显示数据实例讲解
10-23
是一个典型的Ajax应用，在页面上，您只需要点击“下一页”，数据区将自动加载...类似的效果在很多网站上应用，尤其在一些需要展示大量图片数据的网页如淘宝商品列表页，Ajax分页效果让您的网站数据加载显得非常流畅。
mysql 分页例子
11-12
本篇将详细讲解MySQL分页的基本原理、语法以及实际应用。 一、分页概念与原理 1. 分页概念：分页是将大范围的数据分割成若干小块，每次查询只返回一部分数据，用户可以逐页浏览，减轻服务器压力，提升页面加载速度...
PHP分页详细讲解（有实例）
12-19
本篇文章主要面向初学者，通过实例讲解PHP分页的基本原理和实现方法。 1. 分页原理 分页的核心思想是将数据库中的数据集分割成多个部分，每次只显示一部分。关键的两个参数是每页显示的记录数（$PageSize）和当前页...
web小项目-曼波生日录(Servlet+JSP+MySQL)
m0_75138009的博客
07-24 905
当记录条数过多时会自动出现滚轮，数据不会超出紫框。
《MySQL DBA 修炼之道》第四章 开发进阶
最新发布
Trishia的博客
07-25 651
博主学习《MySQL DBA 修炼之道》结合自身理解的总结。主要讲解了范式、反范式、慢SQL查询、事务和锁等。
数据库安全：MySQL安全配置，MySQL安全基线检查加固
wangyuxiang946的博客
07-23 2268
MySQL基线检查，基线配置，安全加固
测试环境搭建整套大数据系统（十七：mysql同步，字段类型映射错误问题）
weixin_43446246的博客
07-24 474
mysql表hive表其中 type是字符串类型，但是yarn上一直报错number这个类是往int类型转才会使用到的。
北大青鸟MySQL高级查询与数据操作教程
本资源是关于北大青鸟MySQL数据库教程的第三章PPT资料，主要讲解了高级查询技术以及SQL语句在数据操作中的应用。章节内容包括以下几个关键知识点： 1. 高级查询技术：首先介绍了如何一次性插入多条数据，通过SQL...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值