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

How to implement clustered index in MySQL? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

What does a clustered index and a nonclustered index mean in MySQL, and what's the difference?

In MySQL, the InnoDB engine table is the (clustered) index organization table (clustered index organize table), while the MyISAM engine table is the heap organization table (heap organize table).

Some people call a clustered index a clustered index.

Of course, the concept of clustered indexes is not unique to MySQL, as do other database systems.

In short, a clustered index is a form of index organization, and the logical order of the key values of the index determines the physical storage order of the table data rows, while the non-clustered index is a general index, which only creates the corresponding index on the data columns. does not affect the physical storage order of the entire table.

Let's first look at the differences between the two forms of storage:

To put it simply, the physical storage order of the data in the IOT table is the same as the order of the primary key index, so if the new data is discrete, it will cause the data blocks to be discrete rather than sequential. The order in which HOT data is written is stored in the order in which it is written.

The advantages of IOT over HOT are:

Range query is more efficient

When the data is updated frequently (the clustered index itself is not updated), it is less likely to produce fragmentation.

It is especially suitable for scenarios where a small part of hot data is read and written frequently.

Fast access to data through the primary key

The shortcomings of IOT table are as follows:

If the data changes are mainly discrete, then the efficiency will be worse than the HOT table.

The shortcomings of the HOT table are:

The index back to the table is expensive to read.

Most of the data are read randomly and cannot be guaranteed to be read sequentially, so it is expensive.

Only one clustered index can be created per InnoDB table, and a clustered index can consist of one or more columns.

As mentioned above, InnoDB is a clustered index organization table, and its clustered index selection rules are as follows:

First select the explicitly defined primary key index as the clustered index

If not, select * indexes that do not allow NULL.

If not, ROWID built into the InnoDB engine is used as the clustered index.

Let's take a look at the schematic diagram of the InnoDB primary key index:

The picture is from high performance MySQL.

As you can see, in the leaf node of this index structure, the key value of the node is the value of the primary key, while the value of the node stores the rest of the column data, as well as additional ROWID, rollback pointer, trx id, and so on.

This is the answer to the question about how to implement the clustered index in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.