欧美97色伦欧美一区二区日韩,国产福利片在线观看,freexxx性欧美vide0高清,西西亚洲,日本欧美国产精品第一页久久,成人18免费软件

當前位置： 維易PHP培訓學院 > MYSQL教程 > 內容正文

Mysql應用MySQL Index Condition Pushdown(ICP)性能優化方法實例

作者：VEPHP   時間 2017-10-22

《Mysql應用MySQL Index Condition Pushdown(ICP)性能優化方法實例》要點：
本文介紹了Mysql應用MySQL Index Condition Pushdown(ICP)性能優化方法實例，希望對您有用。如果有疑問，可以聯系我們。

一 觀點介紹MYSQL入門

Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特征,是一種在存儲引擎層使用索引過濾數據的一種優化方式.MYSQL入門

a 當關閉ICP時,index 僅僅是data access 的一種拜訪方式,存儲引擎通過索引回表獲取的數據會傳遞到MySQL Server 層進行where條件過濾.MYSQL入門

b 當打開ICP時,如果部門where條件能使用索引中的字段,MySQL Server 會把這部門下推到引擎層,可以利用index過濾的where條件在存儲引擎層進行數據過濾,而非將所有通過index access的結果傳遞到MySQL server層進行where過濾.MYSQL入門

優化效果:ICP能減少引擎層拜訪基表的次數和MySQL Server 拜訪存儲引擎的次數,減少io次數,提高查詢語句性能.MYSQL入門

二 原理MYSQL入門

Index Condition Pushdown is not used:MYSQL入門

? 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.MYSQL入門

三 理論案例MYSQL入門

a 環境準備
?? 數據庫版本 5.6.16
?? 封閉緩存
?? MYSQL入門

代碼如下:

???? set query_cache_size=0;
???? set query_cache_type=OFF;
?

?? 測試數據下載地址
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)

此時情況下根據MySQL的最左前綴原則, first_name 可以使用索引,last_name采用了like 模糊查詢,不克不及使用索引.
c 關閉ICP
MYSQL入門

代碼如下:

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環節光陰消耗是 0.000189s
MYSQL入門

代碼如下:

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)

當封閉ICP時 查詢在sending data環節時間消耗是 0.000735s
MYSQL入門

代碼如下:

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 執行時間是未開啟的2/3,sending data 環節的時間消耗前者僅是后者的1/4.
ICP 開啟時的執行計劃 含有 Using index condition 標示 ,表示優化器使用了ICP對數據拜訪進行優化.
MYSQL入門

代碼如下:

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 關閉時的執行計劃顯示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)

案例闡發MYSQL入門

以上面的查詢為例關閉ICP 時,存儲引擎通前綴index first_name 拜訪表中225條first_name 為Anneke的數據,并在MySQL server層根據last_name like '%sig' 進行過濾
開啟ICP 時,last_name 的like '%sig'條件可以通過索引字段last_name 進行過濾,在存儲引擎內部通過與where條件的對比,直接過濾掉不符合條件的數據.該過程不回表,只拜訪符合條件的1條記錄并返回給MySQL Server ,有效的減少了io拜訪和各層之間的交互.MYSQL入門

ICP 關閉時 ,僅僅使用索引作為拜訪數據的方式.MYSQL入門

Mysql應用MySQL Index Condition Pushdown(ICP)性能優化方法實例MYSQL入門

ICP 開啟時 ,MySQL將在存儲引擎層 利用索引過濾數據,減少不需要的回表,注意 虛線的using where 表示如果where條件中含有沒有被索引的字段,則還是要經過MySQL Server 層過濾.MYSQL入門

Mysql應用MySQL Index Condition Pushdown(ICP)性能優化方法實例MYSQL入門

四 ICP的使用限定 MYSQL入門

1 當sql需要全表拜訪時,ICP的優化策略可用于range, ref, eq_ref,? ref_or_null 類型的拜訪數據方法 .
2 支持InnoDB和MyISAM表.
3 ICP只能用于二級索引,不能用于主索引.
4 并非全部where條件都可以用ICP篩選.
?? 如果where條件的字段不在索引列中,還是要讀取整表的記錄到server端做where過濾.
5 ICP的加速效果取決于在存儲引擎內通過ICP篩選掉的數據的比例.
6 5.6 版本的不支持分表的ICP 功能,5.7 版本的開始支持.
7 當sql 使用覆蓋索引時,不支持ICP 優化方法.MYSQL入門

代碼以下:

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)

歡迎參與《Mysql應用MySQL Index Condition Pushdown(ICP)性能優化方法實例》討論，分享您的想法，維易PHP學院為您提供專業教程。

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/12617.html


    標簽：