MySQL是怎样运行的——第十二章

这一章主要是讲基于成本的查询优化。核心方法有两种，一种是通过index dive这种方法去算io代价，另一种是根据记录数和势来估算。

12.1

成本分为两大类，即IO成本和CPU成本。
为了计算这两种成本，我们需要基本单位。基本单位主要有两个，一是从磁盘上读取一个页的时间开销，默认为1.0。二是读取以及检测一条记录是否符合搜索条件的开销，默认为0.2。

12.2

这次用到的还是以前那张表

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 uk_key2 (key2),
	KEY idx_key3 (key3),
	KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

这张表里有10000条记录，除id以外都是随机插入的值。

基于成本的优化步骤

在生成最终执行的物理执行计划前，优化器会找出所有可以用来执行该语句的方案，并估算这些方案的成本，从中选择一个最优的计划（方案）。

整体分四个步骤：

1. 根据条件找出所有能用的索引
2. 计算全表扫描的代价
3. 计算使用不同索引执行查询的代价
4. 选择代价最低的方案

下面举一个sql的例子：

select * from single_table where
	key1 in ('a', 'b', 'c') and
	key2 > 10 and key2 < 1000 and
	key3 > key2 and
	key_part1 like '%hello%' and
	common_field = '123'

step1 找出能用的索引

对于 B+树索引来说， 只要索引列和常数使用=、<=>、 IN、 NOT IN、 IS NULL、
IS NOT NULL、>、<、>=、<=、 BETWEEN、!= (不等于也可以写成<>) 或者 LIKE 操作符连接起来， 就会产生一个扫描区间 （用LIKE匹配字符附缀时，也会产生一个扫描区间）。
也就是说，这些搜索条件都可能使用到索引，设计 MySQL 的大叔把一个查询中可能使用到的索引称之为 possible keys.

上面这个sql根据分析可知，能用的索引有idx_key1和uk_key2。

step2 计算全表扫描的代价

计算这部分只要知道表的信息就可以了。表的信息通过show table status这类sql来查看。

show table status like 'single_table';

我们只关心两个选项。
一个是Rows，表示有多少条记录。对于用MyISAM引擎