MySQL分页查询实例讲解

1. 前言

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

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

2. 问题

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

CREATE TABLE `t` (  `a0` ) NOT NULL,  `a1` ) NOT NULL,  `a2` ,) ,) ',  `a4` ,) ,) ',  `a6` ,) ,) ',  `a8` ,) ,) ',  `b1` ,) ,) ',  `b3` ,) ,) ',  `b5` ,) ,) ',  `b7` ,) ,) ',  `b9` ,) ,) ',  `c2` ,) ,) ',  `c4` ,) ,) ',  `c6` ,) ) ',  `c8` ) ) ',  `d1` ) ) ',  `d3` ,) ,) ',  `d5` ,) ,) ',  `d7` ,) ,) ',  `d9` ) ,) ',  `e2` ,) ,) ',  `e4` ,) ,) ',  `e6` ,) ,) ',  `e8` ,) ,) ',  `f1` ,) ,) ',  PRIMARY KEY (`a0`,`a1`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

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

SQL1: ,;

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

SQL2: ,) 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使用的内存，默认内存比较小，有时候不够用:  ;
• 执行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%';

我们一般只要关心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               |+--------------------------------+---------------------+                |             |+--------------------------------+---------------------+#  SET optimizer_trace="enabled=on";#  ;#  SELECT trace FROM information_schema.optimizer_trace\G

注意两个重要变量

max_length_for_sort_data: 1024

sort_buffer_size: 8388608

【SQL1执行过程】

,;

解析

• 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执行过程】

,) 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执行过程】

,; row in set (8.82 sec)

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

【SQL2执行过程】

,) AS A USING (a0, a1); row in set (1.05 sec)

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

5.2 回表排序的影响

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

;

【SQL1执行过程】

,; 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