MySQL索引與IndexConditionPushdown

來源：懂視網(wǎng) 責編：小采時間：2020-11-09 13:02:16

MySQL索引與IndexConditionPushdown

MySQL索引與IndexConditionPushdown:大約在兩年前，我寫了一篇關(guān)于MySQL索引的文章。最近有同學在文章的評論中對文章的內(nèi)容提出質(zhì)疑，質(zhì)疑主要集中在聯(lián)合索引的使用方式上。在那篇文章中，我說明聯(lián)合索引是將各個索引字段做字符串連接后作為key，使用時將整體做前綴匹配。而這名同學在這個頁面

推薦度：

點擊下載本文 文檔為doc格式

導(dǎo)讀MySQL索引與IndexConditionPushdown:大約在兩年前，我寫了一篇關(guān)于MySQL索引的文章。最近有同學在文章的評論中對文章的內(nèi)容提出質(zhì)疑，質(zhì)疑主要集中在聯(lián)合索引的使用方式上。在那篇文章中，我說明聯(lián)合索引是將各個索引字段做字符串連接后作為key，使用時將整體做前綴匹配。而這名同學在這個頁面

大約在兩年前，我寫了一篇關(guān)于MySQL索引的文章。最近有同學在文章的評論中對文章的內(nèi)容提出質(zhì)疑，質(zhì)疑主要集中在聯(lián)合索引的使用方式上。在那篇文章中，我說明聯(lián)合索引是將各個索引字段做字符串連接后作為key，使用時將整體做前綴匹配。而這名同學在這個頁面

大約在兩年前，我寫了一篇關(guān)于MySQL索引的文章。最近有同學在文章的評論中對文章的內(nèi)容提出質(zhì)疑，質(zhì)疑主要集中在聯(lián)合索引的使用方式上。在那篇文章中，我說明聯(lián)合索引是將各個索引字段做字符串連接后作為key，使用時將整體做前綴匹配。

而這名同學在這個頁面找到了如下一句話：index condition pushdown is usually useful with multi-column indexes: the first component(s) is what index access is done for, the subsequent have columns that we read and check conditions on。從而認為聯(lián)合索引的使用方式與文中不符。

實際上，這個頁面所講述的是在MariaDB 5.3.3（MySQL是在5.6）開始引入的一種叫做Index Condition Pushdown（以下簡稱ICP）的查詢優(yōu)化方式。由于本身不是一個層面的東西，前文中說的是Index Access，而這里是Query Optimization，所以并不構(gòu)成對前文正確性的影響。在寫前文時，MySQL還沒有ICP，所以文中沒有涉及相關(guān)內(nèi)容，但考慮到新版本的MariaDB或MySQL中ICP的啟用確實影響了一些查詢行為的外在表現(xiàn)。所以決定寫這篇文章詳細講述一下ICP的原理以及對索引使用方式的優(yōu)化。

實驗

先從一個簡單的實驗開始直觀認識ICP的作用。

安裝數(shù)據(jù)庫

首先需要安裝一個支持ICP的MariaDB或MySQL數(shù)據(jù)庫。我使用的是MariaDB 5.5.34，如果是使用MySQL則需要5.6版本以上。

Mac環(huán)境下可以通過brew安裝：

brew install mairadb

其它環(huán)境下的安裝請參考MariaDB官網(wǎng)關(guān)于下載安裝的文檔。

導(dǎo)入示例數(shù)據(jù)

與前文一樣，我們使用Employees Sample Database，作為示例數(shù)據(jù)庫。完整示例數(shù)據(jù)庫的下載地址為：https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2。

將下載的壓縮包解壓后，會看到一系列的文件，其中employees.sql就是導(dǎo)入數(shù)據(jù)的命令文件。執(zhí)行

mysql -h[host] -u[user] -p < employees.sql

