查询成本
查询语句执行之前都需要计算一下执行成本,并选择成本较低的执行方案。
执行的成本来自两方面:
- server:连接管理,查询缓存,语法解析,查询优化
- 存储引擎:数据存取
本质由I/O成本(存储引擎将数据和索引存储到磁盘,查询时再加载到内存后做操作)和CPU组成(读取并对记录做相应的排序匹配等操作的损耗)
而成本计算和成本常数有关,如读取一个页面花费成本默认为1.0,检测一条记录是否符合搜索条件的成本默认为0.2,通过查询mysql表可得知相应的成本常数表 <SHOW TABLES FROM mysql LIKE '%cost%'>
单表查询的成本
在语句执行前,MySQL的查询优化器会找出执行该语句所有可能使用的方案,再找出最低成本的方案即执行计划,之后再调用存储引擎提供的接口进行查询。
可使用的方案:
-
根据搜索条件,找出所有可能使用的索引possible keys:
只要索引列和常数使用=、<=>、IN、
NOT IN
、IS NULL
、IS NOT NULL
、>
、<
、>=
、<=
、BETWEEN
、!=
(不等于也可以写成<>
)或者LIKE
操作符连接起来,就可以产生一个所谓的范围区间
(LIKE
匹配字符串前缀也行) -
计算全表扫描的代价
将聚簇索引对应的页加载到内存中,依次检测记录是否和搜索条件符合;
成本计算过程:
- I/O成本 = 聚簇索引页面数 * 加载一个页面的成本常数(一般为1.0) + 1.1(微调值)
- CPU成本 = 表的记录数 * 访问一条记录需要的成本常数(一般为0.2) + 1.0(微调值)
- 总成本 = I/O成本 + CPU成本
计算成本的时候需要直到两个值:聚簇索引页面数和表中记录数;可以通过语句<show table status 表名>查看表的统计信息,如
- rows记录数,InnoDB是估值而MyISAM是精值。
- data_length该表存储空间字节数,MyISAM是数据文件的大小,InnoDB是聚簇索引占用的存储空间的大小,因页大小默认为16k,所以聚簇索引页面数=data_length / 16 /1024
-
计算使用不同索引执行查询的代价
MySQL查询优化器先分析使用唯一二级索引的成本,再分析使用普通索引的成本
引使用二级索引+回表查询,那么计算成本就需要知道包含多少条二级索引记录
- 范围区间数量:由范围区间最左和最右记录对应的目录项所在页之间有多少页面A
- 需要回表的记录数:范围区间数量得到的二级索引记录条数即为回表操作次数B
计算过程
- I/O成本 1= 范围区间数 * 1.0
- I/O成本 2 = 回表时 二级索引记录 * 1.0
- CPU成本 1 = 读取二级索引数量 * 读取一条记录成本常数(一般为0.2) + 微调值(一般为0.01)
- CPU成本 2 = 读取并需检测搜索条件回表后的聚簇索引数量 * 读取一条记录成本常数(一般为0.2)
TODO 是否有可能使用索引合并 index merge
in区间产生的很多单点区间,也就是很多范围区间树,这样会加大I/O成本。
MySQL提供直接访问索引对应的B+树来计算某个范围区间内对应的索引记录条数称为index dive。也就是说如果in里面有200个参数就需要index dive200次,为了应对这种情况MySQL同时提供一个系统变量eq_range_index_dive_limit,意思是如果
-
单点数大于这个变量值,采用index dive方式。
-
单点数大于这个变量值,要使用所谓的索引统计数据来估算,而估算值可通过计算平均单个值的重复次数:rows / cardinality
- <show index from 表名>来查询cardinality属性:即索引列中不重复值的个数,如果为1意味该列值全部重复,在InnoDB中是个估值。
- <show table staus 表名>来查询row属性:表中记录数
那么计算需要回表的记录数为:单个值的平均重复数 * in中参数个数
连接查询的成本
以连表查询采用的是嵌套循环连接算法为例,驱动表访问一次,被驱动表访问多次。那么查询成本就包括了:
- 单次查询驱动表的成本
- 多次查询被驱动表的成本(与驱动表查询出的记录条数或称扇出有关)= 驱动表扇出数 * 单次访问被驱动表的成本
对于左右连接,只需分别计算驱动表和被驱动表成本最低的方案。
但对于内连接,驱动表和被驱动表位置可互换,所以要先考虑最优的连接顺序再分表考虑两表的最低成本方案。
所以为尽可能减少成本,尽量在被驱动表的连接列上建索引,这样就可以用ref访问来降低访问被驱动表的成本
多表连接查询略。