MySQL 数据库优化(11)The Query Optimization Process

摘自:http://www.chenyajun.com/2009/01/02/1623

查询优化过程

查询优化器
一个查询通常有很多种不同的但是产生相同结果的执行方式,优化器要找到最好的。
MySQL使用基于代价的优化器,它意味着它尽力预计不同执行计划的代价并选择最不昂贵的,代价的单位是一个4k的数据页的次数。你可以通过查看 Last_query_cost看到优化器预计的代价:

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM sakila.film_actor;
+----------+
| count(*) |
+----------+
| 5462     |
+----------+
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 1040.599000 |
+-----------------+-------------+

这意味着它要随机读取1040个数据页。它基于统计而估计:每个表或者索引的page数量,索引中不同值的比例,行和key的长度,key的分布。 优化器估计时并不包括任何类型的cache所产生的效果——它假定每次读都是一个磁盘IO操作。

但优化器并不是选择最好的执行计划,由于很多原因:
统计数据可能是错的。至少不准确,InnoDB在支持MVCC时,没有维护关于一个表中行数的精确统计。
代价度量不是精确等同于运行查询的真正开销,所以即使在统计精确的情况下,查询可能比MySQL估计的要更昂贵或者不昂贵。一个查询计划可能在某些情况下 虽然是顺序执行,但是实际更便宜,因为当读是顺序时磁盘IO稍快,或者当页已经cache在内存中时。
MySQL的优化可能并不符合你。可能你要最快的执行时间,但是MySQL不是真正理解快,它理解的是代价。
MySQL并不考虑并发执行的其它查询,它们会当前查询的运行速度。
MySQL并不总是做基于代价的优化。某些时候它只是使用一些规则,比如:如果有一个全文的MATCH()从句,如果有FULLTEXT索引,则使用它。 这时即使有可能更快的索引来使用可能也不会用。
优化器并不考虑不在它控制下的操作,比如执行存储过程或者用户定义的函数。
优化器不能总是估计每个可能的执行计划,所以可能错失一个优化的计划。

MySQL优化器高度复杂,有2种大概类型的优化,静态的和动态的。静态优化只要检查查询树即可,这独立于具体的值,比如WHERE从句的中常量。 动态查询基于上下文,依赖于很多因素,你可以将这称为运行时优化。
在执行prepared 语句或者存储过程时这种不同是很重要的。MySQL只要一次性做静态优化,但是它必须在它每次执行一个查询时重新评估动态优化。

下面是一些MySQL知道如何做的优化类型:

重新安排join的顺序
表的join并不一定就是按照你指定的顺序。
转换outer joins到inner joins
一个outer join可能在某些情况下等同于inner join。MySQL能识别并重写join。
应用代数上等价的规则
比如将5=5 and a > 5减少为a > 5。
count()、min()、max()优化
索引和列的非空性能帮助MySQL优化这些表达式,比如要找到要找到一个索引树最左列的最小值,MySQL只需要请求索引中第一行即可。它甚至可以在查询 优化阶段完成,在查询过程中将此作为常量。同样找到最大值也是类似。如果服务器使用了这个优化,你将在EXPLAIN中看到“select tables optimized away”。这意味着优化器在查询计划中将表删除了以常量取代之。
同样,COUNT(*)不带WHERE时也能在某些存储引擎上优化(比如MyISAM,保存了行的精确数目)。
减少常量表达式
当MySQL检测到一个表达式能缩减为一个常量时,它将在优化阶段这么做。比如,一个用户定义的变量在查询过程中如果没有变化则能被转换为一个常量。代数 表达式是另外一个例子。
可能奇怪的是,在查询优化阶段,一个查询可以缩减为一个常量。一个例子是在索引列上的MIN()。这也能扩展到一个在主键或者唯一索引上的常量搜寻。如果 一个WHERE从句在一个如此索引上使用一个常量表达式,MySQL能在查询开始找到这个值,优化器将能在剩余的查询中将这个值做常量对待。
比如:

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;
+----+-------------+------------+-------+----------------+-------+------+
| id | select_type | table      | type  | key            | ref   | rows |
+----+-------------+------------+-------+----------------+-------+------+
| 1  | SIMPLE      | film       | const | PRIMARY        | const | 1    |
| 1  | SIMPLE      | film_actor | ref   | idx_fk_film_id | const | 10   |
+----+-------------+------------+-------+----------------+-------+------+

