示例表

  1. CREATE TABLE single_table (
  2.     id INT NOT NULL AUTO_INCREMENT,
  3.     key1 VARCHAR(100),
  4.     key2 INT,
  5.     key3 VARCHAR(100),
  6.     key_part1 VARCHAR(100),
  7.     key_part2 VARCHAR(100),
  8.     key_part3 VARCHAR(100),
  9.     common_field VARCHAR(100),
  10.     PRIMARY KEY (id),
  11.     KEY idx_key1 (key1),
  12.     UNIQUE KEY idx_key2 (key2),
  13.     KEY idx_key3 (key3),
  14.     KEY idx_key_part(key_part1, key_part2, key_part3)
  15. ) Engine=InnoDB CHARSET=utf8;

Condition filtering介绍

MySQL中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询成本由下边两个部分构成：

  • 单次查询驱动表的成本
  • 多次查询被驱动表的成本

我们把对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名：fanout）。很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。

当查询优化器想计算整个连接查询所使用的成本时，就需要计算出驱动表的扇出值，有的时候扇出值的计算是很容易的，比如下边这两个查询：

  • 全表扫描
SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2;
  • 使用 idx_key2 索引
SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
WHERE s1.key2 >10 AND s1.key2 < 1000;

有的时候扇出值的计算就变得很棘手，比方说下边几个查询：

  • 查询优化器不会真正的去执行查询，所以它只能猜这9693记录里有多少条记录满足common_field > ‘xyz’条件。
SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
    WHERE s1.common_field > 'xyz';
  • 本查询可以使用idx_key2索引，所以只需要从符合二级索引范围区间的记录中猜有多少条记录符合common_field > ‘xyz’条件，也就是只需要猜在95条记录中有多少符合common_field > ‘xyz’条件。
SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
    WHERE s1.key2 > 10 AND s1.key2 < 1000 AND
          s1.common_field > 'xyz';
  • 在驱动表s1选取idx_key2索引执行查询后，优化器需要从符合二级索引范围区间的记录中猜有多少条记录符合下边两个条件：
    • key1 IN (‘a’, ‘b’, ‘c’)
    • common_field > ‘xyz’
SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
    WHERE s1.key2 > 10 AND s1.key2 < 1000 AND
          s1.key1 IN ('a', 'b', 'c') AND
          s1.common_field > 'xyz';

说了这么多，其实就是想表达在这两种情况下计算驱动表扇出值时需要靠猜：

  • 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条。
  • 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

设计MySQL的大叔把这个猜的过程称之为condition filtering。当然，这个过程可能会使用到索引，也可能使用到统计数据，也可能就是设计MySQL的大叔单纯的瞎猜，

小贴士： 在MySQL 5.7之前的版本中，查询优化器在计算驱动表扇出时，如果是使用全表扫描的话，就直接使用表中记录的数量作为扇出值，如果使用索引的话，就直接使用满足范围条件的索引记录条数作为扇出值。在MySQL 5.7中，设计MySQL的大叔引入了这个condition filtering的功能，就是还要猜一猜剩余的那些搜索条件能把驱动表中的记录再过滤多少条，其实本质上就是为了让成本估算更精确。 我们所说的纯粹瞎猜其实是很不严谨的，设计MySQL的大叔们称之为启发式规则（heuristic），大家有兴趣的可以再深入了解一下哈。

两表连接的成本分析

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以想要得到最优的查询方案只需要：

  • 分别为驱动表和被驱动表选择成本最低的访问方法

对于内连接来说，驱动表和被驱动表的位置是可以互换的，所以需要考虑两个方面的问题：

  • 不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。
  • 然后分别为驱动表和被驱动表选择成本最低的访问方法

以内连接为例

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
    ON s1.key1 = s2.common_field 
    WHERE s1.key2 > 10 AND s1.key2 < 1000 AND 
          s2.key2 > 1000 AND s2.key2 < 2000;

可以选择的连接顺序有两种：

  • s1连接s2，也就是s1作为驱动表，s2作为被驱动表。
  • s2连接s1，也就是s2作为驱动表，s1作为被驱动表。

使用s1作为驱动表的情况

分析对于驱动表的成本最低的执行方案

