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

发布时间:2020-07-19编辑:脚本学堂
mysql 从5.1开始,提供了关于优化器选项的开关选项,可以强制在优化器糊涂时,关闭一些不该做的事情;或者当想查看优化器为什么要这么选择时,可以手动关闭或者打开这些选项来进行优化策略的改变。

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

复制代码 代码示例:
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
把内表去重然后生成有对应索引的临时表(有点类似其他数据中的物化视图),然后通过外表的对应键值遍历这张临时表。
主要针对不能抓华为半连接的检索类型。