MySQL 优化器计算 常数

MySQL version:8.0.18-commercial MySQL Enterprise Server

现有CBO的消耗的计算是根据一些常用数字来计算出结果的，如以下两张表engine_cost 和 server_cost

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 | 2020-01-03 10:20:15 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |       NULL | 2020-01-03 10:20:15 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> select * from mysql.server_cost ;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name                    | cost_value | last_update         | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost   |       NULL | 2020-01-03 10:20:15 | NULL    |            20 |
| disk_temptable_row_cost      |       NULL | 2020-01-03 10:20:15 | NULL    |           0.5 |
| key_compare_cost             |       NULL | 2020-01-03 10:20:15 | NULL    |          0.05 |
| memory_temptable_create_cost |       NULL | 2020-01-03 10:20:15 | NULL    |             1 |
| memory_temptable_row_cost    |       NULL | 2020-01-03 10:20:15 | NULL    |           0.1 |
| row_evaluate_cost            |       NULL | 2020-01-03 10:20:15 | NULL    |           0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.00 sec)

其中修改方式 和 增加计算数字的方式

UPDATE mysql.engine_cost
  SET cost_value = 2.0
  WHERE cost_name = 'io_block_read_cost';
FLUSH OPTIMIZER_COSTS;

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;

执行 FLUSH 就是为了告诉服务器，重新读取消耗计算的表。否则不生效