6.1 为什么查询速度会慢

一个查询由多个子查询构成，消除子查询的满操作，或影响其他查询的子查询是主要的工作。
了解查询的生命周期、清楚查询的时间消耗情况对于优化查询有很大的意义。查询的大致步骤：从客户端，到服务器，解析，生成执行计划，执行，返回结果给客户端。“执行”最为重要的阶段，包含了大量为了检索数据到存储引擎的调用以及后续的数据处理，包括排序、分组等。除此之外还需要浪费时间的地方有：网络，CPU计算，生成统计信息和执行计划、锁等待，I/O，上下文切换等。

6.2 慢查询基础：优化数据访问

查询性能低下基本原因是访问的数据太多。如下步骤分析总是有效：

  1. 确认是否存在检索大量超过需要的数据，包括行和列。
  2. 确认MySQL服务器层是否需要分析大量超过需要的数据。

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

    查询请求超过实际需要的数据，给MySQL带来了额外的负担，增加I/O传输开销，CPU和内存开销等。
    典型案例：
  • 查询不需要的记录

MySQL先返回全部结果集再进行计算。比如查询列表页有100条数据，但只显示前面10条，实际是取出全部的结果集再计算出需要的10条，抛弃大量不需要的数据。最有效的解决方法是在查询后面加上LIMIT（通过ID来找到具体10条开始ID）

  • 多表关联时返回全部列

联表后 SELECT * 会取出所有相关表的列，大量消耗资源。

  • 总是取出全部列

总是用 SELECT * 的查询，是否真的需要所有的列，这会大量消耗资源。

  • 重复查询相同数据

可以缓存相同查询的数据，避免多次给服务器带来额外的负担。

6.2.2 MySQL是否在扫描额外的记录

对于MySQL，最简单的衡量查询开销的三个指标：

  • 响应时间
  • 扫描行数
  • 返回行数

这三个指标会记录到MySQL的慢查询日志中，这可以推算出大概的查询时间。

响应时间

响应时间是两部分之和：服务时间和排队时间。

扫描的行数和返回的行数

理想情况下扫描的行数和返回的行数应该是相同的，但这并不常见。扫描的行数对返回的行数的比率通常在1 : 1 和 10 : 1之间。

扫描的行数和访问类型

在EXPLAIN语句中的type列反应了访问类型，包括全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等，速度从慢到快，扫描行数也从多到少。
一般MySQL使用如下三种方式应用WHERE条件，从优到劣依次为：

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

MySQL不会告诉我们生成结果实际需要扫描的行数，只会告诉生成结果时扫描的行数（实际扫描的行数中的大部分很可能是被WHERE条件过滤掉的，对最终的结果集没有贡献）。
若查询需要扫描数据比返回数据少很多，可以尝试下列方法优化：

  • 使用索引覆盖扫描（把需要用的列都放到索引中）。
  • 改变库表结构（例如使用单独的汇总表）。

  • 重写查询。

    6.3 重构查询的方式

    6.3.1 一个复杂查询还是多个简单查询

    必要情况下，拆分复杂查询是有利的。

    6.3.2 切分查询

    对于一个大的查询有时需要“分而治之”，拆分成若干小查询（比如删除数据）。若一个大的语句一次性完成，可能需要一次性锁住很大数据、占满整个事务日志、耗尽系统资源、阻塞很多查询。

    6.3.3 分解关联查询

    很多高性能应用都会对关联查询进行分解。用分解关联查询的方式重构查询的优势：

  • 让缓存效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象，拆开关联查询为单表查询后，可能有单表查询可以直接获得结果，不需要查询

  • 执行单个查询可以减少锁的竞争
  • 在应用层做关联，更容易做到高性能和可扩展
  • 查询本身效率也可能会有所提升
  • 可以减少冗余记录的查询。
  • 更进一步，这样做相当于在应用中实现了哈希关联，而不是MySQL的嵌套循环关联

    6.4 查询执行的基础

    图6-1展示当向MySQL发送一个请求时，MySQL做了什么：
    image.png
  1. 客户端发送一条查询给服务器
  2. 服务器先检查查询缓存，如果命中了缓存，则返回结果，否则往下
  3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划
  4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询

  5. 将结果返回客户端

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

    MySQL客户端和服务器之间的通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据。所以，无须将一个消息切成小块独立来发送。
    协议简单快速，单也很多地方限制了MySQL，其一是没法进行流量控制。数据接收方必须接收完整个消息才能响应发送方（像来回抛球的游戏：在任一时刻，只有一人能控制球和抛球），客户端和服务端双方都是推送数据。
    客户端用一个单独的数据包将查询传给服务器（参数max_allowed_packet可以控制若查询语句过长，服务端会拒绝接收更多的数据并抛出相应错误）。
    服务器给用户的数据通常很多，由多个数据包组成，当服务器开始响应客户端请求时，客户端必须完整接收整个返回结果，不能只取几条数据就停止接收，接收时有两种方式：

    • 客户端缓存所有结果到内存后，再做后续处理
    • 客户端不缓存，而直接进行处理

如PHP的库函数：

  1. $link = mysql_connect('localhost', 'user', 'password');
  2. $sql = 'SELECT * FROM HUGE_TABLE';
  3. $result = mysql_query($sql, $link);                                // 全缓存后处理
  4. // $result = mysql_unbuffered_query($sql, $link);    // 不缓存，直接处理
  5. while ($row = mysql_fetch_array($result)) {
  6.       // todo
  7. }

