277_DBA_执行计划成本概述_统计信息收集

查询成本估算

查询成本 = I/0 成本 + CPU 成本   （读取一个页面花费成本是1.0， 检索一条记录是否符合条件成本是0.2）

测试SQL

Select * from s1 where key1 in (“a”,”b”,”c”) and key2 > 10 and key2 < 100 and key3 > key2 and key_part1 like “%bob%” and commen_field= “json”

1 单表成本

 全表扫描的代价

对于s1单表 成本来说  12075008字节 / 16/1024 = 1,965个页

show table status like "s1";   #data_length 12075008   index_length 20021248  rows 99385

IO = 1,965 * 1.0 + 1.1 =1966.43  (1.1 是微调值)   # 1965代表数据页数量（数据字节/16KB = page_numbers）
Cpu = 99385 * 0.1 + 1.0 = 9939.5   (1.0是微调值)   # 99385 代表rows 行数
成本 = IO + CPU = 1966.43 + 9939.5 = 11,905.93 

使用索引的代价

二级索引代价 (先算二级唯一索引 再算普通二级索引)

Select * from s1 where key1 in (“a”,”b”,”c”) and key2 > 10 and key2 < 100 and key3 > key2 and key_part1 like “%bob%” and commen_field= “json”

使用唯一二级索引 key2

对于二级索引+回表方式执行的查询: 成本= 扫描区间数量和需要回表的记录数

扫描区间数量：无论查询扫描区间的二级索引占多少页, 成本与扫描一个页的I/O成本相同， key2 > 10 and key2 < 100区间为 (10,100) 就一个区间, 意味着一个I/O成本 1 * 1.0 =1

需要回表的记录数: 找到 key=10 和 key=100的页号, 然后根据page Header的PAGE_N_RECS属性 可以大概分析出行数, 例 有100行数据，CPU 成本= 100 * 0.2 + 0.01 =20.01 （0.2是读取记录成本常数， 0.01是微调值）

根据需要回表的记录（主键值）执行回表操作：mysql定义每次回表相当于访问一个数据页, 100个记录意味着100个页 固IO成本 100*1  = 10 （1读取一个页的I/O成本常数）

回表操作后取到结果集再判断 是否符合其它条件：100行数据的 cpu 成本 = 100 * 0.2 = 20 （0.2是 检测一条记录的成本常数）

• 总成本 = IO （扫描区间的数量+ 预估二级索引记录数）+ cpu （读取二级索引记录成本+读取后检测是否符合其它条件成本）
• 总成本 = IO (1 + 100 *1 )  + CPU (100 * 0.2 + 0.01 + 100 * 0.2) = 101 + 40.01=141.01

其它二级索引方式以此类推，最终结合综合成本 选出最优的，但可见 走了索引的成本 和全表扫描的成本差异是巨大的

2 基于索引统计数据的成本计算

前文说道 扫描索引形成扫描区间，如果类似这类SQL select * from xxx where xxx in (a,b,c,d,e) 会有多个扫描区间，MySQL把通过索引访问索引树来计算某个扫描区间内索引记录数的方式称为 index_dive;

但是如果扫描区间非常多, in 里面有 2000个字段, 那么性能可能还不如直接做全表扫描

MySQL为了优化这类访问方式 做了阈值限制，访问的区间数 大于 @@eq_range_index_dive_limit (默认值200) 就使用索引统计数据(index statistics)来进行估算

show index from s1; 

结合 show table status like “s1” 中的rows, 99385 / Cardinality  拿key2为例 代表 key2列的单个值重复次数，此处为1，意味着没有重复值，需要回表99385次

如果有 2000个扫描区间 + 每个单点回表 2000 * 1 =2000次回表 （此处特殊key2是唯一索引，如果普通索引可能是 10个重复值，2000*10 =20000）

这样通过索引统计数据来估算出该表不同索引的查询成本

3 连接查询的成本

对于join查询 成本 = 单次查询驱动表的成本 + 多次查询被驱动表的成本（具体次数等于驱动表的扇出）

例

Select * from s1 join s2  where s1.key2 > 10 and s1.key2 < 1000;   #s1是驱动表，扇出就是 10 <s1.key2< 100 区间内的行数，该扇出值决定被驱动表执行多少次

explain Select * from s1 join s2  where s1.key2 > 10 and s1.key2 < 1000 and s1.key1 in (1,9,100) and s1.commen_field > "xxx"

这情况 用到了 key2的唯一索引，但是在这之后 还有多少条记录满足 s1.key1 in (1,9,100) and s1.commen_field > "xxx" 条件呢？这是优化器需要判断的（靠哥德巴赫猜想 XJBM）MySQL把这种猜的过程称为 condition Filtering（条件过滤）可能是index_dive 也可能 index_static

两表的连接成本分析

连接查询的总成本 = 单次访问驱动表的成本 + 扇出值  *  单次访问被驱动表的成本

•  对于左/右外连接 驱动表是固定的, 只需要分别为驱动/被驱动选择成本最低的访问方法即可
•  对于内连接，驱动表和被驱动位置可以互换, 要考虑最优的连接顺序以及最优成本

explain Select * from s1 join s2  on s1.id = s2.id where s1.key2 > 10 and s1.key2 < 1000 and s1.key1 in (1,9,100) and s1.commen_field > "xxx"

核心思想：1 尽量减少驱动表的扇出  2 访问被驱动表的成本要低

执行计划显示 s1 是驱动表， s2是被驱动表 分析相关成本如下

•   驱动表：用key2的唯一索引充当条件的成本最低
•   被驱动表：可以用到主键id充当检索条件

固总的成本 = 用unique_key2 访问 S1 的成本 + s1的扇出 *  访问 S2(primary key) 的成本

调节成本常数

一条SQL执行时候, 其实在server 和 innodb引擎层都有执行，server层负责 连接管理，查询缓存，语法解析和优化，引擎层负责具体的存/取

在server层操作的成本常数存在 mysql.server_cost中， 在引擎层的成本常数存在 mysql.engine_cost中

select * from mysql.server_cost;

Select * from mysql.engine_cost

补充 查看库下面表的大小和行数估算统计

自动统计相关信息

innodb_stats_auto_recalc | ON

innodb_stats_method | nulls_equal  # 代表所有null值都相等(nulls_equal, nulls_unequal, nulls_ignored)

手动收集统计信息

Analyze table table_name  #立即收集相关统计信息

Flush  table table_name # 让优化器重新加载新的统计信息

库下表的排序

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='test'
order by table_rows desc, index_length desc;

select * from mysql.innodb_table_stats where table_name = "sbtest1_97";

N_rows 统计原理：从聚簇索引中取N个页， 统计每个页 有多少行数据，再用平均值*全部叶子节点数量，相关统计参数
innodb_stats_persistent    ON    
innodb_stats_persistent_sample_pages 20   # 统计数据时 采样的页面数量

索引统计信息

Innodb_Index_stats

select * from innodb_index_stats where table_name="sbtest1_97";