mysql-optimization.md

Mysql 性能优化

一、MySQL 查询过程

想针对 Mysql 进行优化，很有必要先了解 Mysql 的查询过程，才能做到有的放矢。

很多的查询优化工作实际上就是遵循一些原则让 MySQL 的优化器能够按照预想的合理方式运行而已。

（1）客户端/服务端通信协议

MySQL 客户端/服务端通信是半双工模式：即任一时刻，要么是服务端向客户端发送数据，要么是客户端向服务器发送数据。

客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。

（2）查询缓存

解析一个查询语句前，如果查询缓存是打开的，那么 MySQL 会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL 将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql 库中的系统表，其查询结果 都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

既然是缓存，就会失效，那查询缓存何时失效呢？MySQL 的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL 必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

• 任何的查询语句在开始之前都必须经过检查，即使这条 SQL 语句永远不会命中缓存
• 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，也不在本文讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

• 用多个小表代替一个大表，注意不要过度设计
• 批量插入代替循环单条插入
• 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
• 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

当然查询缓存系统本身是非常复杂的，这里讨论的也只是很小的一部分，其他更深入的话题，比如：缓存是如何使用内存的？如何控制内存的碎片化？事务对查询缓存有何影响等等，读者可以自行阅读相关资料，这里权当抛砖引玉吧。

（3）语法解析和预处理

MySQL 通过关键字将 SQL 语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如 SQL 中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据 MySQL 规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

（4）查询优化

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成执行计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL 使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在 MySQL 可以通过查询当前会话的 last_query_cost 的值来得到其计算当前查询的成本。

mysql> select * from t_message limit 10;
...省略结果集

mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 6391.799000 |
+-----------------+-------------+

示例中的结果表示优化器认为大概需要做 6391 个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。

有非常多的原因会导致 MySQL 选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL 认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但 MySQL 值选择它认为成本小的，但成本小并不意味着执行时间短）等等。

MySQL 的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：

• 重新定义表的关联顺序（多张表关联查询时，并不一定按照 SQL 中指定的顺序进行，但有一些技巧可以指定关联顺序）
• 优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找 B+Tree 索引最左端，反之则可以找到最大值，具体原理见下文）
• 提前终止查询（比如：使用 Limit 时，查找到满足数量的结果集后会立即终止查询）
• 优化排序（在老版本 MySQL 会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于 I/O 密集型应用，效率会高很多）

随着 MySQL 的不断发展，优化器使用的优化策略也在不断的进化，这里仅仅介绍几个非常常用且容易理解的优化策略，其他的优化策略，大家自行查阅吧。

（5）查询执行引擎

在完成解析和优化阶段以后，MySQL 会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL 在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

（6）返回结果

查询过程的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL 仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等等。

如果查询缓存被打开且这个查询可以被缓存，MySQL 也会将结果存放到缓存中。

结果集返回客户端是一个增量且逐步返回的过程。有可能 MySQL 在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足 ① 中所描述的通信协议的数据包发送，再通过 TCP 协议进行传输，在传输过程中，可能对 MySQL 的数据包进行缓存然后批量发送。

小结

回头总结一下 MySQL 整个查询执行过程，总的来说分为 6 个步骤：

1. 客户端向 MySQL 服务器发送一条查询请求。
2. MySQL 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
3. MySQL 服务器进行 SQL 解析、预处理。
4. MySQL 服务器用优化器生成对应的执行计划。
5. MySQL 服务器根据执行计划，调用存储引擎的 API 来执行查询。
6. MySQL 服务器将结果返回给客户端，同时缓存查询结果。

二、数据结构优化

良好的逻辑设计和物理设计是高性能的基石。

数据类型优化

数据类型优化基本原则

• 更小的通常更好 - 越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的 CPU 周期也更少。
  • 例如：整型比字符类型操作代价低，因而会使用整型来存储 IP 地址，使用 DATETIME 来存储时间，而不是使用字符串。
• 简单就好 - 如整型比字符型操作代价低。
  • 例如：很多软件会用整型来存储 IP 地址。
  • 例如：UNSIGNED 表示不允许负值，大致可以使正数的上限提高一倍。
• 尽量避免 NULL - 可为 NULL 的列会使得索引、索引统计和值比较都更复杂。

类型的选择

• 整数类型通常是标识列最好的选择，因为它们很快并且可以使用 AUTO_INCREMENT。

• ENUM 和 SET 类型通常是一个糟糕的选择，应尽量避免。

• 应该尽量避免用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢。对于 MD5、SHA、UUID 这类随机字符串，由于比较随机，所以可能分布在很大的空间内，导致 INSERT 以及一些 SELECT 语句变得很慢。

  • 如果存储 UUID ，应该移除 - 符号；更好的做法是，用 UNHEX() 函数转换 UUID 值为 16 字节的数字，并存储在一个 BINARY(16) 的列中，检索时，可以通过 HEX() 函数来格式化为 16 进制格式。

