MySQL分页查询实例讲解

1. 前言

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

MySQL 延迟关联
MySQL Optimizer Trace使用
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。再看一下表信息：

【MySQL】分页查询实例讲解

一开始的时候，使用最直接的语句，耗时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%';

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。这里从中挑选重要的几点列举下：

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 >
Sort_buffer有大小限制，在我们的场景中，是8MB。
数据量超过Sort_buffer时，会初步排序，然后写入外部临时表。
若使用了临时表，通过"多路归并排序"逐步合并，直到最后输出有序结果。
使用了临时表时，查询性能一般来说会急剧下降。
对于带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: 各条件不满足，不启用优先队列。

【MySQL】分页查询实例讲解

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

,;

【MySQL】分页查询实例讲解

解析

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

【MySQL】分页查询实例讲解

解析

首先执行子查询 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)

【MySQL】分页查询实例讲解

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

【SQL2执行过程】

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

【MySQL】分页查询实例讲解

这次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. 参考资料

https://dev.mysql.com/doc/internals/en/optimizer-tracing.html
http://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_OPT_TRACE.html
https://dev.mysql.com/doc/refman/5.5/en/select-optimization.html
https://www.percona.com/blog/2007/04/06/using-delayed-join-to-optimize-count-and-limit-queries/
MySQL排序内部原理探秘 (http://geek.csdn.net/news/detail/105891)
MySQL Server 5.6.34 Source Code
高性能MySQL 3rd Edition

秒客网

【MySQL】分页查询实例讲解

MySQL分页查询实例讲解

1. 前言

2. 问题

3. 背景知识

3.1 Optimizer Trace 使用简介

3.2 MySQL排序简介

4. 观察案例中SQL的执行

5. 场景扩展

5.1 Limit对执行过程的影响

5.2 回表排序的影响

6. 参考资料

相关文章