亲宝软件园·资讯

展开

MySQL 查询优化 理解MySQL查询优化处理过程

岛上码农 人气:0
想了解理解MySQL查询优化处理过程的相关内容吗,岛上码农在本文为您仔细讲解MySQL 查询优化的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:MySQL,查询优化,MySQL,查询,下面大家一起来学习吧。

MySQL查询优化需要经过解析、预处理和优化三个步骤。在这些过程中,都有可能发生错误。本篇文章不会深入讨论错误处理,而是帮助理解 MySQL 执行查询的方式,以便可以写出更好的查询语句。

解析器和预处理器

一开始,MySQL 的解析器将查询语句拆分成一系列指令并从中构建一棵“解析树”。解析器使用 MySQL 的SQL 语法去翻译和验证查询语句。例如,解析器保证了查询中的指令是有效且次序正确,并且会检查那种类似字符串引号未配对的错误。

预处理器则检查构建好的解析树中那些解析器无法处理的语义信息。例如,检查数据表和列是否存在,并且处理字段名称和别名以保证列引用没有歧义。接下来,预处理器会检查权限,通常这会非常快(除非你的服务端有一大堆权限配置)。

查询优化器

经过解析器和预处理器后,解析树就被确定是有效的了,可以被优化器进行处理并最终转变为一个查询计划。一个具有相同结果的查询通常有很多种执行方式,而优化器的职责是找出其中最优的选项。

MySQL使用基于代价估计的优化器,这意味着它视图预测众多执行计划的代价,并选择代价最低的那个。最初的单位成本是随机的4KB 数据页读取,而现在变得更为复杂,包括了如执行 WHERE比较条件的代价。可以通过显示 Last_query_cost 会话变量来查看查询优化器估计查询语句的代价。

SELECT SQL_NO_CACHE COUNT(*) FROM sakila.film_actor;
SHOW STATUS LIKE 'Last_query_cost';

显示的 Last_query_cost 意味着优化器估计需要执行对应次数的随机数据页访问才能完成查询。这是基于如下统计估算的结果:

优化器并不会考虑估计内容的缓存——它假设每次都从磁盘 I/O 读取结果。优化器并不是每次都能选择最优的执行计划,原因如下:

MySQL 查询优化器是其中非常复杂的一部分,使用了很多优化方式将查询语句转换成为一个查询执行计划。通常有两种优化方式:静态优化和动态优化。静态优化可以简单地通过检查解析树进行。例如,优化器可以将 WHERE 条件通过数学运算规则转换成一个等式。静态优化与具体的值无关,例如 WHERE条件的常量值。他们执行一次后会一直有效,即便是查询语句使用了不同的值再次执行。可以理解为是“编译时优化”。

相反,动态优化是基于具体的情景的,并依赖于多种因素。例如,WHERE 条件中的值或索引中对应的数据行数。这个过程在每次查询都需要重新估计,可以理解为是“运行时优化”。以下是一些 MySQL 的典型优化方式:

EXPLAIN SELECT film.film_id, film_actor.actor_id
FROM sakila.film
	INNER JOIN sakila.film_actor USING(film_id)
WHERE film.film_id = 1;

MySQL 会将这个查询拆分为2步,因此分析结果会有两行。第一步是是在 film 表中查找对应的数据行。由于 是按主键film_id查询的,MySQL 知道只有一行数据。 因此,此时的查询分析结果的 ref 是常量。在第二步中,MySQL 会将 film_id 作为已知值,因此对 film_actor 的查询的 ref 也是常量。其他类似的场景还有 WHERE,USING或 ON 条件中的约束条件是等式。在这个例子中,MySQL 知道 USING条件的 film_id 在查询语句中都是相同的值,这个值必须和 WHERE条件的 film_id 相同。

EXPLAIN SELECT film.film_id FROM sakila.film WHERE film_id=1;

在分析结果中的 Extra字段会看到“Impossible WHERE noticed after reading const tables”。在其他情形也会有提前中止的情况,例如:

SELECT film.film_id
FROM sakila.film
	LEFT OUTER JOIN sakila.film_actor USING(film_id)
WHERE sakila.film_actor.film_id IS NULL;

这个查询排除那些有演员的电影。每部电源都可能有多名演员,但是只要找到一名演员后,MySQL 就会停止处理当前的这部电影,而去处理下一部。对于 DISTINCT,NOT EXISTS 也会有类似的情况。

SELECT film.film_id
FROM sakila.film
	INNER JOIN sakila.film_actor USING(film_id)
WHERE film.film_id > 500;

MySQL 会知道 WHERE 条件的约束不仅适用于 film 表,同样也适用于 film_actor 表。但对于其他数据库则未必会有这样的优化效果。

实际上,MySQL 使用的优化手段比上述列举的多得多,这里没法一一列举。只是需要记住 MySQL 的优化器的复杂性及其智能化程度。因此,应当让优化器发挥其作用,而不是无限优化查询语句直到 MySQL 的优化器没有用武之地。当然,虽然 MySQL 的优化器很聪明,但是它给出的并不一定是最优结果,有些时候你知道最优结果,而 MySQL 未必知道。这种情况下,你可以对查询语句进行优化从而帮助 MySQL 完成优化工作,而有些时候则需要增加查询的提示,或是重写查询,修改数据表设计或增加索引。

加载全部内容

相关教程
猜你喜欢
用户评论