查询状态

对于一个MySQL连接（或一个线程），有很多种方式能看到当前状态，比如 SHOW FULL PROCESSLIST 命令，各状态解释：

  • Sleep：线程正在等待客户端发送新的请求
  • Query：线程正在执行查询或正在降结果发送给客户端
  • Locked：在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁（如：InnoDB的行锁），并不会体现在线程状态中
  • Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
  • Copying to tmp table [on dist]：线程正在执行查询，且将其结果集都复制到一个临时表中，这种状态一般要么是在做GROUP BY，要么是文件排序，或UNION操作。若状态带有”on disk”，则表示MySQL正在将一个内存临时表放到磁盘上
  • Sorting result：线程正在对结果集进行排序

  • Sending data：多种情况：线程可能在多个状态之间传送数据，或在生成结果集，或在向客户端返回数据

    6.4.2 查询缓存

    在解析一个查询语句之前，如果查询缓存是打开的，MySQL会先检查查询是否命中查询缓存中的数据，若命中则返回结果，否则会进入下一阶段的处理。

    6.4.3 查询优化处理

    查询的下一生命周期是将一个SQL转换成一个执行计划，MySQL根据这个计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划等。

    语法解析器和预处理

    MySQL解析器：通过关键字将SQL语句解析成一棵“解析树”，使用MySQL语法规则验证和解析查询（语句结构层面）。
    预处理器：根据一些MySQL规则进一步检查解析树是否合法（语句内容层面），验证权限。

    查询优化器

    MySQL使用基于成本的优化器，将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。
    通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。其根据统计信息得来：每个表或索引页面个数、索引基数（不同值的数量）、索引和数据行的长度、索引分布情况。评估时不做缓存层面考虑。
    导致MySQL选择错误执行计划的原因有很多：

  • 统计信息不准确。如：InnoDB因为MVCC的架构而不能维护一个数据表的行数的精确统计信息

  • 执行计划的成不估算不等同于实际执行的成本。如：可能数据恰好在内存中
  • MySQL优化器的最优并不一定和使用者最优一致。因为MySQL是基于成本模型选择最优执行计划
  • MySQL不考虑并发执行的查询
  • MySQL并不是任何时候都基于成本的优化，若存在 MATCH( ) 语句且存在全文索引，则会使用该索引，不考虑别的路径
  • MySQL不考虑不受其成本控制的操作成本，如：执行存储过程或用户自定义函数等，或标识字段的某个独立字段基数很低的情况
  • 有时无法估算所有可能的执行计划，而错过实际最优计划

MySQL优化策略：

  • 静态优化（“编译时优化”），对解析树进行分析而优化
  • 动态优化（“运行时优化”），对运行时变化而动态优化，甚至在查询执行过程中也会重新优化（如：关联操作中，范围检查的执行计划会针对每一行重新评估索引。通过 EXPLAIN 的 Extra 的 "range checked for each record" 来确认）

MySQL能够处理的优化类型（只列举了部分）：

  • 重新定义关联表的顺序
  • 将外连接转化成内连接
  • 使用等价变换规则

如：(a < b AND b = c) AND a = 5，会改写为：b > 5 AND b = c AND a = 5

  • 优化COUNT( )、MIN( ) 和MAX( )

B-Tree中，最小值与最大值分别的节点页的最左和最右。通过count(*)获取表所有数据通常可以使用存储引擎提供的一些优化（MyISAM维护了一个变量来存放该值）

  • 预估并转化为常数表达式

当MySQL检测到一个表达式可以转化为常数的时候，就会将其作为常数进行优化

  • 覆盖索引扫描
  • 子查询优化
  • 提前终止查询

当查询不可能存在的值时，或使用 LIMIT 等

  • 等值传播

如：Select film.film_id FROM film LEFT JOIN film_actor USING(film_id) WHERE film.film_id > 500; 的优化，会自动识别 film.film_id > 500 对 film_actor 字段也适用

  • 列表IN( )的比较

MySQL的IN( )并不是等价转换成OR（O(n)），而是先对IN数值排序，再通过二分查找（O(log n)）来确定是否满足条件
若优化器无法给出最优的结果，可以再查询加 hint 提示，也可以重写查询，或重新设计更优的库表结构，或添加更合适的索引。

数据和索引的统计信息

MySQL服务器层没有表和索引的统计信息，因此在生成执行计划的时候需要从存储引擎获取该数据，包括：每个表和索引有多少个页面及其基数是多少，数据行和索引长度、索引的分布信息等。

MySQL如何执行关联查询

在MySQL中，每个查询都是一次关联，所以读取结果临时表也是一次关联。
MySQL的关联执行策略：任何关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后嵌套循环到下个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各表匹配的行，返回查询中需要的各个列。

  1. SELECT tbl1.col1, tbl2.col2 FROM tbl1 INNER JOIN tbl2 USING(col3) WHERE tbl1.col1 IN(5, 6)

image.png
根据优化器执行的路径绘制出对应的“泳道图”，如图6-2，从左上到右下。
本质上，MySQL对所有的类型的查询都以同样的方式运行（如：

  • 在FROM子句中遇到子查询时，先执行子查询并将结果放到一个临时表（没有索引）
  • 执行UNION查询时也使用临时表
  • 遇到右外连接时，将其改写为等价的左外连接

