开发者社区> xaubllxwtvaqiu> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

浅析InnoDB索引结构(2)

简介: 浅析InnoDB索引结构
+关注继续查看


3.2 特点2:聚集索引非叶子节点存储指向子节点的指针

对上面的测试表继续写入新数据,直到聚集索引树从一层分裂成两层。

我们根据旧文 InnoDB表聚集索引层高什么时候发生变化 里的计算方式,推算出来预计一个叶子节点最多可存储111条记录,因此在插入第112条记录时,就会从一层高度分裂成两层高度。经过实测,也的确是如此。

[root@yejr.me] [innodb]>select count(*) from t1;
+----------+
| count(*) |
+----------+
|      112 |
+----------+

[root@yejr.me]# innblock innodb/t1.ibd scan 16
...
===INDEX_ID:238
level1 total block is (1)
block_no:     3,level:   1|*|
level0 total block is (2)
block_no:     5,level:   0|*|block_no:     6,level:   0|*|

此时可以看到根节点依旧是pageno=3,而叶子节点变成了[5, 6]两个page。由此可知,根节点上应该只有两条物理记录,存储着分别指向pageno=[5, 6]这两个page的指针。

我们解析下3号page,看看它的具体结构:

[root@yejr.me]# innodb_space -s ibdata1 -T innodb/t1 -p 3 page-dump

...
records:
{:format=>:compact,
:offset=>125,
:header=>
{:next=>138,
:type=>:node_pointer,
:heap_number=>2,
:n_owned=>0,
:min_rec=>true, #第一条记录是min_key
:deleted=>false,
:nulls=>[],
:lengths=>{},
:externs=>[],
:length=>5},
:next=>138,
:type=>:clustered,
#第一条记录,只存储key值
:key=>[{:name=>"id", :type=>"INT UNSIGNED", :value=>1}],
:row=>[],
:sys=>[],
:child_page_number=>5, #value值是指向的叶子节点pageno=5
:length=>8} #整条记录消耗8字节,除去key值4字节外,指针也需要4字节

{:format=>:compact,
:offset=>138,
:header=>
{:next=>112,
:type=>:node_pointer,
:heap_number=>3,
:n_owned=>0,
:min_rec=>false,
:deleted=>false,
:nulls=>[],
:lengths=>{},
:externs=>[],
:length=>5},
:next=>112,
:type=>:clustered,
#第二条记录,只存储key值
:key=>[{:name=>"id", :type=>"INT UNSIGNED", :value=>56}],
:row=>[],
:sys=>[],
:child_page_number=>6, #value值是指向的叶子节点pageno=6
:length=>8}

优化建议2: 索引列数据长度越小越好,这样索引树存储效率越高,在非叶子节点能存储越多数据,延缓索引树层高分裂的速度,平均搜索效率更高


3.3 特点3:辅助索引同时会存储主键索引列值

在辅助索引中,总是同时会存储主键索引(或者说聚集索引)的列值,其作用就是在对辅助索引扫描时,可以从叶子节点直接得到对应的聚集索引值,并可根据该值回表查询获取行数据(如果需要回表查询的话)。这个特性也被称为Index Extensions(5.6版本之后的优化器新特性,详见 Use of Index Extensions)。

此外,在辅助索引的非叶子节点中,索引记录的key值是索引定义的列值,而对应的value值则是聚集索引列值(简称PKV)。如果辅助索引定义时已经包含了部分聚集索引列,则索引记录的value值是未被包含的余下的聚集索引列值。

创建如下测试表:

