MySQL 优化器成本模型

发表于 更新于

本文探讨一下 MySQL5.7 版本中 优化器的代价模型。相关内容也可查看官网

MySQL 优化器成本模型由两个表组成，分别位于 mysql.server_cost 和 mysql.engine_cost 中。

  • server_cost: 针对服务器操作的优化器成本估算
  • engine_cose: 针对特定存储引擎的特定操作的优化器成本估算

server_cost

server_cost 包含以下值：

  • disk_temptable_create_cost：缺省值 40.0，该字段标识基于磁盘的存储引擎（InnoDB 和 MyISAM）中创建临时表的成本估算。从缺省值也可以看出，默认情况下创建临时表的代价是非常昂贵的。
  • disk_temptable_row_cost：缺省值 1.0，该字段标识基于磁盘的存储引擎（InnoDB 和 MyISAM）中创建临时行的成本估算。
  • key_compare_cost：缺省值 0.1，该字段表示比较记录的成本。
  • memory_temptable_create_cost：缺省值 2.0，MEMORY 存储引擎创建临时表的成本，从与 disk_temptable_create_cost 的对比中可以看出，默认情况下 MEMORY 存储引擎创建临时表的成本是远小于 InnoDB 和 MyISAM 存储引擎的。
  • memory_temptable_row_cost：缺省值 0.2，MEMORY 存储引擎创建临时行的成本。
  • row_evaluate_cost：缺省值 0.2，行评估成本，即扫描了多少行的成本。

engine_cost

  • io_block_read_cost：缺省值 1.0，从磁盘读取索引或数据块的成本。
  • memory_block_read_cost：缺省值 1.0，从内存数据库缓冲区读取索引说数据块的成本。

修改成本模型

1
2
3
4
5
6
7
8
9
10
11
-- 修改所有的 io_block_read_cost 成本
UPDATE mysql.engine_cost
  SET cost_value = 2.0
  WHERE cost_name = 'io_block_read_cost';
FLUSH OPTIMIZER_COSTS;

-- 仅修改 InnoDB 存储引擎的成本
INSERT INTO mysql.engine_cost
  VALUES ('InnoDB', 0, 'io_block_read_cost', 3.0,
  CURRENT_TIMESTAMP, 'Using a slower disk for InnoDB');
FLUSH OPTIMIZER_COSTS;

实战

打开优化器跟踪

1
2
3
4
-- 查看优化器跟踪的开关
SHOW VARIABLES LIKE '%optimizer_trace%';
-- 打开优化器跟踪
SET GLOBAL optimizer_trace = 'enabled=on,one_line=on';

未完待续 …