）。不过不是所有查询都可以转换成上面的形式（如：全外连接，可能也是MySQL不支持全外连接的原因）。

执行计划

与很多关系型数据库不同，MySQL不是生成查询字节码来执行查询，而是生成一棵查询的指令树，然后通过存储引擎执行完成这棵指令树返回结果。（对查询执行 EXPLAIN EXTENDED 和 SHOW WARNINGS 可以看到重构出的查询）
MySQL总是从一个表开始一直嵌套循环、回溯完成所有表关联，所以MySQL的执行计划总是如图6-4所示，是一棵左侧深度优先树。
image.png

关联查询优化器

通常多表关联可以又多种不同的关联顺序，而优化器通过评估不同顺序的成本来选择一个代价最小的关联顺序。
例：

  1. SELECT film.film_id, film.titlr, film.release_year, actor.actor_id,
  2.     actor.first_name, actor.last_name
  3. FROM film
  4. INNER JOIN film_actor USING(film_id)
  5. INNER JOIN actor USING(actor_id)

获取执行计划时，关联顺序是actor -> film_actor -> film，而非film -> film_actor -> actor，原因是首先查找表actor可以减少数据过滤量，减少循环嵌套次数。而后续再关联查询都是通过唯一id关联，只需要一次查找就能查找出来，所以是一样的效率。（可以使用 STRAIGHT_JOIN 来使用原顺序进行执行）
搜索空间：如果有n个表的关联，那么需要检查 n! 种关联顺序，则所有的可能的执行计划就是搜索空间。（例：当有10个表关联时，搜索空间为 3 628 800 ）当搜索空间非常大的时候，优化器不可能逐一评估每一种关联关系顺序的成本。当需要关联的表超过 optimizer_search_depth 的限制时，就会选择“贪婪”搜索模式。

排序优化

排序是成本很高的操作，当不能使用索引生成排序结果时，需要进行“文件排序(filesort)”使用“快速排序”算法，数据量小则在内存“排序缓冲区”中进行，大则需要使用磁盘。（内存不够时，将数据分块“快排”，存到磁盘，再合并返回排序结果）
MySQL两种获取需排序数据方法：

  • 两次传输（旧版本使用）

读取行指针和需要排序的字段，对其进行排序后，再根据排序结果读取数据行。（需要大量I/O，可参与排序的每次数据增大）

  • 单次传输（新版本使用）

当查询需要所有的列总长度不超过 max_length_for_sort_data 时，读取排序数据行所有列，直接排序返回结果。（减少I/O，需要多次合并）
排序所需空间可能因为有变长列，导致所有空间比原数据行长。
在关联查询的时候如果需要排序，MySQL有两种方式排序：

  • 如果ORDER BY的列来自第一个表，则在关联处理第一个表的时候就进行文件排序。（EXPLAIN 的 Extra 会显示 Using filesort ）
  • 此外，会先将关联结果存放到临时表，在所有关联结束后，再进行排序。（EXPLAIN 的 Extra 会显示 Using temporary; Using filesort ）

    6.4.4 查询执行引擎

    MySQL在优化阶段为每个表创建了一个 handler 实例（可获取表信息），在查询执行阶段，有大量的操作需要调用存储引擎实现的接口（称为 handler API 的接口）来完成。
    存储引擎的接口有很多功能，但底层接口只有十几个，通过搭配组合使用，实现复杂功能。

    注：并不是所有操作都由handler完成（如：表锁），但handler可能实现了自己的级别的、更细粒度的锁。

