背景
之前学习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这个工具还是蛮好用的，其他的命令，大家自己去挖掘吧。
