【原】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 '[e-mail]';

指标观察：

• 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 '[e-mail]';

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 '[e-mail]'))",
        "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./db/99145（复制到浏览器或者点击“阅读原文”立即查看）

关于作者

崔虎龙，云和恩墨MySQL技术顾问，长期服务于金融、游戏、物流等行业的数据中心，设计数据存储架构，并熟悉数据中心运营管理的流程及规范，自动化运维等。擅长MySQL、Redis、MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验，10年数据库运维工作经验，其中专职做MySQL工作8年；曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务；涉及行业：金融（银行、理财）、物流、游戏、医疗、重工业等。

END