《高性能MySQL》（五）查询性能优化

查询优化、索引优化、库表结构优化需要齐头并进。

一. 为什么查询速度会慢

查询是一项任务，由一系列子任务组成，每个子任务都要消耗一定时间，优化查询就是要么消除一部分子任务、要么减少子任务的执行次数、要么提供子任务的执行速度。

查询的生命周期大致包括：从客户端、到服务器、服务器进行解析、生成执行计划、执行、返回结果给客户端。其中执行是最重要的阶段，包括大量检索数据到存储引擎以及后续数据处理，如排序、分组等。

查询会在如网络、CPU计算、生成统计信息和执行计划、锁等待（互斥等待）等操作上花费时间。慢查询普遍都会存在如操作被额外的重复执行了多次、某些操作执行的太慢等问题。

二. 优化数据访问

大部分性能低下的查询都可以通过减少访问的数据量来优化：

1. 检查是否访问了太多的行或列；
2. 确认MySQL服务器层是否在分析大量超过需要的数据行。

2.1 是否向数据库请求了不需要的数据

• 查询不需要的记录：比如查询大量数据后，再仅显示前面N行。最简单的解决方案是查询加LIMIT。
• 多表关联时返回全部列：比如 SELECT * FROM A JOIN B JOIN C ，实际上仅需要A表的列，改为 SELECT A.* 最好明确指定每个返回列。
• 总是取出全部列：每次使用 SELECT * 时都要仔细思考是否需要全部列，有时DBA会禁止类似写法。
• 重复查询相同的数据：比如用户评论时要获取头像URL，可以考虑缓存这类数据，避免每次都要重复查询。

2.2 是否在扫描额外的记录

衡量查询开销的三个指标：慢查询日志包含这三项内容

• 响应时间：服务时间+排队时间，但实际测量往往无法区分二者；
• 扫描的行数：EXPLAIN的type列反应了访问类型，如全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等，速度由慢至快，扫描行数由多到少；访问类型不够合适时，最好创建一个合适的索引；MySQL不会返回真实的扫描行数，只会告知生成结果时一共扫描了多少行，但可能大部分行是被WHERE条件过滤；
• 返回的行数：扫描的行数和返回行数的比值通常应该很小，介于1:1和1:10之间，当然有时也会很大（比如分组统计查询）。

EXPLAIN的 Using where 表示MySQL将通过WHERE条件筛选存储引擎返回的记录，MySQL应用Where条件有三种方式，由好到坏依次是：

1. 在索引中使用WHERE条件来过滤不匹配的记录，在存储引擎层完成。
2. 使用索引覆盖扫描（Extra列出现Using index）返回记录，直接从索引中过滤不需要的记录并返回命中的结果，在服务器层完成，但无需回表查询记录。
3. 从数据表返回数据，过滤不满足条件的记录（Extra列出现Using where），在服务器层完成，需要先从数据表读出记录然后过滤。

扫描行数远远大于返回行数，需要优化：

• 使用索引覆盖扫描。
• 改变库表结构，如使用单独的汇总表。
• 重写复杂查询，让MySQL优化器可以以更优化的方式执行。

2.3 重构查询的方式

2.3.1 判断一个复杂查询还是多个简单查询

设计查询时需要考虑的一个问题是：是否需要将一个复杂查询拆分为多个简单查询？我们普遍会选择让数据库尽可能的完成更多的工作，因为下意识会认为网络通信、查询解析和优化是一件代价很高的事情。

但对于MySQL来说，这种常识是不准确的：

1. MySQL的连接和断开都很轻量；
2. 现代网络速度越来越快，无论是带宽还是延迟；
3. MySQL每秒能在内存扫描上百万行数据，相比下响应数据给客户端要慢很多，其他条件不变的情况下越少查询是更好；
4. 一些情况下切为小查询能减少工作量，并且能够带来整体性能的提升，小事务经常能更高效，并且若中间暂停一段时间，也可以把一次性的压力分散到大的时间段，降低对服务器的影响和锁的持有时间。

