13.查询成本的计算(gold_axe)
执行计划:成本最低的方案
possible key: 查询中可能用到的索引
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;
单表查询
查询例句:
SELECT * FROM single_table WHERE
key1 IN ('a', 'b', 'c') // idx_key1
AND
key2 > 10 AND key2 < 1000// uk_key2
AND
key3 > key2 // 不可能用到索引
AND
key_part1 LIKE '%hello%' //不可能用到索引
AND
common_field = '123';//不可能用到索引
possible key:idx_key1,uk_key2
查询成本 有2种,
I/O成本:加载到内存的成功
CPU成本:读取,检测记录是否满足,对结果排序
mysql为每个表维护了统计信息, 有 Rows(大概几行) Data_length(基础索引多大,单位B)
一页16k,因此能算出聚簇索引有几页
这里假设97页,9693行
现在计算比较上一个查询成本:
-
全表扫描成本 估算
I/O成本: 97(总页数)*1(I/O成本参数默认,可调)+1.1(微调值不用管)
CPU成本: 9693(行)* 0.2(成本常数)+1.0(微调值)
虽然,聚簇索引只有最下层的叶子节点是真的放着数据, 只要沿着最下层的双向链表 从左往右就能遍历了, 实际是不用遍历内节点的, 这里的估算比较粗暴, 但是就是这么估的
-
用唯一索引 uk_key2 的成本估算
key2 > 10 AND key2 < 1000
1.把这个区间的二级索引全部读入磁盘, 的I/O成本 是1
就是查(10,1000)区间, 估算时 查询优化器认为, 一个区间的I/O成本 就是读取一页的成本
2.从中拿到主键id, 查看一条成本0.2
首先要沽出(10,1000)区间大概有几条记录(肯定少于1000-10),如果最左和最右相隔不远,就直接得出精确值,不然的话 平均每个页几条(统计左边10页) * 左右间有几页(往上找共同父节点)
注意: 这样直接去访问树, 来估算条数,叫
index dive
是真的去访问了, 这样这个得到估算值的本身的动作,就会产生成本
为了不让这个为了估算产生的成本太大, 如果 in(参数) 参数多于200就不用index dive, 而是直接:
Rows/Cardinality来估算条数
Rows: 统计的大概行数
Cardinality:基数,表示重复程度的,1是完全相同,和行数相等是每行都不相等
这种估算代价小,但是不怎么精确
这里假设是95
95 * 0.2 +0.01=19.01
3.凭着符合key2 > 10 AND key2 < 1000 的id 去聚簇索引拿到完整一条的记录(忽略不计), 主要是 查看对其他查询条件的满足(key1 IN ('a', 'b', 'c'), key3 > key2,key_part1 LIKE '%hello%' ,common_field = '123' 这些)
95*0.2
-
普通二级索引 idx_key1 的成本估算
key1 IN ('a', 'b', 'c')
- 把3个单点区间读入磁盘, 还是和上文说的一样,一个区间当一页算
3*1 - 同上, 查看1读入的数据拿到id, 需要先估满足条件的有几条(不是三条哦.key1不限制值不能相同), 和上文一样估, 这里即使一共118条件
118*2+0.01
3.同上, 用id回表(忽略不计), 查看完整的记录,应用其他筛选条件
118*0.2=23.6
这里明显不能 Index Merge (idx_key1,uk_key2 并行查询然后合并 ), 因为二级索引是等值查询 是必要条件 (因为取交集必须是id是有序的取交集才快)
连接查询的成本
成本= 访问驱动表+ 驱动表扇出数* 单词访问被驱动表成本
扇出数的计算 叫 Condition filtering
主要靠猜, 没有筛选条件, 直接用驱动表的统计的大概Rows, key2 > 10 AND key2 < 1000 和上文说的一样 index dive
外连接的最优查询方案: 分别选择最优访问方法
外连接的最优查询方案:还要选择驱动顺序
