mysql索引合并的說(shuō)明和使用

本篇內容介紹了“索引合并的說(shuō)明和使用”的有關(guān)知識，在實(shí)際案例的操作過(guò)程中，不少人都會(huì )遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學(xué)有所成！

什么是索引合并
   下面我們看下mysql文檔中對索引合并的說(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.
   根據官方文檔中的說(shuō)明，我們可以了解到：

   1、索引合并是把幾個(gè)索引的范圍掃描合并成一個(gè)索引。

   2、索引合并的時(shí)候，會(huì )對索引進(jìn)行并集，交集或者先交集再并集操作，以便合并成一個(gè)索引。

   3、這些需要合并的索引只能是一個(gè)表的。不能對多表進(jìn)行索引合并。

怎么確定使用了索引合并
   在使用explain對sql語(yǔ)句進(jìn)行操作時(shí)，如果使用了索引合并，那么在輸出內容的type列會(huì )顯示 index_merge，key列會(huì )顯示出所有使用的索引。如下：

   index_merge_sql

使用索引合并的示例
數據表結構
mysql> show create table test\G
*************************** 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\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=4\G
*************************** 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)
   從上面的兩個(gè)案例大家可以發(fā)現，相同模式的sql語(yǔ)句，可能有時(shí)能使用索引，有時(shí)不能使用索引。是否能使用索引，取決于mysql查詢(xún)優(yōu)化器對統計數據分析后，是否認為使用索引更快。


   因此，單純的討論一條sql是否可以使用索引有點(diǎn)片面，還需要考慮數據。


注意事項
   mysql5.6.7之前的版本遵守range優(yōu)先的原則。也就是說(shuō)，當一個(gè)索引的一個(gè)連續段，包含所有符合查詢(xún)要求的數據時(shí)，哪怕索引合并能提供效率，也不再使用索引合并。舉個(gè)例子：


mysql> explain select * from test where (key1_part1=1 and key1_part2=1) and key2_part1=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ref
possible_keys: key1,key2
          key: key2
      key_len: 4
          ref: const
         rows: 9
        Extra: Using where
1 row in set (0.00 sec)
   上面符合查詢(xún)要求的結果只有一條，而這一條記錄被索引key2所包含。


   可以看到這條sql語(yǔ)句使用了key2索引。但是這個(gè)并不是最快的執行方式。其實(shí)，把索引key1和索引key2進(jìn)行索引合并，取交集后，就發(fā)現只有一條記錄適合。應該查詢(xún)效率會(huì )更快。