Visual illustration of Mysql index structure and mysql index Diagram

Visual illustration of Mysql index structure and mysql index Diagram

1. Simulate and create raw data. On the left side, you can easily describe and simulate the data. The engine is mysiam ~ On the right is a normal simulation data table randomly arranged by EXCEL, and the primary keys are arranged by 1-27 (if the simulation data is not random, it is originally written in order, the index sorting process is not obvious. That is to say, the data on the right makes the raw data to be tested in this way before the index is created, all the data in the back end is based on this criterion, so that the sorting effect after the index is generated is better. This table has four fields (id, a, B, c) and 21 rows of data in total.

II. create index a. For example, after index a is created, the index structure changes from sorting by primary key ID to a new rule. We say that the index is actually a data structure. Then index a is created, that is, a new structure is created, which is sorted by field a rules. The first is the data row represented by primary key ID 1, and the second is the data row with ID = 3, the third ID = 5 indicates the data row...

New sorted primary key ID (ID indicates the data in their row ): 1 3 5 6 9 16 18 26 2 10 11 12 13 14 15 20 25 4 7 8 17 19 21 22 24 27 it is not difficult to find that when the field a is the same, they sort the primary key ids before and after, for example, the value of a = 1.1, but their sorting is the ID values of 1, 3, 5, 6 .. The sorting order of the corresponding rows is similar to that of the primary key ID.

III. create an index (a, B). For example, after a joint index (a, B) is created, the index structure changes from sorting by primary key ID to new rules, the sorting rules are first sorted by field a, and then by field B Based on Field. That is, field B is also sorted Based on Index.

New sorted primary key ID (ID indicates the data in their row ): 6 18 23 10 15 20 7 22 27 1 3 26 2 11 25 4 8 24 5 9 16 12 13 14 17 19 21 it is not difficult to find that when the field a and B values are the same, they are also determined by the primary key ID before and after the arrangement. For example, they are also a = 1.1, B = 2.1 rows (, 6, 23), but their sorting is 6, 18, 23. Field (a, B) index, first sort by a index, then on the basis of, sort by B 6 18 23 10 15 20 22 27 1 3 26 2 11 25 4 8 24 5 9 16 12 13 14 17 19 21

4. Create an index (a, B, c)

Field (a, B, c) indexes are first sorted by a and B indexes, and then sorted by c Based on (a, B ).

New sorted primary key ID (ID indicates the data in their row ): 23 6 18 15 20 10 27 22 7 1 26 3 11 2 25 24 4 8 5 16 9 12 14 13 17 19 21

V. Conclusion:

And the previous Mysql-index-BTree type [Lite version] said the same, the last row of the B-TREE tree leaf node, from left to right, is in this order, different indexes in different order.

We know that the process of reading data (equivalent to the process of finding a room), if there is an index (room registration form), first read the index data structure (because of its small data reading speed ), on the leaf node of its structure, find the storage location of the real physical disk (equivalent to finding the house number), and then take the house number to the disk to get the data directly, this is a process of reading data. If there is no index, then you don't know the destination. Choose one room.

When no index is available, the primary key ID is actually their index, which is arranged according to the rules of the primary key ID from small to large. When there is an index, index a, Union Index (a, B ), on the Three B + TREE structures corresponding to the joint index (a, B, c), the physical disks at the end of the leaf node are different.

Conclusion: 1. if no index is created, it is arranged progressively according to the ID Primary Key. 2. when index a is created, a new structure index (B + TREE) is generated to record a new structure rule for quick search. when index a, index AB, and index abc are created, their data sorting is different. 4. index abc takes into account both index AB and index a. Therefore, if the former is used, the latter two do not need to be created. when an index is created, non-indexed columns are sorted by ID increments by default.

When a new data entry is inserted, an index of the table is maintained while the data is stored, and it is placed in a proper position. It explains why indexes may have a negative impact when the data volume is too large. The INSERT and DELETE operations on indexed tables are slow, and frequent insertion and deletion of data also consumes time to maintain indexes, what are the bottlenecks ?? 500 million? Here is a brief introduction to the storage principle of an index.