In fact, the back table we talked about before is the use of two index trees at the same time, first in the secondary index tree to search for the corresponding primary key value, and then in the primary key index tree to query the complete record.

But my question today is, do two different secondary index trees work at the same time? In theory, it should work at the same time, otherwise MySQL is too stupid. But according to songge daily development experience, this kind of thing is best to avoid, if it happens to search two index trees at the same time, probably your index design has a problem, at this time to check whether the index design is reasonable.

In bold is the practical experience, but for the two index effective knowledge point, we still want to understand, together look at.

1. Index merge

For example, I have the following table structure:

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `username` (`username`),
  KEY `address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the code

SQL > alter table address = username; address = address; username = address;

Now my query SQL is as follows:

select * from user where username='1' or address='1';
Copy the code

There are two search criteria, username and address, which are two indexes belonging to two different index trees. Does it search both index trees? Or do you just search one index tree and filter the results from the first tree with another search criteria?

Let’s look at the database execution plan:

From a quick glance at the execution plan, you can guess that both indexes are used, and that there are several new faces in the execution plan:

  • The type ofindex_merge.
  • Extra forUsing union(username,address); Using where.

The index_merge of this type is an index merge.

2. The old game

Of course index_Merge was not originally available, it was introduced from MySQL5.0. Although you probably won’t be using versions prior to MySQL5.0 anymore, I’ll give you an idea of what MySQL is like. Before MySQL5.0, for the query SQL we presented above, there would be no index walk, full table scan. In those days, if you wanted to implement the above query, but also wanted to use the index, your SQL would say:

select * from user where username='1' union all select * from user where address='1' and username! ='1'
Copy the code

But this is obviously a little clunky.

So, starting with MySQL5.0, you can automatically scan multiple indexes in a query and merge the results, which we described earlier as index_Merge.

3. Three scenarios

There are three variations of the index merge algorithm, which we’ll look at separately.

3.1 the union

This is the union of two indexes.

Let’s look at the following SQL

select * from user where username like '1%' or address like '1%';
Copy the code

SQL > select * from index tree; select * from index tree; select * from index tree;

As you can see, index merging has already occurred in this execution plan (see Type, key, Extra).

Is index merging always sent whenever there are two index queries? Let’s look at another chestnut:

select * from user where username>'a' or address='1';
Copy the code

If you look at it, the search criteria have changed. Instead of index merging, we have a full table scan. Why is that? This leads to a condition for index merging, namely: each index corresponding to the search criteria, the primary key search must be ordered, if the primary key search is unordered, sorry, index merging can not be used. In a secondary index, the data is sorted in the order of the secondary index, with a structure similar to the following:

username A primary key
a 20
b 30
c 9
c 10
c 18
d 1
d 5

When username is the same, the primary key is in order. When username is different, the primary key is not in order. If the obtained primary key is out of order, the index merge cannot be realized.

This raises the question of why index merges occur only when the primary key is obtained in order. Because future de-duplication (union, sort-union) or intersection (intersect) is more efficient only if the primary key is ordered.

As of MySQL5.0, index merge is enabled by default, but you can also disable it.

SET optimizer_switch = 'index_merge_union=off';
Copy the code

After the shutdown, look at the execution plan:

As you can see, there’s still an index merge, but instead of a union, it’s sort_union, so let’s see what sort_union is.

3.2 sort_union

Sort_union is basically the same as union, but with an additional ability to sort.

Because we said earlier that if you get an unordered primary key, no index merge occurs and you might end up going straight to a full table scan. MySQL > select * from address; select * from username; select * from address; select * from username; select * from address;

It’s basically an extra bold step compared to union.

So let’s go ahead and close sort_union as follows:

SET optimizer_switch = 'index_merge_sort_union=off';
Copy the code

After closing, go to the execution plan as follows:

In this case, there is no index merge, direct full table scan.

3.3 intersects

This is the intersection of two indexes.

For example, SQL:

select * from user where username like '1%' and address like '1%';
Copy the code

It is possible for an intersection to occur during the execution of this SQL. Of course, this is not absolute, it depends on the optimizer after optimization.

Songo tried for a long time and couldn’t reproduce an example, mainly because my simulation wasn’t right. If you have an existing Using INTERSECT example, please share it with us.

But LET me share this principle with you. Let’s look at the following picture:

Select * from S where username = ‘1%’ and address = ‘1%’; select * from T where address = ‘1%’ and username = ‘1%’; Intersect’s primary key id is in order when the search is complete.

  1. First, search the secondary index S to find the first record that meets the condition. Since the leaf node of the secondary index stores the primary key value, do not rush back to the table after getting the primary key value.
  2. Then go to secondary index T to search for the first record that meets the criteria and get the corresponding primary key.
  3. Compare the primary key values obtained in the first step and the second step: 3.1 If the primary key values are not equal, discard the primary key with the smaller value and leave the larger primary key. In the next search on S, compare the larger primary key with the primary key found in S. If the value of the primary key is equal, the primary key satisfies the search criteria. Then return to the table with the primary key.
  4. Repeat the first three steps until no records meet the criteria in their respective indexes.

This is known as cross-fetching primary keys.

Ok, so those are the three cases of index merging.

4. Summary

Some of you might say, well, since there are index merges, can I just create any index I want? Nonono!!! Index merging is a last resort. If it happens, it’s probably because you didn’t design the index properly, so you should think about how to optimize the index.

References:

  • Dev.mysql.com/doc/refman/…
  • How MySQL works
  • High Performance MySQL
  • www.modb.pro/db/29619