衣舞晨风
0%

查看MySQL InnoDB 表索引的高度

发表于 更新于 分类于
本文字数： 7.8k 阅读时长 ≈ 7 分钟

在看《MySQL技术内幕：InnoDB存储引擎》B+树索引章节中看到这么一句话：

但是B+索引在数据库中有一个特点就是高扇出性，因此在数据库中，B+树的高度一般都在2-4层，也就是说查找某一键值的行记录时最多只需要2-4次IO。因为当前一般的机械磁盘每秒至少可以做100次IO，2-4次的IO意味着查询时间只需要0.02-0.04秒。

那么，当一个表很大的时候，索引还是是2-4层吗？那么这时搜索子节点会不会很慢？

下面通过user库中的uc_users表，来验证一下。

表结构如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CREATE TABLE `uc_users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(40) DEFAULT NULL COMMENT 'username (login principal)',
  `connection_id` bigint(20) DEFAULT NULL COMMENT 'username (login principal)',
  `email` varchar(254) DEFAULT NULL COMMENT 'email (login principal)',
  `email_verified` tinyint(1) NOT NULL DEFAULT '0',
  `phone_number` varchar(20) DEFAULT NULL COMMENT 'mobile phone number (login principal?)',
  `phone_verified` tinyint(1) NOT NULL DEFAULT '0',
  `display_name` varchar(40) DEFAULT NULL COMMENT 'name for displaying',
  `nickname` varchar(40) DEFAULT NULL COMMENT 'nickname',
  `given_name` varchar(40) DEFAULT NULL COMMENT 'given name or first name',
  `family_name` varchar(40) DEFAULT NULL COMMENT 'family name or surname',
  `middle_name` varchar(40) DEFAULT NULL COMMENT 'middle name',
  `avatar_url` varchar(2000) DEFAULT NULL COMMENT 'avatar image url',
  `password` varchar(255) DEFAULT NULL COMMENT 'password hash (login credential)',
  `password_strength` int(11) DEFAULT NULL COMMENT 'password strength',
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `locked` tinyint(1) NOT NULL DEFAULT '0',
  `type` smallint(6) NOT NULL COMMENT 'type (for lite-auth)',
  `source` varchar(100) DEFAULT NULL COMMENT 'where user come from',
  `last_login_at` timestamp NULL DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `birth_date` varchar(10) DEFAULT NULL,
  `zone_info` varchar(20) DEFAULT NULL,
  `locale` varchar(20) DEFAULT NULL,
  `website` varchar(2000) DEFAULT NULL,
  `address` varchar(1000) DEFAULT NULL,
  `metadata` varchar(5000) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `external_source` varchar(255) DEFAULT NULL,
  `external_id` varchar(255) DEFAULT NULL,
  `reg_client_id` varchar(128) DEFAULT NULL,
  `version` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_phone_number` (`phone_number`),
  KEY `index_email` (`email`),
  KEY `index_username` (`username`),
  KEY `index_created_at` (`created_at`),
  KEY `index_updated_at` (`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=2022228456 DEFAULT CHARSET=utf8 COMMENT='user table';

索引信息如下：

数据量：5968 8046

查看index level 通过innodb_ruby查看。
具体语法参见：https://github.com/jeremycole/innodb_ruby/wiki

方法一

1、登陆MySQL对应机器，找到MySQL数据存放位置

1
ps -ef | grep MySQL

2、找到ibdata文件位置

3、执行如下命令

1
innodb_space -s ibdata1 -T user/uc_users space-indexes

执行结果如下：

  • id：表示此索引的ID。
  • name：索引的名称，PRIMARY代表的就是聚集索引，因为InnoDB表是聚集索引组织表，行记录就是聚集索引；idx_c就是辅助索引的名称。
  • root：索引中根节点的page号，可以看出聚集索引的Root节点是第3号page（前0、1、2号Page已经被使用），辅助索引的根节点是第4、5、6、7、8个page。
  • fseg：page的说明，internal表示非叶子节点或属于根节点，leaf表示叶子节点（也就是数据页）。
  • used：索引使用了多少个page，可以看出聚集索引的非叶子节点使用了1819个page，叶子节点使用了1112265个page。
  • allocated：索引分配了多少个page，可以看出聚集索引的非叶子节点分配了2522个page，叶子节点分配了1271136个page。
  • fill_factor：索引的填充度，used/allocated表示填充度，也就是实际使用的大小百分比。

现在我们知道了Root节点页后，就可以使用innodb_ruby的另外一个功能，打印页结构信息，需要了解InnoDB页结构。

1
2
3
# 这里查看是索引中根节点的page号为3的索引信息 即主键索引id列
# 从返回结果中可以看到index_id=>55
innodb_space -s ibdata1 -T user/uc_users -p 3 page-dump

执行结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
[root@uoc-db2 MySQL3306]# innodb_space -s ibdata1 -T user/uc_users -p 3 page-dump
#<Innodb::Page::Index:0x00000002dc04b8>:

fil header:
{:checksum=>2973709054,
 :offset=>3,
 :prev=>nil,
 :next=>nil,
 :lsn=>47538913608,
 :type=>:INDEX,
 :flush_lsn=>0,
 :space_id=>31}

fil trailer:
{:checksum=>2973709054, :lsn_low32=>294273352}

page header:
{:n_dir_slots=>2,
 :heap_top=>225,
 :garbage_offset=>192,
 :garbage_size=>42,
 :last_insert_offset=>0,
 :direction=>:left,
 :n_direction=>1,
 :n_recs=>3,
 :max_trx_id=>0,
 :level=>3,
 :index_id=>55,
 :n_heap=>7,
 :format=>:compact}

fseg header:
{:leaf=>
  <Innodb::Inode space=<Innodb::Space file="user/uc_users.ibd", page_size=16384, pages=2044416>, fseg=2>,
 :internal=>
  <Innodb::Inode space=<Innodb::Space file="user/uc_users.ibd", page_size=16384, pages=2044416>, fseg=1>}

sizes:
  header           120
  trailer            8
  directory          4
  free           16189
  used             195
  record            63
  per record     21.00

page directory:
[99, 112]

system records:
{:offset=>99,
 :header=>
  {:next=>129,
   :type=>:infimum,
   :heap_number=>0,
   :n_owned=>1,
   :min_rec=>false,
   :deleted=>false,
   :length=>5},
 :next=>129,
 :data=>"infimum\x00",
 :length=>8}
{:offset=>112,
 :header=>
  {:next=>112,
   :type=>:supremum,
   :heap_number=>1,
   :n_owned=>4,
   :min_rec=>false,
   :deleted=>false,
   :length=>5},
 :next=>112,
 :data=>"supremum",
 :length=>8}

garbage records:
{:format=>:compact,
 :offset=>192,
 :header=>
  {:next=>171,
   :type=>:node_pointer,
   :heap_number=>5,
   :n_owned=>0,
   :min_rec=>false,
   :deleted=>false,
   :nulls=>[],
   :lengths=>{},
   :externs=>[],
   :length=>5},
 :next=>171,
 :type=>:clustered,
 :key=>[{:name=>"id", :type=>"BIGINT", :value=>2002372990}],
 :row=>[],
 :sys=>[],
 :child_page_number=>1206485,
 :length=>12}

{:format=>:compact,
 :offset=>171,
 :header=>
  {:next=>171,
   :type=>:node_pointer,
   :heap_number=>4,
   :n_owned=>0,
   :min_rec=>false,
   :deleted=>false,
   :nulls=>[],
   :lengths=>{},
   :externs=>[],
   :length=>5},
 :next=>171,
 :type=>:clustered,
 :key=>[{:name=>"id", :type=>"BIGINT", :value=>55861094}],
 :row=>[],
 :sys=>[],
 :child_page_number=>948104,
 :length=>12}


records:
{:format=>:compact,
 :offset=>129,
 :header=>
  {:next=>150,
   :type=>:node_pointer,
   :heap_number=>2,
   :n_owned=>0,
   :min_rec=>true,
   :deleted=>false,
   :nulls=>[],
   :lengths=>{},
   :externs=>[],
   :length=>5},
 :next=>150,
 :type=>:clustered,
 :key=>[{:name=>"id", :type=>"BIGINT", :value=>1}],
 :row=>[],
 :sys=>[],
 :child_page_number=>375704,
 :length=>12}

{:format=>:compact,
 :offset=>150,
 :header=>
  {:next=>213,
   :type=>:node_pointer,
   :heap_number=>3,
   :n_owned=>0,
   :min_rec=>false,
   :deleted=>false,
   :nulls=>[],
   :lengths=>{},
   :externs=>[],
   :length=>5},
 :next=>213,
 :type=>:clustered,
 :key=>[{:name=>"id", :type=>"BIGINT", :value=>47377252}],
 :row=>[],
 :sys=>[],
 :child_page_number=>948104,
 :length=>12}

{:format=>:compact,
 :offset=>213,
 :header=>
  {:next=>112,
   :type=>:node_pointer,
   :heap_number=>6,
   :n_owned=>0,
   :min_rec=>false,
   :deleted=>false,
   :nulls=>[],
   :lengths=>{},
   :externs=>[],
   :length=>5},
 :next=>112,
 :type=>:clustered,
 :key=>[{:name=>"id", :type=>"BIGINT", :value=>68265101}],
 :row=>[],
 :sys=>[],
 :child_page_number=>1644800,
 :length=>12}

页结构信息中有一个level字段，表示的就是Root节点页的高度，同样，level + 1就等于这个索引的高度。

比如再看看phone_number索引列（page号为4），结果如下：
https://github.com/jiankunking/backups/blob/master/MySQL/index/phone_number.page.contents

方法二

1、查看innodb_page_size

1
SHOW GLOBAL STATUS LIKE 'Innodb_page_size';

执行结果如下：

2、通过hexdump这样的工具就可以快速定位到所需要的树高度信息

1
hexdump -s 49216 -n 02 uc_users.ibd

执行结果如下：

查看uc_users表，49216表示的是316384+64（这里innodb_page_size设置为了16384，如果是8192就是38192），即第3个页偏移量64位置开始读取2个字节，这里PAGE_LEVEL为00 03，那么索引的高度就为4。

相关文章

欢迎关注我的其它发布渠道