Mysql 学习（十）基于成本的优化 二

连接查询成本

• 上一节说完了单表查询的成本计算，这里我们就需要讲解一些连接查询的复杂计算
• 之前说过连接查询采用的是嵌套循环连接算法，所以从步骤来说，成本计算可以分为两步：
  • 单次查询驱动表的成本
  • 根据驱动表的记录，多次查询被驱动表的成本
• 这里提一个概念，我们一般把单次查询驱动表得到的条数称之为扇出，因为第二步是根据驱动表的记录去查找被驱动表，所以我们可以简单的得到一个结论，当驱动表扇出的值小，则对被驱动表查找的次数也少，连接查询的成本就会变低。
• 举个例子：SELECT * FROM index_value_table AS s1 INNER JOIN index_value_table AS s2;
  • 统计数据中s1表的记录行数是9693，也就是说 s1 的扇出值为 9693
• 而这个扇出值不是我们计算出来的，而是大概估算出来的，而这个估算的过程叫 condition filtering，具体怎么估算是底层的实现，这里就不多赘述。
• 下面我们就来真正讲解一下连接查询的成本计算公式

两表连接查询

• 连接查询的成本计算公式：连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本
• 对于 左外连接 和 右外连接 查询而言，驱动表是固定的，所以只需要为驱动表和被驱动表选择成本最低的访问方法
• 对于 内连接 而言，驱动表和被驱动表 的 角色可以相互换，所以需要考虑两个问题：
  • 不同的表作为驱动表最终的查询成本可能是不同的
  • 分别为驱动表和被驱动表选择成本最低的访问方法
• 举个例子： SELECT * FROM index_value_table AS s1 INNER JOIN index_value_table AS s2 ON s1.value1 = s2.common_field WHERE s1.value2 > 10 AND s1.value2 < 1000 AND s2.value2 > 1000 AND s2.value2 < 2000;
• 分析一下这个语句，这个可选的连接顺序有两种：
  • s1 连接 s2，也就是s1 作为驱动表，s2 作为被驱动表
  • s2 连接 s1 ，也就是s2 作为驱动表，s1 作为被驱动表
• mysql的查询优化器，需要分别考虑这两种链接的成本，所以我们接着来分别分析两个查询情况
• s1 连接 s2，也就是s1 作为驱动表，s2 作为被驱动表：
  • 先来看 s1 表的搜索条件 s1.value2 > 10 AND s1.value2 < 1000，可以用索引idx_key2找出对应的数据
  • 再来看 s2表的搜索条件 s2.common_field = 常数 和 s2.value2 > 1000 AND s2.value2 < 2000，很显然有索引idx_key2，对比全表扫描，我们肯定会使用idx_key2索引进行查找
  • 所以成本使用idx_key2访问s1的成本 + s1的扇出 × 使用idx_key2访问s2的成本
• s2 连接 s1 ，也就是s2 作为驱动表，s1 作为被驱动表：
  • 先来看 s2 表的搜索条件 s1.value2 > 1000 AND s1.value2 < 2000，可以用索引idx_key2找出对应的数据
  • 再来看 s2表的搜索条件 s2.value1= 常数 和 s2.key2 > 10 AND s2.key2 < 1000，这个时候索引就有 idx_key1 的ref方式访问 和 idx_key2 的 range 方式访问。所以优化器需要从 全表扫描和上面两种方式中选一个成本最低的方案。
  • 所以成本使用idx_key2访问s2的成本 + s2的扇出 × 使用idx_key1访问s1的成本
• 最后优化器会比较这两种方式的最优访问成本，选取那个成本更低的连接顺序去真正的执行查询。
• 从上面这个例子看出来，成本中占大头的是 扇出记录数 x 访问被驱动表的成本，所以我们一般优化都是有下面两个方面：
  • 尽量减少驱动表的扇出
  • 对被驱动表的访问成本尽量低

多表连接查询

• 刚才我们讲了两个表的连接成本计算，那要是多表连接的话怎么办？
• 两个表 是 2x1=2 种连接顺序需要计算
• 三个表 是 3x2x1 = 6 种连接顺序需要计算
• n个表 是 nx(n-1)…x3x2x1 种连接顺序需要计算
• 由上面的规则可以知道，如果连接的话，计算成本的性能消耗就太大了，所以我们需要减少这样，有下面三种方法：
  • 提前结束某种顺序的成本评估：MySQL在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就压根儿不对该连接顺序继续往下分析了。
  • 系统变量optimizer_search_depth：为了防止无穷无尽的分析各种连接顺序的成本，设计MySQL的大佬们提出了optimizer_search_depth系统变量，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与optimizer_search_depth值相同数量的表进行穷举分析。
  • 根据某些规则压根儿就不考虑某些连接顺序：即使是有上面两条规则的限制，但是分析多个表不同连接顺序成本花费的时间还是会很长，所以设计MySQL的大佬干脆提出了一些所谓的启发式规则（就是根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序压根儿就不分析，这样可以极大的减少需要分析的连接顺序的数量，但是也可能造成错失最优的执行计划。他们提供了一个系统变量optimizer_prune_level来控制到底是不是用这些启发式规则。