技術分享 | SQL 最佳化：ICP 的缺陷

什麼是 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可以在執行階段提高執行效率，但是在最佳化階段並不能改善執行計劃。