Introduction to the clustered index of Mysql index _mysql

What is the difference between a clustered index and a nonclustered index in MySQL?

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

The clustered index is also referred to as the cluster index.

Of course, the concept of clustered index is not MySQL, other database systems also have.

In short, a clustered index is an index organization in which the logical order of the key values determines the physical storage order of the table data rows, and the nonclustered index is the normal index, which simply creates the corresponding index on the data column, without affecting the physical storage order of the entire table.

Let's take a look at the differences between the two storage types:

Simply put, the physical storage order of the data in the IoT table is the same as that of the primary key index, so if the new data is discrete, the data blocks will tend to be discrete rather than in order. The order in which hot table data is written is stored in write-time order.

The advantages of the IoT table compare to the hot table:

The scope query efficiency is more high;

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

Especially suitable for a small number of hot data frequently read and write scenes;

Access to data by primary key is fast reachable;

The deficiencies of the IoT table are:

If the data change is mainly discrete, then the efficiency will be worse than hot table;

The shortages of hot tables are:

Index back table reading overhead is large;

Most of the data is random when read, and cannot be guaranteed to be read sequentially, the overhead is large;

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

As mentioned above, InnoDB is the clustered Index organization table, and its clustered index selection rule is this:

First, an explicitly defined primary key index is selected as a clustered index;

If not, select the first unique index that does not allow null;

Still not, use the InnoDB engine built-in ROWID as a clustered index;

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

Pictures from High-performance MySQL

As you can see, in the leaf node of this index structure, the node key value is the value of the primary key, and the value of the node stores the remaining column data, along with additional information such as ROWID, rollback pointer, Trx IDs, and so on.

Combined with this diagram, and as described above, we can see that in the InnoDB table, the clustered index is the same as the entire table, and the entire table is the clustered index. A primary key is necessarily a clustered index, and a clustered index is not necessarily a primary key.

MyISAM is the heap organization table, which does not have the concept of a clustered index.