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

Connect with http://blog.itpub.net/7728585/viewspace-2126305/

How big is the range of NEXT-KEY LOCK in RR mode?

To prove a point:

1. Analyze the linked list in the page of the secondary index. If the linked list in the secondary index page is sorted first by KEY and then sorted by PRIMARY KEY with the same KEY, then our argument is basically verified.

This can be made up later.

This article uses homemade tools. / bcview and. / mysqlblock

On the net disk

Http://pan.baidu.com/s/1num76RJ

At the same time, a lot of theoretical knowledge comes from

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

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

Wait for articles

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

Query OK, 0 rows affected (0.08 sec)

Mysql > insert into test values (1Pol 1)

Query OK, 1 row affected (0.08 sec)

Use mysqlblock to view the result

Current read blocks is: 3-This Block is data blocks (index pages)!

Current read blocks is: 4-This Block is data blocks (index pages)!

This should be the first node of the primary key B + tree and the first node of the secondary index B + tree.

Page 4 should be the secondary index. Let's verify and check it.

From 38 bytes to 74 bytes is INDEX HEADER. Check that his last 8 bytes is index ID.

Compare with INNODB_SYS_INDEXES

Current block:00000003--Offset:00066--cnt bytes:08--data is:0000000000000029

Current block:00000004--Offset:00066--cnt bytes:08--data is:000000000000002a

The INDEX_ID 0X29 0X2A is 41-42 in decimal system.

Mysql > select * from information_schema.INNODB_SYS_INDEXES where index_id in (41 cr. 42)

+-+ +

| | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |

+-+ +

| | 41 | PRIMARY | 40 | 3 | 1 | 3 | 24 | 50 |

| | 42 | b | 40 | 0 | 1 | 4 | 24 | 50 | |

+-+ +

2 rows in set (0.01sec)

View

Mysql > select * from information_schema.INNODB_SYS_TABLES where table_id=40

+-+

| | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |

+-+

| | 40 | test/test | 33 | 5 | 24 | Barracuda | Dynamic | 0 | Single |

+-+

1 row in set (0.02 sec)

It can be determined that 41 42 is the primary key and secondary index of the test table, and it is confirmed that current block:00000004 is the secondary index for storing data.

The only leaf node is also the root node (because there is so little data)

So let's look at page 04.

. / bcview test.ibd 16 94 14 | more

Current block:00000003--Offset:00094--cnt bytes:14--data is:010002001b696e66696d756d0002

Current block:00000004--Offset:00094--cnt bytes:14--data is:010002001b696e66696d756d0002

Infimum:

010002

001b-offset

696e66696d756d0002-"infimum\ 0"

The offset of the first row is determined. The position of the first row of 0X1b level 27 is 99-27.

. / 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:800000018000000100000000000000000000000000

Here, the aggregate index block and the secondary index record are the same.

Clustered index page03

80000001000000000707a70000011b011080000001

It contains

Offset-cluster key fields (N bytes)

Transaction id (6 bytes)

Roll pointer (7 bytes)

Non-key fields (M bytes)

000000000707a70000011b0110, these 13 bytes are transaction id and roll pointer.

Let's return to the topic and discuss the secondary index PAGE 4.

80000001 column b

Column 80000001 a

In fact, it is 1 and 1, and the 1 of bit 15 should be the representation of MYSQL symbol bit.

We found it, and then we inserted

Insert into test values (5dint 1)

Mysql > insert into test values (5prime1)

Query OK, 1 row affected (0.03 sec)

Mysql > commit

Find this record by offset

[root@ora12ctest test] #. / bcview test.ibd 16 124 2 | more

Current block:00000004--Offset:00124--cnt bytes:02--data is:000e

The offset 0X0E is 14

So the position of 5 1 in the secondary index page PAGE 4 is 126 "14" 140.

. / bcview test.ibd 16 140 30 | more

Current block:00000004--Offset:00140--cnt bytes:30--data is:800000018000000500000000000000000000000000000000000000000000

80000001 column b

Column 80000005 a

At this time, it is actually (BRV 1 ARV 1)-- > (BRV 1 ARV 5)

At this time, we insert

Mysql > insert into test values (3Pol 1)

Query OK, 1 row affected (0.00 sec)

Looking back, let's take a look at the cheap amount of the next record in the first record.

[root@ora12ctest test] #. / bcview test.ibd 16 124 2 | more

Current block:00000004--Offset:00124--cnt bytes:02--data is:001c

It is found that it has been changed to 0X1C 28. Let's find out if it is our newly inserted one.

126-28-154

. / bcview test.ibd 16 154 20 | more

Current block:00000004--Offset:00154--cnt bytes:20--data is:8000000180000003

No problem, before looking at the offset of the next entry in this record

Current block:00000004--Offset:00152--cnt bytes:02--data is:fff2

We find that fff2 is obviously a negative complement. There is a conversion to a negative number of-14.

Then the next one is

154-140,140

Check it out.

[root@ora12ctest test] #. / bcview test.ibd 16 140 10 | more

Current block:00000004--Offset:00140--cnt bytes:20--data is:8000000180000005

We have found this record (Borel 1 Magazine 5).

So the original (BRV 1 ARV 1)-- > (BRV 1 ARV 5) has become due to the addition of (BRV 1 ARV 3).

(BRV 1 ARV 1)-- > (BRV 1 ARV 3)-- > (BRV 1 ARV 5)

This proves our point of view, that is, if we first follow the key value of the auxiliary index at the leaf node of the B + number

Sort and then sort by the value of PRIMARY. And order by auxiliary index KEY,primary key

Just now we have seen it with the naked eye (BRV 1 ARV 1)-- > (BRV 1 ARV 3)-- > (BRV 1 ARV 5)

However, we are adding some irregular ones to take a look.

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)

Run with the program and have a look.

[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->

Obviously, the running of the program also validates our results. The order in which we insert is out of order, but what we see is

The secondary index is sorted by the primary key A with the same sort by column B.

This program can only be used for this column, and the amount of data is small, so it will not work if the B + tree index is split, and the inserted value must be

Positive numbers should not be negative and 0 minus the highest positive number in INNODB is the sign 1, which is different from CAccord Category +, and his method of calculation has not been found yet.

So it can be obtained simply with A ^ 0X80000000, and can only be obtained on Little_endian platforms such as LINUX

First you need to use. / bcview and. / mysqlblock

To determine the PAGE NO of the secondary index, as mentioned above. Then use yes. / a.out test.ibd 4 4 to find the page number.

The table must be:

Create table test (an int,b int,primary key (a), key (b))

Separate table spaces. Because I am dead everywhere, all I can live is to read the linked list structure in the leaf node. I have no problem with the 5.7INNODB engine execution.

The line format is:

Mysql > select * from INNODB_SYS_TABLES where name='test/test'

->

+-+

| | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |

+-+

| | 40 | test/test | 33 | 5 | 24 | Barracuda | Dynamic | 0 | Single |

+-+

But it should be fine in the default line format of 5.6 INNODB, but it hasn't been tested.

Attaching the code is simple:

Click (here) to collapse or open

# include

# include

# include

Void* reverse (void* pjinint length) / / Little_endian reverse

{

Int i

Char* s = (char*) (p)

Char* temp = (char*) calloc (1 dint length)

Memcpy (temp,s,length)

For (iSuppli)

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.