Several graphs describe the data distribution of MyISAM and InnoDB and the data distribution differences between primary key indexes and level two indexes

Table structure:

CREATE TABLE Layout_test (

col1 int not NULL,

col2 int not NULL,

PRIMARY KEY (col1),

KEY (col2)

);

Description: The table's primary key value (col1) is 1~100000, but is inserted in random order

The values of column col2 are randomly assigned between 1~100, so there are many duplicate values.

MyISAM table (non-clustered index):

Data Distribution Chart:

Primary key Data distribution:

Col2 column index distribution (in fact, no difference from primary key index distribution)

InnoDB Data distribution (supports clustered index, index is table, so data does not need isolated storage like MyISAM)

Primary Key distribution (using a clustered index, each leaf node contains a primary key value, a transaction ID, a rollback pointer for transactions and MVCC, and all remaining columns)

Secondary index (Index on col2 column, and clustered index very different, the leaf node of level Two index contains index column and primary key column, store not "row child pin", but primary key value)

Final comparison Chart