代码拉取完成，页面将自动刷新
title: Mysql 性能优化
date: 2020-06-03 20:16:48
categories:
- 数据库
- 关系型数据库
- Mysql
tags:
- 数据库
- 关系型数据库
- Mysql
- 性能
permalink: /pages/396816/
良好的逻辑设计和物理设计是高性能的基石。
DATETIME 来存储时间，而不是使用字符串。UNSIGNED 表示不允许负值，大致可以使正数的上限提高一倍。整数类型通常是标识列最好的选择，因为它们很快并且可以使用 AUTO_INCREMENT。
ENUM 和 SET 类型通常是一个糟糕的选择，应尽量避免。
应该尽量避免用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢。对于 MD5、SHA、UUID 这类随机字符串，由于比较随机，所以可能分布在很大的空间内，导致 INSERT 以及一些 SELECT 语句变得很慢。
- 符号；更好的做法是，用 UNHEX() 函数转换 UUID 值为 16 字节的数字，并存储在一个 BINARY(16) 的列中，检索时，可以通过 HEX() 函数来格式化为 16 进制格式。应该避免的设计问题：
ALTER TABLE 非常耗时。ALTER TABLE。NULL
范式化目标是尽量减少冗余，而反范式化则相反。
范式化的优点：
DISTINCT 或 GROUP BY 语句范式化的缺点：
在真实世界中，很少会极端地使用范式化或反范式化。实际上，应该权衡范式和反范式的利弊，混合使用。
索引优化应该是查询性能优化的最有效手段。
如果想详细了解索引特性请参考：Mysql 索引
WHERE 过滤条件的列应该考虑添加索引。=、IN 可以乱序 - 不需要考虑 =、IN 等的顺序使用 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;
其他优化的办法还包括使用预先计算的汇总表，或者关联到一个冗余表，冗余表中只包含主键列和需要做排序的列。
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）分解成对每一个表进行一次单表查询，然后将结果在应用程序中进行关联，这样做的好处有：
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：子查询中的第一个 SELECTDEPENDENT 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 使用分析
在 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";
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。