查询成本
Explain 输出成本
前面我们已经对MySQL查询优化器如何计算成本有了比较深刻的了解。但是Explain语句输出中缺少了一个衡量执行计划好坏的重要属性——成本。
不过MySQL已经输出中缺少了一个查看某个执行计划花费的成本的方式：
在 EXPLAIN 单词和真正的查询语句中间加上FROMAT=JSON。
这样我们就可以得到一个json格式的执行计划，里边包含该计划花费的成本，比如这样：
explain format= json
SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S') AND expire_time> '2021-03-22 18:28:28' AND expire_time<= '2021-03-22 18:35:09' AND insert_time> expire_time AND order_note LIKE '%7排1%' AND order_status = 0\G
Optimizer Trace
自认为比较牛逼的同学可能有这样的疑问：我就觉得使用其他的执行方案比 EXPLAIN 输出的这种方案强，凭什么优化器做的决定和我想得不一样呢？为什么MySQL一定要全表扫描，不用索引呢？所以：在MySQL5.6以及之后的版本中，MySQL提出了一个 optimizertrace 的功能，这个功能可以让我们方便地查看优化 optimizer_trace 决定。
show variavles like 'optimizer_trace';
可以看到enable值为off,表明这个功能默认是关闭的。
如果想打开这个功能，必须首先把enabled的值改为on,就像这样：（注意这个开关是session级别的）：
set optimizer_trace = 'enabled=on';
one_line的值是控制输出格式的，如果为on那么所有输出格式都将在一行中展示，我们就保持其默认值off。
注意：开启trace会影响MySQL性能，所以只能临时分析sql使用，用完之后立即关闭。
现在我们有一个搜索条件比较多的查询语句，它的执行计划如下：
explain
SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S')
AND expire_time> '2021-03-22 18:28:28' AND insert_time> '2021-03-22
18:35:09' AND order_note LIKE '%7****排1%';
可以看到该查询可能使用的索引有3个，u_idx_day_status,idx_order_no,idx_expire_time，那么为什么优化器最终选择了 idx_order_no 而不选择其他的索引或者直接全表扫描呢？这时候就可以通过 otpimizer trace 功能来查看优化器的具体工作过程：（记得开启 optimizer trace 功能）
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
然后我们就可以输入我们想要查看优化过程的查询语句，当该查询语句执行完成后，就可以到information_schema 数据库下的 OPTIMIZER_TRACE 表中查看完成的优化过程。
优化过程大致分为三个阶段：prepare阶段、optimize阶段、execute阶段。
我们所说的基于成本的优化主要集中在optimize阶段，对于单表查询来说，我们主要关注optimize阶段的"rows_estimation"这个过程，这个过程深入分析了对单表查询的各种执行方案的成本；
对于多表连接查询来说，我们更多需要关注"considered_execution_plans"这个过程，这个过程里会写明各种不同的连接方式所对应的成本。反正优化器最终会选择成本最低的那种方案来作为最终的执行计划，也就是我们使用的 EXPLAIN 语句所展现出的那种方案。
如果对使用 EXPLAIN 语句展示出的对某个查询的执行计划很不理解，就可以尝试使用 optimizer_trace 功能来详细了解每一种执行方案对应的成本。
连接查询的成本
condition fitering 介绍
我们前边说过，MySQL中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询成本由下边两个部分构成。
- 单表查询驱动表的成本
- 多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）
对驱动表进行查询后得到的记录条数称之为驱动表的 扇出 （英文名：fanout）。
很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。当查询优化器想计算整个连接查询所使用的成本时，就需要计算出驱动表的扇出值，有的时候扇出值的计算是很容易的，比如下边这两个查询：
查询一：
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2;
假设使用s1表作为驱动表，很显然对驱动表的单表查询只能使用全表扫描的方式执行，驱动表的扇出值也很明确，那就是驱动表中有多少记录，扇出值就是多少。统计数据中s1表的记录行数是10573，也就是说优化器就直接会把10573当作在s1表的扇出值。
查询二：
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE s1.expire_time > '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09';
仍然假设s1表是驱动表的话，很显然对驱动表的单表查询可以使用 idx_expire_time 索引执行查询。此时范围区间( ‘2021-03-22 18:28:28’, ‘2021-03-22 18:35:09’)中有多少条记录，那么扇出值就是多少。
但是有的时候扇出值的计算就变得很棘手，比方说下边几个查询：
查询三：
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE s1.order_note > 'xyz';
本查询和查询一类似，只不过对于驱动表s1多了一个 order_note > ‘xyz’ 的搜索条件。查询优化器又不会真正的去执行查询，所以它只能猜这10573记录里有多少条记录满足 order_note > ‘xyz’ 条件。
查询四：
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE s1.expire_time > '2021-03-22 18:28:28' AND s1.expire_time <= '2021-03-22 18:35:09' AND s1.order_note > 'xyz';
本查询和查询二类似，只不过对于驱动表s1也多了一个 order_note > ‘xyz’ 的搜索条件。不过因为本查询可以使用 idx_expire_time 索引，所以只需要从符合二级索引范围区间的记录中猜有多少条记录符合 order_note > ‘xyz’ 条件，也就是只需要猜在39条记录中有多少符合 order_note > ‘xyz’ 条件。
通过上面4个案例大家可以看到，MySQL在计算数据的时候很多时候只能靠猜。
MySQL把这个猜的过程称之为 condition filtering 。当然，这个过程可能会使用到索引，也可能使用到统计数据，也可能就是MySQL单纯的瞎猜，整个评估过程非常复杂，所以我们不去细讲。
在MySQL5.7之前的版本中，查询优化器在计算驱动扇出时，如果是使用全表扫描的话，就直接使用表中记录的数量作为扇出值，如果使用索引的话，就直接使用满足条件范围的索引记录条数作为扇出值。
在MySQL 5.7中，MySQL引入了这个 condition filtering 的功能，就是还要猜一猜剩余的那些搜索条件能把驱动表中的记录再过滤多少条，其实本质上就是为了让成本估算更精确。我们所说的纯粹瞎猜其实是很不严谨的，MySQL称之为启发式规则。
两表连接的成本分析
连接查询的成本计算公式是这样的：连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本
对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以想要得到最优的查询方案只需要分别为驱动表和被驱动表选择成本最低的访问方法。
可是对于内连接来说，驱动表和被驱动表的位置是可以互换的，所以需要考虑两个方面的问题：不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序，然后分别为驱动表和被驱动表选择成本最低的访问方法。
很显然，计算内连接查询成本的方式更麻烦一些，下边我们就以内连接为例来看看如何计算出最优的连接查询方案。当然在某些情况下，左（外）连接和右（外）连接查询在某些特殊情况下可以被优化为内连接查询。
SELECT
*
FROM
order_exp AS s1
INNER JOIN order_exp2 AS s2 ON s1.order_no = s2.order_note
WHERE s1.expire_time > '2021-03-22 18:28:28'
AND s1.expire_time <= '2021-03-22 18:35:09'
AND s2.expire_time > '2021-03-22 18:35:09'
AND s2.expire_time <= '2021-03-22 18:35:59';
可以选择的连接顺序有两种
- s1连接s2，也就是s1作为驱动表，s2作为被驱动表。
- s2连接s1，也就是s2作为驱动表，s1作为被驱动表。
查询优化器需要分别考虑这两种情况下的最优查询成本，然后选取哪个成本更低的连接顺序以及该连接顺序下各表的最优访问方法作为最终的查询计划。我们定性地分析，不想分析单表查询那样定量地分析了。
具体都可以使用分析语句来执行
explain format=json
SELECT * FROM order_exp AS s1
INNER JOIN order_exp2 AS s2 ON s1.order_no = s2.order_note
WHERE s1.expire_time > '2021-03-22 18:28:28'
AND s1.expire_time <= '2021-03-22 18:35:09'
AND s2.expire_time > '2021-03-22 18:35:09'
AND s2.expire_time <= '2021-03-22 18:35:59';
多表连接的成本分析
首先要考虑下多表连接时可能产生出多少种连接顺序：
- 对于两表连接，比如表A和表B连接只有AB、BA这两种连接顺序。其实相当于2x1 = 2种连接顺序。
- 对于三表连接，比如表A、表B、表C进行连接有ABC、ACB、BAC、BCA、CAB、CBA这么6种连接顺序。其实相当于 3 x 2 x 1 = 6 种连接顺序。
对于四表连接的话，则会有4 × 3 × 2 × 1 = 24种连接顺序。对于n表连接的话，则有 n × (n-1) × (n-2) × ··· × 1种连接顺序，就是n的阶乘种连接顺序，也就是n!。
有n个表进行连接，MySQL查询优化器要每一种连接顺序的成本都计算一遍么？那可是 n! 种连接顺序呀。其实真的是要都算一遍，不过MySQL用了很多办法减少计算非常多种连接顺序的成本的方法。
- 提前结束某种顺序的成本评估
MySQL在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就压根儿不对该连接顺序继续往下分析了。比方说A、B、C三个表进行连接，已经得到连接顺序ABC是当前的最小连接成本，比方说10.0，在计算连接顺序BCA时，发现B和C的连接成本就已经大于10.0时，就不再继续往后分析BCA这个连接顺序的成本了。 - 系统变量optimizer_search_depth
为了防止无穷无尽的分析各种连接顺序的成本，MySQL提出了 optimizer_search_depth 系统变量，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与 optimizer_search_depth 值相同数量的表进行穷举分析。很显然，该值越大，成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长，否则得到不是很好的执行计划，但可以省掉很多分析连接成本的时间。
show variables like 'optimizer_search_depth';
select @@optimizer_search_depth;
- 根据某些规则压根儿就不考虑某些连接顺序
即使是有上边两条规则的限制，但是分析多表连接顺序成本花费的时间还是会很长，所以MySQL干脆提出了一些所谓的启发式规则（就是根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序压根儿就不分析，这样可以极大地减少需要分析的连接顺序的数量，但是也可能造成措施最优的执行计划。它们提供了一个系统变量 optimizer_prune_level 来控制到底是不是用这些启发式规则。
调节成本常数
我们前边已经介绍了两个成本常数
- 读取一个页面花费的成本默认是1.0
- 检测一条记录是否符合搜索条件的成本默认是0.2
其实除了这两个成本常数，MySQL还支持很多，它们被存储到了MySQL数据库的两个表种
show tables from mysql like '%cost%';
因为一条语句的执行其实是分为两层：server层、存储引擎层
在Server层进行连接管理、查询缓存、语法解析、查询优化等操作，在存储引擎层执行具体的数据存取操作。也就是说一条语句在server层中执行的成本是和它操作的表使用的存储引擎是没关系的。所以关于这些操作对应的成本常数就存储在了 server_cost 表中，而依赖于存储引擎的一些操作对应的成本常数就存储在了 engine_cost 表中。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
