MySQL查询性能优化

作者: Karl90 | 来源:发表于2020-01-05 22:01 被阅读0次

1. 为什么查询速度慢

一个查询是由许多子任务组成的，每个子任务都会消耗一定的时间。优化一个查询，其实就是要优化其子任务。

一个查询的生命周期步骤：

客户端发送一条查询给MySQL服务器

MySQL服务器先检查查询缓存

如果命中缓存，则立刻返回存储在查询缓存中的结果给客户端

否则，进行下一阶段

解析器进行SQL解析，生成解析树

预处理器验证权限，确认合法的解析树。

查询优化器根据解析树进行优化，生成对应的查询执行计划

查询执行引擎根据查询优化器生成的查询执行计划，调用存储引擎的API接口执行查询

存储引擎从数据文件中查询相应数据返回给查询执行引擎

查询执行引擎将存储引擎的API接口返回的结果进行处理

将结果存入查询缓存

将结果返回给客户端

查询花费时间的地方包括：

网络

CPU计算

生成统计信息

生成执行计划

锁等待（互斥等待）

向底层存储引擎检索数据的调用操作

内存操作

CPU操作

内存不足时导致的I/O操作

上下文切换

系统调用

遇到的问题可能包括：

不必要的额外操作

某些操作被额外地重复了很多次

某些操作执行的太慢

优化查询子任务的思路：

消除其中一些子任务

减少子任务的执行次数

让子任务运行更快

2. 优化数据访问

查询性能低的最基本原因是访问数据太多，可以从两个步骤分析：

确认应用程序是否在检索大量超过需要的数据

访问了太多的行

访问了太多的列

确认MySQL服务器是否分析大量超过需要的数据行

1. 是否向数据库请求了不需要的数据

查询不需要的记录

查询大量结果然后舍弃不需要的。

解决方法：查询后面加LIMIT。
多表关联时返回全部列

解决方法：只查询需要的列。
总是取出全部列

解决方法：若没有缓存则避免使用SELECT *。
重复查询相同的数据

如用户评论需要查询用户头像URL。

解决方法：将数据缓存，需要的时候从缓存中获取。

2. MySQL是否在扫描额外的记录

在确定查询只返回需要的数据后，需要查看查询为了返回结果是否扫描了过多的数据。

衡量查询开销的指标：

响应时间

服务时间

排队时间

I/O

锁等待（行锁、表锁）

判断响应时间是否是合理的值（快速上限估计法）

扫描的行数和反回的行数

扫描的行数和返回的类型

在EXPLAIN语句中的type列反应了访问类型（速度从慢到快、扫描的行数从多到少）：

全表扫描

索引扫描

范围扫描

唯一索引查询

常数引用

一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：

在索引中使用WHERE条件过滤不匹配的记录。（在存储引擎层完成）

使用索引覆盖扫描（在Extra中出现Using index）返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，无需再回表查询记录。

从数据表中返回数据，然后过滤不满足条件的记录（在Extra中出现Using where）。这是在MySQL服务器层完成的，MySQL需要先从数据表读取记录然后过滤。

如果发现查询需要扫瞄大量数据但只返回少数的行，通常可以尝试以下技巧：

使用索引覆盖扫描，把所有需要用到的列都放到索引中，这样存储引擎无需回表获取对应的行就可以返回结果。

改变库表结构，比如使用单独的汇总表。

重写复杂的查询，让MySQL优化器能够以更优化的方式执行查询。

3. 重构查询的方式

将查询转化另一种写法返回相同的结果，但性能更好。
修改应用代码，用另一种方式完成查询。
- 在设计查询时考虑是否将一个复杂的查询拆分成多个简单的查询。（视情况而定）
- 切分查询
  - 删除大量数据时会锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但很重要的查询。（一次删除10000行数据比较高效，能减少删除时锁持有时间）
- 分解关联查询
  
  可以每次对一个表进行一次单表查询，然后将结果在应用程序中进行关联。
  1. 优势：
    
    让缓存更高效。
    
    许多应用程序可以方便地缓存单表查询对应的结果对象。对于MySQL的查询缓存，如果关联中的某个表发生变化，那么无法使用查询缓存；拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。
    
    将查询分解后，执行单个查询可以减少锁的竞争。
    
    在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
    
    查询本身效率也可能会有所提升。
    
    使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，这样可能比随机的关联更高效。
    
    可以减少冗余记录的查询。
    
    在应用层做关联查询，意味着对某条记录应用之需要查询一次；在数据库中做关联查询，可能需要重复地访问一部分数据。这样的重构可能会减少网络和内存的消耗。
    
    相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。（某些场景中哈希关联效率很高）
  2. 场景：
    
    当应用能够方便地缓存单个查询结果的时候。
    
    当可以将数据分布到不同的MySQL服务器上的时候。
    
    当能够使用IN()的方式代替关联查询的时候。
    
    当查询中使用同一个数据表的时候。

4. 查询执行

使用SHOW FULL PROCESSLIST命令查看当前状态：

Sleep

线程正在等待客户端发送新的请求。
Query

线程正在执行查询或者正在将结果发送给客户端。
Locked

在MySQL服务器层，该线程正在等待表锁。
Analyzing and statistics

线程正在收集存储引擎的统计信息，并生成查询的执行计划。
Copying to tmp table [on disk]

