Learning of "Todo" clustered index, nonclustered index, clustered index organization table, heap organization table, InnoDB, MyISAM, etc.

Reference:

Http://www.jb51.net/article/76007.htm

http://blog.csdn.net/xqy1522/article/details/6750252

The following is a summary of the first article:

In MySQL, the InnoDB engine table is the (clustered) Index organization table (clustered index organize table), and the MyISAM engine table is the heap organization table (heap organize tables). A clustered index is an index organization, in which the logical order of key values determines the physical storage order of table data rows, whereas a nonclustered index is a normal index, which simply creates an appropriate index on the data column and does not affect the physical storage order of the entire table. The order in which the data is physically stored in the IoT table is the same as the primary key index, so if the new data is discrete, the data blocks tend to be discrete, rather than tending to order. The order in which hot table data is written is stored in write-time order. The advantage of the IoT table compared to hot tables is that the range query is more efficient, the data is updated frequently (the clustered index itself is not updated), it is less prone to fragmentation, especially for scenes where a small number of hotspot data is frequently read and written, and when the data is accessed by the primary key, the shortage of the IoT table is: Data change , then the efficiency will be worse than the hot table; The shortage of hot table is: The index table read overhead is very large, most of the data read random, can not be guaranteed sequential read, the cost is large;

Each InnoDB table can only create one clustered index, and a clustered index may consist of one or more columns.
InnoDB is a clustered index organization table, and its clustered index selection rule is this: first select explicitly defined primary key index as a clustered index, if not, select the first non-nullable unique index, or not, use the InnoDB engine built-in ROWID as a clustered index; As you can see, in the leaf node of this index structure, the node key value is the value of the primary key, while the value of the node stores the remaining column data, as well as additional information such as ROWID, rollback pointer, TRX ID, and so on. In conjunction with this diagram, and as described above, we can know that the clustered index in the InnoDB table is equivalent to the entire table, and the entire table is a clustered index. The primary key is necessarily a clustered index, and the clustered index is not necessarily a primary key. MyISAM is a heap organization table, which has no concept of clustered indexes.

Learning of "Todo" clustered index, nonclustered index, clustered index organization table, heap organization table, InnoDB, MyISAM, etc.