InnoDB索引结构简析

对于这类的InnoDB索引结构简析和丰田sock的题，想必每位都是想知道的，接下来让小编为大家分享一下吧！

0.简介

InnoDB表索引有哪些特点，索引组成结构是怎样的？

1.InnoDB聚集索引的特点

我知道InnoDB引擎中的聚集索引组织表必须有聚集索引。

行数据存储在聚集索引的叶节点中，它们存储的相对顺序取决于它们在聚集索引中的顺序。我们这里指的是相对排序而不是物理排序，因为叶节点数据页中行数据的物理排序和相对排序可能不一致。我们稍后会解释这一点。

InnoDB聚集索引的选择顺序如下

如果存在显式定义的主键，则选择该主键作为聚集索引。

否则，选择第一个唯一索引，其中任何列都不能为NULL。

如果前两者都不存在，InnoDB选择内置的DB_ROW_ID作为聚集索引，并将其命名为GEN_CLUUST_INDEX。

特别说明DB_ROW_ID占用6个字节，每次递增，在整个实例内全局分配。也就是说，如果当前实例中有多个表使用内置的DB_ROW_ID作为聚集索引，当向这些表插入新数据时，内置的DB_ROW_ID值不连续，会发生跳跃。像这样

t1表的ROW_ID1,3,7,10t2表的ROW_ID2,4,5,6,8,92，InnoDB索引结构

InnoDB默认的索引数据结构采用B+树，索引数据存储在叶子节点中。

InnoDB的基本I/O存储单元是数据页，默认情况下一个页是16KB。正如我在简介中所说，每个页面本质上都保留了1/16的可用空间，用于将来的“可变长度”数据更新。因此，顺序插入的理想状态会导致碎片量最少。填充15/16的页面空间。如果任意写入，页面空间利用率约为1/2到15/16。

如果row_format=DYNAMIC|COMPRESSED，则最大索引长度为3072字节，如果row_format=REDUNDANT|COMPACT，则最大索引长度为767字节。如果页面大小不是默认的16KB，则最大索引长度也会相应更改。

接下来，我们验证InnoDB索引的各个基本结构特征。

首先，创建如下测试表

[rootyejrme][innodb]gt;CREATETABLE`t1``id`int10unsignedNOTNULLAUTO_INCRMENT,`c1`int10unsignedNOTNULLDEFAULT39;`c2`varchar100NOTNULL,`c3`varchar100NOTNULL,PRIMARYKEY`id`,KEY`c1``c1`ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;使用以下方法创建10个测试数据。

setuuid1=uuid;setuuid2=uuid;insertintot1select0,roundrand1024,uuid1,concatuuid1,uuid2;t1看一下表的整体结构。

innodb_space-sibdata1-Tinnodb/t1space-indexesidnamerootfsegfseg_idusedallocatedfill_factor238PRIMARY3internal111110000238PRIMARY3leaf200000239c14internal31110000239c14leaf40000innblockinnodb/t1ibdscan16===INDEX_ID33360238level0totalblockis1block_no:3,level:0||===INDEX_ID:239level0totalblockis1block_no:4,level:0||可见

索引ID索引类型根节点页码索引级别高度238主键索引聚集索引31239二级索引41

3、InnoDB索引功能验证

31功能1聚集索引叶节点存储整行数据。

首先，我们扫描第三页并截取第一条实际记录的内容。

[rootyejrme]第一条物理记录，id=1:key=gt;[],row=gt;[,],sys=gt;[,],length=gt;129,transaction_id=gt;10950,roll_pointer=gt;显然，数据的全部内容实际上都被存储了。

聚集索引树的键值为主键索引值i=10，聚集索引节点值为其他非聚集索引列c1、c2、c3以及隐藏列DB_TRX_ID和DB_ROLL_PTR。

优化建议1避免存储大对象数据，让每个叶子节点存储更多数据，降低碎片率，提高缓冲池利用率。另外，可以尽可能地防止溢出。

32特性2聚集索引非叶节点存储指向其子节点的指针。

继续向上面的测试表写入新数据，直到聚集索引树从一级分裂为两级。

根据上一篇文章《InnoDB表聚集索引的高度什么时候发生变化？》中的计算方法，我们计算出一个叶子节点最多可以存储111条记录，所以当插入第112条记录时就会进行分裂。从一层高到两层。层高。根据实际测试，确实如此。

[rootyejrme][innodb]gt;selectcountfromt1;+---------+|count|+----------+|112|+----------+[rootyejrme]innodb_space-sibdata1-Tinnodb/t1-p3page-dumprecords:externs=gt;[],length=gt;5,next=gt;138,type=gt;clustered，值为叶节点page指向到。=5:length=gt;8第二条记录的键值为key=gt;[],row=gt;[],sys=gt;[],child_page_number=gt;6,39;039;39;39;仅保存通话。写入500条数据的Shell脚本[rootyejrme]!/bin/bash~/bash_profilecd/data/perconadi=1max=500while[$i-le$max]domysql-Smysqlsock-e34;innodbi=`expr$i+1`DONEdefault键[Roottest1perconad]Innodb_space-Sibdata1-T2-P4PAGE-DUMPAGE-DUMPRECORDS3:Externs=[],LENGTH=GT;4,TYPE=GT;secondary,Key=GT;[,],row=gt;[,],b列的实际值为323:sys=gt;[],child_page_number=gt;335544345，同上。它实际上是child_page_number=10，而不是b列中的值。sys=>;[],child_page_number=gt;0,length=gt;36这里还可以使用hexdump工具[rootyejrme]来查找有二级索引的数据部分。001b696e66696d756d0003000b0000infimum0010070:73757072656d756d1410001100263030supremum000010080:61356434326464353636333238393362a5d42dd56632893b0010090:356600000143000001360000000914005fC600100a0:0019ffcc3734353838323461333938397458824a398900100b0:3261613737653161000003770000026f2aa77e1awo00100c0:0000000a000000000000000000000039;00a5d42dd56632893b5f39;7458824a39892aa77e1a34;000c090:0000000100000001000018ffd3000000000c0a0:000316000000003dfeb0000001db0110=000c0b03336000000002000000020000000000000000tn3000c060:02001a696e66696d756d0003000b0000infimum000c070:73757072656d756d0000100020000000supremum000c080:000314000000003df8ac000001230110=MySQL版本为PerconaServer5722-22。我下载了源码，自己编译。[rootyejrme]mysql-SmysqlsockinnodbServerversion:5722-22-logSourcedistribution[rootyejrme]gt;sServerversion:5722-22-logSourcedistribution