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.

   根据官方文档中的说明,我们可以了解到:

   1、索引合并是把几个索引的范围扫描合并成一个索引。

   2、索引合并的时候,会对索引进行并集,交集或者先交集再并集操作,以便合并成一个索引。

   3、这些需要合并的索引只能是一个表的。不能对多表进行索引合并。

怎么确定使用了索引合并

   在使用explain对sql语句进行操作时,如果使用了索引合并,那么在输出内容的type列会显示 index_merge,key列会显示出所有使用的索引。如下:

   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)

   从上面的两个案例大家可以发现,相同模式的sql语句,可能有时能使用索引,有时不能使用索引。是否能使用索引,取决于mysql查询优化器对统计数据分析后,是否认为使用索引更快。

   因此,单纯的讨论一条sql是否可以使用索引有点片面,还需要考虑数据。

注意事项

   mysql5.6.7之前的版本遵守range优先的原则。也就是说,当一个索引的一个连续段,包含所有符合查询要求的数据时,哪怕索引合并能提供效率,也不再使用索引合并。举个例子:

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)

   上面符合查询要求的结果只有一条,而这一条记录被索引key2所包含。

   可以看到这条sql语句使用了key2索引。但是这个并不是最快的执行方式。其实,把索引key1和索引key2进行索引合并,取交集后,就发现只有一条记录适合。应该查询效率会更快。

   tips:这条sql语句未在mysql5.6.7之后版本执行验证,以上为理论推导。有兴趣的话,您可以到mysql5.6.7之后版本上验证下。


觉得文章有用?立即: 和朋友一起 共学习 共进步!

猜您喜欢

2 thoughts on “mysql索引合并:一条sql可以使用多个索引

  1. Pingback: 【转载】ourmysql | LittleBill

  2. Pingback: 【转载】大牛文章 | LittleBill

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

*

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>