Mysql索引成本计算规则 Optimizer Trace_sql索引成本分析

1. 什么是执行成本？

         mysql在执行一条查询sql时，可以通过全表扫描，走索引并回表等多种方案去执行，但mysql总会选择一种成本最低的方案。mysql是怎么知道哪中方案成本最低呢？在回答这个问题之前，首先需要了解一下什么是执行成本，其实在mysql中一条查询语句的执行成本是由下边这两个方面组成的：

• I/O成本
  • 我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。
• CPU成本
  • 加载数据到内存中之后，在内存中需要检测数据是否满足对应的搜索条件，有order by时还需要对数据进行排序，这些操作被称为CPU成本

         对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2，这些数字称之为成本常数。

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

1. 根据搜索条件，找出所有可能使用的索引
2. 计算全表扫描的代价
3. 计算使用可能用到的索引(possible_key)执行查询的代价
4. 对比各种执行方案的代价，找出成本最低的那一个

知道了什么是执行成本之后，接下来看一下mysql是怎么进行成本分析的！

         

2. 开启Optimizer Trace

         MySQL5.6之前的版本只能通过EXPLAIN语句查看到最后优化器决定使用的执行计划，却无法知道它为什么做这个决策.

         在MySQL 5.6以及之后的版本中，MySQL提出了一个optimizer trace（优化跟踪）的功能，它可以让我们看到 优化器生成执行计划、选择最优索引的整个过程，一切都是与执行成本有关
         
开启Optimizer Trace时，需要设置优化追踪为开启状态enabled=on

 set optimizer_trace = "enabled=on";
 show variables like '%optimizer_trace%';
 要执行的sql语句
 select * from information_schema.optimizer_trace;
1
2
3
4

         上述语句对于navicate客户端可能会出现一些问题，如下图。明明设置了优化追踪为开启状态enabled=on，为什么TRACK列中却是空的呢？

为了在navicate中也可以查看Optimizer Trace，需要再做以下设置，（原本的值为1、-1，太小了）

set optimizer_trace_limit=4;
set optimizer_trace_offset=-4;
1
2

以上命令执行完毕后，再次执行select * from information_schema.optimizer_trace;查看Optimizer Trace的配置信息，然后即可在navicate中看到对应sql的TRACK信息了

复制TRACK列中的JSON数据到JSON可视化界面，或者下载一个HiJson工具，即可查看sql成本核算的过程信息了

         

2. sql成本核算规则