MySQL篇-优化查询语句

查询速度慢的原因

  1. 额外查询大量不需要的行数据

总是先把所有的行数据查询出来,然后取其中的几条数据。有效的解决方式是LIMIT

  1. 总是查询所有的列

总是把所有的列查询出来,但是不需要用到其中的一些列。典型的SELECT *

  1. 重复查询相同的数据

在某些地方总会执行相同的查询得到结果,例如查询用户信息。

衡量MySQL的查询开销

  1. 响应时间
  2. 扫描的行数及返回的行数
  3. 扫描库表的访问类型

响应时间

  1. 是否有大量的磁盘I/O
  2. 网络是否拥塞
  3. 是否在同一时间有过多的连接争用行锁

计算响应时间的方法:

  1. 了解查询需要哪些索引及它的执行计划
  2. 计算大概需要多少个顺序I/O或者随机I/O
  3. 把所有消耗时间求和估算出参考值,判断某次查询是否在该参考值允许的范围内

扫描行数及返回的行数

扫描的行数远远大于返回的行数时,该查询就是不合理的,应该优化、重写该查询或者在某列上添加索引,使扫描的行数尽量等于返回的行数。通常出现的情况是 扫描行数 :返回行数 = 1:1 ~ 10 :1

扫描库表的访问类型

访问类型从坏到好总共分为以下几种:

  1. ALL:全表扫描,将所有数据读出后再过滤条件,返回需要的结果

image-20200507094629915

  1. index:索引扫描,按照索引的顺序进行扫描全表,然后根据索引覆盖情况决定是否回表查询行数据

image-20200507100654571

  1. range:范围扫描,以范围的形式扫描索引,根据索引覆盖情况决定是否回表查询(通常至少需要达到这一步)

Using where代表索引被利用到where条件中过滤不符合条件的行,Using index代表索引覆盖了查询的所有列,不需要回表

image-20200507100819576

  1. ref:利用非唯一索引查询从非聚簇索引中查找符合条件的值,根据索引覆盖情况决定是否需要回表,它的好处是不需要扫描全表,仅需扫描部分满足索引值的数据

image-20200507101450916

  1. ref_eq:返回结果集仅有一行,通常是利用主键或者唯一性索引进行查询时会出现该类型

  2. const:将一个主键值放在where条件后面,MySQL会优化成一个常量

image-20200507102020830

WHERE条件的三种应用(从好到坏):

  1. 在索引中使用WHERE条件过滤掉不需要的行,此时查询分析会出现Using Where
  2. 使用索引覆盖查询,索引列覆盖查询的列,此时出现Using index
  3. 把所有数据读出,再应用WHERE条件过滤掉不符合的行数据,相当于全表扫描

扫描大量的数据却返回很少的行时,可以应用优化技巧:

  1. 使用索引覆盖扫描
  2. 改变库表结构
  3. 重写该查询

重构查询的方式

切分查询

将一个大的查询切分成许多小的查询完成,每一次查询返回一部分结果,减少锁的占用时间。

例如:每个月删除一次Message表:

1
DELETE FROM Message WHERE created < DATE_SUB(NOW(), INTERNAL 3 MONTH);

可以使用下面的查询方法优化:

1
2
3
4
5
6
row_affected = 0;
do {
row_affected = do_query(
"DELETE FROM Message WHERE created < DATE_SUB(NOW(), INTERNAL 3 MONTH) LIMIT 10000"
)
} while (row_affected > 0)

一次性删除所有数据转化为每次删除1万行数据,可以减少锁的占用时间。假设有10万行数据,一次性删除10万行数据,会一直占用这10万行的行锁;每次只删除1万行数据,每次只占用1万行的行锁。

分解关联查询

把一次关联查询分解成多个单表查询。

例如下面的关联查询:

1
2
3
4
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'

分解成多个单表查询:

1
2
3
SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id = '1234';
SELECT * FROM post WHERE post.id IN (123, 456, 567, 9008, 8904);

这样做的好处是:

  1. 可以在应用程序做关联,减少数据库的压力
  2. 可以将查询结果缓存在应用程序当中,下次不必查询数据库
  3. 减少锁的竞争,关联查询可能会查询额外的列并占用行锁
0%