表设计

应该避免的设计问题：

• 太多的列 - 设计者为了图方便，将大量冗余列加入表中，实际查询中，表中很多列是用不到的。这种宽表模式设计，会造成不小的性能代价，尤其是 ALTER TABLE 非常耗时。
• 太多的关联 - 所谓的实体 - 属性 - 值（EVA）设计模式是一个常见的糟糕设计模式。Mysql 限制了每个关联操作最多只能有 61 张表，但 EVA 模式需要许多自关联。
• 枚举 - 尽量不要用枚举，因为添加和删除字符串（枚举选项）必须使用 ALTER TABLE。
• 尽量避免 NULL

范式和反范式

范式化目标是尽量减少冗余，而反范式化则相反。

范式化的优点：

• 比反范式更节省空间
• 更新操作比反范式快
• 更少需要 DISTINCT 或 GROUP BY 语句

范式化的缺点：

• 通常需要关联查询。而关联查询代价较高，如果是分表的关联查询，代价更是高昂。

在真实世界中，很少会极端地使用范式化或反范式化。实际上，应该权衡范式和反范式的利弊，混合使用。

三、索引优化

索引优化应该是查询性能优化的最有效手段。

如果想详细了解索引特性请参考：Mysql 索引

何时使用索引

• 对于非常小的表，大部分情况下简单的全表扫描更高效。
• 对于中、大型表，索引非常有效。
• 对于特大型表，建立和使用索引的代价将随之增长。可以考虑使用分区技术。
• 如果表的数量特别多，可以建立一个元数据信息表，用来查询需要用到的某些特性。

索引优化策略

• 索引基本原则
  • 索引不是越多越好，不要为所有列都创建索引。
  • 要尽量避免冗余和重复索引。
  • 要考虑删除未使用的索引。
  • 尽量的扩展索引，不要新建索引。
  • 频繁作为 WHERE 过滤条件的列应该考虑添加索引。
• 独立的列 - “独立的列” 是指索引列不能是表达式的一部分，也不能是函数的参数。
• 前缀索引 - 索引很长的字符列，可以索引开始的部分字符，这样可以大大节约索引空间。
• 最左前缀匹配原则 - 将选择性高的列或基数大的列优先排在多列索引最前列。
• 使用索引来排序 - 索引最好既满足排序，又用于查找行。这样，就可以使用索引来对结果排序。
• =、IN 可以乱序 - 不需要考虑 =、IN 等的顺序

四、SQL 优化

SQL 优化后，可以通过执行计划（EXPLAIN）来查看优化效果。

SQL 优化基本思路：

• 只返回必要的列 - 最好不要使用 SELECT * 语句。

• 只返回必要的行 - 使用 WHERE 语句进行查询过滤，有时候也需要使用 LIMIT 语句来限制返回的数据。

• 缓存重复查询的数据 - 应该考虑在客户端使用缓存，尽量不要使用 Mysql 服务器缓存（存在较多问题和限制）。

• 使用索引来覆盖查询

优化 COUNT() 查询

COUNT() 有两种作用：

• 统计某个列值的数量。统计列值时，要求列值是非 NULL 的，它不会统计 NULL。
• 统计行数。

统计列值时，要求列值是非空的，它不会统计 NULL。如果确认括号中的表达式不可能为空时，实际上就是在统计行数。最简单的就是当使用 COUNT(*) 时，并不是我们所想象的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计行数。

我们最常见的误解也就在这儿，在括号内指定了一列却希望统计结果是行数，而且还常常误以为前者的性能会更好。但实际并非这样，如果要统计行数，直接使用 COUNT(*)，意义清晰，且性能更好。

（1）简单优化

SELECT count(*) FROM world.city WHERE id > 5;

SELECT (SELECT count(*) FROM world.city) - count(*)
FROM world.city WHERE id <= 5;

（2）使用近似值

有时候某些业务场景并不需要完全精确的统计值，可以用近似值来代替，EXPLAIN 出来的行数就是一个不错的近似值，而且执行 EXPLAIN 并不需要真正地去执行查询，所以成本非常低。通常来说，执行 COUNT() 都需要扫描大量的行才能获取到精确的数据，因此很难优化，MySQL 层面还能做得也就只有覆盖索引了。如果不还能解决问题，只有从架构层面解决了，比如添加汇总表，或者使用 Redis 这样的外部缓存系统。

优化关联查询

在大数据场景下，表与表之间通过一个冗余字段来关联，要比直接使用 JOIN 有更好的性能。

如果确实需要使用关联查询的情况下，需要特别注意的是：

• 确保 ON 和 USING 字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表 A 和表 B 用某列 column 关联的时候，如果优化器关联的顺序是 A、B，那么就不需要在 A 表的对应列上创建索引。没有用到的索引会带来额外的负担，一般来说，除非有其他理由，只需要在关联顺序中的第二张表的相应列上创建索引（具体原因下文分析）。
• 确保任何的 GROUP BY 和 ORDER BY 中的表达式只涉及到一个表中的列，这样 MySQL 才有可能使用索引来优化。