MySQL分2步执行,对应于输出的2行,第一步是找到film表中希望的行。MySQL的优化器知道只有一行,因为在film_id列上有一个主 键,此时已经通过索引在查询优化阶段知道找到了多少行。因为查询优化器知道查找数量,表的ref 所以为const。
在第二阶段,MySQL依据第一阶段找到的行数将film_id列作为已知的常量处理。
覆盖索引
用来避免读取行数据。
子查询优化
MySQL能转换某些类型的子查询到更加有效的形式,
提前终止
典型的例子是LIMIT从句,比如MySQL检测到一个不可能的条件。它将终止整个查询。

mysql> EXPLAIN SELECT film.film_id FROM sakila.film WHERE film_id = -1;
+----+...+-----------------------------------------------------+
| id |...| Extra                                               |
+----+...+-----------------------------------------------------+
| 1  |...| Impossible WHERE noticed after reading const tables |
+----+...+-----------------------------------------------------+

在优化阶段查询即已停止,比如下面这个查询:
mysql> SELECT film.film_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL;
这个查询将消除那些有actor的film,每个film可能有多个actor,但是只要它找到一个actor,它将停止处理当前film并移到下一个, 因为它知道where从句禁止输出那个film。同样的“不同/不存在”优化能适用到某些类型的distinct,not exists()和left join查询。

等价关系的传播
当一个查询持有2个列并做等价关系比较时(比如在join条件中),可以在等价列之间传播where从句。比如
mysql> 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表,因为USING从句迫使2个列匹配。

IN()列表比较
在MySQL中排序了IN()中的值,使用二分查找来决定是否有一个值在里面,这是O(logn)。而OR()则是O(n)的。

表和索引统计
服务器层包含查询优化器,并不存储数据和索引的统计,这是存储引擎的工作。MySQL查询优化会向引擎询问关于表的统计。

MySQL的join执行策略
join这个术语广泛使用。多表联合查询就像for循环一样。。
MySQL执行每种查询几乎总是同样的方式。比如它处理FROM从句中的子查询是先执行它,将结果放到一个临时表中,然后处理这个表就像一个普通表那样 (就是derived table)。MySQL也同临时表执行UNION查询,它重写所有的RIGHT OUTER JOIN到等价的LEFT OUTER JOIN。
MySQL不可能总是以这种方式执行每个合法的SQL查询。比如。一个FULL OUTER JOIN:FULL OUTER JOIN can’t be executed with nested loops and backtracking as soon as a table with no matching rows is found, because it might begin with a table that has no matching rows.

执行计划
MySQL并不产生字节代码来执行查询,执行计划实际上是一棵指令树,查询执行引擎将依据此产生查询结果。最后的计划包含足够的信息来重建原始查询。如果 你执行EXPLAIN EXTENDED于一查询上,前面加上SHOW WARNINGS,你可以看到重建后的查询。任何一个多表查询都能表示为一棵树。但是这棵树并不是平衡的。
MySQL总是以一个表开始然后在下一个表中找到匹配的行。因此MySQL的查询执行计划总是一颗左边深的树。

