Mysql索引的使用-組合索引+跳躍條件
關於MYSQL組合索引的使用，官方對下面的例子的說法是可以使用索引：
KEY（key_part1,key_part2,key_part3）
select .... from table where key_part1='xxx' and key_part3='yyy';
從MYSQL的執行計劃看，確實也是使用索引;
但在實際的最佳化過程中，我們只是簡單的關注是否使用了這個索引是不夠的。
[@more@]我們需要關注的是:
對key_part3這個關鍵字過濾的時候，是否用到了索引？
下面我們來建立一個例子：
CREATE TABLE `im_message_201001_12` (
`msg_id` bigint(20) NOT NULL default '0',
`time` datetime NOT NULL,
`owner` varchar(64) collate latin1_bin NOT NULL,
`other` varchar(64) collate latin1_bin NOT NULL,
`content` varchar(8000) collate latin1_bin default NULL,
PRIMARY KEY (`msg_id`),
KEY `im_msg_own_oth_tim_ind` (`owner`,`other`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
查詢語句:
select count(distinct concat('ab',content)) dis ,count(*) all from im_message_201001_12
where
owner='huaniaoyuchong83'
and time between '2010-01-01 00:00:00' and '2010-02-01 00:00:00' ;
我們看到，查詢的條件，對索引來說是跳躍的。
這對ORACLE來說並不是難事。SQL最佳化器會在索引裡完成對time欄位的過濾。
用HINT：/*+INDEX_SS(TABLE INDEX_NAME)*/ 可以來輔助。
但對MYSQL來說，你可能並不知道，是什麼時候對time欄位進行過濾的。
當然我們希望是透過索引來過濾TIME欄位。這樣最後回表的次數就會少一些。
在測試過程中，我們透過觀察MYSQL的Innodb_buffer_pool_read_requests（邏輯讀）變數的變化，來推測結果。
注意以下查詢過程中，條件time的變化，以及變數Innodb_buffer_pool_read_requests的變化
#######測試環境：
OS：RHEL 4.7 X86_64
MYSQL 5.0.51a / 5.1.40
請在開始下面測試前，執行：
select count(distinct concat('c',content)),count(*) from im_message_201001_11 where owner='huaniaoyuchong83' ；
以讓所有結果都在CACHE裡；
#######開始第一次測試
show session status like 'Innodb_buffer_pool_read_requests';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 136566076 |
+----------------------------------+-----------+
1 row in set (0.02 sec)
select count(distinct concat('c',content)),count(*) from im_message_201001_11 where owner='huaniaoyuchong83' and time between '2010-01-01 00:00:00' and '2010-02-01 00:00:00' ;
+-------------------------------------+----------+
| count(distinct concat('c',content)) | count(*) |
+-------------------------------------+----------+
| 35644 | 44397 |
+-------------------------------------+----------+
1 row in set (1.40 sec)
show session status like 'Innodb_buffer_pool_read_requests';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 136742193 |
+----------------------------------+-----------+
1 row in set (0.02 sec)
select 136742193-136566076 ;
+---------------------+
| 136742193-136566076 |
+---------------------+
| 176117 |
+---------------------+
1 row in set (0.00 sec)
#######開始第二次測試
show session status like 'Innodb_buffer_pool_read_requests';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 136742194 |
+----------------------------------+-----------+
1 row in set (0.02 sec)
select count(distinct concat('c',content)),count(*) from im_message_201001_11 where owner='huaniaoyuchong83' and time between '2010-01-01 00:00:00' and '2010-01-05 00:00:00' ;
+-------------------------------------+----------+
| count(distinct concat('c',content)) | count(*) |
+-------------------------------------+----------+
| 3679 | 4097 |
+-------------------------------------+----------+
1 row in set (0.74 sec)
show session status like 'Innodb_buffer_pool_read_requests';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 136916032 |
+----------------------------------+-----------+
1 row in set (0.01 sec)
select 136916032-136742194;
+---------------------+
| 136916032-136742194 |
+---------------------+
| 173838 |
+---------------------+
1 row in set (0.00 sec)
#######開始第三次測試
show session status like 'Innodb_buffer_pool_read_requests';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 136916033 |
+----------------------------------+-----------+
1 row in set (0.01 sec)
select count(distinct concat('c',content)),count(*) from im_message_201001_11 where owner='huaniaoyuchong83' and time between '2010-01-01 00:00:00' and '2010-01-01 00:00:00' ;
+-------------------------------------+----------+
| count(distinct concat('c',content)) | count(*) |
+-------------------------------------+----------+
| 0 | 0 |
+-------------------------------------+----------+
1 row in set (0.85 sec)
show session status like 'Innodb_buffer_pool_read_requests';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 137086323 |
+----------------------------------+-----------+
1 row in set (0.01 sec)
select 137086323-136916033;
+---------------------+
| 137086323-136916033 |
+---------------------+
| 170290 |
+---------------------+
1 row in set (0.00 sec)
#######開始第四次測試
show session status like 'Innodb_buffer_pool_read_requests';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 137086324 |
+----------------------------------+-----------+
1 row in set (0.02 sec)
select count(*) from im_message_201001_11 where owner='huaniaoyuchong83' and time between '2010-01-01 00:00:00' and '2010-02-01 00:00:00' ;
+----------+
| count(*) |
+----------+
| 44397 |
+----------+
1 row in set (0.05 sec)
show session status like 'Innodb_buffer_pool_read_requests';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 137092204 |
+----------------------------------+-----------+
1 row in set (0.01 sec)
select 137092204-137086324 ;
+---------------------+
| 137092204-137086324 |
+---------------------+
| 5880 |
+---------------------+
1 row in set (0.00 sec)
#######開始第五次測試
show session status like 'Innodb_buffer_pool_read_requests';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 137092205 |
+----------------------------------+-----------+
1 row in set (0.01 sec)
select count(*) from im_message_201001_11 where owner='huaniaoyuchong83' ;
+----------+
| count(*) |
+----------+
| 44397 |
+----------+
1 row in set (0.04 sec)
show session status like 'Innodb_buffer_pool_read_requests';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 137098085 |
+----------------------------------+-----------+
1 row in set (0.01 sec)
select 137098085-137092205 ;
+---------------------+
| 137098085-137092205 |
+---------------------+
| 5880 |
+---------------------+
1 row in set (0.00 sec)
#######開始第六次測試
show session status like 'Innodb_buffer_pool_read_requests';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 137098131 |
+----------------------------------+-----------+
1 row in set (0.02 sec)
select count(*) from im_message_201001_11 where owner='huaniaoyuchong83' and time between '2010-01-01 00:00:00' and '2010-01-05 00:00:00' ;
+----------+
| count(*) |
+----------+
| 4097 |
+----------+
1 row in set (0.05 sec)
show session status like 'Innodb_buffer_pool_read_requests';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 137104011 |
+----------------------------------+-----------+
1 row in set (0.01 sec)
select 137104011-137098131;
+---------------------+
| 137104011-137098131 |
+---------------------+
| 5880 |
+---------------------+
1 row in set (0.00 sec)
####### 分析結果
前三次查詢，從索引檢索後需要回表：
time 結果行數 邏輯讀
30天 44397 176117
5天 4097 173838
1天 0 170290
後三次查詢，從索引檢索後不需要回表
time 結果行數 邏輯讀
30天 44397 5880
無time條件 44397 5880
5天 4097 5880
從資料來看，
select count(*) 這樣的查詢，有或者沒有time條件，邏輯讀是一樣，都不用回表。
這裡也說明這種情況MYSQL是用索引進行time欄位的過濾。
select count(distinct concat('c',content)),count(*), 這樣的查詢，用到了索引以外的欄位，是必需回表的。
但透過邏輯讀發現，不管查詢結果是多少行，邏輯讀都差不多，在17W左右。
特別是結果行為0時，如果是透過索引過濾time，那麼邏輯讀應該接近5900,而不是17W。
這也說明，這種情況下，MYSQL沒有使用索引來對TIME欄位進行過濾;
所以MYSQL對相同WHERE條件的查詢，還採用了不同的最佳化程式；但MS這個最佳化有點問題。
對這樣的索引，需要最佳化，可以。 調整索引順序(`owner`,`time`,`other`)。
但是這僅僅是對一個SQL的最佳化。
你還要考慮到系統裡還有很多其他類似的SQL需要用到這個索引。 所以在最佳化時，需要評估所有的SQL。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/703656/viewspace-1031577/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Mysql索引的使用 - 組合索引 + 範圍條件的處理MySql索引
- mysql組合索引，abc索引命中MySql索引
- mysql索引合併:一條sql可以使用多個索引MySql索引
- MySQL單列索引和組合索引的區別MySql索引
- MySQL 組合索引不生效MySql索引
- MySQL學習筆記：組合索引-索引下推MySql筆記索引
- 【原創】MySQL 模擬條件索引MySql索引
- ORACLE 組合索引 使用分析Oracle索引
- MySQL單列索引和組合索引的區別介紹MySql索引
- MySQL索引條件下推的簡單測試MySql索引
- 索引與null（二）：組合索引索引Null
- Oracle優化－索引原理[注意索引跳躍式掃描!Oracle優化索引
- 【原創】MySQL 模擬條件索引薦MySql索引
- 【索引】反向索引--條件 範圍查詢索引
- 跳躍式索引(Skip Scan Index)的淺析索引Index
- Oracle中組合索引的使用詳解Oracle索引
- 【索引】反向索引--條件 範圍查詢（二）索引
- Percona MySQL 5.6 WHERE 條件中 OR 的索引測試MySql索引
- mysql 索引合併MySql索引
- 【Mysql】InnoDB 中的聚簇索引、二級索引、聯合索引MySql索引
- MySQL組合索引和最左匹配原則MySql索引
- 跳過索引某些列任然使用索引的特性索引
- 跳躍式索引(Skip Scan Index)淺析 - 轉索引Index
- 對線面試官：Mysql組合索引的生效規則面試MySql索引
- MySQL學習筆記：組合索引-最左原則MySql筆記索引
- MYSQL INNODB 組合索引分支節點資料解析MySql索引
- MySQL複合索引探究MySql索引
- MySQL建立複合索引MySql索引
- 聊聊Mysql索引和redis跳錶MySql索引Redis
- SQLServer索引優化（3）：對於建組合索引的要求SQLServer索引優化
- 跳躍式索引掃描(index skip scan) [final]索引Index
- 組合索引的選擇原則索引
- Mysql索引使用MySql索引
- 使用組合索引處理包含空值的查詢索引
- mysql (ICP) 索引條件下推對比ORACLE進行說明MySql索引Oracle
- 各種索引型別發生的條件索引型別
- mysql聯合索引的選擇性MySql索引
- 謂詞條件是is null走索引嗎？Null索引