09 查询性能优化

Wu Jun 2018-12-18 21:49:57
07 数据库 > 01 MySQL

1 使用 Explain 进行分析

Explain 用来分析 SELECT 查询语句,开发人员可以通过分析 Explain 结果来优化查询语句。

比较重要的字段有:

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 还不支持松散索引扫描。