1. Q：对MySQL来说，什么是成本？
2. Q：优化步骤
3. 实战（单表）
   1. 前提：
   2. 根据搜索条件，找出所有有可能使用到的索引
   3. 计算全表扫描的成本
   4. 计算根据不同的索引进行查询的成本
   5. 选择成本最低的执行方案
4. 实战（联表）
5. 上结论
6. 动手
   1. 以s1作为驱动表，s2作为被驱动表的成本计算
   2. 以s2作为驱动表，以s1作为被驱动表的成本计算
   3. 总结

Q：对MySQL来说，什么是成本？

• CPU成本：检测是否符合条件等需要CPU完成的工作的成本
• I/O成本：数据从磁盘到内存的加载成本，数据是按页加载的，所以在计算成本时，需要计算页数。

在MySQL中，加载一个数据页到内存中的成本常数是1.0，检索一条记录是否匹配的成本常数是0.2.这两个常数在计算总成本时会经常用到。

Q：优化步骤

1. 根据搜索条件，找出所有有可能使用到的索引
2. 计算全表扫描的成本
3. 计算根据不同的索引进行查询的成本
4. 选则成本最低的执行方案

实战（单表）

前提：

（1）：表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE TABLE single_table (
	id INT NOT NULL AUTO_INCREMENT,
	key1 VARCHAR ( 100 ),
	key2 INT,
	key3 VARCHAR ( 100 ),
	key_part1 VARCHAR ( 100 ),
	key_part2 VARCHAR ( 100 ),
	key_part3 VARCHAR ( 100 ),
	common_field VARCHAR ( 100 ),
	PRIMARY KEY ( id ),
	KEY idx_key1 ( key1 ),
	UNIQUE KEY idx_key2 ( key2 ),
KEY idx_key3 ( key3 ),
KEY idx_key_part ( key_part1, key_part2, key_part3 )) ENGINE = INNODB CHARSET = utf8;

（2）：SQL：

1
2
3
4
5
6
7
8
9

SELECT
	* 
FROM
	single_table 
WHERE
	key1 IN ( 'a', 'b', 'c' ) 
	AND key2 > 10AND key2 < 1000AND key3 > key2 
	AND key_part1 LIKE '%hello%' 
	AND common_field = '123';

根据搜索条件，找出所有有可能使用到的索引

我们定义凡是索引列能和比较值形成一个范围区间的，都是有可能被使用到的
key1：key1列有索引，也是与常数进行比较的，有可能会被使用到
key2：大于10与小于1000，也是在与常数在进行比较，有可能会被使用到
key3：与key2进行比较，而key2不属于常数，不会被使用到
key_part1：使用到了模糊匹配，不是常数，不会被使用到
common_field：该列本身就没有索引，不会被使用到
综上，有可能被使用到的索引只有idx_key1和idx_key2.

计算全表扫描的成本

全表扫描的成本从两方面计算，CPU成本和I/O成本。
那么如何计算呢？我们可以通过SHOW TABLE STATSU LIKE TABLE_NAME来查看我们目标表的信息，我们关心其中的Data_length和Rows。在本例中，Data_length的大小是2637824，Rows是9856。
I/O成本：通过Data_length计算，Data_length代表的是我们表所占字节数多少，那么根据默认的页大小16K来计算，2637824/16/1024=162页，那么成本就是162*1.0 + 1.1 = 163.1，其中1.1是微调常数。
CPU成本：CPU成本我们通过行数计算，成本是9856 * 0.2 + 1.0 = 1972.2
总成本为：1972.2 + 163.1 = 2135.3

计算根据不同的索引进行查询的成本

