【原创】MySQL 优化器可选开关详解

江南才子

　　MySQL 从5.1开始，提供了关于优化器选项的开关选项。 这样，我们可以强制在优化器糊涂的时候，关闭一些我们认为不该做的事情；或者是当我们想查看优化器为什么要这么选择的时候，可以手动关闭或者打开这些选项来进行优化策略的改变。
　　MySQL 从5.5 和 5.6 开始，增加了更多的开关选项。 以便使查询更加优化。 下面我来一个一个解释下这些优化开关的含义以及用途。
　　

　　MySQL 5.1 增加以下开关：
　　index_merge
　　1). index_merge_intersection
　　如果有两个单独的索引都可用，但是其中任何一个都不是最优化的，那么优化器选择合并两个索引并且在他俩的结果集中做一个交集，然后根据这个交集对磁盘数据进行匹配。
　　2). index_merge_union
　　用于OR，把所有相关索引连接起来，找到记录对应的ROWID，然后根据ROWID获取磁盘上的数据。
　　3). index_merge_sort_union
　　用于OR，把所有相关索引连接起来，找到记录对应的ROWID，并且好顺序，然后根据ROWID获取磁盘上的数据。
　　

　　MySQL 5.5 新增以下开关：
　　engine_condition_pushdown
　　只用于NDB引擎。
　　不开启： 所有数据节点的数据都发送到SQL节点来处理。
　　开启后： 按照WHERE条件过滤后的数据发送到SQL节点来处理。
　　

　　MySQL 5.6 增加以下开关：
　　1. mrr
　　mrr_cost_based
　　这个是从MySQL 5.6 开始增加的开关。  意思是针对普通索引把资源利用最大化，主要是针对多列索引，也叫组合索引来做基本扫描，然后对匹配的记录按照主键排序，这样按照有序的主键顺序从磁盘上扫描需要的全部记录。根本功能是把对磁盘的随机扫描转化为顺序扫描。
　　主要针对的是数据太大，放不到CACHE里面（比如INNODB，或者MYISAM）。  目前对全索引扫描意义不大。
　　2. batched_key_access
　　（在JOIN BUFFER POOL里）当内表被扫描后，针对扫描到的记录，找到对应的主键值并且一次***给MRR接口。省掉了频繁跟磁盘交互的IO部分。
　　3. block_nested_loop
　　（在JOIN BUFFER POOL里）当内表被扫描后，针对原来的记录加上一个是否匹配标记，生成新的记录。这样下次外表的值如果再来遍历内表，就只扫描不匹配的即可。省掉部分CPU资源。
　　4. index_condition_pushdown
　　不开启： 对于组合索引（A,B）来说，如果A可以用到索引，B不能用到，那么只能在索引树上找到A，然后再用A对应的指针到磁盘上遍历数据。
　　开启后： 同样对于组合索引（A,B）来说，如果A可以用到索引，B不能用到，那么在索引树上找到A，然后再通过B的过滤条件在索引树上进行过滤，删选后的指针到磁盘上遍历数据。
　　但是ICP并不能把随机扫描变为顺序扫描,只是减少了对磁盘交互的IO部分。
　　5. use_index_extensions
　　主要用于INNODB的第二索引，也就是普通的索引,把索引中包含的主键值利用到。比如主键为(a,b),索引为(c). 如果用到了索引c，那么把索引变成(c,a,b) 这样，就可以用到新的组合索引了。
　　不过这种场合用的也比较少，一般是根据组合主键中的第一个字段和普通索引一起来做检索的时候。
　　

　　6. semijoin
　　表示在连接的时候内表去重的过程 。
　　比如对以下的查询，a.class_id 就只需要扫描一次和b.id相同的记录就可以不在继续了。因为不需要关心b表里面是否有多少相同的class_id，只需要知道有还是没有就行了。
　　所以一般用在对子查询的优化居多。
　　
　　

mysql> select b.class_name from class as b, student as a where a.class_id = b.id;                               
+------------+
| class_name |
+------------+
| xinxi    |
| xinxi    |
+------------+
2 rows in set (0.00 sec)

　　7. firstmatch
　　只选用内表的第一条与外表匹配的记录。
　　8. loosescan
　　把内表的数据基于索引分组，取每组第一条数据即可。
　　9. materialization
　　    subquery_materialization_cost_based
　　把内表去重然后生成有对应索引的临时表（有点类似其他数据中的物化视图），然后通过外表的对应键值遍历这张临时表。
　　主要针对不能抓华为半连接的检索类型。