mysql-查询的成本

mysql的查询优化器会找出执行该语句的所有可能的方案,对比找出成本最低的执行计划

1. 计算全表扫描的成本

　　计算全表扫描需要两个信息,聚簇索引占用的页面数,该表的记录数,mysql为每个表维护了一系列的统计信息 使用 SHOW TABLE STATUS可以查看信息

　　rows 对应myisam这个值是准确的,对于innodb是个估算值

　　data length 表数据占用的字节数 加入data length 值为 1589248 默认一个页16k 可以计算出有97个页

　　- 计算io成本

　　97 * 1.0 +1.1 = 98,1  97指索引占用的页数,1.0 是加载一个页面的成本常数,1.1 是个微调值

　　- 计算cpu成本

　　9693 * 0.2 + 1.0 = 1939.6  9693为rows记录数 0.2是访问一条记录的成本常数 后面1.0是微调值

 

2. 范围查询成本计算

index dive 通过访问索引对应的b+树来计算某个范围区间的索引记录条数的方式称为index dive 比如in方法中传入10,100 先查找到索引为10的记录 然后查到索引为100的记录,

如果跨页就查找上一个索引记录页 计算跨几个页就行

这个dive值可配置 超过了就使用索引统计数据来估算 mysql为每个索引维护一个统计数据 

根据rows / Cardinality 求出单条记录重复值大小

参数个数 * 单条记录重复值大小计算出需要回表的数量

 

3. 多表查询的成本计算

有一部分估值需要猜测,不是准确值

- 如果是使用全表扫描 计算驱动扇出需要猜

- 使用索引扫描 需要猜驱动表扇出除索引条件外满足其他搜索条件的记录数

这个过程称为condition filtering

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

对于多表也不是所有连接顺序都要枚举一遍,

　　- mysql会维护一个当前最小连接查询成本,在计算过程中超过就不继续计算了

　　- optimizer_search_depth 计算这个数量

 

innodb统计数据

innodb以表为单位统计存储统计数据的,会统计表的索引数据和表的数据

 

 n_rows的计算方法是: 

innodb_stats_persistent_sample_pages的选取出这个数量的页,计算主键数量 计算出每个叶面积平均数值 * 叶子节点数

 

 

定期更新统计数据

- 开启innodb_stats_auto_recalc 计算过程是异步的

　　当更新数超过表的记录10% 更新统计数据

-  手动调用analyze table更新统计数据 计算过程是同步的

- 可以手动更改 innodb_table_stats和innodb_index_stats表 然后flush table tablename;

 

innodb_stats_method控制着是否把null当成相同值计算到重复值中