14-Mysql内核查询成本计算实战-06

调节成本常数

我们前边已经介绍了两个成本常数：
读取一个页面花费的成本默认是1.0
检测一条记录是否符合搜索条件的成本默认是0.2
其实除了这两个成本常数，MySQL还支持很多，它们被存储到了MySQL数据库的两个表中：

SHOW TABLES FROM mysql LIKE '%cost%';

因为一条语句的执行其实是分为两层的：server层、存储引擎层。
在server层进行连接管理、查询缓存、语法解析、查询优化等操作，在存储引擎层执行具体的数据存取操作。也就是说一条语句在server层中执行的成本是和它操作的表使用的存储引擎是没关系的，所以关于这些操作对应的成本常数就存储在了server_cost表中，而依赖于存储引擎的一些操作对应的成本常数就存储在了engine_cost表中。

mysql.server_cost表

server_cost表中在server层进行的一些操作对应的成本常数，具体内容如下：

SELECT * FROM mysql.server_cost;

我们先看一下server_cost各个列都分别是什么意思：
cost_name 表示成本常数的名称。
cost_value 表示成本常数对应的值。如果该列的值为NULL的话，意味着对应的成本常数会采用默认值。
last_update 表示最后更新记录的时间。
comment 注释。

从server_cost中的内容可以看出来，目前在server层的一些操作对应的成本常数有以下几种：
disk_temptable_create_cost 默认值40.0 创建基于磁盘的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
disk_temptable_row_cost 默认值1.0 向基于磁盘的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
key_compare_cost 0.1 两条记录做比较操作的成本，多用在排序操作上，如果增大这个值的话会提升filesort的成本，让优化器可能更倾向于使用索引完成排序而不是filesort。
memory_temptable_create_cost 默认值2.0 创建基于内存的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。
memory_temptable_row_cost 默认值0.2 向基于内存的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。
row_evaluate_cost 默认值0.2 这个就是我们之前一直使用的检测一条记录是否符合搜索条件的成本，增大这个值可能让优化器更倾向于使用索引而不是直接全表扫描。

MySQL在执行诸如DISTINCT查询、分组查询、Union查询以及某些特殊条件下的排序查询都可能在内部先创建一个临时表，使用这个临时表来辅助完成查询（比如对于DISTINCT查询可以建一个带有UNIQUE索引的临时表，直接把需要去重的记录插入到这个临时表中，插入完成之后的记录就是结果集了）。在数据量大的情况下可能创建基于磁盘的临时表，也就是为该临时表使用MyISAM、InnoDB等存储引擎，在数据量不大时可能创建基于内存的临时表，也就是使用Memory存储引擎。大家可以看到，创建临时表和对这个临时表进行写入和读取的操作代价还是很高的就行了。
这些成本常数在server_cost中的初始值都是NULL，意味着优化器会使用它们的默认值来计算某个操作的成本，如果我们想修改某个成本常数的值的话，需要做两个步骤：对我们感兴趣的成本常数做update更新操作，然后使用下边语句即可：
FLUSH OPTIMIZER_COSTS;
当然，在你修改完某个成本常数后想把它们再改回默认值的话，可以直接把cost_value的值设置为NULL，再使用FLUSH OPTIMIZER_COSTS语句让系统重新加载。

mysql.engine_cost表

engine_cost表表中在存储引擎层进行的一些操作对应的成本常数，具体内容如下：

SELECT * FROM mysql.engine_cost;

与server_cost相比，engine_cost多了两个列：
engine_name列
指成本常数适用的存储引擎名称。如果该值为default，意味着对应的成本常数适用于所有的存储引擎。
device_type列
指存储引擎使用的设备类型，这主要是为了区分常规的机械硬盘和固态硬盘，不过在MySQL 5.7.X这个版本中并没有对机械硬盘的成本和固态硬盘的成本作区分，所以该值默认是0。
我们从engine_cost表中的内容可以看出来，目前支持的存储引擎成本常数只有两个：
io_block_read_cost 默认值1.0 从磁盘上读取一个块对应的成本。请注意我使用的是块，而不是页这个词。对于InnoDB存储引擎来说，一个页就是一个块，不过对于MyISAM存储引擎来说，默认是以4096字节作为一个块的。增大这个值会加重I/O成本，可能让优化器更倾向于选择使用索引执行查询而不是执行全表扫描。
memory_block_read_cost 默认值1.0 与上一个参数类似，只不过衡量的是从内存中读取一个块对应的成本。
怎么从内存中和从磁盘上读取一个块的默认成本是一样的？这主要是因为在MySQL目前的实现中，并不能准确预测某个查询需要访问的块中有哪些块已经加载到内存中，有哪些块还停留在磁盘上，所以MySQL简单的认为不管这个块有没有加载到内存中，使用的成本都是1.0。与更新server_cost表中的记录一样，我们也可以通过更新engine_cost表中的记录来更改关于存储引擎的成本常数，做法一样。