使用innodb_space命令查看段(Segment)、区(extent)、页(page)以及索引信息
使用innodb_space命令查看段(Segment)、区(extent)、页(page)以及索引信息
前面我们介绍完枯燥的表空间、段、区数据结构后,现在我们来看看实战,以一个200w+的数据表user_info2为例,结合工具innodb_space来分析具体的数据表结构,innodb_space的安装具体可以参见innodb_space工具使用
两个变量:
- MYSQL_BASE_DIR 指代mysql的安装目录
- MYSQL_DATA_DIR 指代mysql的数据目录
执行cd $MYSQL_DATA_DIR 进入mysql数据存放目录,在我本机此目录为 /usr/local/var/mysql,后续所有innodb_space的命令的执行均在此目录下
user_info2数据表的数据量:
select count(1) from user_info2;
输出:
+----------+
| count(1) |
+----------+
| 2300000 |
+----------+
test数据库目录下文件结构:
-rw-r----- 1 staff admin 67B May 13 19:49 db.opt
-rw-r----- 1 staff admin 21K Jun 3 17:07 test_fsp.frm
-rw-r----- 1 staff admin 784K Jun 3 17:07 test_fsp.ibd
-rw-r----- 1 staff admin 8.6K May 24 19:46 user_info.frm
-rw-r----- 1 staff admin 112K Jun 4 10:10 user_info.ibd
-rw-r----- 1 staff admin 8.6K May 25 16:03 user_info2.frm
-rw-r----- 1 staff admin 344M Jun 3 15:12 user_info2.ibd
查看表空间文件数据页数量、分布的百分比
innodb_space -s /usr/local/var/mysql/ibdata1 -T test/user_info2 space-page-type-summary
输出
type count percent description
INDEX 20377 92.56 B+Tree index
ALLOCATED 1634 7.42 Freshly allocated
IBUF_BITMAP 2 0.01 Insert buffer bitmap
FSP_HDR 1 0.00 File space header
INODE 1 0.00 File segment inode
XDES 1 0.00 Extent descriptor
可以看到当前user_info2总共有20377个数据(索引)页，占比92%,新申请的空闲空间数据页占比7%,元数据信息页总的占比不足1%
查看表空间的索引信息
innodb_space -s /usr/local/var/mysql/ibdata1 -T test/user_info2 space-indexes
输出
id name root fseg fseg_id used allocated fill_factor
51 PRIMARY 3 internal 1 17 17 100.00%
51 PRIMARY 3 leaf 2 13530 13536 99.96%
52 idx_phone 4 internal 3 16 16 100.00%
52 idx_phone 4 leaf 4 4850 6237 77.76%
root为索引的根节点数据页,fseg为类型(internal代表非页节点,leaf代表页节点)fseg_id为段ID
查看表空间每个段的基本信息
innodb_space -s /usr/local/var/mysql/ibdata1 -T test/user_info2 space-inodes-summary
输出
INODE fseg_id=1, pages=17, frag=17, full=0, not_full=0, free=0
INODE fseg_id=2, pages=13536, frag=32, full=210, not_full=1, free=0
INODE fseg_id=3, pages=16, frag=16, full=0, not_full=0, free=0
INODE fseg_id=4, pages=6237, frag=29, full=28, not_full=69, free=0
- fseg_id=1参照上述PRIMARY索引节点信息,fseg_id=1代表主索引的非页节点,一共使用了17个碎片页
- fseg_id=2代表PRIMARY的数据(叶子)节点,真实的数据存放于这些页面,一共13536个数据页
- fseg_id为3和4的代表的是辅助索引idx_phone的描述信息,与PRIMARY索引类似,此处不再赘述
查看inodes各个段、区具体的描述信息
innodb_space -s /usr/local/var/mysql/ibdata1 -T test/user_info2 space-inodes-detail
输出
INODE fseg_id=1, pages=17, frag=17 pages (3, 133, 134, 135, 138, 141, 142, 143, 145, 149, 150, 151, 152, 154, 156, 161, 165), full=0 extents (), not_full=0 extents () (0/0 pages used), free=0 extents ()
INODE fseg_id=2, pages=13536, frag=32 pages (5, 6, 7, 8, 9, 12, 13, 14, 16, 18, 19, 20, 21, 22, 23, 27, 28, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 43, 44, 47, 50), full=210 extents (64-127, 192-255, 320-383, 384-447, 512-575, 576-639, 640-703, 832-895, 896-959, 1024-1087, 1088-1151, 1152-1215, 1216-1279, 1344-1407, 1472-1535, 1600-1663, 1664-1727, 1728-1791, 1856-1919, 1920-1983, 1984-2047, 2048-2111, 2112-2175, 2176-2239, 2304-2367, 2368-2431, 2496-2559, 2624-2687, 2752-2815, 2880-2943, 3008-3071, 3072-3135, 3200-3263, 3328-3391, 3392-3455, 3456-3519, 3584-3647, 3648-3711, 3712-3775, 3776-3839, 3840-3903, 3904-3967, 3968-4031, 4032-4095, 4096-4159, 4224-4287, 4288-4351, 4352-4415, 4416-4479, 4544-4607, 4608-4671, 4736-4799, 4800-4863, 4928-4991, 5056-5119, 5184-5247, 5312-5375, 5440-5503, 5568-5631, 5696-5759, 5824-5887, 5952-6015, 6016-6079, 6144-6207, 6272-6335, 6336-6399, 6464-6527, 6528-6591, 6592-6655, 6720-6783, 6784-6847, 6848-6911, 6976-7039, 7040-7103, 7104-7167, 7232-7295, 7296-7359, 7360-7423, 7424-7487, 7488-7551, 7552-7615, 7616-7679, 7680-7743, 7808-7871, 7872-7935, 7936-7999, 8000-8063, 8064-8127, 8128-8191, 8192-8255, 8256-8319, 8320-8383, 8448-8511, 8512-8575, 8576-8639, 8640-8703, 8768-8831, 8832-8895, 8896-8959, 9024-9087, 9088-9151, 9216-9279, 9280-9343, 9408-9471, 9536-9599, 9664-9727, 9728-9791, 9856-9919, 9984-10047, 10112-10175, 10240-10303, 10368-10431, 10496-10559, 10624-10687, 10752-10815, 10880-10943, 11008-11071, 11072-11135, 11200-11263, 11328-11391, 11456-11519, 11584-11647, 11648-11711, 11776-11839, 11904-11967, 11968-12031, 12096-12159, 12160-12223, 12288-12351, 12416-12479, 12480-12543, 12608-12671, 12736-12799, 12800-12863, 12864-12927, 12992-13055, 13056-13119, 13120-13183, 13248-13311, 13312-13375, 13440-13503, 13504-13567, 13568-13631, 13696-13759, 13760-13823, 13824-13887, 13952-14015, 14016-14079, 14080-14143, 14144-14207, 14272-14335, 14336-14399, 14400-14463, 14464-14527, 14592-14655, 14656-14719, 14720-14783, 14784-14847, 14848-14911, 14912-14975, 14976-15039, 15104-15167, 15168-15231, 15232-15295, 15296-15359, 15360-15423, 15424-15487, 15488-15551, 15552-15615, 15616-15679, 15680-15743, 15808-15871, 15872-15935, 15936-15999, 16000-16063, 16064-16127, 16128-16191, 16192-16255, 16256-16319, 16448-16511, 16512-16575, 16576-16639, 16640-16703, 16704-16767, 16832-16895, 16896-16959, 16960-17023, 17024-17087, 17152-17215, 17216-17279, 17280-17343, 17408-17471, 17472-17535, 17536-17599, 17664-17727, 17728-17791, 17792-17855, 17920-17983, 17984-18047, 18112-18175, 18176-18239, 18304-18367, 18368-18431, 18496-18559, 18560-18623, 18688-18751, 18752-18815, 18880-18943, 19008-19071, 19136-19199), not_full=1 extents (19200-19263) (58/64 pages used), free=0 extents ()
INODE fseg_id=3, pages=16, frag=16 pages (4, 136, 137, 139, 140, 144, 146, 147, 148, 155, 157, 158, 159, 160, 162, 163), full=0 extents (), not_full=0 extents () (0/0 pages used), free=0 extents ()
INODE fseg_id=4, pages=6237, frag=29 pages (10, 11, 15, 17, 24, 25, 26, 29, 41, 42, 45, 46, 49, 51, 52, 53, 54, 55, 56, 57, 58, 60, 61, 62, 128, 129, 130, 131, 132), full=28 extents (1408-1471, 2944-3007, 3136-3199, 5632-5695, 5760-5823, 5888-5951, 6080-6143, 6208-6271, 10432-10495, 10560-10623, 10688-10751, 10816-10879, 10944-11007, 11136-11199, 11264-11327, 11392-11455, 11520-11583, 11712-11775, 11840-11903, 12032-12095, 12224-12287, 12352-12415, 12544-12607, 12672-12735, 12928-12991, 13184-13247, 13376-13439, 13632-13695), not_full=69 extents (19392-19455, 19520-19583, 19840-19903, 19968-20031, 20160-20223, 20608-20671, 20800-20863, 20928-20991, 21248-21311, 19264-19327, 9472-9535, 10176-10239, 18816-18879, 18432-18495, 18624-18687, 17856-17919, 19072-19135, 4672-4735, 8704-8767, 1792-1855, 5248-5311, 9152-9215, 18944-19007, 17344-17407, 4480-4543, 4864-4927, 17600-17663, 9792-9855, 704-767, 448-511, 16768-16831, 2240-2303, 18240-18303, 960-1023, 18048-18111, 4160-4223, 4992-5055, 17088-17151, 2432-2495, 16320-16383, 256-319, 7168-7231, 10048-10111, 1280-1343, 9600-9663, 768-831, 8384-8447, 8960-9023, 15744-15807, 6912-6975, 7744-7807, 5120-5183, 14208-14271, 3264-3327, 2816-2879, 1536-1599, 2560-2623, 14528-14591, 15040-15103, 3520-3583, 9344-9407, 9920-9983, 10304-10367, 5504-5567, 2688-2751, 13888-13951, 6656-6719, 5376-5439, 6400-6463) (3029/4416 pages used), free=0 extents ()
此处通过双向链表遍历了具体的数据元信息,给出了每一个段里面的区(编号)、碎片页信息，以fseg_id=2的数据节点说明
- frag=32 pages (5, 6, 7, 8,....）此处省略了一部分碎片页,代表当前段一共使用的32个数据页以及具体数据页的编号
- full=210 extents (64-127, 192-255, 320-383,....)代表当前使用的full区的个数以及具体的区的编号(区的编号以起始-结束数据页的方式给出)
- not_full=1 extents (19200-19263) (58/64 pages used), 代表当前未完全填满的区的位置
- free=0 extents () 空闲区的位置
查看段对应的索引具体信息
innodb_space -s /usr/local/var/mysql/ibdata1 -T test/user_info2 -F 1 space-index-fseg-pages-summary
输出
page index level data free records
3 51 2 272 15974 16
133 51 1 7888 8132 464
134 51 1 15776 14 928
135 51 1 15776 14 928
138 51 1 15776 14 928
141 51 1 15776 14 928
142 51 1 15776 14 928
143 51 1 15776 14 928
145 51 1 15776 14 928
149 51 1 15776 14 928
150 51 1 15776 14 928
151 51 1 15776 14 928
152 51 1 15776 14 928
154 51 1 15776 14 928
156 51 1 15776 14 928
161 51 1 15776 14 928
165 51 1 1258 14958 74
- page 页号,ibd文件中的数据页编号
- index 索引编号,一个索引由2个段(非叶节点段、叶节点段)构成
- level 索引的层级,叶子节点的level为0,向上生长,越靠近根节点越大,此处page=3,level=2代表根节点,亦即此数据表的B+树索引为3层
- data 当前数据页使用的空间大小
- free 当前数据页空闲的空间大小
- records 当前的数据页数据条数
innodb_space -s /usr/local/var/mysql/ibdata1 -T test/user_info2 -F 2 space-index-fseg-pages-summary | more
输出
page index level data free records
5 51 0 7565 8645 85
6 51 0 15130 1040 170
7 51 0 15130 1040 170
8 51 0 15130 1040 170
9 51 0 15130 1040 170
12 51 0 15130 1040 170
13 51 0 15130 1040 170
14 51 0 15130 1040 170
16 51 0 15130 1040 170
18 51 0 15130 1040 170
19 51 0 15130 1040 170
20 51 0 15130 1040 170
21 51 0 15130 1040 170
22 51 0 15130 1040 170
此处more只显示了部分数据,可以看到,非叶子节点一页的数据条目数载928左右,叶子节点数据条目数在170左右,据此可以粗略测算一棵深度为2和3的数据量以及对应的文件大小
- 深度为2(2层)的B+树
- 数据量:928*170 = 157760,数据量规模在15w+
- 数据文件大小(只有一个主索引的case):(928+1)*16kb/1024 = 14M左右
- 深度为3(3层)的B+树
- 数据量:928*928*170 = 146401280,数据量规模在1.4亿
- 数据文件大小：(928*928+928+1)*16kb/(1024*1024) = 13GB左右
此处的估算均为粗略的估算,最终的结果还跟表中的数据行的平均大小(字段多少)相关,上例中表的字段个数为6个,每行的字节数平均在89(15130/170 = 89)左右，对于其他类型的数据表,各位可以自己根据实际的表结构自行进行换算后计算
谈到了索引,此处可以进一步来看一下Mysql整体的索引结构
一个典型的3层索引结构,数据查询从根节点开始,逐级往下层查找,找到记录对应的叶子节点后,将对应的叶子节点load进内存后进一步进行页内查找,说明一下索引查找只能定位到数据页,并不能定位到具体的数据条目，因此mysql的数据查找过程实际上是由两部分的构成
-
位于磁盘的索引数据页(磁盘IO,对于单个主键类型的id查询,通常是2-3次)查找
-
位于内存的具体的数据页(内存)查找,只不过由于内存的速度较磁盘的速度快很多(具体参见存储体系一文介绍)，此部分的开销通常可以忽略不计,因此我们的数据查询开销花费都集中在上述部分的磁盘IO部分,亦即索引查找部分
可以参见下图红色部分以查找key=3的记录的过程
以user_info2表为例,我们来看下主索引的数据页结构,-I 参数后接索引名,-l 参数后接索引层级
innodb_space -s /usr/local/var/mysql/ibdata1 -T test/user_info2 index-level-summary -I PRIMARY -l 2
根节点输出:
page index level data free records min_key
3 51 2 272 15974 16 id=1
根节点具体的key的目录槽(具体目录槽的内容可以参见数据页格式分析一章)
innodb_space -s /usr/local/var/mysql/ibdata1 -T test/user_info2 -p 3 page-directory-summary
输出
slot offset type owned key
0 99 infimum 1
1 176 node_pointer 4 (id=394316)
2 244 node_pointer 4 (id=1025356)
3 312 node_pointer 4 (id=1656396)
4 112 supremum 5
- slot: 页数据槽索引
- offset: 数据槽对应的页内偏移位置
- type:数据槽类型,infimum/supremum代表最大最小值,系统添加,node_pointer索引节点类型，指向下一层节点
- owned: 代表当前槽与临接槽之间的数据节点个数(实际上page_dir_slot是一个稀疏目录)
- key:当前槽的节点Key值
可以看到根节点数据页page_slot槽目前有三个(抛开infimum根supremum两个记录，这俩记录是系统记录,代表当前页的最小、最大值,后续介绍页结构会说明),key值即是主键ID的数值
再来看看level=1的数据页节点
innodb_space -s /usr/local/var/mysql/ibdata1 -T test/user_info2 index-level-summary -I PRIMARY -l 1
输出:
page index level data free records min_key
133 51 1 7888 8132 464 id=1
134 51 1 15776 14 928 id=78796
135 51 1 15776 14 928 id=236556
138 51 1 15776 14 928 id=394316
141 51 1 15776 14 928 id=552076
142 51 1 15776 14 928 id=709836
143 51 1 15776 14 928 id=867596
145 51 1 15776 14 928 id=1025356
149 51 1 15776 14 928 id=1183116
150 51 1 15776 14 928 id=1340876
151 51 1 15776 14 928 id=1498636
152 51 1 15776 14 928 id=1656396
154 51 1 15776 14 928 id=1814156
156 51 1 15776 14 928 id=1971916
161 51 1 15776 14 928 id=2129676
165 51 1 1258 14958 74 id=2287436
以数据页133为例,我们可以看看133数据页的slots目录数据内容
innodb_space -s /usr/local/var/mysql/ibdata1 -T test/user_info2 -p 133 page-directory-summary | more
输出(此处只列出了部分数据页):
slot offset type owned key
0 99 infimum 1
1 176 node_pointer 4 (id=426)
2 244 node_pointer 4 (id=1106)
3 312 node_pointer 4 (id=1786)
4 380 node_pointer 4 (id=2466)
5 448 node_pointer 4 (id=3146)
6 516 node_pointer 4 (id=3826)
7 584 node_pointer 4 (id=4506)
8 652 node_pointer 4 (id=5186)
9 720 node_pointer 4 (id=5866)
10 788 node_pointer 4 (id=6546)
11 856 node_pointer 4 (id=7226)
12 924 node_pointer 4 (id=7906)
13 992 node_pointer 4 (id=8586)
14 1060 node_pointer 4 (id=9266)
15 1128 node_pointer 4 (id=9946)
查看node_pointer指向的下层数据页(dump数据页内容)
innodb_space -s /usr/local/var/mysql/ibdata1 -T test/user_info2 -p 133 page-records | more
输出:
Record 125: (id=1) → #5
Record 142: (id=86) → #6
Record 159: (id=256) → #7
Record 176: (id=426) → #8
Record 193: (id=596) → #9
Record 210: (id=766) → #12
Record 227: (id=936) → #13
Record 244: (id=1106) → #14
Record 261: (id=1276) → #16
Record 278: (id=1446) → #18
Record 295: (id=1616) → #19
Record 312: (id=1786) → #20
Record 329: (id=1956) → #21
Record 346: (id=2126) → #22
我们来看看一条按照ID查询的sql具体的数据查找过程
select * from user_info2 where id = 1107
数据页流向: PAGE_NO_3(根节点页)->PAGE_NO_133(level=1的节点页)->PAGE_14,一共三次磁盘IO,实际上Mysql通常会把索引的根节点页常驻内存,亦即根节点的磁盘IO实际上可以省略,总共花费2次磁盘IO即可以定位到数据页
