如何證明INNODB輔助索引葉子結點KEY值相同的按照PRIMARY KEY排序

gaopengtttt發表於2016-10-14
索引排序
接http://blog.itpub.net/7728585/viewspace-2126305/
 RR模式下NEXT-KEY LOCK範圍到底有多大
 
證明觀點：
1、對輔助索引的頁中連結串列進行分析,如果在輔助索引頁內的連結串列按照首先是KEY排序然後KEY相同的按照PRIMARY KEY排序那麼基本就驗證了我們的說法
   這個隨後可以補上

這篇文章用到了自制工具./bcview和./mysqlblock
在網盤

同時很多理論知識來自
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)!
這裡面應該是主鍵的B+樹第一個結點和輔助索引B+樹的第一個結點
page 4 應該就是輔助索引，我們進行驗證檢視
從38位元組到74位元組的是INDEX HEADER，檢視他的最後8個位元組是index ID
和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
得到INDEX_ID 0X29 0X2A 就是10進位制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表的主鍵和輔助索引，同時確認了current block:00000004就是輔助索引儲存資料的
唯一一個葉子結點也是根結點(因為資料很少)，
那麼我們對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  --偏移量
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個位元組就是transaction id 和roll pointer
我們回到主題討論輔助索引PAGE 4
80000001 b列
80000001 a列
其實就是1和1,第15位的1應該是MYSQL符號位的表示
我們找到了，然後我們插入
insert into test values(5,1);
mysql> insert into test values(5,1);
Query OK, 1 row affected (0.03 sec)


mysql> commit;
透過偏移量進行找到這個記錄
[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列


這個時候實際上是 (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
發現已經改變了變為了0X1C為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
我們發現fff2明顯是負數 補碼存在轉換為負數為-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)的加入變為了
(B:1 A:1)-->(B:1 A:3)-->(B:1 A:5)
由此證明了我們的觀點,就是在B+數的葉子結點如果先按照輔助索引的KEY值
排序然後按照PRIMARY的值排序。及order by 輔助索引KEY,primary key

剛才肉眼已經看到了(B:1 A:1)-->(B:1 A:3)-->(B:1 A:5)
然我們在加入一些無規則的來看看。
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-->

顯然程式的執行也驗證我們的結果。。我們插入的順序是無序的，但是檢視到的是
輔助索引按照B列排序相同的按照主鍵A進行排序。

本程式只能用於這個列子，並且資料量不多，如果造成了B+樹索引分裂肯定不行，並且插入的值必須為
正數不要為負數和0,INNODB中正數的最高為符號為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));
單獨表空間。因為我任何地方都是寫死了的，活的只有讀取葉子結點內的連結串列結構而已。我在5.7 INNODB引擎執行沒有問題。
行格式為：
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     |
+----------+-----------+------+--------+-------+-------------+------------+---------------+------------+
但是應該在5.6 INNODB預設的行格式下也沒問題，但是沒測試過。

附上程式碼很簡單：

點選(此處)摺疊或開啟

  1. #include<stdio.h>
  2. #include<stdlib.h>
  3. #include<string.h>

  5. void* reverse(void* p,int length) //Little_endian  reverse
  6. {
  7.         int i;
  8.         char* s= (char*)(p);
  9.         char* temp = (char*)calloc(1,length);
  10.         memcpy(temp,s,length);


  13.         for(i=0;i<length;i++)
  14.         {
  15.         s[i] = temp[length-1-i];
  16.         }
  17.         free(temp);
  18.         temp=NULL;
  19.         return p;
  20. }



  24. int main(int argc,char *argv[])
  25. {
  26.         FILE* fd;
  27.         long blofset;
  28.         short level;
  29.     long int index_no;
  30.         short initof;
  31.         int B;
  32.         int A;
  33.         int reofset;


  36.         if(argc != 3 )
  37.         {
  38.                 printf("USEAGE ERROR useage:./tool dbf pageno\n");
  39.                 exit(3);
  40.         }

  42.         if(!(fd = fopen(argv[1],"r")))
  43.         {
  44.                 perror("error:");
  45.                 exit(1);
  46.         }

  48.     sscanf(argv[2],"%ld",&blofset);
  49.         fseek(fd,blofset*16*1024,SEEK_SET);
  50.         fseek(fd,64,SEEK_CUR);
  51.         fread(&level,2,1,fd);
  52.         fread(&index_no,8,1,fd);
  53.         reverse(&level,2);
  54.         reverse(&index_no,8);
  55.         fseek(fd,23,SEEK_CUR);
  56.         fread(&initof,2,1,fd);
  57.         reverse(&initof,2);
  58.         printf("Index_no is:%ld\n",index_no);
  59.         if(initof != 0 )
  60.         {
  61.                 printf("find first one record!\n");
  62.                 while(1)
  63.                 {
  64.                         fseek(fd,initof-2,SEEK_CUR);
  65.                         fread(&initof,2,1,fd);
  66.                         reverse(&initof,2);
  67.                         if(initof == 0)
  68.                         {
  69.                                 break;
  70.                         }
  71.                         else
  72.                         {
  73.                                 fread(&B,4,1,fd);
  74.                                 fread(&A,4,1,fd);
  75.                                 fseek(fd,-8,SEEK_CUR);
  76.                                 reverse(&B,4);
  77.                                 reverse(&A,4);
  78.                                 A=A^0X80000000;
  79.                                 B=B^0X80000000;
  80.                                 printf("B:%d,A:%d-->\n",B,A);
  81.                         }

  83.                 }
  84.         }
  85.         else
  86.         {
  87.                 printf("no record find!\n");
  88.                 exit(2);
  89.         }
  90. }

編譯用gcc test.c 得到a.out跑就行了.
是 ./a.out test.ibd 4

這裡引入另外一個問題
MYSQL中表記錄返回的順序問題。詳細參考下面：
http://blog.itpub.net/7728585/viewspace-2126470/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2126344/，如需轉載，請註明出處，否則將追究法律責任。

相關文章