MySQL查询优化器

目的

了解查询优化器的优化策略
了解和查看查询优化器优化后的执行计划
了解优化器是怎么优化的
了解优化器在关联语句中的处理
了解一些查询优化器的提示

MySQL查询优化器的优化策略

简单分为 静态优化 和 动态优化。

一、静态优化(“编译时优化”):

可以直接对解析树进行分析并完成优化。
优化器通过一些简单的代叔变换将WHERE条件转化为另一种等价形式。
静态优化不依赖特别的数值，比如WHERE条件里带一些常数等。
静态优化在第一次完成后一直有效，即使使用不同的参数值执行查询也不会发生变化。

二、动态优化(“运行时优化”):

动态优化和查询上下文有关或者其他因素有关，比如WHERE条件中的取值，索引中条目对应的数据行数等。
每次查询都需要重新评估。

MySQL能够处理的优化类型

先说一点，MySQL优化器使用了很多复杂的优化技巧把查询转化为执行计划。“不要试着比优化器更聪明”，通常都应让MySQL按照自己优化的方式执行语句，绝大多数情况优化器都是更优的。
你也可以通过EXPLAIN EXTENDED SELECT … … ; SHOW WARNINGS; 查看最终优化后的执行sql。
如何使用“EXPLAIN EXTENDED”可参考官方文档上的Extended EXPLAIN Output Format

重新定义关联表顺序。
数据表的关联并不总是按照查询中指定顺序进行的。
将外链接转化为内连接。
优化器可以根据某些因素(比如WHERE条件，库表结构等)，使得一个外连接等价于一个内连接。
等价变换规则。
MySQL通过等价变换来简化并规范表达式。MySQL可以合并和减少比较。比如 (5=5 AND a>5)会被改写为a>5。(a < b AND b=c) AND a=5会被改写为(b>5 AND b=c AND a=5)
优化 COUNT()、MIN()、MAX()。
查找某列最大/最小值，该列又有索引，查找最大值，则会直接找最后一行；最小值，则直接找第一行。因为索引已经排好序了。可以从EXPLAIN中看到：“Select tables optimized away”。这说明已经从执行计划中移除该表并用常数取代。
预估并转化为常数表达式
如果一个表达式可以被简化为一个常量，那么这个表达式就会被转换。在WHERE 、USING、ON这些连接条件强制值相等的条件中，常量具有传递性 ` EXPLAIN SELECT table_a.id, table_b.id FROM table_b INNER JOIN table_a ON table_a.tb_id = table_b.id WHERE table_b.id = 1

![](https://ww1.sinaimg.cn/large/87faef88ly1fqnue0bqrbj20r102ut8o.jpg)

6. <font face="华文新魏" size="3">覆盖索引</font>  
当索引包含查询需要的列时，MySql就可以使用索引来避免读取行数据。

7. <font face="华文新魏" size="3">子查询优化</font>  
MySQL可以将某些类型的子查询转换成相等的效率更高的形式。

8. <font face="华文新魏" size="3">提前终止查询</font>  
- MySQL在发现已经满足查询需求时，会立刻终止查询。
- MySQL检测一个不成立的条件也会立刻返回空结果。

EXPLAIN SELECT * FROM table_a WHERE id = - 1;

![](https://ww1.sinaimg.cn/large/87faef88ly1fqongff9j4j20sx01rjra.jpg)

9. <font face="华文新魏" size="3">等值传播</font>  
如果两个列的值通过等式关联，那么MySQL能把其中一个列的WHERE条件传递到另一个列。
比如：

SELECT table_a.id, table_b.id FROM table_b INNER JOIN table_a ON table_a.tb_id = table_b.id WHERE table_b.id < 100;

MySQL会判断把WHERE后面的关联作用于table_a表，等价于

SELECT table_a.id, table_b.id FROM table_b INNER JOIN table_a ON table_a.tb_id = table_b.id WHERE table_b.id < 100 AND table_a.tb_id < 100;

10. <font face="华文新魏" size="3">列表IN()的比较</font>  
MySql会对IN()里面的数据进行排序，然后用二分法查找某个值是否在列表中，这个算法的效率是O(Log n)。这其他数据库等价转换为多个OR条件连接的复杂度O(n)来说，IN()里大量取值时会更快。

## MySQL如何关联查询
MySQL关联查询策略:  
其实就是嵌套循环查询。MySQL先从第一个表循环读，然后再嵌套循环到下一个表寻找匹配，如此反复，直到找到所有表的匹配的行为止。 

举个栗子：  
- 内连接sql

SELECT tbl_user.name, tbl_bankcard.bankcard FROM tbl_user INNER JOIN tbl_bankcard ON tbl_user.id = tbl_bankcard.user_id WHERE tbl_user.moblie in (‘18611112222’,’18611113333’)

MySQL在查询这条SQL时，用伪代码表示查询过程如下:
```ruby
outer_iter = iterator over tbl_user where moblie in ('18611112222','18611113333');
outer_row = outer_iter.next;
while outer_row:
    inner_iter = iterator over tbl_bankcard where user_id = outer_row.id;
    inner_row = inner_iter.next;
    while inner_row:
        output [outer_row.name, inner_row.bankcard]
        inner_row = inner_iter.next;
    end
    outer_row = outer_iter.next;
