"MySQL optimized" clustered index and non-clustered index concept

first understand two words:

InnoDB Secondary index points to a reference to a primary key ( clustered index )

The secondary and primary indexes of the MyISAM point to the physical row ( non-clustered index )

　A clustered index is an algorithm that re-organizes actual data on disk to sort by the value of one or more columns you specify. The characteristic is that the order of storing data is consistent with the index order. In general, the primary key creates a clustered index by default, and only one clustered index is allowed on a table (reason: Once the data is stored, there can be only one order).

In the principle of database, this explains the difference between clustered and nonclustered indexes:
　　The leaf node of the clustered index is the data node, and the leaf node of the non-clustered index is still the index node, but there is a pointer to the corresponding data block.

InnoDB and MyISAM's primary key indexes are compared to level two indexes:

That is, the node of the InnoDB's primary index is put together with the data, and the nodes of the secondary index hold the primary key position.

Both the primary and secondary indexes of MyISAM point to the location of the data on the disk.

The leaf node of the InnoDB two-level index holds the key field plus the primary key value. Therefore, a two-level index query first finds the primary key value, and then innodb the corresponding data block by the primary key index based on the primary key value found.
MyISAM's Level Two index leaf node is also a combination of the column value and the row number, and the leaf node holds the physical address of the data. So you can see that there is no difference between the primary key index and the level two index of the MyISAM, the primary key index is just a unique, non-empty index called primary, and the MyISAM engine can have no primary key

You can also use the following image to understand:

First, the MyISAM index points to the physical line:

InnoDB Primary Index The leaf node is the primary key and the data, the secondary index points to the primary key

The row data is stored directly on the InnoDB primary index file, called the clustered index, and the secondary index points to a reference to the primary key

In MyISAM, both the primary and secondary indexes point to the physical row (disk location).

Note: for InnoDB,

1: The primary key index stores both the index value and the row's data in the leaf

2: If there is no primary key, it will be unique key

3: If there is no unique, the system generates an internal ROWID key.

4: In the index structure of the primary key, like InnoDB, both the primary key value is stored and the row data is stored, which is called "Clustered index".

1. Clustered index
A) an index entry directly corresponds to the actual data record of the storage page, which is "direct"
b) The primary key uses it by default
c) The sorting of the index entries and the storage sort of the data rows is exactly the same, taking advantage of this, want to modify the data storage order, you can change the primary key method (undo the original primary key, another find can meet the primary key requirements of a field or a set of fields, rebuild the primary key)
d) A table can have only one clustered index (reason: Once the data is stored, the order can only have one)

2, non-clustered index
A) cannot be "direct" and may be linked to a multi-level page table before it can be located to a data page
b) A table can have multiple non-clustered indexes

-------------------------------------clustered index Advantage disadvantage;-----------------------------------

Advantage: There is less time to query entries based on primary key, no back row (data is under primary key node)

Disadvantage: If you encounter irregular data insertions, it causes frequent page splits.

Page splitting process for clustered indexes

Understanding: The  original index is as follows

A 8 is inserted at this point and you need to insert the 13,16,17 after moving the 8

For the MyISAM engine: Simply move the index node after storing the data,
Clustered index for InnoDB: You need to move the 13,16,17 after inserting the data. But because there is data on the three nodes, it also creates additional overhead. The equivalent of three nodes moving with data at the same time moving.




can also be used to understand:

Summarize:

1:innodb's buffer_page is very powerful.

2: The primary key value of the clustered index, should be as far as possible the value of continuous growth, rather than random values,

(Do not use random strings or UUID)

Doing so will result in a large number of page splits and page movements.

　　In order to see the effect, you can insert 1000 data into the database by inserting 1000 data in order and disorderly order in Java. Look at the time of execution to see the effect.







such as:Innodb_pages_written represents the number of pages that have been written, you can insert 1000 data sequentially and randomly insert 1000 data to observe the growth change.

Mysql>Show status like '%page_%';+----------------------------------+-------+|Variable_name|Value|+----------------------------------+-------+|Innodb_buffer_pool_pages_data|  the   ||Innodb_buffer_pool_pages_dirty| 0     ||Innodb_buffer_pool_pages_flushed| 749   ||Innodb_buffer_pool_pages_free| 243   ||Innodb_buffer_pool_pages_misc|  -    ||Innodb_buffer_pool_pages_total|  +   ||Innodb_dblwr_pages_written| 628   ||Innodb_page_size| 16384 ||innodb_pages_created|  the    ||Innodb_pages_read| 736   || Innodb_pages_written | 749 ||Tc_log_max_pages_used| 0     ||Tc_log_page_size| 0     ||Tc_log_page_waits| 0     |+----------------------------------+-------+ -Rowsinch Set(0.00Sec

The difference between clustered index and nonclustered index reference: http://www.cnblogs.com/qlqwjy/p/7770580.html

"MySQL optimized" clustered index and non-clustered index concept