【Mysql】MySQL · 特性分析 · Index Condition Pushdown (ICP)
原文地址:
前言
提過,我們在之後的文章中會從 optimizer 的選項出發,系統的介紹 optimizer 的各個變數,包括變數的原理、作用以及原始碼實現等,然後再進一步的介紹最佳化器的工作過程(SQL 語句扁平化處理、索引選擇、代價計算、多表連線順序選擇以及物理執行等內容),本期我們先看一下眾所周知的 ICP,官方文件請參考這裡。
ICP 測試
首先,我們們來看一下開啟 ICP 與關閉 ICP 之間的效能區別,以下是測試過程:
準備資料:
create table icp(id int, age int, name varchar(30), memo varchar(600)) engine=innodb; alter table icp add index aind(age, name, memo); --let $i= 100000 while ($i)
{ --eval insert into icp values($i, 1, 'a$i', repeat('a$i', 100)) --dec $i }
PS: MySQL 有一個叫profile的東東,可以用來監視 SQL 語句在各個階段的執行情況,我們們可以使用這個工具來觀察 SQL 語句在各個階段的執行情況,關於 profile 的詳細說明可以參考官方文件。
開啟 ICP 的效能測試:
set profiling=on; set optimizer_switch="index_condition_pushdown=on”; (default enabled) mysql> select * from icp where age = 999 and name like '%999%'; +------+------+------+------+ | id | age | name | memo | +------+------+------+------+ | NULL | 999 | 999 | 999 | +------+------+------+------+ 1 row in set (0.00 sec) mysql> explain select * from icp where age = 999 and name like '%999%'; +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ | 1 | SIMPLE | icp | ref | aind | aind | 5 | const | 1 | Using index condition | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) mysql> show profiles; +----------+------------+-----------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------------------------+ | 1 | 0.00043550 | select * from icp where age = 999 and name like '%999%' | | 2 | 0.00043250 | explain select * from icp where age = 999 and name like '%999%' | +----------+------------+-----------------------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) mysql> show profile cpu,block io for query 2; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000084 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000064 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000046 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000082 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | | explaining | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000031 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000013 | 0.000000 | 0.000000 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+ 13 rows in set, 1 warning (0.00 sec) mysql> show session status like '%handler%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 2 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 4 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 1 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 42 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 39 | +----------------------------+-------+ 18 rows in set (0.00 sec)
關閉 ICP 的效能測試:
mysql> set optimizer_switch="index_condition_pushdown=off”; mysql> select * from icp where age = 1 and memo like '%9999%'; mysql> select * from icp where age = 999 and name like '%999%'; +------+------+------+------+ | id | age | name | memo | +------+------+------+------+ | NULL | 999 | 999 | 999 | +------+------+------+------+ 1 row in set (0.00 sec) mysql> explain select * from icp where age = 999 and name like '%999%'; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | icp | ref | aind | aind | 5 | const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> show profiles; +----------+------------+-----------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------------------------+ | 1 | 0.00043550 | select * from icp where age = 999 and name like '%999%' | | 2 | 0.00043250 | explain select * from icp where age = 999 and name like '%999%' | | 3 | 0.00081350 | show session status like '%handler%' | | 4 | 0.00010350 | set optimizer_switch="index_condition_pushdown=off" | | 5 | 0.00036525 | select * from icp where age = 999 and name like '%999%' | | 6 | 0.00032950 | explain select * from icp where age = 999 and name like '%999%' | +----------+------------+-----------------------------------------------------------------+ 6 rows in set, 1 warning (0.00 sec) mysql> show profile cpu,block io for query 5; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000068 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000032 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000088 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000049 | 0.000000 | 0.000000 | 0 | 0 | | end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000024 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+ 15 rows in set, 1 warning (0.00 sec) mysql> show session status like '%handler%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 4 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 8 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 2 | | Handler_read_last | 0 | | Handler_read_next | 2 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 58 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 54 | +----------------------------+-------+ 18 rows in set (0.00 sec)
測試結論:由以上測試情況可以看到,在二級索引是複合索引且前面的條件過濾性較低的情況下,開啟 ICP 可以有效的降低 server 層和 engine 層之間互動的次數,從而有效的降低在執行時間。
ICP 原理
5.6 之前,在 SQL 語句的執行過程中,server 層透過 engine 的 api 獲取資料,然後再進行 where_cond 的判斷(具體判斷邏輯在: evaluate_join_record),每一條資料都需要從engine層返回server層做判斷。我們回顧一下上面把 ICP 關掉的測試,可以看到 Handler_read_next 的值陡增,其原因是第 1 個欄位區分度不高,且 memo 欄位無法使用索引,造成了類似 index 掃描的的情況,效能較低。
5.6 之後,在利用索引掃描的過程中,如果發現 where_cond 中含有這個 index 相關的條件,則將此條件記錄在 handler 介面中,在索引掃描的過程中,只有滿足索引與handler介面的條件時,才會返回到 server 層做進一步的處理,在字首索引區分度不夠,其它欄位區分度高的情況下可以有效的減少 server & engine之間的開銷,提升查詢效能。
ICP 原始碼實現
我們在上小節提到,index condition down 所用的條件是記在handler介面中的,我們們分析一下“記錄”的過程是如何實現的。
首先,最佳化器計算代價後會生成一個 JOIN_TAB 的左支樹,每一個 JOIN_TAB 包含相關表的指標、表的讀取方式、訪問表所包含的索引等資訊,最佳化器會在make_join_readinfo 中對JOIN_TAB中表的訪問方式進行相應的修正,並進一步將 where cond 中和索引相關的條件記錄到 table 的控制程式碼中,堆疊如下:
#0 make_cond_for_index (cond=0x2b69680179e8, table=0x2b6968012100, keyno=0, other_tbls_ok=true)
#1 in push_index_cond (tab=0x2b696802aa48, keyno=0, other_tbls_ok=true, trace_obj=0x2b696413ec30)
#2 in make_join_readinfo (join=0x2b6968017db0, options=0, no_jbuf_after=4294967295)
#3 in JOIN::optimize (this=0x2b6968017db0)
#4 in mysql_execute_select (thd=0x3176760, select_lex=0x3179470, free_join=true)
其次, make_cond_for_index 是一個遞迴的過程,對 where_cond中的每一個條件進行判斷,對滿足條件的 cond 重新組合成一個新的cond,最後將新的 cond 掛在table->file 下面(table->file 指的是操作物理表的介面函式,此變數為thd下私有的,不共享,共享的是tab->table->s),詳細參考make_cond_for_index 的詳細實現,設定的堆疊如下:
#0 ha_innobase::idx_cond_push (this=0x2b696800e810, keyno=0, idx_cond=0x2b69680179e8)
#1 0x0000000000a60a55 in push_index_cond (tab=0x2b696802aa48, keyno=0, other_tbls_ok=true, trace_obj=0x2b696413ec30)
#2 0x0000000000a6362f in make_join_readinfo (join=0x2b6968017db0, options=0, no_jbuf_after=4294967295)
#3 0x0000000000d9b8bd in JOIN::optimize (this=0x2b6968017db0 #4 0x0000000000a5b9ae in mysql_execute_select (thd=0x3176760, select_lex=0x3179470, free_join=true)
再次,server 層根據生成的 JOIN_TAB 讀取engine層的內容,在engine讀取的時候,會進行index_condition_pushdown的呼叫,即 ICP 的呼叫,堆疊如下:
#0 Item_func_like::val_int (this=0x2b6978005a28)
#1 0x0000000001187b66 in innobase_index_cond (file=0x2b696800e810)
#2 0x0000000001393566 in row_search_idx_cond_check (mysql_rec=0x2b69680129f0 <incomplete sequence="" \361>, prebuilt=0x2b69680130f8, rec=0x2b692b56e4cf "\200", offsets=0x2b697008d450)
#3 0x0000000001397e2b in row_search_for_mysql (buf=0x2b69680129f0 <incomplete sequence="" \361>, mode=2, prebuilt=0x2b69680130f8, match_mode=1, direction=0)
#4 0x00000000011696b9 in ha_innobase::index_read (this=0x2b696800e810, buf=0x2b69680129f0 <incomplete sequence="" \361>, key_ptr=0x2b697800a660 "", key_len=5, find_flag=HA_READ_KEY_EXACT)
#5 0x00000000006ecc58 in handler::index_read_map (this=0x2b696800e810, buf=0x2b69680129f0 <incomplete sequence="" \361>, key=0x2b697800a660 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
#6 0x00000000006d6bb4 in handler::ha_index_read_map (this=0x2b696800e810, buf=0x2b69680129f0 <incomplete sequence="" \361>, key=0x2b697800a660 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
#7 0x00000000009a1870 in join_read_always_key (tab=0x2b697800a1b8)
#8 0x000000000099d480 in sub_select (join=0x2b6978005df0, join_tab=0x2b697800a1b8, end_of_records=false)
#9 0x000000000099c6c0 in do_select (join=0x2b6978005df0)
#10 0x00000000009980a4 in JOIN::exec (this=0x2b6978005df0)
#11 0x0000000000a5bac0 in mysql_execute_select (thd=0x32801a0, select_lex=0x3282eb0, free_join=true)
可見在 ICP 的判斷是呼叫相關item的函式的,雖然同是呼叫 server 層的函式,但是沒有 ICP 的呼叫需要根據主建找到記錄,然後再匹配,而有了 ICP 可以省略一次主鍵查詢資料的過程,進而提升效率。
ICP 使用限制及問題
- 只支援 select 語句;
- 5.6 中只支援 MyISAM 與 InnoDB 引擎;
- ICP的最佳化策略可用於range、ref、eq_ref、ref_or_null 型別的訪問資料方法;
- 不支援主建索引的 ICP;
- 當 SQL 使用覆蓋索引時但只檢索部分資料時,ICP 無法使用,詳細的分析可以參考 中 Olav Sandst?的分析,程式碼實現部分可以參考 make_join_readinfo;
-
在查詢的時候即使正確的使用索引的前N個欄位(即遵循字首索引的原則),還是會用到 ICP,無故的多了 ICP 相關的判斷,這應該是一個退化的問題,例:
mysql> explain select * from icp where age = 999 and name like '999%'; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | icp | range | aind | aind | 98 | NULL | 1 | Using index condition | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
PS: engine condition pushdown 是 NDB 使用的,其它引擎不支援。
補充:
-
如:
02:28:07>show status like ‘Handler_read%’;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 61 |
+———————–+——-+
6 rows in set (0.41 sec)Handler_read_first 代表讀取索引頭的次數,如果這個值很高,說明全索引掃描很多。
Handler_read_key代表一個索引被使用的次數,如果我們新增加一個索引,可以檢視Handler_read_key是否有增加,如果有增加,說明sql用到索引。
Handler_read_next 代表讀取索引的下列,一般發生range scan。
Handler_read_prev 代表讀取索引的上列,一般發生在ORDER BY … DESC。
Handler_read_rnd 代表在固定位置讀取行,如果這個值很高,說明對大量結果集進行了排序、進行了全表掃描、關聯查詢沒有用到合適的KEY。
Handler_read_rnd_next 代表進行了很多表掃描,查詢效能低下。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29096438/viewspace-2085921/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL Index Condition Pushdown(ICP)的使用限制MySqlIndex
- mysql 5.6引入index condition pushdownMySqlIndex
- Index Condition Pushdown測試Index
- MySQL:關於ICP特性的說明(未完)MySql
- MySQL · 特性分析 · MySQL 5.7新特性系列一MySql
- MySQL MRR和ICP介紹MySql
- MySQL 8.0新特性-倒敘索引 desc indexMySql索引Index
- mysql 索引( mysql index )MySql索引Index
- <MYSQL Index>MySqlIndex
- 學習MYSQL之ICP、MRR、BKAMySql
- Using index condition Using indexIndex
- Mysql——index(索引)使用MySqlIndex索引
- MySQL index hints 使用MySqlIndex
- MySQL 中 一條 order by index limit 語句的分析MySqlIndexMIT
- 【Mysql】index extensions介紹MySqlIndex
- mysql的Covering IndexMySqlIndex
- MySQL • 特性分析 • 到底是誰執行了FTWLMySql
- Index of /Downloads/MySQL-5.5/IndexMySql
- MYSQL中的type:index 和 Extra:Using indexMySqlIndex
- MySQL系列-- 5. MySQL高階特性MySql
- mysql高階特性MySql
- MySQL 8.0 新特性MySql
- MySQL 5.7新特性MySql
- MySQL 8.0 新增特性MySql
- 【Mysql】mysql公開課之-mysql5.7複製特性MySql
- mysql (ICP) 索引條件下推對比ORACLE進行說明MySql索引Oracle
- MySQL·引擎特性·像NOSQL那樣使用MySQLMySql
- MySQL-18 MySQL8其他新特性MySql
- 【MySQL】Merge Index導致死鎖MySqlIndex
- mysql 函式substring_index()MySql函式Index
- mysql loose index scan的實現MySqlIndex
- mysql oder by 使用index一例MySqlIndex
- 【MySQL】MySQL5.6新特性之Batched Key AccessMySqlBAT
- 【MySQL】MySQL5.6新特性之crash-safeMySql
- 【mysql】SUBSTRING_INDEX 用法舉例MySqlIndex
- 三高Mysql - Mysql特性和未來發展MySql
- 【Mysql】Mysql5.7新特性之-json儲存MySqlJSON
- 【Mysql】mysql5.7新特性之-sys schema的作用MySql