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

Follow up with the previous article:

Http://blog.itpub.net/7728585/viewspace-2126344/

How to prove that the leaf nodes of INNODB secondary index are sorted according to PRIMARY KEY with the same key value

We set up a table in the previous article

Mysql > create table test (an int,b int,primary key (a), key (b))

Query OK, 0 rows affected (0.08 sec)

And inserted the data.

Mysql > insert into test values (1Pol 1)

Query OK, 1 row affected (0.08 sec)

Mysql > insert into test values (5prime1)

Query OK, 1 row affected (0.03 sec)

Mysql > insert into test values (3Pol 1)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into test values (4jue 2)

Query OK, 1 row affected (.59 sec)

Mysql > insert into test values (10ju 4)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into test values (7 and 4)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into test values (8. 5)

Query OK, 1 row affected (0.01sec)

Mysql > insert into test values (11pr. 5)

Query OK, 1 row affected (0.01sec)

Mysql > insert into test values (20pc6)

Query OK, 1 row affected (0.01sec)

Mysql > insert into test values (21pr. 6)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into test values (19507)

Query OK, 1 row affected (0.03 sec)

Mysql > insert into test values (16pc7)

Query OK, 1 row affected (0.01sec)

Through the analysis and program, the storage order in the secondary index column b is as follows:

[root@ora12ctest test] #. / a.out test.ibd 4

Index_no is:42

Find first one record!

Bazaar 1thecontrol ARU 1For->

Bazaar 1, Apura, 3, Mustang->

Bazaar 1, Apura, 5, etc.->

Bazaar 2, Magna, Avu, 4, Mutual->

BRV 4MAL ARU 7PUR->

BRV 4Magic ARU 10PUR->

Bazaar 5 Magnum Apura 8 muri->

Bazaar 5 Magnum Avu 11 muri->

Bazaar 6 ~ (th) A _ (14) 20 ~ (th))

Bazaar 6 ~ (th) A _ (14) 21 ~ (- 1)

Bazaar 7, Apura, 16, murmur->

Bazaar 7 Magnum ARU 19 Mustco->

Here we discuss SELECT * FROM using USING INDEX indexes to override scanning B columns and using table-generated clustered indexes instead of indexes.

Return order and performance comparison.

First of all, the conclusion of the guess is given:

1. When using the USING INDEX B column index, the return order should be the same as the return order of the secondary index on column B, that is, the result of the program. Here, we should pay attention to one point.

Friends who are familiar with ORACLE will see that the data of the index is actually INDEX KEY+ROWID if they DUMP over the index block, then index override scan (INDEX FAST FULL SCAN) must not be used in this case.

Because the index does not contain a value at all, but INNODB is different, it contains PRIMARY KEY, so it uses USING INDEX.

2. Without using any indexes, only full table scans are used. In fact, full table scans scan the leaf nodes of the clustered index B + tree in linked list order, so we can infer that its order is and column A.

The order of the primary key is consistent.

Here is the proof of these two points:

1 、

Mysql > explain select * from test force index (b)

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | SIMPLE | test | NULL | index | NULL | b | 5 | NULL | 12 | 100.00 | Using index |

+-- +

1 row in set, 1 warning (0.00 sec)

Obviously the Using index B index.

Look at the results:

Mysql > select * from test force index (b)

+-+ +

| | a | b | |

+-+ +

| | 1 | 1 |

| | 3 | 1 |

| | 5 | 1 |

| | 4 | 2 |

| | 7 | 4 |

| | 10 | 4 |

| | 8 | 5 |

| | 11 | 5 |

| | 20 | 6 |

| | 21 | 6 |

| | 16 | 7 |

| | 19 | 7 |

+-+ +

Is it exactly the same as the program that runs according to the linked list structure?

Bazaar 1thecontrol ARU 1For->

Bazaar 1, Apura, 3, Mustang->

Bazaar 1, Apura, 5, etc.->

Bazaar 2, Magna, Avu, 4, Mutual->

BRV 4MAL ARU 7PUR->

BRV 4Magic ARU 10PUR->

Bazaar 5 Magnum Apura 8 muri->

Bazaar 5 Magnum Avu 11 muri->

Bazaar 6 ~ (th) A _ (14) 20 ~ (th))

Bazaar 6 ~ (th) A _ (14) 21 ~ (- 1)

Bazaar 7, Apura, 16, murmur->

Bazaar 7 Magnum ARU 19 Mustco->

So conclusion 1 has been verified.

2 、

Mysql > explain select * from test force index (primary)

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | NULL |

+-- +

