高性能MySQL -- 查询性能优化

一般来说一个好的程序：查询优化，索引优化，库表结构要同时进行优化。今天我们来讲一下查询优化。

我们需要对MySQL的架构有基本认知，所以这里贴一张图大家看看：

图片来自于《小林coding》

为什么从查询会慢？

查询的生命周期大概可以按照如下顺序来看：从客户端到服务器，然后在服务器上进行语法解析，生成执行计划，执行，并给客户端返回结果。执行是整个生命周期中最重要的一个阶段，其中包括了大量为了检索数据对存储引擎的调用以及调用后的数据处理，包括排序，分组等。在这些过程中有很多地方需要消耗大量时间，例如：网络，CPU计算，生成统计信息和执行计划，锁等待（互斥等待）。因为查询可能会很慢。

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

我们需要注意两个点：

• 确认应用程序是否在检索大量且不必要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。
• 确认MySQL服务器层是否在分析大量不需要的数据行。

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

以下是一些典型案例：

• 查询了不需要的数据，没有加limit，但是前端一个分页只能显示一部分，而你把数据全部查了给前端了。
• 多表联接时返回全部列，但是实际上只需要返回你需要的列就可以了，没有必要的全部。
• 总是取出全部的列，每次当你使用SELECT*的时候，你都必须思考清楚你是不是真的需要所有列的数据。但是取出全部的列在有的时候也并不是一件坏事，我们可能有缓存机制，取出全部的列可能会对缓存机制有好处，但是你必须知道这样做的代价是什么。
• 查复查询相同的数据：遇到常用的数据我们一定要用缓存存起来。

MySQL是否在扫描额外的记录

在MySQL中有3个最简单的衡量查询开销的指标：

1. 响应时间
2. 扫描的行数
3. 返回的行数

这三个指标会被记录到MySQL的慢日志中。

响应时间

响应时间 = 服务时间 + 排队时间

服务时间是真正执行查询使用了多少时间，排队时间是服务器等待某些资源而没有真正执行查询的时间 – 可能是等待I/O操作完成。

扫描的行数和返回的行数

扫描的行数和返回的行数大多数情况是相同的，但是在做一个联接查询的时候，服务器必须要扫描多行才能生成结果集中的一行。

扫描的行数和访问类型

我们在评估查询开销的时候，有一个语句是非常好的，这个语句叫做EXPLAIN，EXPLAIN语句中的type列反应了访问类型。访问类型有很多种

• 全表扫描
• 索引扫描
• 范围扫描
• 唯一索引查询
• 常数引用等

这里列出的速度是从慢到快。

我们来把这些查询类型详细的讲解一下，因为这个很重要：

1. ALL

全表扫描(Full Table Scan), MySQL将遍历全表以找到匹配的行.

mysql> explain select * from film where rating ='G';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

film 表中 rating 字段没有索引.

2. index

全索引扫描(Full Index Scan), index 与 ALL 区别为 index 类型只遍历索引树. MYSQL 遍历整个索引来查找匹配的行.

mysql> explain select title from film;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | index | NULL          | idx_title | 514     | NULL | 1000 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

虽然 where 条件中没有用到索引, 但是要取出的列 title 是索引包含的列, 所以只要全扫描 title 索引即可, 直接使用索引树查找数据.

3. range

索引范围扫描, 常见于 ‘<’, ‘<=’, ‘>’, ‘>=’, ‘between’ 等操作符.

mysql> explain select * from film where film_id > 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | range | PRIMARY       | PRIMARY | 2       | NULL |  900 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

因为 film_id 是索引, 所以只要查找索引的某个范围即可, 通过索引找到具体的数据.

4. ref

使用非唯一性索引或者唯一索引的前缀扫描, 返回匹配某个单独值的记录行.

mysql> explain select * from payment where customer_id = 10;
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | payment | NULL       | ref  | idx_fk_customer_id | idx_fk_customer_id | 2       | const |   25 |   100.00 | NULL  |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+

customer_id 在 payment 表中是非唯一性索引

5. eq_ref

类似ref, 区别就在使用的索引是唯一索引. 在联表查询中使用 primary key 或者 unique key 作为关联条件.

