Mysql索引的使用-組合索引+跳躍條件
關(guān)于MySQL組合索引的使用，官方對(duì)下面的例子的說(shuō)法是可以使用索引：
讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來(lái)自于我們對(duì)這個(gè)行業(yè)的熱愛(ài)。我們立志把好的技術(shù)通過(guò)有效、簡(jiǎn)單的方式提供給客戶，將通過(guò)不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：域名注冊(cè)、網(wǎng)絡(luò)空間、營(yíng)銷軟件、網(wǎng)站建設(shè)、通遼網(wǎng)站維護(hù)、網(wǎng)站推廣。
KEY（key_part1,key_part2,key_part3）
select .... from table where key_part1='xxx' and key_part3='yyy';
從MYSQL的執(zhí)行計(jì)劃看，確實(shí)也是使用索引;
但在實(shí)際的優(yōu)化過(guò)程中，我們只是簡(jiǎn)單的關(guān)注是否使用了這個(gè)索引是不夠的。
[@more@]我們需要關(guān)注的是:
對(duì)key_part3這個(gè)關(guān)鍵字過(guò)濾的時(shí)候，是否用到了索引？
下面我們來(lái)創(chuàng)建一個(gè)例子：
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;
查詢語(yǔ)句:
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' ;
我們看到，查詢的條件，對(duì)索引來(lái)說(shuō)是跳躍的。
這對(duì)ORACLE來(lái)說(shuō)并不是難事。SQL優(yōu)化器會(huì)在索引里完成對(duì)time字段的過(guò)濾。
用HINT：/*+INDEX_SS(TABLE INDEX_NAME)*/ 可以來(lái)輔助。
但對(duì)MYSQL來(lái)說(shuō)，你可能并不知道，是什么時(shí)候?qū)ime字段進(jìn)行過(guò)濾的。
當(dāng)然我們希望是通過(guò)索引來(lái)過(guò)濾TIME字段。這樣最后回表的次數(shù)就會(huì)少一些。
在測(cè)試過(guò)程中，我們通過(guò)觀察MYSQL的Innodb_buffer_pool_read_requests（邏輯讀）變量的變化，來(lái)推測(cè)結(jié)果。
注意以下查詢過(guò)程中，條件time的變化，以及變量Innodb_buffer_pool_read_requests的變化
#######測(cè)試環(huán)境：
OS：RHEL 4.7 X86_64
MYSQL 5.0.51a / 5.1.40
請(qǐng)?jiān)陂_(kāi)始下面測(cè)試前，運(yùn)行：
select count(distinct concat('c',content)),count(*) from im_message_201001_11 where owner='huaniaoyuchong83' ；
以讓所有結(jié)果都在CACHE里；
#######開(kāi)始第一次測(cè)試
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)
#######開(kāi)始第二次測(cè)試
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)
#######開(kāi)始第三次測(cè)試
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)
#######開(kāi)始第四次測(cè)試
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)
#######開(kāi)始第五次測(cè)試
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)
#######開(kāi)始第六次測(cè)試
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)
####### 分析結(jié)果
前三次查詢，從索引檢索后需要回表：
time 結(jié)果行數(shù) 邏輯讀
30天 44397 176117
5天 4097 173838
1天 0 170290
后三次查詢，從索引檢索后不需要回表
time 結(jié)果行數(shù) 邏輯讀
30天 44397 5880
無(wú)time條件 44397 5880
5天 4097 5880
從數(shù)據(jù)來(lái)看，
select count(*) 這樣的查詢，有或者沒(méi)有time條件，邏輯讀是一樣，都不用回表。
這里也說(shuō)明這種情況MYSQL是用索引進(jìn)行time字段的過(guò)濾。
select count(distinct concat('c',content)),count(*), 這樣的查詢，用到了索引以外的字段，是必需回表的。
但通過(guò)邏輯讀發(fā)現(xiàn)，不管查詢結(jié)果是多少行，邏輯讀都差不多，在17W左右。
特別是結(jié)果行為0時(shí)，如果是通過(guò)索引過(guò)濾time，那么邏輯讀應(yīng)該接近5900,而不是17W。
這也說(shuō)明，這種情況下，MYSQL沒(méi)有使用索引來(lái)對(duì)TIME字段進(jìn)行過(guò)濾;
所以MYSQL對(duì)相同WHERE條件的查詢，還采用了不同的優(yōu)化程序；但MS這個(gè)優(yōu)化有點(diǎn)問(wèn)題。
對(duì)這樣的索引，需要優(yōu)化，可以。 調(diào)整索引順序(`owner`,`time`,`other`)。
但是這僅僅是對(duì)一個(gè)SQL的優(yōu)化。
你還要考慮到系統(tǒng)里還有很多其他類似的SQL需要用到這個(gè)索引。 所以在優(yōu)化時(shí)，需要評(píng)估所有的SQL。
網(wǎng)站名稱：Mysql索引的使用-組合索引+跳躍條件
網(wǎng)頁(yè)鏈接：http://www.rwnh.cn/article4/pgsjie.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、網(wǎng)頁(yè)設(shè)計(jì)公司、動(dòng)態(tài)網(wǎng)站、品牌網(wǎng)站建設(shè)、、定制開(kāi)發(fā)
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
