.jpg)
? ? ? ? ? 轉(zhuǎn)載請(qǐng)注明來(lái)源：mysql索引合并:一條sql可以使用多個(gè)索引
前言
mysql的索引合并并不是什么新特性。早在mysql5.0版本就已經(jīng)實(shí)現(xiàn)。之所以還寫這篇博文，是因?yàn)楹枚嗳诉€一直保留著一條sql語(yǔ)句只能使用一個(gè)索引的錯(cuò)誤觀念。本文會(huì)通過(guò)一些示例來(lái)說(shuō)明如何使用索引合并。
什么是索引合并
下面我們看下mysql文檔中對(duì)索引合并的說(shuō)明：
The?Index?Merge?method?is?used?to?retrieve?rows?with?several?range?scans?and?to?merge?their?results?into?one.? The?merge?can?produce?unions,?intersections,?or?unions-of-intersections?of?its?underlying?scans.? This?access?method?merges?index?scans?from?a?single?table;? it?does?not?merge?scans?across?multiple?tables.
1、索引合并是把幾個(gè)索引的范圍掃描合并成一個(gè)索引。
2、索引合并的時(shí)候，會(huì)對(duì)索引進(jìn)行并集，交集或者先交集再并集操作，以便合并成一個(gè)索引。
3、這些需要合并的索引只能是一個(gè)表的。不能對(duì)多表進(jìn)行索引合并。
使用索引合并有啥收益
簡(jiǎn)單的說(shuō)，索引合并，讓一條sql可以使用多個(gè)索引。對(duì)這些索引取交集，并集，或者先取交集再取并集。從而減少?gòu)臄?shù)據(jù)表中取數(shù)據(jù)的次數(shù)，提高查詢效率。
怎么確定使用了索引合并
在使用explain對(duì)sql語(yǔ)句進(jìn)行操作時(shí)，如果使用了索引合并，那么在輸出內(nèi)容的type列會(huì)顯示 index_merge，key列會(huì)顯示出所有使用的索引。如下：
在explain的extra字段中會(huì)以下幾種：
Using union 索引取并集
Using sort_union 先對(duì)取出的數(shù)據(jù)按rowid排序，然后再取并集
Using intersect 索引取交集
你會(huì)發(fā)現(xiàn)并沒(méi)有 sort_intersect，因?yàn)楦鶕?jù)目前的實(shí)現(xiàn)，想索引取交集，必須保證通過(guò)索引取出的數(shù)據(jù)順序和rowid順序是一致的。所以，也就沒(méi)必要sort了。
sort_union索引合并的示例
數(shù)據(jù)表結(jié)構(gòu)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
@@######@@ |
數(shù)據(jù)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
@@######@@ |
使用索引合并的案例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
@@######@@ |
未使用索引合并的案例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
@@######@@ |
sort_union總結(jié)
從上面的兩個(gè)案例大家可以發(fā)現(xiàn)，相同模式的sql語(yǔ)句，可能有時(shí)能使用索引，有時(shí)不能使用索引。是否能使用索引，取決于mysql查詢優(yōu)化器對(duì)統(tǒng)計(jì)數(shù)據(jù)分析后，是否認(rèn)為使用索引更快。
因此，單純的討論一條sql是否可以使用索引有點(diǎn)片面，還需要考慮數(shù)據(jù)。
union索引合并使用案例
數(shù)據(jù)表結(jié)構(gòu)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
@@######@@ |
數(shù)據(jù)結(jié)構(gòu)和之前有所調(diào)整。主要調(diào)整有如下兩方面：
1、引擎從myisam改為了innodb。
2、組合索引中增加了id，并把id放在最后。
數(shù)據(jù)
數(shù)據(jù)和上面的數(shù)據(jù)一樣。
使用索引合并的案例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
@@######@@ |
union總結(jié)
相同的數(shù)據(jù)，相同的sql語(yǔ)句，只是數(shù)據(jù)表結(jié)構(gòu)有所調(diào)整，就從sort_union變?yōu)榱藆nion。有以下幾個(gè)原因：
1、只要通過(guò)索引取出的數(shù)據(jù)已經(jīng)按rowid進(jìn)行了排序，就可以使用union。
2、組合索引中在最后加id字段，目的就是通過(guò)索引前兩個(gè)字段取出的數(shù)據(jù)是按id排序。
3、把引擎從myisam改為innodb，目的就是讓id和rowid的順序一致。
intersect使用案例
mysql索引合并:一條sql可以使用多個(gè)索引
http://www.php.cn/
以上就是mysql索引合并:一條sql可以使用多個(gè)索引的內(nèi)容，更多相關(guān)內(nèi)容請(qǐng)關(guān)注PHP中文網(wǎng)（www.php.cn）！
mysql>?show?create?table?testG ***************************?1.?row?*************************** ???????Table:?test Create?Table:?CREATE?TABLE?`test`?( ??`id`?int(11)?NOT?NULL?AUTO_INCREMENT, ??`key1_part1`?int(11)?NOT?NULL?DEFAULT?'0', ??`key1_part2`?int(11)?NOT?NULL?DEFAULT?'0', ??`key2_part1`?int(11)?NOT?NULL?DEFAULT?'0', ??`key2_part2`?int(11)?NOT?NULL?DEFAULT?'0', ??PRIMARY?KEY?(`id`), ??KEY?`key1`?(`key1_part1`,`key1_part2`), ??KEY?`key2`?(`key2_part1`,`key2_part2`) )?ENGINE=MyISAM?AUTO_INCREMENT=18?DEFAULT?CHARSET=utf8 1?row?in?set?(0.00?sec)
mysql>?select?*?from?test; +----+------------+------------+------------+------------+ |?id?|?key1_part1?|?key1_part2?|?key2_part1?|?key2_part2?| +----+------------+------------+------------+------------+ |??1?|??????????1?|??????????1?|??????????1?|??????????1?| |??2?|??????????1?|??????????1?|??????????2?|??????????1?| |??3?|??????????1?|??????????1?|??????????2?|??????????2?| |??4?|??????????1?|??????????1?|??????????3?|??????????2?| |??5?|??????????1?|??????????1?|??????????3?|??????????3?| |??6?|??????????1?|??????????1?|??????????4?|??????????3?| |??7?|??????????1?|??????????1?|??????????4?|??????????4?| |??8?|??????????1?|??????????1?|??????????5?|??????????4?| |??9?|??????????1?|??????????1?|??????????5?|??????????5?| |?10?|??????????2?|??????????1?|??????????1?|??????????1?| |?11?|??????????2?|??????????2?|??????????1?|??????????1?| |?12?|??????????3?|??????????2?|??????????1?|??????????1?| |?13?|??????????3?|??????????3?|??????????1?|??????????1?| |?14?|??????????4?|??????????3?|??????????1?|??????????1?| |?15?|??????????4?|??????????4?|??????????1?|??????????1?| |?16?|??????????5?|??????????4?|??????????1?|??????????1?| |?17?|??????????5?|??????????5?|??????????1?|??????????1?| |?18?|??????????5?|??????????5?|??????????3?|??????????3?| |?19?|??????????5?|??????????5?|??????????3?|??????????1?| |?20?|??????????5?|??????????5?|??????????3?|??????????2?| |?21?|??????????5?|??????????5?|??????????3?|??????????4?| |?22?|??????????6?|??????????6?|??????????3?|??????????3?| |?23?|??????????6?|??????????6?|??????????3?|??????????4?| |?24?|??????????6?|??????????6?|??????????3?|??????????5?| |?25?|??????????6?|??????????6?|??????????3?|??????????6?| |?26?|??????????6?|??????????6?|??????????3?|??????????7?| |?27?|??????????1?|??????????1?|??????????3?|??????????6?| |?28?|??????????1?|??????????2?|??????????3?|??????????6?| |?29?|??????????1?|??????????3?|??????????3?|??????????6?| +----+------------+------------+------------+------------+ 29?rows?in?set?(0.00?sec)
mysql>?explain?select?*?from?test?where?(key1_part1=4?and?key1_part2=4)?or?(key2_part1=4?and?key2_part2=4)G ***************************?1.?row?*************************** ???????????id:?1 ??select_type:?SIMPLE ????????table:?test ?????????type:?index_merge possible_keys:?key1,key2 ??????????key:?key1,key2 ??????key_len:?8,4 ??????????ref:?NULL ?????????rows:?3 ????????Extra:?Using?sort_union(key1,key2);?Using?where 1?row?in?set?(0.00?sec)
mysql>?explain?select?*?from?test?where?(key1_part1=1?and?key1_part2=1)?or?key2_part1=4G ***************************?1.?row?*************************** ???????????id:?1 ??select_type:?SIMPLE ????????table:?test ?????????type:?ALL possible_keys:?key1,key2 ??????????key:?NULL ??????key_len:?NULL ??????????ref:?NULL ?????????rows:?29 ????????Extra:?Using?where 1?row?in?set?(0.00?sec)
mysql>?show?create?table?testG ***************************?1.?row?*************************** ???????Table:?test Create?Table:?CREATE?TABLE?`test`?( ??`id`?int(11)?NOT?NULL?AUTO_INCREMENT, ??`key1_part1`?int(11)?NOT?NULL?DEFAULT?'0', ??`key1_part2`?int(11)?NOT?NULL?DEFAULT?'0', ??`key2_part1`?int(11)?NOT?NULL?DEFAULT?'0', ??`key2_part2`?int(11)?NOT?NULL?DEFAULT?'0', ??PRIMARY?KEY?(`id`), ??KEY?`key1`?(`key1_part1`,`key1_part2`,`id`), ??KEY?`key2`?(`key2_part1`,`key2_part2`,`id`) )?ENGINE=InnoDB?AUTO_INCREMENT=30?DEFAULT?CHARSET=utf8 1?row?in?set?(0.00?sec)
mysql>?explain?select?*?from?test?where?(key1_part1=4?and?key1_part2=4)?or?(key2_part1=4?and?key2_part2=4)G ***************************?1.?row?*************************** ???????????id:?1 ??select_type:?SIMPLE ????????table:?test ?????????type:?index_merge possible_keys:?key1,key2 ??????????key:?key1,key2 ??????key_len:?8,8 ??????????ref:?NULL ?????????rows:?2 ????????Extra:?Using?union(key1,key2);?Using?where 1?row?in?set?(0.00?sec)
.png)
