MySQL性能优化（九）range和ref
有的时候，我们会遇到这样的情况：明明有索引，明明有更好的执行计划，但是优化器并没有选择这个最优的执行计划。
优化器可能会选择并非最优的索引，可能选择并非最优的数据访问方式。
下面是一个真实的例子：
一个例子
SELECT * FROM dtops_mysql_stat WHERE tenant_id = 1 and instance_name='dtstack-dev1:3306' and check_time >='2018-04-03' and check_time <= '2018-05-03' ORDER BY check_time desc limit 10;
索引：
KEY `ind_tenantid` (`tenant_id`,`instance_name`,`check_time`)
根据这个SQL的where条件和索引，这个SQL的效率应该非常好，索引ind_tenantid和where条件完全符合，order by也可以利用索引的有序性。
但是，这个SQL执行却需要1秒。
我们来看一下SQL的执行计划：
查询用到了ind_tenantid索引，但是只用到了索引中的tenant_id和instance_name字段，这可以从type和key len看出。索引中的check_time，本来可以用来过滤check_time的数据，但是优化器并没有使用。
我们给这个SQL加上一个force index：
SELECT * FROM mysql_stat force index(ind_tenantid) WHERE tenant_id = 1 and instance_name='dtstack:3306' and check_time >='2018-04-03' and check_time <= '2018-05-03' ORDER BY check_time desc limit 10;
加上force index之后，SQL的执行时间只需要几毫秒，对比一下执行计划：
type变成了range，key len变成了204，说明加上force index后，优化器用到了check_time来过滤数据。
但是优化器为什么会这么选择呢？我们可以使用optimizer trace功能来跟踪优化器是如何来评估各种可能的执行计划。
关于optimizer trace的具体使用方法，可以参考官方文档： https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_OPT_TRACE.html
这里不做具体介绍。
我们来对比下上述2个SQL的trace文件：
不加force index的trace
（图1）
（图2）
（图3）
我们来看一下trace中的关键信息：
1、table scan的cost为78431，这是优化器根据表的记录数评估得到的全表扫描的成本。
2、使用ind_tenantid进行range访问的成本为84899，比全表扫描的成本还要高，所以没有选择这个执行计划。
3、ref的成本为30635。
最终选择了ind_tenantid的ref执行计划。
所以不加force index的情况下，由于where条件的过滤性不高，优化器评估后认为所用索引range的成本比全表扫描还要高，所以放弃了对本SQL效率最高的执行计划。
加了force index的trace
加上force index之后的trace：
（图1）
（图2）
（图3）
加上force index后，
1、table scan的成本为infinity。这可能就是mysql优化器实现force index使用的手段。
2、range scan的成本还是84899，由于加了force idnex，这个成本比table scan的成本低，所以没有排除这个执行计划。
3、最终选择了range scan的执行计划。为什么这里没有使用ref的执行计划？可能是优化器在选择同一个索引的不同执行计划时，优先使用range。
总结
优化器是关系型数据库的一个核心组件，实现也比较复杂。
当遇到优化器选择的执行计划和我们想要的最优的执行计划不一致时，我们可以尝试使用SQL Hint或调整优化器参数来影响优化器。
我们可以使用optimizer trace来观察优化器如何选择执行计划。
如果要对优化器成本评估有更深入的理解，可以尝试去分析优化器的代码。
