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

Today, I will talk to you about how to understand type:index and Extra:Using in MYSQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Consider the following TYPE and Extra in the execution plan

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

| | 1 | SIMPLE | testud | NULL | index | NULL | id2 | 10 | NULL | 3 | 100.00 | Using index |

Type:index does not use the index B + tree structure, but only uses the index leaf node linked list structure to scan. We know that there is a two-way pointer between the leaf nodes in the index.

And the data of leaf nodes are sorted. Its approach is similar to that of ALL, and its access efficiency is not high. Its main application scenario is to avoid order by using using filesort.

That is, to avoid sorting. It is a way to access data, just like const, ref, eq_ref, etc.

Extra:Using index when the secondary index contains all the fields required by the query, the select query only needs to pass the index and can

Get all the data, so you don't need to go back to the table. Note that all the data here is all of the conditional predicates and query fields.

The sum of such as

Select id1 from test where id2=1

This index must contain id1 and id2. There is a special case called Index Extensions, which is described later.

It can consider the B+ tree structure, such as using type:ref or not considering using type:index.

Generally speaking, the size of the index is much smaller than the size of the table, whether in terms of returning to the table or reading the physical file size, using the

Using index can improve query performance. Also known as index override scan

These two places are often confusing, and they do not always appear together (although it is possible), in fact, they are not necessarily related.

Here is the structure of my test table

Mysql > show create table testud

| | Table | Create Table |

| | testud | CREATE TABLE `testud` (

`id1` int (11) NOT NULL

`id2` int (11) DEFAULT NULL

`id3` int (11) DEFAULT NULL

`id4` int (11) DEFAULT NULL

PRIMARY KEY (`id1`)

KEY `id2` (`id2`, `id3`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

1 row in set (0.05sec)

1. Type:index can appear alone.

Mysql > explain select * from testud force index (id2) order by id2

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

| | 1 | SIMPLE | testud | NULL | index | NULL | id2 | 10 | NULL | 3 | 100.00 | NULL |

1 row in set, 1 warning (0.00 sec)

This only represents the sort that type=index avoids, but you need to use a two-way linked list to access the entire leaf node from beginning to end.

2. Extra:Using index can appear alone.

Mysql > explain select id2 from testud where id2=1

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

| | 1 | SIMPLE | testud | NULL | ref | id2 | id2 | 5 | const | 1 | 100.00 | Using index |

1 row in set, 1 warning (0.00 sec)

Here type is ref, which represents the scanning id2=1 of a single value through a non-unique index, that is, here (id2,id3) is a non-unique index, and 1 is a single value, and he takes into account the index.

The structure of the B + tree not only considers the leaf node, but also needs to filter the id2=1 condition from the root node to the branch node (if any), and then to the leaf node.

And because id2 is included in the id2,id3, of course, you can use Using index.

From the above two cases, there is no inevitable relationship between type:index and Extra:Using index. They each represent the meaning of value.

3. It is very simple to appear together.

Mysql > explain select id2 from testud

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

| | 1 | SIMPLE | testud | NULL | index | NULL | id2 | 10 | NULL | 3 | 100.00 | Using index |

1 row in set, 1 warning (0.01sec)

You need to use a two-way linked list to access the entire leaf node from beginning to end, and the index id2 contains all the required data.

There is also a special scenario that needs to improve Using index, which has been asked by many people. The official document is called

9.2.1.7 Use of Index Extensions

To put it simply, for example, the above KEY `id2` (`id2`, `id3`), we know that the leaf node actually has the primary key data at the end in addition to indexing its own data. I am in front of this.

It has been verified, refer to:

Http://blog.itpub.net/7728585/viewspace-2128817/

At this time, the index id2 actually contains data arranged like id2 id3 id1, if id2 is sorted by id3, if id3 is sorted by id1, then

Our using index extends the scope to the following statement:

Mysql > explain select id1,id2,id3 from testud where id2=1

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

| | 1 | SIMPLE | testud | NULL | ref | id2 | id2 | 5 | const | 1 | 100.00 | Using index |

1 row in set, 1 warning (0.01sec)

We can see that Using index is in effect.

Finally, let's briefly explain the index coverage scan in ORACLE.

There are two kinds of ORACLE.

Index fast full scan: it is mainly scanned according to the physical order of disks. We know that linked lists are called linked lists because they have pointers pointing to the front or back, such as those commonly used in C language.

* next * pr to indicate before and after, since it is a pointing relationship, it is not necessarily physically ordered. But this approach is faster, and you can use physical multi-block reads, but the returned data is not orderly, so consider carefully that there is actually no such way in MYSQL.

Index full scan: the return of this kind of access is orderly, it's a bit like index+Using index scanning in MYSQL, and he also uses it a lot to avoid sorting.

After reading the above, do you have any further understanding of how to understand type:index and Extra:Using in MYSQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.