线程正在执行查询，并且将其结果都复制到一个临时表中：
- GROUP BY操作
- 文件排序操作
- UNION操作
on disk标记表示MySQL正在将一个内存临时表放到磁盘上。
Sorting result

线程正在对结果集进行排序。
Sending data

多种情况：
- 线程可能在多个状态之间传送数据
- 在生成结果集
- 在向客户端返回数据

5. 优化特定的查询类型

1. 优化`COUNT()`查询类型

最好使用COUNT(*)
MyISAM在没有任何WHERE条件下使用COUNT(*)才最快
简单的优化：
- 查找所有ID大于5的城市：
```
SELECT COUNT(*) FROM world.city WHERE ID > 5;
```
  通过SHOW STATUS的结果可以看到该查询需要扫瞄大量的行数据。进行优化：
```
SELECT (SELECT COUNT(*) FROM world.city) - COUNT(*) FROM world.city WHERE ID <= 5;
```
  这样可以大大减少需要扫描的行数，因为查询优化阶段会将其中的子查询直接当成一个常数来处理。
- 在同一查询中统计同一个列的不同值得数量：
```
SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULL) AS red FROM items;
```
使用近似值
- 某些业务场景不需要完全精确的COUNT值，此时可以执行EXPLAIN使用近似值来代替。
- 尝试删除DISTINCT这样的约束来避免文件排序。.
更复杂的查询
- MySQL层面能做的只有索引覆盖扫描。
- 可以考虑更改应用架构
- 可以增加Memcached这样的外部缓存系统。
原则：快速、精确、简单实现，三者永远只能满足其二，必须舍掉其中一个。

2. 优化关联查询

确保ON或者USING子句中的列上有索引。

在创建索引时就要考虑到关联的顺序。当A表和B表用c列关联时，如果优化器关联顺序是B、A，那么就不需要在B表的对应列上建上索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列。

这样MySQL才有可能使用索引来优化这个过程。
当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方。

因为普通关联的地方可能会变成笛卡尔积，不同类型的关联可能会生成不同的结果。

3. 优化子查询

5.6版本以前尽可能使用关联查询。

4. 优化`GROUP BY`和`DISTINCT`

使用索引是最有效的优化方式。
当无法使用索引时：
GROUP BY使用两种策略：
- 使用临时表做分组
- 使用文件排序做分组
可以通过使用SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按照希望的方式运行。
如果需要对关联查询做分组，并且是按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高。

下面的查询效率不会很好：
```
SELECT actor.first_name, actor.last_name, COUNT(*) 
FROM sakila.film_actor 
INNER JOIN sakila.actor 
USING(actor_id) 
GROUP BY actor.first_name, actor.last_name;
```
改写成下面的写法效率会更高：
```
SELECT actor.first_name, actor.last_name, COUNT(*) 
FROM sakila.film_actor 
INNER JOIN sakila.actor 
USING(actor_id) 
GROUP BY film_actor.actor_id;
```
要注意SQL_MODE可能设置禁止在SELECT中直接使用非分组列，此时会用到子查询，导致创建和填充临时表，而子查询中创建的临时表是没有任何索引的。
```
SELECT actor.first_name, actor.last_name, c.cnt 
FROM sakila.actor 
INNER JOIN (
  SELECT actor_id, COUNT(*), AS cnt 
  FROM sakila.film_actor 
  GROUP BY actor_id 
) AS c USING(actor_id);
```
当使用GROUP BY时，结果集会自动按照分组字段进行排序。如果不关心排序，可使用GROUP BY NULL让MySQL不进行排序。

5. 优化`LIMIT`分页

问题：偏移量大时会抛弃前面大量的数据。

解决方法：

在页面中限制分页的数量
优化大偏移量的性能（尽可能使用索引覆盖扫描，而不是查询所有列，然后根据需要关联操作再返回所需的列）
比如：
```
SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;
```
- 通过延迟关联改写为：
```
SELECT film_id, description 
FROM sakila.film 
INNER JOIN (
  SELECT film_id FROM sakila.film 
  ORDER BY title LIMIT 50, 5
) AS lim USING(film_id);    
```
- 有时也可以将LIMIT查询转换成已知位置查询，让MySQL通过范围扫描获得到对应的结果。如果在一个位置列上有索引，并且预先计算出了边界值，上面的查询就可以改写成：
```
SELECT film_id, description 
FROM sakila.film 
WHERE position 
BETWEEN 50 AND 54 
ORDER BY position;
```
- 如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。
  
  首先获取第一组结果：
```
SELECT * FROM sakila.rental 
ORDER BY rental_id DESC LIMIT 20;
```
  假设上面查询返回的主键为10029到10010，那后面的查询就可以从10010开始：
```
SELECT * FROM sakila.rental 
WHERE rental_id < 10010 
ORDER BY rental_id DESC LIMIT 20;
```
  用这种方式无论翻页到多么后面性能都会非常好。
- 使用预先计算的汇总表
- 关联到一个冗余表，冗余表只包含主键列和需要做排序的数据列。

6. 优化`UNION`查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询。

需要手动将WHERE、LIMIT、ORDER BY等子句写到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化。
除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这回导致整个临时表的数据做唯一性检查，代价非常高。

MySQL查询性能优化

1. 为什么查询速度慢

2. 优化数据访问

3. 重构查询的方式

4. 查询执行