mysql optimization (3) clustered index and non-clustered index

Clustered index and non-clustered index are major categories of btree

According to the algorithm btree

hash hash (memory engine) in memory

mysam

innodb

There are differences in the organization of data, although they are all btrees. They are divided according to the algorithm.

mysam points to the redirected disk location index and data independence and non-clustering.

innodb points to The reference itself has data all clustered here! Because it stores both the primary key value and the row data, it is called a clustered index

The primary index hides the row data, which is called a clustered index, and the secondary index points to a reference to the primary key.

If there is no primary key, we will look for a unique key... If there is no unique key, the system will generate a row id
Irregular insertion without row return will cause page splits

innodb

Advantages: When there are relatively few query entries based on the primary key, no row backing is required (the data is under the primary key node)

Disadvantages: If irregular data is inserted, frequent page splits will occur

Insert 10,000 pieces of data regularly and 10,000 pieces of data irregularly.

Observe the difference in time and experience the impact of clustered index and page splitting.

The above is mysql optimization (3) For the content of clustered index and non-clustered index, please pay attention to the PHP Chinese website (m.sbmmt.com) for more related content!