1 row in set, 1 warning (0.00 sec)

Obviously did not use the index, then we can conclude that he used the table, that is, the clustered index, and returned according to the linked list of the clustered index, that is, according to the primary key.

The order of column An is returned, and because it is the primary key, the order is naturally fixed without looking at the value of column B. Come see

Mysql > select * from test force index (primary)

+-+ +

| | a | b | |

+-+ +

| | 1 | 1 |

| | 3 | 1 |

| | 4 | 2 |

| | 5 | 1 |

| | 7 | 4 |

| | 8 | 5 |

| | 10 | 4 |

| | 11 | 5 |

| | 16 | 7 |

| | 19 | 7 |

| | 20 | 6 |

| | 21 | 6 |

+-+ +

You can see that it is true if conclusion 2 is verified.

Of course, this conclusion is not only suitable for SELECT full index scanning, but I have added a column to prove it.

C

Mysql > alter table test add column c int

Query OK, 0 rows affected (1.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > update test set cantilever 100

Query OK, 12 rows affected (0.11 sec)

Rows matched: 12 Changed: 12 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

The goal is otherwise the way MYSQL overrides the scan with the index Using index:

1 、

Mysql > explain select * from test force index (b) where b in (4pm 5pm 7)

+-+-

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-+-

| | 1 | SIMPLE | test | NULL | range | b | b | 5 | NULL | 6 | 100.00 | Using index condition |

+-+-

1 row in set, 1 warning (0.00 sec)

Mysql > select * from test force index (b) where b in (4pm 5pm 7)

+-- +

| | a | b | c | |

+-- +

| | 7 | 4 | 100 | |

| | 10 | 4 | 100 | |

| | 8 | 5 | 100 | |

| | 11 | 5 | 100 | |

| | 16 | 7 | 100 | |

| | 19 | 7 | 100 | |

+-- +

6 rows in set (0.01 sec)

2 、

Mysql > explain select * from test force index (primary) where b in (4pm 5pm 7)

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 30.00 | Using where |

+-- +

1 row in set, 1 warning (0.00 sec)

Mysql > select * from test force index (primary) where b in (4pm 5pm 7)

+-- +

| | a | b | c | |

+-- +

| | 7 | 4 | 100 | |

| | 8 | 5 | 100 | |

| | 10 | 4 | 100 | |

| | 11 | 5 | 100 | |

| | 16 | 7 | 100 | |

| | 19 | 7 | 100 | |

+-- +

6 rows in set (0.00 sec)

You can clearly see the difference between them, that is, query 1 is obtained by querying the leaf nodes of the B-column secondary index and then bookmarking to find the clustered index returned by the primary key.

Order is, of course, how columns B in secondary index B are sorted. Query 2 is, of course, the condition for direct access to clustered index filtering, which is, of course, the order of primary keys.

Then we discuss the performance issue. Although they are all returned sequentially according to the leaf nodes of the B+ tree, the clustered index has more information than the secondary index.

Maybe it should be said that the clustered index is also the value of column B of Agraine, and the secondary index is also the value of column B of column Agraine.

But as we can see from the previous article:

. / bcview test.ibd 16 126 30 | more

Current block:00000003--Offset:00126--cnt bytes:21--data is:80000001000000000707a70000011b011080000001

Current block:00000004--Offset:00126--cnt bytes:21--data is:8000000180000001

In the clustered index

Messages like 000000000707a70000011b0110 are actually transaction id and roll pointer.

Then we can intuitively judge that under the same amount of data, the leaf PAGE of the secondary index will be less than the PAGE of the clustered index.

Then the performance should also be better.

Conclusion:

1. If you find that the order of returning data using different indexes is different, don't be surprised. It is normal to be different. If it is the same, you should be surprised. INNODB full table scan

It can be guaranteed that the order in which the data is returned is the order of the primary key (although we only verify the case of single-leaf nodes, the leaf nodes of the B+ tree have between PAGE and PAGE

), but not in ORACLE. I once read in ORACLE's book that if you want to ensure sorting, you can only use ORDER BY, but it depends on

It does not apply in INNODB, of course, if insurance plus ORDER BY is also possible, because the operation of SORT will be ignored by the optimizer, just in case.

In fact, another function of indexes in INNODB and ORACLE is to avoid sorting.

2. Create table test (an int,b int,primary key (a), key (b)); in this way, index override scanning can be used if where b = in INNODB

But not in ORACLE, the reason is given earlier.

3. In terms of performance, the performance of INNODB unsing index is better than that of full table scans (clustered indexes) in most cases, and the reasons have also been given.

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.