6.4.5 返回结果给客户端

  1. 返回结果，即使不需要返回结果也会返回影响行数。返回过程是增量、逐步的过程，可以从开始生成第一个结果时就逐步返回。满足MySQL客户端/服务器通信协议的封装包，通过TCP传输协议（可能会进行缓存批量传输）。
  2. 缓存查询结果，如果查询可以被缓存。

    6.5 MYSQL查询优化器的局限性

    6.5.1 关联子查询

    MySQ的 IN() 子查询实现得糟糕。
    例：找到演员 Penelope Guiness 参演过的所有影片信息。很自然会写查询：
    1. SELECT * FROM film WHERE film_id IN (
    2. SELECT film_id FROM film_actor WHERE actor_id = 1)
    因为 MySQL 对 IN( ) 列表中的选项有专门的优化策略，一般认为会先执行子查询，但 Mysql 是将相关的外层表压到子查询中，也就是改写成：
    1. SELECT * FROM film WHERE EXISTS (
    2.  SELECT * FROM film_actor WHERE actor_id = 1
    3.  AND film_actor.film_id = fiml.film_id)
    这时子查询需要根据 film_id 来关联外部表 film，所以无法先执行这个子查询。通过 EXPLAIN 的 SELECT_TYPE 可以看到子查询是一个相关子查询 DEPENDENT SUBQUERY：
    image.png
    MySQL 执行计划显示，先对 film 表进行全表扫描，根据返回的 film_id 逐个执行子查询。
    优化改写1：
    1. SELECT film.* FROM film INNER JOIN film_actor USING(film_id) WHERE actor_id = 1
    优化改写2：
    使用函数 GROUP_CONCAT() 在 IN( ) 中构造一个由逗号分隔的列表，使用 EXISTS() 等效改写。
    1. SELECT * FROM film WHERE EXISTS (
    2. SELECT * FROM film_actor WHERE actor_id = 1
    3. AND film_actor.film_id = film.film_id)

    如何用好关联子查询

    并不是所有关联子查询的性能都会很差。有时关联子查询是一种合理、自然，甚至性能最好的写法。
    例1：等效查询一，查询二 ```sql — 查询一 EXPLAIN SELECT film_id, language_id FROM film WHERE NOT EXISTS ( SELECT * FROM film_actor WHERE film_actor.film_id = film.film_id)

— 查询二 EXPLAIN SELECT film.film_id, film.language_id FROM film LEFT JOIN film_actor USING(film_id) WEHRE film_actor.film_id IS NULL

  1.            ![image.png](https://cdn.nlark.com/yuque/0/2021/png/1204927/1633398928495-b9407525-a24e-4342-baaa-706e58c21635.png#clientId=u949c1772-117a-4&from=paste&height=356&id=u72c42106&margin=%5Bobject%20Object%5D&name=image.png&originHeight=356&originWidth=279&originalType=binary&ratio=1&size=32437&status=done&style=shadow&taskId=u9321e642-2d73-41b5-bafd-035ee5aed82&width=279)       ![image.png](https://cdn.nlark.com/yuque/0/2021/png/1204927/1633398953138-735bed00-2e0a-4f2f-89ee-5d8d89a6abb3.png#clientId=u949c1772-117a-4&from=paste&height=359&id=u8c00d5cb&margin=%5Bobject%20Object%5D&name=image.png&originHeight=359&originWidth=351&originalType=binary&ratio=1&size=35593&status=done&style=shadow&taskId=u37a3e5b8-91e4-4674-973d-6a2f6295859&width=351)<br />区别：
  3. - 表 film_actor 的访问类型一个是 DEPENDENT SUBQUERY，一个是SIMPLE，是因为写法不同导致
  4. - 表 film，查询二的 EXTRA 中没有 “Using where“
  5. - 表 film_actor 的 EXTRA 有 `Not exists` ，这时前面章节提到的 `提前终止算法` ，用来避免在表 film_actor 的索引中读取任何额外的行，这等效于 NOT EXISTS查询，一旦匹配到一行数据，就立刻停止扫描。
  7. 结果：<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/1204927/1633399848865-1f5fbea9-ec60-4258-83d1-1625e2664edf.png#clientId=u949c1772-117a-4&from=paste&height=100&id=u0ac1e584&margin=%5Bobject%20Object%5D&name=image.png&originHeight=100&originWidth=330&originalType=binary&ratio=1&size=14594&status=done&style=shadow&taskId=uf0ac4b12-a0b7-4e74-8992-6c200b9ff9f&width=330)<br />子查询写法要慢些。
  9. ---
  11. 有时子查询写法会更快，如当返回结果中只有一个表中的某些列的时候。<br />例2：查询所有包含同一个演员参演的电影
  12. ```sql
  13. -- 查询一
  14. SELECT DISTINCT film.film_id FROM film INNER JOIN film_actor USING(film_id)
  16. -- 查询二
  17. SELECT film_id FROM film WHERE EXISTS(
  18.     SELECT * FROM film_actor WHERE film.film_id = film_actor.film_id)

区别：

  • 查询二使用 EXISTS 写的语句词义简单明了
  • 查询二不需要使用 DISTINCT 和 GROUP BY，而一旦用了他们，在查询过程会产生临时表

结果：
image.png
子查询写法要快些。

6.5.2 UNION的限制

有时，MySQL无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。
如下例：原语句会取出 actor 和 customer 所有满足条件的数据存到临时表中，再取出前20条数据。而优化语句则会取出 actor 和 customer 各最多20条数据（当然也是存放到临时表中），再取出前20条数据。

  1. -- 原语句
  2. (SELECT first_name, last_name FROM actor ORDER BY last_name) UNION ALL
  3. (SELECT first_name, last_name FROM customer ORDER BY last_name) LIMIT 20;
  5. -- 优化语句
  6. (SELECT first_name, last_name FROM actor ORDER BY last_name limit 20) UNION ALL
  7. (SELECT first_name, last_name FROM customer ORDER BY last_name LIMIT 20) LIMIT 20;

6.5.3 索引合并优化

上一章已讨论过，当 WHERE 子句中包含多个复杂条件的时候，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

6.5.4 等值传递

某些时候，等值传递会带来意想不到的额外消耗。
例如，有两表关联查询，优化器会将表1满足条件的数据复制应用到关联表的各个表中，当移动的数据非常多时，会导致优化和执行都变慢。

6.5.5 并行执行

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

6.5.6 哈希关联

MySQL并不支持哈希关联（只支持嵌套循环关联），可以通过第5章“创建自定义哈希索引”来模拟实现。

6.5.7 松散索引扫描

MySQL不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。（松散索引扫描相当于Oracle中的跳跃索引扫描）
例：有索引(a, b)，查询

  1. SELECT ... FROM tbl WHERE b BETWEEN 2 AND 3;

image.png
MySQL 5.0 之后的版本，某些特殊场景下可以使用松散索引扫描的。如，在一个分组查询中需要找到分组的最大值和最小值。

  1. SELECT actor_id, MAX(film_id) FROM film_actor GROUP BY actor_id

其执行计划为：
image.png
在 EXPLAIN 的 Extra 显示 "Using index for group-by" ，表示因为 group by 使用松散索引扫描。
MySQL 5.6 之后，松散索引扫描的一些限制会通过 “索引条件下推(ICP: index condition pushdown)” 的方式解决。