mysql> explain select * from film a left join film_text b on a.film_id = b.film_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL             | 1000 |   100.00 | NULL        |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 2       | sakila.a.film_id |    1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+

6. const/system

当 MySQL 对查询某部分进行优化, 并转换为一个常量时, 使用这些类型访问. 如将主键置于 where 列表中, MySQL 就能将该查询转换为一个常量, system 是 const 类型的特例, 当查询的表只有一行的情况下使用 system.

mysql> explain select * from film where film_id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | const | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

7. NULL

MySQL 不用访问表或者索引就直接能到结果.

mysql> explain select 1 from dual where 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

dual是一个虚拟的表, 可以直接忽略.

MySQL有三种方式使用WHERE：

• 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
• 使用索引覆盖，直接从索引中过滤不需要的记录并返回命中结果，这个过程不需要进行回表查询。
• 从数据表中返回数据，然后过滤不满足条件的记录。这是在MySQL服务器层完成的。

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

• 使用索引覆盖扫描
• 改变库表结构
• 重写复杂查询

重构查询的方法

切分查询

有的时候对于一个大查询，我们需要分而治之，将大查询切分成小查询，每个查询的功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

例如删除旧数据。定期删除大量数据的时候，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据，占慢整个事务日志，耗尽系统资源，阻塞很多小但是重要的查询。例如这个：

DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH)

可以优化成这个样子：

rows_affected = 0;
do {rows_affected = do_query("DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000");// 每次操作完成之后睡一会儿sleep(10000);
} while (row_affected > 0);

分解联接查询

对于联接查询，可以对每一张表进行一次单表查询，然后将结果在应用程序中进行联接。例如下面这个查询：

SELECT * FROM tag JOIN tag_post ON tag_post.tag_id = tag.id JOIN post ON tag_post.post_id = post.id WHERE tag.tag = 'mysql';

可以分解成下面这些查询：

SELECT * FROM tag WHERE = 'mysql';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123, 465, 78);

为什么要这样做呢？

• 让缓存效率更高。许多应用程序可以方便的缓存单表查询的结果对象。例如上面查询的tag mysql已经被缓存了，那么应用就可以跳过第一个查询。
• 在查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做联接可以更容易对数据库进行拆分，更容易做到高性能和可拓展。
• 可以减少冗余记录的访问。在应用层做联接查询，意味着对于某条记录应用只需要查询一次，而在数据库中做联接查询则可能需要重复的访问一部分数据。从这点看，这样的重构可能会减少网络和内存的消耗。
• 查询本身的效率可能会提升。在这个例子中，使用IN()代替联接查询，可以让MySQL按照ID顺序进行查询，这可能比随机的联接要更高效。

查询执行的基础

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

MySQL的客户端和服务器之间的通信协议是半双工的，这意味着在同一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。这样带来的问题就是无法进行流量控制。这里有一个小细节：

多数连接MySQL的库函数都可以获得全部结果集并将结果缓存到内存里，还可以逐行获取需要的数据。默认一般是获得全部结果集并将他们缓存到内存中。MySQL需要等所有的数据全部发送完毕才能释放这条查询所占用的资源。

查询状态

Sleep

线程正在等待客户端发送新的请求。

Query

线程正在执行查询或者正在将结果发送给客户端。

Locked

在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态，但在其他没有行锁的引擎中也经常会出现。

Analyzing and statistics

线程正在收集存储引擎的统计信息，并生成查询的执行计划。

Copying to tmp table [on disk]

线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上。

Sorting result

线程正在对结果集进行排序。

Sending data

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

语法解析器和预处理

MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这里会检测语法错误，是否使用了错误的关键字，关键字的顺序是否正确，引号是否能够前后匹配等。

然后预处理器检查生成的解析树，检查数据表和数据列是否存在，还是解析名字和别名看看是否有歧义等

下一步预处理器会验证权限。

查询优化器

到这里为止解析树是已经完全合法的了，优化器会将其转换成查询执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到最好的执行计划。

