MySQL Index Condition Pushdown(ICP)性能優(yōu)化方法實(shí)例
一 概念介紹
Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特性,是一種在存儲(chǔ)引擎層使用索引過濾數(shù)據(jù)的一種優(yōu)化方式。
a 當(dāng)關(guān)閉ICP時(shí),index 僅僅是data access 的一種訪問方式，存儲(chǔ)引擎通過索引回表獲取的數(shù)據(jù)會(huì)傳遞到MySQL Server 層進(jìn)行where條件過濾。
b 當(dāng)打開ICP時(shí),如果部分where條件能使用索引中的字段,MySQL Server 會(huì)把這部分下推到引擎層,可以利用index過濾的where條件在存儲(chǔ)引擎層進(jìn)行數(shù)據(jù)過濾,而非將所有通過index access的結(jié)果傳遞到MySQL server層進(jìn)行where過濾.
優(yōu)化效果:ICP能減少引擎層訪問基表的次數(shù)和MySQL Server 訪問存儲(chǔ)引擎的次數(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.
三 實(shí)踐案例
a 環(huán)境準(zhǔn)備
數(shù)據(jù)庫版本 5.6.16
關(guān)閉緩存
set query_cache_size=0;
set query_cache_type=OFF;
測(cè)試數(shù)據(jù)下載地址
b 當(dāng)開啟ICP時(shí)
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)
此時(shí)情況下根據(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)
當(dāng)開啟ICP時(shí) 查詢?cè)趕ending data環(huán)節(jié)時(shí)間消耗是 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)
當(dāng)關(guān)閉ICP時(shí) 查詢?cè)趕ending data環(huán)節(jié)時(shí)間消耗是 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 開啟時(shí)整個(gè)sql 執(zhí)行時(shí)間是未開啟的2/3，sending data 環(huán)節(jié)的時(shí)間消耗前者僅是后者的1/4。
ICP 開啟時(shí)的執(zhí)行計(jì)劃 含有 Using index condition 標(biāo)示 ，表示優(yōu)化器使用了ICP對(duì)數(shù)據(jù)訪問進(jìn)行優(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)閉時(shí)的執(zhí)行計(jì)劃顯示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 時(shí),存儲(chǔ)引擎通前綴index first_name 訪問表中225條first_name 為Anneke的數(shù)據(jù),并在MySQL server層根據(jù)last_name like '%sig' 進(jìn)行過濾
開啟ICP 時(shí)，last_name 的like '%sig'條件可以通過索引字段last_name 進(jìn)行過濾，在存儲(chǔ)引擎內(nèi)部通過與where條件的對(duì)比，直接過濾掉不符合條件的數(shù)據(jù)。該過程不回表,只訪問符合條件的1條記錄并返回給MySQL Server ,有效的減少了io訪問和各層之間的交互。
ICP 關(guān)閉時(shí) ，僅僅使用索引作為訪問數(shù)據(jù)的方式。
ICP 開啟時(shí) ，MySQL將在存儲(chǔ)引擎層 利用索引過濾數(shù)據(jù)，減少不必要的回表，注意 虛線的using where 表示如果where條件中含有沒有被索引的字段，則還是要經(jīng)過MySQL Server 層過濾。
四 ICP的使用限制
1 當(dāng)sql需要全表訪問時(shí),ICP的優(yōu)化策略可用于range, ref, eq_ref, ref_or_null 類型的訪問數(shù)據(jù)方法 。
2 支持InnoDB和MyISAM表。
3 ICP只能用于二級(jí)索引，不能用于主索引。
4 并非全部where條件都可以用ICP篩選。
如果where條件的字段不在索引列中,還是要讀取整表的記錄到server端做where過濾。
5 ICP的加速效果取決于在存儲(chǔ)引擎內(nèi)通過ICP篩選掉的數(shù)據(jù)的比例。
6 5.6 版本的不支持分表的ICP 功能，5.7 版本的開始支持。
7 當(dāng)sql 使用覆蓋索引時(shí)，不支持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)文章
Mysql保持現(xiàn)有內(nèi)容在后面增加內(nèi)容的sql語句
這篇文章主要介紹了Mysql保持現(xiàn)有內(nèi)容在后面增加內(nèi)容的sql語句,需要的朋友可以參考下2017-05-05
MySQL中通過EXPLAIN如何分析SQL的執(zhí)行計(jì)劃詳解
這篇文章主要給大家介紹了關(guān)于MySQL中通過EXPLAIN如何分析SQL的執(zhí)行計(jì)劃的相關(guān)資料，文中通過圖文以及示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的安康學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-08-08
Mysql 本地計(jì)算機(jī)無法啟動(dòng) mysql 服務(wù) 錯(cuò)誤 1067：進(jìn)程意外終止。
初學(xué)php接觸mysql，遇到一些問題，卸載重裝后，無法啟動(dòng)mysql服務(wù)，網(wǎng)絡(luò)上有很多種說法，我這里將我解決這個(gè)問題的辦法提出2009-12-12
Undo日志也叫做回滾日志,是MySQL數(shù)據(jù)庫當(dāng)中一種重要的日志,用于記錄更新操作之前的數(shù)據(jù)狀態(tài),這篇文章主要給大家介紹了關(guān)于如何通過一篇文章帶你了解MySQL之undo日志的相關(guān)資料,需要的朋友可以參考下2023-06-06
這篇文章主要為大家詳細(xì)介紹了如何使用本地MySql實(shí)現(xiàn)一把分布式鎖,以及Mysql實(shí)現(xiàn)分布式鎖的原理是怎么樣的,有需要的小伙伴可以了解下2024-11-11
Node-Red實(shí)現(xiàn)MySQL數(shù)據(jù)庫連接的方法
這篇文章主要介紹了Node-Red實(shí)現(xiàn)MySQL數(shù)據(jù)庫連接的方法,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-08-08
MySQL外鍵約束(FOREIGN KEY)是表的一個(gè)特殊字段,經(jīng)常與主鍵約束一起使用,下面這篇文章主要給給大家介紹了關(guān)于MySQL外鍵約束(Foreign?Key)的相關(guān)資料,需要的朋友可以參考下2022-06-06
增量備份是在全備或上次增量備份基礎(chǔ)上,只備份新增或修改的文件,減少數(shù)據(jù)量和時(shí)間,binlog記錄數(shù)據(jù)庫變更,重啟時(shí)創(chuàng)建新日志文件,增量備份復(fù)雜但減輕服務(wù)器負(fù)擔(dān),而binlog幫助精確恢復(fù)數(shù)據(jù)2023-09-09
使用mysql workbench自動(dòng)生成ER圖的實(shí)現(xiàn)步驟
MySQL Workbench是一款專為MySQL設(shè)計(jì)的ER/數(shù)據(jù)庫建模工具,它是著名的數(shù)據(jù)庫設(shè)計(jì)工具DBDesigne4的繼任者,可以通過MySQL Workbench設(shè)計(jì)和創(chuàng)建新的數(shù)據(jù)庫圖示,本文給大家介紹了使用mysql workbench自動(dòng)生成ER圖的實(shí)現(xiàn)步驟,需要的朋友可以參考下2024-06-06
批量替換 MySQL 指定字段中的字符串是數(shù)據(jù)庫應(yīng)用中很常見的需求，但是有很多初學(xué)者在遇到這種需求時(shí)，通常都是用腳本來實(shí)現(xiàn)；其實(shí)，MySQL 內(nèi)置的有批量替換語法，效率也會(huì)高很多；想了解具體方法，繼續(xù)閱讀本文吧 :)2009-09-09