Mysql执行成本-Part2(连接查询的成本、调节成本常数)

方便後续解释概念，我们在创建一个跟原先single_table一样的表，叫single_table2(s2)。

mysql> create table single_table2(                                                           -> id int not null auto_increment,
    -> key1 varchar(10),
    -> key2 int,
    -> key3 varchar(100),
    -> key_part1 varchar(100),
    -> key_part2 varchar(100),
    -> key_part3 varchar(100),
    -> common_field varchar(100),
    -> primary key (id),
    -> key idx_key1 (key1),
    -> unique key uk_key2 (key2),
    -> key idx_key3 (key3),
    -> key idx_key_part(key_part1, key_part2, key_part3)
    -> ) engine=InnoDB charset=utf8;
Query OK, 0 rows affected, 1 warning (0.44 sec)

连接查询的成本

条件过滤

首先要知道什麽是条件过滤？
我们已经知道MySQL中连接查询是巢状结构回圈，驱动表会被存取一次，被驱动表会被存取多次。
因此成本就是

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

我们把查询驱动表後的纪录笔数称为驱动表的扇出(fanout)。
有时候扇出值很好计算

select * from s1 inner join s2;

很明显这是全表资料扫描，驱动表有多少纪录，扇出值就是多少，以我们前文的数字就是10146

有时候没那麽容易知道

select * from s1 inner join s2
    where s1.commom_field > 'xyz';

多了一个条件s1.commom_field > 'xyz'，但由於最佳化器并不会真的去执行查询，所以我们只能猜测有多少笔纪录符合这条件，而这个猜测的过程也就是条件过滤。

两表连接的成本分析

连接查询总成本=单次存取驱动的成本+驱动表扇出值x单次存取被驱动表的成本。

对於左外连接和右外连接，驱动表与被驱动表是固定的，所以会单纯直接计算成本最低的方式即可。

但对於内连接来说，驱动表与被驱动表的位置是可以互换的，因此需要考虑当不同的表做驱动表时，成本最低的连接顺序是那一个。

再来实际运行的时候，比重较大的是驱动表扇出值x单次存取被驱动表的成本，所以我们要尽可能的降低这两个值，
像是在被驱动表中建立索引，如果可以，被驱动表的连接列最好是该表的主键或是唯一二级索引，这样就更快了。

多表连接的成本分析

对於n表连接会有nx(n-1)x(n-2)x...x1种的顺序，也就是n阶的连接顺序。
而这麽多种变化一一计算成本想来就是个费时的工程，因此有一些优化的方式。

• 提前结束某种连接顺序的成本评估：MySQL会维护一个全域变数存放当前最小的连接查询成本，当成本计算的过程中超过这个数值，即不会在继续计算成本。
• 系统变数optimizer_search_depth：为了防止无穷无尽的分析各种连接顺序的成本，工程师设计了一个这样的变数，只有连接表的数量小於等於该值才会继续分析。
• 某些规格直接不考虑某些连接顺序：工程师直接根据过去的经验订定了一些启发式规则，凡是不满足规则的直接不分析，提供了一个系统变数optimizer_prune_level来控制是否使用这些规则。

调节成本常数

前文介绍了两个成本常数：

1. 读取一个页面花费的成本预设是1
2. 读取及检查一笔纪录是否符合条件的成本预设是0.2
   其实除了这两个成本常数以外，还支持很多其他常数，存在mysql库的两个表中：

mysql> show tables from mysql like '%cost%';
+--------------------------+
| Tables_in_mysql (%cost%) |
+--------------------------+
| engine_cost              |
| server_cost              |
+--------------------------+
2 rows in set (0.46 sec)

前文提到再执行一笔叙述时，分为两个部分server层跟储存引擎层，server层负责连接管理、查询快取、语法解析、查询最佳化等，而储存引擎层才是具体执行操作的地方，server层的成本常数都存在server_cost，而储存引擎层则存在engine_cost。

接下来我们分别来看看这两个表有那些成本常数
servercost

mysql> select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name                    | cost_value | last_update         | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost   |       NULL | 2018-07-17 11:08:22 | NULL    |            20 |
| disk_temptable_row_cost      |       NULL | 2018-07-17 11:08:22 | NULL    |           0.5 |
| key_compare_cost             |       NULL | 2018-07-17 11:08:22 | NULL    |          0.05 |
| memory_temptable_create_cost |       NULL | 2018-07-17 11:08:22 | NULL    |             1 |
| memory_temptable_row_cost    |       NULL | 2018-07-17 11:08:22 | NULL    |           0.1 |
| row_evaluate_cost            |       NULL | 2018-07-17 11:08:22 | NULL    |           0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.26 sec)

disk_temptable_create_cost：基於磁碟的临时表成本
disk_temptable_row_cost：向基於磁碟的临时表存取一笔纪录的成本
key_compare_cost：两笔纪录比较操作的成本，多用在排序操作上
memory_temptable_create_cost：基於记忆体的临时表成本
memory_temptable_row_cost：向基於记忆体的临时表存取一笔纪录的成本
row_evaluate_cost：读取并检测一笔纪录是否符合搜索条件的成本

在执行语法像是distinct子句、group by子句、union子句，mysql都有可能在内部创建一个临时表来辅助完成查询。资料量大的时候可能创建基於磁碟的临时表（使用myisam，innodb等引擎），而资料不多时可能创建基於记忆体的临时表（使用memory引擎）。

这些常数预设值都是null，想要更新数值的话如下：

mysql> update mysql.server_cost set cost_value=0.4 where cost_name='row_evaluate_cost';
Query OK, 1 row affected (0.54 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush optimizer_costs;
Query OK, 0 rows affected (0.57 sec)

mysql> select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name                    | cost_value | last_update         | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost   |       NULL | 2018-07-17 11:08:22 | NULL    |            20 |
| disk_temptable_row_cost      |       NULL | 2018-07-17 11:08:22 | NULL    |           0.5 |
| key_compare_cost             |       NULL | 2018-07-17 11:08:22 | NULL    |          0.05 |
| memory_temptable_create_cost |       NULL | 2018-07-17 11:08:22 | NULL    |             1 |
| memory_temptable_row_cost    |       NULL | 2018-07-17 11:08:22 | NULL    |           0.1 |
| row_evaluate_cost            |        0.4 | 2021-09-27 08:42:34 | NULL    |           0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.00 sec)

enginecost

mysql> select * from mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost     |       NULL | 2018-07-17 11:08:22 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |       NULL | 2018-07-17 11:08:22 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.03 sec)

这边特别说明一个列engine_name，表示成本常数适用的储存引擎名称，default表示都通用。

io_block_read_cost：从磁碟上读取一个区块对应的成本(对innodb来说，一页就是一个区块，不过对myisam来说预设为4096位元组作为一个区块)
memory_block_read_cost：跟前面类似，只是变成从记忆体读取。

一样可以透过跟前述一样的方式更新成本常数，也可以透过新增的方式来插入只符合特定引擎的成本常数。

耶～take a break