MySQL(InnoDB表空间工具innodb_ruby)

后面也会持续更新，学到新东西会在其中补充。
建议按顺序食用，欢迎批评或者交流！
缺什么东西欢迎评论！我都会及时修改的！
Jeremy Cole的博客：blog.jcole.us/innodb/
ruby安装后 -bash: gem: command not found 错误
InnoDB 表空间可视化工具innodb_ruby
Linux 安装Ruby详解(在线和离线安装)
Innodb-ruby深入探索Innodb存储结构
Innodb数据结构空间占用

前言

若没有学过行结构，索引，页的结构等等的知识是看不懂的！
可以看我往期文章！

环境搭建

因为学表空间比较费劲因此我找了个工具来可视化表空间
怎可想！安装这个工具安装了一天！版本问题以及资料不全难啊！

注意：！！！！！！！！！
版本是centos7.9 mysql5.7 用其他的版本都会报错了（我都试过了！！！！）

yum -y install zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel tk-devel gcc make gdbm-devel db4-devel libpcap-devel xz-devel libffi-devel wget

#安哪都可以 版本一定要是3.0的装其他有问题
wget -O ruby.tar.gz https://cache.ruby-china.com/pub/ruby/3.0/ruby-3.0.0.tar.gz

#解压缩
tar -zxf ruby.tar.gz
#进入对应目录
cd ruby-3.0.0/
#在当前解压的目录下，编辑配置并编译源代码进行安装
# -prefix是将ruby安装到指定目录，也可以自定义
./configure --prefix=/usr/local/ruby    
make && make install
# 耐心等待...
ln -s /root/ruby-3.0.0/ruby /usr/bin/ruby
ln -s /root/ruby-3.0.0/bin/gem /usr/bin/gem

[root@CentOS8 ruby-3.0.0]# ruby -v
ruby 3.0.0p0 (2020-12-25 revision 95aff21468) [x86_64-linux]
[root@CentOS8 ruby-3.0.0]# gem -v
3.2.3

gem install bigdecimal
gem install bindata
gem install digest-crc
gem install getoptlong
gem install histogram
gem install gnuplot
gem install rspec
gem install rubocop
gem install rubocop-rspec
sudo gem install innodb_ruby -V

ln -s /usr/local/ruby/lib/ruby/gems/3.0.0/gems/innodb_ruby-0.14.0/bin/innodb_space /usr/bin/innodb_space

git clone https://github.com/jeremycole/innodb_ruby.git
cd innodb_ruby

innodb_space --help

mysql> create database testdb;
mysql> use testdb;
mysql> create table test1(id int primary key auto_increment,c1 varchar(10),dt datetime ,key c1(c1));
mysql> insert into test1 values(1,'abc',now());
#查看数据目录
mysql> show variables like '%datadir%';
+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| datadir       | /soft/mysql/mysql9.0/data/ |
+---------------+----------------------------+
1 row in set (0.05 sec)
#开启独立表空间！
vi /etc/my.cnf
[server]
innodb_file_per_table=1


drop table t;
CREATE TABLE t(
a INT NOT NULL,
b VARCHAR(8000),
c INT NOT NULL,
PRIMARY KEY(a),
KEY idx_c(c)
)ENGINE=INNODB;
INSERT INTO t SELECT 1,REPEAT('a',7000),-1;
INSERT INTO t SELECT 2,REPEAT('a',7000),-2;
INSERT INTO t SELECT 3,REPEAT('a',7000),-3;
INSERT INTO t SELECT 4,REPEAT('a',7000),-4;
INSERT INTO t SELECT 5,REPEAT('a',7000),-5;

通过Innodb_space查看系统表空间信息

[root@MySQL5 mysql]#  innodb_space  -s ibdata1  system-spaces

testdb是数据库
t是数据表
pages：总数据页数量，这里t表，一共使用到了9个 page页

指定表查看索引空间信息

innodb_space -s ibdata1 -T testdb/t space-indexes;

• id：索引ID，相同的ID表示是同一个索引
• root：当前索引的根page页编号
• fseg：leaf 叶子节点 internal 非叶子节点
• used：索引使用到了多少page页
• allocated：分配了多少page页
• fill_factor：page页使用占比 ：used / allocated

