查询性能优化

Wu Jun 2020-01-06 19:43:49

Categories： > Tags：

1 使用 Explain 进行分析

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

EXPLAIN 只能解释SELECT操作，其他操作要重写为 SELECT 后查看执行计划。
EXPLAIN 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN 不考虑各种 Cache
EXPLAIN 不能显示 MySQL 在执行查询时所作的优化工作
部分统计信息是估算的，并非精确值

mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)

1.1 id

id 标识 SQL 执行的顺序，从大到小的执行。若该值为 NULL，则说明这一行是其他行的联合结果。

1.2 select_type

类型	说明
SIMPLE	简单 SELECT，不使用 UNION 或子查询
PRIMARY	包含 UNION 或子查询，最外层的 SELECT 被标记为 PRIMARY
UNION	UNION 中的第二个或后面的 SELECT 语句
DEPENDENT UNION	UNION 中的第二个或后面的 SELECT 语句，同时该语句依赖外部的查询
UNION RESULT	UNION 的结果
SUBQUERY	一般子查询中的子查询被标记为 SUBQUERY，也就是位于 SELECT 列表中的查询
DEPENDENT SUBQUERY	子查询中的第一个 SELECT，取决于外面的查询
DERIVED	派生表的 SELECT， FROM 子句的子查询
UNCACHEABLE SUBQUERY	一个子查询的结果不能被缓存，必须重新评估外链接的第一行

1.3 table

对应行正在访问哪一个表，表名或者别名

关联优化器会为查询选择关联顺序，左侧深度优先
当 from 中有子查询的时候，表名是 derivedN 的形式，N 指向子查询，也就是 explain 结果中的下一列
当有 union result 的时候，表名是 union 1,2 等的形式，1,2 表示参与 union 的 query id

1.4 type

表示 MySQL 在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

类型	说明
All	最坏的情况，全表扫描
index	和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。
range	范围扫描，一个有限制的索引扫描。
ref	一种索引访问，它返回所有匹配某个单个值的行。
eq_ref	最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生
const	当确定最多只会有一行匹配的时候，MySQL 优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入 where 子句时，mysql 把这个查询转为一个常量
system	system 是 const 类型的特例，当查询的表只有一行的情况下，使用 system
NULL	MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

1.5 possible_keys

指出 MySQL 能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

1.6 key

key 列显示 MySQL 实际决定使用的键（索引）。

如果没有选择索引，键是 NULL。要想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX。

1.7 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好

1.8 ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

1.9 rows

表示 MySQL 根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

1.10 Extra

该列包含 MySQL 解决查询的详细信息

类型	说明
Using where	列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示 mysql 服务器将在存储引擎检索行后再进行过滤
Using temporary	表示 MySQL 需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort	MySQL 中无法利用索引完成的排序操作称为“文件排序”
Using join buffer	改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where	这个值强调了 where 语句会导致没有符合条件的行。
Select tables optimized away	这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
Not exists	MYSQL 优化了 LEFT JOIN，一旦它找到了匹配 LEFT JOIN 标准的行，就不再搜索了。
Using index	说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。
Using index condition	“索引条件推送”。在二级索引上执行如like这样的操作的
distinct	优化 distinct 操作，在找到第一匹配的元组后即停止找同样值的动作

2 优化数据访问

2.1 减少请求的数据量

只返回必要的列：最好不要使用 SELECT * 语句。
只返回必要的行：使用 LIMIT 语句来限制返回的数据。
缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

2.2 减少服务器端扫描的行数

最有效的方式是使用索引来覆盖查询。

3 重构查询方式

3.1 切分复杂查询

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

3.2 分解关联查询

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

让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
减少锁竞争；
在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
查询本身效率也可能会有所提升。

4 SQL 优化

4.1 负向查询不能使用索引

select name from user where id not in (1,3,4);

应该修改为:

select name from user where id in (2,5,6);

4.2 前导模糊查询不能使用索引

如:

select name from user where name like '%zhangsan'

非前导则可以:

select name from user where name like 'zhangsan%'

建议可以考虑使用 Lucene 等全文索引工具来代替频繁的模糊查询。

4.3 数据区分不明显的不建议创建索引

如 user 表中的性别字段，可以明显区分的才建议创建索引，如身份证等字段。

4.4 字段的默认值不要为 null

这样会带来和预期不一致的查询结果。

4.5 在字段上进行计算不能命中索引

select name from user where FROM_UNIXTIME(create_time) < CURDATE();

应该修改为:

select name from user where create_time < FROM_UNIXTIME(CURDATE());

4.6 最左前缀问题

如果给 user 表中的 username pwd 字段创建了复合索引那么使用以下 SQL 都是可以命中索引:

select username from user where username='zhangsan' and pwd ='axsedf1sd'

select username from user where pwd ='axsedf1sd' and username='zhangsan'

select username from user where username='zhangsan'

但是使用

select username from user where pwd ='axsedf1sd'

是不能命中索引的。

4.7 如果明确知道只有一条记录返回

select name from user where username='zhangsan' limit 1

可以提高效率，可以让数据库停止游标移动。

4.8 不要让数据库帮我们做强制类型转换

select name from user where telno = 18722222222

这样虽然可以查出数据，但是会导致全表扫描。

需要修改为

select name from user where telno='18722222222'

4.9 如果需要进行 join 的字段两表的字段类型要相同

不然也不会命中索引。

5 查询执行的基础

5.1 Mysql 客户端/服务器通信协议

半双工

优点：简单快捷；
缺点：无法进行流量控制，一旦一端开始发送消息，另一端要完整接收后才能响应它。
- 服务器端：
  - 参数 max_allowed_packet 的作用：如果查询太大，服务端会拒绝接收更多的数据。
- 客户端：
  - 如果服务器发送的数据特别多，客户端必须完整地接收完整个结果。粗暴地断开连接不是个好主意，应该使用 limit 进行限制。
  - 服务器端发送完全部数据，才会释放相应资源。

5.2 查询缓存

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

缓存匹配原理:

Key: SQL 的哈希值；
Value: 缓存(查询结果数据)。

其中对于函数 Hash(SQL) 是大小写敏感的，因此即使轻微的改动，也无法利用缓存。（Perconan 版本的 Mysql 能够忽略注释。）

MySQL 的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。

6 优化器的局限性

6.1 关联子查询

使用较小的表作为驱动表

6.2 UNION 的限制

外层的 limit 无法提前传入内层，可在内层先 limit 一下，减少数据量。

6.3 Mysql 无法并行执行

Mysql 无法利用多核。

6.4 哈希关联

Mysql的关联都是嵌套关联，不支持哈希关联。
不过可以通过建立哈希索引来曲线实现哈希关联。
Memory 存储的索引都是哈希索引。

6.5 松散索引扫描

当查询条件不是最左前缀索引时，无法利用索引，Mysql 还不支持松散索引扫描。

数据类型

MyBatis Generator