本篇文章给大家分享的是有关如何进行MySQL索引条件下推的简单测试,小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。
自MySQL 5.6开始,在索引方面有了一些改进,比如索引条件下推(指数条件下推,ICP),严格来说属于优化器层面的改进。
如果简单来理解,就是优化器会尽可能的把索引 条件的处理从服务器层下推到存储引擎层。举一个例子,有一个表中含有组合索引idx_cols包含(c1, c2,…, cn) n个列,如果在c1上存在范围扫描的,条件,那么剩余的c2,…, cn这n - 1个上索引都无法用来提取和过滤数据,而ICP就是把这个事情优化一下。
我们在MySQL 5.6的环境中来简单测试一下。
我们创建表emp,含有一个主键,一个组合索引来说明一下。
创建表emp (
empno短整型(5)无符号not null auto_increment,
ename varchar (30) not null,
deptno短整型(5)无符号不是null,
工作varchar(30)不是null,
主键(empno),
键idx_emp_info (deptno, ename)
)引擎=InnoDB字符集=utf8;
当然我也随机插入了几条数据,意思一下。
插入emp值(& # 39;zhangsan& # 39;, 1 & # 39;首席执行官# 39;),(2,& # 39;lisi # 39;, 2 & # 39;首席财务官# 39;),(3 & # 39;wangwu& # 39;, 3 & # 39;首席技术官# 39;),(4 & # 39;jeanron100& # 39;, 3 & # 39; Enginer& # 39;);
ICP的控制在数据库参数中有一个优化器参数optimizer_switch来统一管理,我想这也是MySQL优化器离我们最贴近的时候了。可以使用如下的方式来查看。
显示变量如& # 39;optimizer_switch& # 39;;
当然在5.6以前的版本中,你是看不到指数条件下推这样的字样的。在5.6版本中查看到的结果如下:
# mysqladmin var | grep optimizer_switch optimizer_switch ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,| index_merge_union index_merge=,=, index_merge_sort_union=, index_merge_intersection=, engine_condition_pushdown=, index_condition_pushdown=, mrr=, mrr_cost_based=, block_nested_loop=, batched_key_access=,实体化=,semijoin=, loosescan=, firstmatch=, subquery_materialization_cost_based=, use_index_extensions=对下面我们就用两个语句来对比说明一下,就通过执行计划来对比。
设置optimizer_switch=癷ndex_condition_pushdown=off"
比;解释select *,,从emp 1和100之间的deptno和ename=& # 39; jeanron100& # 39;;
+——+ + - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - +
| | id select_type | | |表类型possible_keys | key | key_len | ref | |行Extra ,,,,,|
+ - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - +
|,1 | SIMPLE ,,,,| emp ,| ALL | idx_emp_info 零| | NULL ,,零| |,,,4 |使用地方|
+ - - - + - - - - - - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - +
而如果开启,看看ICP是否启用。
设置optimizer_switch=癷ndex_condition_pushdown=知道;;比;解释select *,,从emp deptno 10至3000和ename=& # 39; jeanron100& # 39;;
+ - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
| | id select_type表| | type | possible_keys | key ,,,,,,,,| key_len | ref | |行Extra ,,,,,,,,,,,,,,,|
+ - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
|,1 | SIMPLE ,,,,| emp ,| |范围idx_emp_info | idx_emp_info | 94年,,,,,零| |,,,| 1 |使用指数条件
+ - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
1行组(0.00秒)如果你观察仔细,会发现两次的语句还是不同的,那就是范围扫描的范围不同,如果还是用原来的语句,结果还是有一定的限制的。
比;解释select *,,从emp 1和300之间的deptno和ename=& # 39; jeanron100& # 39;;
