MySQL查询性能优化

6. 查询性能优化

6.1 为什么查询速度会慢

• 查询由一系列子任务组成，如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么让子任务运行得更快。

• 在每一个消耗大量时间的查询案例中，我们都能看到一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。

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

查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化：

• 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。

• 确认MySQL服务器层是否在分析大量超过需要的数据行。

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

• 查询不需要的记录：MySQL先返回全部结果集再进行计算。最简单有效的解决方法就是加上LIMIT。

• 多表关联时返回全部列：只取需要的列

• 总是取出全部列：一些DBA是严格禁止SELECT *的写法的，这样做有时候还能避免某些列被修改带来的问题。

• 重复查询相同的数据：当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出。

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

访问类型从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。速度从慢到快，扫描的行数也是从小到大。在EXPLAIN语句中的type列反应了访问类型。

如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：

• 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了

• 改变库表结构。例如使用单独的汇总表。

• 重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询。

6.3 重构查询的方法

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

MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询是很有必要的。

6.3.2 切分查询

有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询。删除旧的数据就是一个很好的例子。

• 定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

• 将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。

6.3.3 分解关联查询

对关联查询进行分解，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。有如下的优势：

• 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。

• 将查询分解后，执行单个查询可以减少锁的竞争。查询本身效率也可能会有所提升。

• 可以减少冗余记录的查询。在数据库中做关联查询，可能需要重复地访问一部分数据。

6.3 查询执行的基础

当向MySQL发送一个请求的时候，MySQL到底做了什么：

1. 客户端发送一条查询给服务器。

2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。

3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。

4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。

5. 将结果返回给客户端。

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

• MySQL客户端和服务器之间的通信协议是“半双工”的，这意味着要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。

• 这意味着没法进行流量控制。一旦一端开始发生消息，另一端要接收完整个消息才能响应它。

• 多数连接MySQL的库函数都可以获得全部结果集并缓存到内存里，还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。

当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取数据，而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题，但是如果需要返回一个很大的结果集的时候，这样做并不好，因为库函数会花很多时间和内存来存储所有的结果集。如果能够尽早开始处理这些结果集，就能大大减少内存的消耗，这种情况下可以不使用缓存来记录结果而是直接处理。这样做的缺点是，对于服务器来说，需要查询完成后才能释放资源，所以在和客户端交互的整个过程中，服务器的资源都是被这个查询所占用的已。

6.4.2 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。

6.4.3 查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。

• 语法解析器和预处理：MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树。

• 查询优化器：由优化器将其转化成执行计划。

  • 一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

  • MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

  • 优化策略可以简单地分为两种，一种是静态优化（编译时优化），一种是动态优化（运行时优化）。

MySQL如何执行关联查询

• 对于UNION查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询。

• 在MySQL的概念中，每个查询都是一次关联，所以读取结果临时表也是一次关联。

• MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。

• MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个联表无法找到更多的行以后，MySQL返回到上一层次关联表，看是否能够找到更多的匹配记录，依此类推迭代执行。

执行计划

MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。

MySQL总是从一个表开始一直嵌套循环、回溯完成所有表关联。所以，MySQL的执行计划总是如图6-4所示，是一棵左测深度优先的树。

6.4.4 查询执行引擎

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样会生成对应的字节码。

MySQL只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，这些接口也就是我们称为“handler AP’的接口。查询中的每一个表由一个handler的实例表示。

6.4.5 返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放到查询缓存中。MySQL将结果集返回客户端是一个增量、逐步返回的过程。

这样处理有两个好处：服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也让MySQL客户端第一时间获得返回的结果。

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

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

• HIGH_PRIORITY和LOW_PRIORITY：这个提示告诉MySQL，当多个语句同时访问某一个表的时候，哪些语句的优先级相对高些、哪些语句的优先级相对低些。

• DELAYED：这个提示对INSERT和REPLACE有效。MySQL会将使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空闲时批量将数据写入。日志系统使用这样的提示非常有效。

• STRAIGHT_JOIN：这个提示可以放置在SELECT语句的SELECT关键字之后，也可以放置在任何两个关联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联。第二个用法则是固定其前后两个表的关联顺序。