MySQL使用基于成本的优化器。它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。最初成本的最小单位是随机读取一个4KB数据页的成本，后来引入了一些因子来进行估算。巨复杂！！！虽然查询优化是可能有问题的，但是读者不要为了这个可能产生的问题费尽心思，因为您不一定可以比查询优化器做的更好了。

优化策略可以分成两种：

• 静态优化
• 动态优化

静态优化在第一次完成之后就一直有效，即使使用不同的参数重复执行查询页不会发生变化，可以认为是一种编译时优化。而动态优化跟上下文有关，可以理解为运行时优化。

下面是MySQL能够处理的一些优化类型：

• 重新定义联接表的顺序

• 将外联接转换成内联接

• 使用代数等价变换规则。例如：

  (5 = 5 AND b = c) AND a = 5 
  可以改写成
  a > 5
  

  就是类似这样的优化。

• 优化COUNT(), MIN(), MAX()。例如：我想要找某一列的最小值，只需要查询对应B-Tree索引最左端的记录，MySQL可以直接获取第一行记录，最大值就可以直接获取最后一行记录。

• 预估并转换为常数表达式。

• 覆盖索引优化

• 子查询优化

• 提取终止查询。当遇到LIMIT子句的时候，MySQL会自动中止等

• 等值传播。如果两列的值可以通过等式联接，那么MySQL能够把其中一列的WHERE条件

• 列表IN()的比较。列表IN()的比较。在很多数据库系统中，IN()完全等同于多个OR条件的子句，因为这两者是完全等价的。在MySQL中这点是不成立的，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O（log n）复杂度的操作，等价地转换成OR查询的复杂度为O（n），对于IN()列表中有大量取值的时候，MySQL的处理速度将会更快。

表和索引的统计信息

服务器层有查询优化器，但是数据和索引的统计信息是在存储引擎层实现的。因此MySQL查询执行计划的时候，需要向存储引擎层获取响应的统计信息。常见的统计信息有：

• 每个表或者索引有多少个页面
• 每个表的每个索引的基数是多少，数据行和索引的长度是多少
• 索引的分布信息等

MySQL如何执行联接查询

MySQL认为每一个查询都是联接 – 不仅仅是匹配两张表中对应的查询，而是每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都是联接。

对于UNION查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后重新读出临时表中的数据来完成UNION查询。在MySQL概念中，每个查询都是一次联接，所以读取临时表的结果也是一次联接。

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

执行计划

我们用一张图来表示MySQL的联接查询执行计划：

联接查询优化器

联接优化器会尝试在所有的联接顺序中选择一个成本最低的来生成执行计划树。如果可能，优化器会遍历每一个表，然后逐个做嵌套循环，计算执行每一棵可能的计划树的成本，最后返回一个最优的执行计划。但是这个代价可能有点大，所以优化器选择使用贪婪搜索的方式查找最优的联接顺序。当需要联接的表超过optimizer_search_depth的限制的时候，就会选择贪婪搜索模式了。

排序优化

排序是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。当不能使用索引生成排序结果的时候，MySQL需要自己排序，如果数据量小的话在内存中进行，如果数据量大则需要使用磁盘。如果需要排序的数据量小于排序缓冲区，MySQL使用内存进行快速排序操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用快速排序进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排序好的块进行合并。

查询执行引擎

调用存储引擎实现的接口来完成，这些接口就是我们说的handler API的接口。查询中的每一个表都由一个handler的实例表示。如果一个表在查询中出现了三次，服务器就会创建三个handler对象，handler里面存储了表的所有列名，索引统计信息等等。

将结果返回给客户端

MySQL将结果集返回客户端是一个增量、逐步返回的过程。例如，对于关联操作，一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。

MySQL查询优化器的局限性

等值传递

有些时候，等值传递会带来一些意想不到的额外消耗。例如一列上的巨大IN()列表，优化器知道它将等于其他表中的一些列，这是由于WHERE, ON 或者 USING子句使列彼此相等。

优化器通过将列表复制到所有相关表中的相应列来“共享”列表。通过因为各个表新增了过滤条件，所以优化器可以高效地从存储引擎过滤记录。但是如果这个列表非常大，则会导致优化和执行都会变慢。

并行执行

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

在同一个表中查询和更新

