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

This article mainly explains "index analysis in MySQL storage engine", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "index analysis in MySQL storage engine"!

We know that different storage engine files are different, we can look at the data file directory:

show VARIABLES LIKE 'datadir';

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

One of them is the same file,.frm. .frm is the file defined by the table structure in MySQL. No matter which storage engine you choose when you build the table, it will be generated. We will not look at it.

Let's look at how the other two files implement indexes for MySQL's different storage engines.

Let's look at MyISAM first.

MyISAM

In MyISAM, there are two other files:

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

A.MYI file, I stands for Index, is the index file of MyISAM, storing the index, for example, we create a primary key index above the id field, then the primary key index is in this index file.

That is, in MyISAM, index and data are two separate files. So how do we find the data based on the index?

In MyISAM's B+Tree, leaf nodes store disk addresses corresponding to data files. So after finding the key from the index file.MYI, you will get the corresponding data record from the data file.MYD.

Here is the primary index drawn, if it is a secondary index, what is the difference?

In MyISAM, the secondary index is also in the.MYI file. Secondary indexes store and retrieve data in the same way as primary indexes. They find disk addresses in index files and retrieve data from data files.

InnoDB

InnoDB has only one file (.ibd file), so where does the index go?

In InnoDB, it organizes the storage of data with the primary key as the index, 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 stores our data directly.

What is a clustered index?

The logical order of the index keys 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 kind of directory sorted by pinyin is called clustered index).

In InnoDB, the way it organizes data is called clustered index organize table, so primary key index is clustered index, non-primary key is non-clustered index.

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

In InnoDB, primary and secondary indexes have a hierarchy.

Secondary indexes store secondary indexes and primary key values. If you use a secondary index query, you will query the primary key index based on the primary key value, and finally get the data.

For example, if we query name= 'Green Mountain' with the name index, 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 doesn't have a primary key?

If we define a PRIMARY KEY, InnoDB will select the PRIMARY KEY as the clustered index.

If no primary key is explicitly defined, InnoDB selects the first unique index that does not contain NULL values as the primary key index.

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

select _rowid name from t2;

So what? There will be no tables without primary keys.

At this point, I believe that everyone has a deeper understanding of "index analysis in MySQL storage engine", so let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

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.