什么是 ICP（Index Condition Pushdown）

比如有這樣一個索引idx_test(birth_date,first_name,hire_date)
查詢語句select * from employees where birth_date >= '1957-05-23' and birth_date <='1960-06-01' and hire_date>'1998-03-22';
的執行過程：

2. 查找出來的10萬行數據包含 hire_date 字段，MySQL 會把 hire_date >'1998-03-22'
 這個條件下推到存儲引擎，進一步篩選數據，假設還剩1000行；

實驗

還是上面那個例子，創建一個組合索引：

alter table employees add index idx_test(birth_date,first_name,hire_date);

執行下面這個SQL：

SELECT *
FROM employees
WHERE birth_date >= '1957-05-23'
    AND birth_date <= '1960-06-01'
    AND hire_date > '1998-03-22';

執行計劃如下：

mysql [localhost:5735] {msandbox} (employees) > explain select * from employees where birth_date >= '1957-05-23' and birth_date <='1960-06-01' and hire_date>'1998-03-22';
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_test      | NULL | NULL    | NULL | 298980 |    15.74 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+

可以看到并沒有使用 idx_test
索引，但如果加 hint 強制走 idx_test
索引，我們知道可以使用 ICP，執行計劃如下：

mysql [localhost:5735] {msandbox} (employees) > explain select * from employees force index(idx_test) where birth_date >= '1957-05-23' and birth_date <='1960-06-01' and hire_date>'1998-03-22';
+----+-------------+-----------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key      | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_test      | idx_test | 3       | NULL | 141192 |    33.33 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

• 全表掃描需要掃描 300024 行，執行時間 0.15 秒

• 走 idx_test 索引需要掃描 141192 行（Rows_examined: 1065 是個 bug，這顯然不是掃描行數，掃描行數我們可以從執行計劃看出，在這個例子中執行計劃里的 rows 是真實的掃描行數，不是估算值，這個知識點不影響理解本文）。因為沒有其他條件，從返回結果行數我們也能知道回表次數就是 1065，執行時間只要 0.037 秒

# Time: 2022-11-24T18:02:01.001734+08:00
# Query_time: 0.146939  Lock_time: 0.000850 Rows_sent: 1065  Rows_examined: 300024
SET timestamp=1669284095;
select * from employees where birth_date >= '1957-05-23' and birth_date <='1960-06-01' and hire_date>'1998-03-22';
# Time: 2022-11-24T18:01:09.001223+08:00
# Query_time: 0.037211  Lock_time: 0.001649 Rows_sent: 1065  Rows_examined: 1065
SET timestamp=1669284032;
select * from employees force index(idx_test) where birth_date >= '1957-05-23' and birth_date <='1960-06-01' and hire_date>'1998-03-22';

很顯然走 idx_test 索引比全表掃描效率更高，那為什么優化器不選擇走 idx_test 索引呢？一個不會犯錯的說法是優化器有它的算法，并不以人類認為的時間快慢為標準來進行選擇。這次我們打破砂鍋問到底，優化器的算法是什么？

成本計算

讀取以及檢測記錄是否滿足對應的搜索條件、對結果集進行排序等這些操作損耗的時間稱之為CPU成本。

對于InnoDB存儲引擎來說，頁是磁盤和內存之間交互的基本單位，MySQL5.7 中規定讀取一個頁面花費的成本默認是1.0，讀取以及檢測一條記錄是否符合搜索條件的成本默認是0.2。1.0、0.2這些數字稱之為成本常數（不同版本可能不一樣，可以通過 mysql.server_cost、mysql.engine_cost 查看）。

• IO成本：929*1 = 929 （929 是主鍵索引的頁數，通過表的統計信息中的 Data_length/pagesize 得到）

• CPU 成本：298980*0.2 = 59796（298980是掃描行數，全表掃描時這是一個估算值，也就是表的統計信息中的 Rows）
• 總成本 = IO成本 + CPU 成本 = 929 + 59796 = 60725

mysql [localhost:5735] {msandbox} (employees) > explain format=json select * from employees  where birth_date >= '1957-05-23' and birth_date <='1960-06-01' and hire_date>'1998-03-22'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "60725.00"
    },
    "table": {
      "table_name": "employees",
      "access_type": "ALL",
      "possible_keys": [
        "idx_test"
      ],
      "rows_examined_per_scan": 298980,
      "rows_produced_per_join": 47059,
      "filtered": "15.74",
      "cost_info": {
        "read_cost": "51313.14",
        "eval_cost": "9411.86",
        "prefix_cost": "60725.00",
        "data_read_per_join": "6M"
      },
      "used_columns": [
        "emp_no",
        "birth_date",
        "first_name",
        "last_name",
        "gender",
        "hire_date"
      ],
      "attached_condition": "((`employees`.`employees`.`birth_date` >= '1957-05-23') and (`employees`.`employees`.`birth_date` <= '1960-06-01') and (`employees`.`employees`.`hire_date` > '1998-03-22'))"
    }
  }
}
1 row in set, 1 warning (0.00 sec)

• 訪問 idx_test 索引的成本：

• IO 成本=1*1=1（優化器認為讀取索引的一個范圍區間的I/O成本和讀取一個頁面是相同的，而條件中只有 birth_date >= '1957-05-23' and birth_date <='1960-06-01' 這一個范圍）

• CPU 成本 = 141192*0.2 = 28238.4（掃描行數 "rows_examined_per_scan": 141192）

• 回表的成本（不會考慮索引條件下推的作用，因此回表次數等于索引掃描行數）：

• 回表 IO 成本 = 141192*1 = 141192
• 回表 CPU 成本 = 141192*0.2 = 28238.4
• 總成本：1+28238.4+141192+28238.4=197669.8

mysql [localhost:5735] {msandbox} (employees) > explain format=json select * from employees force index(idx_test) where birth_date >= '1957-05-23' and birth_date <='1960-06-01' and hire_date>'1998-03-22'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "197669.81"
    },
    "table": {
      "table_name": "employees",
      "access_type": "range",
      "possible_keys": [
        "idx_test"
      ],
      "key": "idx_test",
      "used_key_parts": [
        "birth_date"
      ],
      "key_length": "3",
      "rows_examined_per_scan": 141192,
      "rows_produced_per_join": 47059,
      "filtered": "33.33",
      "index_condition": "((`employees`.`employees`.`birth_date` >= '1957-05-23') and (`employees`.`employees`.`birth_date` <= '1960-06-01') and (`employees`.`employees`.`hire_date` > '1998-03-22'))",
      "cost_info": {
        "read_cost": "188257.95",
        "eval_cost": "9411.86",
        "prefix_cost": "197669.81",
        "data_read_per_join": "6M"
      },
      "used_columns": [
        "emp_no",
        "birth_date",
        "first_name",
        "last_name",
        "gender",
        "hire_date"
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)

結論

• IO成本：1065*1 = 1065

• CPU成本：1065*0.2 = 213

但是優化器在計算回表成本時，顯然沒有考慮 ICP，直接將掃描索引的行數 141192 當作了回表的次數，所以得到的回表成本巨大，總成本遠遠大于全表掃描的成本。

因此，我們可以得到的結論是：ICP可以在執行階段提高執行效率，但是在優化階段并不能改善執行計劃。