MySQL Index Condition Pushdown(ICP)性能優(yōu)化方法實(shí)例_MySQL教程
推薦:MySQL order by性能優(yōu)化方法實(shí)例這篇文章主要介紹了MySQL order by性能優(yōu)化方法實(shí)例,本文講解了MySQL中order by的原理和優(yōu)化order by的三種方法,需要的朋友可以參考下 前言 工作過(guò)程中,各種業(yè)務(wù)需求在訪問(wèn)數(shù)據(jù)庫(kù)的時(shí)候要求有order by排序。有時(shí)候不必要的或者不合理的排序操作很可能導(dǎo)致數(shù)據(jù)庫(kù)系統(tǒng)崩
這篇文章主要介紹了MySQL Index Condition Pushdown(ICP)性能優(yōu)化方法實(shí)例,本文講解了概念介紹、原理、實(shí)踐案例、案例分析、ICP的使用限制等內(nèi)容,需要的朋友可以參考下
一 概念介紹
Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特性,是一種在存儲(chǔ)引擎層使用索引過(guò)濾數(shù)據(jù)的一種優(yōu)化方式。
a 當(dāng)關(guān)閉ICP時(shí),index 僅僅是data access 的一種訪問(wèn)方式,存儲(chǔ)引擎通過(guò)索引回表獲取的數(shù)據(jù)會(huì)傳遞到MySQL Server 層進(jìn)行where條件過(guò)濾。
b 當(dāng)打開(kāi)ICP時(shí),如果部分where條件能使用索引中的字段,MySQL Server 會(huì)把這部分下推到引擎層,可以利用index過(guò)濾的where條件在存儲(chǔ)引擎層進(jìn)行數(shù)據(jù)過(guò)濾,而非將所有通過(guò)index access的結(jié)果傳遞到MySQL server層進(jìn)行where過(guò)濾.
優(yōu)化效果:ICP能減少引擎層訪問(wèn)基表的次數(shù)和MySQL Server 訪問(wèn)存儲(chǔ)引擎的次數(shù),減少io次數(shù),提高查詢語(yǔ)句性能。
二 原理
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ù)庫(kù)版本 5.6.16
關(guān)閉緩存
代碼如下:
set query_cache_size=0;
set query_cache_type=OFF;
測(cè)試數(shù)據(jù)下載地址
b 當(dāng)開(kāi)啟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)開(kāi)啟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 開(kāi)啟時(shí)整個(gè)sql 執(zhí)行時(shí)間是未開(kāi)啟的2/3,sending data 環(huán)節(jié)的時(shí)間消耗前者僅是后者的1/4。
ICP 開(kāi)啟時(shí)的執(zhí)行計(jì)劃 含有 Using index condition 標(biāo)示 ,表示優(yōu)化器使用了ICP對(duì)數(shù)據(jù)訪問(wèn)進(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 訪問(wèn)表中225條first_name 為Anneke的數(shù)據(jù),并在MySQL server層根據(jù)last_name like '%sig' 進(jìn)行過(guò)濾
分享:MySQL slave_net_timeout參數(shù)解決的一個(gè)集群?jiǎn)栴}案例這篇文章主要介紹了MySQL slave_net_timeout參數(shù)解決的一個(gè)集群?jiǎn)栴}案例,問(wèn)題日志請(qǐng)見(jiàn)正文,本文使用slave_net_timeout參數(shù)解決了這個(gè)問(wèn)題,需要的朋友可以參考下 【背景】 對(duì)一套數(shù)據(jù)庫(kù)集群進(jìn)行5.5升級(jí)到5.6之后,alter.log 報(bào)warning異常。 復(fù)制代碼 代碼如下: 2015-02-
- 防止服務(wù)器宕機(jī)時(shí)MySQL數(shù)據(jù)丟失的幾種方案
- MySQL Semisynchronous Replication介紹
- MySQL延遲關(guān)聯(lián)性能優(yōu)化方法
- MySQL 5.7增強(qiáng)版Semisync Replication性能優(yōu)化
- MySQL order by性能優(yōu)化方法實(shí)例
- MySQL slave_net_timeout參數(shù)解決的一個(gè)集群?jiǎn)栴}案例
- 使用innodb_force_recovery解決MySQL崩潰無(wú)法重啟問(wèn)題
- MySQL replace into 語(yǔ)句淺析(二)
- MySQL replace into 語(yǔ)句淺析(一)
- MySQL定期自動(dòng)刪除表
- MySQL中的CONCAT函數(shù)使用教程
- MySQL中的RAND()函數(shù)使用詳解
MySQL教程Rss訂閱編程教程搜索
MySQL教程推薦
- 淺析一個(gè)MYSQL語(yǔ)法(在查詢中使用count)的兼容性問(wèn)題
- 將MySQL的臨時(shí)目錄建立在內(nèi)存中的教程
- 防止服務(wù)器宕機(jī)時(shí)MySQL數(shù)據(jù)丟失的幾種方案
- MySQL order by性能優(yōu)化方法實(shí)例
- MySQL筆記之字符串函數(shù)的應(yīng)用
- MYSQL的導(dǎo)入導(dǎo)出與還原備份
- Mysql select in 按id排序?qū)崿F(xiàn)方法
- 去掉mysql連接時(shí)報(bào)警聲音的方法
- 通過(guò)frm&ibd 恢復(fù) Mysql ibdata 丟失或損壞的數(shù)據(jù)教程
- Ubuntu Server下MySql數(shù)據(jù)庫(kù)備份腳本代碼
猜你也喜歡看這些
- 清理SQL Server 2008日志文件Cannot shrink log file 2 的解決方案
- 如何掌握SQL Server的鎖機(jī)制和鎖模式
- 如何在SAN上創(chuàng)建SQL Server群集
- SQL Server查詢語(yǔ)句的使用
- 解讀SQL Server與Access語(yǔ)法小差異
- 如何查看并導(dǎo)出數(shù)據(jù)表中字段的注釋信息
- Microsoft SQL Server 2008安裝圖解教程(Windows 7)
- 高手是怎樣煉成的:精妙SQL語(yǔ)句介紹
- 如何使用SQL Server 2008升級(jí)顧問(wèn)
- 解讀SQL Server游標(biāo)使用實(shí)例
- 相關(guān)鏈接:
復(fù)制本頁(yè)鏈接| 搜索MySQL Index Condition Pushdown(ICP)性能優(yōu)化方法實(shí)例
- 教程說(shuō)明:
MySQL教程-MySQL Index Condition Pushdown(ICP)性能優(yōu)化方法實(shí)例。