MySQL 语句优化 ICP
ICP介绍
Index Condition Pushdown(ICP)是MySQL 5.6中的的新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。
当MySQL使用索引从表中检索行时，索引条件下推(ICP)是一种优化。
- 在禁用ICP的情况下： 存储引擎遍历索引以定位基表中的行，并将它们返回给Server层，Server层再去为这些数据行进行WHERE条件的过滤。
- 在启用ICP的情况下： 如果只使用索引中的列就可以计算WHERE条件的部分内容，那么MySQL服务器将WHERE条件的这部分内容下推到存储引擎。存储引擎再通过使用索引条目来计算推入索引条件，只有满足了这个条件才从表中读取行。
实现方式
备注：
ICP的目标是减少整行读取的数量，从而减少I/O操作。同时减少Server层和innoDB层的数据交互，并且避免Server层数据处理，Server层更多的资源用在协调上。
执行计划
通过EXPLAIN上Extra信息里的 Using index / Using index condition 进行区分。
控制参数
mysql> SHOW VARIABLES LIKE 'optimizer_switch'\G;
ICP使用条件
- ICP用于range、ref、eq_ref和ref_or_null访问方法。
- ICP可以用于InnoDB和MyISAM表，包括分区表。
- 对于InnoDB表，ICP只用于二级索引。
- 虚拟生成的列上创建二级索引时，不支持ICP。
- 引用存储函数的条件不能下推。
- 无法下推触发的条件。
- 引用子查询的条件不能下推。
ICO对于IO影响 ， 通过STATUS观察
#创建表
CREATE TABLE `tuser` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` tinyint DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_age_name` (`age`,`name`)
) ENGINE=InnoDB;
#模拟数据
DELIMITER //
DROP PROCEDURE IF EXISTS proc_batch_insert;
CREATE PROCEDURE tuser_batch_insert()
BEGINDECLARE pre_name BIGINT;DECLARE ageVal INT;
DECLARE i INT;
SET pre_name=139;
SET ageVal=100;
SET i=1;
WHILE i <= 2000 DO
INSERT INTO tuser(`name`,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+1)*rand()%30,NOW(),NOW());
SET pre_name=pre_name+100;
SET i=i+1;
END WHILE;
END //
DELIMITER ;
#执行模拟数据call tuser_batch_insert();
#测试语句
FLUSH STATUS; #刷新统计计数
SHOW STATUS LIKE '%Handler_read%';
SET optimizer_switch="index_condition_pushdown=off";
SET optimizer_switch="index_condition_pushdown=on";
SELECT * FROM tuser WHERE age > 29 AND name LIKE '%639@qq.com';
指标观察：
- Handler_read_first：此选项表明SQL是在做一个全索引扫描，注意是全部，而不是部分，所以说如果存在WHERE语句，这个选项是不会变的。
- Handler_read_key：此选项数值如果很高，那么恭喜你，你的系统高效的使用了索引，一切运转良好。
- Handler_read_next：此选项表明在进行索引扫描时，按照索引从数据文件里取数据的次数。
- Handler_read_prev：此选项表明在进行索引扫描时，按照索引倒序从数据文件里取数据的次数，一般就是ORDER BY … DESC。
- Handler_read_rnd：此选项表明查询直接操作了数据文件，很多时候表现为没有使用索引或者文件排序。
- Handler_read_rnd_next：此选项表明在进行数据文件扫描时，从数据文件里取数据的次数。
备注：
这里Handler_read_next索引扫描时，按照索引从数据文件里取数据的次数，明显差距非常大。
OPTIMIZER_TRAC进行观察
refine_plan： 该阶段展示的是改善之后的执行计划，如执行计划中没有需要再优化的地方，可直接应用：
SHOW VARIABLES LIKE 'optimizer_switch';
SET OPTIMIZER_TRACE="enabled=on";
SELECT * FROM tuser WHERE age > 29 AND name LIKE '%639@qq.com';
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30 \G;
SET OPTIMIZER_TRACE="enabled=off";
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30 \G;
*************************** 1. row ***************************
。。。
{
"refine_plan": [
{
"table": "`tuser`",
"pushed_index_condition": "((`tuser`.`age` > 29) and (`tuser`.`name` like '%639@qq.com'))",
"table_condition_attached": null
} ]
}- table：涉及的表名及其别名。
- pushed_index_condition：可使用到ICP的条件句。
- table_condition_attached： 在attaching_conditions_to_tables阶段添加了附加条件的条件语句。
- access_type：优化后的索引访问类型。
总结
ICP是使用场景是二级索引，ICP的加速效果取决于筛选掉的数据的比例：
- 减少了回表的IO。
- 降低了innoDB引擎层传递到Server层的成本。
- 比如 select for update更新数据 ，ICP大大减少行锁的数量，因为行锁是在引擎层。
墨天轮原文链接：https://www.modb.pro/db/99145（复制到浏览器或者点击“阅读原文”立即查看）
关于作者
崔虎龙，云和恩墨MySQL技术顾问，长期服务于金融、游戏、物流等行业的数据中心，设计数据存储架构，并熟悉数据中心运营管理的流程及规范，自动化运维等。擅长MySQL、Redis、MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验，10年数据库运维工作经验，其中专职做MySQL工作8年；曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务；涉及行业：金融（银行、理财）、物流、游戏、医疗、重工业等。
END