查询性能优化

Wu Jun 2020-01-06 19:43:49
Categories: > Tags:

1 使用 Explain 进行分析

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

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

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

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 减少请求的数据量

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 客户端/服务器通信协议

半双工

5.2 查询缓存

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

缓存匹配原理:

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

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

6 优化器的局限性

6.1 关联子查询

使用较小的表作为驱动表

6.2 UNION 的限制

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

6.3 Mysql 无法并行执行

Mysql 无法利用多核。

6.4 哈希关联

6.5 松散索引扫描

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