Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

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

show VARIABLES LIKE 'datadir';
Copy the code

Each InnoDB table has two files (.frm and.ibd), and MyISAM table has three files (.frm,.myd,.myi).

One is the same file,.frm. .frm is a file that defines the table structure in MySQL. It will be generated regardless of which storage engine you use when creating a table.

Let’s take a look at how the other two files implement indexing for MySQL’s different storage engines.

Let’s start with MyISAM.

MyISAM

In MyISAM, there are two other files:

One isa.myd file, where D stands for Data, which is the MyISAM Data file that holds Data records, such as all the table Data of our user_myisam table.

MYI file, where I stands for Index, is the Index file of MyISAM, where the Index is, for example, if we create a primary key Index on the ID field, then the primary key Index is in that Index file.

In other words, in MyISAM, index and data are two separate files. So how do we find the data according to the index?

In MyISAM B+Tree, the leaf node stores the disk address corresponding to the data file. So from the index file. Once the key is found in MYI, it goes to the data file. Get the corresponding data record in MYD.

This is a primary key index. What would be different if it were a secondary index?

In MyISAM, the secondary index is also in this. MYI file. Secondary indexes store and retrieve data in the same way as primary key indexes, which find the disk address in the index file and then fetch data in the data file.

InnoDB

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

In InnoDB, it organizes data storage by index of primary key, so index file and data file are the same file, both in.ibD file.

In InnoDB’s primary key index leaf node, it stores our data directly.

What is a clustered index?

The logical order of the key values is the same as the physical order in which the table rows are stored. (For example, dictionaries are sorted by pinyin, and their contents are sorted by pinyin, which is called a clustered index).

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

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

In InnoDB, there is a primary and secondary index.

Secondary indexes store secondary indexes and primary key values. If a secondary index query is used, the primary key index is queried based on the primary key value and the data is finally retrieved.

For example, if we use the name index to query name= ‘Castle peak’, it will find the primary key at the leaf node, that is, id=1, and then fetch the data at the leaf node with the primary key index.

Another question, what if a table has no primary key?

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

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

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

select _rowid name from t2;
Copy the code

So? There will be no tables without primary keys.

summary

From the above analysis, we know how indexes are implemented in MyISAM and InnoDB, two storage engines.

After reading this article, if you find it helpful, please like it, comment and follow it. Thank you for your likes.