深入理解MySQL查询优化与执行流程-优快云博客

本文链接：https://blog.youkuaiyun.com/wszt_gzt/article/details/25694899

常见的原因如下：

向数据库请求了不需要的数据
是否扫描额外的记录：响应时间、扫描的行数、返回的行数

重构查询的方式主要有：

是用一次复杂的查询还是用多次简单的查询？
把一个任务切分成很多小任务，比如在删除数据的时候为了防止锁表太长时间
分解关联查询：有利于缓存使用、锁竞争、数据库拆分等

查询执行

当向MySQL发送一个请求的时候过程如下：

客户端发送一条查询给服务器
缓存命中则返回，否则继续
SQL解析、预处理，再由优化器生成对应的执行计划
调用存储引擎的API执行查询
返回结果

下面依次来看其中的组成部分。

通信协议

在任何一个时刻，要么是由服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生，这种方式使得MySQL通信简单快速。可以用SHOW FULL PROCESSLIST来查看连接状态：

Sleep
Query
Locked
Analyzing and statistics
Copying to tmp table[on disk]
Sorting result
Sending data

了解这些状态的基本含义非常有用。

查询优化处理

优化处理由很多部分组成，下面依次来看：

语法解析器和预处理

通过关键字将SQL语句解析成解析树，预处理器根据MySQL规则进一步检查解析树是否合法。

查询优化器

一条语句有很多种执行方法，优化器的作用是找到其中最好的，而有时候MySQL返回的并不一定是最小的。常见的优化方法如下：

重新定义关联表的顺序
将外连接转化成内连接
使用等价变换规则
优化COUNT、MIN、MAX
预估并转化为常数表达式
覆盖索引扫描
子查询优化
提前终止查询
等值传播
列表IN的比较

可以看到有的优化是防止我们写出很傻的SQL。

数据和索引的统计信息

统计信息由存储引擎实现，不同的存储引擎可能存储不同的信息。因为服务层没有任何统计信息，所以MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。

MySQL执行关联查询

MySQL对任何关联都执行嵌套关联操作，也就是MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。这个过程有点像递归。MySQL优化器关键的一部分就是关联查询优化，它决定了多表关联时的顺序。

优化器的局限性

MySQL的子查询实现的不好，最差的是WHERE条件中包含IN的，如下：

SELECT
	*
FROM
	sakila.film
WHERE
	film_id
IN(
	SELECT
		film_id
	FROM
		sakila.film_actor
	WHERE
		actor_id = 1
)

一般认为会先执行IN里面的，但实际上并不是这样，而是：

SELECT
	*
FROM
	sakila.film
WHERE
	film_id
IN(
	SELECT
		film_id
	FROM
		sakila.film_actor
	WHERE
		actor_id = 1
)

在上面我们知道了关联查询的原理，那么这个查询效率就会非常差。

有时候MySQL无法将限制条件从外层下推到内层，这使得原来能够限制部分返回结果的条件无法应用到内层查询的优化上：

(
	SELECT
		*
	FROM
		sakila.actor
	ORDER BY
		last_name
)
UNION ALL
(
	SELECT
		*
	FROM
		sakila.cumtomer
	ORDER BY
		last_name
)
LIMIT 20;

这样写的话会分别执行两个查询，并将结果存放在一个临时表中，然后再从这个临时表中取出前20条。

如果有一个非常大的IN表，而MySQL优化器发现在WHERE、ON或者USING的子句，将这个列表的值和另一个表的某列相关联。那么优化器会将IN列表都复制应用到关联的各个表中。因此各个表新增了过滤条件。

优化器的提示

如果对优化器选择的执行计划不满意，可以使用优化器提供的几种提示来控制最终的执行：

HIGH_PRIORITY和LOW_PRIORITY:多个语句同时访问一个表的时候，查询语句的优先级。
DELAYED:把要插入的数据放入缓存区并立即返回
STRAIGHT_JOIN:让查询中所有的表按照在语句中出现的顺序进行关联
SQL_SMALL_RESULT和SQL_BIG_RESULT:告诉优化器在distinct、group by的时候如何使用临时表及排序
SQL_BUFFER_RESULT:告诉优化器将查询结果放入到一个临时表
SQL_CACHE和SQL_NO_CACHE:结果是否应该缓存在查询缓存中
SQL_CALC_FOUND_ROWS:让返回的结果集包含更多的信息
FRO UPDATE和LOCK IN SHARE MODE:控制select的锁机制
USE INDEX、IGNORE INDEX、FORCE INDEX:使用或者不使用哪些索引

在优化器前耍一些小聪明是不好的，这样做收效甚微，而且给维护带来了很多额外的工作量。

优化特定类型的查询

一个容易产生的误解是：MyISAM的COUNT函数总是非常快。COUNT快的前提条件是没有任何WHERE条件，因为此时武宣实际计算表的行数，MySQL可以利用存储引擎的特性直接获取这个值。如果有条件时可以这样优化：

SELECT
	(
		SELECT
			COUNT(*)
		FROM
			world.City
	)
	-
	COUNT(*)
FROM
	world.City
WHERE
	id <= 5;

这样只需要扫描5行。

在系统需要进行分页操作时，通常使用LIMIT加上偏移量的办法来实现。在偏移量非常大的时候，比如LIMIT 1000,20这样的查询，这时MySQL需要查询10020条记录然后只返回最好的20条，前面的记录都被抛弃了。优化的方法通常是索引加延迟关联，比如从：

SELECT file_id, description FROM sakila.film ORDER BY title LIMIT 50,5;

改成：

SELECT
	film.film_id, film.description
FROM
	sakila.film
INNER JOIN(
	SELECT
		film_id
	FROM
		sakila.film
	ORDER BY
		title
	LIMIT
		50,5
) AS lim USING(film_id);

另外可以使用自定义变量实现一些诡异的优化：

SELECT GREATEST(@found:=-1, id) AS id, 'users' AS which_tbl
FROM users WHERE id = 1
UNION ALL
	SELECT id, 'users_archived'
	FROM user_archived WHERE id = 1 AND @found IS NULL
UNION ALL
	SELECT 1 'reset' FROM DUAL WHERE(@found : NULL) IS NOT NULL

这样就能实现：在活跃表找不到数据的时候才去不活跃用户表中查找。当然自定义变量还有很多其他的有趣用法。

----------

END