InnoDB存储引擎存储结构详解-实战篇

2022/07/28 1213点热度 0人点赞 0条评论

背景

之前学习mysql的时候，了解了到了页，段的概念，页的结构是什么，都简单的了解下了，毕竟都是纸面看到的，也没有深入源码了解。总觉的悬在上面，直接通过数据库文件反编译也比较麻烦。媳妇介绍了一个工具innodb_ruby, 说它可以扒mysql数据的结构。这几天扒拉了下，蛮好用的，好多知识也和之前的对上了。

我的mysql的配置文件如下(本地开发单机环境，没做什么优化，也没开启binlog)：

[root@localhost data]# cat /etc/my.cnf
[client]
#客户端默认连接字集集,若编译安装时已指定则不用填写
#character-set-server = utf8  
#客户端连接通信端口
port = 3306    
#客户端通信的用户密码端口等信息保存文件
#socket = /data/mysql/tmp/mysql.sock   
default-character-set=utf8

# The MySQL server
[mysqld]
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#配置mysql的内存大小，一般数据库服务器的80%
innodb_buffer_pool_size = 2g
##mysql服务端监听端口
port  = 3306     
# 指定服务器id
server_id = 22
#mysql数据库存放目录
datadir = /data/mysql/data/ 
#socket = /data/mysql/tmp/mysql.sock       
#服务端pid进程文件,若丢失则重启Mysql重新生成,若重启失败,
#则可能由于mysqld进程未杀死,用pkill mysql后则能重启成功Mysql
pid-file =/data/mysql/mysqld.pid
#指定错误日志目录
log-error=/data/mysql/logs/
#slow log
#slow-query-log=1
#slow_query_log_file= "slow.log"
#long_query_time=10
#binlog设置
#log_bin=mysql-bin
#binlog-format=MIXED
#独立表空间设置
innodb-file-per-table=1
explicit_defaults_for_timestamp=true
lower_case_table_names=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
performance_schema_max_table_instances=400
table_definition_cache=400
table_open_cache=256
max_allowed_packet = 32M
# 设置默认引擎
default-storage-engine=INNODB
#设置默认字符
character-set-server=utf8 
collation-server=utf8_general_ci
#设置最大链接数
max_connections=500

[root@localhost data]# ll
总用量 188488
-rw-r-----. 1 mysql mysql       56 4月  28 23:34 auto.cnf
-rw-------. 1 mysql mysql     1672 4月  28 23:34 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 4月  28 23:34 ca.pem
-rw-r--r--. 1 mysql mysql     1112 4月  28 23:34 client-cert.pem
-rw-------. 1 mysql mysql     1676 4月  28 23:34 client-key.pem
-rw-r-----  1 mysql mysql     1140 7月  24 15:27 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 7月  24 17:00 ibdata1
-rw-r-----. 1 mysql mysql 50331648 7月  24 17:00 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 7月  24 17:00 ib_logfile1
-rw-r-----  1 mysql mysql 12582912 7月  24 22:12 ibtmp1
drwxr-x---  2 mysql mysql      114 7月  23 11:18 innodb_space
drwxr-x---. 2 mysql mysql     4096 4月  28 23:34 mysql
drwxr-x---. 2 mysql mysql     8192 4月  28 23:34 performance_schema
drwxr-x---  2 mysql mysql     4096 6月  22 22:26 portal
-rw-------. 1 mysql mysql     1676 4月  28 23:34 private_key.pem
-rw-r--r--. 1 mysql mysql      452 4月  28 23:34 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 4月  28 23:34 server-cert.pem
-rw-------. 1 mysql mysql     1680 4月  28 23:34 server-key.pem

安装

可以参考:https://github.com/jeremycole/innodb_ruby

mac安装

使用ruby，安装innodb_ruby

sudo  gem install innodb_ruby
# 安装成功以后
yxkdeMacBook-Pro bin % where innodb_space
/usr/local/bin/innodb_space

centos 7 安装

centos上默认ruby是2.0无法安装的，所以要先升级ruby

#卸载操作系统原有的ruby
sudo yum remove ruby
#下载ruby
wget https://cache.ruby-lang.org/pub/ruby/2.5/ruby-2.5.0.tar.gz
#解压
tar -zxvf ruby-2.5.0.tar.gz
#进入目录
cd ruby-2.5.0
#创建安装目录
sudo mkdir -p /usr/local/ruby
#配置并指定安装位置
sudo ./configure --prefix=/usr/local/ruby
#编译与安装
sudo make && sudo make install
#建立软链接
sudo ln -s /usr/local/ruby/bin/ruby /usr/local/bin/ruby
#查看ruby版本
ruby -v
#安装 innodb_ruby
gem install innodb_ruby

使用innodb_space --help 获取到命令的帮助信息

例子:
  #使用ibdata1作为系统表空间，从系统表空间中加载表名或索引的元数据，自动生成描述索引的元数据字典
  innodb_space -s ibdata1 [-T table-name [-I index-name [-R record-offset]]] [options] <mode>
  #使用 *.ibd来读取表空间结构或索引结构
  innodb_space -f file-name.ibd [-r ./describer.rb -d DescriberClass] [options] <mode>
    使用指定表名的ibd文件 读取表空间结构或索引

支持以下参数:
  --help, -?  打印命令的帮助信息
  --trace, -t
    Enable tracing of all data read. Specify twice to enable even more
    tracing (including reads during opening of the tablespace) which can
    be quite noisy.

  --system-space-file, -s <arg>
    Load the system tablespace file or files <arg>: Either a single file e.g.
    'ibdata1', a comma-delimited list of files e.g. 'ibdata1,ibdata1', or a
    directory name. If a directory name is provided, it will be scanned for all
    files named 'ibdata?' which will then be sorted alphabetically and used to
    load the system tablespace.

  If using the --system-space-file option, the following options may also
  be used:

    --table-name, -T <name>
     指定表名，简写-T.

    --index-name, -I <name>
     指定索引名称

    --system-space-tables, -x
      Allow opening tables from the system space to support system spaces with
      tables created without innodb-file-per-table enabled.

    --data-directory, -D <directory>
      Open per-table tablespace files from <directory> rather than from the
      directory where the system-space-file is located.

  --space-file, -f <file>
    指定表空间文件

  --page, -p <page>
    指定操作的page

  --record, -R <offset>
    Operate on the record located at <offset> within the index page.

  --level, -l <level>
    level=0 为数据层或回表层，level=1或2 索引层级

  --list, -L <list>
    Operate on the list <list>.

  --fseg-id, -F <fseg_id>
     指定段的编号

  --require, -r <file>
    Use Ruby's 'require' to load the file <file>. This is useful for loading
    classes with record describers.

  --describer, -d <describer>
    Use the named record describer to parse records in index pages.

The following modes are supported:
  system-spaces ： 打印系统中所有表的概要信息.
  data-dictionary-tables ：打印所有的表
    Print all records in the SYS_TABLES data dictionary table.

  data-dictionary-columns ： 打印所有的字段
    Print all records in the SYS_COLUMNS data dictionary table.

  data-dictionary-indexes
    Print all records in the SYS_INDEXES data dictionary table.

  data-dictionary-fields
    Print all records in the SYS_FIELDS data dictionary table.

  space-summary
    统计表空间中的所有页面，可以使用--page/-p指定页码 

  space-index-pages-summary
    统计表空间内的所有’INDEX‘类型的page，对分析page的填充率和每页的记录有用。
    除了INDEX类型的页面，ALLOCATED类型的页面也会被打印出来
    可以通过-p 指定开始的页号

  space-index-fseg-pages-summary
    与 space-index-pages-summary 相同，但是一次只能通过-F 指定一个段

  space-index-pages-free-plot
    Use Ruby's gnuplot module to produce a scatterplot of page free space for
    all 'INDEX' and 'ALLOCATED' pages in a tablespace. More aesthetically
    pleasing plots can be produced with space-index-pages-summary output,
    but this is a quick and easy way to produce a passable plot. A starting
    page number can be provided with the --page/-p argument.

  space-page-type-regions
    Summarize all contiguous regions of the same page type. This is useful to
    provide an overall view of the space and allocations within it. A starting
    page number can be provided with the --page/-p argument.

  space-page-type-summary
    按类型统计所有的page，可以通过-p 指定起始页 

  space-indexes
    统计所有索引的申请、使用，以及填充率

  space-lists
    Print a summary of all lists in a space.

  space-list-iterate
    Iterate through the contents of a space list.

  space-extents
    Iterate through all extents, printing the extent descriptor bitmap.

  space-extents-illustrate
    Iterate through all extents, illustrating the extent usage using ANSI
    color and Unicode box drawing characters to show page usage throughout
    the space.

  space-lsn-age-illustrate
    Iterate through all pages, producing a heat map colored by the page LSN
    using ANSI color and Unicode box drawing characters, allowing the user to
    get an overview of page modification recency.

  space-inodes-fseg-id
    Iterate through all inodes, printing only the FSEG ID.

  space-inodes-summary
    Iterate through all inodes, printing a short summary of each FSEG.

  space-inodes-detail
   遍历所有的inodes节点，打印每个段的详细报告

  index-level-summary
    Print a summary of all pages at a given level (provided with the --level/-l
    argument) in an index.

  index-fseg-internal-lists
  index-fseg-leaf-lists
    Print a summary of all lists in an index file segment. Index root page must
    be provided with --page/-p.

  page-dump
    dump 页面元数据

  page-account
    Account for a page's usage in FSEGs.

  page-validate
    Validate the contents of a page.

  page-directory-summary
    Summarize the record contents of the page directory in a page. If a record
    describer is available, the key of each record will be printed.

  page-records
    Summarize all records within a page.

  page-illustrate
    Produce an illustration of the contents of a page.

