MySQL2_4查询成本分析
MySQL执行查询语句的成本分析
MySQL 的查询优化器会比较多种查询方案，得到成本最低的方案，即所谓执行计划，之后才会调用存储引擎提供的接口来执行查询，这个过程大致如下：
1、根据搜索条件，找出所有可能使用的索引（possible keys）；
2、计算全表扫描的代价；
3、计算使用不同索引执行查询的代价；
4、对比各种执行方案的代价，找出成本最低的那一个。
计算代价
1、全表扫描的代价
全表扫描需要将聚簇索引所有页面加载到内存然后一一匹配，对全表扫描的性能统计涉及到聚簇索引占用的页面数和该表中的记录数，可以使用SHOW TABLE STATUS来统计这个信息。
1 | SHOW TABLE STATUS LIKE 'ad_content'; |
结果中的Rows表示记录条数，在 MyISAM 中是准确的，而在 InnoDB 中则是一个估计值。因为全表扫描时需要判断每条记录是否满足条件，因此该值能粗略代表对该表执行全表扫描所需的CPU 成本。
Data_length表示表占用的存储空间字节数，对于 MyISAM 来说就是数据文件的大小，对于 InnoDB 来说就是聚簇索引占用的存储空间大小。因为默认页面大小为 16KB，因此可以推导出聚簇索引的页面数量是Data_length / 16 / 1024，实际上可以粗略代表对该表执行全表扫描所需的IO 成本。
2、二级索引的代价
从二级索引中定位数据的 ID，回表（到聚簇索引）根据上一步的 ID 值找到完整的记录。
另外，统计记录数量时，还会用到一个index dive的方法（MySQL 自创的一个方法），比如：
1 | SELECT * FROM single_table WHERE key1 IN ('aa1', 'aa2', 'aa3', ... , 'zzz'); |
由于 idx_key1 并不是唯一（unique）索引，因此key1='aa1'这些条件并不一定能唯一确定一条记录，查询优化器会先获取索引对应的 B+树的区间最左记录和区间最右记录，然后估算这两条记录之间有多少条记录，这种方式被称为index dive。
当然index dive并不是一定会生效，如果 in 条件中的参数超过了阈值就不能生效，这个阈值可以通过SHOW VARIABLES LIKE '%dive%';查到。
如果因为超过阈值（默认 200）而无法生效，则需要改用所谓的索引统计数据来进行估算，MySQL 也会为表中的每一个索引维护一份统计数据，可以使用show index from 表名来查看，其中有一个比较关键的字段Cardinality表示基数，即索引列中不重复值的个数，计算平均一个值重复次数即Rows / Cardinality，比如表里现在有 100 行数据，表基数为 2，则最终计算出的每个值会有的记录条数就是 50，对于上面那条 sql，如果IN条件中有 10000 个参数，则统计结果约需要回表的记录数就是50 * 10000 = 500000，不过这种方式相对index dive来说虽然简单，但是并不精确。
3、多个二级索引的代价（索引合并 Index Merge）
将多个搜索条件用 AND 连接之后，查询优化器还需要判断一下是否满足 Intersection 索引合并的条件，简而言之，如果查询出的记录都是按主键排序的，则可以采用 Intersection 索引合并。
4、对比多种执行方案，找出成本最低的一种
查询优化器在统计每种查询方式的代价时都会给一个代价常量，用于计算每一种方式的代价值，最后比较选取代价最小的那个。
连接查询的成本
单表连接
对两表的连接查询来说，查询成本主要包括：
1、单次查询驱动表的成本；
2、根据第一步查出的结果（称为扇出）多次查询被驱动表的成本。
所以，判断连接查询成本的方法是计算扇出值的大小：
1 | 连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本 |
当连接方式为外连接，则驱动表是固定的，因此查询时只要为驱动表和被驱动表选择最优访问方式即可，但是如果是内连接，驱动表和被驱动表的位置是可以互换的，那么优化器会使用索引统计数据来进行比较选择。
多表连接
如果是 n 张表的内连接，计算的时间复杂度将是单表连接的 n!倍，当然优化器并不是老老实实地这么做：
1、预先设定最小连接成本，只要少于这个值就采用这种连接方式；
2、只对optimizer_search_depth张表执行连接顺序成本的分析，这个值是一个系统变量，显然越大则成本分析越精确；
3、启发式规则：如果满足一些规则则直接不分析，这些规则被称为启发式规则。