查看节点page空间信息

查看主键PRIMARY root页面空间信息

innodb_space -s ibdata1 -T testdb/t -p 3 page-records

查看指定leaf-page（聚簇索引）空间信息

innodb_space -s ibdata1 -T testdb/t -p 5 page-records

这里的a = 1 就是主键值 指向表的数据

innodb_space -s ibdata1 -T testdb/t -p 6 page-records

innodb_space -s ibdata1 -T testdb/t -p 7 page-records

不截图了懂这个结构就行！

查看二级索引index root空间信息

innodb_space -s ibdata1 -T testdb/t -p 4 page-records

c 是索引列 c = -5 指向 a = 5 主键 也就是聚簇索引。

通过information_schema.tables查看数据空间占用信息

mysql> select table_name,data_length,index_length from information_schema.tables where table_name = 't';
+------------+-------------+--------------+
| table_name | data_length | index_length |
+------------+-------------+--------------+
| t          |       65536 |        16384 |
+------------+-------------+--------------+
1 row in set (0.01 sec)

DATA_LENGTH
对于InnoDB，DATA_LENGTH 是为聚集索引分配的大约空间量（以字节为单位）。具体来说，它是聚集索引大小（以页为单位）乘以InnoDB页大小。
65536 / 16384 = 4 也就验证了之前的这张图

INDEX_LENGTH
对于InnoDB，INDEX_LENGTH 是为非聚集索引分配的大约空间量（以字节为单位）。具体来说，它是非聚集索引大小的总和（以页为单位）乘以 InnoDB页大小。
16384 / 16384 = 1

查看t表使用总page数

每个类型页数总计space-page-type-summary

innodb_space -s ibdata1 -T testdb/t space-page-type-summary

总占的页数(page)是5 + 1 + 1 + 1 + 1 = 9

查看t表真实占用空间大小

select * from information_schema.innodb_sys_tablespaces where name = 'testdb/t';

• FILE_SIZE
  文件的表观大小，表示未压缩的文件的最大大小。
• ALLOCATED_SIZE
  文件的实际大小，即磁盘上分配的空间量。

结论：
1.information_schema.table查看DATA_LENGTH，主键索引也就是真实数据的空间占用大小。
2.information_schema.innodb_sys_tablespace 查看File_size,为当前t总占用空间大小。

# t表使用总page页数9 
9 x 16384 = 147456
file_size = 147456
# t表聚簇索引page页数4
4 x 16384 = 65536
DATA_LENGTH = 65536

每行数据占用空间

innodb_space -s ibdata1 -T testdb/t space-indexes

PRIMARY索引一共分2层，第1层非叶子节点只有一个page，第2层叶子节点共3个page页，需要注意的是这里叶子节点为0层，依次往上推。

查看每一层tree空间占用
1层，非叶子节点（此处为root节点）：

innodb_space -s ibdata1 -T testdb/t -I PRIMARY -l 1 index-level-summary

page页编号：
level：当前page所处的层级
data：当前page占用空间大小
free：当前page空闲空间大小，page大小固定16k，所以就会存在页用不满的现象
recoreds：当前page存储的数据行数，这里因为是非叶子节点，所以指的是主键行数。
min_key：当前页中最小的索引列，这里是主键 1。

0层，叶子节点：

innodb_space -s ibdata1 -T testdb/t -I PRIMARY -l 0 index-level-summary

通过data / records = 每行数据占的空间

一行数据确实挺长的…

page#5为例，看下当前page页空间分布说明：

innodb_space -s ibdata1 -T testdb/t -p 5 page-illustrate

创建空的表

create table e(id int);

space-page-type-regions 模式将总结相同页面类型所有连续区域的类型：

innodb_space -f testdb/e.ibd space-page-type-regions

FSP_HDR 、 IBUF_BITMAP 、 INODE ，以及一个用于（空）索引根的 INDEX 页面。还有两个未使用的 FREE (ALLOCATED) 页面。

这里我截的是e表是独立表空间。系统表空间SYS就是系统表空间里有的。

space-lists 模式可会汇总表空间中的区段描述符链表和inode列表

