mysql优化器可选开关用法详解

有关mysql优化器可选开关的用法。

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，只需要知道有还是没有就行了。
所以一般用在对子查询的优化居多。
 

7. firstmatch
只选用内表的第一条与外表匹配的记录。

8. loosescan
把内表的数据基于索引分组，取每组第一条数据即可。

9. materialization
subquery_materialization_cost_based
把内表去重然后生成有对应索引的临时表（有点类似其他数据中的物化视图），然后通过外表的对应键值遍历这张临时表。
主要针对不能抓华为半连接的检索类型。

您可能感兴趣的文章：
mysql数据库性能优化技巧
mysql优化器可选开关用法详解
学习MySQL数据分页查询(limit用法)
实例详解mysql limit大数据量分页优化的方法
mysql 索引优化之btree、hash与rtree
MySQL-5.6.13 zip解压版的安装与配置教程
mysql中limit、order by和group by的用法实例解析
mysql sleep连接过多的解决方法
mysql 重新建立索引的方法
优化mysql的limit offset的例子