MySQL Index Condition Pushdown(ICP)性能優(yōu)化方法實例
一 概念介紹
Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特性,是一種在存儲引擎層使用索引過濾數(shù)據(jù)的一種優(yōu)化方式。
a 當關(guān)閉ICP時,index 僅僅是data access 的一種訪問方式，存儲引擎通過索引回表獲取的數(shù)據(jù)會傳遞到MySQL Server 層進行where條件過濾。
b 當打開ICP時,如果部分where條件能使用索引中的字段,MySQL Server 會把這部分下推到引擎層,可以利用index過濾的where條件在存儲引擎層進行數(shù)據(jù)過濾,而非將所有通過index access的結(jié)果傳遞到MySQL server層進行where過濾.
優(yōu)化效果:ICP能減少引擎層訪問基表的次數(shù)和MySQL Server 訪問存儲引擎的次數(shù),減少io次數(shù)，提高查詢語句性能。
二 原理
Index Condition Pushdown is not used:
1 Get the next row, first by reading the index tuple, and then by using the index tuple to locate and read the full table row.
2 Test the part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.
Index Condition Pushdown is used
1 Get the next row s index tuple (but not the full table row).
2 Test the part of the WHERE condition that applies to this table and can be checked using only index columns.
If the condition is not satisfied, proceed to the index tuple for the next row.
3 If the condition is satisfied, use the index tuple to locate and read the full table row.
4 est the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.
三 實踐案例
a 環(huán)境準備
數(shù)據(jù)庫版本 5.6.16
關(guān)閉緩存
set query_cache_size=0;
set query_cache_type=OFF;
測試數(shù)據(jù)下載地址
b 當開啟ICP時
mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from employees where first_name='Anneke' and last_name like '%sig' ;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------+
| 1 | 0.00060275 | select * from employees where first_name='Anneke' and last_name like '%sig' |
+----------+------------+--------------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
此時情況下根據(jù)MySQL的最左前綴原則, first_name 可以使用索引，last_name采用了like 模糊查詢，不能使用索引。
c 關(guān)閉ICP
mysql> set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from employees where first_name='Anneke' and last_name like '%sig' ;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
mysql> SET profiling = 0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------+
| 2 | 0.00097000 | select * from employees where first_name='Anneke' and last_name like '%sig' |
+----------+------------+--------------------------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)
當開啟ICP時 查詢在sending data環(huán)節(jié)時間消耗是 0.000189s
mysql> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000094 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000025 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000044 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000021 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000093 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000024 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000189 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)
當關(guān)閉ICP時 查詢在sending data環(huán)節(jié)時間消耗是 0.000735s
mysql> show profile cpu,block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000045 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000024 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000049 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000735 | 0.001000 | 0.000000 | 0 | 0 |
| end | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000023 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)
從上面的profile 可以看出ICP 開啟時整個sql 執(zhí)行時間是未開啟的2/3，sending data 環(huán)節(jié)的時間消耗前者僅是后者的1/4。
ICP 開啟時的執(zhí)行計劃 含有 Using index condition 標示 ，表示優(yōu)化器使用了ICP對數(shù)據(jù)訪問進行優(yōu)化。
mysql> explain select * from employees where first_name='Anneke' and last_name like '%nta' ;
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | employees | ref | idx_emp_fnln | idx_emp_fnln | 44 | const | 224 | Using index condition |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
ICP 關(guān)閉時的執(zhí)行計劃顯示use where.
mysql> explain select * from employees where first_name='Anneke' and last_name like '%nta' ;
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
| 1 | SIMPLE | employees | ref | idx_emp_fnln | idx_emp_fnln | 44 | const | 224 | Using where |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
案例分析
以上面的查詢?yōu)槔P(guān)閉ICP 時,存儲引擎通前綴index first_name 訪問表中225條first_name 為Anneke的數(shù)據(jù),并在MySQL server層根據(jù)last_name like '%sig' 進行過濾
開啟ICP 時，last_name 的like '%sig'條件可以通過索引字段last_name 進行過濾，在存儲引擎內(nèi)部通過與where條件的對比，直接過濾掉不符合條件的數(shù)據(jù)。該過程不回表,只訪問符合條件的1條記錄并返回給MySQL Server ,有效的減少了io訪問和各層之間的交互。
ICP 關(guān)閉時 ，僅僅使用索引作為訪問數(shù)據(jù)的方式。
ICP 開啟時 ，MySQL將在存儲引擎層 利用索引過濾數(shù)據(jù)，減少不必要的回表，注意 虛線的using where 表示如果where條件中含有沒有被索引的字段，則還是要經(jīng)過MySQL Server 層過濾。
四 ICP的使用限制
1 當sql需要全表訪問時,ICP的優(yōu)化策略可用于range, ref, eq_ref, ref_or_null 類型的訪問數(shù)據(jù)方法 。
2 支持InnoDB和MyISAM表。
3 ICP只能用于二級索引，不能用于主索引。
4 并非全部where條件都可以用ICP篩選。
如果where條件的字段不在索引列中,還是要讀取整表的記錄到server端做where過濾。
5 ICP的加速效果取決于在存儲引擎內(nèi)通過ICP篩選掉的數(shù)據(jù)的比例。
6 5.6 版本的不支持分表的ICP 功能，5.7 版本的開始支持。
7 當sql 使用覆蓋索引時，不支持ICP 優(yōu)化方法。
mysql> explain select * from employees where first_name='Anneke' and last_name='Porenta' ;
+----+-------------+-----------+------+---------------+--------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+--------------+---------+-------------+------+-----------------------+
| 1 | SIMPLE | employees | ref | idx_emp_fnln | idx_emp_fnln | 94 | const,const | 1 | Using index condition |
+----+-------------+-----------+------+---------------+--------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select first_name,last_name from employees where first_name='Anneke' and last_name='Porenta' ;
+----+-------------+-----------+------+---------------+--------------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+--------------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | employees | ref | idx_emp_fnln | idx_emp_fnln | 94 | const,const | 1 | Using where; Using index |
+----+-------------+-----------+------+---------------+--------------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)
相關(guān)文章
這篇文章主要介紹了解讀數(shù)據(jù)庫的嵌套查詢的性能問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-03-03
SQL HAVING子句在GROUP BY中的條件篩選靈活運用
這篇文章主要為大家介紹了SQL HAVING子句在GROUP BY中的條件篩選靈活運用示例詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪2023-11-11
在MySQL數(shù)據(jù)庫操作中,運算符扮演著較為重要的角色,連接表達式中的各個操作數(shù),其作用是用來指明對操作數(shù)所進行的運算,下面這篇文章主要給大家介紹了關(guān)于MySQL基本查詢和運算符的相關(guān)資料,需要的朋友可以參考下2022-08-08
下面小編就為大家?guī)硪黄贛ysql的Sequence實現(xiàn)方法。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-09-09
Mysql報Table?'mysql.user'?doesn't?exist問題的解
這篇文章主要給大家介紹了關(guān)于Mysql報Table?'mysql.user'?doesn't?exist問題的解決方法,初學(xué)者可能會遇到這個問題,文中通過圖文將解決方法介紹的非常詳細,需要的朋友可以參考下2022-05-05
有的小伙伴發(fā)現(xiàn)自己的mysql無法正常連接遠程服務(wù)器,下面這篇文章主要給大家介紹了關(guān)于MySQL遠程無法連接的一些常見原因,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2022-09-09
MySQL安裝與創(chuàng)建用戶操作(新手入門指南)
這篇文章主要為大家介紹了MySQL安裝與創(chuàng)建用戶的使用講解是非常適合小白新手的入門學(xué)習(xí)，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2023-05-05
這篇文章主要給大家分享的是mysql事務(wù)解析，事務(wù)(transaction)是業(yè)務(wù)邏輯的一個基本的單元組成，下面文章圍繞mysql事務(wù)的相關(guān)資料展開詳細內(nèi)容,需要的朋友可以參考一下希望對大家有所幫助2022-01-01
優(yōu)化是一項復(fù)雜的任務(wù),因為它最終需要對整個系統(tǒng)的理解,下面這篇文章主要給大家總結(jié)介紹了關(guān)于Mysql常見的慢查詢優(yōu)化方式,文中介紹的非常詳細,需要的朋友可以參考下2023-05-05
這篇文章主要介紹了MySQL索引詳解及演進過程以及延申出面試題，索引在關(guān)系型數(shù)據(jù)庫中，是一種單獨的、物理的對數(shù)據(jù)庫表中的一列或者多列值進行排序的一種存儲結(jié)構(gòu)2022-07-07