InnoDB Index Implementation

for Tables of the InnoDB storage engine, records are saved in a certain order by default, and if there is a well-defined primary key, it is saved in the primary key order. If there is no primary key, but there is a unique index, it is saved in the order of the unique index. If there is neither a primary key nor a unique index, an inner column is automatically generated in the table, saved in the order of the column. According to the primary key or internal column access is the fastest, index InnoDB table as far as possible to specify the primary key, when the table has a few columns are unique, can be used as the primary key, to select the most commonly used as the access condition of the column as the primary key, improve query efficiency. In addition,the normal index of theInnoDB table will hold the key value of the primary key, so that row-level locks can be implemented by locking the index.

         It can be said that the database must have an index, no index, the retrieval process becomes a sequential lookup,O (n)the complexity of the time is almost unbearable. It's very easy to imagine how a table with only a single keyword can be usedB +The tree is indexed as long as the keyword is stored to the node of the tree. When a database contains multiple fields in a single record, a treeB +The tree can only store the primary key, and if it retrieves a non-primary key field, the primary key index loses its function and becomes a sequential lookup. A second set of indexes should be established on the second column to retrieve.  This index is determined by the independentB +trees to organize. There are two common ways to resolve multipleB +tree to access the same set of table data, a cluster index called clustered (Clustered Index), which is called a non-clustered index (Secondary Index). Although these two names are called indexes, this is not a separate index type, but a way of storing data. For clustered index storage, row data and primary keyB +trees are stored together, secondary keysB +Tree only stores secondary and primary keys, primary and non-primary keysB +trees are almost two kinds of trees. For non-clustered index storage, the primary keyB +The tree stores a pointer to the true data row in the leaf node, rather than the primary key.

      InnoDBusing a clustered index, the primary key is organized into a treeB +tree, and the row data is stored on the leaf node, if you use"where ID =14"such a condition to find the primary key, follow theB +The Tree retrieval algorithm can find the corresponding leaf node, and then obtain the row data. If theNamecolumn for a conditional search, you need two steps: The first step in the secondary indexB +Retrieving in TreeName, reaching its leaf node to get the corresponding primary key. The second step uses the primary key in the primary indexB +tree species executed once againB +a tree retrieval operation that eventually reaches the leaf node to get the entire row of data.

      myismuse of non-clustered indexes, two clusters of nonclustered indexesB +The tree does not look the same, the structure of the node is exactly the same as the contents of the storage, the primary key indexB +the node of the tree stores the primary key, the secondary key indexB +The tree stores the secondary keys. Table data is stored in a separate place, the twoB +

< Span style= "FONT-FAMILY:CALIBRI;" lang= "en-us" xml:lang= "en-US" >         4 id as the primary index, name as a secondary index. The diagram clearly shows the difference between the clustered index and the non-clustered index.

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/8A/20/wKioL1goc1-gTq9CAALLwQwKAcg461.png-wh_500x0-wm_3 -wmp_4-s_391814458.png "title=" indexing principle. PNG "alt=" Wkiol1goc1-gtq9caallwqwkacg461.png-wh_50 "/>

Clustered Index Benefits:

1 faster query times: because the row data and leaf nodes are stored together so that the primary key and row data are loaded together into memory, the leaf node can be found to return the row data immediately, if you follow the primary key Id to organize data and get data faster.

2Reduce index maintenance:Secondary indexes use primary keys as"Pointers" Rather than using address values as pointers, the benefit of reducing the maintenance of secondary indexes when row movement or data page splitting occurs, using primary key values as pointers makes secondary indexes take up more space, and the benefit isInnoDByou do not need to update this in the secondary index when you move a row"Pointers". That is, the position of the line (implemented by16Kof thePageThe data in the database will be changed (the previous.B +tree node splitting andPage), using a clustered index will ensure that the primary keyB +how the nodes of the tree change, the secondary index tree is unaffected.

InnoDB implementation of a row lock:

InnoDB row locks are added to the index, and the implementation process is as follows:

1. Search by secondary index: row locks are added to the columns corresponding to the secondary index, and the primary key index is found based on the primary key entry lock.

For example, press name Field Search name= ' ellision ' , ellision The index column is locked and the primary key index entry - lock. This makes it impossible for other transactions to access the name= ' ellision ' column, or to access the corresponding data column based on other data items.

This article is from the "Data Chen" blog, please be sure to keep this source http://jianhuchenmou.blog.51cto.com/7449945/1872457

InnoDB Index Implementation