6.5.8 最大值和最小值优化

对于 MIN( ) 和 MAX( ) 查询，MySQL的优化做得并不好。
如下例，没有 first_name 字段的索引，主键索引是严格按照 actor_id 字段的大小顺序排列的：

  1. SELECT MIN(actor_id) FROM actor WHERE first_name = 'PENELOPE';

MySQL 选择使用全表扫描来执行。优化办法是移除MIN( )，利用主键的自增来处理：

  1. SELECT actor_id FROM actor USE INDEX(PRIMARY) WHERE first_name = 'PENELOPE' LIMIT 1;

6.5.9 在同一个表上查询和更新

MySQL 在默认隔离级别下不允许对同一个表同时进行查询和更新。
如下例：

  1. -- 原语句
  2. UPDATE tbl AS outer_tbl SET cnt = (
  3.     SELECT count(*) FROM tbl AS inner_tbl WHERE inner_tbl.type = touter_tbl.type);
  5. -- 改写语句
  6. UPDATE tbl INNER JOIN (
  7.   SELECT type, count(*) AS cnt FROM tbl GROUP BY type
  8. ) AS der USING(type) SET tbl.cnt = der.cnt;

可以通过使用生成临时表的形式来绕过限制。

6.6 查询优化器的提示（hint）

如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示（hint）来控制最终的执行计划。如下：

  • HIGH_PRIORITY 和 LOW_PRIORITY

用于告诉 MySQL 当有多个语句同时访问某一个表的时候，哪些语句的优先级相对高或低，本质上只是改变了语句的运行顺序。只对有表锁的引擎有效，比如 MyISAM 有效，InnoDB 无效。
HIGH_PRIORITY 用于 SELECT、INSERT 的时候，MySQL 会将此语句重新调度到所有正在等待表锁的语句之前，实际是将其放在表的队列的最前面。
LOW_PRIORITY 则正好相反用于 SELECT、INSERT、UPDATE、DELETE，只要队列中还有需要访问同一个表的语句，就等待至其完成后再运行。
这两个提示可能导致并发插入被禁用等情况，而导致严重性能降低。

  • DELAYED

用于将语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空闲时批量将数据写入。
部分引擎可用，该提示对 INSERT 和 REPLACE 有效。日志系统使用这样的提示有效，或其他需要写入大量数据而客户端不需要等待单条语句完成 I/O 的应用。会导致函数 LAST_INSERT_ID( ) 无法正常工作。

  • STRAIGHT_JOIN

用于在 MySQL 无法选择正确的表关联顺序时，给定关联顺序的作用。

  1.   - 让查询中所有的表按照在语句中出现的顺序进行关联（在 SELECT 关键字后使用）
  2.   - 固定前后两个表的关联顺序（在两个关联表的名字之间使用）
  • SQL_SMALL_RESULT 和 SQL_BIG_RESULT

用于对使用 SELECT 语句在 GROUP BY 或 DISTINCT 时，建议优化器使用内存或磁盘的临时表。

  1.   - SQL_SMALL_RESULT 表示结果少，可以在内存处理
  2.   - SQL_BIG_RESULT 表示结果多，建议在磁盘处理
  • SQL_BUFFER_RESULT

告诉优化器将查询结果放入到一个临时表，然后尽快释放表锁。由服务器端缓存。

  • SQL_CACHE 和 SQL_NO_CACHE

告诉 MySQL 这个结果集是否应该缓存在查询缓存中。

  • SQL_CALC_FOUND_ROWS

让 MySQL 返回的结果集包含更多信息，返回 LIMIT 要求的结果集总数。
可以通过函数 FOUND_ROW( ) 获得这个值。

  • FOR UPDATE 和 LOCK IN SHARE MODE

用于控制 SELECT 语句的锁机制，对符合查询条件的数据行加（写/读）锁。
只对实现了行级锁的引擎有效，InnoDB 内置支持这两个提示。另外，这两个提示会让某些优化无法正常使用（例：索引覆盖扫描），InnoDB 不能在不妨问主键的情况下排他地锁定行，因为行的版本信息保存在主键中。因此要防止滥用，而导致服务器锁争用问题。
INSERT…SELECT 语句不需要这两个提示，会默认加上读锁（可禁用）。

  • USE INDEX 、IGNORE INDEX 和 FORCE INDEX

告诉优化器使用或者不使用哪些索引来查询，其中 FORCE INDEX 和 USE INDEX 基本相同，除了一点：FORCE INDEX 告诉优化器全表扫描的成本会远高于索引扫描。
在 MySQL 5.1 和之后版本可以通过新增选项 FOR ORDER BY 和 FOR GROUP BY 来指定是否对排序和分组有效。
在 MySQL 5.0 和之后版本，新增了控制参数：

  1.   - optimizer_search_depth：控制优化器在穷举执行计划时的限度。（查询长时间处于“Statistics”就可以考虑调低此参数）
  2.   - optimizer_prune_level：默认打开，让优化器根据需要扫描的行数来决定是否跳过某些执行计划。
  3.   - optimizer_switch：包含了一些开启/关闭优化器特性的标志位。（例： MySQL 5.1 中可以用于控制禁用索引合并的特性等）

6.7 优化特定类型的查询

6.7.1 优化 COUNT( ) 查询

