Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "mysql index merger description and use". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

What is index merge?

Let's take a look at the mysql documentation's description of index merging:

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.

According to the official documentation, we can learn:

Index merging is to scan and merge the ranges of several indexes into one index.

2. When indexes are merged, the indexes will be united, intersected or intersected first and then united, so as to merge into one index.

These indexes that need to be merged can only be of one table. Cannot index merge multiple tables.

How to determine if index merge is used

When using explain to operate on sql statements, if index merge is used, the type column of the output will show index_merge, and the key column will show all used indexes. As follows:

index_merge_sql

Example of using index merge

data table structure

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)

data

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)

Cases using index merging

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)

Cases without index merge

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)

From the above two cases, you can find that sql statements of the same pattern may sometimes use indexes and sometimes cannot use indexes. The ability to use an index depends on whether the mysql query optimizer analyzes the statistics and decides that it is faster to use an index.

Therefore, simply discussing whether a sql can use an index is a bit one-sided, and you need to consider the data.

precautions

Versions of mysql prior to 5.6.7 adhere to the range-first principle. That is, when a contiguous segment of an index contains all the data that meets the query requirements, index merging is no longer used, even if it provides efficiency. For example:

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)

There is only one result that meets the query requirements, and this record is contained by index key2.

You can see that this sql statement uses the key2 index. But this is not the fastest way to execute. In fact, index key1 and index key2 are index merged, and after taking the intersection, only one record is found to be suitable. Query efficiency should be faster.

"mysql index merge description and use" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.