MySQL 優化器 Index Condition Pushdown下推（ICP）

ICP 測試

準備數據

CREATE TABLE `icp` (`employee_id` int(6) NOT NULL AUTO_INCREMENT,`first_name` varchar(20) DEFAULT NULL,`last_name` varchar(25) DEFAULT NULL,`email` varchar(25) DEFAULT NULL,`phone_number` varchar(20) DEFAULT NULL,PRIMARY KEY (`employee_id`)
);insert  into `icp`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`) values (100,'Steven','K_ing','SKING','515.123.4567'),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568'),(102,'Lex','De Haan','LDEHAAN','515.123.4569'),(103,'Mary','Hunold','AHUNOLD','590.423.4567'),(104,'Mary','Ernst','BERNST','590.423.4568'),(105,'Mary','Austin','DAUSTIN','590.423.4569'),(106,'Valli','Pataballa','VPATABAL','590.423.4560'),(107,'Diana','Lorentz','DLORENTZ','590.423.5567'),(108,'Nancy','Greenberg','NGREENBE','515.124.4569'),(109,'Daniel','Faviet','DFAVIET','515.124.4169'),(110,'Mary','Chen','JCHEN','515.124.4269'),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369'),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469'),(113,'Mary','Popp','LPOPP','515.124.4567'),(114,'Den','Raphaely','DRAPHEAL','515.127.4561'),(115,'Alexander','Khoo','AKHOO','515.127.4562'),(116,'Mary','Lichtman','SBAIDA','515.127.4563'),(117,'Mary','Tobias','STOBIAS','515.127.4564'),(118,'Mary','Oberman','GHIMURO','515.127.4565'),(119,'Karen','Colmenares','KCOLMENA','515.127.4566'),(120,'Matthew','Weiss','MWEISS','650.123.1234'),(121,'Adam','Fripp','AFRIPP','650.123.2234'),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234'),(123,'Mary','Vollman','SVOLLMAN','650.123.4234'),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234'),(125,'Julia','Nayer','JNAYER','650.124.1214'),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224'),(127,'Mary','Landry','JLANDRY','650.124.1334'),(128,'Mary','Markle','SMARKLE','650.124.1434'),(129,'Mary','Bissot','LBISSOT','650.124.5234'),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234'),(131,'Mary','Marlow','JAMRLOW','650.124.7234'),(132,'TJ','Olson','TJOLSON','650.124.8234'),(133,'Mary','Mallin','JMALLIN','650.127.1934'),(134,'Michael','Rogers','MROGERS','650.127.1834'),(135,'Ki','Gee','KGEE','650.127.1734'),(136,'Mary','Philtanker','HPHILTAN','650.127.1634'),(137,'Renske','Ladwig','RLADWIG','650.121.1234'),(138,'Stephen','Stiles','SSTILES','650.121.2034'),(139,'Mary','Seo','JSEO','650.121.2019'),(140,'Mary','Hofman','JPATEL','650.121.1834'),(141,'Trenna','Rajs','TRAJS','650.121.8009'),(142,'Curtis','Davies','CDAVIES','650.121.2994'),(143,'Mary','Matos','RMATOS','650.121.2874'),(144,'Mary','Vargas','PVARGAS','650.121.2004'),(145,'John','Russell','JRUSSEL','011.44.1344.429268'),(146,'Karen','Partners','KPARTNER','011.44.1344.467268'),(147,'Mary','Errazuriz','AERRAZUR','011.44.1344.429278'),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268'),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018'),(150,'Mary','Weedman','PTUCKER','011.44.1344.129268'),(151,'Mary','Bernstein','DBERNSTE','011.44.1344.345268'),(152,'Peter','Hall','PHALL','011.44.1344.478968'),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718'),(154,'Mary','Cambrault','NCAMBRAU','011.44.1344.987668'),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508'),(156,'Mary','K_ing','JKING','011.44.1345.429268'),(157,'Mary','Sully','PSULLY','011.44.1345.929268'),(158,'Mary','Dymetman','AMCEWEN','011.44.1345.829268'),(159,'Mary','Smith','LSMITH','011.44.1345.729268'),(160,'Mary','Doran','LDORAN','011.44.1345.629268'),(161,'Mary','Sewall','SSEWALL','011.44.1345.529268'),(162,'Mary','Vishney','CVISHNEY','011.44.1346.129268'),(163,'Mary','Greene','DGREENE','011.44.1346.229268'),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268'),(165,'David','Lee','DLEE','011.44.1346.529268'),(166,'Mary','Ande','SANDE','011.44.1346.629268'),(167,'Mary','Banda','ABANDA','011.44.1346.729268'),(168,'Lisa','Ozer','LOZER','011.44.1343.929268'),(169,'Mary','Bloom','HBLOOM','011.44.1343.829268'),(170,'Tayler','Fox','TFOX','011.44.1343.729268'),(171,'Mary','Mary','WSMITH','011.44.1343.629268'),(172,'Mary','Bates','EBATES','011.44.1343.529268'),(173,'Mary','Kumar','SKUMAR','011.44.1343.329268'),(174,'Ellen','Abel','EABEL','011.44.1644.429267'),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266'),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265'),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264'),(178,'Mary','Grant','KGRANT','011.44.1644.429263'),(179,'Mary','Johnson','CJOHNSON','011.44.1644.429262'),(180,'Mary','Taylor','WTAYLOR','650.507.9876'),(181,'Mary','Fleaur','JFLEAUR','650.507.9877'),(182,'Mary','Sullivan','MSULLIVA','650.507.9878'),(183,'Girard','Geoni','GGEONI','650.507.9879'),(184,'Mary','Sarchand','NSARCHAN','650.509.1876'),(185,'Mary','Bull','ABULL','650.509.2876'),(186,'Mary','Botman','JDELLING','650.509.3876'),(187,'Mary','Cabrio','ACABRIO','650.509.4876'),(188,'Kelly','Chung','KCHUNG','650.505.1876'),(189,'Jennifer','Dilly','JDILLY','650.505.2876'),(190,'Mary','Gates','TGATES','650.505.3876'),(191,'Randall','Perkins','RPERKINS','650.505.4876'),(192,'Sarah','Bell','SBELL','650.501.1876'),(193,'Mary','Everett','BEVERETT','650.501.2876'),(194,'Mary','McCain','SMCCAIN','650.501.3876'),(195,'Vance','Jones','VJONES','650.501.4876'),(196,'Alana','Walsh','AWALSH','650.507.9811'),(197,'Mary','Feeney','KFEENEY','650.507.9822'),(198,'Mary','OConnell','DOCONNEL','650.507.9833'),(199,'Mary','Grant','DGRANT','650.507.9844'),(200,'Mary','Whalen','JWHALEN','515.123.4444'),(201,'Mary','Hartstein','MHARTSTE','515.123.5555'),(202,'Pat','Fay','PFAY','603.123.6666'),(203,'Susan','Mavris','SMAVRIS','515.123.7777'),(204,'Mary','Baer','HBAER','515.123.8888'),(205,'Mary','Higgins','SHIGGINS','515.123.8080'),(206,'William','Gietz','WGIETZ','515.123.8181');mysql> desc employees;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| employee_id  | int         | NO   | PRI | NULL    | auto_increment |
| first_name   | varchar(20) | YES  | MUL | NULL    |                |
| last_name    | varchar(25) | YES  |     | NULL    |                |
| email        | varchar(25) | YES  |     | NULL    |                |
| phone_number | varchar(20) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

ICP 只能作用于二級索引，所以需要建立一個二級索引。執行下述命令建立 first_name 和 last_name 的聯合索引：

mysql> alter table employees add index first_name_last_name (first_name, last_name);

打開 ICP 的性能測試

為了明確看到查詢性能，我們啟用 profiling：

mysql> set profiling = 1;
mysql> select * from employees where first_name='Mary' and last_name like '%man';
mysql> select * from employees where first_name='Mary' and last_name like '%man';
mysql> select * from employees where first_name='Mary' and last_name like '%man';
mysql> show profiles;
+----------+------------+---------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                     |
+----------+------------+---------------------------------------------------------------------------+
|        1 | 0.00165975 | select * from employees where first_name='Mary' and last_name like '%man' |
|        2 | 0.00162125 | select * from employees where first_name='Mary' and last_name like '%man' |
|        3 | 0.00164050 | select * from employees where first_name='Mary' and last_name like '%man' |
+----------+------------+---------------------------------------------------------------------------+

注意：mysql 默認開啟 ICP 機制

PS：MySQL 有一個叫 profile 的東東，可以用來監視 SQL 語句在各個階段的執行情況，咱們可以使用這個工具來觀察 SQL 語句在各個階段的運行情況，關于 profile 的詳細說明可以參考官方文檔。

上述執行中查詢語句中只有 first_name 采用索引，last_name 由于使用了模糊查詢，沒法使用索引進行匹配。

關閉 ICP 性能測試

mysql> set optimizer_switch='index_condition_pushdown=off';
mysql> set profiling = 1;
mysql> select * from employees where first_name='Mary' and last_name like '%man';
mysql> select * from employees where first_name='Mary' and last_name like '%man';
mysql> select * from employees where first_name='Mary' and last_name like '%man';
mysql> show profiles;
+----------+------------+---------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                     |
+----------+------------+---------------------------------------------------------------------------+
|        1 | 0.00317075 | select * from employees where first_name='Mary' and last_name like '%man' |
|        2 | 0.00316175 | select * from employees where first_name='Mary' and last_name like '%man' |
|        3 | 0.00316075 | select * from employees where first_name='Mary' and last_name like '%man' |
+----------+------------+---------------------------------------------------------------------------+

結果對比

從上述結果可以看出，打開 ICP 機制時，隨機執行三次查詢，花費時間都在 0.0016...

而關閉 ICP 機制后，隨機執行三次查詢，花費時間都在 0.0031...，約是打開 ICP 機制時的 2 倍

此外，通過 explain 命令也可查看 ICP 是否打開，執行如下命令：

mysql> explain select * from employees where first_name='Mary' and last_name like '%man';

情況 1

+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | first_name_last_name | first_name_last_name | 83      | const |   56 |    11.11 | Using index condition |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+

情況 2

+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ref  | first_name_last_name | first_name_last_name | 83      | const |   56 |    11.11 | Using where |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+

情況 1 中的 Extra 列顯示 Using index condition 代表 ICP 機制已打開；

情況 2 中的 Extra 列顯示 Using where 代表 ICP 機制已關閉；

ICP原理

5.6 之前，在 SQL 語句的執行過程中，server 層通過 engine 的 api 獲取數據，然后再進行 where_cond 的判斷（具體判斷邏輯在:?evaluate_join_record），每一條數據都需要從 engine 層返回 server 層做判斷。5.6 之后，在利用索引掃描的過程中，如果發現 where_cond 中含有這個 index 相關的條件，則將此條件記錄在 handler 接口中，在索引掃描的過程中，只有滿足索引與 handler 接口的條件時，才會返回到 server 層做進一步的處理，在前綴索引區分度不夠，其它字段區分度高的情況下可以有效的減少 server & engine之間的開銷，提升查詢性能。

舉例說明：

mysql> select * from employees where first_name='Mary';
+-------------+------------+------------+----------+--------------------+
| employee_id | first_name | last_name  | email    | phone_number       |
+-------------+------------+------------+----------+--------------------+
|         166 | Mary       | Ande       | SANDE    | 011.44.1346.629268 |
|         105 | Mary       | Austin     | DAUSTIN  | 590.423.4569       |
|         204 | Mary       | Baer       | HBAER    | 515.123.8888       |
|         167 | Mary       | Banda      | ABANDA   | 011.44.1346.729268 |
|         172 | Mary       | Bates      | EBATES   | 011.44.1343.529268 |
|         151 | Mary       | Bernstein  | DBERNSTE | 011.44.1344.345268 |
|         129 | Mary       | Bissot     | LBISSOT  | 650.124.5234       |
|         169 | Mary       | Bloom      | HBLOOM   | 011.44.1343.829268 |
|         186 | Mary       | Botman     | JDELLING | 650.509.3876       |
|         185 | Mary       | Bull       | ABULL    | 650.509.2876       |
|         187 | Mary       | Cabrio     | ACABRIO  | 650.509.4876       |
|         154 | Mary       | Cambrault  | NCAMBRAU | 011.44.1344.987668 |
|         110 | Mary       | Chen       | JCHEN    | 515.124.4269       |
|         160 | Mary       | Doran      | LDORAN   | 011.44.1345.629268 |
|         158 | Mary       | Dymetman   | AMCEWEN  | 011.44.1345.829268 |
|         104 | Mary       | Ernst      | BERNST   | 590.423.4568       |
|         147 | Mary       | Errazuriz  | AERRAZUR | 011.44.1344.429278 |
|         193 | Mary       | Everett    | BEVERETT | 650.501.2876       |
|         197 | Mary       | Feeney     | KFEENEY  | 650.507.9822       |
|         181 | Mary       | Fleaur     | JFLEAUR  | 650.507.9877       |
|         190 | Mary       | Gates      | TGATES   | 650.505.3876       |
|         178 | Mary       | Grant      | KGRANT   | 011.44.1644.429263 |
|         199 | Mary       | Grant      | DGRANT   | 650.507.9844       |
|         163 | Mary       | Greene     | DGREENE  | 011.44.1346.229268 |
|         201 | Mary       | Hartstein  | MHARTSTE | 515.123.5555       |
|         205 | Mary       | Higgins    | SHIGGINS | 515.123.8080       |
|         140 | Mary       | Hofman     | JPATEL   | 650.121.1834       |
|         103 | Mary       | Hunold     | AHUNOLD  | 590.423.4567       |
|         179 | Mary       | Johnson    | CJOHNSON | 011.44.1644.429262 |
|         173 | Mary       | Kumar      | SKUMAR   | 011.44.1343.329268 |
|         156 | Mary       | K_ing      | JKING    | 011.44.1345.429268 |
|         127 | Mary       | Landry     | JLANDRY  | 650.124.1334       |
|         116 | Mary       | Lichtman   | SBAIDA   | 515.127.4563       |
|         133 | Mary       | Mallin     | JMALLIN  | 650.127.1934       |
|         128 | Mary       | Markle     | SMARKLE  | 650.124.1434       |
|         131 | Mary       | Marlow     | JAMRLOW  | 650.124.7234       |
|         171 | Mary       | Mary       | WSMITH   | 011.44.1343.629268 |
|         143 | Mary       | Matos      | RMATOS   | 650.121.2874       |
|         194 | Mary       | McCain     | SMCCAIN  | 650.501.3876       |
|         118 | Mary       | Oberman    | GHIMURO  | 515.127.4565       |
|         198 | Mary       | OConnell   | DOCONNEL | 650.507.9833       |
|         136 | Mary       | Philtanker | HPHILTAN | 650.127.1634       |
|         113 | Mary       | Popp       | LPOPP    | 515.124.4567       |
|         184 | Mary       | Sarchand   | NSARCHAN | 650.509.1876       |
|         139 | Mary       | Seo        | JSEO     | 650.121.2019       |
|         161 | Mary       | Sewall     | SSEWALL  | 011.44.1345.529268 |
|         159 | Mary       | Smith      | LSMITH   | 011.44.1345.729268 |
|         182 | Mary       | Sullivan   | MSULLIVA | 650.507.9878       |
|         157 | Mary       | Sully      | PSULLY   | 011.44.1345.929268 |
|         180 | Mary       | Taylor     | WTAYLOR  | 650.507.9876       |
|         117 | Mary       | Tobias     | STOBIAS  | 515.127.4564       |
|         144 | Mary       | Vargas     | PVARGAS  | 650.121.2004       |
|         162 | Mary       | Vishney    | CVISHNEY | 011.44.1346.129268 |
|         123 | Mary       | Vollman    | SVOLLMAN | 650.123.4234       |
|         150 | Mary       | Weedman    | PTUCKER  | 011.44.1344.129268 |
|         200 | Mary       | Whalen     | JWHALEN  | 515.123.4444       |
+-------------+------------+------------+----------+--------------------+
56 rows in set (0.01 sec)

ICP的原理簡單說來就是將可以利用索引篩選的 where 條件在存儲引擎一側進行篩選，而不是將所有 index access 的結果取出放在 server 端進行 where 篩選。

以上面的查詢為例，在沒有 ICP 時，首先通過索引前綴從存儲引擎中讀出 56 條 first_name 為 Mary 的記錄，然后在 server 端用 where 篩選 last_name 的 like 條件（回表操作）；而啟用 ICP 后，會繼續在 56 條數據上繼續使用索引對 last_name 進行匹配，篩選掉不符合 where 條件的記錄，這個過程不需要讀出整條記錄，同時只返回給 server 篩選后的 6 條記錄，因此提高了查詢性能。

ICP 使用場景

上述是關于 Index Condition Pushdown（ICP）優化的適用條件的說明。讓我們逐一解釋每個場景，并提供相應的示例：

1）ICP 適用于range,?ref,?eq_ref, and?ref_or_null?訪問方法，當需要訪問完整的表行時。

示例：考慮一個包含 "users" 表的數據庫，其中有一個名為 "age" 的索引。我們想要查詢年齡大于等于 18 歲且性別為男性的用戶的信息。

SELECT * FROM users WHERE age >= 18 AND gender = 'Male';

在這種情況下，ICP 可以應用于 "age" 索引和 "gender" 索引，以減少訪問完整表行的次數。

2.）ICP 可以用于?InnoDB?and?MyISAM?表，包括分區的 InnoDB 和 MyISAM 表。

示例：對于 InnoDB 表和 MyISAM 表，ICP 都可以應用。例如，在具有 InnoDB 存儲引擎的數據庫中，ICP 可以用于減少對輔助索引的完整行讀取次數。

3）對于 InnoDB 表，ICP 僅適用于輔助索引。ICP 的目標是減少完整行讀取的次數，從而減少 I/O 操作。對于 InnoDB 聚集索引，完整的記錄已經被讀入 InnoDB 緩沖區。在這種情況下使用 ICP 不會減少 I/O。

示例：考慮一個包含 "orders" 表的數據庫，其中有一個聚集索引（主鍵索引）和一個輔助索引。我們想要查詢購買了特定產品的訂單信息。

SELECT * FROM orders WHERE product_id = '123';

在這種情況下，ICP 可以應用于輔助索引，以減少對完整行的讀取次數。

4）ICP 不支持在虛擬生成列上創建的輔助索引。InnoDB 支持對虛擬生成列創建輔助索引。

示例：考慮一個包含 "users" 表的數據庫，其中有一個名為 "full_name" 的虛擬生成列。我們想要查詢具有特定全名的用戶信息。

SELECT * FROM users WHERE full_name = 'John Doe';

在這種情況下，ICP 無法應用于虛擬生成列上的輔助索引。

5）無法下推涉及子查詢的條件。

示例：考慮一個包含 "users" 和 "orders" 兩個表的數據庫。我們想要查詢購買了特定產品的用戶的信息。

SELECT * FROM users WHERE user_id IN (SELECT user_id FROM orders WHERE product_id = '123');

在這種情況下，涉及子查詢的條件無法被下推。

6）無法下推涉及存儲函數的條件。存儲引擎無法調用存儲函數。

示例：考慮一個包含 "users" 表的數據庫，其中有一個名為 "get_age()" 的存儲函數。我們想要查詢年齡大于等于 18 歲的用戶的信息。

SELECT * FROM users WHERE get_age() >= 18;

在這種情況下，涉及存儲函數的條件無法被下推。

7）無法下推觸發器中的條件。

示例：考慮一個包含觸發器的數據庫，當插入或更新 "users" 表時觸發。我們想要查詢滿足特定條件的用戶的信息。

SELECT * FROM users WHERE condition = 'some_condition';

在這種情況下，觸發器中的條件無法被下推。

8）（適用于 MySQL 8.0.30 及更高版本）無法將條件下推到包含對系統變量的引用的派生表。

示例：考慮一個包含派生表的查詢，其中引用了系統變量。

SELECT * FROM (SELECT * FROM users WHERE age >= @min_age) AS derived_table;

在這種情況下，條件無法被下推到引用了系統變量的派生表。

總結：ICP 是一種針對 MySQL 的查詢優化技術，具有一些適用條件和限制。它可以減少對完整表行的訪問次數，從而提升查詢性能。然而，需要注意的是，在特定的場景和條件下，ICP 可能無法應用。