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

This article introduces you how to carry out the index classification of MySQL, the content is very detailed, interested friends can refer to, hope to be helpful to you.

The problem of index classification of MySQL has always been a headache. Almost all the data will give you a long list of primary key indexes, single-valued indexes, overlay indexes, adaptive hash indexes, full-text indexes, clustered indexes, non-clustered indexes, etc. It gives people the impression that there are many ways to implement MySQL index, but there is no clear classification. So I try to summarize how to classify the index types of MySQL to make it easier for you to remember. As multiple storage engines are supported in MySQL, there is a slight difference between different storage engines. If there is no special statement below, the default refers to the InnoDB storage engine.

Indexes can be divided into many names from different dimensions, but one problem needs to be made clear-the essence of the index is a data structure, and the partition of other indexes is for practical applications.

First, divide it according to the underlying data structure

Index is a data structure to improve query efficiency, and there are many data structures that can improve query efficiency, such as binary search tree, red-black tree, hopping table, hash table (hash table) and so on. MySQL uses B+Tree and hash table (Hash table) as the underlying data structure of the index (in fact, jump table is also used to achieve full-text index, but this is not an important test point, so it can be ignored).

1. Hash index

MySQL does not explicitly support Hash indexes, but as an internal optimization. Specifically in the Innodb storage engine, it will monitor the lookup of the secondary index on the table. If it is found that a secondary index is accessed frequently and the secondary index becomes hot data, the hash index will be established for it. Therefore, in the Innodb of MySQL, the Hash index is automatically generated for hot spot data. This hash index, according to the characteristics of the scenario it uses, is also called adaptive Hash index.

2. B+ tree index

This is the basic implementation of MySQL indexing. Except for full-text index and hash index, the indexes of Innodb and MyISAM are all realized by B+ tree.

Second, divide it according to the number of index fields

In order to meet different data retrieval needs, the index can contain either one field or multiple fields at the same time. An index composed of a single field can be called a single-valued index, otherwise it is called a composite index, also known as a composite index or a multi-valued index.

This is easy to understand. If we have a table, we have three attributes, id,age and name. If you build an index on id, this is a single-valued index; if you build an index on name and age, this is a composite index.

The data order of the index of the composite index is related to the order of the fields. In an index containing multiple values, if the values of the previous fields are duplicated, they will be sorted by the values that follow them.

The premise of using an overlay index is that the field length is relatively short, and it is not suitable to use an overlay index for fields with a longer value length for many reasons. For example, the index is generally stored in memory, and if it takes up a large amount of space, it may be loaded from disk, affecting performance.

Third, divide 1. 1 according to whether the index is established on the primary key. Primary key index

In MySQL, data is organized according to the primary key, so every table must have a primary key index, and there can be only one primary key index, which cannot be null and must be unique. If no primary key index is specified when creating the table, a hidden field is automatically generated as the primary key index.

two。 Auxiliary index

If it is not a primary key index, it can be called a non-primary key index, or a secondary index or a secondary index. The leaf node of the primary key index stores the complete data row, while the leaf node of the non-primary key index stores the primary key index value. when querying data through the non-primary key index, the primary key index will be found first. then go to the primary key index to find the corresponding data.

Here, suppose we have a table, user, with three columns: ID,age,name,create_time,id is the primary key and (age,create_time,name) builds a secondary index. Execute the following sql statement:

Select name from user where age > 2 order by create_time desc.

Normally, the query is divided into two steps:

1. Find the primary key of the record according to the secondary index

two。 Look up the record in the primary key index and return name.

But in fact, we can see that the secondary index node is built according to age,create_time,name, and the index information contains all the information we want. If the name information can be returned from the secondary index, the second step is completely unnecessary and can greatly improve the query speed.

According to this idea, Innodb is optimized for query scenarios using secondary indexes, called overlay indexes. (I don't know why this term is used in the industry. It's too easy to cause ambiguity. Wouldn't it be better to call an index overlay query?)

Fourth, according to the storage relevance of the data and the index.

According to the storage relationship between data and index, it can be divided into clustered index and non-clustered index (also called clustered index and nonclustered index). A clustered index, also known as a cluster index, is a method of reorganizing the actual data on disk to sort by the value of one or more specified columns. To put it succinctly, the difference between the two is whether the storage order of the index and the storage order of the data are related, the relevant is the clustered index, the irrelevant is the non-clustered index. The specific implementation will vary according to the data structure of the index. Here we take the index implemented by B+ tree as an example to illustrate clustered index and non-clustered index.

1. Clustering index

In the primary key index of Innodb, non-leaf nodes store index pointers, while leaf nodes store both indexes and data, which is a typical clustered index. (here, it can be found that the order in which indexes and data are stored is strongly related. So it is a typical clustering index), as shown in the figure:

two。 Non-clustered index

The index and the data file in MyISAM are stored separately, and the leaf node of B+Tree stores the address where the data is stored, not the specific data, which is a typical non-clustered index; in other words, the data can be stored anywhere on the disk, and the index can be stored anywhere on the disk, as long as the leaf node records the correct data storage address. Therefore, the index storage order has nothing to do with the data storage relationship, and it is a typical non-clustered index. In addition, the secondary index in Inndob is also a non-clustered index.

5. Other categories 1. Unique index

As the name implies, rows with the same index value are not allowed, thus prohibiting duplicate indexes or key values. The system checks whether there are duplicate key values when creating the index, and checks each time data is added using INSERT or UPDATE statements. If there are duplicate values, the operation will fail and an exception will be thrown.

It is important to note that the primary key index must be a unique index, and the unique index is not necessarily the primary key index. A unique index can be understood as simply setting a unique property for the index.

two。 Full-text index

Prior to MySQL version 5.6, only the MyISAM storage engine supported the full-text engine. In version 5.6, InnoDB adds support for full-text indexing, but does not support Chinese full-text indexing. In version 5.7.6, MySQL has a built-in ngram full-text parser to support word segmentation in Asian languages. Mainly used to use keywords to query text, not the main scenario-oriented MySQL, the use of less, will not be discussed here.

VI. Summary

Finally, a brain map is summarized to facilitate memory:

On how to carry out the index classification of MySQL is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.