end

再看这个SQL左外连接版本：

SELECT tbl_user.name, tbl_bankcard.bankcard 
FROM tbl_user LEFT JOIN tbl_bankcard ON tbl_user.id = tbl_bankcard.user_id
WHERE tbl_user.moblie in ('18611112222','18611113333')

MySQL在查询这条SQL时，用伪代码表示查询过程如下:

outer_iter = iterator over tbl_user where moblie in ('18611112222','18611113333');
outer_row = outer_iter.next;
while outer_row:
  inner_iter = iterator over tbl_bankcard where user_id = outer_row.id;
  inner_row = inner_iter.next;
  if inner_row:
      while inner_row:
          output [outer_row.name, inner_row.bankcard]
          inner_row = inner_iter.next;
      end
  else 
      output [outer_row.name, NULL]
  end
  outer_row = outer_iter.next;
end

基本上MySQL所有类型的查询都是这种方式运行。包括子查询，也是生成一张临时表，被当做普通表进行循环嵌套。以及右外链接也是会改写成等价的左外连接。

多表关联的一种方式：

graph TD
    B(Join) --> A[Join]
    C(Join) --> A
    D(tbl1) --> B
    E(tbl2) --> B
    F(tbl3) --> C
    G(tbl4) --> C

但是MySQL是通过从一个表开始一直嵌套循环的方式：

graph TD
    B(Join) --> A[Join]
    C(tbl4) --> A
    D[Join] --> B
    E[tbl3] --> B
    F[tbl1] --> D
    G[tbl2] --> D

关联查询优化器

MySQL查询优化器中最重要的一部分。它决定了多表查询的顺序。它评估不同顺序的成本选择成本最小的一个。
有时优化器给出的并不是最优的关联顺序，可使用STRAIGHT_JOIN关键字替换JOIN关键字重写查询。还是那句，“不要试着比优化器更聪明”。
不过，如果有超过n个表的关联，那么需要检查n的阶乘种关联顺序。我们称之为所有可能的执行计划的“搜索空间”。实际上，当需要关联的表超过 optimizer_search_depth 的限制的时候，就会选择“贪婪”搜索模式。

查询优化器的提示(hint)

用于控制查询执行计划。列举一些课使用的提示：

HIGH_PRIORITY 和 LOW_PRIORITY
当多条语句同时访问数据库时，设置语句优先级。HIGH_PRIORITY会使语句放在表的队列的最前面，LOW_PRIORITY则相反。这两个提示只对使用表锁的存储引擎有效。
DELAYED
这个提示用于INSERT和REPLACE。使用该提示会将插入的行数据放入缓冲区，然后在表空闲时批量写入数据。适合于日志插入等场景。但并不是所有存储引擎都支持，还会导致函数 LAST_INSERT_ID()无法正常工作。
STRAIGHT_JOIN
这个提示可用于SELECT语句中SELECT关键字之后，也可放置于两个关联表之间。该提示作用一是让查询中的表按语句出现的顺序关联。作用二是固定前后两个表的关联顺序。
SQL_SMALL_RESULT 和 SQL_BIG_RESULT
这个提示只对SELECT有效。它告诉优化器对GROUP BY或者DISTINCT查询如果使用临时表和排序。SQL_SMALL_RESULT会让优化器认为结果集很小，将结果放在内存中的索引临时表中，避免排序。SQL_BIG_RESULT 则告诉优化器结果集很大，在磁盘临时表进行排序。
SQL_BUFFER_RESULT
这个提示告诉优化器将结果放在临时表中，并且尽快释放掉表锁。
SQL_CACHE 和 SQL_NO_CACHE
这个提示告诉MySQL是否将结果集放在查询缓存中。
USING INDEX、IGNORE INDEX 和 FORCE INDEX
这几个提示分别告诉优化器使用或者不使用或者强制使用索引。

控制优化器的一些参数

SHOW VARIABLES LIKE "optimizer_%";

optimizer_search_depth
控制穷举执行计划的限度。
optimizer_prune_level
默认打开的，让优化器根据需要扫描的行数来决定是否跳过某些执行计划
optimizer_switch
此变量包含了一些开关优化器特性的标志位。

总结

MySQL优化器做了很多工作把SQL语句变成更优的查询方式
使用 EXPLAIN EXTENDED … SHOW WARNINGS关键字查看优化后的指令
MySQL的关联查询是“嵌套循环”的
可使用查询优化提示控制查询执行计划，但是，“不要试着比优化器更聪明”。

目的

MySQL查询优化器的优化策略

MySQL能够处理的优化类型

关联查询优化器

查询优化器的提示(hint)

控制优化器的一些参数

总结

相关文章：