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

This article is to share with you about the principle of Innodb index in MySQL, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

What is an index?

The index is translated into a directory that is used to quickly locate the location of the data we are looking for. For example, we compare a database to a book, and the index is the catalogue in the book. To find something of interest in the book at this moment, we usually don't go through the whole book to confirm where the content is, but through the book's catalog, locate the number of pages in the chapter of the content, and then turn directly to the page.

Let's take a look at the index in the database:

Full table scan VS index scan

Take the dictionary as an example, a full table scan means that if we look up a word, then read through the Xinhua dictionary, and then find the word we want to find, and the corresponding to the full table scan is the index lookup. Index lookup is to find the specific location of the data we are looking for in the index part of the table, and then go to the table to find all the data we are looking for.

OK, don't talk too much nonsense, start talking!

Text

Science popularization of index

First introduce the concept of clustered index and non-clustered index!

In the Mysql we usually use, we use the following statement

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name,...) Index_col_name: col_name [(length)] [ASC | DESC]

The indexes created, such as composite index, prefix index and unique index, all belong to non-clustered index. In some books, they are also called secondary index (secondary index). In the following article, we call it non-clustered index, and its data structure is B + tree.

So, this clustered index has no statement to generate separately in Mysql. In Innodb, the data in Mysql is stored in the order of primary keys. Then the clustering index is to construct a B + tree according to the primary key of each table, and the leaf node stores the row data of the whole table. Because the data in the table can only be sorted by one B + tree, a table can have only one clustered index.

In Innodb, a clustered index defaults to the primary key index.

At this time, the witty reader should ask me

What if my watch doesn't have a primary key?

The answer is, if there is no primary key, the clustered index is built according to the following rules

When there is no primary key, a unique and non-empty index column is used as the primary key to become the clustered index of the table. If there is no such index, InnoDB will implicitly define a primary key as the clustered index.

Ps: do you remember the difference between adding a primary key and using uuid as a primary key? Because the primary key uses a clustered index, if the primary key is self-increasing id, then the corresponding data must also be stored on disk adjacent to each other, and the write performance is relatively high. If it is in the form of uuid, frequent insertions will cause innodb to move disk blocks frequently, resulting in lower write performance.

Introduction to the principle of Index

Let's start with a table with a primary key, as shown below. PId is the primary key.

PIdnamebirthday5zhangsan2016-10-028lisi2015-10-0411wangwu2016-09-0213zhaoliu2015-10-07

Draw the structure of the table as follows

As shown in the above figure, it is divided into two parts, the upper part is the B + tree formed by the primary key, and the lower part is the real data on the disk! So, when we execute the following statement

Select * from table where pId='11'

So, the execution process is as follows

As shown in the figure above, starting from the root, after 3 searches, you can find the real data. If you do not use an index, do a progressive scan on disk until you find the location of the data. Obviously, it is faster to use indexes. However, when writing data, you need to maintain the structure of the B+ tree, so write performance will degrade!

OK, next introduce a non-clustered index! We execute the following statement

Create index index_name on table (name)

The structure diagram is as follows

Note that a new B+ tree will be generated based on your index field. Therefore, each time we add an index, we increase the size of the table and take up disk storage space. However, note that the leaf node of a non-clustered index is not real data, and its leaf node is still an index node, storing the value of the index field and the corresponding primary key index (clustered index).

If we execute the following statement

Select * from table where name='lisi'

The structure diagram is as follows

As can be seen from the red line in the image above, start with the non-clustered index tree, and then find the clustered index. According to the clustering index, find the complete data on the B+ tree of the clustering index!

that

What is not going to be queried on the clustered index tree?

Remember that the value of this index field is stored on our non-clustered index tree. If, at this point we execute the following statement

Select name from table where name='lisi'

At this point, the structure diagram is as follows

As shown in the red line above, if the desired value is found on the non-clustered index tree, it will not be queried on the clustered index tree. Remember the indexing problem mentioned by the blogger in the correct posture of select:

When executing select col from table where col =?, and there is an index on col, the efficiency is better than executing select * from table where col =? Several times faster!

After looking at the picture above, you should have a deeper understanding of this sentence.

So what happens when we execute the following statement at this time?

Create index index_birthday on table (birthday)

At this point, the structure diagram is as follows

The above is the principle of Innodb index in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.