Mysql-index data sorting

The previous article briefly introduced several structures and storage methods of B-TREE, but the relationship between index and data still does not seem to be related.

So this article will use the actual An example of data rows. After creating the index, what is the order in which they are sorted on B+TREE.

1. Simulate and create original data

In the figure below, the left side is the simulated data for my own convenience. The engine is mysiam~

The right side is a normal simulation data table after randomly arranging them using EXCEL. The primary keys are then arranged according to 1-27 (if it is not random, I will write it in order when simulating the data. Adding an index makes it difficult to see the index sorting process)

That is to say, the data on the right, so that the original data we want to test, was sorted like this before the index was built, and all subsequent data are sorted by This is done according to the standards, so that you can better see the sorting effect after the index is generated.

The table has 4 fields (id, a, b, c), with a total of 27 rows of data

2. Create index a

As shown below, after creating index a, in the index structure, the original ordering according to the primary key ID becomes a new rule. We say that the index is actually a data structure. Then create index a, which is to create a new structure, sort according to the rules of field a, the first data row represented by the primary key ID is 1, the second data row represented by ID=3, and the third data row represented by ID=5 data row. . .

New sorting primary key ID (ID represents their row of data): 1 3 5 6 9 16 18 23 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 field a is the same, their arrangement is based on the primary key ID before and after, for example, the same value is a=1.1, But their ordering is ID value 1, 3, 5, 6. . The corresponding rows are sorted in a similar order to the primary key ID. (That is, the sorting when the same value is used, the smaller ID is in front)

3. Create index (a,b)

As shown below, when creating a union After indexing (a,b), in the index structure, the original sorting according to the primary key ID has changed to a new rule. The sorting rule first sorts according to field a, and then sorts according to field b based on a. That is, based on index a, field b is also sorted.

New sorting primary key ID (ID represents their row of data): 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 values ​​of fields a and b are the same, their arrangement is also determined by the primary key ID Determined, for example, the same row (18,6,23) with a=1.1 and b=2.1, but their order is 6,18,23.

Field (a,b) index, first sort by a index, and then based on a, sort by b

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

4. Create index (a,b,c)

field(a,b ,c) Index, first sort by a, b index, then based on (a, b), sort by c

New sorting primary key ID (represented by ID Data for their line): 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

5. Conclusion:

is the same as the previous article Mysql-Index-BTree Type [Simplified], B-TREE tree The last row of leaf nodes, arranged from left to right, is in this order. Different indexes are in different orders. With the order, wouldn't the search be much more convenient and faster?

#We know that the process of reading data (equivalent to the process of finding a room), if there is Index (room registration table), first read the data structure of the index (because it has small data and is fast to read), find the storage location of the real physical disk in the leaf node of its structure (equivalent to finding the house number), and then get Go to the disk with the house number and get the data directly. This is a process of reading data. If there is no index, it means you don’t know the destination. Just search from room to room.

When there is no index, the primary key ID is actually their index, arranged according to the rules of primary key ID from small to large;

When there is an index, index a, joint index (a, b ), the joint index (a, b, c) corresponds to the three B+TREE structures, and the physical disks pointed to by the end of the leaf nodes are different.

Conclusion:

1. If no index is created, it is arranged in ascending order according to the ID primary key

2. When index a is created, A new structural index (B+TREE) will be generated to record a new structural rule to facilitate quick search

3. When creating index a, index ab, index abc, the three corresponding data are sorted It’s different

4. Index abc takes into account both index ab and index a, so when the former is available, the latter two do not need to be created

5. When an index is created, non-indexed columns are sorted by increasing ID by default

More conclusions: Mysql-index summary: http:// blog.csdn.net/ty_hf/article/details/53526405

When a new piece of data is inserted, while storing the data, a copy of this table will also be maintained. Index and place it in a suitable location. It explains why indexes may have negative effects when the amount of data is particularly large. INSERT and DELETE on the indexed table will become slower. Frequent insertion and deletion of data will also consume time in maintaining the index. What is the bottleneck? ? 500W? To be verified.

The above is the content of Mysql-index data sorting. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!