Mysql为什么选择走全表/索引：内核查询成本计算规则

trace工具的介绍

使用方式

‐‐ 开启trace
SET optimizer_trace="enabled=on";
-- 查询数据
select * from file_info where file_name > 'a' order by create_time;
-- 查看trace信息
SELECT * FROM information_schema.OPTIMIZER_TRACE;

常见的trace字段信息

• "join_preparation": ‐‐第一阶段：SQL准备阶段，格式化sql
• "join_optimization": ‐‐第二阶段：SQL优化阶段
• "condition_processing": ‐‐条件处理
• "table_dependencies": ‐‐表依赖详情
• "rows_estimation": ‐‐预估表的访问成本
• "table_scan": ‐‐全表扫描情况
• "potential_range_indexes": ‐‐查询可能使用的索引
• "index": "PRIMARY", ‐‐主键索引
• "index": "idx_name_age_position", ‐‐辅助索引
• "analyzing_range_alternatives": ‐‐分析各个索引使用成本
• "rowid_ordered": false, ‐‐使用该索引获取的记录是否按照主键排序
• "index_only": false, ‐‐是否使用覆盖索引
• "rows": 5061, ‐‐索引扫描行数
• "cost": 6074.2, ‐‐索引使用成本
• "chosen": false, ‐‐是否选择该索引
• "best_access_path": ‐‐最优访问路径
• "considered_access_paths": ‐‐最终选择的访问路径
• "access_type": "scan", ‐‐访问类型：为scan，全表扫描
• "chosen": true, ‐‐确定选择
• "join_execution": ‐‐第三阶段：SQL执行阶段

最终选择方式

• 从索引，全表扫描中选择cost值最小的一种进行执行

一条sql会产生什么成本

• I/O成本：我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。
• CPU成本：读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。
• 对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2

单表查询的成本

• 在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询。

单表查询的索引选择过程

• 根据搜索条件，找出所有可能使用的索引：possible keys
• 计算全表扫描的代价：I/O成本（扫描的页数）* 1.0 + 1.1（固定值） + CPU成本（行数） * 0.2 + 1.0（固定值）
• 计算使用不同索引执行查询的代价
• 对比各种执行方案的代价，找出成本最低的那一个

全表扫描的记录行数的值从哪里来？

• MySQL为每个表维护了一系列的统计信息！

• 使用 SHOW TABLE STATUS 语句来查看表的统计信息
  

• 得到的Rows列信息：对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。

• Data_length：表示表占用的存储空间字节数。

• MyISAM存储引擎的表来说，该值就是数据文件的大小。
• 对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：
  Data_length = 聚簇索引的页面数量 x 每个页面的大小
  对于我刚才的结果：49152 ÷ 16 ÷ 1024 = 3页

• 全表扫描成本：87*0.2 + 1.1 + 3*1 + 1.0=90

计算使用不同索引执行查询的代价

• 范围区间数量：一个连续的范围IO成本就是1。
• in的数量：in中的每一个字符都是一个区间。也就是说in的每一条记录都是一次回表。
• 需要回表的记录数：估算这个这个范围内需要回表的次数。mysql5.7版本涉及到的页面小于10页，精确计算；如果涉及到的页面超过10页数据，进行估算（取前10页的记录行数进行估算，有几个页面可以找他的B+书的上一级）！每行回表都是一次记录！MySQL评估回表操作的I/O成本依旧很简单粗暴，他们认为每次回表操作都相当于访问一个页面；简单说就是有多少记录，回表几次。
• 回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立。cpu匹配。

in参数过多的处理

• 查看in参数精确匹配的个数值（MySQL 5.7.21中默认200）： show variables like '%dive%';
• in的参数个数超过这个eq_range_index_dive_limit的值：估算一个值的重复次数 ≈ Rows（全表扫描估计的行数） ÷ Cardinality（show index from 查询出来的当前索引Cardinality的和）。然后用估算后的值乘上当前的in的格式，就是实际计算的值。

连接查询的成本

• 连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本
• 扇出：对驱动表进行查询后得到的记录条数
• 计算方式的细节与单表相同！