COUNT( ) 的作用

  • 统计某个列值的数量，需要列值为非空

  • 统计行数，使用 count(*)

    关于 MyISAM 的神话

    只有没有任何 WHERE 条件的 COUNT() 才非常快，由引擎特性决定，无须实际地去计算表的行数。否则和别的引擎的没有任何的不同。
    如果 MySQL 知道某列 col 不可能为 NULL 值，那么会将 COUNT(col) 表达式优化为 COUNT(    ) 。

    简单的优化

    可以利用 MyISAM 在 COUNT(*) 全表非常快的特性，再 count 反义词义，进而作减法来获取需要的 count 的数量。

    使用近似值

    某些业务场景（例如：统计实时的pv uv）并不要求完全精确的 COUNT 值，此时可以通过近似值来代替。用 EXPLAIN 出来的优化器估算行数 row 是个不错的近似值，不需要真正地执行查询，所以成本很低。
    退而求其次，可以模糊一些查询条件进行统计，把需要精确过滤而不能快速定位的语句删除掉，以及避免文件排序（例如：删除 distinct ），也是办法。

    更复杂的优化

    当需要精确的统计值，可能需要通过建立覆盖索引来做统计了。

    6.7.2 优化关联查询

  • 确保 ON 和 USING 字句中的列上有索引。（例如：执行顺序是 A -> B，通过 k 来关联，则要确保 B 表的 k 列有索引，否则查找值需要全表扫描）

  • 确保 GROUP BY 和 ORDER BY 的表达式只涉及一个表，才有可能通过索引来优化

    6.7.3 优化子查询

    MySQL 5.6 之前的版本，自查询最好通过关联查询来替代。

    6.7.4 优化 GROUP BY 和 DISTINCT

    很多场景下，这两种查询都是共同的，使用同样的处理方式来处理，或相互转化。
    使用索引优化是最有效的办法。当无法使用索引时， GROUP BY 使用两种策略：临时表或文件排序来做分组。通过前面讨论过的 hints 的 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 来提示优化器按照希望的方式运行。
    如果需要对关联查询做分组，并且按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高。如： ```sql — 原 SQL 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;

— 优化 SQL SELECT actor.first_name, actor.last_name, COUNT(*) FROM film_actor INNER JOIN actor USING(actor_id) GROUP BY actor.actor_id

  1. 当 SQL 无法改写成如上形式时，甚至 MySQL 服务器设置禁用这样的 SQL_MODE，可以通过 `MIN( )` 或 `MAX( )` 来绕过限制。<br />在分组查询中直接使用非分组列通常不是好主意，因为结果通常是不确定的，当索引改变，或优化器选择不同策略都可能导致结果不一样。<br />如果查询使用分组查询，且结果不需要排序，可以使用 `ORDER BY NULL` 来避免默认的文件排序（结果集自动按照分组的字段进行排序）。<br />可以在 GROUP BY 子句中直接使用 DESC 或 ASC 关键字，使分组的结果集按照需要的方向排序。
  2. <a name="ZUj2E"></a>
  3. ### 优化 GROUP BY WITH ROLLUP
  4. 最好将 `WITH ROLLUP` 转移到应用程序中处理。
  5. <a name="TwR2s"></a>
  6. ## 6.7.5 优化 LIMIT 分页
  7. 系统需要进行分页时，通常会使用 LIMIT 加偏移量的办法实现，同时还有 ORDER BY 。若有对应的索引效果不错，否则需要大量 filesort 操作。通常会扫描大量数据，但前面的大量的数据都不符合结果要求丢弃掉了。<br />当偏移量非常大时，优化方式：
  9.    - 在页面中限制分页的数量
  10.    - 优化大偏移量的性能
  11. <a name="bOSM6"></a>
  12. ### 优化分页查询
  13. 最简单的办法时尽可能使用索引覆盖扫描，减少条件列，再根据需要做关联再返回所需要的列。例：
  14. ```sql
  15. -- 原 SQL
  16. SELECT film_id, description FROM film ORDER BY title LIMIT 50, 5;
  18. -- 优化 SQL
  19. SELECT film.film_id, film.description FROM film INNER JOIN (
  20.   SELECT film_id FROM film ORDER BY title LIMIT 50, 5
  21. ) AS lim USING(film_id);

通过“延迟关联”的方式提升查询效率。
也可以将 LIMIT 查询转换成位置的查询。例：若在一个位置列上有索引，并且预先计算出边界值，则上面 SQL 可以改写成：

  1. SELECT film_id, description FROM film
  2. WHERE position BETWEEN 50 AND 54 ORDER BY position;

若可以通过传入单调增加的主键值来跳过不需要的列，是可行的，也是高效的：

  1. SELECT * FROM rental WHERE rental_id < 16090 ORDER BY rental_id DESC LIMIT 20;

还有其他办法，使用冗余的表来存取。

6.7.6 优化 SQL_CALC_FOUND_ROWS