连接优化器
连接优化器将决定执行多表查询最好的顺序。经常能以多种顺序join表得到相同的结果。连接优化器估计各种不同计划的代价,尝试选择代价最低的。下面是一 个查询,它的表格能以不同的顺序join而不改变结果:
mysql> SELECT film.film_id, film.title, film.release_year, actor.actor_id,
-> actor.first_name, actor.last_name
-> FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> INNER JOIN sakila.actor USING(actor_id);
可能mysql首先从film表开始,使用film_actor上的film_id索引来找到actor_id值,然后在actor表主键上查找。现在用 EXPLAIN看看MySQL如何查询:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: PRIMARY,idx_fk_film_id
key: PRIMARY
key_len: 2
ref: sakila.actor.actor_id
rows: 1
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: film
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.film_actor.film_id
rows: 1
Extra:
这个想象的不一样,MySQL首先从actor开始,这真的有效吗?STRAIGHT_JOIN关键词迫使join按照查询中指定的顺序进行处理。下面是 EXPLAIN的输出:
mysql> EXPLAIN SELECT STRAIGHT_JOIN film.film_id…\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: PRIMARY,idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 1
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.film_actor.actor_id
rows: 1
Extra:
这能显示MySQL要反转join顺序:这样做能在第一个表中检查稍少的行。
首先处理film表,要求591次刺探到film_actor表和actor表。
如果首先扫描actor表,在随后的表中它将只需要处理200次索引查找。
反向连接顺序能要求较少的回溯(backtracking)和重读(rereading)。为了确认优化器的选择,可以执行这两个不同的查询,并在每次执 行后检查Last_query_cost变量。连接顺序重排后估计代价大约是241,而强迫使用的连接顺序具有的代价是1154。
这是一个简单的例子,显示了MySQL连接优化器能重排查询使得代价较小,使用STRAIGHT_JOIN可以看到你所认为的查询的效率。
某些情况下查询不能重排,连接优化器可据此直接减小搜索空间。一个LEFT JOIN便是一个例子。

排序优化
排序操作可能是一个花代价的操作。
当MySQL不能使用索引来产生一个排序后的结果时,它必须对行本身进行排序。它可以在内存或者磁盘上进行,但是都被称为文件排序,即使并不实际使用文 件。
如果要排序的值正好适合进sort buffer,MySQL能完全在内存中进行排序,这称为quicksort。如果MySQL不能在内存中这样做,它将在磁盘上对上chunk进行排序。 它对每个chunk使用快速排序后使用归并排序。
有2种文件排序算法:
两路(old)
读行指针和ORDER BY列,排序,然后扫描排序了的列表重读行后输出。
这个算法可能很昂贵,因为它2次读表,第二次读取引起很大量的随机IO。对MyISAM尤其昂贵,因为它使用一个系统调用来取出每个行(因为MyISAM 依赖于OS的cache来持有数据)。另一方面,它在排序中存储了小部分数据,因此如果要排序的行完全在内存中,排序会稍廉价,产生最终结果的代价较低。
一路(new)
读取所有需要查询的列,通过ORDER BY中的列排序,然后扫描排序后的列表输出特定的列。
这个算法只在MySQ 4.1和更新的版本中有,它更有效,特别在IO密集数据库中,因为避免2次读表,将随机IO换取为顺序IO。实际上,它可能需要更多的空间,因为它持有所 有需要的列,而不仅仅是那些用来对行排序的列。这意味着sort buffer中可以存较少的集合,filesort将不得不进行更多趟的归并操作。
MySQL可能对一个filesort使用比你期望的更多临时存储空间,因为它会为每个将排序的集合分配一个固定大小的记录。这些记录都是足够大能容下最 大可能元素,包括每个VARCHAR列的全部长度。
当排序一个join时,MySQL在查询阶段可能以2个阶段进行filesort。如果ORDER BY从句仅仅引用连接顺序中第一个表中的列,MySQL能filesort这个表并进行join。如果这发生了,EXPLAIN将显示Using filesort于Extra列。不然,MySQL必须存储查询的结果到一个临时表,在join完成之后对临时表进行filesort。在这种情况 下,EXPLAIN显示“Using temporary; Using filesort“于Extra列。如果有一个LIMIT,它将在filesort之后被应用,因此。临时表和filesort可能很大。
参考Optimizing for filesort。


Related posts(相关文章):