索引條件下推，Index Condition Pushdown，簡稱ICP娇跟，是MySQL通過索引查詢數(shù)據(jù)的一種優(yōu)化方法，對于特定場景下的SQL優(yōu)化具有明顯的性能提高太颤。
原文地址：
https://mytecdb.com/blogDetail.php?id=97
1. ICP索引下推原理
舉一個例子苞俘，有一個索引如下：
idx_all(a,b,c)
查詢語句：
select d from t where a='xx' and b like '%xx%' and c like '%xx%'
查詢走索引idx_all，但是只能使用前綴a的部分龄章。
這樣一個查詢吃谣，在沒有使用ICP時，存儲引擎根據(jù)索引條件a='xx'做裙，獲取記錄岗憋，將這些記錄返回給Server層，Server層再根據(jù)條件 b like '%xx%' 和 c like '%xx%' 來進(jìn)一步過濾記錄锚贱，最后回表拿到d字段數(shù)據(jù)返回給用戶仔戈。
使用ICP時，存儲引擎根據(jù)索引條件a='xx'拧廊，獲取記錄杂穷，并在引擎層根據(jù)條件 b like '%xx%' 和 c like '%xx%' 來過濾數(shù)據(jù)，然后引擎返回記錄到Server層卦绣，顯然，使用ICP時飞蚓，返回給Server層的記錄數(shù)量會顯著減少滤港，Server層不需要再過濾，直接回表查詢，整體效率將會提高很多溅漾。
總的來說山叮，ICP主要在引擎層增加了條件過濾能力，減少了引擎層向Server層傳輸?shù)臄?shù)據(jù)量添履。
2. ICP索引下推的適用條件
- 查詢走索引屁倔，explain中的訪問方式為range，ref暮胧，eq_ref锐借，ref_or_null，并且需要回表查詢往衷。
- ICP可用于InnoDB钞翔、MyISAM及分區(qū)表。
- 對于InnoDB表席舍，ICP只適用于走二級索引的查詢布轿。
- ICP不支持在虛擬列上創(chuàng)建的二級索引。
- 如果where條件涉及子查詢来颤，則不能使用ICP汰扭。
- 如果where條件使用函數(shù)，不能使用ICP福铅。
- 觸發(fā)器條件也不能使用ICP萝毛。
如果一個SQL使用了ICP優(yōu)化，那么在Explain的輸出中本讥，其Extra列會顯示 Using index condition珊泳。
3. ICP索引下推舉例
表結(jié)構(gòu)：
CREATE TABLE `people` (
`zipcode` varchar(50) DEFAULT NULL,
`lastname` varchar(50) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT '0',
KEY `idx_all` (`zipcode`,`lastname`,`address`)
);
select age from people where zipcode='0000005' and lastname like '%1%' and address like '%1%';
這個SQL走了二級索引 idx_all，并且需要回表查詢拷沸，所以滿足ICP優(yōu)化的條件色查，從explain的結(jié)果來看，Extra字段也確實(shí)是Using index condition撞芍，如下：
mysql> explain select age from people where zipcode='0000005' and lastname like '%xx%' and address like '%xx%';
+----+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+------+---------------+---------+---------+-------+------+----------+-----------------------+
| 1 | ref | idx_all | idx_all | 153 | const | 1 | 50.00 | Using index condition |
+----+------+---------------+---------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
ICP優(yōu)化效果：
- 表中記錄總數(shù)：393216
- 滿足條件【zipcode='0000005'】的記錄數(shù)：32768
- 滿足條件【zipcode='0000005' and lastname like '%1%' and address like '%1%'】的記錄數(shù)：15000
執(zhí)行耗時：
- 未使用ICP：1.07秒
- 使用ICP：0.62秒
從以上執(zhí)行耗時來看秧了，ICP優(yōu)化效果還是不錯的，當(dāng)然不同的數(shù)據(jù)分布對優(yōu)化效果也會有一定的影響序无，有興趣的可以自己測試一下验毡。
