MySQL篇-优化查询语句

发表于 | 分类于
字数统计 1,179字 | 阅读时长 4分钟

查询速度慢的原因

  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%