mysql 索引合并: 一条 sql 可以使用多个索引
前言
mysql 的索引合并并不是什么新特性。早在 mysql5.0 版本就已经实现。之所以还写这篇博文，是因为好多人还一直保留着一条 sql 语句只能使用一个索引的错误观念。本文会通过一些示例来说明如何使用索引合并。
什么是索引合并
下面我们看下 mysql 文档中对索引合并的说明：
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.
根据官方文档中的说明，我们可以了解到：
索引合并是把几个索引的范围扫描合并成一个索引。
索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。
这些需要合并的索引只能是一个表的。不能对多表进行索引合并。
使用索引合并有啥收益
简单的说，索引合并，让一条 sql 可以使用多个索引。对这些索引取交集，并集，或者先取交集再取并集。从而减少从数据表中取数据的次数，提高查询效率。
怎么确定使用了索引合并
在使用 explain 对 sql 语句进行操作时，如果使用了索引合并，那么在输出内容的 type 列会显示 index_merge，key 列会显示出所有使用的索引。如下：
在 explain 的 extra 字段中会以下几种：
你会发现并没有 sort_intersect，因为根据目前的实现，想索引取交集，必须保证通过索引取出的数据顺序和 rowid 顺序是一致的。所以，也就没必要 sort 了。
sort_union 索引合并的示例
数据表结构
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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)
|
数据
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 | 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)
|
使用索引合并的案例
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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)
|
未使用索引合并的案例
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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)
|
sort_union 总结
从上面的两个案例大家可以发现，相同模式的 sql 语句，可能有时能使用索引，有时不能使用索引。是否能使用索引，取决于 mysql 查询优化器对统计数据分析后，是否认为使用索引更快。
因此，单纯的讨论一条 sql 是否可以使用索引有点片面，还需要考虑数据。
union 索引合并使用案例
数据表结构
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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`,`id`),
KEY `key2` (`key2_part1`,`key2_part2`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
|
数据结构和之前有所调整。主要调整有如下两方面：
引擎从 myisam 改为了 innodb。
组合索引中增加了 id，并把 id 放在最后。
数据
数据和上面的数据一样。
使用索引合并的案例
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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)
|
union 总结
相同的数据，相同的 sql 语句，只是数据表结构有所调整，就从 sort_union 变为了 union。有以下几个原因：
只要通过索引取出的数据已经按 rowid 进行了排序，就可以使用 union。
组合索引中在最后加 id 字段，目的就是通过索引前两个字段取出的数据是按 id 排序。
把引擎从 myisam 改为 innodb，目的就是让 id 和 rowid 的顺序一致。
intersect 使用案例
数据结构和数据和 union 案例中的一致。
使用索引合并的案例
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> explain select * from test where (key1_part1=1 and key1_part2=1) and (key2_part1=1 and key2_part2=1)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: index_merge
possible_keys: key1,key2
key: key2,key1
key_len: 8,8
ref: NULL
rows: 3
Extra: Using intersect(key2,key1); Using where; Using index
1 row in set (0.02 sec)
|
相关文档
Index Merge Optimization
MySQL 优化器：index merge 介绍
再来吐槽一个关于 MySQL 的索引合并问题
index merge 的补充说明
http://www.bo56.com/mysql索引合并一条sql可以使用多个索引/
#sohu-dba#
