Shulou(Shulou.com)06/01 Report--

In this article, the editor introduces in detail "non-clustered index analysis of MyISAM storage engine in MySQL". The content is detailed, the steps are clear, and the details are handled properly. I hope this article "non-clustered index analysis of MyISAM storage engine in MySQL" can help you solve your doubts.

In InnoDB, the index is the data, that is, the leaf node of the B+ tree of the clustered index already contains all the complete user records. Although MyISAM's index scheme also uses a tree structure, it stores the index and data separately, which is also called non-clustered index.

Create table index_demo (C1 int, c2 int, c3 char (1), primary key (C1)) ROW_FORMAT=COMPACT

The records in the table are stored separately in a file in the order in which they are inserted. The file is not divided into several data pages, and as many records as there are, you can insert as many records into the file as you like. We can quickly access a record by line number. When MyISAM is used as the storage engine in the table, its records are represented in the storage space as shown in the figure:

Because the data is not deliberately sorted by primary key size when inserting data, we cannot use dichotomy to look up the data. Tables that use the MyISAM storage engine will store the index information in a separate file, called an index file. MyISAM creates a separate index for the primary key of the table, but instead of storing the complete user record in the leaf node of the index, it stores a combination of the primary key value and the row number. That is, first find the corresponding line number through the index, and then find the corresponding record through the line number.

In the InnoDB storage engine, we only need to search the clustered index according to the primary key value once to find the corresponding record; in the MyISAM storage engine, we need to do a table return operation, which also means that the indexes established in MyISAM are all secondary indexes.

MyISAM stores the address offset recorded in the data file directly at the index leaf node. From this, we can see that the operation of MyISAM returning to the table is very fast, because it takes the address offset to fetch data directly from the file, while InnoDB obtains the primary key and then looks for records in the clustered index. Although it is not slow, it is not as slow as directly using the address to access.

If necessary, we can also index other columns separately or federated, similar to indexes in InnoDB, except that the corresponding column + row numbers are stored at the leaf node, and these index pages are all secondary indexes.

We can see that for the non-clustered index, the structure of the non-clustered index is the same, that is, the leaf node stores the corresponding column + row number, regardless of whether the primary key is the sorting rule or the non-primary key is the sorting rule.

After reading this, the article "non-clustered Index Analysis of MyISAM Storage engine in MySQL" has been introduced. If you want to master the knowledge points of this article, you still need to practice and use it yourself. If you want to know more about related articles, 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.