2.3.2 切分查询

最常见的案例，比如要定期清除大量数据，如果一个语句一次完成可能会锁住很多数据、占满整个事务日志、消耗过多系统资源、阻塞很多小但重要的查询。

// 每一万行删除一次
rows_affected = 0
do {
	rows_affected = do_query("DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

2.3.3 分解关联查询

对每个表进行一次单表查询，在应用层对结果进行关联：

--关联查询
SELECT * 
FROM tag
JOIN tag_post
ON tag_post.tag_id = tag.tag_id
JOIN post
ON tag_post.post_id = post.post_id
WHERE tag.tag = 'mysql';
--分解为三次单表查询
SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post_id IN (123,456,567,9098,8904);

优点：

• 让缓存效率更高，单表结果方便进行缓存，已缓存的数据下次就可以跳过；关联的表发生变化会使MySQL查询缓存失效；
• 查询分解后可以减少锁的竞争；
• 应用层做关联更容易对数据库进行拆分，方便高性能和可扩展；
• 查询效率本身也有提升，上述例子使用 IN() 代替关联查询可以使MySQL按照ID顺序查询，要比随机关联高效。
• 减少冗余记录的查询，在应用层做关联意味着某条记录只需查询一次，而在数据库关联查询可能需要重复的访问一部分数据。

适用场景：

• 应用方便缓存单个查询的结果时；
• 可以将数据分布到不同的MySQL服务器时；
• 能够使用 IN() 代替关联查询时；
• 当查询中使用同一个数据表时。

三. 查询执行的基础

3.1 一个查询在MySQL中的执行过程

MySQL执行一个查询的过程：

1. 客户端发送一条查询给服务器。
2. 服务器先检查缓存，若命中缓存就立即返回结果。否则进入下一步。
3. 服务器进行SQL解析、预处理，再由优化器生成对应的执行计划。
4. 根据执行计划调用存储引擎的API来执行查询。
5. 将结果返回客户端。

3.2 MySQL 客户端 / 服务器通信协议

MySQL 客户端 / 服务器通信协议是半双工协议，即要么服务器向客户端发送数据、要么客户端向服务器发送数据，两个动作不能同时发生。

优点是通信简单快速，缺点是无法进行流量控制。一方一旦开始发送消息，另一方只能接收完整消息后才能进行响应。一方不能使另一方停止，MySQL一般要等所有数据都发送给客户端后才能释放查询所占用的资源，所以客户端缓存数据可以减轻服务器压力，让查询尽快结束早点释放资源。

查看MySQL连接/线程的状态：SHOW FULL PROCESSLIST

• Sleep：线程正在等待客户端发送新的请求。
• Query：线程正在执行查询或正在将结果发送给客户端。
• Locked：在MySQL服务器层，该线程正在等待表锁；存储引擎级别的锁（如行锁）不会体现在线程状态。
• Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。
• Copying to tmp table [on disk]：线程正在执行查询，并将结果集都复制到一个临时表中，一般是在做GROUP BY操作、文件排序、UNION操作等；on disk表示正在将一个内存表放到磁盘。
• Sorting result：线程正在对结果集进行排序。
• Sending data：线程可能在多个状态间传送数据、或者生成结果集、或者在向客户端返回数据。

3.3 查询优化处理

查询的生命周期中下一步是将一个SQL转换为一个执行计划，MySQL按照执行计划和存储引擎交互：

• 解析SQL：MySQL通过关键字将SQL语句进行解析，生成一棵解析树，通过MySQL语法规则进行验证和解析查询（如关键字是否正确、顺序是否正确、引号前后是否匹配）。
• 预处理：根据一些MySQL规则进一步检查解析树是否合法（数据表和列是否存在、名字或别名是否有歧义），验证权限。
• 优化SQL执行计划

查询优化器：MySQL使用基于成本的优化器，预测一个查询使用某种执行计划时的成本并选择最小的一个。

导致优化器选择错误执行计划的原因：

• 统计信息不准确，评估成本依赖存储引擎提供的统计信息，有时信息的偏差会很大，如MVCC架构并不维护数据表行数的精确统计。
• 执行计划的成本估算并不等价于实际执行的成本，如读取的页面可能在内存或磁盘，具体要多少次物理I/O无法得知。
• MySQL的最优并不一定是最快的执行方式，只是基于成本模型的最优计划。
• MySQL并不考虑其他并发执行的查询。
• MySQL并不考虑不受控制的操作的成本，如存储过程或自定义函数。
• 有些特殊情况并不基于成本选择优化，如全文搜索的 MATCH() 子句，存在全文索引就会使用，即使有时别的索引和WHERE条件会更快。

优化器的优化策略：

• 静态优化：直接对解析树进行分析，并完成优化。
• 动态优化：与查询的上下文或其他因素有关，如WHERE条件取值、索引中条目对应的数据行数等，需要每次查询时重新评估。

能够优化的类型：

• 重新定义关联表的顺序
• 将外连接转化为内连接
• 使用等价变换规则
• 优化 COUNT() 、MIN() 和 MAX() ，找到某一列最小值只需查询对应B-Tree索引最左端的记录，使用此类优化在EXPLAIN中会出现 Select tables optimized away 表示优化器从执行计划中移除该表，用一个常数取代。
• 预估并转化为常数表达式
• 覆盖索引扫描
• 子查询优化
• 提前终止查询
• 等值传播
• 列表 IN() 的比较

3.4 MySQL如何执行关联查询

3.4.1 MySQL中的关联查询

MySQL中每个查询都是一次关联。比如对于UNION查询，MySQL先将一系列单个查询的结果放在一个临时表中，然后再重新读出临时表数据完成UNION查询。

MySQL中关联查询流程：

• MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去直到找到所有表中匹配的行为止。
• 然后根据各个表匹配的行，返回查询中需要的各个列。
• MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行，返回到上一层关联表继续查找更多匹配的记录，依次迭代执行。

关联操作1如下：

SELECT tab1.col1, tab2.col2
FROM tab1 INNER JOIN tab2 USING(col3)
WHERE tab1.col1 IN(5, 6);

伪代码如下：

关联操作2如下：

SELECT tab1.col1, tab2.col2
FROM tab1 LEFT OUTER JOIN tab2 USING(col3)
WHERE tab1.col1 IN(5, 6);

伪代码如下：

关联操作1的内连接使用泳道图表示：

3.4.2 执行计划

MySQL并不像其他关系型数据库那样生成查询字节码来执行查询，而是生成查询的一棵指令树，最终的执行计划包含了重构查询的全部信息。

对某个查询执行 EXPLAIN EXTENDED ，然后再执行 SHOW WARNINGS 可以看到重构出的查询。

任意多表查询都可以使用一棵树表示，如下图为一个四表关联操作：

上图这种平衡树并非MySQL执行查询的方式，MySQL总是从一个表开始嵌套循环、回溯完成所有表关联，所以是下图这种左侧深度优先树：

3.4.3 关联查询优化器

关联查询优化是优化器最重要的部分，决定多个表关联的顺序。优化器通过评估不同顺序的成本选择一个代价最小的关联顺序。

有时优化器选择的并非一定是最优顺序，此时可以使用 STRAIGHT_JOIN 关键字重写查询，让优化器按指定顺序执行，绝大部分情况优化器的判断要比开发者精准。

优化器如何计算成本：

• 一般会遍历每个表逐个做嵌套循环计算每一棵执行计划树的成本。
• 但如果有超过N个表关联，需要检查N的阶乘种关联顺序；比如N=10时就有3628800种关联顺序，过多的可能导致优化器不可能逐一评估每种顺序的成本；
• 当搜索空间特别大时，优化器选择贪婪搜索方式，而非逐个评估。

3.5 排序优化

• 无论怎样，排序都是一个成本很高的操作，在不能使用索引排序时，数据量小的排序会在内存中进行，数据量大时需要使用磁盘；
• 判断条件是需要排序的数据量是否小于排序缓冲区；
• 内存中直接使用快速排序；内存不够排序，先将数据分块，每个块进行快速排序，结果存放在磁盘，然后将排好序的各个块合并，并返回最终结果。
• 在关联查询时如果需要排序，MySQL分两种情况来处理：
  • ORDER BY 子句中所有列都来自于关联的第一个表，MySQL在处理第一个表时就进行文件排序，这种情况EXPLAIN 的 Extra 字段会有 Using filesort ；
  • 其他情况下，MySQL都会将关联的结果放到一个临时表中，然后在所有的关联结束后再进行文件排序，这种情况EXPLAIN 的 Extra 字段会有 Using temporary; Using filesort ；查询中有LIMIT会在排序后应用，MySQL 5.6 版本后，当只需返回部分排序结果时不会对所有结果进行排序。

四. 查询优化器

4.1 查询优化器的局限性

4.1.1 关联子查询

MySQL子查询十分糟糕，特别是WHERE条件包含 IN() 的子查询语句。

SELECT * FROM film 
WHERE film_id IN(
	SELECT film_id FROM film_actor WHERE actor_id = 1);
--MySQL会先执行子查询，返回所有actor_id = 1的film_id，所以我们会认为会如下执行：
SELECT * FROM film 
WHERE film_id IN(XXX, XXX, ......, XXX);
--但实际上，MySQL会将外层表压到子查询中，因为它认为这样可以更快的找到数据行：
SELECT * FROM film 
WHERE EXISTS(
	SELECT * FROM film_actor WHERE actor_id = 1 AND film.film_id = film_actor.film_id);

MySQL选择先对file表进行全表扫描，根据返回的film_id逐个执行子查询，当数据量大时这种执行性能会很糟糕，所以我们可以改写查询如下：

--使用内连接代替IN子查询：
SELECT film.* FROM film 
INNER JOIN film_actor USING(film_id)
WHERE actor_id = 1;
--使用函数GROUP_CONCAT拼接一个逗号分隔的列表：
（省略）

4.1.2 UNION取LIMIT时内外层优化

有时我们希望对UNION的结果集只获取前N条记录，可以在每个子句中添加LIMIT，为了保证获取正确的顺序，需要在外层再增加一个全局的 ORDER BY 和 LIMIT 操作。

(SELECT a,b FROM xx1 ORDER BY a)
UNION ALL
(SELECT a,b FROM xx2 ORDER BY a)
LIMIT 20
-- 改写为
(SELECT a,b FROM xx1 ORDER BY a LIMIT 20)
UNION ALL
(SELECT a,b FROM xx2 ORDER BY a LIMIT 20)
ORDER BY a
LIMIT 20;

4.1.3 索引合并优化

WHERE子句包含多个复杂条件时，MySQL可以访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

4.1.4 等值传递

如一个非常大的 IN() 列表，优化器发现有WHERE、ON或USING的子句，将这个列表的值和另外一个表的某个列关联。优化器会将列表都复制应用到关联的各个表中，列表特别大会导致无法高效的从表中过滤记录。

4.1.5 并行执行

MySQL无法利用多核来并行执行查询。

4.1.6 哈希关联

MySQL只支持嵌套循环关联，只能通过建立哈希索引来变相的实现哈希关联

4.1.7 松散索引扫描

MySQL不支持松散索引扫描，无法按照不连续的方式扫描一个索引。假设有索引 (A,B) 查询 SELECT ... FROM TB WHERE B BETWEEN 2 AND 3 因为索引前导字段是A，但查询只指定了B，所以无法使用索引，只能全表扫描。

4.1.8 最大值和最小值优化

SELECT MIN(actor_id) FROM actor WHERE first_name = 'XX';
--因为first_name并没有索引，所以MySQL会进行一次全表扫描，通过SHOW STATUS确认
FLUSH STATUS;
SELECT ...;
SHOW STATUS;
--优化：使用LIMIT重写
SELECT actor_id FROM actor USE INDEX(PRIMARY) WHERE first_name = 'XX' LIMIT 1;

4.2 查询优化器的提示

暂略。

4.3 优化特定类型的查询

4.3.1 优化COUNT()

COUNT(*) 统计结果集的行数，COUNT(row) 统计某个列值的数量，二者含义不同。

--一个查询统计不同颜色
SELECT SUM(IF(color = 'blue', 1, 0)) AS blue, SUM(IF(color = 'red', 1, 0)) AS red FROM items;
SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULL) AS red FROM items;

不需要精确值时可以使用近似值代替，比如EXPLAIN的估算行数。

COUNT() 一般都需要扫描大量的数据行才能获得精确的结果，只能加索引覆盖扫描，如果这样还不能解决性能问题，只能考虑修改应用的架构，比如增加汇总表 / 缓存系统等。

4.3.2 优化关联查询

• 确保ON或USING子句的列上有索引，创建索引时要考虑关联的顺序，当表A和B用列c关联，若优化器的关联顺序的B、A，那么B表就不需要创建索引，一般关联只需在关联顺序的第二张表对应列上创建索引。
• 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
• 升级MySQL时注意关联语法、运算符优先级等其他可能会发生变化的地方，普通关联可能会变成笛卡尔积，不同类型的关联可能会生成不同的结果。

4.3.3 优化子查询

尽可能用关联查询代替子查询。

4.3.4 优化GROUP BY和DISTINCT

MySQL优化器会在内部处理时相互转换这两类查询，使用索引来优化；若索引无法使用，GROUP BY 会使用临时表或文件排序来做分组。

对关联查询做分组，并且按照查找表中的某个列进行分组，采用查找表的标识列效率会较高：

--效率差
SELECT actor.first_name,actor.last_name, count(*)
FROM film_actor
INNER JOIN actor USING(actor_id)
GROUP BY actor.first_name,actor.last_name
--效率高
SELECT actor.first_name,actor.last_name, count(*)
FROM film_actor
INNER JOIN actor USING(actor_id)
GROUP BY actor.actor_id
--这种写法只是在姓名和actor_id直接关联的情况下可以这样用，且有一些问题

4.3.5 优化LIMIT分页

分页操作：使用LIMIT加偏移量实现，加上合适的ORDER BY子句。

偏移量非常大时，比如 LIMIT 10000,20 只返回20条数据抛弃前面10000条记录。优化要么在页面中限制分页的数量，要么优化大偏移量的性能。

• 延迟关联：最简单就是使用索引覆盖扫描，而不是返回所有列。然后根据需要再做一次关联操作返回所有列，这种改法对于偏移量大的情况效率提升比较高。

  SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;
  --优化为：
  SELECT film.film_id, film.description 
  FROM film
  INNER JOIN(SELECT film_id FROM film ORDER BY title LIMIT 50,5) AS lim USING(film_id)

• LIMIT可以转换为已知位置的查询

  --上述查询可改为：
  SELECT film_id, description 
  FROM film
  WHERE position BETWEEN 50 TO 54 
  ORDER BY position;

• LIMIT查询主要问题是OFFSET会导致MySQL扫描大量不需要的行并且丢弃掉，可以记录上次取数据的位置，下次直接从该位置开始扫描：

  --第一次查询，返回主键为16049到16030的租借记录
  SELECT * 
  FROM rental
  ORDER BY rental_id DESC LIMIT 20;
  --下次查询直接从16030开始
  SELECT * 
  FROM rental
  WHERE rental_id < 16030
  ORDER BY rental_id DESC LIMIT 20;

4.3.6 优化 SQL_CALC_FOUND_ROWS

分页的一个常用技巧是在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示，从而获得去掉LIMIT以后满足条件的行数，作为分页的总数，好像是MySQL预测到了总行数，但实际上MySQL需要扫描扫描所有满足条件的行来得到行数。

所以添加此提示后，不管是否需要，MySQL都会扫描所有满足条件的行，而不是满足LIMIT的行数后就终止扫描。

4.3.7 优化 UNION 查询

MySQL总是会通过创建并填充临时表的方式来执行 UNION 查询，所以导致很多优化策略受限（经常要手工将WHERE，LIMIT，ORDER BY放到UNION子查询中以便优化器优化）。

除非必须要服务器消除重复的行，否则一定要使用UNION ALL，没有ALL时MySQL会给临时表增加DISTINCT选项，这会导致整个临时表做唯一性检查，这样的代价非常高。

4.3.8 静态查询分析

Percona Toolkit中的pt-query-advisor能够解析查询日志，分析查询模式，给出所有可能存在潜在问题的查询，并给出足够详细的建议。

4.3.9 使用用户自定义变量

暂略。

4.4 案例

4.4.1 构建一个队列表

• 一个表包含多种类型的记录：未处理的记录、已处理记录、正在处理记录。

• 一个或多个消费者线程在表中查找未处理的记录，然后标记为正在处理，处理完成后再更新为已处理状态。

• 不合理的地方：

  • 随着队列表越来越大和索引深度的增加，找到未处理记录的速度越来越慢；可以将队列表拆分为已处理和未处理两部分来优化。
  • 处理过程一般包括两步：找到未处理记录，然后加锁。前者增加服务器压力，加锁则让各个消费者线程增加竞争。

• 如何让消费者标记正在处理的记录，而不使多个消费者重复处理同一记录？

  • 任何情况都应避免使用 SELECT FOR UPDATE ，会导致大量事务阻塞并等待；

  • 用一个属性列标识正在处理记录的连接ID。

  • ```sql
    BEGIN;
    –查找未有线程处理且状态为待发送的记录ID
    SELECT id
    FROM unsent_emails
    WHERE owner = 0
    AND status = ‘unsent’
    LIMIT 10 FOR UPDATE;
    –result：123，456，789
    –更新这些记录状态，并且将连接ID写入owner
    UPDATE unsent_emails
    SET status = ‘claimed’, owner = CONNECTION_ID()
    WHERE id IN(123, 456, 789);
    COMMIT;

    * 上述SELECT走了索引，但FOR UPDATE锁会使其他查询阻塞，可以改进如下（无需用SELECT查询哪些记录还未被处理，可以用来改写所有的SELECT FOR UPDATE）：
    
      ```sql
      SET AUTOCOMMIT = 1;
      COMMIT;
      --
      UPDATE unsent_emails
      SET status = 'claimed', owner = CONNECTION_ID()
      WHERE owner = 0 
        AND status = 'unsent'
      LIMIT 10;
      SET AUTOCOMMIT = 0;
      SELECT id
      FROM unsent_emails
      WHERE owner = CONNECTION_ID()
        AND status = 'claimed';
      --result：123，456，789
      --更新这些记录状态，并且将连接ID写入owner

• 基础原则：

  • 除非不得已，否则不要使用轮询。
  • 尽量用UPDATE代替 SELECT FOR UPDATE，事务提交越快，持有锁的时间就越短，可以大大减少竞争和加速串行执行效率。
  • 将已经处理完成和未处理数据分开，保证数据集足够小。
  • 无法优化的查询尝试用不同策略来实现相同目的。
  • 任务队列最好能从数据库移除，使用Redis、memcached、Q4M引擎等方案。

4.4.2 计算两点之间的距离

感觉不是常用的场景，暂略。

---

参考：

🔗 《高性能MySQL》