mysql index merging: one sql can use multiple indexes
黄舟
Release： 2017-02-21 10:15:43
Original
4852 people have browsed it
Please indicate the source when reprinting: mysql index merging: one sql can use multiple indexes
Foreword
MySQL's index merging is not a new feature. It has been implemented as early as mysql5.0 version. The reason why I am writing this blog post is because many people still retain the misconception that a SQL statement can only use one index. This article will illustrate how to use index merging through some examples.
What is index merging
Let's take a look at the description of index merging in the mysql document:
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.
Copy after login
1. Index merging is to merge the range scans of several indexes into one index. 2. When merging indexes, the indexes will be combined, intersected, or intersected first and then combined to merge into one index. 3. These indexes that need to be merged can only belong to one table. Index merging cannot be performed on multiple tables.
What are the benefits of using index merging
Simply put, index merging allows one SQL to use multiple indexes. Take the intersection, union, or intersection first and then the union of these indices. This reduces the number of times to retrieve data from the data table and improves query efficiency.
How to confirm that index merging is used
When using explain to operate a SQL statement, if index merging is used, index_merge will be displayed in the type column of the output content, and all indexes used will be displayed in the key column. as follows:
There are the following types in the extra field of explain: Using union index to get the union set Using sort_union first sort the retrieved data by rowid, and then get the union set Using intersect index to get the intersection
You will find that there is no sort_intersect, because according to the current implementation, if you want to retrieve the intersection by index, you must ensure that the order of the data retrieved through the index is consistent with the rowid order. Therefore, there is no need to sort.
Example of sort_union index merging
Data table structure
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)
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)
Copy after login
未使用索引合并的案例
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)
Copy after login
sort_union summary
From the above two cases, you can find that the SQL statements of the same mode may sometimes be able to use indexes, and sometimes they may not be able to use indexes. Whether the index can be used depends on whether the MySQL query optimizer thinks that using the index is faster after analyzing the statistical data. Therefore, it is a bit one-sided to simply discuss whether an sql can use indexes, and the data also needs to be considered.
union index merge use case
Data table structure
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)
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)
Copy after login
union summary
The same data, the same SQL statement, but the data table structure has been adjusted, from sort_union to union. There are several reasons: 1. As long as the data retrieved through the index has been sorted by rowid, union can be used. 2. Add the id field at the end of the combined index. The purpose is to sort the data retrieved by the first two fields of the index by id. 3. Change the engine from myisam to innodb. The purpose is to make the order of id and rowid consistent.
intersect use case
mysql index merging: one sql can use multiple indexes http://www.php.cn/
The above is mysql index merging :One sql can use the content of multiple indexes. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
