如何证明INNODB辅助索引叶子结点KEY值相同的按照PRIMARY KEY排序

接http://blog.itpub.net/7728585/viewspace-2126305/
 RR模式下NEXT-KEY LOCK范围到底有多大
 
证明观点：
1、对辅助索引的页中链表进行分析,如果在辅助索引页内的链表按照首先是KEY排序然后KEY相同的按照PRIMARY KEY排序那么基本就验证了我们的说法
   这个随后可以补上

这篇文章用到了自制工具./bcview和./mysqlblock
在网盘
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)!
这里面应该是主键的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>
  4. void* reverse(void* p,int length) //Little_endian  reverse
  5. {
  6.         int i;
  7.         char* s= (char*)(p);
  8.         char* temp = (char*)calloc(1,length);
  9.         memcpy(temp,s,length);
  10.         for(i=0;i<length;i++)
  11.         {
  12.         s[i] = temp[length-1-i];
  13.         }
  14.         free(temp);
  15.         temp=NULL;
  16.         return p;
  17. }
  18. int main(int argc,char *argv[])
  19. {
  20.         FILE* fd;
  21.         long blofset;
  22.         short level;
  23.     long int index_no;
  24.         short initof;
  25.         int B;
  26.         int A;
  27.         int reofset;
  28.         if(argc != 3 )
  29.         {
  30.                 printf("USEAGE ERROR useage:./tool dbf pageno\n");
  31.                 exit(3);
  32.         }
  33.         if(!(fd = fopen(argv[1],"r")))
  34.         {
  35.                 perror("error:");
  36.                 exit(1);
  37.         }
  38.     sscanf(argv[2],"%ld",&blofset);
  39.         fseek(fd,blofset*16*1024,SEEK_SET);
  40.         fseek(fd,64,SEEK_CUR);
  41.         fread(&level,2,1,fd);
  42.         fread(&index_no,8,1,fd);
  43.         reverse(&level,2);
  44.         reverse(&index_no,8);
  45.         fseek(fd,23,SEEK_CUR);
  46.         fread(&initof,2,1,fd);
  47.         reverse(&initof,2);
  48.         printf("Index_no is:%ld\n",index_no);
  49.         if(initof != 0 )
  50.         {
  51.                 printf("find first one record!\n");
  52.                 while(1)
  53.                 {
  54.                         fseek(fd,initof-2,SEEK_CUR);
  55.                         fread(&initof,2,1,fd);
  56.                         reverse(&initof,2);
  57.                         if(initof == 0)
  58.                         {
  59.                                 break;
  60.                         }
  61.                         else
  62.                         {
  63.                                 fread(&B,4,1,fd);
  64.                                 fread(&A,4,1,fd);
  65.                                 fseek(fd,-8,SEEK_CUR);
  66.                                 reverse(&B,4);
  67.                                 reverse(&A,4);
  68.                                 A=A^0X80000000;
  69.                                 B=B^0X80000000;
  70.                                 printf("B:%d,A:%d-->\n",B,A);
  71.                         }
  72.                 }
  73.         }
  74.         else
  75.         {
  76.                 printf("no record find!\n");
  77.                 exit(2);
  78.         }
  79. }

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

这里引入另外一个问题
MYSQL中表记录返回的顺序问题。详细参考下面：
http://blog.itpub.net/7728585/viewspace-2126470/

时间： 2024-07-30 23:51:53

如何证明INNODB辅助索引叶子结点KEY值相同的按照PRIMARY KEY排序的相关文章

ORACLE 索引和MYSQL INNODB 辅助索引对NULL的处理区别

ORACLE 索引和MYSQL INNODB 辅助索引对NULL的处理 我们清楚ORACLE中的b+索引是对键值的NULL进行存储的,以致于我们 IS NULL这种肯定是用不到索引的, 当然这提及的ORACLE表为堆表,索引为单列B+树索引,(有一种优化方式为建立组合索引如create index xx on tab(a,'1') 这样来保证索引记录NULL值 这样DUMP出来为 ..... row#11[7886] flag: ------, lock: 2, len=12 col 0; NU

ios-如何设置NSDictionary的key值

问题描述 如何设置NSDictionary的key值 我想传递预定key值的dictionary到多个方法中,我以前见过其他的类实现过,但是我不知道应该怎么设置,我在m文件中用到的是这样: NSString *name = [dictionary objectForKey:kObjectsName]; NSDate *date = [dictionary objectForKey:kObjectsDate]; 如何设置? 解决方案 在.m文件中放#define声明 #define kObject

关于primary key和foreign key的问题处理

今天碰到一个问题,在drop primary key的时候,提示ORA-02273的错误. SQL> ALTER TABLE AR1_ACCOUNT DROP PRIMARY KEY; ALTER TABLE AR1_ACCOUNT DROP PRIMARY KEY * ERROR at line 1: ORA-02273: this unique/primary key is referenced by some foreign keys 通过下面的字典表查找的关联的表 SQL> SELEC

防御性编程习惯：求出链表中倒数第 m 个结点的值及其思想的总结

防御性编程习惯 程序员在编写代码的时候,预料有可能出现问题的地方或者点,然后为这些隐患提前制定预防方案或者措施,比如数据库发生异常之后的回滚,打开某些资源之前,判断图片是否存在,网络断开之后的重连次数或者是否连接备用网络,除法运算中的除数问题,函数或者类在接受数据的时候的过滤情况,比如如果输入一个指针参数,是否需要判断是不是空指针?输入一个字符串参数,是否需要判断字符串空否--总的来说就是防止出现不可预见的事情,设计出鲁棒性的代码. 看下面的例子 输入一个链表,输出链表中倒数第 m 个结点额内容

叶子结点-二叉树的c++编程，我们做课程设计

问题描述 二叉树的c++编程,我们做课程设计 二叉树的前序,中序,后序遍历,求叶子结点,结点个数,和深度的编程 解决方案 用MatLAB做电路课程设计教你做课程设计! 解决方案二: http://www.cnblogs.com/elleniou/archive/2012/05/03/2480042.html

微信自定义菜单click事件的key值可以是中文吗？

问题描述 微信自定义菜单click事件的key值可以是中文吗? 我发现我的key值设成中文的话,好像没效果,求大神帮忙解决?????? 解决方案 是不是乱码了?微信都是以utf-8编码发送数据的.你是以utf-8解码的吗?

android百度地图，一个工程中有多个包，申请了一个key,值显示网格

问题描述 android百度地图,一个工程中有多个包,申请了一个key,值显示网格 请各位大神指教,新key机制我不大懂什么意思,是有多少个包就要申请多少个key,或配置多少次吗?请求解决方法. 解决方案 针对地图包,肯定一个工程就一个啊,你说的什么意思 解决方案二: 只显示网格,不显示地图说明你的key有问题,重新申请一个key然后更改代码里的key值就好了,我当时做的时候也有这个问题 ,改了key值就好了 解决方案三: 你好,其实你申请key的时候应该是对应你电脑上的keystone中的sh

ios-nsdictionary获得指定key值

问题描述 nsdictionary获得指定key值 在nsdictionary中保存json解析的结果: {statusCode"":""200""body"":[ {status"":""success""remarks"":null }]data"":[abcd"":[ {category"&q

播放地址-有研究过PPTV视频地址获取的吗？KEY值算法问题

问题描述 有研究过PPTV视频地址获取的吗?KEY值算法问题 搞了很久,网上的算法都过期了,求最新的额!头疼++++++++ 解决方案 我这有dex,文件会反编译的大神可以帮我下吗?