如何證明INNODB輔助索引葉子結(jié)點KEY值相同的按照PRIMARY KEY排序
接http://blog.itpub.net/7728585/viewspace-2126305/
RR模式下NEXT-KEY LOCK范圍到底有多大
證明觀點:
1、對輔助索引的頁中鏈表進(jìn)行分析,如果在輔助索引頁內(nèi)的鏈表按照首先是KEY排序然后KEY相同的按照PRIMARY KEY排序那么基本就驗證了我們的說法
這個隨后可以補(bǔ)上
這篇文章用到了自制工具
./bcview和./
mysqlblock
在網(wǎng)盤
http://pan.baidu.com/s/1num76RJ
同時很多理論知識來自
http://blog.itpub.net/7728585/viewspace-2065464/
http://blog.itpub.net/7728585/viewspace-2063921/
等文章
mysql> create table test (a int,b int,primary key(a),key(b));
Query OK, 0 rows affected (0.08 sec)
mysql> insert into test values(1,1);
Query OK, 1 row affected (0.08 sec)
使用mysqlblock查看得到
current read blocks is : 3 --This Block is data blocks( index pages)!
current read blocks is : 4 --This Block is data blocks( index pages)!
這里面應(yīng)該是主鍵的B+樹第一個結(jié)點和輔助索引B+樹的第一個結(jié)點
page 4 應(yīng)該就是輔助索引,我們進(jìn)行驗證查看
從38字節(jié)到74字節(jié)的是INDEX HEADER,查看他的最后8個字節(jié)是index ID
和INNODB_SYS_INDEXES中進(jìn)行對比
current block:00000003--Offset:00066--cnt bytes:08--data is:0000000000000029
current block:00000004--Offset:00066--cnt bytes:08--data is:000000000000002a
得到INDEX_ID 0X29 0X2A 就是10進(jìn)制41 42
mysql> select * from information_schema.INNODB_SYS_INDEXES where index_id in (41,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.01 sec)
查看
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)
可以確定41 42 就是test表的主鍵和輔助索引,同時確認(rèn)了current block:00000004就是輔助索引存儲數(shù)據(jù)的
唯一一個葉子結(jié)點也是根結(jié)點(因為數(shù)據(jù)很少),
那么我們對page 04進(jìn)行查看
./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 --偏移量
696e66696d756d0002 --"infimum\0"
確定了第一行的偏移量0X1b級27 級第一行的位置為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
這里聚集索引塊和輔助索引記錄的東西就一樣了,
聚集索引page03
80000001000000000707a70000011b011080000001
其中包含了
offset ----cluster key fields (N bytes)
transaction id (6 bytes)
roll pointer (7 bytes)
non-key fields (M bytes)
000000000707a70000011b0110這13個字節(jié)就是transaction id 和roll pointer
我們回到主題討論輔助索引PAGE 4
80000001 b列
80000001 a列
其實就是1和1,第15位的1應(yīng)該是MYSQL符號位的表示
我們找到了,然后我們插入
insert into test values(5,1);
mysql> insert into test values(5,1);
Query OK, 1 row affected (0.03 sec)
mysql> commit;
通過偏移量進(jìn)行找到這個記錄
[root@ora12ctest test]# ./bcview test.ibd 16 124 2|more
current block:00000004--Offset:00124--cnt bytes:02--data is:000e
偏移量0X0E就是14
那么5 1 在輔助索引頁PAGE 4中的位置是126+14=140
./bcview test.ibd 16 140 30|more
current block:00000004--Offset:00140--cnt bytes:30--data is:800000018000000500000000000000000000000000000000000000000000
80000001 b列
80000005 a列
這個時候?qū)嶋H上是 (B:1 A:1)-->(B:1 A:5)
這個時候我們插入
mysql> insert into test values(3,1);
Query OK, 1 row affected (0.00 sec)
回過頭我們再次查看第一條記錄(B:1,A:1)下一條記錄的便宜量
[root@ora12ctest test]# ./bcview test.ibd 16 124 2|more
current block:00000004--Offset:00124--cnt bytes:02--data is:001c
發(fā)現(xiàn)已經(jīng)改變了變?yōu)榱?X1C為28我們找一下看看是不是我們新插入的(B:1,A:3)
126+28=154
./bcview test.ibd 16 154 20|more
current block:00000004--Offset:00154--cnt bytes:20--data is:8000000180000003
沒有問題,在查看這條記錄的下一條的偏移量
current block:00000004--Offset:00152--cnt bytes:02--data is:fff2
我們發(fā)現(xiàn)fff2明顯是負(fù)數(shù) 補(bǔ)碼存在轉(zhuǎn)換為負(fù)數(shù)為-14
則下一條就是
154-14=140
查看就是
[root@ora12ctest test]# ./bcview test.ibd 16 140 10|more
current block:00000004--Offset:00140--cnt bytes:20--data is:8000000180000005
我們找到了(B:1,A:5)的這條記錄。
那么原始的(B:1 A:1)-->(B:1 A:5) 由于(B:1 A:3)的加入變?yōu)榱?br />
(B:1 A:1)-->(B:1 A:3)-->(B:1 A:5)
由此證明了我們的觀點,就是在B+數(shù)的葉子結(jié)點如果先按照輔助索引的KEY值
排序然后按照PRIMARY的值排序。及order by 輔助索引KEY,primary key
剛才肉眼已經(jīng)看到了
(B:1 A:1)-->(B:1 A:3)-->(B:1 A:5)
然我們在加入一些無規(guī)則的來看看。
mysql> insert into test values(4,2);
Query OK, 1 row affected (0.59 sec)
mysql> insert into test values(10,4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(7,4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(8,5);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(11,5);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(20,6);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(21,6);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(19,7);
Query OK, 1 row affected (0.03 sec)
mysql> insert into test values(16,7);
Query OK, 1 row affected (0.01 sec)
用程序跑一下看看
[root@ora12ctest test]# ./a.out test.ibd 4
Index_no is:42
find first one record!
B:1,A:1-->
B:1,A:3-->
B:1,A:5-->
B:2,A:4-->
B:4,A:7-->
B:4,A:10-->
B:5,A:8-->
B:5,A:11-->
B:6,A:20-->
B:6,A:21-->
B:7,A:16-->
B:7,A:19-->
顯然程序的運行也驗證我們的結(jié)果。。我們插入的順序是無序的,但是查看到的是
輔助索引按照B列排序相同的按照主鍵A進(jìn)行排序。
本程序只能用于這個列子,并且數(shù)據(jù)量不多,如果造成了B+樹索引分裂肯定不行,并且插入的值必須為
正數(shù)不要為負(fù)數(shù)和0,INNODB中正數(shù)的最高為符號為1這個和C/C++不同,暫時沒有找到他的計算方式
所以簡單的用
A^0X80000000來得到,同時只能是Little_endian 平臺 如LINUX
首先你要使用
./bcview和./mysqlblock
來確定輔助索引的PAGE NO才行,就像上面說的。然后使用
是 ./a.out test.ibd 4 4就是找到的page號。
表必須是:
create table test (a int,b int,primary key(a),key(b));
單獨表空間。因為我任何地方都是寫死了的,活的只有讀取葉子結(jié)點內(nèi)的鏈表結(jié)構(gòu)而已。我在5.7 INNODB引擎執(zhí)行沒有問題。
行格式為:
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 |
+----------+-----------+------+--------+-------+-------------+------------+---------------+------------+
但是應(yīng)該在5.6 INNODB默認(rèn)的行格式下也沒問題,但是沒測試過。
附上代碼很簡單:
-
#include<stdio.h>
-
#include<stdlib.h>
-
#include<string.h>
-
-
void* reverse(void* p,int length) //Little_endian reverse
-
{
-
int i;
-
char* s= (char*)(p);
-
char* temp = (char*)calloc(1,length);
-
memcpy(temp,s,length);
-
-
-
for(i=0;i<length;i++)
-
{
-
s[i] = temp[length-1-i];
-
}
-
free(temp);
-
temp=NULL;
-
return p;
-
}
-
-
-
-
int main(int argc,char *argv[])
-
{
-
FILE* fd;
-
long blofset;
-
short level;
-
long int index_no;
-
short initof;
-
int B;
-
int A;
-
int reofset;
-
-
-
if(argc != 3 )
-
{
-
printf("USEAGE ERROR useage:./tool dbf pageno\n");
-
exit(3);
-
}
-
-
if(!(fd = fopen(argv[1],"r")))
-
{
-
perror("error:");
-
exit(1);
-
}
-
-
sscanf(argv[2],"%ld",&blofset);
-
fseek(fd,blofset*16*1024,SEEK_SET);
-
fseek(fd,64,SEEK_CUR);
-
fread(&level,2,1,fd);
-
fread(&index_no,8,1,fd);
-
reverse(&level,2);
-
reverse(&index_no,8);
-
fseek(fd,23,SEEK_CUR);
-
fread(&initof,2,1,fd);
-
reverse(&initof,2);
-
printf("Index_no is:%ld\n",index_no);
-
if(initof != 0 )
-
{
-
printf("find first one record!\n");
-
while(1)
-
{
-
fseek(fd,initof-2,SEEK_CUR);
-
fread(&initof,2,1,fd);
-
reverse(&initof,2);
-
if(initof == 0)
-
{
-
break;
-
}
-
else
-
{
-
fread(&B,4,1,fd);
-
fread(&A,4,1,fd);
-
fseek(fd,-8,SEEK_CUR);
-
reverse(&B,4);
-
reverse(&A,4);
-
A=A^0X80000000;
-
B=B^0X80000000;
-
printf("B:%d,A:%d-->\n",B,A);
-
}
-
-
}
-
}
-
else
-
{
-
printf("no record find!\n");
-
exit(2);
-
}
-
}
編譯用gcc test.c 得到a.out跑就行了.
是
./a.out test.ibd 4
這里引入另外一個問題
MYSQL中表記錄返回的順序問題。詳細(xì)參考下面:
http://blog.itpub.net/7728585/viewspace-2126470/