• SQL_SMALL_RESULT和SQL_BIG_RESULT：这两个提示只对SELECT语句有效。它们告诉优化器对GROUP BY或者DISTINCT查询如何使用临时表及排序。SQL_SMALL_RESULT告诉优化器结果集会很小，可以将结果集放在内存中的索引临时表，以避免排序操作。如果是SQL_BIG_RESULT，则告诉优化器结果集可能会非常大，建议使用磁盘临时表做排序操作。

• SQL_BUFFER_RESULT： 这个提示告诉优化器将查询结果放入到一个临时表，然后尽可能快地释放表锁。

• SQL_CACHE和SQL_NO_CACHE：这个提示告诉MySQL这个结果集是否应该缓存在查询缓存中，

6.6 优化特定类型的查询

6.6.1 优化COUNT()查询

COUNT（）是一个特殊的函数，有两种非常不同的作用：它可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的（不统计NULL）。如果在COUNT（）的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数

COUNT() 的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能空时，实际上就是在统计行数。最简单的就是当我们使用COUNT(*) 的时候，这种情况下会忽略所有的列而直接统计所有的行数。如果希望知道的是结果集的行数，最好使用COUNT（*）

• 有时候某些业务场景并不要求完全精确的COUNT值，此时可以用近似值。EXPLAIN 出来的优化器估算的行数就是一个不错的近似值，执行EXPLAIN并不需要真正地去执行查询，所以成本很低。

• 通常来说，COUNT() 都需要扫描大量的行才能获得精确的结果，因此很难优化。在MySQL层面还能做的就只有索引覆盖扫描.

6.6.2 优化关联查询

• 确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。

• 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。

6.6.3 优化子查询

• 最重要的优化建议就是尽可能使用关联查询代替，至少当前的MySQL版本需要这样。

• 如果使用的是MySQL 5.6或更新的版本或者MariaDB，那么就可以直接忽略关于子查询的这些建议了。

6.6.4 优化GROUP BY和DISTINCT

• 在很多场景下，MySQL都使用同样的办法优化这两种查询。MySQL优化器会在内部处理的时候相互转化这两类查询。

• 它们都可以使用索引来优化，这也是最有效的优化办法。

• 当无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组。

• 如果不关心结果集的顺序，而这种默认排序又导致了需要文件排序，则可以使用ORDER BY NULL，让MySQL不再进行文件排序。

6.6.5 优化LIMIT分页

• 优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。

• 这里的“延迟关联”将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的LIMITT子句。

• 如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。

6.6.6 优化UNION查询

• MySQL总是通过创建并填充临时表的方式来执行UNION查询，导致很多优化策略没法很好使用。

• 因此需要手工地将WHERE、LIMIT、ORDER BY等子句“下推“到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化（例如，直接将这些子句冗余地写一份到各个子查询）。

• 除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。

6.6.7 静态查询分析

Percona Toolkit中的pt-query-advisor能够解析查询日志、分析查询模式，然后给出所有可能存在潜在问题的查询，并给出足够详细的建议。这像是给MySQL所有的查询做一次全面的健康检查。它能检测出许多常见的问题。

6.6.8 使用用户自定义变量

• 减少重复计算：在一个复杂的查询中，某个值如果需要在多个地方使用，通常情况下可能需要重复计算这个值。通过将这个值存储在一个用户自定义变量中，可以避免重复计算，从而减少查询的总体资源消耗。

• 编写偷懒的UNION：假设需要编写一个UNION查询，其第一个子查询作为分支条件先执行，如果找到了匹配的行，则跳过第二个分支。在某些业务场景中确实会有这样的需求，比如先在一个频繁访问的表中查找“热”数据，找不到再去另外一个较少访问的表中查找“冷”数据。（区分热数据和冷数据是一个很好的提高缓存命中率的办法）。

  • 下面的查询会在两个地方查找一个用户——一个主用户表、一个长时间不活跃的用户表，不活跃用户表的目的是为了实现更高效的归档：

  • SELECT id FROM users WHERE id=123 UNION ALL SELECT id FROM users_archived WHERE id=123;

• 不仅是在SELECT语句中，在其他任何类型的SQL语句中都可以对变量进行赋值。事实上，这也是用户自定义变量最大的用途。