MySQL Index Condition Pushdown(ICP)优化
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL Index Condition Pushdown(ICP)优化,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3951字,纯文字阅读大概需要6分钟。
内容图文
![MySQL Index Condition Pushdown(ICP)优化](/upload/InfoBanner/zyjiaocheng/448/643a7b1237f84c4eb78d28c94e1eec40.jpg)
mysql> desc employees.employees; +------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+-------+ | emp_no | int(11) | NO | PRI | NULL | | | birth_date | date | NO | | NULL | | | first_name | varchar(14) | NO | | NULL | | | last_name | varchar(16) | NO | | NULL | | | gender | enum(‘M‘,‘F‘) | NO | | NULL | | | hire_date | date | NO | | NULL | | +------------+---------------+------+-----+---------+-------+ 6 rows in set (0.09 sec)
mysql> alter table employees add index idx_fn_ln (first_name,last_name );//添加联合索引 Query OK, 0 rows affected (3.98 sec) Records: 0 Duplicates: 0 Warnings: 0
Index Condition Pushdown打开的情况下
mysql> explain select * from employees where first_name =‘Mary‘ and last_name like ‘%man‘; +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-----------------------+ | 1 | SIMPLE | employees | ref | idx_fn_ln | idx_fn_ln | 16 | const | 224 | Using index condition | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec)
关闭的情况下:
mysql> set optimizer_switch = ‘index_condition_pushdown=off‘; //关闭index condition pushdown Query OK, 0 rows affected (0.02 sec) mysql> explain select * from employees where first_name =‘Mary‘ and last_name like ‘%man‘; +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+ | 1 | SIMPLE | employees | ref | idx_fn_ln | idx_fn_ln | 16 | const | 224 | Using where | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
看where语句中包含上述联合索引,并且包含一个非索引列:
mysql> set optimizer_switch=‘index_condition_pushdown=on‘; Query OK, 0 rows affected (0.00 sec) mysql> explain select * from employees where first_name =‘Mary‘ and last_name =‘%man‘ and gender =‘M‘; +----+-------------+-----------+------+---------------+-----------+---------+-------------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+-----------+---------+-------------+------+------------------------------------+ | 1 | SIMPLE | employees | ref | idx_fn_ln | idx_fn_ln | 34 | const,const | 1 | Using index condition; Using where | +----+-------------+-----------+------+---------------+-----------+---------+-------------+------+------------------------------------+ 1 row in set (0.01 sec)
同样有using index condition,不过index过滤后,Server还要根据gender列来判断一下 storage engine返回的值;
ICP只能用于二级索引,不能用于主索引。
也不是全部where条件都可以用ICP筛选,如果某where条件的字段不在索引中,当然还是要读取整条记录做筛选,在这种情况下,仍然要到server端做where筛选。
ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。
本文使用employees数据库表和数据在这里************下载,该库功能数据齐全,employees_db-full-1.0.6.tar.bz2;
MySQL Index Condition Pushdown(ICP)优化
标签:
本文系统来源:http://www.cnblogs.com/onlysun/p/4515335.html
内容总结
以上是互联网集市为您收集整理的MySQL Index Condition Pushdown(ICP)优化全部内容,希望文章能够帮你解决MySQL Index Condition Pushdown(ICP)优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。