High performance MySQL (5) Indexing strategy one clustered index

A clustered index is not a separate index type, but rather a way of storing data. The InnoDB clustered index actually holds the B-tree index and data row information in the same structure.

Because data rows cannot be stored in two different places, a table can have only one clustered index (although it can be emulated by overriding the index, which is described later).

The main discussion here is the InnoDB engine, where InnoDB aggregates data through primary keys, and if there is no primary key, a Non-empty unique index is selected, and if not, InnoDB implicitly defines a primary key as a clustered index.

Clustered index is a double-edged sword, to be considered carefully.

Advantages:

1, the relevant data can be saved together, such as the implementation of e-mail, based on the user ID to aggregate data, so that only need to go from disk to a small number of data pages can get a user all the messages, if no clustered index, each message may cause an I/O

2, data access faster. Because both the index and the data are in a b-tree.

3, using the clustered index query, you can directly use the primary key values in the page node.

Disadvantages:

1, clustered index to maximize the I/O-intensive application performance, if the data are placed in memory, the advantage is gone.

2, the insertion speed is heavily dependent on the insertion order, in the order of the primary key into the datasheet is the fastest speed. If not, it is best to use the Optimize table command to organize the data after inserting it.

3. The cost of updating clustered indexed columns is high because new data needs to be moved.

4. When inserting a new row, or when a primary key update needs to move a row, it can cause the page to split, resulting in more disk space being consumed.

5, Level two index (nonclustered) may be larger than expected, because the leaf node of the two-level index contains the primary key columns that reference the row.

6, two-level index access requires 2 index lookups, not one, because the two-level index holds not the physical location of the row, but the primary key value.

InnoDB and MyISAM data distribution contrast.

CREATE TABLE ' layout_test ' (
  ' col1 ' int (one) not null,
  ' col2 ' int (one) not NULL,
  PRIMARY key (' col1 '),
  key ' Col2 ' (' col2 ')
);

The primary key is not incremented, randomly inserted, col2 is copied, and many repetitions are repeated.

MyISAM is simpler, and he is stored on disk in the order in which the data is inserted.