解读InnoDB页面--索引结构

前言

很久之前了解到innodb_space，觉得很强大，可视化了InnoDB页面，但是没有形成文档，总结出来，有一些经常被问到的东东可以用这个查看了。

环境

centos 7
Linux 3.10.0-693.2.2.el7.x86_64 #1 SMP Tue Sep 12 22:26:13 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
Server version: 5.7.20 MySQL Community Server (GPL)

安装

sudo yum install rubygems ruby-devel
sudo gem install innodb_ruby

解读

总述

选项

1. -f 加载表空间，如ibd文件
2. -s 加载系统表空间，如ibd
3. -T 指定表名
4. -I 指定索引名

继续拿这个表做例子:

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gmt_create` datetime NOT NULL,
  `gmt_modified` datetime NOT NULL,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` varchar(500) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `is_used` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `idx_is_used` (`is_used`),
  KEY `idx_gmt_create` (`gmt_create`)
) ENGINE=InnoDB AUTO_INCREMENT=69313841 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

索引结构

index-recurse

[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -I PRIMARY index-recurse | more
ROOT NODE #3: 369 records, 5166 bytes
  NODE POINTER RECORD ≥ (id=43000569) → #9
  INTERNAL NODE #9: 1050 records, 14700 bytes
    NODE POINTER RECORD ≥ (id=43000569) → #7
    LEAF NODE #7: 68 records, 15028 bytes
      RECORD: (id=43000569) → (gmt_create="184616470-38-96 24:80:37", gmt_modified="-891360229-11-75 00:49:77", k=-1271582413, c="73586-15688153734-79729593694-96509299839-83724898275-8671183
3539-78981337422-35049690573-51724173961-87474696253989966", pad="21624-36689827414-04092488557-09587706818-65008859162 \x80\x00\x00\x00w\x00", is_used=-2147477504)
      RECORD: (id=43000570) → (gmt_create="184616470-38-96 24:80:37", gmt_modified="-891360231-94-84 49:64:33", k=-1271451079, c="70079-70972780322-70018558993-71769650003-09270326047-3241701
2031-10768856803-14235120402-93989080412-18690312264047768", pad="26683-45880822084-77922711547-29057964468-76514263618 \x80\x00\x00\x00w\x00", is_used=-2147475456)
      RECORD: (id=43000571) → (gmt_create="184616470-38-96 24:80:37", gmt_modified="-891360229-49-27 20:33:85", k=-1288227272, c="27441-24903985029-56844662308-79012577859-40518387141-6058841
9212-24399130405-42612257832-29494881732-71506024440268430", pad="35807-96849339132-53943793991-69741192222-48634174017 \x80\x00\x00\x00w\x00", is_used=-2080364544)
...

index-record-offsets

[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -I PRIMARY index-record-offsets | more
page_offset         record_offset
7                   127
7                   348
7                   569
7                   790
7                   1011
7                   1232
7                   1453
7                   1674
7                   1895
7                   2116
...

index-level-summary

[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -I PRIMARY -l 0 index-level-summary | more
page    index   level   data    free    records min_key
7       74      0       15028   1192    68      id=43000569
8       74      0       15028   1192    68      id=43000637
10      74      0       15028   1192    68      id=43000705
11      74      0       15028   1192    68      id=43000773
12      74      0       15028   1192    68      id=43000841
13      74      0       15028   1192    68      id=43000909
14      74      0       15028   1192    68      id=43000977
15      74      0       15028   1192    68      id=43001045
16      74      0       15028   1192    68      id=43001113
17      74      0       15028   1192    68      id=43001181
18      74      0       15028   1192    68      id=43001249
19      74      0       15028   1192    68      id=43001317
...

记录结构

record-dump

[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 7 -R 128 record-dump
Record at offset 128

Header:
  Next record offset  : -8702
  Heap number         : 512
  Type                : conventional
  Deleted             : false
  Length              : 7

System fields:
  Transaction ID: 38377940
  Roll Pointer:
    Undo Log: page 112897, offset 4249
    Rollback Segment ID: 0
    Insert: false

Key fields:
  id: 270727424

Non-key fields:
  gmt_create: "222619031-78-45 93:12:68"
  gmt_modified: "-370929284-39-30 89:87:40"
  k: -1255066825
  c: ""
  pad: "3586-15688153734-79729593694-96509299839-83724898275-8671183"
  is_used: -1288359111

记录历史

record-history

[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 7 -R 128 record-history
Transaction   Type                Undo record
[root@localhost mysql]# 

其他拓展

space-lists

[root@localhost mysql]# innodb_space -s ibdata1 space-lists
name                length      f_page      f_offset    l_page      l_offset
free                619         32768       6918        0           438
free_frag           5           0           398         0           318
full_frag           2           0           158         0           278
full_inodes         1           2           38          2           38
free_inodes         2           322         38          243         38
[root@localhost mysql]#

space-list-iterate

[root@localhost mysql]# innodb_space -s ibdata1 space-list-iterate -L free_frag
start_page  page_used_bitmap
384         #############........###################################........
16384       ##..............................................................
32768       ##..............................................................
320         ####################################################.######.##..
256         #############################.##.#..##...###..#.######..####.##.
[root@localhost mysql]#

space-inodes-summary

[root@localhost mysql]# innodb_space -s ibdata1 space-inodes-summary
INODE fseg_id=1, pages=3552, frag=32, full=47, not_full=8, free=0
INODE fseg_id=2, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=3, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=4, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=5, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=6, pages=0, frag=0, full=0, not_full=0, free=0
INODE fseg_id=7, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=8, pages=0, frag=0, full=0, not_full=0, free=0
INODE fseg_id=9, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=10, pages=0, frag=0, full=0, not_full=0, free=0
INODE fseg_id=11, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=12, pages=0, frag=0, full=0, not_full=0, free=0
INODE fseg_id=13, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=14, pages=0, frag=0, full=0, not_full=0, free=0
INODE fseg_id=15, pages=160, frag=32, full=2, not_full=0, free=0
INODE fseg_id=16, pages=1, frag=1, full=0, not_full=0, free=0
...

undo-history-summary

[root@izhp3j8nb7nyq2g923r9fvz mysql]# innodb_space -s ibdata1 undo-history-summary
Page    Offset  Transaction   Type                Table
345     2452    162569        delete
345     2512    162600        delete
345     2598    162601        update_deleted
345     2675    162569        update_existing     SYS_TABLESPACES
345     2716    162569        update_existing     SYS_DATAFILES
345     2763    162600        update_existing     SYS_TABLESPACES
345     2817    162600        update_existing     SYS_DATAFILES
347     2859    162595        delete              mysql/innodb_table_stats
284     2925    162581        delete              mysql/innodb_index_stats
284     3020    162581        delete              mysql/innodb_index_stats
284     3115    162581        delete              mysql/innodb_index_stats
284     3194    162597        delete              mysql/innodb_index_stats
284     3303    162597        delete              mysql/innodb_index_stats
284     3412    162597        delete              mysql/innodb_index_stats
284     3521    162597        delete              mysql/innodb_index_stats
284     3614    162589        delete              mysql/innodb_index_stats
284     3717    162589        delete              mysql/innodb_index_stats
284     3820    162589        delete              mysql/innodb_index_stats
284     3923    162589        delete              mysql/innodb_index_stats
284     4010    162581        delete              mysql/innodb_index_stats
284     4097    162581        delete              mysql/innodb_index_stats
284     4184    162581        delete              mysql/innodb_index_stats
284     4271    162581        delete              mysql/innodb_index_stats
312     3626    162568        delete
312     3679    162568        delete
312     3740    162568        delete
...

undo-record-dump

Print a detailed description of an undo record and the data it contains:

[root@localhost mysql]# innodb_space -s ibdata1 -p page -R offset undo-record-dump
/usr/local/share/gems/gems/innodb_ruby-0.9.15/bin/innodb_space:1582:in `undo_record_dump': undefined method `root' for nil:NilClass (NoMethodError)
    from /usr/local/share/gems/gems/innodb_ruby-0.9.15/bin/innodb_space:2055:in `<top (required)>'
    from /usr/local/bin/innodb_space:23:in `load'
    from /usr/local/bin/innodb_space:23:in `<main>'

引用ruby库

$ irb -r innodb
> sys = Innodb::System.new("ibdata1")
> idx = sys.index_by_name("sakila/film", "PRIMARY")
> rec = idx.binary_search([1])

参考

https://github.com/jeremycole/innodb_ruby/wiki
开发原理
https://blog.jcole.us/2013/01/10/btree-index-structures-in-innodb/