用于返回结果集数量，这是个精确值，需要扫描所有满足条件的数据，需要付出高昂的代价。
优化方向：

  • 把总数量改成下一页
  • 通过 EXPLAIN 的 rows 来返回一个近似值

    6.7.7 优化 UNION 查询

    MySQL 总是通过创建并填充临时表的方式来执行 UNION 。因此很多优化策略在 UNION 查询中无法很好地使用。经常需要手工地将 WHERE、LIMIT、ORDER BY 等字句下推到 UNION 的各个子查询中。
    若非必须通过 MySQL 来消除重复行，查询 UNION 需要改成 UNION ALL，这可以避免 MySQL 给临时表加上 DISTINCT，也就是对数据做唯一性检查。

    6.7.8 静态查询分析

    6.7.9 使用用户自定义变量

    用户自定义变量是一个用来存储内容的临时容器，在连接 MySQL 的整个生命周期都存在。可以如下定义，使用： ```sql — 定义 SET @one := 1; SET @min_actor := (SELECT MIN(actor_id) FROM actor); SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK;

— 使用 SELECT … WHERE col <= @last_week;

  1. 不能使用用户自定义变量的场景：
  3.    - 使用用户自定义常量，无法使用查询缓存
  4.    - 不能在使用常量或者标识符的地方使用自定义变量（如：表明，列名和 LIMIT ）
  5.    - 用户自定义变量的生命周期在一个链接中有效
  6.    - 如果使用连接池或持久化连接，自定义变量可能让查询出bug
  7.    - 不同 MySQL版本之间可能有兼容性问题
  8.    - 不能显式地声明自定义变量的类型，其是一个动态类型（如弱类型的php，js等）
  9.    - MySQL 优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想的方式运行
  10.    - 赋值的顺序和时间点并不总是固定的，这以来优化器的决定
  11.    - 赋值符号 := 优先级非常低，注意使用括号
  12.    - 使用为定义变量不会产生语法错误需要注意，否则容易犯错
  13. <a name="ZqlVk"></a>
  14. ### 优化排名语句
  15. ```sql
  16. SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
  17. SELECT actor_id,
  18.     @curr_cnt := COUNT(*) AS cnt,
  19.   @rank            := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank,
  20.   @prev_cnt := @curr_cnt AS dummy
  21. FROM film_actor GROUP BY actor_id ORDER BY cnt DESC LIMIT 10;

有时候会导致一些奇怪的问题，排查起来可能比较困难，不如通过两次查询解决来的快。第一次查询查出结果，第二次用来计算结果（这个过程可以移动到代码程序中来完成，不占用 MySQL 的资源）。

避免重复查询刚刚更新的数据

例：希望获取刚更新的结果，往往需要两次查询（update后，select一次），可以通过自定义变量来优化（也需要两次查询，但第二次查询不需要查表）

  1. -- 原 SQL
  2. UPDATE t1 SET updated_at = NOW() WHERE id = 1;
  3. SELECT updated_at FROM t1 WHERE id = 1;
  5. -- 自定义变量
  6. UPDATE t1 SET updated_at = NOW() WHERE id = 1 AND @now := NOW();
  7. SELECT @now;

统计更新和插入的数量

当使用了 INSERT ON DUPLICATE KEY UPDATE 时，如果需要获取插入行数，可以操作：

  1. INSERT INTO t1(c1, c2) VALUES (4,4), (2,1), (3,1)
  2. ON DUPLICATE KEY UPDATE
  3.     c1 = VALUES(c1) + (0 * (@x := @x + 1));

确定取值的顺序

使用用户自定义变量的一个常见问题是没有注意到在赋值和读取变量的时候可能是在查询的不同阶段，导致查询结果不符合预期，解决办法是让变量的赋值和取值发生在执行查询的同一阶段。

注：由于复杂的先后顺序容易搞错，所以不建议使用自定义变量来参与查询执行过程

例：在 select 和 where 中，因为分别是在查询执行的不同阶段被执行的语句。

  1. -- 原 SQL
  2. SET @rownum := 0;
  4. SELECT actor_id, @rownum := @rownum + 1 AS cnt
  5. FROM actor
  6. WHERE @rownum <= 1;
  8. -- 调整 SQL
  9. SET @rownum := 0;
  11. SELECT actor_id, @rownum AS rownum
  12. FROM actor
  13. WHERE (@rownum := @rownum + 1) <= 1;

编写偷懒的 UNION

场景：若查表一没结果则查表二（冷热数据区分，以提高缓存命中率）
例：

  1. -- 原 SQL
  2. SELECT id FROM users WHERE id = 123
  3. UNION ALL
  4. SELECT id FROM users_archived WHERE id = 123;
  6. -- 优化 SQL
  7. SELECT GREATEST(@found := -1, id) AS id, 'users' AS which_tbl
  8. FROM users WHERE id = 123
  9. UNION ALL
  10.     SELECT id, 'users_archived'
  11.   FROM users_archived WHERE id = 1 AND @found IS NULL
  12. UNION ALL
  13.     SELECT 1, 'reset' FROM DUAL WHERE (@found := NULL) IS NOT NULL;

用户自定义变量的其他用处

