MySQL Database index simple principle

Today the main introduction of the MySQL version is 5.6, the official default InnoDB is the default storage engine, almost 99% of the scene can be used InnoDB engine.

The data structure implemented by the InnoDB index engine is a B + tree, and B does not represent a two-fork tree, but rather represents the balance, note that the B + Tree index can find only the page where the data row is located, and then the database is found by reading the page into memory, then looking in memory and then checking the data.

New features of B + trees:

All records are in the leaf node, and the sequence of emissions, in general, B + Tree Insert must be continuous emissions, leaf nodes are page units, if the data is not ordered, B + tree in order to maintain the order of the index, will be a large number of page splitting, resulting in a lot of resources wasted, so, The primary key of the general selection InnoDB is the integer type and irrelevant business.

B + Tree in the database has a feature is its high fan out, so the height of B + tree is generally 2-3 layers, that is, to find a key value, up to 2-3 io, the general time only need 0.02 seconds

Clustered index:

Clustered index is a B + tree constructed by the primary key, which contains all the row data in the leaf node, in general, the query optimizer is very inclined to use clustered index, because the clustered index can find the data directly in the leaf node, do not need to return the row lookup, in addition to the primary key index, we call the secondary index, Page level does not contain all the data, all the data only in the primary key, the leaf node contains the key value and the corresponding primary key, by finding the primary key to find row data,

MySQL Database index simple principle