06.Mysql性能优化.md

title

Mysql 性能优化

date

2020-06-03 13:16:48 -0700

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 非常耗时。
太多的关联 - 所谓的实体 - 属性 - 值（EAV）设计模式是一个常见的糟糕设计模式。Mysql 限制了每个关联操作最多只能有 61 张表，但 EAV 模式需要许多自关联。
枚举 - 尽量不要用枚举，因为添加和删除字符串（枚举选项）必须使用 ALTER TABLE。
尽量避免 NULL

范式和反范式

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

范式化的优点：

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

范式化的缺点：

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

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

索引优化

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

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

何时使用索引

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

索引优化策略

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

SQL 优化

使用 EXPLAIN 命令查看当前 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);

执行计划（`EXPLAIN`）

如何判断当前 SQL 是否使用了索引？如何检验修改后的 SQL 确实有优化效果？

在 SQL 中，可以通过执行计划（EXPLAIN）分析 SELECT 查询效率。

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)

EXPLAIN 参数说明：

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 ⭐：表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。
- system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据。如果是 B + 树索引，我们知道此时索引构造成了多个层级的树，当查询的索引在树的底层时，查询效率就越低。const 表示此时索引在第一层，只需访问一层便能得到数据。
- eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。
- ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描。
- range：索引范围扫描，比如，<，>，between 等操作。
- index：索引全表扫描，此时遍历整个索引树。
- ALL：表示全表扫描，需要遍历全表来找到对应的行。
possible_keys：此次查询中可能选用的索引。
key ⭐：此次查询中实际使用的索引。
ref：哪个字段或常数与 key 一起被使用。
rows ⭐：显示此查询一共扫描了多少行，这个是一个估计值。
filtered：表示此查询条件所过滤的数据的百分比。
extra：额外的信息。

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

optimizer trace

在 MySQL 5.6 及之后的版本中，我们可以使用 optimizer trace 功能查看优化器生成执行计划的整个过程。有了这个功能，我们不仅可以了解优化器的选择过程，更可以了解每一个执行环节的成本，然后依靠这些信息进一步优化查询。

如下代码所示，打开 optimizer_trace 后，再执行 SQL 就可以查询 information_schema.OPTIMIZER_TRACE 表查看执行计划了，最后可以关闭 optimizer_trace 功能：

SET optimizer_trace="enabled=on";
SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";

数据模型和业务

表字段比较复杂、易变动、结构难以统一的情况下，可以考虑使用 Nosql 来代替关系数据库表存储，如 ElasticSearch、MongoDB。
在高并发情况下的查询操作，可以使用缓存（如 Redis）代替数据库操作，提高并发性能。
数据量增长较快的表，需要考虑水平分表或分库，避免单表操作的性能瓶颈。
除此之外，我们应该通过一些优化，尽量避免比较复杂的 JOIN 查询操作，例如冗余一些字段，减少 JOIN 查询；创建一些中间表，减少 JOIN 查询。

Navigation Menu

Mysql 性能优化

数据结构优化

数据类型优化

数据类型优化基本原则

类型的选择

表设计

范式和反范式

索引优化

何时使用索引

索引优化策略

SQL 优化

优化 `COUNT()` 查询

优化关联查询

优化 `GROUP BY` 和 `DISTINCT`

优化 `LIMIT`

优化 UNION

优化查询方式

切分大查询

分解大连接查询

执行计划（`EXPLAIN`）

optimizer trace

数据模型和业务

参考资料

FilesExpand file tree

06.Mysql性能优化.md

Latest commit

History

06.Mysql性能优化.md

File metadata and controls

Mysql 性能优化

数据结构优化

数据类型优化

数据类型优化基本原则

类型的选择

表设计

范式和反范式

索引优化

何时使用索引

索引优化策略

SQL 优化

优化 COUNT() 查询

优化关联查询

优化 GROUP BY 和 DISTINCT

优化 LIMIT

优化 UNION

优化查询方式

切分大查询

分解大连接查询

执行计划（EXPLAIN）

optimizer trace

数据模型和业务

参考资料

优化 `COUNT()` 查询

优化 `GROUP BY` 和 `DISTINCT`

优化 `LIMIT`

执行计划（`EXPLAIN`）