涉及s1表单表的搜索条件:

  • s1.key2 > 10 AND s1.key2 < 1000

所以这个查询可能使用到idx_key2索引，从全表扫描和使用idx_key2这两个方案中选出成本最低的那个，这个过程我们上边都唠叨过了，很显然使用idx_key2执行查询的成本更低些。

分析对于被驱动表的成本最低的执行方案

涉及被驱动表s2的搜索条件就是：

  • s2.common_field = 常数（这是因为对驱动表s1结果集中的每一条记录，都需要进行一次被驱动表s2的访问，此时那些涉及两表的条件现在相当于只涉及被驱动表s2了。）
  • s2.key2 > 1000 AND s2.key2 < 2000

很显然，第一个条件由于common_field没有用到索引，所以并没有什么卵用，此时访问s2表时可用的方案也是全表扫描和使用idx_key2两种，假设使用idx_key2的成本更小。

所以此时使用s1作为驱动表时的总成本就是（暂时不考虑使用join buffer对成本的影响）：

  • 使用idx_key2访问s1的成本 + s1的扇出 × 使用idx_key2访问s2的成本

使用s2作为驱动表的情况

分析对于驱动表的成本最低的执行方案

涉及s2表单表的搜索条件有哪些：

  • s2.key2 > 1000 AND s2.key2 < 2000

所以这个查询可能使用到idx_key2索引，从全表扫描和使用idx_key2这两个方案中选出成本最低的那个，假设使用idx_key2执行查询的成本更低些。

分析对于被驱动表的成本最低的执行方案

涉及被驱动表s1的搜索条件就是：

  • s1.key1 = 常数
  • s1.key2 > 10 AND s1.key2 < 2000

这时就很有趣了，使用idx_key1可以进行ref方式的访问，使用idx_key2可以使用range方式的访问。这是优化器需要从全表扫描、使用idx_key1、使用idx_key2这几个方案里选出一个成本最低的方案。这里有个问题啊，因为idx_key2的范围区间是确定的：(10, 1000)，怎么计算使用idx_key2的成本我们上边已经说过了，可是在没有真正执行查询前，s1.key1 = 常数中的常数值我们是不知道的，怎么衡量使用idx_key1执行查询的成本呢？其实很简单，直接使用索引统计数据就好了（就是索引列平均一个值重复多少次）。一般情况下，ref的访问方式要比range成本更低，这里假设使用idx_key1进行对s1的访问。

使用s2作为驱动表时的总成本就是：

  • 使用idx_key2访问s2的成本 + s2的扇出 × 使用idx_key1访问s1的成本

最后优化器会比较这两种方式的最优访问成本，选取那个成本更低的连接顺序去真正的执行查询。从上边的计算过程也可以看出来，连接查询成本占大头的其实是驱动表扇出数 x 单次访问被驱动表的成本，所以我们的优化重点其实是下边这两个部分：

  • 尽量减少驱动表的扇出
  • 对被驱动表的访问成本尽量低

我们需要尽量在被驱动表的连接列上建立索引，这样就可以使用ref访问方法来降低访问被驱动表的成本了。如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了。

多表连接的成本分析

首先要考虑一下多表连接时可能产生出多少种连接顺序：

  • 对于n表连接的话，则有 n × (n-1) × (n-2) × ··· × 1种连接顺序，就是n的阶乘种连接顺序，也就是n!。

实真的是要都算一遍，不过设计MySQL的大叔们想了很多办法减少计算非常多种连接顺序的成本的方法：

  • 提前结束某种顺序的成本评估

MySQL在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就压根儿不对该连接顺序继续往下分析了。

  • 系统变量optimizer_search_depth

为了防止无穷无尽的分析各种连接顺序的成本，设计MySQL的大叔们提出了optimizer_search_depth系统变量，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与optimizer_search_depth值相同数量的表进行穷举分析。很显然，该值越大，成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长，否则得到不是很好的执行计划，但可以省掉很多分析连接成本的时间。

  • 根据某些规则压根儿就不考虑某些连接顺序

启发式规则（就是根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序压根儿就不分析，这样可以极大的减少需要分析的连接顺序的数量，但是也可能造成错失最优的执行计划。他们提供了一个系统变量optimizer_prune_level来控制到底是不是用这些启发式规则。