高权重域名做网站,仙游网站建设,做ppt找图片在哪个网站,seo关键字排名查询性能优化
查询优化器
9.等值传播 如果两个列的值通过等式关联#xff0c;那么MySQL能够把其中一个列的WHERE条件传递到另一列上。例如#xff0c;我们看下面的查询:
mysql SELECT film.film_id FROM film- INNER JOIN film_actor USING(film_id)- WHERE f…查询性能优化
查询优化器
9.等值传播 如果两个列的值通过等式关联那么MySQL能够把其中一个列的WHERE条件传递到另一列上。例如我们看下面的查询:
mysql SELECT film.film_id FROM film- INNER JOIN film_actor USING(film_id)- WHERE film_id 500;因为这里使用了film_id字段进行等值关联MySQL知道这里的WHERE子句不仅适用于film表而且对于film_actor表同样适用。如果适用的是其他的数据库管理系统可能还需要手动通过一些条件来告知优化器这个WHERE条件适用于两个表那么写法就会如下:
... WHERE film.film_id 500 AND film_actor.film_id 500在MySQL中这是不必要的这样写反而会让查询更难维护。
10.列表IN()的比较 在很多数据库系统中IN()完全等同于多个OR条件的子句因为这两者是完全等价的。在MySQL中这点是不成立的,MySQL将IN()列表中的数据先进行排序然后通过二分查找的方式来确定列表中的值是否满足条件这是一个O(logn)复杂度的操作等价地转换成OR查询的复杂度为O(n)对于IN()列表中有大量取值的时候MySQL的处理速度将会更快。
上面列举的远不是MySQL优化器的全部MySQL还会做大量其他的优化。上面的例子已经足以说明优化器的复杂性和智能性了。如果说从上面的讨论中应该学到什么那就是不要自以为比优化器更聪明。最终你可能会占点便宜但是更有可能会使查询变得更加复杂而难以维护而最终的收益却未零。让优化器按照它的方式工作就可以了。当然虽然优化器已经很智能了但是有时候也无法给出最优的结果。有时候你可能比优化器更了解数据例如由于应用逻辑使得某些条件总是成立还有时优化器缺少某种功能特性如哈希索引再如前面提到的从优化器的执行成本角度评估出来的最优执行计划实际运行中可能比其他的执行计划更慢。如果能够确认优化器给出的不是最佳选择并且清楚背后的原理那么也可以帮助优化器做进一步的优化。例如可以在查询中添加hint提示也可以重写查询或者重新设计更优的库表结构或者添加更合适的索引
数据和索引的统计信息 重新回忆一下MySQL的架构MySQL架构由多个层次组成。在服务器层有查询优化器却没有保存数据和索引的统计信息。统计信息由存储引擎实现不同的存储引擎可能会存储不同的统计信息(也可以按照不同的格式存储统计信息)。某些引擎例如Archive引擎则根本没有存储任何统计信息。因为服务器层没有任务统计信息所以MySQL查询优化器在生成查询的执行计划时需要向存储引擎获取相应的统计信息。存储引擎则提供给优化器对应的统计信息包括:每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引长度、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。
MySQL如何执行关联查询
MySQL中关联(join)一次所包含的意义比一般意义上理解的要更广泛。总的来说MySQL认为任何一个查询都是一次关联——并不仅仅是一个查询需要到两个表匹配才叫关联,所以在MySQL中每一个查询每一个片段(包括子查询甚至基于单表的SELECT)都可能是关联。所以理解MySQL如何执行关联查询至关重要。我们先来看一个UNION查询的例子对于UNION查询MySQL先将一系列的单个查询结果放到一个临时表中然后再重新读出临时表数据来完成UNION查询。在MySQL的概念中每个查询都是一次关联所以读取结果临时表也是一次关联。当前MySQL关联执行的策略很简单:MySQL对任何关联都执行嵌套循环关联操作即MySQL先在一个表中循环取出单条数据然后再嵌套循环到下一个表中寻找匹配的行依次下去知道找到所有表中匹配的行为止。然后根据各个表匹配的行返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行如果最后一个关联表无法找到更多的行以后MySQL返回上一层次关联表看是否能够找到更多的匹配记录依此类推迭代执行。按照这样的方式查找第一个表记录再嵌套查询下一个关联表然后回溯到上一个表在MySQL中是通过嵌套循环的方式实现——正如其名嵌套循环关联。请看下面的例子中的简单查询:
SELECT
tbl1.col1,
tbl2.col2
FROM
tbl1
INNER JOIN tbl2 USING ( col3 )
WHERE
tbl1.col1 IN (5,6)假设MySQL按照查询中的表顺序进行关联我们则可以用下面的伪代码表示MySQL将如何完成这个查询:
outer_iter iterator over tbl1 where col1 IN (5,6)
outer_row outer_iter.next
while outer_row
inner_iter iterator over tbl2 where col3 outer_row.col3
inner_row inner_iter.next
while inner_now
output [outer_row.col1, inner_row.col2]
inner_row inner_iter.next
endouter_row outer_iter.next
end上面的执行计划对于单表查询和多表关联查询都适用如果是一个单表查询那么只需完成上面外层的基本操作。对于外连接上面的执行过程仍然适用。例如我们将上面查询修改如下:
SELECT
tbl1.col1,
tbl2.col2
FROM
tbl1
LEFT OUTER JOIN tbl2 USING ( col3 )
WHERE
tbl1.col1 IN (5,6)对应的伪代码如下
outer_iter iterator over tbl1 where col1 IN (5,6)
outer_row outer_iter.next
while outer_row
inner_iter iterator over tbl2 where col3 outer_row.col3
inner_row inner_iter.next
if inner_row
while inner_row
output [outer_row.col1, inner_row.col2]
inner_row inner_iter.next
end
else
output [outer_row.col1, NULL]
end
outer_row outer_iter.next
end另一种可视化查询执行计划的方法是根据优化器执行的路径绘制出对应的泳道图。如图所示绘制了前面示例中内连接的泳道图。如图所示从本质上说MySQL对所有的类型的查询都以同样的方式运行。例如MySQL在FROM子句中遇到子查询时先执行子查询并将其结果放到一个临时表中(MySQL的临时表是没有任何索引的在编写复杂的子查询和关联查询的时候需要注意这一点。这一点对UNION查询也一样)。然后将这个临时表当作一个普通表对待(正如其名派生表)。MySQL在执行UNION查询时也使用类似的临时表在遇到右外连接的时候MySQL将其改写成等价的左外连接。简而言之当前版本的MySQL会将所有的查询类型都转换成类似的执行计划。不过不是所有的查询都可以转换成上面的形式。例如全外连接就无法通过嵌套玄幻和回溯的方式完成这是当发现关联表中没有找到任何匹配行的时候则可能是因为关联是恰好从一个没有任何匹配的表开始。这大概也是MySQL并不支持全外连接的原因还有些场景虽然可以转换成嵌套循环的方式但是效率却非常差。
执行计划 和很多其他关系数据库不同MySQL并不会生成查询字节码执行查询。MySQL生成查询的一棵指令书然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行EXPLAIN EXTENDED后再执行SHOW WARNINGS,就可以看到重构出的查询。(MySQL根据执行计划生成输出。这和原查询有完全相同的语义但是查询语句可能并不完全相同)。任何多表查询都可以使用一棵树表示例如可以按照如图所示执行一个四表的关联操作.在计算机科学中这被成为一棵平衡树。但是这并不是MySQL执行查询的方式。正如前面提到的MySQL总是会从一个表开始一直嵌套循环、回溯完成所有表关联。所以MySQL的执行计划总是如图所示是一棵左侧深度优先的树
关联查询优化器
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表的主键找到对应的记录。Oracle用户会用下面的术语描述:“film表作为驱动表先查找film_actor表然后以此结果为驱动表再查找actor表”。这样做效率应该会不错我们再使用EXPLAIN 看看MySQL将如何执行这个查询:
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: actorpartitions: NULLtype: ALL
possible_keys: PRIMARYkey: NULLkey_len: NULLref: NULLrows: 200filtered: 100.00Extra: NULL
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: film_actorpartitions: NULLtype: ref
possible_keys: PRIMARY,idx_fk_film_idkey: PRIMARYkey_len: 2ref: sakila.actor.actor_idrows: 27filtered: 100.00Extra: Using index
*************************** 3. row ***************************id: 1select_type: SIMPLEtable: filmpartitions: NULLtype: eq_ref
possible_keys: PRIMARYkey: PRIMARYkey_len: 2ref: sakila.film_actor.film_idrows: 1filtered: 100.00Extra: NULL
3 rows in set, 1 warning (0.00 sec)这和我们前面给出的执行计划完全不同。MySQL从actor表开始(我们从上面的EXPLAIN 结果的第一行输出可以看出这点)。然后与我们前面的计划按照相反的顺序进行关联。这样是否效率更高呢我们来看看我们先使用STRIGHT_JOIN关键字按照我们之前的顺序执行这里是对应的EXPLAIN输出结果:
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: filmpartitions: NULLtype: ALL
possible_keys: PRIMARYkey: NULLkey_len: NULLref: NULLrows: 1000filtered: 100.00Extra: NULL
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: film_actorpartitions: NULLtype: ref
possible_keys: PRIMARY,idx_fk_film_idkey: idx_fk_film_idkey_len: 2ref: sakila.film.film_idrows: 5filtered: 100.00Extra: Using index
*************************** 3. row ***************************id: 1select_type: SIMPLEtable: actorpartitions: NULLtype: eq_ref
possible_keys: PRIMARYkey: PRIMARYkey_len: 2ref: sakila.film_actor.actor_idrows: 1filtered: 100.00Extra: NULL
3 rows in set, 1 warning (0.00 sec)我们来分析一下为什么MySQL会将关联顺序倒转过来:可以看到关联顺序倒转后的第一个关联表只需要扫描很少的行数(严格来说MySQL并不根据读取的记录来选择最优的执行计划。实际上MySQL通过预估需要读取的数据页来选择读取的数据页越少越好。不过读取的记录数通常能够很好地反应一个查询的成本)。在两种关联顺序下第二个和第三个关联表都是根据索引查询速度都很快不同地是需要扫描的索引项的数量是不同的:
1.将film表作为第一个关联表时会找到1000条记录然后对film_actor和actor表进行嵌套循环查询2.如果MySQL选择首先扫描actor表只会返回200条记录进行后面的嵌套循环查询。
换句话说倒转的关联顺序会让查询进行跟梢的嵌套循环和回溯操作。为了验证优化器的选择是否正确我们单独执行两个查询并且看看对应的Last_query_cost状态值。我们看到倒转的关联顺序的预估成本为241,而原来的查询的预估成本为1154. 这个简单的例子主要想说明MySQL是如何选择合适的关联顺序来让查询执行的成本尽可能的低。重新定义关联的顺序是优化器非常重要的一部分功能。不过有的时候优化器给出的并不是最优的关联顺序。这时可以使用STRAIGHT_JOIN关键字重写查询让优化器按照你认为的最优的关联顺序执行——不过老实说人的判断很难那么精准。绝大多数时候优化器做出的选择都比普通人的判断要更准确。关联优化器会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树。如果可能优化器会遍历每一个表然后逐个做嵌套循环计算每一棵可能的执行计划树的成本最后返回一个最优的执行计划。 不过糟糕的是如果有超过N个表的关联那么需要检查N的阶乘种关联顺序。我们称之为所有可能的执行计划的搜索空间搜索空间的增长速度非常快——例如若是10个表的关联那么共有3 628 800种不同的关联顺序当搜索空间非常大的时候优化器不可能注意评估每一种关联顺序的成本。这时优化器选择使用贪婪搜索的方式查找最优的关联顺序。实际上当需要关联的表超过optmizer_search_depth的限制的时候就会选择贪婪搜索模式了。在MySQL这些年的发展过程重优化器积累了很多启发式的优化策略来加速执行计划的生成。绝大多数情况下这都是有效地但因为不会去计算每一种关联顺序的成本所以偶尔也会选择一个不是最优的执行计划。有时各个查询的顺序并不能随意安排这时关联优化器可以根据这些规则大大减少搜索空间例如左连接、相关子查询(后面将讨论子查询)。这是因为后面的表的查询需要依赖于前面表的查询结果。这种依赖关系通常可以帮助优化器大大减少需要扫描的的执行计划数量
