MySQL index and performance (2) clustered index

A clustered index is a way of storing data, which in effect preserves the B + Tree index and data rows in the same structure, and the InnoDB table is organized according to the clustered index (similar to the Index organization table of Oracle).

InnoDB through primary key clustering data, if no primary key is defined, a unique Non-empty index is chosen instead, and if there is no such index, the primary key is implicitly defined as the clustered index.

The image below illustrates the difference between a clustered index table (InnoDB) and an ordinary heap organization table (MyISAM):

For normal heap organization tables (right), table data and indexes are stored, and there is no difference between primary key indexes and level two index storage.

For clustered index tables (left), table data is stored with the primary key, the leaf node of the primary key index stores the row data, and the leaf node of the level two index stores the primary key value of the row.

The Clustered Index table maximizes the performance of I/O-intensive applications, but it also has the following limitations:

1 The insertion speed is heavily dependent on the insertion order, in the order of the primary key is the fastest way to insert, otherwise there will be page splitting, seriously affecting performance. Therefore, for InnoDB tables, we generally define a self increasing ID column primary key.

2 The cost of updating a primary key is high because it will cause the row to be updated to move. Therefore, for InnoDB tables, we generally define primary keys as not updatable.

3 Two-level index access requires two index lookups, the first time a primary key value is found, and the second time the row data is found based on the primary key value.

The leaf nodes of the secondary index store the primary key value, not the row pointer, to reduce the maintenance of the level two index when rows move or data page splits, but the level two index takes up more space.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/