mysql索引合并:一条sql可以使用多个索引

栏目: 数据库 · 发布时间: 5年前

内容简介: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

前言

mysql的索引合并并不是什么新特性。早在 mysql 5.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、这些需要合并的索引只能是一个表的。不能对多表进行索引合并。

使用索引合并有啥收益

简单的说,索引合并,让一条sql可以使用多个索引。对这些索引取交集,并集,或者先取交集再取并集。从而减少从数据表中取数据的次数,提高查询效率。

怎么确定使用了索引合并

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

mysql索引合并:一条sql可以使用多个索引

在explain的extra字段中会以下几种:

Using union 索引取并集

Using sort_union 先对取出的数据按rowid排序,然后再取并集

Using intersect 索引取交集

你会发现并没有 sort_intersect,因为根据目前的实现,想索引取交集,必须保证通过索引取出的数据顺序和rowid顺序是一致的。所以,也就没必要sort了。

sort_union索引合并的示例

数据表结构

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 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=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索引合并使用案例

数据表结构

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)

数据结构和之前有所调整。主要调整有如下两方面:

1、引擎从myisam改为了innodb。

2、组合索引中增加了id,并把id放在最后。

数据

数据和上面的数据一样。

使用索引合并的案例

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。有以下几个原因:

1、只要通过索引取出的数据已经按rowid进行了排序,就可以使用union。

2、组合索引中在最后加id字段,目的就是通过索引前两个字段取出的数据是按id排序。

3、把引擎从myisam改为innodb,目的就是让id和rowid的顺序一致。

intersect使用案例

数据结构和数据和union案例中的一致。

使用索引合并的案例

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)

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Programming Amazon Web Services

Programming Amazon Web Services

James Murty / O'Reilly Media / 2008-3-25 / USD 49.99

Building on the success of its storefront and fulfillment services, Amazon now allows businesses to "rent" computing power, data storage and bandwidth on its vast network platform. This book demonstra......一起来看看 《Programming Amazon Web Services》 这本书的介绍吧!

CSS 压缩/解压工具
CSS 压缩/解压工具

在线压缩/解压 CSS 代码

HEX CMYK 转换工具
HEX CMYK 转换工具

HEX CMYK 互转工具

HEX HSV 转换工具
HEX HSV 转换工具

HEX HSV 互换工具