有如下用法：

  • 查询运行时计算总数的平均数
  • 模拟 GROUP 语句中的函数 FIRST() 和 LAST()
  • 对大量数据做一些数据计算
  • 计算一个大表的 MD5 散列值
  • 编写一个样本处理函数，当样本中的数值超过某个边界值的时候将其变成0
  • 模拟读 / 写游标

  • 在 SHOW 语句的 WHERE 字句中加入变量值

    6.8 案例学习

    6.8.1 使用 MySQL 构建一个对列表

    使用 MySQL 实现队列表，很多系统在搞流量、高并发情况下处理不好。
    例：可以有发邮件业务，有状态三个（未处理，正在处理，已处理），有多个消费脚本在处理，这时就可能出现同一行记录被多个脚本同时消费的问题。
    解决办法：

  • 使用消息队列（rabbitmq 之类的）来处理

  • 通过 redis 对某脚本读取到的数据加锁（redis 单线程来处理）
  • 通过 mysql 读的时候加互斥锁，select for update（这可能会让其他查询进入等待，导致性能问题）
  • 通过 AUTOCOMMIT + CONNECTION_ID( ) 来解决

    AUTOCOMMIT + CONNECTION_ID( )

    所有的 SELECT FOR UPDATE 都可以使用类似的方法改写。 ```sql SET AUTOCOMMIT = 1;

COMMIT;

UPDATE unsent_emails SET status = ‘claimed’, owner = CONNECTION_ID() WHERE owner = 0 AND status = ‘unset’ LIMIT 10;

SET AUTOCOMMIT = 0;

SELECT id FROM unsent_emails WHERE owner = CONNECTION_ID() AND status = ‘claimed’;

  1. 那些正在被进程处理，而进程意外退出的情况，只需定期将状态改回再处理一次就可以了。
  3.    1. 执行 SHOW PROCESSLIST，获取当前工作线程ID，有（10， 20， 30）
  4.    1. UPDATE 10分钟死掉的线程：
  5. ```sql
  6. UPDATE unsent_emails
  7.     SET owner = 0, status = 'unsent'
  8.   WHERE owner NOT IN (0，10，20，30) AND status = 'claimed'
  9.   AND ts < CURRENT_TIMESTAMP - INTERVAL 10 MINUTE;

6.8.2 计算亮点之间的距离

不建议用 MySQL 做太复杂的空间信息存储（PostgreSQL 是不错的选择）。
例：计算以某点为中心，一定半径内的所有点。已有下存储表：

  1. CREATE TABLE locations (
  2.   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  3.   name VARCHAR(30),
  4.   lat FLOAT NOT NULL,
  5.   lon FLOAT NOT NULL
  6. );
  7. INSERT INTO locations(name, lat, lon)
  8.     VALUES('Charlottesville, Viginia', 38.03, -78.48),
  9.               ('Chicago, Illinois', 41.85, -87.65),
  10.         ('Washigton, DC', 38.89, -77.-4);

假设地球是圆球形的

方案一：

计算出弧度乘以地球半径
R * ACOS(COS(latA) * COS(latB) * COS(lonA - lonB) + SIN(latA) * SIN(latB) 

  1. SELECT * FROM locations WHERE 9879 * ACOS(
  2.   COS(RADIANS(lat)) * COS(RADIANS(38.03)) * COS(RADIANS(lon) - RADIANS(-78.48))
  3.       + SIN(RADIANS(lat)) * SIN(RADIANS(38.03))
  4. ) <= 100;

缺点：无法使用索引；非常消耗性能。
优化方向：是否需要这么精确的计算

  • 实际距离不是计算的弧线距离，可能需要绕弯路
  • 可以参考使用邮政编码来确定个人所在地区，再查找邮政编码一样的目的地

因此通常不需要精确计算。

方案二：

我们是否可以不搜索圆形而搜索正方形？边长200英里的正方形，顶点到中心的距离约141英里，实际需要的是100英里，相差不远。
根据正方形公式计算弧度为0.0253（100英里）的中心到边长距离：

  1. SELECT * FROM locations
  2. WHERE lat BETWEEN  38.03 - DEGREES(0.0253) AND 38.03 + DEGREES(0.0253)
  3.     AND lon BETWEEN -78.48 - DEGREES(0.0253) AND 78.48 + DEGREES(0.0253);

使用索引来优化优化

添加 (lat, lon) 或 (lon, lat) ，但无法高效利用索引，因为是范围查询。
这时可以使用 IN( ) 来进行优化：

  1. 新增两列，用于存储经纬度的近似值 FLOOR( )
  2. 计算中心点的某个范围的近似范围的经纬度值（地板值，天花板值）
  3. 生成 IN( ) 列表中的整数，再次查询
  4. 剔除误差（因为目前得到的是正方形距离结果），这时可以直接使用最大圆公式或毕达哥拉斯定理
    1. UPDATE locations
    2. SET lat_floor = FLOOR(lat), lon_floor = FLOOR(lon);
    ```sql SELECT FLOOR( 38.03 - DEGREES(0.0253)) AS lat_lb, CEILING( 38.03 + DEGREES(0.0253)) AS lat_ub, FLOOR(-78.48 - DEGREES(0.0253)) AS lon_lb, CEILING(-78.48 + DEGREES(0.0253)) AS lon_ub,

— 结果 lat_lb, lab_ub, lon_lb, lon_ub 36, 40, -80, -77

  1. ```sql
  2. SELECT * FROM locations
  3. WHERE lat BETWEEN  38.03 - DEGREES(0.0253) AND 38.03 + DEGREES(0.0253)
  4.     AND lon BETWEEN -78.48 - DEGREES(0.0253) AND 78.48 + DEGREES(0.0253)
  5.   AND lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80, -79, -78, -77);
  1. SELECT * FROM locations 
  2. WHERE lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80, -79, -78, -77)
  3.     AND 9879 * ACOS(
  4.     COS(RADIANS(lat)) * COS(RADIANS(38.03)) * COS(RADIANS(lon) - RADIANS(-78.48))
  5.       + SIN(RADIANS(lat)) * SIN(RADIANS(38.03))
  6.   ) <= 100;

6.9 总结