Shulou(Shulou.com)06/01 Report--

For tables from the InnoDB storage engine, records are saved in a certain order by default, or in primary key order if there is a clearly defined primary key. 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 internal column is automatically generated in the table and saved in the order of that column. According to the primary key or internal column access is the fastest, the index InnoDB table as far as possible to specify its own primary key, when there are several columns in the table are unique, can be used as the primary key, to select the column as the most common access condition as the primary key to improve query efficiency. In addition, the normal index of the InnoDB table holds the key value of the primary key, so that the row-level lock can be achieved by locking the index.

It can be said that the database must have an index, without an index, the retrieval process becomes a sequential search, and the time complexity of O (n) is almost unbearable. It is easy to imagine how a table consisting of only a single keyword can be indexed using a B+ tree, as long as the keyword is stored in the node of the tree. When a record in a database contains multiple fields, a B+ tree can only store the primary key. If the non-primary key field is retrieved, the primary key index loses its function and becomes a sequential search. At this point, you should create a second set of indexes on the second column to be retrieved. This index is organized by a separate B+ tree. There are two common ways to solve the problem of multiple B + trees accessing the same set of table data, one is called clustered index (clustered index), and the other is called non-clustered index (secondary index). Although both of these names are called indexes, they are not a separate type of index, but a way to store data. For clustered index storage, row data and primary key B + tree are stored together, secondary key B + tree only stores secondary key and primary key, primary key and non-primary key B + tree are almost two types of trees. For non-clustered index storage, the primary key B + tree stores pointers to real rows of data at the leaf node, not the primary key.

InnoDB uses a clustered index to organize the primary key into a B+ tree, and the row data is stored on the leaf node. If you use the condition such as "where id = 14" to find the primary key, you can find the corresponding leaf node according to the B+ tree retrieval algorithm, and then get the row data. If you do a conditional search on the Name column, you need two steps: the first step is to retrieve the Name in the secondary index B + tree and reach its leaf node to get the corresponding primary key. The second step is to use the primary key to perform another B+ tree retrieval operation in the primary index B+ tree species, and finally reach the leaf node to get the whole row of data.

MyISM uses a non-clustered index, the two B + trees of the non-clustered index look the same, the structure of the nodes is exactly the same, but the content stored is different, the node of the primary key index B + tree stores the primary key, and the secondary key index B + tree stores the secondary key. The table data is stored in a separate place, and the leaf nodes of both B+ trees use an address to point to the real table data, and there is no difference between the two keys for table data. Because the index tree is independent, the index tree that does not need to access the primary key is retrieved by the secondary key.

To better illustrate the difference between the two indexes, let's imagine that a table stores four rows of data as shown in the following figure. Id is the primary index and Name is the secondary index. The figure clearly shows the difference between clustered index and non-clustered index.

Advantages of clustered indexing:

1 query speed is faster: because the row data and the leaf node are stored together, so the primary key and row data are loaded into memory together, if you find the leaf node, you can immediately return the row data. If you organize the data according to the primary key Id, it is faster to get the data.

2 reduce index maintenance: the advantage of using the primary key as the "pointer" instead of using the address value as the pointer is that it reduces the maintenance of the secondary index when the row moves or the data page is split, using the primary key value as a pointer will make the secondary index take up more space, in exchange for the advantage that InnoDB does not have to update the "pointer" in the secondary index when moving rows. That is to say, the position of the row (which is located by 16K Page in the implementation, which will be discussed later) will change with the modification of the data in the database (the previous Btree node split and the Page split). The use of clustering index can ensure that no matter how the node of the primary key B+ tree changes, the secondary index tree will not be affected.

Implementation of InnoDB row lock:

The row lock of InnoDB is added to the index, which is implemented as follows:

1. Search by secondary index: the row lock is added to the column corresponding to the secondary index, and the primary key index is locked according to the primary key entry.

As shown in the figure above, retrieving the name='Ellision',Ellision index column by pressing the name field will add a lock and a lock on the primary key index entry 14. In this way, when other transactions cannot access the name='Ellision' column, nor can it access the corresponding data column based on other data items.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.