要理解优化关联查询的第一个技巧，就需要理解 MySQL 是如何执行关联查询的。当前 MySQL 关联执行的策略非常简单，它对任何的关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后在嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为为止。然后根据各个表匹配的行，返回查询中需要的各个列。

太抽象了？以上面的示例来说明，比如有这样的一个查询：

SELECT A.xx,B.yy
FROM A INNER JOIN B USING(c)
WHERE A.xx IN (5,6)

假设 MySQL 按照查询中的关联顺序 A、B 来进行关联操作，那么可以用下面的伪代码表示 MySQL 如何完成这个查询：

outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);
outer_row = outer_iterator.next;
while(outer_row) {
    inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;
    inner_row = inner_iterator.next;
    while(inner_row) {
        output[inner_row.yy,outer_row.xx];
        inner_row = inner_iterator.next;
    }
    outer_row = outer_iterator.next;
}

可以看到，最外层的查询是根据A.xx列来查询的，A.c上如果有索引的话，整个关联查询也不会使用。再看内层的查询，很明显B.c上如果有索引的话，能够加速查询，因此只需要在关联顺序中的第二张表的相应列上创建索引即可。

优化 GROUP BY 和 DISTINCT

Mysql 优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化，这也是最有效的优化方法。

优化 LIMIT

当需要分页操作时，通常会使用 LIMIT 加上偏移量的办法实现，同时加上合适的 ORDER BY 字句。如果有对应的索引，通常效率会不错，否则，MySQL 需要做大量的文件排序操作。

一个常见的问题是当偏移量非常大的时候，比如：LIMIT 10000 20这样的查询，MySQL 需要查询 10020 条记录然后只返回 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 tmp USING(film_id);

这里的延迟关联将大大提升查询效率，让 MySQL 扫描尽可能少的页面，获取需要访问的记录后在根据关联列回原表查询所需要的列。

有时候如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET，比如下面的查询：

SELECT id FROM t LIMIT 10000, 10;
改为：
SELECT id FROM t WHERE id > 10000 LIMIT 10;

其他优化的办法还包括使用预先计算的汇总表，或者关联到一个冗余表，冗余表中只包含主键列和需要做排序的列。

优化 UNION

MySQL 总是通过创建并填充临时表的方式来执行 UNION 查询。因此很多优化策略在UNION查询中都没有办法很好的时候。经常需要手动将WHERE、LIMIT、ORDER BY等字句“下推”到各个子查询中，以便优化器可以充分利用这些条件先优化。

除非确实需要服务器去重，否则就一定要使用UNION ALL，如果没有ALL关键字，MySQL 会给临时表加上DISTINCT选项，这会导致整个临时表的数据做唯一性检查，这样做的代价非常高。当然即使使用 ALL 关键字，MySQL 总是将结果放入临时表，然后再读出，再返回给客户端。虽然很多时候没有这个必要，比如有时候可以直接把每个子查询的结果返回给客户端。

优化查询方式

切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

DELEFT FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);

rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

分解大连接查询

将一个大连接查询（JOIN）分解成对每一个表进行一次单表查询，然后将结果在应用程序中进行关联，这样做的好处有：

• 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
• 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
• 减少锁竞争；
• 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可扩展。
• 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

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 tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

五、执行计划

如何检验修改后的 SQL 确实有优化效果？这就需要用到执行计划（EXPLAIN）。

使用执行计划 EXPLAIN 用来分析 SELECT 查询效率，开发人员可以通过分析 EXPLAIN 结果来优化查询语句。

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

各列含义如下：

• id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
• select_type: SELECT 查询的类型.
  • SIMPLE, 表示此查询不包含 UNION 查询或子查询
  • PRIMARY, 表示此查询是最外层的查询
  • UNION, 表示此查询是 UNION 的第二或随后的查询
  • DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
  • UNION RESULT, UNION 的结果
  • SUBQUERY, 子查询中的第一个 SELECT
  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
• table: 查询的是哪个表
• partitions: 匹配的分区
• type: join 类型
• possible_keys: 此次查询中可能选用的索引
• key: 此次查询中确切使用到的索引.
• ref: 哪个字段或常数与 key 一起被使用
• rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
• filtered: 表示此查询条件所过滤的数据的百分比
• extra: 额外的信息

更多内容请参考：MySQL 性能优化神器 Explain 使用分析

参考资料

• 《高性能 MySQL》
• 我必须得告诉大家的 MySQL 优化原理
• 20+ 条 MySQL 性能优化的最佳经验
• MySQL 性能优化神器 Explain 使用分析

传送门

◾ 🏠 DB-TUTORIAL 首页 ◾ 🎯 我的博客 ◾