1 min. Understanding index differences between MyISAM and InnoDB

I. Index of MYISAM

MyISAM indexes are stored separately from Row records, called Nonclustered indexes (unclustered index).

There is no essential difference between the primary key index and the normal index:

• Areas with contiguous aggregation store Row Records separately
• The leaf node of the primary key index, storing the primary key, and the pointer to the corresponding row record
• The leaf node of the normal index, storing the index column, and the pointer to the corresponding row record

VoiceOver: The MyISAM table can have no primary key.

The primary key index and the normal index are two separate index B + trees, which are positioned to the leaf node of the B + tree and then to the row record by the pointer when the index column is searched.

As an example, MyISAM:

There are four records in the table:

1, Shenjian, M, a  3, Zhangsan, M, a  5, Lisi, M, a  

Its B + Tree index constructs such as:

• Row records are stored separately
• ID is PK, there is an index tree of ID, leaves point to row record
• Name is key, there is an index tree of name, and leaves also point to Row Records

Second, the index of InnoDB

The primary key index of the InnoDB is stored together with the row records, so it is called the clustered index (Clustered index):

• No separate area to store Row Records
• The leaf node of the primary key index, which stores the primary key, with the corresponding row record (not the pointer)
• Voice-over: Therefore, the InnoDB PK query is very fast.

Because of this feature, the InnoDB table must have a clustered index:

(1) If the table defines PK, then PK is the clustered index;

(2) If the table does not define PK, the first non-null unique column is a clustered index;

(3) Otherwise, InnoDB will create a hidden Row-id as the clustered index;

A clustered index can only have one, because the data row can have only one copy of the clustered storage on the physical disk.

InnoDB can have more than one normal index, which is different from a clustered index:

• leaf node of normal index, storing primary key (also not pointer)

For the InnoDB table, the inspiration here is:

(1) It is not recommended to use a longer column master key, such as char (64), because all ordinary indexes will store the primary key, it will cause the normal index is too large;

(2) It is recommended to use the key key of the trend increment, because the data row and index, so as not to insert records, there are a large number of index splitting, row record movement;

Is still the example above, but the storage engine is replaced with InnoDB:

The table is still four records:

1, Shenjian, M, a  3, Zhangsan, M, a  5, Lisi, M, a  

Its B + Tree index constructs such as:

• ID is PK, row record and ID index tree are stored together
• Name is key, there is an index tree of name, leaf storage ID

When:

The leaf node of the B + tree is first positioned through the name secondary index to get id=5, and then to the row record through the clustered index.

VoiceOver: So, actually swept the index tree 2 times.

Iii. Summary

MyISAM and InnoDB use B + trees for indexing:

• MyISAM indexes are stored separately from the data
• MyISAM index leaves store pointer, primary key index is not much different from normal index
• InnoDB clustered index and data row Unified storage
• InnoDB the clustered index stores the data row itself, the normal index stores the primary key
• InnoDB must have and only one clustered index
• InnoDB recommends using a trend increment integer as the PK, rather than using a longer column as the PK

1 min. Understanding index differences between MyISAM and InnoDB