就可以完成建庫、建表和load數(shù)據(jù)等一系列操作。此時數(shù)據(jù)庫中會多一個叫做employees的數(shù)據(jù)庫。庫中的表如下：

MariaDB [employees]> SHOW TABLES;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments |
| dept_emp |
| dept_manager |
| employees |
| salaries |
| titles |
+---------------------+
6 rows in set (0.00 sec)

我們將使用employees表做實驗。

建立聯(lián)合索引

employees表包含雇員的基本信息，表結(jié)構(gòu)如下：

MariaDB [employees]> DESC employees.employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

這個表默認只有一個主索引，因為ICP只能作用于二級索引，所以我們建立一個二級索引：

ALTER TABLE employees.employees ADD INDEX first_name_last_name (first_name, last_name);

這樣就建立了一個first_name和last_name的聯(lián)合索引。

查詢

為了明確看到查詢性能，我們啟用profiling并關(guān)閉query cache：

SET profiling = 1;
SET query_cache_type = 0;
SET GLOBAL query_cache_size = 0;

然后我們看下面這個查詢：

MariaDB [employees]> SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 254642 | 1959-01-17 | Mary | Botman | M | 1989-11-24 |
| 471495 | 1960-09-24 | Mary | Dymetman | M | 1988-06-09 |
| 211941 | 1962-08-11 | Mary | Hofman | M | 1993-12-30 |
| 217707 | 1962-09-05 | Mary | Lichtman | F | 1987-11-20 |
| 486361 | 1957-10-15 | Mary | Oberman | M | 1988-09-06 |
| 457469 | 1959-07-15 | Mary | Weedman | M | 1996-11-21 |
+--------+------------+------------+-----------+--------+------------+

根據(jù)MySQL索引的前綴匹配原則，兩者對索引的使用是一致的，即只有first_name采用索引，last_name由于使用了模糊前綴，沒法使用索引進行匹配。我將查詢聯(lián)系執(zhí)行三次，結(jié)果如下：

+----------+------------+---------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------+
| 38 | 0.00084400 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
| 39 | 0.00071800 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
| 40 | 0.00089600 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
+----------+------------+---------------------------------------------------------------------------+

然后我們關(guān)閉ICP：

SET optimizer_switch='index_condition_pushdown=off';

在運行三次相同的查詢，結(jié)果如下：

+----------+------------+---------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------+
| 42 | 0.00264400 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
| 43 | 0.01418900 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
| 44 | 0.00234200 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
+----------+------------+---------------------------------------------------------------------------+

有意思的事情發(fā)生了，關(guān)閉ICP后，同樣的查詢，耗時是之前的三倍以上。下面我們用explain看看兩者有什么區(qū)別：

MariaDB [employees]> EXPLAIN SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man';
+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | employees | ref | first_name_last_name | first_name_last_name | 44 | const | 224 | Using index condition |
+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

MariaDB [employees]> EXPLAIN SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man';
+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-------------+
| 1 | SIMPLE | employees | ref | first_name_last_name | first_name_last_name | 44 | const | 224 | Using where |
+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

前者是開啟ICP，后者是關(guān)閉ICP。可以看到區(qū)別在于Extra，開啟ICP時，用的是Using index condition；關(guān)閉ICP時，是Using where。

其中Using index condition就是ICP提高查詢性能的關(guān)鍵。下一節(jié)說明ICP提高查詢性能的原理。

原理

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

以上面的查詢?yōu)槔跊]有ICP時，首先通過索引前綴從存儲引擎中讀出224條first_name為Mary的記錄，然后在server段用where篩選last_name的like條件；而啟用ICP后，由于last_name的like篩選可以通過索引字段進行，那么存儲引擎內(nèi)部通過索引與where條件的對比來篩選掉不符合where條件的記錄，這個過程不需要讀出整條記錄，同時只返回給server篩選后的6條記錄，因此提高了查詢性能。

下面通過圖兩種查詢的原理詳細解釋。