Innodb表空间辅助工具使用

前期准备- 表以及数据构建

CREATE DATABASE innodb_space;
use innodb_space;

# 用户表一
drop TABLE if exists `t_user_info`;
CREATE TABLE `t_user_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `nick_name` varchar(30) NOT NULL COMMENT '用户昵称',
  `mobile` varchar(11) NOT NULL DEFAULT '0' COMMENT '用户手机号',
  `pwd` varchar(32) NOT NULL DEFAULT '0' COMMENT '加密后的登录密码',
  `salt` varchar(10) NOT NULL DEFAULT '0' COMMENT '密码盐值',
  `status` char(1) NOT NULL DEFAULT '1' COMMENT '用户状态，1启用，0禁用',
  `tenant_id` smallint(4) DEFAULT '1001' COMMENT '租户id',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_user` (`mobile`,`tenant_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

# 用户表二（和表已唯一的区别是id` bigint(11) ）
drop TABLE if exists `t_user_info1`;
CREATE TABLE `t_user_info1` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `nick_name` varchar(30) NOT NULL COMMENT '用户昵称',
  `mobile` varchar(11) NOT NULL DEFAULT '0' COMMENT '用户手机号',
  `pwd` varchar(32) NOT NULL DEFAULT '0' COMMENT '加密后的登录密码',
  `salt` varchar(10) NOT NULL DEFAULT '0' COMMENT '密码盐值',
  `status` char(1) NOT NULL DEFAULT '1' COMMENT '用户状态，1启用，0禁用',
  `tenant_id` smallint(4) DEFAULT '1001' COMMENT '租户id',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_user` (`mobile`,`tenant_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='用户表';

-- 格式化手机号
drop function if exists `format_mobile`;
CREATE  FUNCTION `format_mobile`(m varchar(3),n int) RETURNS varchar(11) CHARSET utf8
    NO SQL
begin        
  return concat(m,LPAD(concat(n,''),9,'0')) ;    
end
;
#随机状态
drop function if exists `rand_status`;
CREATE  FUNCTION `rand_status`() RETURNS varchar(1) CHARSET utf8
    NO SQL
begin        
  return floor(rand() *100)%2;    
end
;

# 生成随机字符串函数,最大32位
drop function if exists `rand_string`;
create function `rand_string`(n int) returns varchar(32) charset utf8 no sql
begin        
  declare rand_str varchar(32) default "";        
  declare i int default 0;
  while i < n do        
      set rand_str=substring(replace(uuid(),  '-',  ''), 1, n);
      set i= i+1;        
  end while;        
  return rand_str; 
end  
;

#生成测试数据的存储过程，动态表名需要预处理语句，为了快速插入，就直接改表名
drop procedure if exists `insert_data`;
create  procedure `insert_data`(in n int) deterministic
begin  
    declare i int default 1;
    declare start_create_time datetime default now();
    -- 自己根据要灌的数据算下，1秒一条记录 一年365*24*3600=31 536 000 一年约3000万
    declare sec int default -365*3*24*3600;
    set autocommit = 0;
    while (i <= n) do
        -- 每条记录+1秒
        set sec=sec+1;
        insert into t_user_info 
        (`nick_name`,`mobile`,`pwd`,`salt`,`status`,`create_time`,`update_time`) 
        values 
        (rand_string(30),format_mobile('13',i),rand_string(32),rand_string(5),rand_status() ,date_add(start_create_time,interval sec second),date_add(start_create_time,interval sec second)) ;
        if i%500=0 then
            -- 500条提交一次
            commit;
        end if;
        set i=i+1 ;
    end while ;
    -- 提交剩余的
    commit ;
    set autocommit = 1;
end 
;

CALL insert_data(50000000);
-- 修改下表名为t_user_info1
CALL insert_data(100000000);

/* 动态表名的模板
drop procedure if exists selectByTableName;
create procedure selectByTableName(in tableName varchar(50))
begin
    #定义语句
    set @stmt = concat('select * from ',tableName);
    #预定义sql语句，从用户变量中获取
    prepare stmt from @stmt;
    #执行sql语句
    execute stmt;
    #释放资源，后续还可以使用
    deallocate prepare stmt;
end;
*/

我两张表分别灌了5000w的数据，磁盘各占用7.7gb

[root@localhost innodb_space]# ll -h
-rw-r----- 1 mysql mysql   61 7月  16 17:38 db.opt
-rw-r----- 1 mysql mysql 8.8K 7月  23 11:18 t_user_info1.frm
-rw-r----- 1 mysql mysql 7.7G 7月  24 17:00 t_user_info1.ibd
-rw-r----- 1 mysql mysql 8.8K 7月  16 18:42 t_user_info.frm
-rw-r----- 1 mysql mysql 7.7G 7月  16 22:22 t_user_info.ibd

概念说明

段、区、页

InnoDB 为了管理好页，提出了表空间(Tablespace)的概念。

表空间是一个逻辑结构，它可以对应文件系统上一个或多个真实文件
表空间可以看成是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中；
表空间由段（segment）、区（extent）、页（page）组成
表空间分为系统表空间和独立表空间
- 系统表空间是InnoDB默认提供的一个表空间，也可以理解为共享表空间，在mysql数据目录下，名称是ibdata1
  - 存储表的最顶层索引（可以理解为起点指针），undo log、事务相关的信息、双写缓冲（double write buffer）
    - 对应的1和2区是双写缓冲区
  - 会随着mysql的运行一直不停的增大
  - 如果没有开启独立表空间，所有表的数据、索引，插入缓冲也放在这里
  - 表删除以后占用空间降不下来
- 独立表空间，在my.cnf中开启了innodb_file_per_table=1 ，mysql会为每张表创建一个独立表空间（表名.ibd）
  - 存储表的数据、索引和插入缓冲
  - 表删除后占用空间直接删除
  - 在mysql5.6.6及以后的版本中，InnoDB默认会使用独立表空间
- 其他表空间
  - 通用表空间
  - undo表空间
  - 临时表空间

段（segment）包含256区

段（segment）是逻辑概念
段中不要求区与区之间是相邻的
分为索引段、数据段、回滚段等
- 当我们创建数据表、索引的时候，就会相应的创建对应的段
- 回滚段主要用于数据回滚和多版本控制。

区（extent）包含64 页

区是页的集合，一个区包含64个连续的页，一个区默认大小为1MB
区能保证上面的64个页在物理上是连续的（有利于IO）
InnoDB每次从磁盘一次申请4~5个区
申请的64个页在使用的时候会确定类型，可能存储多张表的数据
在区中有这些：
- FSP_HDR类型：这个类型的页面是用来登记整个表空间的一些整体属性以及本组所有的区，也就是extent 0 ~ extent 255这256个区的属性。需要注意的一点是， 整个表空间只有一个FSP_HDR类型的页面
- IBUF_BITMAP类型：这个类型的页面是存储本组所有的区的所有页面关于INSERT BUFFER的信息
- INODE类型：这个类型的页面存储了许多称为INODE的数据结构
- XDES类型：全称是extent descriptor，用来登记本组256个区的属性，也就是说对于在extent 256区中的该类型页面存储的就是extent 256 ~ extent 511这些区的属性

页（page）默认16kb

页是InnoDB管理的最小单位
页包含（文件头：38字节，文件尾8字节，其他填充数据）
每个页面分配一个32位整数页码，通常称为偏移量（offset），只是页面与空间开头的偏移量
InnoDB的数据限制为64TB，是有页码的限制

看下独立表空间的结构

[root@localhost data]# ll innodb_space/ -h
总用量 16G
-rw-r----- 1 mysql mysql   61 7月  16 17:38 db.opt
-rw-r----- 1 mysql mysql 8.8K 7月  23 11:18 t_user_info1.frm
-rw-r----- 1 mysql mysql 7.7G 7月  24 17:00 t_user_info1.ibd
-rw-r----- 1 mysql mysql 8.8K 7月  16 18:42 t_user_info.frm
-rw-r----- 1 mysql mysql 7.7G 7月  16 22:22 t_user_info.ibd

表的基础信息

-- 查询表所属的表空间id
SELECT * FROM information_schema.innodb_sys_tablespaces WHERE name LIKE '%user_info';

从结果上，可以看出来，行格式为dynamic，一页的大小为16kb，表空间类型为single，文件大小

该表的表空间id是53，

-- 查询表的表id
SELECT * FROM information_schema.innodb_sys_tables WHERE name LIKE '%user_info';

该表的table_id为57

-- 查看表的索引id
SELECT * FROM information_schema.innodb_sys_indexes WHERE table_id=57

对应的索引id为71，72

以上信息我们先记着，后面会用到

开始使用

进度mysql的data目录，看自己mysql的配置文件，数据存储在哪里。

[root@localhost data]# ls
auto.cnf    client-cert.pem  ibdata1      ibtmp1        performance_schema  public_key.pem   sys
ca-key.pem  client-key.pem   ib_logfile0  innodb_space  portal              server-cert.pem  xportal
ca.pem      ib_buffer_pool   ib_logfile1  mysql         private_key.pem     server-key.pem

概要信息

我们先看下我们这两张表的概要信息

[root@localhost data]# innodb_space -s ibdata1    system-spaces |grep t_user_info
name                            pages       indexes (grep 以后这行会丢失)
innodb_space/t_user_info        502528      2           
innodb_space/t_user_info1       502528      2

列出了表占用的页数，以及索引个数，相对比较简单

表的索引信息

我们先看下对应表的索引信息

innodb_space -s ibdata1 -T innodb_space/t_user_info space-indexes

输出：

[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info space-indexes
id          name       root    fseg        fseg_id     used        allocated   fill_factor 
71          PRIMARY    3       internal    1           465         480         96.88%      
71          PRIMARY    3       leaf        2           406505      406560      99.99%      
72          unq_user   4       internal    3           177         224         79.02%      
72          unq_user   4       leaf        4           87720       87776       99.94%      
[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info1 space-indexes
id          name       root    fseg        fseg_id     used        allocated   fill_factor 
73          PRIMARY    3       internal    1           465         480         96.88%      
73          PRIMARY    3       leaf        2           406505      406560      99.99%      
74          unq_user   4       internal    3           177         224         79.02%      
74          unq_user   4       leaf        4           87720       87776       99.94%

解释：

id:索引ID列
name 索引名称 PRIMARY 表示聚簇索引
root 索引根节点位于的数据页编号
fseg 索引类型(internal为内部节点，我们一般称为非叶子节点,leaf为叶子节点)
- 索引存储在非叶子节点上
- 数据存储在叶子节点上
fseg_id 索引所属段ID
- 虽然两张表t_user_info和t_user_info1的段都一样，但是表示的都是对应表的.ibd文件里的，分属于不同的表空间
used 使用的page页数量
- 通过表一（t_user_info）和表二(t_user_info1)的的对比，bigint类型创建表的时候是固定大小，并不会取你创建表的()中的数值
allocated 申请的数据页数量
- 一共申请了495040个数据页，使用了494867个数据页
- 从这里可以看出不是一个个的申请，是一次申请一批，预留一定的buffer，这里保证连贯性，从磁盘里读取的时候，不是只读对应的一页，而是相关的数据连着一片，这种方式对
- 我们算下 select (480+406560+224+87776)* 16*1024/(1024*1024*1024) = 7.5537 GB 接近物理存储的空间,物理磁盘还会有些填充
fill_factor used/allocated 使用百分比

统计每个类型占用连续空间的页的数量

统计的是t_user_info.ibd文件里的信息

[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info space-page-type-regions
start       end         count       type                
0           0           1           FSP_HDR             
1           1           1           IBUF_BITMAP         
2           2           1           INODE               
3           16383       16381       INDEX               
16384       16384       1           XDES                
16385       16385       1           IBUF_BITMAP         
16386       16388       3           INDEX               
16389       16447       59          FREE (ALLOCATED)    
16448       32767       16320       INDEX                 
......
475200      491519      16320       INDEX               
491520      491520      1           XDES                
491521      491521      1           IBUF_BITMAP         
491522      491583      62          FREE (ALLOCATED)    
491584      496839      5256        INDEX               
496840      496895      56          FREE (ALLOCATED)    
496896      496904      9           INDEX               
496905      497151      247         FREE (ALLOCATED)

解释：

Start 表示起始页的页码
end 表示同类型结束的页码
count 为当前类型page的连续数量

统计表空间中各页类型占比

innodb_space -s ibdata1 -T innodb_space/t_user_info space-page-type-summary

结果

[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info space-page-type-summary
type                count       percent     description         
INDEX               494867      98.48       B+Tree index        
ALLOCATED           7598        1.51        Freshly allocated   
IBUF_BITMAP         31          0.01        Insert buffer bitmap
XDES                30          0.01        Extent descriptor   
FSP_HDR             1           0.00        File space header   
INODE               1           0.00        File segment inode

解释

type 页的类型
count 数量
Percent 占比
description 页的基本信息描述

就拿节点type=INDEX 来说

因为索引结构为b+Tree, 并且数据也在B+Tree的叶子节点上挂着，所以494867 = （465+406505+177+87720）为使用的页面的数量

表空间每个段的基本信息

[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info space-inodes-summary
INODE fseg_id=1, pages=480, frag=32, full=6, not_full=1, free=0
INODE fseg_id=2, pages=406560, frag=32, full=6351, not_full=1, free=0
INODE fseg_id=3, pages=224, frag=32, full=2, not_full=1, free=0
INODE fseg_id=4, pages=87776, frag=32, full=1370, not_full=1, free=0

解释：

fseg_id=1参照上述索引空间中PRIMARY索引节点信息,fseg_id=1代表主索引的非页节点,一共使用了32个碎片页
fseg_id=2代表PRIMARY的数据(叶子)节点,真实的数据存放于这些页面,一共406560个数据页
fseg_id为3和4的代表的是唯一索引unq_user的描述信息,存储的是unq_user 索引与主键id的映射，回表就是在这里

查看inodes每个段的详细报告

[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info space-inodes-detail
INODE fseg_id=1, pages=480, frag=32 pages (3, 197, 198, 199, 200, 203, 205, 206, 207, 209, 210, 212, 213, 214, 216, 217, 218, 220, 221, 223, 224, 225, 227, 228, 230, 231, 232, 234, 235, 236, 238, 239), full=6 extents (32704-32767, 101504-101567, 170112-170175, 238784-238847, 307456-307519, 376128-376191), not_full=1 extents (444800-444863) (49/64 pages used), free=0 extents ()
INODE fseg_id=2, pages=406560, frag=32 pages (5, 6, 7, 8, 9, 10, 13, 14, 16, 17, 18, 19, 20, 22, 23, 24, 25, 27, 28, 29, 30, 31, 33, 34, 35, 36, 38, 39, 40, 41, 42, 44), full=6351 extents (......), not_full=1 extents (496896-496959) (9/64 pages used), free=0 extents ()
INODE fseg_id=3, pages=224, frag=32 pages (4, 201, 202, 204, 208, 211, 215, 219, 222, 226, 229, 233, 237, 240, 241, 242, 243, 244, 245, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255, 16386, 16387, 16388), full=2 extents (86400-86463, 267648-267711), not_full=1 extents (448896-448959) (17/64 pages used), free=0 extents ()
INODE fseg_id=4, pages=87776, frag=32 pages (11, 12, 15, 21, 26, 32, 37, 43, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 192, 193, 194, 195, 196), full=1370 extents (256-319, 640-703, ......, 496128-496191, 496448-496511), not_full=1 extents (496832-496895) (8/64 pages used), free=0 extents ()

此处通过双向链表遍历了具体的数据元信息,给出了每一个段里面的区(编号)、碎片页信息，以fseg_id=2的数据节点说明

frag=32 frag=32 pages (5, 6, 7, 8, 9, 10,......,42,44),代表当前段一共使用的32个数据页以及具体数据页的编号
full=6351 extents (......)代表当前使用的full区的个数以及具体的区的编号(以开始-结束)
not_full=1 extents (496896-496959) (9/64 pages used) 代表当前未完全填满的区的位置
free=0 extents () 空闲区的位置

查看段对应的索引具体信息

指定段查看具体的索引信息, -F 1 指定段1查看索引信息

# 查看第1个段（主键索引段）的明细
[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info -F 1 space-index-fseg-pages-summary
page        index   level   data    free    records 
3           71      2       8352    7670    464     
197         71      1       7902    8132    439     
198         71      1       15804   12      878     
......     
444846      71      1       15804   12      878     
444847      71      1       15804   12      878     
444848      71      1       7740    8300    430     
444849      0       0       0       16384   0       
444850      0       0       0       16384   0       
444851      0       0       0       16384   0       
444852      0       0       0       16384   0       
......
444861      0       0       0       16384   0       
444862      0       0       0       16384   0       
444863      0       0       0       16384   0       
32704       71      1       15804   12      878     
32705       71      1       15804   12      878     
32706       71      1       15804   12      878     
.....      
376187      71      1       15804   12      878     
376188      71      1       15804   12      878     
376189      71      1       15804   12      878     
376190      71      1       15804   12      878     
376191      71      1       15804   12      878    

# 查看第2个段（主键对应的数据段）的明细
[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info -F 2 space-index-fseg-pages-summary |more
page        index   level   data    free    records 
5           71      0       7503    8719    61      
6           71      0       15129   1065    123     
7           71      0       15129   1065    123     
8           71      0       15129   1065    123       
.......  
496902      71      0       15129   1065    123     
496903      71      0       15129   1065    123     
496904      71      0       8610    7610    70      
496905      0       0       0       16384   0       
496906      0       0       0       16384   0       
496907      0       0       0       16384   0           
.......      
496957      0       0       0       16384   0       
496958      0       0       0       16384   0       
496959      0       0       0       16384   0       
64          71      0       15129   1065    123     
65          71      0       15129   1065    123      
........ 
93          71      0       15129   1065    123     
94          71      0       15129   1065    123     
95          71      0       15129   1065    123     
96          71      0       15129   1065    123
.........

# 查看第3个段（unq_user索引段）的明细
[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info -F 3 space-index-fseg-pages-summary |more
page        index   level   data    free    records 
4           72      2       5632    10534   176     
201         72      1       8000    8128    250     
202         72      1       16000   4       500     
204         72      1       16000   4       500     
208         72      1       16000   4       500     
211         72      1       16000   4       500     
.......  
448899      72      1       16000   4       500     
448900      72      1       16000   4       500     
448901      72      1       16000   4       500     
448902      72      1       16000   4       500   

# 查看第4个段（unq_user数据段）的明细
[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info -F 4 space-index-fseg-pages-summary |more
page        index   level   data    free    records 
11          72      0       7980    8130    285     
12          72      0       15960   10      570     
15          72      0       15960   10      570     
.......   
496835      72      0       15960   10      570     
496836      72      0       15960   10      570     
496837      72      0       15960   10      570     
496838      72      0       15960   10      570     
496839      72      0       12740   3288    455     
496840      0       0       0       16384   0       
496841      0       0       0       16384   0       
496842      0       0       0       16384   0       
..........     
496893      0       0       0       16384   0       
496894      0       0       0       16384   0       
496895      0       0       0       16384   0       
256         72      0       15960   10      570     
257         72      0       15960   10      570     
258         72      0       15960   10      570     
259         72      0       15960   10      570     
260         72      0       15960   10      570     
261         72      0       15960   10      570     
..........

解释：

page 页号,ibd文件中的数据页编号
index 索引编号,一个索引由2个段(非叶节点段、叶节点段)构成
level 索引的层级,叶子节点的level为0,向上生长,越靠近根节点越大,此处page=3,level=2代表根节点,亦即此数据表的B+树索引为3层
- 0可能是空节点
- 我灌了5千万的数据，根节点的页上填充了1半的容量（所以以后面试不要说3层的B+Tree只能存2000万数据），两层索引灌满+一层叶子节点的数据差不多能容纳1亿条
- 此时整个索引层的大小为（464页+1页）*16kb 约7MB多点，从7MB的数据里再通过近似二分查找的定位数据，大家想下，效率不会太慢；
- 如果索引层达到3层呢？数据能达到多少？大家自己算下
data 当前数据页使用的空间大小
free 当前数据页空闲的空间大小
- 索引的空间利用率比较高，数据固定（索引长度以及回表字段）
- 数据库的利用率相对较低，要考虑后续的更新
records 当前的数据页数据条数
- 一页主键索引能填充878条数据，如果一条数据对应一页，那么是878页
- 一页主键数据页能填充123条数据（主键数据页就是具体的数据）
- 自定义索引unq_user 每页能填充500条数据
- 自定义数据页unq_user每页能填充570条数据

合理的设计索引结构还是能省不少的空间的

我们再接着看一个命令index-level-summary，看下对应层级的信息

以t_user_info表为例,我们来看下主索引的数据页结构,-I 参数后接索引名,-l 参数后接索引层级

#根索引（第一层索引）
[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info index-level-summary -I PRIMARY -l 2
page    index   level   data    free    records min_key 
3       71      2       8352    7670    464     id=1
# 第二层索引
[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info index-level-summary -I PRIMARY -l 1
page    index   level   data    free    records min_key 
197     71      1       7902    8132    439     id=1
198     71      1       15804   12      878     id=53936
199     71      1       15804   12      878     id=161930
200     71      1       15804   12      878     id=269924
203     71      1       15804   12      878     id=377918
205     71      1       15804   12      878     id=485912
.....
444841  71      1       15804   12      878     id=49191206
444842  71      1       15804   12      878     id=49299200
444843  71      1       15804   12      878     id=49407194
444844  71      1       15804   12      878     id=49515188
444845  71      1       15804   12      878     id=49623182
444846  71      1       15804   12      878     id=49731176
444847  71      1       15804   12      878     id=49839170
444848  71      1       7740    8300    430     id=49947164
#第三层数据
[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info index-level-summary -I PRIMARY -l 0 |more
page    index   level   data    free    records min_key 
5       71      0       7503    8719    61      id=1
6       71      0       15129   1065    123     id=62
7       71      0       15129   1065    123     id=185
8       71      0       15129   1065    123     id=308
9       71      0       15129   1065    123     id=431
10      71      0       15129   1065    123     id=554
13      71      0       15129   1065    123     id=677
14      71      0       15129   1065    123     id=800
16      71      0       15129   1065    123     id=923
17      71      0       15129   1065    123     id=1046
18      71      0       15129   1065    123     id=1169
.....

#我们再来看下unq_user的索引结构
# 根索引（第一层索引）
[root@localhost data]#  innodb_space -s ibdata1 -T innodb_space/t_user_info index-level-summary -I unq_user -l 2
page    index   level   data    free    records min_key 
4       72      2       5632    10534   176     mobile="13000000001", tenant_id=1001
#第二层索引
[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info index-level-summary -I unq_user -l 1
page    index   level   data    free    records min_key 
201     72      1       8000    8128    250     mobile="13000000001", tenant_id=1001
202     72      1       16000   4       500     mobile="13000142216", tenant_id=1001
204     72      1       16000   4       500     mobile="13000427216", tenant_id=1001
208     72      1       16000   4       500     mobile="13000712216", tenant_id=1001
211     72      1       16000   4       500     mobile="13000997216", tenant_id=1001
215     72      1       16000   4       500     mobile="13001282216", tenant_id=1001
219     72      1       16000   4       500     mobile="13001567216", tenant_id=1001
222     72      1       16000   4       500     mobile="13001852216", tenant_id=1001
226     72      1       16000   4       500     mobile="13002137216", tenant_id=1001
229     72      1       16000   4       500     mobile="13002422216", tenant_id=1001
233     72      1       16000   4       500     mobile="13002707216", tenant_id=1001
237     72      1       16000   4       500     mobile="13002992216", tenant_id=1001
240     72      1       16000   4       500     mobile="13003277216", tenant_id=1001
.......
#第三层索引
[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info index-level-summary -I unq_user -l 0|more
page    index   level   data    free    records min_key 
11      72      0       7980    8130    285     mobile="13000000001", tenant_id=1001
12      72      0       15960   10      570     mobile="13000000286", tenant_id=1001
15      72      0       15960   10      570     mobile="13000000856", tenant_id=1001
21      72      0       15960   10      570     mobile="13000001426", tenant_id=1001
26      72      0       15960   10      570     mobile="13000001996", tenant_id=1001
32      72      0       15960   10      570     mobile="13000002566", tenant_id=1001
37      72      0       15960   10      570     mobile="13000003136", tenant_id=1001
43      72      0       15960   10      570     mobile="13000003706", tenant_id=1001
45      72      0       15960   10      570     mobile="13000004276", tenant_id=1001
......

解释:

就比space-index-fseg-pages-summary多了一个min_key

min_key:当前页的最小key值,这个key为主键id
在这里还隐藏了一个max_key
在mysql的page里用inflimum 和supremum

我们通过下面的dump page 来看下内部的结构

dump page 详细信息（重点）

这块建议大家仔细看，通过不同的数据分析B+Tree的数据结构

# 随机dump一个索引页页面号为200（最最开始的页面头部和这个不太一样）
[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info -p 200  page-dump|more
#<Innodb::Page::Index:0x00000000012d47d0>:
# page 页面的文件头，标记了页面的一些信息
fil header:
#<struct Innodb::Page::FilHeader
# 校验和
 checksum=2124023493,
 # 页码偏移量 200和页面的页码一样
 offset=200,
 # 前驱指针，指向的page的页码
 prev=199,
 # 后继指针，指向的page的页码
 next=203,
 # lsn  当前已写入的redo 日志量
 lsn=75988847,
 # page的类型
 type=:INDEX,
 flush_lsn=0,
 # 表空间id，最开始已经查了，可以看前面
 space_id=53>

fil trailer:
#<struct Innodb::Page::FilTrailer checksum=2124023493, lsn_low32=75988847>
# 页面header
page header:
#<struct Innodb::Page::Index::PageHeader
 n_dir_slots=220,
 heap_top=15924,
 n_heap_format=33648,
 n_heap=880,
 # 行格式
 format=:compact,
 garbage_offset=0,
 #垃圾信息，删除的信息，可以重用的
 garbage_size=0,
 # 最后插入数据的偏移量
 last_insert_offset=15912,
 direction=:right,
 n_direction=877,
 #记录数
 n_recs=878,
 # 最大事务id
 max_trx_id=0,
 # 索引层级
 level=1,
 # 索引编号，整体描述
 index_id=71>
#索引类型，标记是叶子节点还是非叶子节点
fseg header:
#<struct Innodb::Page::Index::FsegHeader leaf=nil, internal=nil>
#空间占用分布
sizes:
  # header占用120字节（固定）
  header           120
  # trailer占用8字节（固定）
  trailer            8
  directory        440
  free              12
  used           16372
  record         15804
  # 每条记录占用18字节（一个索引只有8字节，额外确有10字节的占用）
  per record     18.00
#页目录
page directory:
[99,
 180,
 252,
 324,
 396,
 468,
 .......
 15516,
 15588,
 15660,
 15732,
 15804,
 112]

# 两个字段：infimum 和 supremum 分别是本页面中的偏移量
system records:
#<struct Innodb::Page::Index::SystemRecord
 #自己的偏移量
 offset=99,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=5,
   # 指向本page内的偏移量
   next=126,
   type=:infimum,
   # 当前页面的序号
   heap_number=0,
   n_owned=1,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=nil,
   lengths=nil,
   externs=nil>,
 next=126,
 data="infimum\x00",
 length=8>
#<struct Innodb::Page::Index::SystemRecord
# supremum并没有指向偏移量
 offset=112,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=5,
   next=112,
   type=:supremum,
   heap_number=1,
   n_owned=7,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=nil,
   lengths=nil,
   externs=nil>,
 next=112,
 data="supremum",
 length=8>
# 垃圾记录（可以理解为已经删除的记录）
garbage records:
# 记录信息，一条索引记录指向一个page
# 数据记录从126字节开始，125个字节都是系统占用的
records:
#<struct Innodb::Page::Index::UserRecord
 type=:clustered,
 format=:compact,
 # infimum 指向的这条记录
 offset=126,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=5,
   next=144,
   type=:node_pointer,
   heap_number=2,
   n_owned=0,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=[],
   lengths={},
   externs=[]>,
 # 链表指向的下一条记录  
 next=144,
 # 索引字段描述：索引的的key，可以是聚簇也可以是非聚簇
 key=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="id",
    type="BIGINT",
    value=269924,
    extern=nil>],
 row=[],
 sys=[],
 # 子页面的page编号
 child_page_number=2739,
 transaction_id=nil,
 roll_pointer=nil,
 # 一条记录占用的长度
 length=12>
#紧接着126下一个索引，因为126这条记录占用12字节，所以这条记录的偏移量就是126+12=144了
#<struct Innodb::Page::Index::UserRecord
 type=:clustered,
 format=:compact,
 offset=144,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=5,
   next=162,
   type=:node_pointer,
   heap_number=3,
   n_owned=0,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=[],
   lengths={},
   externs=[]>,
 next=162,
 key=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="id",
    type="BIGINT",
    value=270047,
    extern=nil>],
 row=[],
 sys=[],
 child_page_number=2740,
 transaction_id=nil,
 roll_pointer=nil,
 length=12>
 .....
# 200这个page页面中最后一条记录
#<struct Innodb::Page::Index::UserRecord
 type=:clustered,
 format=:compact,
 # 最后一条记录的偏移量，接近16kb的，所以这个offset是页面的相对字节位置
 offset=15912,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=5,
   next=112,
   type=:node_pointer,
   # 当前页面的序号
   heap_number=879,
   n_owned=0,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=[],
   lengths={},
   externs=[]>,
 next=112,
 key=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="id",
    type="BIGINT",
    value=377795,
    extern=nil>],
 row=[],
 sys=[],
 child_page_number=3808,
 transaction_id=nil,
 roll_pointer=nil,
 length=12>

 # 随便dump一个数据页，页面号为20
 [root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info -p 10  page-dump|more
#<Innodb::Page::Index:0x00000000020c07e0>:

fil header:
#<struct Innodb::Page::FilHeader
# 校验和
 checksum=4100977516,
 # 页面编号偏移量
 offset=10,
 prev=9,
 next=13,
 # lsn  当前已写入的redo 日志量
 lsn=3112162,
 # 类型还是INDEX
 type=:INDEX,
 flush_lsn=0,
 # 表空间id
 space_id=53>

fil trailer:
#<struct Innodb::Page::FilTrailer checksum=4100977516, lsn_low32=3112162>

page header:
#<struct Innodb::Page::Index::PageHeader
 n_dir_slots=31,
 heap_top=15249,
 n_heap_format=32893,
 n_heap=125,
 format=:compact,
 garbage_offset=0,
 garbage_size=0,
 last_insert_offset=15137,
 direction=:right,
 n_direction=122,
 # 123条记录
 n_recs=123,
 #最大事务id
 max_trx_id=0,
 # 索引层级
 level=0,
 #对应的索引id
 index_id=71>

fseg header:
#<struct Innodb::Page::Index::FsegHeader leaf=nil, internal=nil>
# 统计信息
sizes:
  # header占用120字节
  header           120
  # trailer占用8字节
  trailer            8
  # directory占用62字节
  directory         62
  # 空闲1065字节
  free            1065
  used           15319
  record         15129
  #一条记录占用123字节
  per record     123.00

page directory:
[99,
 500,
 992,
 1484,
 1976,
 2468,
 2960,
 3452,
 3944,
 4436,
 4928,
 5420,
 5912,
 6404,
 6896,
 ......
  14276,
 112]

system records:
#<struct Innodb::Page::Index::SystemRecord
 offset=99,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=5,
   next=131,
   type=:infimum,
   heap_number=0,
   n_owned=1,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=nil,
   lengths=nil,
   externs=nil>,
 next=131,
 data="infimum\x00",
 length=8>
#<struct Innodb::Page::Index::SystemRecord
 offset=112,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=5,
   next=112,
   type=:supremum,
   heap_number=1,
   n_owned=8,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=nil,
   lengths=nil,
   externs=nil>,
 next=112,
 data="supremum",
 length=8>

garbage records:
# 数据记录
records:
#<struct Innodb::Page::Index::UserRecord
 type=:clustered,
 format=:compact,
 # 当前页面中的偏移字节数
 offset=131,
 # 当前数据的描述信息
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=11,
   next=254,
   type=:conventional,
   heap_number=2,
   n_owned=0,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=[],
   # 字段长度描述
   lengths={"nick_name"=>30, "mobile"=>11, "pwd"=>32, "salt"=>5, "status"=>1},
   externs=[]>,
 next=254,
 # 主键key，所以，即使主键不添加索引，天然就有一条，不设置主键，InnoDB也会生成一条
 key=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="id",
    type="BIGINT",
    value=554,
    extern=nil>],
 # 数据字段描述   
 row=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="nick_name",
    type="VARCHAR(90)",
    value="29f9f16004f411ed841f8045ddf698",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="mobile",
    type="VARCHAR(33)",
    value="13000000554",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="pwd",
    type="VARCHAR(96)",
    value="29f9f58304f411ed841f8045ddf698d4",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="salt",
    type="VARCHAR(30)",
    value="29f9f",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="status",
    type="VARCHAR(3)",
    value="0",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="tenant_id",
    type="SMALLINT",
    value=1001,
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="create_time",
    type="DATETIME",
    value="185017151-43-03 39:30:57",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="update_time",
    type="DATETIME",
    value="-572548138-02-52 61:00:48",
    extern=nil>],
 sys=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="DB_TRX_ID",
    type="TRX_ID",
    value=7977,
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="DB_ROLL_PTR",
    type="ROLL_PTR",
    value=
     #<struct Innodb::DataType::RollPointerType::Pointer
      is_insert=true,
      rseg_id=36,
      undo_log=#<struct Innodb::Page::Address page=280, offset=1120>>,
    extern=nil>],
 # 因为数据已经是叶子节点了，就没有子页面了   
 child_page_number=nil,
 #事务id
 transaction_id=7977,
 roll_pointer=
  #<struct Innodb::DataType::RollPointerType::Pointer
   is_insert=true,
   rseg_id=36,
   undo_log=#<struct Innodb::Page::Address page=280, offset=1120>>,
 length=118>
 .........

 #<struct Innodb::Page::Index::UserRecord
 type=:clustered,
 format=:compact,
 offset=15137,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=11,
   next=112,
   type=:conventional,
   heap_number=124,
   n_owned=0,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=[],
   lengths={"nick_name"=>30, "mobile"=>11, "pwd"=>32, "salt"=>5, "status"=>1},
   externs=[]>,
 next=112,
 key=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="id",
    type="BIGINT",
    value=676,
    extern=nil>],
 row=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="nick_name",
    type="VARCHAR(90)",
    value="29fef9d804f411ed841f8045ddf698",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="mobile",
    type="VARCHAR(33)",
    value="13000000676",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="pwd",
    type="VARCHAR(96)",
    value="29fefe0804f411ed841f8045ddf698d4",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="salt",
    type="VARCHAR(30)",
    value="29fef",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="status",
    type="VARCHAR(3)",
    value="1",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="tenant_id",
    type="SMALLINT",
    value=1001,
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="create_time",
    type="DATETIME",
    value="185017151-86-65 46:92:17",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="update_time",
    type="DATETIME",
    value="-565229901-26-73 31:68:64",
    extern=nil>],
 sys=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="DB_TRX_ID",
    type="TRX_ID",
    value=7978,
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="DB_ROLL_PTR",
    type="ROLL_PTR",
    value=
     #<struct Innodb::DataType::RollPointerType::Pointer
      is_insert=true,
      rseg_id=37,
      undo_log=#<struct Innodb::Page::Address page=281, offset=1472>>,
    extern=nil>],
 child_page_number=nil,
 transaction_id=7978,
 roll_pointer=
  #<struct Innodb::DataType::RollPointerType::Pointer
   is_insert=true,
   rseg_id=37,
   undo_log=#<struct Innodb::Page::Address page=281, offset=1472>>,
 length=118>

# 随机一个索引的数据页，页面号为45
[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info -p 45  page-dump|more
#<Innodb::Page::Index:0x00000000024910e0>:

fil header:
#<struct Innodb::Page::FilHeader
 checksum=3380047716,
 offset=45,
 prev=43,
 next=46,
 lsn=4006484,
 type=:INDEX,
 flush_lsn=0,
 space_id=53>

fil trailer:
#<struct Innodb::Page::FilTrailer checksum=3380047716, lsn_low32=4006484>

page header:
#<struct Innodb::Page::Index::PageHeader
 n_dir_slots=143,
 heap_top=16080,
 n_heap_format=33340,
 n_heap=572,
 format=:compact,
 garbage_offset=0,
 garbage_size=0,
 last_insert_offset=16059,
 direction=:right,
 n_direction=569,
 n_recs=570,
 max_trx_id=8020,
 level=0,
 index_id=72>

fseg header:
#<struct Innodb::Page::Index::FsegHeader leaf=nil, internal=nil>

sizes:
  header           120
  trailer            8
  directory        286
  free              10
  used           16374
  record         15960
  per record     28.00

page directory:
[99,
 211,
 323,
 435,
 547,
 ......
  15891,
 112]

system records:
#<struct Innodb::Page::Index::SystemRecord
 offset=99,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=5,
   next=127,
   type=:infimum,
   heap_number=0,
   n_owned=1,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=nil,
   lengths=nil,
   externs=nil>,
 next=127,
 data="infimum\x00",
 length=8>
#<struct Innodb::Page::Index::SystemRecord
 offset=112,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=5,
   next=112,
   type=:supremum,
   heap_number=1,
   n_owned=7,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=nil,
   lengths=nil,
   externs=nil>,
 next=112,
 data="supremum",
 length=8>

garbage records:

records:
#<struct Innodb::Page::Index::UserRecord
#可以理解为间接类型，虽然他的level是0，但是他没有直接存储数据，而是存储了索引对应的id
 type=:secondary,
 format=:compact,
 offset=127,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=7,
   next=155,
   type=:conventional,
   heap_number=2,
   n_owned=0,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=[],
   lengths={"mobile"=>11},
   externs=[]>,
 next=155,
 #unq_user索引的最底层结构
 # key是联合唯一索引
 key=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="mobile",
    type="VARCHAR(33)",
    value="13000004276",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="tenant_id",
    type="SMALLINT",
    value=1001,
    extern=nil>],
 # 数据是主键   
 row=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="id",
    type="BIGINT",
    value=4276,
    extern=nil>],
 sys=[],
 child_page_number=nil,
 transaction_id=nil,
 roll_pointer=nil,
 length=21>
 .....
#<struct Innodb::Page::Index::UserRecord
 type=:secondary,
 format=:compact,
 offset=16059,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=7,
   next=112,
   type=:conventional,
   heap_number=571,
   n_owned=0,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=[],
   lengths={"mobile"=>11},
   externs=[]>,
 next=112,
 key=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="mobile",
    type="VARCHAR(33)",
    value="13000004845",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="tenant_id",
    type="SMALLINT",
    value=1001,
    extern=nil>],
 row=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="id",
    type="BIGINT",
    value=4845,
    extern=nil>],
 sys=[],
 child_page_number=nil,
 transaction_id=nil,
 roll_pointer=nil,
 length=21>

综合以上的信息：

数据在聚簇索引上
一条索引占18字节，数据字段只有8字节，其他都是额外的空间
InnoDB通过抽象key:(索引字段描述)和row（具体的索引或数据）
每个索引指向一个页面，索引存储的是对应页面的最小值（之前一直以为是左开右闭）
非聚簇索引通过回表找到数据，索引有序，但回表对应的字段无序，大量回表会产生大量的IO

之前我画的一个InnoDB的索引结构图

单从这次的试验来说，在最底层的page页面中，是没有最大记录的（这个问题后续看下mysql的源码再跟进下）

可以再通过page-records和index-recurse对上面3个页面中数据部分进行补充

# dump主键索引的明细记录
[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info1 -p 200 page-records|more
Record 126: (id=269924) → #2739
Record 144: (id=270047) → #2740
Record 162: (id=270170) → #2741
Record 180: (id=270293) → #2742
Record 198: (id=270416) → #2743
Record 216: (id=270539) → #2744
Record 234: (id=270662) → #2745
Record 252: (id=270785) → #2746
Record 270: (id=270908) → #2747
Record 288: (id=271031) → #2748
Record 306: (id=271154) → #2749
.....
Record 15804: (id=377057) → #3802
Record 15822: (id=377180) → #3803
Record 15840: (id=377303) → #3804
Record 15858: (id=377426) → #3805
Record 15876: (id=377549) → #3806
Record 15894: (id=377672) → #3807
Record 15912: (id=377795) → #3808

# dump主键数据的明细记录
[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info1 -p 10 page-records|more
Record 131: (id=554) → (nick_name="4095623f0a3611ed841f8045ddf698", mobile="13000000554", pwd="4095666e0a3611ed841f8045ddf698d4", salt="40956", status="0", tenant_id=1001, create_time="185018639-59-79 19:77:42", update_time="413993507-89-84 39:21:92")
Record 254: (id=555) → (nick_name="409580350a3611ed841f8045ddf698", mobile="13000000555", pwd="409584930a3611ed841f8045ddf698d4", salt="40958", status="1", tenant_id=1001, create_time="185018639-60-12 75:21:74", update_time="414049802-89-37 81:35:04")
Record 377: (id=556) → (nick_name="40958ac20a3611ed841f8045ddf698", mobile="13000000556", pwd="40958eff0a3611ed841f8045ddf698d4", salt="40958", status="1", tenant_id=1001, create_time="185018639-60-46 30:66:06", update_time="414106097-88-91 23:48:16")
Record 500: (id=557) → (nick_name="409594dc0a3611ed841f8045ddf698", mobile="13000000557", pwd="4095995c0a3611ed841f8045ddf698d4", salt="40959", status="1", tenant_id=1001, create_time="185018639-60-79 86:10:38", update_time="414162392-88-44 65:61:28")
Record 623: (id=558) → (nick_name="40959f370a3611ed841f8045ddf698", mobile="13000000558", pwd="4095a3700a3611ed841f8045ddf698d4", salt="4095a", status="0", tenant_id=1001, create_time="185018639-61-80 52:43:34", update_time="414331277-87-04 92:00:64")
Record 746: (id=559) → (nick_name="4095a9270a3611ed841f8045ddf698", mobile="13000000559", pwd="4095ad5d0a3611ed841f8045ddf698d4", salt="4095a", status="1", tenant_id=1001, create_time="185018639-62-14 07:87:66", update_time="414387572-86-58 34:13:76")
Record 869: (id=560) → (nick_name="4095b3130a3611ed841f8045ddf698", mobile="13000000560", pwd="4095b7460a3611ed841f8045ddf698d4", salt="4095b", status="0", tenant_id=1001, create_time="185018639-62-47 63:31:98", update_time="414443867-86-11 76:26:88")
Record 992: (id=561) → (nick_name="4095bd250a3611ed841f8045ddf698", mobile="13000000561", pwd="4095c1600a3611ed841f8045ddf698d4", salt="4095c", status="1", tenant_id=1001, create_time="185018639-62-81 18:76:30", update_time="414500162-85-65 18:40:00")
Record 1115: (id=562) → (nick_name="4095c7210a3611ed841f8045ddf698", mobile="13000000562", pwd="4095cb560a3611ed841f8045ddf698d4", salt="4095c", status="0", tenant_id=1001, create_time="185018639-63-14 74:20:62", update_time="414556457-85-18 60:53:12")
......
Record 14768: (id=673) → (nick_name="409a5fcf0a3611ed841f8045ddf698", mobile="13000000673", pwd="409a640c0a3611ed841f8045ddf698d4", salt="409a6", status="1", tenant_id=1001, create_time="185018640-02-40 61:06:06", update_time="421142972-30-68 89:88:16")
Record 14891: (id=674) → (nick_name="409a69c30a3611ed841f8045ddf698", mobile="13000000674", pwd="409a6dfa0a3611ed841f8045ddf698d4", salt="409a6", status="0", tenant_id=1001, create_time="185018640-02-74 16:50:38", update_time="421199267-30-22 32:01:28")
Record 15014: (id=675) → (nick_name="409a73b70a3611ed841f8045ddf698", mobile="13000000675", pwd="409a78110a3611ed841f8045ddf698d4", salt="409a7", status="1", tenant_id=1001, create_time="185018640-03-07 71:94:70", update_time="421255562-29-75 74:14:40")
Record 15137: (id=676) → (nick_name="409a7dd10a3611ed841f8045ddf698", mobile="13000000676", pwd="409a820b0a3611ed841f8045ddf698d4", salt="409a8", status="1", tenant_id=1001, create_time="185018640-03-41 27:39:02", update_time="421311745-14-05 09:90:08")

# dump 自定义索引的数据记录
[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info1 -p 45 page-records|more
Record 127: (mobile="13000004276", tenant_id=1001) → (id=4276)
Record 155: (mobile="13000004277", tenant_id=1001) → (id=4277)
Record 183: (mobile="13000004278", tenant_id=1001) → (id=4278)
Record 211: (mobile="13000004279", tenant_id=1001) → (id=4279)
Record 239: (mobile="13000004280", tenant_id=1001) → (id=4280)
Record 267: (mobile="13000004281", tenant_id=1001) → (id=4281)
Record 295: (mobile="13000004282", tenant_id=1001) → (id=4282)
......
Record 15863: (mobile="13000004838", tenant_id=1001) → (id=4838)
Record 15891: (mobile="13000004839", tenant_id=1001) → (id=4839)
Record 15919: (mobile="13000004840", tenant_id=1001) → (id=4840)
Record 15947: (mobile="13000004841", tenant_id=1001) → (id=4841)
Record 15975: (mobile="13000004842", tenant_id=1001) → (id=4842)
Record 16003: (mobile="13000004843", tenant_id=1001) → (id=4843)
Record 16031: (mobile="13000004844", tenant_id=1001) → (id=4844)
Record 16059: (mobile="13000004845", tenant_id=1001) → (id=4845)

[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info -I PRIMARY index-recurse |more
ROOT NODE #3: 464 records, 8352 bytes
  NODE POINTER RECORD ≥ (id=1) → #197
  INTERNAL NODE #197: 439 records, 7902 bytes
    NODE POINTER RECORD ≥ (id=1) → #5
    LEAF NODE #5: 61 records, 7503 bytes
      RECORD: (id=1) → (nick_name="29e30a1d04f411ed841f8045ddf698", mobile="13000000001", pwd="29e3215c04f411ed841f8045ddf698d4", salt="29e32", status="1", tenant_id=1001, create_time="185017149-01-77 75:64:49", update_time="-613024240-37-42 68:67:20")
      RECORD: (id=2) → (nick_name="29e344c704f411ed841f8045ddf698", mobile="13000000002", pwd="29e35b5b04f411ed841f8045ddf698d4", salt="29e35", status="0", tenant_id=1001, create_time="185017149-02-11 31:08:81", update_time="-612967945-36-96 10:80:32")
      RECORD: (id=3) → (nick_name="29e37acf04f411ed841f8045ddf698", mobile="13000000003", pwd="29e383d504f411ed841f8045ddf698d4", salt="29e38", status="1", tenant_id=1001, create_time="185017149-02-44 86:53:13", update_time="-612911650-36-49 52:93:44")
      RECORD: (id=4) → (nick_name="29e38d1204f411ed841f8045ddf698", mobile="13000000004", pwd="29e3913c04f411ed841f8045ddf698d4", salt="29e39", status="1", tenant_id=1001, create_time="185017149-02-78 41:97:45", update_time="-612855355-36-02 95:06:56")
      RECORD: (id=5) → (nick_name="29e398d904f411ed841f8045ddf698", mobile="13000000005", pwd="29e39ceb04f411ed841f8045ddf698d4", salt="29e39", status="1", tenant_id=1001, create_time="185017149-03-11 97:41:77", update_time="-612799060-35-56 37:19:68")
      RECORD: (id=6) → (nick_name="29e3a27404f411ed841f8045ddf698", mobile="13000000006", pwd="29e3a68804f411ed841f8045ddf698d4", salt="29e3a", status="1", tenant_id=1001, create_time="185017149-03-45 52:86:09", update_time="-612742765-35-09 79:32:80")
      RECORD: (id=7) → (nick_name="29e3aea004f411ed841f8045ddf698", mobile="13000000007", pwd="29e3b2ae04f411ed841f8045ddf698d4", salt="29e3b", status="0", tenant_id=1001, create_time="185017149-03-79 08:30:41", update_time="-612686470-34-63 21:45:92")
      RECORD: (id=8) → (nick_name="29e3b97404f411ed841f8045ddf698", mobile="13000000008", pwd="29e3be3604f411ed841f8045ddf698d4", salt="29e3b", status="0", tenant_id=1001, create_time="185017149-04-12 63:74:73", update_time="-612630175-34-16 63:59:04")
      RECORD: (id=9) → (nick_name="29e3c44104f411ed841f8045ddf698", mobile="13000000009", pwd="29e3c84c04f411ed841f8045ddf698d4", salt="29e3c", status="1", tenant_id=1001, create_time="185017149-05-13 30:07:69", update_time="-612461290-32-76 89:98:40")
      RECORD: (id=10) → (nick_name="29e3ce8204f411ed841f8045ddf698", mobile="13000000010", pwd="29e3d30b04f411ed841f8045ddf698d4", salt="29e3d", status="1", tenant_id=1001, create_time="185017149-05-46 85:52:01", update_time="-612404995-32-30 32:11:52")
      RECORD: (id=61) → (nick_name="29e5da5304f411ed841f8045ddf698", mobile="13000000061", pwd="29e5deac04f411ed841f8045ddf698d4", salt="29e5d", status="0", tenant_id=1001, create_time="185017149-23-25 24:00:97", update_time="-609421360-07-61 65:06:88")
    NODE POINTER RECORD ≥ (id=62) → #6
    LEAF NODE #6: 123 records, 15129 bytes
      RECORD: (id=62) → (nick_name="29e5e48804f411ed841f8045ddf698", mobile="13000000062", pwd="29e5e8de04f411ed841f8045ddf698d4", salt="29e5e", status="1", tenant_id=1001, create_time="185017149-23-58 79:45:29", update_time="-609365065-07-15 07:20:00")
      RECORD: (id=63) → (nick_name="29e5eeba04f411ed841f8045ddf698", mobile="13000000063", pwd="29e5f33604f411ed841f8045ddf698d4", salt="29e5f", status="1", tenant_id=1001, create_time="185017149-23-92 34:89:61", update_time="-609308770-06-68 49:33:12")
      RECORD: (id=64) → (nick_name="29e5f91d04f411ed841f8045ddf698", mobile="13000000064", pwd="29e5fd7404f411ed841f8045ddf698d4", salt="29e5f", status="0", tenant_id=1001, create_time="185017149-24-25 90:33:93", update_time="-609252475-06-21 91:46:24")
      RECORD: (id=65) → (nick_name="29e6034a04f411ed841f8045ddf698", mobile="13000000065", pwd="29e607a004f411ed841f8045ddf698d4", salt="29e60", status="0", tenant_id=1001, create_time="185017149-24-59 45:78:25", update_time="-609196180-05-75 33:59:36")
      RECORD: (id=66) → (nick_name="29e60d7d04f411ed841f8045ddf698", mobile="13000000066", pwd="29e611d604f411ed841f8045ddf698d4", salt="29e61", status="1", tenant_id=1001, create_time="185017149-24-93 01:22:57", update_time="-609139885-05-28 75:72:48")
      RECORD: (id=67) → (nick_name="29e617b304f411ed841f8045ddf698", mobile="13000000067", pwd="29e61c3004f411ed841f8045ddf698d4", salt="29e61", status="0", tenant_id=1001, create_time="185017149-25-26 56:66:89", update_time="-609083590-04-82 17:85:60")

[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info -I unq_user index-recurse|more
ROOT NODE #4: 176 records, 5632 bytes
  NODE POINTER RECORD ≥ (mobile="13000000001", tenant_id=1001) → #201
  INTERNAL NODE #201: 250 records, 8000 bytes
    NODE POINTER RECORD ≥ (mobile="13000000001", tenant_id=1001) → #11
    LEAF NODE #11: 285 records, 7980 bytes
      RECORD: (mobile="13000000001", tenant_id=1001) → (id=1)
      RECORD: (mobile="13000000002", tenant_id=1001) → (id=2)
      RECORD: (mobile="13000000003", tenant_id=1001) → (id=3)
      RECORD: (mobile="13000000004", tenant_id=1001) → (id=4)
      RECORD: (mobile="13000000005", tenant_id=1001) → (id=5)
      RECORD: (mobile="13000000006", tenant_id=1001) → (id=6)
      RECORD: (mobile="13000000007", tenant_id=1001) → (id=7)
      RECORD: (mobile="13000000008", tenant_id=1001) → (id=8)
      RECORD: (mobile="13000000009", tenant_id=1001) → (id=9)
      RECORD: (mobile="13000000010", tenant_id=1001) → (id=10)
      RECORD: (mobile="13000000011", tenant_id=1001) → (id=11)
      RECORD: (mobile="13000000012", tenant_id=1001) → (id=12)
      RECORD: (mobile="13000000013", tenant_id=1001) → (id=13)
      RECORD: (mobile="13000000014", tenant_id=1001) → (id=14)
      RECORD: (mobile="13000000015", tenant_id=1001) → (id=15)
      RECORD: (mobile="13000000016", tenant_id=1001) → (id=16)
      RECORD: (mobile="13000000017", tenant_id=1001) → (id=17)
      RECORD: (mobile="13000000018", tenant_id=1001) → (id=18)
      RECORD: (mobile="13000000019", tenant_id=1001) → (id=19)
      RECORD: (mobile="13000000020", tenant_id=1001) → (id=20)
      RECORD: (mobile="13000000021", tenant_id=1001) → (id=21)
      RECORD: (mobile="13000000022", tenant_id=1001) → (id=22)
      RECORD: (mobile="13000000023", tenant_id=1001) → (id=23)
      RECORD: (mobile="13000000281", tenant_id=1001) → (id=281)
      RECORD: (mobile="13000000282", tenant_id=1001) → (id=282)
      RECORD: (mobile="13000000283", tenant_id=1001) → (id=283)
      RECORD: (mobile="13000000284", tenant_id=1001) → (id=284)
      RECORD: (mobile="13000000285", tenant_id=1001) → (id=285)
    NODE POINTER RECORD ≥ (mobile="13000000286", tenant_id=1001) → #12
    LEAF NODE #12: 570 records, 15960 bytes
      RECORD: (mobile="13000000286", tenant_id=1001) → (id=286)
      RECORD: (mobile="13000000287", tenant_id=1001) → (id=287)
      RECORD: (mobile="13000000288", tenant_id=1001) → (id=288)

最后我们来看看一条按照ID查询的sql具体的数据查找过程

select * from t_user_info where id = 100

[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info -p 3  page-dump|more
......
records:
#<struct Innodb::Page::Index::UserRecord
 type=:clustered,
 format=:compact,
 offset=126,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=5,
   next=144,
   type=:node_pointer,
   heap_number=2,
   n_owned=0,
   info_flags=1,
   offset_size=nil,
   n_fields=nil,
   nulls=[],
   lengths={},
   externs=[]>,
 next=144,
 #第一条记录的索引未1
 key=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="id",
    type="BIGINT",
    value=1,
    extern=nil>],
 row=[],
 sys=[],
 # 指向的子页面
 child_page_number=197,
 transaction_id=nil,
 roll_pointer=nil,
 length=12>

#<struct Innodb::Page::Index::UserRecord
 type=:clustered,
 format=:compact,
 offset=144,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=5,
   next=162,
   type=:node_pointer,
   heap_number=3,
   n_owned=0,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=[],
   lengths={},
   externs=[]>,
 next=162,
 # 第二条记录的索引为53936
 key=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="id",
    type="BIGINT",
    value=53936,
    extern=nil>],
 row=[],

在page=3的页面中，第一条数据的索引为1，第二条就是53936了
第一条数据子页面为page=197

我们继续看page=197的页面

[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info -p 197  page-dump|more
.......
# 直接找到records记录
records:
#<struct Innodb::Page::Index::UserRecord
 type=:clustered,
 format=:compact,
 offset=126,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=5,
   next=144,
   type=:node_pointer,
   heap_number=2,
   n_owned=0,
   info_flags=1,
   offset_size=nil,
   n_fields=nil,
   nulls=[],
   lengths={},
   externs=[]>,
 next=144,
 key=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="id",
    type="BIGINT",
    value=1,
    extern=nil>],
 row=[],
 sys=[],
 child_page_number=5,
 transaction_id=nil,
 roll_pointer=nil,
 length=12>

#<struct Innodb::Page::Index::UserRecord
 type=:clustered,
 format=:compact,
 offset=144,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=5,
   next=162,
   type=:node_pointer,
   heap_number=3,
   n_owned=0,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=[],
   lengths={},
   externs=[]>,
 next=162,
 key=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="id",
    type="BIGINT",
    value=62,
    extern=nil>],
 row=[],
 sys=[],
 child_page_number=6,
  transaction_id=nil,
 roll_pointer=nil,
 length=12>

#<struct Innodb::Page::Index::UserRecord
 type=:clustered,
 format=:compact,
 offset=162,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=5,
   next=180,
   type=:node_pointer,
   heap_number=4,
   n_owned=0,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=[],
   lengths={},
   externs=[]>,
 next=180,
 key=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="id",
    type="BIGINT",
    value=185,
    extern=nil>],
 row=[],
 sys=[],
 child_page_number=7,
 transaction_id=nil,
 roll_pointer=nil,
 length=12>

page=197中，第1条记录的最小值为1，第2条的最小值为62，第3条的记录为185，已经比100大了，直接用第2条记录
第2条记录的page=6

[root@localhost data]# innodb_space -s ibdata1 -T innodb_space/t_user_info -p 6  page-dump
.......
# 还是定位到records
records:
#<struct Innodb::Page::Index::UserRecord
 type=:clustered,
 format=:compact,
 offset=131,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=11,
   next=254,
   type=:conventional,
   heap_number=2,
   n_owned=0,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=[],
   lengths={"nick_name"=>30, "mobile"=>11, "pwd"=>32, "salt"=>5, "status"=>1},
   externs=[]>,
 next=254,
 key=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="id",
    type="BIGINT",
    value=62,
    extern=nil>],
 row=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="nick_name",
    type="VARCHAR(90)",
    value="29e5e48804f411ed841f8045ddf698",
    extern=nil>,
......
#<struct Innodb::Page::Index::UserRecord
 type=:clustered,
 format=:compact,
 offset=4805,
 header=
  #<struct Innodb::Page::Index::RecordHeader
   length=11,
   next=4928,
   type=:conventional,
   heap_number=40,
   n_owned=0,
   info_flags=0,
   offset_size=nil,
   n_fields=nil,
   nulls=[],
   lengths={"nick_name"=>30, "mobile"=>11, "pwd"=>32, "salt"=>5, "status"=>1},
   externs=[]>,
 next=4928,
 key=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="id",
    type="BIGINT",
    value=100,
    extern=nil>],
 row=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="nick_name",
    type="VARCHAR(90)",
    value="29e76a7204f411ed841f8045ddf698",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="mobile",
    type="VARCHAR(33)",
    value="13000000100",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="pwd",
    type="VARCHAR(96)",
    value="29e76ec304f411ed841f8045ddf698d4",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="salt",
    type="VARCHAR(30)",
    value="29e76",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="status",
    type="VARCHAR(3)",
    value="0",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="tenant_id",
    type="SMALLINT",
    value=1001,
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="create_time",
    type="DATETIME",
    value="185017149-37-68 08:06:73",
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="update_time",
    type="DATETIME",
    value="-607000676-87-58 76:71:04",
    extern=nil>],
 sys=
  [#<struct Innodb::Page::Index::FieldDescriptor
    name="DB_TRX_ID",
    type="TRX_ID",
    value=7968,
    extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor
    name="DB_ROLL_PTR",
    type="ROLL_PTR",
    value=
     #<struct Innodb::DataType::RollPointerType::Pointer
      is_insert=true,
      rseg_id=61,
      undo_log=#<struct Innodb::Page::Address page=309, offset=1856>>,
    extern=nil>],
 child_page_number=nil,
 transaction_id=7968,
 roll_pointer=
  #<struct Innodb::DataType::RollPointerType::Pointer
   is_insert=true,
   rseg_id=61,
   undo_log=#<struct Innodb::Page::Address page=309, offset=1856>>,
 length=118>
 .......

所以整个查询的数据页流向为:

page=3(根节点页level=2)->page=197(level=1的索引页)->page=6的（level=0的索引页）数据页,一共三次磁盘IO；
实际上InnoDB通常会把索引的根节点页常驻内存,亦即根节点的磁盘IO实际上可以省略,总共花费2次磁盘IO即可以定位到数据页（一张表的根为16kb，100张也就1.6mb，对mysql来说轻轻松松，如果每张表平均5个索引，占用内存空间也不到10MB）
如果是非聚簇索引多了两次磁盘IO

后记：innodb_ruby这个工具还是蛮好用的，其他的命令，大家自己去挖掘吧。

mysql之innodb存储结构

mysql之innodb索引结构

mysql事务-innodb中的redolog详解

innodb中的undolog 详解

mysql事务-MVCC

mysql事务-锁