MySQL系列文章---索引是否使用问题

mysql到底用不用索引，用哪个索引是和内部的执行器分析的成本有关，而不一定选择执行时间最短的那个计划执行。这里说的的成本主要有两个方面：1、IO成本   2 、CPU成本  ，先来理解这两个概念

1、IO（输入输出）成本：

       我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引存储到硬盘上，当我们需要查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个磁盘到内存的这个加载的过程损耗的时间成本叫做IO成本

2、CPU成本：

       读取以及检测记录是否满足对应的搜索条件，对结果集进行比较和排序等这些操作损耗的时间称之为CPU成本。

对于MySQL 5.6以及之前的版本来说，查询优化器是一个黑盒子，你只能通过EXPLAIN语句查看到最后优化器决定使用的执行计划，却无法知道它为什么做这个决策。这对于一部分人来说简直是灾难：“我就觉得使用其他的执行方案比EXPLAIN输出的这种方案强，凭什么优化器做的决定和我想的不一样呢？”

在MySQL 5.6以及之后的版本中，设计MySQL的大佬贴心的为这部分小伙伴提出了一个optimizer trace的功能，这个功能可以让我们方便的查看优化器生成执行计划的整个过程，这个功能的开启与关闭由系统变量optimizer_trace决定，我们看一下：

mysql> SHOW VARIABLES LIKE 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name   | Value                    |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.02 sec)

可以看到enabled值为off，表明这个功能默认是关闭的。

小贴士： one_line的值是控制输出格式的，如果为on那么所有输出都将在一行中展示，不适合人阅读，所以我们就保持其默认值为off吧。

如果想打开这个功能，必须首先把enabled的值改为on，就像这样：

mysql> SET optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)

然后我们就可以输入我们想要查看优化过程的查询语句，当该查询语句执行完成后，就可以到information_schema数据库下的OPTIMIZER_TRACE表中查看完整的优化过程。这个OPTIMIZER_TRACE表有4个列，分别是：

QUERY：表示我们的查询语句。

TRACE：表示优化过程的JSON格式文本。

MISSING_BYTES_BEYOND_MAX_MEM_SIZE：由于优化过程可能会输出很多，如果超过某个限制时，多余的文本将不会被显示，这个字段展示了被忽略的文本字节数。

INSUFFICIENT_PRIVILEGES：表示是否没有权限查看优化过程，默认值是0，只有某些特殊情况下才会是1，我们暂时不关心这个字段的值。

完整的使用optimizer trace功能的步骤总结如下：

1. 打开optimizer trace功能 (默认情况下它是关闭的):

SET optimizer_trace=“enabled=on”;

2. 这里输入你自己的查询语句

SELECT …;

3. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程

SELECT * FROM information_schema.OPTIMIZER_TRACE;

4. 可能你还要观察其他语句执行的优化过程，重复上边的第2、3步

…

5. 当你停止查看语句的优化过程时，把optimizer trace功能关闭

SET optimizer_trace=“enabled=off”;

著作权归NoLongerConfused所有。商业转载请联系NoLongerConfused获得授权，非商业转载请注明出处。