Index Condition Pushdown(ICP)是MySQL 5.6中的的新特性，是一種在存儲引擎層使用索引過(guò)濾數據的一種優(yōu)化方式。ICP可以減少存儲引擎訪(fǎng)問(wèn)基表的次數以及MySQL服務(wù)器訪(fǎng)問(wèn)存儲引擎的次數。
當MySQL使用索引從表中檢索行時(shí)，索引條件下推(ICP)是一種優(yōu)化。
在禁用ICP的情況下：
存儲引擎遍歷索引以定位基表中的行，并將它們返回給Server層，Server層再去為這些數據行進(jìn)行WHERE條件的過(guò)濾。
在啟用ICP的情況下：
如果只使用索引中的列就可以計算WHERE條件的部分內容，那么MySQL服務(wù)器將WHERE條件的這部分內容下推到存儲引擎。存儲引擎再通過(guò)使用索引條目來(lái)計算推入索引條件，只有滿(mǎn)足了這個(gè)條件才從表中讀取行。
通過(guò)EXPLAIN上Extra信息里的 Using index / Using index condition 進(jìn)行區分。
控制參數
mysql> SHOW VARIABLES LIKE 'optimizer_switch'\G;
ICP用于range、ref、eq_ref和ref_or_null訪(fǎng)問(wèn)方法。
ICP可以用于InnoDB和MyISAM表，包括分區表。
對于InnoDB表，ICP只用于二級索引。
虛擬生成的列上創(chuàng )建二級索引時(shí)，不支持ICP。
引用存儲函數的條件不能下推。
無(wú)法下推觸發(fā)的條件。
引用子查詢(xún)的條件不能下推。
#創(chuàng )建表
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();
#測試語(yǔ)句
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';
指標觀(guān)察：
Handler_read_first：此選項表明SQL是在做一個(gè)全索引掃描，注意是全部，而不是部分，所以說(shuō)如果存在WHERE語(yǔ)句，這個(gè)選項是不會(huì )變的。
Handler_read_key：此選項數值如果很高，那么恭喜你，你的系統高效的使用了索引，一切運轉良好。
Handler_read_next：此選項表明在進(jìn)行索引掃描時(shí)，按照索引從數據文件里取數據的次數。
Handler_read_prev：此選項表明在進(jìn)行索引掃描時(shí)，按照索引倒序從數據文件里取數據的次數，一般就是ORDER BY … DESC。
Handler_read_rnd：此選項表明查詢(xún)直接操作了數據文件，很多時(shí)候表現為沒(méi)有使用索引或者文件排序。
Handler_read_rnd_next：此選項表明在進(jìn)行數據文件掃描時(shí)，從數據文件里取數據的次數。
備注：
這里Handler_read_next索引掃描時(shí)，按照索引從數據文件里取數據的次數，明顯差距非常大。
refine_plan：
該階段展示的是改善之后的執行計劃，如執行計劃中沒(méi)有需要再優(yōu)化的地方，可直接應用：
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階段添加了附加條件的條件語(yǔ)句。
access_type：優(yōu)化后的索引訪(fǎng)問(wèn)類(lèi)型。
ICP是使用場(chǎng)景是二級索引，ICP的加速效果取決于篩選掉的數據的比例：
減少了回表的IO。
降低了innoDB引擎層傳遞到Server層的成本。
比如 select for update更新數據 ，ICP大大減少行鎖的數量，因為行鎖是在引擎層。
墨天輪原文鏈接：https://www.modb.pro/db/99145（復制到瀏覽器或者點(diǎn)擊“閱讀原文”立即查看）
聯(lián)系客服
