12.1 什么是成本
-
I/O 成本
当我们想查询表中的记录时，需要先把数据或者索引从磁盘加载到内存中再操作。
-
CPU 成本
读取以及检测记录是否满足对应的搜索条件、对结构集进行排序等这些操作损耗。
对于InnoDB来说，页是磁盘和内存之间交互的基本单位。MySQL规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。这两个称为成本常数。
12.2 单表查询的成本
12.2.1 准备工作
建表并插入数据，即前面用过的 single_table
12.2.2 基本成本的优化步骤
MySQL 的查询优化器会找出执行 SQL 的所有可能方案，对比之后给出成本最低的方案，就是所谓的执行计划。
- 根据索引条件，找出所有可能使用的索引
- 计算全表扫描的成本
- 计算使用不同索引执行查询的成本
- 对比各种执行方案的成本，使用成本最低的那一个
12.2.3 基于索引统计数据的成本计算
查询有时会出现许多单点区间的情况，比如：
SELECT * FROM single_table WHERE key1 IN ('aa1', 'aa2', 'aa3', ... , 'zzz');
由于idx_key1不是唯一索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多少，只能找到对应的区间。然后再计算区间内有多少数据。这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称为index dive。
SHOW VARIABLES LIKE '%dive%';
当 IN 语句中的参数个数小于200时，将使用 index dive 的方式计算各个单点区间对应的记录条数；否则就需要使用索引统计数据来进行估计决定是否使用 index dive 了。
SHOW INDEX FROM single_table;
| 属性名 | 描述 |
|---|---|
| Table | 索引所属的表名 |
| Non_unique | 是否唯一索引，0：是；1：否 |
| Key_name | 索引名 |
| Seq_in_index | 索引列在索引中的位置，从1开始 |
| Column_name | 索引列名 |
| Collation | 排序方式，A：升序；NULL：降序 |
| Cardinality | 索引列中不重复值的数量（估算值） |
| Sub_part | 对字符串或字节串的对n位建立索引时，Sub_part=n，对完整列建立索引时为NULL |
| Packed | 索引列如何被压缩，NULL表示未压缩 |
| NULL | 是否允许存储NULL值 |
| Index_type | 索引的类型，BTREE |
| Comment | 索引列注释信息 |
| Index_comment | 索引注释信息 |
针对索引列，计算出一个值平均的出现次数：
N = Rows ÷ Cardinality
假设 IN 语句中有200个参数，则可能的回表记录数有：
C = 200 × N
12.3 连接查询的成本
12.3.1 准备工作
new single_table2 = single_table
12.3.2 Condition filtering 介绍
两表连接查询的成本：
- 单次查询驱动表的成本
- 多次查询被驱动表的成本
扇出：驱动表进行查询后得到的记录条数
condition filtering 过程：
- 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少。
- 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
12.3.3 两表连接的成本分析
连接查询的成本公式：
连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 × 单次访问被驱动表的成本
12.3.4 多表连接的成本分析
系统变量 optimizer_search_depth
系统变量 optimizer_prune_level
12.4 调节成本常数
SHOW TABLES FROM mysql LIKE '%cost%';
一条语句的执行分为两层：
- server 层：连接管理、查询缓存、语法解析、查询优化
- 存储引擎层：具体的数据存取
12.4.1 mysql.server_cost 表
SELECT * FROM mysql.server_cost;
| 列名 | 含义 |
|---|---|
| cost_name | 成本常数名称 |
| cost_value | 成本常数对应的值。NULL表示采用默认值 |
| last_update | 最后更新记录的时候 |
| comment | 注释 |
| default_value | 默认值 |
| 成本常数名称 | 默认值 | 描述 |
|---|---|---|
| disk_temptable_create_cost | 20 | 创建基于磁盘的临时表的成本，如果增大这个值会让优化器尽量少地创建基于磁盘的临时表 |
| disk_temptable_row_cost | 0.5 | 向基于磁盘的临时表写入或读取一条记录的成本，如果增大这个值会让优化器尽量少地创建基于磁盘的临时表 |
| key_compare_cost | 0.05 | 两条记录做比较操作的成本 |
| memory_temptable_create_cost | 1 | 创建基于内存的临时表的成本，如果增大这个值会让优化器尽量少地创建基于内存的临时表 |
| memory_temptable_row_cost | 0.1 | 向基于内存的临时表写入或读取一条记录的成本，如果增大这个值会让优化器尽量少地创建基于内存的临时表 |
| row_evaluate_cost | 0.1 | 检测一条记录是否符合搜索条件的成本，增大这个值可能让优化器更倾向于使用索引而不是直接全表扫描 |
TIPS：
MySQL 在执行诸如 DISTINCT、GROUP BY、UNION 及某些特殊条件的排序查询时都可能在内部先创建一个临时表。在数据量大的情况下可能创建基于磁盘的临时表，否则创建基于内存的临时表。
UPDATE mysql.server_cost
SET cost_value = 0.4
WHERE cost_name = 'row_evaluate_cost'
FLUSH OPTIMIZER_COSTS;
12.4.2 mysql.engine_cost 表
SELECT * FROM mysql.engine_cost;
| 列名 | 含义 |
|---|---|
| engine_name | 成本常数适用的存储引擎名称，default 表示该常数适用于所有存储引擎 |
| device_type | 存储引擎使用的设备类型，主要为了区分机械硬盘和固态硬盘 |
| 成本常数名称 | 默认值 | 描述 |
|---|---|---|
| io_block_read_cost | 1 | 从磁盘上读取一个块的成本。对于 InnoDB 而言，块就是页；对于MyISAM 而言，默认以 4096 字节作为一个块。增大这个值会加重 I/O 成本，可能让优化器更倾向于选择使用索引 执行查询而不是执行全表扫描。 |
| memory_block_read_cost | 0.25 | 从内存中读取一个块的成本。 |
INSERT INTO mysql.engine_cost
VALUES ('InnoDB', 0, 'io_block_read_cost', 2.0,
CURRENT_TIMESTAMP, 'increase Innodb I/O cost');
FLUSH OPTIMIZER_COSTS;
12.5 总结
- 一个查询的执行成本包括 I/O 成本和 CPU 成本，对于 InnoDB 来说，读取一个页面的 I/O 成本默认是 1.0，读取以及检测一条记录是否符合搜索条件的成本默认是 0.2。
- 可以通过手动修改 mysql 数据库下 engine_cost 表或者 server_cost 表中的某些成本常数，更精确的控制生成执行计划时的成本计算。
