连接查询成本(2)---mysql进阶（四十二)

前面说了mysql每次索引优化是有成本的，先看全表扫描成本，在看索引扫描成本，后选出小成本，每次访问页的成本量大概是1，读取和效验数据是否符合条件，成本大概是0.2，读取和效验数据和文件排序等称为CPU成本，查询页等称为I/O成本。可以用show index from tb，来看表都有什么数据，其中cardinality表示重复率，比较重要

连接查询成本

为了连接表，我们在创建一个和single_table一样的表，single_table2，简称s2。

Condition filtering介绍

我们前面说过连接查询主要要查驱动表和被驱动表，被驱动表的查询次数，取决于驱动表查询出来的数据行数，所以成本为：

多次查询被驱动表成本

我们把驱动表查询出来的数据称为驱动表的扇出(fanout)，显然扇出越小，被驱动查询次数越少，成本越低，所以成本可以看这个扇出值：

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2;

显然这是驱动表全表查询，我们前面统计了s1的数据9693行，所以扇出值为9693。

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s

WHERE s1.key2 >10 AND s1.key2 < 1000;

此处很显然用key2二级索引查询，我们前面计算了此区间的值由95条数据，所以扇出值是95。

但下面的sql就比较麻烦：

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2

WHERE s1.common_field > 'xyz';

这里因为加了个非索引过滤值，查询优化器又不会真的去查询，只会猜9693有多少条数满足。

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';

这里就是在前面的区间95条的基础上，再猜多少符合非索引过滤的条件。

说了这么多，其实就是表达很多情况下，驱动表的扇值需要猜：

使用全部查询，计算扇出满足过滤条件时。

使用索引执行单表查询，那么需要把其他过滤条件计算满足多少。

Mysql吧这种猜的过程就叫做condition filter。

两个表连接的成本分析

连接查询的计算公式，总成本 = 单词访问驱动表成本 + 驱动表扇出 + 单次访问被驱动表成本。

对于左连接和右连接，驱动表是固定的，只要分别为驱动表和被驱动表找到优的解决方法。

可对于内连接，驱动表和被驱动表是可以互换的，所以考虑两个方面，不同表作为驱动表成本不同，取优

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;

很显然，内连接更麻烦一些，上面是一个内连接sql，

以s1为驱动表的话：

先用索引确定驱动表的扇值，就是s1.key2>10 and s1.key2<1000的扇值。然后查询s2的条件就是 s.common_field = 值 和>1000 and <2000的两个条件，这时候可以选择全表查询或者索引查询+回表，我们假设索引成本更低。

这时候就是s1表key2的成本 + s1的扇出 * 使用s2.key2的访问成本

以s2为驱动表的话：

先用所以s2.key2确定扇出，然后根据扇出值，查询s1的条件就是s1.key1 = 值 和 > 10 and <1000，这里个是ref访问，第二个是range访问，一般来说ref会快很多，所以我们这里默认采用个。

这时候成本 = s2表key2的成本 + s2的扇出 * 使用s1.key1的ref访问成本

从上面可以看出，成本主要来自扇出 个 被驱动表的访问成本，所以优化器会选择扇出更小和被驱动表访问成本更低的来查询。

多表连接成本分析

对于两个表连接，连接顺序：2*1 = 2

对于三个表连接，连接顺序：3*2*1 = 6

对于四个表连接，连接顺序：4*3*2*1=24

这种n的阶层连接顺序也就是叫n!

那么每次都全部分析一遍不是很耗费成本吗？mysql有几种解决办法：

提前结束计算：比如ABC三个表连接，维护一个全局低成本变量，当ABC成本已经低时候，bc计算的成本大于低成本就直接取消，不计算bcd了。

系统变量optimizer_search_depth：mysql设置了当前变量，若越大，成本分析越，越有好的执行结果，但耗时，否则得不到好的执行结果，但很节约时间。

启发式规则：mysql有启发式规则和系统变量，凡事不满足这些规则的，直接不分析，这样也导致会错失优执行计划。

调节成本常数

我们前面介绍了两个成本常量，一个是刷新页为1.0，一个是cpu排序或者读取效验数据是0.2，我们可以从数据库看到这两张表。

mysql> SHOW TABLES FROM mysql LIKE '%cost%';
+--------------------------+
| Tables_in_mysql (%cost%) |
+--------------------------+
| engine_cost              |
| server_cost              |
+--------------------------+
2 rows in set (0.00 sec)