MySQL不允许对一张表同时进行查询和更新。

优化特定类型的查询

优化COUNT()查询

COUNT()有两个不同的作用：

1. 统计某个列值的数量，即统计某列值不为NULL的个数。
2. 统计行数。

当使用COUNT(*)时，统计的是行数，它会忽略所有的列而直接统计所有的行数。而在括号中指定了一个列的话，则统计的是这个列上值不为NULL的个数。
可以考虑使用索引覆盖扫描或增加汇总表对COUNT()进行优化。

优化LIMIT和OFFSET子句

处理分页会使用到LIMIT，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率会非常差。例如对于***LIMIT 10000，20***这样的查询，MySql需要查询10020条记录，将前面10000条记录抛弃，只返回最后的20条。这样的代价非常高，如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。
优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要与原表做一次关联操作返回所需的列。对于偏移量很大的时候，这样的效率会提升非常大。考虑下面的查询：

SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

如果这个表非常大，那么这个查询最好改写成下面的这样子：

SELECT film.film_id, film.description FROM sakila.film
INNER JOIN 
(SELECT film_id FROM sakila.film ORDER BY title LIMIT 50,5) AS lim
USING(film_id);

注意优化中关联的子查询，因为只查询film_id一个列，数据量小，使得一个内存页可以容纳更多的数据，这让MySQL扫描尽可能少的页面。在获取到所需要的所有行之后再与原表进行关联以获得需要的全部列。
LIMIT的优化问题，其实是OFFSET的问题，它会导致MySql扫描大量不需要的行然后再抛弃掉。可以借助书签的思想记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就避免了使用OFFSET。可以把主键当做书签使用，例如下面的查询：

SELECT * FROM sakila.rental ORDER BY rental_id DESC LIMIT 20;

假设上面的查询返回的是主键为16049到16030的租借记录，那么下一页查询就可以直接从16030这个点开始：

SELECT * FROM sakila.rental WHERE rental_id < 16030
ORDER BY rental_id DESC LIMIT 20;

该技术的好处是无论翻页到多么后面，其性能都会很好。
此外，也可以用关联到一个冗余表的方式提高LIMIT的性能，冗余表只包含主键列和需要做排序的数据列。

优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化（例如，直接将这些子句冗余地写一份到各个子查询）。

除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字，MySQL仍然会使用临时表存储结果。事实上，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。

子查询优化

MySql的子查询实现的非常糟糕。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。
应该尽可能用关联替换子查询，可以提高查询效率。

排序优化

应该尽量让MySql使用索引进行排序。当不能使用索引生成排序结果的时候，MySql需要自己进行排序。如果数据量小于“排序缓冲区”的大小，则MySql使用内存进行“快速排序”操作。如果数据量太大超过“排序缓冲区”的大小，那么MySql只能采用文件排序，而文件排序的算法非常复杂，会消耗很多资源。
无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序。所以让MySql根据索引构造排序结果非常的重要。

临时表

上面提到在MySql中，任何一个查询实质上都是一个关联查询。那么对于子查询或UNION查询是如何实现关联操作的呢。
对于UNION查询，MySql先将每一个单表查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询。MySql读取结果临时表和普通表一样，也是采用的关联方式。
当遇到子查询时，先执行子查询并将结果放到一个临时表中，然后再将这个临时表当做一个普通表对待。
MySql的临时表是没有任何索引的，在编写复杂的子查询和关联查询的时候需要注意这一点。
临时表也叫派生表。

用IN()取代OR

在MySql中，IN()先将自己列表中的数据进行排序，然后通过二分查找的方式确定列的值是否在IN()的列表中，这个时间复杂度是O(logn)。如果换成OR操作，则时间复杂度是O(n)。所以，对于IN()的列表中有大量取值的时候，用IN()替换OR操作将会更快。

优化MAX()和MIN()

在MySql中，IN()先将自己列表中的数据进行排序，然后通过二分查找的方式确定列的值是否在IN()的列表中，这个时间复杂度是O(logn)。如果换成OR操作，则时间复杂度是O(n)。所以，对于IN()的列表中有大量取值的时候，用IN()替换OR操作将会更快。