MySQL阅读笔记——9.基于成本的优化

在语句执行之前，MySQL查询优化器会找出执行该语句所有可能的方案，对比找到成本最低的方案，该方案叫 执行计划

成本主要分为两种：

I/O成本：以 页 为单位将数据和索引从 磁盘 加载到 内存 的成本，默认规定读取一个页面的成本是1.0 CPU成本：从内存读取记录并检测搜索条件、对结果集进行排序等操作的成本，默认是0.2

9.1 单表查询

具体成本过程：

1. 根据搜索条件找到所有 可能用到的索引

2. 计算使用使用不同索引方案执行查询的代价（优先分析 唯一二级索引）

3. 计算 全表扫描 的代价

4. 对比各种方案代价，找到 执行计划

全表扫描就是把聚集索引的中所有的记录依次和所有条件比较（获取最左侧叶子节点，向右侧遍历比较），把符合条件的放入结果集，由于查询成本=I/O成本+CPU成本因此全表扫描代价与页面数和表中记录数相关 ​ MySQL设计者将为 每个表 都维护了一系列统计信息通过show table status查看：其中的Rows是行数，对于MyISAM是准确值，对InnoDB是估算值；Data_length是表占用的存储空间字节数，对MyISAM是数据文件大小，对InnoDB是聚集索引大小（等同于数据文件）由于每个页面默认16KB，因此可以算出页面的数量

　　通过 索引区间 作为查询条件，MySQL底层会找到区间最左记录和区间最右记录，通过这两个记录向上回溯到 相同祖先节点 通过这些 内节点 的记录数可以获取区间最左记录和区间最右记录的 页面数（准确值），然后从区间最左记录向右读取10个页面，计算 平均 每个页面的记录数。最终通过页面数X平均页面记录数得到索引区间的记录数（估算值）这个过程叫：index dive

　　MySQL也会把IN语句作为 单点区间 来计算成本，如果IN语句中的条件非常多，进行 index dive 效率就会非常低（通过show varibales like %dive%可以查看IN语句参数限制值eq_range_index_dive_limit），因此当IN语句的参数超过限制值不会使用 index dive 而是使用 统计数据 来进行估算（如果使用IN语句却没有用到所引，可能是eq_range_index_dive_limit参数值设置太小，而使用了 统计数据 进行估算）

通过show index from <表名>;可以查看索引的统计数据，其中Cardinality是索引列的基数（去重后记录数的估算值） ​ 通过 show table status like '<表名>'可以查看表空间的统计数据，其中的Rows是表中的记录的估算值 ​ 一个值的重复次数≈Rows / Cardinality，得到 重复次数估算值 和 单点区间 的列相乘，就可以得到这些 单点区间 对应的记录条数估算值了

9.2 连接查询

　　连接查询中，只需要查询一次 驱动表，但要查询多次 被驱动表 其中查询 驱动表 得到的记录数称之为驱动表 的 扇出 （英文：fanout），被驱动表 的查询次数和 扇出 有关

　　对于 内连接 表之间的连接顺序可以互换而不影响结果，因此MySQL优化器会计算不同内连接顺序的多表连接成本进行分析比较

如果 内连接 涉及的表比较多，则对每种连接顺序进行估算分析的成本会很大，因此MySQL设计者想出了多种减少 计算内连接顺序成本 的方法：

1. 提前结束某种顺序的成本分析 ：计算各种内连接顺序成本之前，维护一个表示当前最小内连接查询陈本的全局遍历，当分析某个连接顺序成本时，如果超过了该值，则立即停止分析

2. 系统变量optimizer_search_depth ：如果内连接表的个数小于该值，则会穷举分析每一种内连接顺序的成本，否则只会分析optimizer_search_depth设置数量的顺序，该值越大，成本分析越准确

3. 启发式规则 ：通过系统变量optimizer_prune_level决定是否开启，MySQL设计者根据经验设计了一些规则，凡是不满足这些规则的 内连接 顺序不会进行成本分析

9.3 调节成本常数

　　show table from mysql linke ‘%cost%’可以查看成本所在表，其中的server_cost表是server层的连接管理、查询缓存、语法解析、查询优化等操作的成本常数，engine_cost是存储引擎的成本常数