CREATE TABLE `t3` (
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b` int(10) unsigned NOT NULL DEFAULT '0',
`c` varchar(20) NOT NULL DEFAULT '',
`d` varchar(20) NOT NULL DEFAULT '',
`e` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`a`,`b`),
KEY `k1` (`c`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

随机插入一些测试数据:

# 调用shell脚本写入500条数据
[root@yejr.me]# cat insert.sh
#!/bin/bash
. ~/.bash_profile
cd /data/perconad
i=1
max=500
while [ $i -le $max ]
do
mysql -Smysql.sock -e "insert ignore into t3 select
rand()1024, rand()1024, left(md5(uuid()),20) ,
left(uuid(),20), left(uuid(),20);" innodb
i=`expr $i + 1`
done

# 实际写入498条数据(其中有2条主键冲突失败)
[root@yejr.me] [innodb]>select count(*) from t3;
+----------+
| count(*) |
+----------+
| 498 |
+----------+

解析数据结构:

# 主键
[root@test1 perconad]# innodb_space -s ibdata1 -T innodb/t2 space-indexes
id name root fseg fseg_id used allocated fill_factor
245 PRIMARY 3 internal 1 1 1 100.00%
245 PRIMARY 3 leaf 2 5 5 100.00%
246 k1 4 internal 3 1 1 100.00%
246 k1 4 leaf 4 2 2 1

[root@yejr.me]# innodb_space -s ibdata1 -T innodb/t2 -p 4 page-dump
...
records:
{:format=>:compact,
:offset=>126,
:header=>
{:next=>164,
:type=>:node_pointer,
:heap_number=>2,
:n_owned=>0,
:min_rec=>true,
:deleted=>false,
:nulls=>[],
:lengths=>{"c"=>20},
:externs=>[],
:length=>6},
:next=>164,
:type=>:secondary,
:key=>
[{:name=>"c", :type=>"VARCHAR(80)", :value=>"00a5d42dd56632893b5f"},
{:name=>"b", :type=>"INT UNSIGNED", :value=>323}],
:row=>
[{:name=>"a", :type=>"INT UNSIGNED", :value=>310},
{:name=>"b", :type=>"INT UNSIGNED", :value=>9}],
# 此处给解析成b列的值了,实际上是指向叶子节点的指针,即child_page_number=9
# b列真实值是323
:sys=>[],
:child_page_number=>335544345,
# 此处解析不准确,实际上是下一条记录的record header,共6个字节
:length=>36}

{:format=>:compact,
:offset=>164,
:header=>
{:next=>112,
:type=>:node_pointer,
:heap_number=>3,
:n_owned=>0,
:min_rec=>false,
:deleted=>false,
:nulls=>[],
:lengths=>{"c"=>20},
:externs=>[],
:length=>6},
:next=>112,
:type=>:secondary,
:key=>
[{:name=>"c", :type=>"VARCHAR(80)", :value=>"7458824a39892aa77e1a"},
{:name=>"b", :type=>"INT UNSIGNED", :value=>887}],
:row=>
[{:name=>"a", :type=>"INT UNSIGNED", :value=>623},
{:name=>"b", :type=>"INT UNSIGNED", :value=>10}],
# 同上,其实是child_page_number=10,而非b列的值
:sys=>[],
:child_page_number=>0,
:length=>36} #数据长度16字节

顺便说下,辅助索引上没存储TRX_ID, ROLL_PTR这些(他们只存储在聚集索引上)。

上面用innodb_ruby工具解析的非叶子节点部分内容不够准确,所以我们用二进制方式打开数据文件二次求证确认:

# 此处也可以用 hexdump 工具
[root@yejr.me]# vim -b path/t3.ibd
...
:%!xxd

# 找到辅助索引所在的那部分数据
0010050: 0002 0272 0000 00e1 0000 0002 01b2 0100 ...r............
0010060: 0200 1b69 6e66 696d 756d 0003 000b 0000 ...infimum......
0010070: 7375 7072 656d 756d 1410 0011 0026 3030 supremum.....&00
0010080: 6135 6434 3264 6435 3636 3332 3839 3362 a5d42dd56632893b
0010090: 3566 0000 0143 0000 0136 0000 0009 1400 5f...C...6......
00100a0: 0019 ffcc 3734 3538 3832 3461 3339 3839 ....7458824a3989
00100b0: 3261 6137 3765 3161 0000 0377 0000 026f 2aa77e1a...w...o
00100c0: 0000 000a 0000 0000 0000 0000 0000 0000 ................

# 参考page物理结构方式进行解析,得到下面的结果
/ 第一条记录 /
1410 0011 0026, record header, 5字节
3030 6135 6434 3264 6435 3636 3332 3839 3362 3566,c='00a5d42dd56632893b5f',20B
0000 0143, b=323, 4B
0000 0136, a=310, 4B
0000 0009, child_pageno=9, 4B

/ 2 /
1400 0019 ffcc, record header
3734 3538 3832 3461 3339 3839 3261 6137 3765 3161, c='7458824a39892aa77e1a'
0000 0377, b=887
0000 026f, a=623
0000 000a, child_pageno=10

现在反过来看,上面用innodb_ruby工具解析出来的page-dump结果应该是这样的才对(我只选取一条记录,请自行对比和之前的不同之处):

{:format=>:compact,
:offset=>164,
:header=>
{:next=>112,
:type=>:node_pointer,
:heap_number=>3,
:n_owned=>0,
:min_rec=>false,
:deleted=>false,
:nulls=>[],
:lengths=>{"c"=>20},
:externs=>[],
:length=>6},
:next=>112,
:type=>:secondary,
:key=>
[{:name=>"c", :type=>"VARCHAR(80)", :value=>"7458824a39892aa77e1a"},
{:name=>"b", :type=>"INT UNSIGNED", :value=>887}],
:row=> [{:name=>"a", :type=>"INT UNSIGNED", :value=>623}],
:sys=>[],
:child_page_number=>10,
:length=>36}

可以看到,的确如前面所说,辅助索引的非叶子节点的value值存储的是聚集索引列值

优化建议3:辅助索引列定义的长度越小越好,定义辅助索引时,没必要显式的加上聚集索引列(5.6版本之后)


3.4 特点4:没有可用的聚集索引列时,会使用内置的ROW_ID作为聚集索引

创建几个像下面这样的表,使其选择内置的ROW_ID作为聚集索引:

[root@yejr.me] [innodb]> CREATE TABLE `tn1` (
`c1` int(10) unsigned NOT NULL DEFAULT 0,
`c2` int(10) unsigned NOT NULL DEFAULT 0
) ENGINE=InnoDB;

循环对几个表写数据:

insert into tt1 select 1,1;
insert into tt2 select 1,1;
insert into tt3 select 1,1;
insert into tt1 select 2,2;
insert into tt2 select 2,2;
insert into tt3 select 2,2;

查看 tn1 - tn3 表里的数据(这里由于innodb_ruby工具解析的结果不准确,所以我改用hexdump来分析):

tn1
000c060: 0200 1a69 6e66 696d 756d 0003 000b 0000 ...infimum......
000c070: 7375 7072 656d 756d 0000 1000 2000 0000 supremum.... ...
000c080: 0003 1200 0000 003d f6aa 0000 01d9 0110 .......=........
000c090: 0000 0001 0000 0001 0000 18ff d300 0000 ................
000c0a0: 0003 1500 0000 003d f9ad 0000 01da 0110 .......=........
000c0b0: 0000 0002 0000 0002 0000 0000 0000 0000 ................

tn2
000c060: 0200 1a69 6e66 696d 756d 0003 000b 0000 ...infimum......
000c070: 7375 7072 656d 756d 0000 1000 2000 0000 supremum.... ...
000c080: 0003 1300 0000 003d f7ab 0000 0122 0110 .......=....."..
000c090: 0000 0001 0000 0001 0000 18ff d300 0000 ................
000c0a0: 0003 1600 0000 003d feb0 0000 01db 0110 .......=........
000c0b0: 0000 0002 0000 0002 0000 0000 0000 0000 ................

tn3
000c060: 0200 1a69 6e66 696d 756d 0003 000b 0000 ...infimum......
000c070: 7375 7072 656d 756d 0000 1000 2000 0000 supremum.... ...
000c080: 0003 1400 0000 003d f8ac 0000 0123 0110 .......=.....#..
000c090: 0000 0001 0000 0001 0000 18ff d300 0000 ................
000c0a0: 0003 1700 0000 003e 03b3 0000 012a 0110 .......>.....*..
000c0b0: 0000 0002 0000 0002 0000 0000 0000 0000 ................

其中表示DB_ROW_ID的值分别是:

tn1
0003 12 => (1,1)
0003 15 => (2,2)

tn2
0003 13 => (1,1)
0003 16 => (2,2)

tn3
0003 14 => (1,1)
0003 17 => (2,2)

很明显,内置的DB_ROW_ID的确是在整个实例级别共享自增分配的,而不是每个表独享一个DB_ROW_ID序列

我们可以想象下,如果一个实例中有多个表都用到这个DB_ROW_ID的话,势必会造成并发请求的竞争/等待。此外也可能会造成主从复制环境下,从库上relay log回放时可能会因为数据扫描机制的问题造成严重的复制延迟问题。详情参考 从库数据的查找和参数slave_rows_search_algorithms

优化建议4:自行显示定义可用的聚集索引/主键索引,不要让InnoDB选择内置的DB_ROW_ID作为聚集索引,避免潜在的性能损失

篇幅已经有点大了,本次的浅析工作就先到这里吧,以后再继续。


4、几点总结

最后针对InnoDB引擎表,总结几条建议吧。

  1. 每个表都要有显式主键,最好是自增整型,且没有业务用途
  2. 无论是主键索引,还是辅助索引,都尽可能选择数据类型较小的列
  3. 定义辅助索引时,没必要显式加上主键索引列(针对MySQL 5.6之后)
  4. 行数据越短越好,如果每个列都是固定长的则更好(不是像VARCHAR这样的可变长度类型)

上述测试环境基于Percona Server 5.7.22:

# MySQL的版本是Percona Server 5.7.22-22,我自己下载源码编译的
[root@yejr.me#] mysql -Smysql.sock innodb
...
Server version: 5.7.22-22-log Source distribution
...
[root@yejr.me]> \s
...
Server version: 5.7.22-22-log Source distribution

Enjoy MySQL :)

            </div>

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
参加飞天加速计划后给我的感受
这是一篇关于我参加飞天加速计划后的体验感的小小文章,文采有限,多多包涵
238 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
PostgresChina2018_赖思超_PostgreSQL10_hash索引的WAL日志修改版final
立即下载
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载