使用唯一二级索引idx_key2的成本
第一步：计算二级索引I/O成本，key2的范围区间（10，1000），MySQL的查询优化器认为读取一个范围区间的成本和读取一个数据页的成本是一样的，所以I/O成本是1 * 1.0 = 1.0
第二步：计算二级索引CPU成本（回表数），估算在（10,1000）内记录的数量。这里在进行估算时，取范围最左和最右两条记录，然后判断这两条记录相隔的远不远（是否超过10个数据页），不远的话，就遍历，远的话，就读10个数据页中的数据，然后预估整个区间大概有多少。根据算法得到key2的CPU成本是95 * 0.2 + 0.01 = 19.01，95是记录条数，0.01是微调常数。
第三步：计算回表成本，MySQL认为回一次表的成本和读取一个数据页的成本一样，所以回表成本是95 * 1.0 = 95
第四步：计算回表后检索是否匹配的CPU成本，有多少条数据检索多少次，成本是95 * 0.2 = 19.0
综上，使用idx_key2进行查询的成本是：1 + 19.01 + 95 + 19 = 134.01
使用普通二级索引idx_key1的成本
使用key1查询的条件是，三个单点区间[‘a’, ‘b’, ‘c’]
第一步：计算二级索引I/O成本，三个区间，所以成本是3 * 1.0 = 3.0
第二步：计算二级索引CPU成本（回表数），获取符合三个区间的记录的最左和左右记录，本例中一共有118条，那么成本就是118 * 0.2 + 0.01 = 23.61
第三步：回表操作的I/O成本，118 * 1.0 = 118
第四步：回表操作后的CPU成本，118 * 0.2 23.6
综上，使用idx_key1进行查询的成本是3.0 + 23.61 + 118 + 23.6 = 168.21

选择成本最低的执行方案

因为 134.01 < 168.21 < 2135.3，所以选择idx_key2来进行查询

这里需要补充一个成本计算的方式–基于索引统计数据
先来说明一下这种计算方式提出的背景：我们上文中提到了，如果我们使用的in查询中参数过多的话，会造成很多的单点区间，我们知道，每一个单点区间都要使用index dive去估算区间里的记录条数，当我们单点区间特别多的时候，我们要访问B+树的次数也会很多，有时候但是访问B+树耗费的成本已经大于全表扫描了，所以当我们单点区间特别多的时候，我们就会采用基于索引统计数据的方式来估算记录数，这个阈值我们可以通过“SHOW VARIABLES LIKE '%DIVE%'”查看
统计方式：先总结一下，我们会根据索引的重复比例和总行数来预估。举个例子，我们表的总行数是10000，我们通过SHOW INDEX FORM single_table查看到我们某个索引的Cardinality是1000，Cardinality表示该索引列不重复值的个数，那么该索引中每一个值重复的次数就是10000） / 1000 = 10，之后我们用10乘以单点区间的个数就得出总共要回表的记录个数。
缺点：显而易见的，这种估算方式的误差率很大很大，很有可能把重复低的场景算的不高。

实战（联表）

上结论

联表查询的成本计算公式：
连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本
新概念：

• 扇出数：访问驱动表后得到的记录数。

老概念复习：

• 驱动表：联表查询时只需要单次访问的那张表，在左外连接查询时，左边的表就是驱动表。
• 被驱动表：根据驱动表查出的数据进行检索的目标表，在左外连接查询时，右边的表就是被驱动表。

动手

为了更好的描述我们联表成本的计算过程，我们复制一张和single_table一模一样的表single_table2。
进行查询的sql是：

1
2
3
4
5
6
7
8
9

SELECT
	* 
FROM
	single_table AS s1
	INNER JOIN single_table2 AS s2 ON s1.key1 = s2.common_field 
WHERE
	s1.key2 > 10 
	AND s1.key2 < 1000 AND s2.key2 > 1000 
	AND s2.key2 < 2000;

以s1作为驱动表，s2作为被驱动表的成本计算

我们访问s1时使用的条件是 10 < key2 < 1000，是一次range访问。扇出数据后，访问s2使用的条件是 s2.common_field = 常数 和 1000 < s2.key2 < 2000，但是common_field无索引，可以采用的方式就是使用idx_key2和全表扫描。
从之前的计算中，我们知道访问single_table时，使用idx_key2的成本是小于全表扫描的
总成本 = 使用idx_key2访问s1的成本 + s1的扇出数 * 使用idx_key2访问s2的成本

以s2作为驱动表，以s1作为被驱动表的成本计算

访问s2还是使用idx_key2，但在访问s1的时候，我们用到的条件是 s1.key1 = 常数 和 10 < s1.ke2 < 1000，访问方式是ref，要比range效率更高
总成本 = 使用idx_key2访问s2的成本 + s2的扇出数 * 使用idx_key1访问s1的成本
优化器最后会计算这两种查询方式的具体成本，选择成本低的执行。

总结

• 从上述例子可以看出来，如果联表的条件没有索引的话，在被驱动表上查询时的效率会很低，如果被驱动表的连接条件的索引列是唯一索引或者主键的话，效果会更好。
• 我们要尽量减少扇出数，这样可以优化性能。