MySQL优化：基于成本计算的优化

12 谁最便宜就选谁 ----MySQL基于成本的优化

查询成本由两方面组成：

1. IO成本
   MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的,从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。
2. CPU成本
   读取以及检测记录是否满⾜对应的搜索条件、对结果集进⾏排序等这些操作损耗的时间称之为CPU成本。

设计MySQL的⼤叔规定读取⼀个⻚⾯花费的成本默认是1.0，读取以及检测⼀条记录是否符 合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为成本常数。

单表查询的成本

基于成本的优化步骤

1. 根据搜索条件，找出所有可能使⽤的索引
2. 计算全表扫描的代价
3. 计算使⽤不同索引执⾏查询的代价
4. 对⽐各种执⾏⽅案的代价，找出成本最低的那⼀个

1.根据搜索条件，找出所有可能使用的索引：
通过对where条件和order的分析，可以查询到所有使的索引
2.计算全表扫描的代价：
由于查询成本=I/O成本+CPU成本，所以计算全表扫描的代价需要两个信息：

• 聚簇索引占⽤的⻚⾯数
• 该表中的记录数
  首先使用命令：SHOW TABLE STATUS LIKE 'single_table'\G 查询table的查询状态

mysql>	SHOW	TABLE	STATUS	LIKE	'single_table'\G
***************************	1.	row	***************************
          	Name:	single_table
        	Engine:	InnoDB
       	Version:	10
    	Row_format:	Dynamic
          	Rows:	9693
Avg_row_length:	163
   	Data_length:	1589248
Max_data_length:	0
  	Index_length:	2752512
     	Data_free:	4194304
Auto_increment:	10001
   	Create_time:	2018-12-10	13:37:23
   	Update_time:	2018-12-10	13:38:03
    	Check_time:	NULL
     	Collation:	utf8_general_ci
      	Checksum:	NULL
Create_options:
       	Comment:
1	row	inset	(0.01	sec)

其中有两个参数我们需要关注：

1. rows : 在innodb引擎中这是一个估算值；估算有多少条记录
2. data_length : innodb表示聚簇索引占用存储空间的大小；
   Data_length = 聚簇索引的⻚⾯数量 x 每个⻚⾯的⼤⼩

IO成本 = Data_length/每个页面大小（默认16kb）+1.1（微调值）
CPU成本 = rows * 0.2 + 1.0（微调值）

全表扫描的代价 = Data_length/每个页面大小（16kb）+1.1 + rows*0.2 + 1.0；

3. 计算使用不同索引查询的代价
使用二级索引+回表查询的方式主要依赖两个参数：

1. 索引范围区间数量：无论某个范围区间内二级索引占用的页面有多大，查询优化器粗略的认为读取索引的一个区间范围和读取一个页面的成本是相同的；即IO成本=1；如果是用in查询；每个值为一个区间；如果有n个值，那么查询的IO成本 = n * 1；
2. 需要回表的记录数：如果最左区间和最右区间的距离不是很大（没有超过10个页面）那么查询优化器可以精确的统计出索引之间的记录数；如果超过10页面；那么在这10页面取平均值，然后判断整个区间的页面数量；记录数 = 索引页面数量10页面的平均值；CPU成本 = 记录数0.2；
   所以这里的 二级索引的查询成本 = 1 + 记录数0.2；
   根据二级索引返回的记录数需要对聚簇索引进行回表查询；
   查询的IO成本 = 记录数1；
   查询的CPU成本 = 记录数*0.2；

使用二级索引查询成本 = 索引查询成本 + 回表查询成本 = n个区间 * 1 + 记录数 * 0.2 + 记录数 * 1 + 记录数*0.2；

4. in 查询在其中的特例
有时候使⽤索引执⾏查询时会有许多单点区间，⽐如使⽤IN语句就很容易产⽣⾮常多的单点区间；这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的⽅ 式称之为index dive。少数的单点区间是没有任何问题的；但是超过一定范围之后：系统变量eq_range_index_dive_limit

mysql>	SHOW	VARIABLES	LIKE	'%dive%';
+---------------------------+-------+
|	Variable_name            	|	Value	|
+---------------------------+-------+
|	eq_range_index_dive_limit	|	200  	|
+---------------------------+-------+
1	row	inset	(0.08	sec)

查询优化器就不会使用 index dive的方式了；接下来，我们看一下他采用的计算方式；
首先使用命令 SHOW INDEX FROM single_table 获取索引中的参数Cardinality（区分度；distinct的值）;该值是估算出来的；同时，结合上面的 SHOW TABLE STATUS 中的rows，我们就可以估算出该索引重复的数据 = rows / Cardinality；结合in中的数据量 n 我们就可以估算出 查询的记录数 = n * 索引重复数；

联表查询的成本

两表连接的成本分析（多表类似）

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数（记录数） x 单次访问被驱动表的成本

调节成本常数

我们前边之介绍了两个成本常数：

• 读取⼀个⻚⾯花费的成本默认是1.0
• 检测⼀条记录是否符合搜索条件的成本默认是0.2
• 其实除了这两个成本常数，MySQL还⽀持好多呢，它们被存储到了mysql数据库（这是⼀个系统数据库，我们之前介绍过）的两个表中：

mysql>	SHOW	TABLES	FROM	mysql	LIKE	'%cost%';
+--------------------------+
|	Tables_in_mysql	(%cost%)	|
+--------------------------+
|	engine_cost             	|
|	server_cost             	|
+--------------------------+
2	rows	inset	(0.00	sec)

⼀条语句的执⾏其实是分为两层的：

• server层 (在server层进⾏连接管理、查询缓存、语法解析、查询优化等操作;关于这些操作对应的成本常数就存储在了server_cost表中)
• 存储引擎层(依赖于存储引擎的⼀些操作对应的成本常数就存储在了engine_cost表中。)

从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	这个就是我们之前⼀直使⽤的检测⼀条记录是 否符合搜索条件的成本，增⼤这个值可能让优 化器更倾向于使⽤索引⽽不是直接全表扫描。