innodb_space -f testdb/e.ibd space-lists

free_frag 扩展描述符列表有条目，并且其中只有一个扩展(extent)。


free_inodes 链表中有一个 INODE 页面。

innodb_space -f testdb/e.ibd -L free_frag space-list-iterate

space-list-iterate 命令检查 free_frag 链表的内容，说明在区段列表中所有区段内页面的使用情况("#"表示该页面已使用，"." 表示页面是空闲的) :
FPS_HDR(1) + IBUF_BITMAP(1) + INODE(1) + INDEX(1) = 4
FREE = 2

space-indexes可以将空间中所有索引的文件段进行汇总:

innodb_space -f testdb/e.ibd space-indexes

只有“内部”文件段分配了页面，并且只分配了一个页面。
index-fseg-internal-lists 模式将汇总“内部”文件段中的区段链表:

innodb_space -f testdb/e.ibd -p 3 index-fseg-internal-lists

这三个列表都是空的，因为这个空表没有分配任何完整的区段。
那么，已使用的一个页面在哪里呢？这是一个“碎片”页面，可以使用 index-fseg-internal-frag-pages 模式列出:

innodb_space -f testdb/e.ibd -p 3 index-fseg-internal-frag-pages

只有一个索引页。

一个有一百万行的表

gem install mysql2 -V
[root@MySQL5 ~]# vi insert_data.rb
#!/usr/bin/env ruby

require "mysql2"

m = Mysql2::Client.new(host: "127.0.0.1", username: "root", password: "password", database: "testdb")

m.query("DROP TABLE IF EXISTS t")

m.query("CREATE TABLE t (i INT UNSIGNED NOT NULL, PRIMARY KEY(i)) ENGINE=InnoDB")

(1..1000000).to_a.shuffle.each_with_index do |i, index|
  m.query("INSERT INTO t (i) VALUES (#{i})")
  puts "Inserted #{index} rows..." if index % 10000 == 0
end

[root@MySQL5 ~]# ruby insert_data.rb
Inserted 10000 rows...
...
Inserted 990000 rows...

innodb_space -f testdb/t.ibd space-page-type-regions

统计每个类型的页占用页的数量space-page-type-regions

注意，在 INDEX 页面的连续区域中间有一些FREE (ALLOCATED)页面组成的间隙。这是因为InnoDB 不能保证按顺序使用空闲页面，而且有关批量数据加载的许多优化也会导致页面无序使用。
空间中的链表，有一些区段在 free 链表中，也有一些区段在free_frag链表中:

innodb_space -f testdb/t.ibd space-lists

innodb_space -f testdb/t.ibd -L free space-list-iterate

free_frag 区段描述符链表中的区段有一些“碎片”页面被使用:

innodb_space -f testdb/t.ibd -L free_frag space-list-iterate

通过 space-indexes 命令可以看出大量已使用的页面都在叶子节点，只有3个“内部”页面来管理 b+树 中的2,152个“叶”页面:

innodb_space -f testdb/t.ibd space-indexes

因为内部文件段只有3个页面，所以文件段列表都是空的:

innodb_space -f testdb/t.ibd -p 3 index-fseg-internal-lists

innodb_space -f testdb/t.ibd -p 3 index-fseg-internal-frag-pages

3 个已使用的页面被分配为碎片页:

innodb_space -f testdb/t.ibd -p 3 index-fseg-leaf-lists

有 33个 full 区段和 6 个 not_full 区段:

innodb_space -f testdb/t.ibd -p 3 index-fseg-leaf-frag-pages

此外，叶文件段已经分配了所有可以分配的 32 个碎片页面
也就是一个段分配到32个碎片页面以后会申请新的区。

full区段都是“满”的:

innodb_space -f testdb/t.ibd -p 3 -L full index-fseg-leaf-list-iterate

not_full区段都是部分空闲的

 innodb_space -f testdb/t.ibd -p 3 -L not_full index-fseg-leaf-list-iterate

先到这把学半天人都学麻了

总结

所有结论都需要反复测试！如果有错误欢迎指正！一起努力！
如果喜欢的话，请点个赞吧就算鼓励我一下。