查询速度慢的原因
- 额外查询大量不需要的行数据
总是先把所有的行数据查询出来,然后取其中的几条数据。有效的解决方式是LIMIT
- 总是查询所有的列
总是把所有的列查询出来,但是不需要用到其中的一些列。典型的SELECT *
- 重复查询相同的数据
在某些地方总会执行相同的查询得到结果,例如查询用户信息。
衡量MySQL的查询开销
- 响应时间
- 扫描的行数及返回的行数
- 扫描库表的访问类型
响应时间
- 是否有大量的磁盘I/O
- 网络是否拥塞
- 是否在同一时间有过多的连接争用行锁
计算响应时间的方法:
- 了解查询需要哪些索引及它的执行计划
- 计算大概需要多少个顺序I/O或者随机I/O
- 把所有消耗时间求和估算出参考值,判断某次查询是否在该参考值允许的范围内
扫描行数及返回的行数
扫描的行数远远大于返回的行数时,该查询就是不合理的,应该优化、重写该查询或者在某列上添加索引,使扫描的行数尽量等于返回的行数。通常出现的情况是 扫描行数 :返回行数 = 1:1 ~ 10 :1
扫描库表的访问类型
访问类型从坏到好总共分为以下几种:
- ALL:全表扫描,将所有数据读出后再过滤条件,返回需要的结果
- index:索引扫描,按照索引的顺序进行扫描全表,然后根据索引覆盖情况决定是否回表查询行数据
- range:范围扫描,以范围的形式扫描索引,根据索引覆盖情况决定是否回表查询(通常至少需要达到这一步)
Using where
代表索引被利用到where
条件中过滤不符合条件的行,Using index
代表索引覆盖了查询的所有列,不需要回表
- ref:利用非唯一索引查询,从非聚簇索引中查找符合条件的值,根据索引覆盖情况决定是否需要回表,它的好处是不需要扫描全表,仅需扫描部分满足索引值的数据
ref_eq:返回结果集仅有一行,通常是利用主键或者唯一性索引进行查询时会出现该类型
const:将一个主键值放在
where
条件后面,MySQL会优化成一个常量
WHERE条件的三种应用(从好到坏):
- 在索引中使用WHERE条件过滤掉不需要的行,此时查询分析会出现
Using Where
- 使用索引覆盖查询,索引列覆盖查询的列,此时出现
Using index
- 把所有数据读出,再应用WHERE条件过滤掉不符合的行数据,相当于全表扫描
扫描大量的数据却返回很少的行时,可以应用优化技巧:
- 使用索引覆盖扫描
- 改变库表结构
- 重写该查询
重构查询的方式
切分查询
将一个大的查询切分成许多小的查询完成,每一次查询返回一部分结果,减少锁的占用时间。
例如:每个月删除一次Message
表:
1 | DELETE FROM Message WHERE created < DATE_SUB(NOW(), INTERNAL 3 MONTH); |
可以使用下面的查询方法优化:
1 | row_affected = 0; |
将一次性删除所有数据转化为每次删除1万行数据,可以减少锁的占用时间。假设有10万行数据,一次性删除10万行数据,会一直占用这10万行的行锁;每次只删除1万行数据,每次只占用1万行的行锁。
分解关联查询
把一次关联查询分解成多个单表查询。
例如下面的关联查询:
1 | SELECT * FROM tag |
分解成多个单表查询:
1 | SELECT * FROM tag WHERE tag = 'mysql'; |
这样做的好处是:
- 可以在应用程序做关联,减少数据库的压力
- 可以将查询结果缓存在应用程序当中,下次不必查询数据库
- 减少锁的竞争,关联查询可能会查询额外的列并占用行锁