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;

属性名

描述

Table

索引所在的表名

Non_unique

是否是唯一二级索引/主键  0 代表是 1 代表否/普通索引

Key_name

索引名字

Seq_in_index

该列索引的位置，从1开始计数 例联合索引 1/2/3不同位置对应不同字段

Column_name

列名

Collation

排序方式 A代表升序

Cardinality

基数/不重复值的数量, 对于联合索引来说 key_part3代表 key_part1 & key_part2 & key_part3的组合不重复值

Sub_part

有些索引为前缀索引,前n个字符/字节建立索引

Packed

如何被压缩, null 代表未被压缩

NULL

是否允许存null值

Index_type

C常见 BTREE

Comment

注释信息

结合 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

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;

Cost_name

default

备注

disk_temptable_create_cost

20

创建基于磁盘的临时表成本,增大这个值会让优化器少创基于磁盘临时表

disk_temptable_row_cost

0.5

向基于磁盘临时表写入或读取一条记录的成本,增大该值 会让优化器少创基于磁盘临时表

key_compare_cost

0.05

比较排序成本，增大该值提升filesort成本, 让优化器尽可能走索引而不是 filesort

memory_temptable_create_cost

1

创建内存临时表成本，增大会减少优化器在内存的临时表

memory_temptable_row_cost

0.1

向基于内存临时表写入或读取一条记录的成本,增大该值 会让优化器少创基于磁盘临时表

row_evaluate_cost

0.1

读取一条记录检索是否符合搜索条件,增大该值,让优化器更倾向使用索引

Select * from mysql.engine_cost

Cost_name

default

备注

io_block_read_cost

1

从磁盘上读取一个块对应的成本, innodb默认一个块就是一个页, MyISAM 默认4096 一个块

memory_block_read_cost

0.25

从内存中读取一个块对应的成本

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

自动统计相关信息

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

表中记录的条数

Clustered_index_size

表的聚簇索引占用页面的数量

Sum_of_other_index_sizes

表的其它索引占用的页面数量

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";

stat_name 明细

n_leaf_pages

索引的叶子节点占多少页

Size

该索引共占多少页

n_diff_pfx01

表示某列不重复值有多少

k_1 n_diff_pfx01 代表 k列不重复值, 02 代表 k,id 组合不重复值

MySQL

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。