A brief analysis of indexes in MySQL storage engine

This article will talk to you about how to implement indexes in the MySQL storage engine. I hope it will be helpful to you!

We know that different storage engine files are different. We can view the data file directory:

show VARIABLES LIKE 'datadir';

Each InnoDB table has two files (.frm and .ibd), MyISAM tables have three files (.frm, .MYD, .MYI). [Related recommendations: mysql video tutorial]

has the same file, .frm. .frm is the file that defines the table structure in MySQL. It will be generated no matter which storage engine you choose when creating the table, so we will not look at it.

We mainly look at how the other two files implement the indexes of different MySQL storage engines.

Let’s take a look at MyISAM first.

MyISAM

In MyISAM, there are two other files:

One is the .MYD file, D stands for Data , is the data file of MyISAM, which stores data records, such as all table data of our user_myisam table.

One is the .MYI file. I stands for Index, which is the index file of MyISAM. It stores the index. For example, if we create a primary key index on the id field, then the primary key index is in this index. inside the file.

In other words, in MyISAM, the index and data are two independent files. So how do we find the data based on the index?

In the B Tree of MyISAM, the leaf nodes store the disk addresses corresponding to the data files. Therefore, after finding the key value from the index file .MYI, the corresponding data record will be obtained from the data file .MYD.

What is drawn here is the primary key index. If it is an auxiliary index, what is the difference?

In MyISAM, the auxiliary index is also in this .MYI file. There is no difference between the auxiliary index and the primary key index in the way they store and retrieve data. They also find the disk address in the index file and then obtain the data in the data file.

InnoDB

InnoDB has only one file (.ibd file), so where is the index placed?

In InnoDB, it uses the primary key as the index to organize data storage, so the index file and the data file are the same file, both in the .ibd file.

On the leaf node of InnoDB's primary key index, it directly stores our data.

#What is a clustered index (clustered index)?

means that the logical order of the index key values ​​is consistent with the physical storage order of the table data rows. (For example, the dictionary directory is sorted by Pinyin, and the content is also sorted by Pinyin. This directory sorted by Pinyin is called a clustered index).

In InnoDB, the way it organizes data is called a (clustered index organize table), so the primary key index is a clustered index, and the non-primary keys are non-clustered indexes.

How do indexes other than the primary key, such as the ordinary index we build on the name field, store and retrieve data?

In InnoDB, there is a primary and secondary distinction between primary key indexes and auxiliary indexes.

The auxiliary index stores the auxiliary index and primary key values. If you use an auxiliary index to query, the primary key index will be queried based on the primary key value, and the data will finally be obtained.

For example, if we use the name index to query name= '青山', it will find the primary key value in the leaf node, that is, id=1, and then go to the leaf node of the primary key index to get the data.

Another question, what if a table does not have a primary key?

1. If we define a primary key (PRIMARY KEY), then InnoDB will select the primary key as the clustered index.

2. If the primary key is not explicitly defined, InnoDB will select the first unique index that does not contain a NULL value as the primary key index.

3. If there is no such unique index, InnoDB will choose the built-in 6-byte long ROWID as the hidden clustered index, which will increment the primary key as row records are written.

select _rowid name from t2;

So? There can be no table without a primary key.

Summary

Through the above analysis, we know what the specific implementation form of indexes is in the two major storage engines, MyISAM and InnoDB.

For more programming-related knowledge, please visit: Introduction to Programming! !

The above is the detailed content of A brief analysis of indexes in MySQL storage engine. For more information, please follow other related articles on the PHP Chinese website!