MySQL表结构，表空间，段，区，页，MVCC ，undo 事务槽

妖狐艹你老母 2021-12-15 12:15 257阅读 0赞

索引组织表（IOT表）：为什么引入索引组织表，好处在那里，组织结构特点是什么，如何创建，创建IOT的限制LIMIT。

IOT是以索引的方式存储的表，表的记录存储在索引中，索引即是数据，索引的KEY为PRIMARY KEY。数据的查询可以通过查询索引的同时查询到数据，因为索引和数据存储在一个数据块中，减少了一次磁盘I/O。数据是按照主键顺序创建的索引，索引中有对应的数据，这样依据主键做范围扫描时，减少了读取的数据块数量，减少了磁盘I/O。也减少了索引的存储空间，因为索引和数据存在一起。如果是B树索引就需要创建对索引的存储空间。

两个好处：

一个是减少了范围扫描的磁盘I/O数据块数（页块中有数据，有索引）  
一个是避免了索引自身的空间开销，因为索引和数据在一起，不需要额外的空间。这些优点都是索引组织表的特点决定。  
何时使用IOT：

（1）数据的相关数据片需要存储在一起。  
（2）数据必须按照指定的顺序物理存储。IOT表多用于信息获取、空间应用和OLAP应用（OLAP：Online-Analysis Process）（联机分析处理）。  
HOT与IOT

myisam使用的堆组织表(Heap Organize Table, HOT)使用B-tree索引的存储格式，显示都是随机顺序。  
innodb表是索引组织表(Index Organized Table, IOT)，它的索引则是采用 clustered index 方式，因此主键会按照顺序存储，每次有记录有更新时，会重新整理更新其主键。因此无论是直接从 myisam 表转换过来的，还是后来插入的记录，显示时都会按照主键的顺序。  
mysql> select \* from duplicate\_key;  
\+----+------+  
| id | p\_id |  
\+----+------+  
| 2 | 2 |  
| 3 | 3 |  
| 5 | 5 |  
| 4 | 4 |  
| 6 | 6 |  
| 7 | 7 |  
\+----+------+  
6 rows in set (0.00 sec)  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
此时的duplicate\_key表是myisam引擎的，  
update duplicate\_key set id=id-1的时候会提示  
Duplicate entry ‘4’ for key ‘PRIMARY’错误。

update duplicate\_key set id=id-1 order by id；  
如果这样做，就不会出错，原理上面已做出了说明。

如果是innodb引擎就不会出现这样的情况，因为他的聚集索引存储方式会按顺序来显示。

在myisam引擎使用的时候如果你delete了其中的几条数据，这时的表就是一个hole表。  
如果你不使用表维护命令进行维护，你新插入的数据就会放到你刚刚删除的那个位置。

lnnoDB存储引擎表类型  
对比Oracle 支持的各种表类型 ，InnoDB 存储引擎表更像是 Orale 中的索引组织表 ( index organized table ） 。在InnoDB存储引擎表中 ，每张表都有个主键 ，如果在创建表时没有显式地定义主键 （ Primary Key ) , 则innoDB存储引擎会按如下方式选择或创建主键 。

①首先表中是否有非空的唯一索引 （ Unique NOT NULL ），如果有，则该列即为主键；  
②不符合上述条件，InnoDB存储引擎自动创建一个 6个字节大小的指针。  
lnnoDB逻辑存储结构  
InnoDB存储引擎的逻辑存储结构和 Oracle大致相同 ，所有数据都被逻辑地存放在一个空间中 ，我们称之为表空间 （ tablespace ） 。表空间又由段 （ segment ） 、区 （ extent ） 、页 ( page ） 组成 。页在一些文档中有时也称为块（block) , InnoDB存储引擎的逻辑存储结构大致如图4-1所示。

段：也叫表；  
区：物理上连续的几个页；  
页：16K  
表空间  
表空间可以看做是InnoDB存储引擎逻辑结构的最高层 ，所有的数据都是存放在表空间中。已经介绍了默认情况下 InnoDB存储引擎有一个共享表空间 ibdata1 ，即所有数据都放在这个表空间内 。如果我们启用了参数innodb\_file\_per\_table ，则每张表内的数据可以单独放到一个表空间内 。

segment：表；  
extent：物理上连续的几个页；  
page（block）：16K  
（即：将共享表空间独立出去 innodb\_file\_per\_table 参数）

对于启用了innodb\_file\_per\_table的参数选项，需要注意的是 ，每张表的表空间内存放的只是数据、索引和插入缓冲 ，其他类的数据，如撤销（ Undo） 信息、系统事务信息、 二次写缓冲 （double write buffer ） 等还是存放在原来的共享表空间内。这也就说明了另一个问题：即使在启用了参数innodb\_file\_per\_table之后，共享表空间还是会不断地增加其大小。  
看看初始共享表空间文件有多大 ：

mysql> show variables like %innodb\_file\_per\_table%';  
1

mysql>system ls -lh /var/lib/mysql/ib\*  
1

mysql  
做insert时：要做索引。

IOT（Index Orangized Table，索引组织表）的特点：

1.表按照主键排好序；  
2.主键上有一棵树；  
3.表本身就是索引；  
4.叶子节点就是数据节点；  
5.IOT对于通过主键找表数据的成本最低。  
所以，表选择主键的时候：

1.表上有明显的访问条件，会员条件、会员id；  
2.这个列数据唯一；  
假设没有上面的条件，我们也要选择一个依次递增的数字列来作为主键列；  
假设没有选择主键，mysql会自动建立一个隐含的主键，默认6个字节。  
【非空+唯一==主键】  
主键的选择最好不要出现过度跳跃的情况。特别是对于insert速度要求很高的系统。对于select要求很高的系统就无所谓了。

IOT表的特点：

①对于insert资源消耗相对大；  
②特别是批量insert；  
通过索引从表中取20个数据 ：  
①走索引可能效果很差  
②全表扫描效果也不好  
③这时，IOT就开始启命令了。【没有索引和跳的环节~！】

IOT特别适合的场景：  
从表中批量取数据，这个条件必须是主键列条件。

如何解决批量insert？  
答：让insert主键依次递增。

表空间：表、索引、insert buffer bitmap【记录二级索引每一个数据页的空闲空间剩余情况】  
【知道：计算机里很多地方用bitmap（位图）来存储东西。】

共享表空间：

undo  
insert buffer  
double write（2M）  
system transaction table（系统事务表16K）  
undo可能会导致ibdata共享表空间变得很大很大。。所以，在安装好时，先把undo独立出去。

将undo独立出去：删除数据库，重新安装，重新初始化：  
改参数：  
directory：指定目录（绝对路径）  
tablespaces：>0，eg：1,2,3

undo可能会变得很大  
1.大事务  
一个事务中有大量的dml，产生了大量的undo数据；  
2.长事务  
事务开始后，长时间不提交；

Compact行记录格式  
Compact行记录是在MySQL 5 .0时被引入的，其设计目标是能高效存放数据。简单来说，如果一个页中存放的行数据越多，其性能就越高 。Compact 行记录以如下方式进行存储：

从图4-2可以看到 ，Compact行格式的首部是一个非 NULL变长宇段长度列表 ，而且是按照列的顺序逆序放置的 。当列的长度小于 255 字节 ，用 1字节表示，若大于255个字节， 用2个字节表示，变长宇段的长度最大不可以超过2个宇节（ 这也很好地解释了为什么 MySQL中varchar 的最大长度为65535 ，因为2个字节为 16位 ，即2^16=1=65535 ） 。第二个部分是NULL标志位，该位指示了该行数据中是否有 NULL值 ，用1表示。该部分所占的字节应该为 bytes 。接下去的部分是为记录头信息（record header），固定占用5个字节（40位），每位的含义见表 4-1。最后的部分就是实际存储的每个列的数据了 ，需要特别注意的是 ， NULL不占该部分任何数据 ，即NULL 除了占有NULL标志位 ，实际存储不占有任何空间 。 另外有一点需要注意的是,每行数据除了用户定义的列外 ，还有两个隐藏列 ，事务ID列和回滚指针列 ，分别为6个字节和7个字节的大小 。若InnoDB 表没有定义Primary Key ，每行还会增加一个6字节的RowID列。

（头信息共5B，即40位（bit））

如何评估ddl语句对表的操作风险  
eg：  
select t-bir from t where id=100；  
需要读两个列：t\_bir，id  
描述：假设这个表有4个列，id（9B），varchar（20B），日期（8B），varchar（20B）  
在第一行，变长字段长度列表记录了varchar的大小，NULL标志位记录这行是否有空值，记录头信息，列数据1，列数据2……

DDL操作对表的风险分析：  
修改表的结构（增加列）：

①alter table t1 add column (desc varchar(30))；  
锁住整个表，假设有1000万行。  
②对列重命名 alter table t1 rename column t\_bir t\_birthday；  
ddl操作会锁住表！  
要注意，是否意味着对所有数据行进行处理？  
是的话，就不要做DDL；  
不是的话，就可以。  
【一般，修改列的长度、重命名、增加列啥的，时间都很长，读写很大，且会锁住表。然后生产环境就不能用了。。。】  
MySQL如何减少delete操作对undo空间的占用。  
对oracle来说，delete操作会记录在undo中。而对于MySQL来说，5字节的记录头信息里，deleted\_flag（1位）记录的就是该行是否已被删除，从而减少大量的undo使用；next\_record（16位）记录的是页中下一条记录的相对位置，便于从IOT表里面一行行的扫描。

【上边提到的两个隐藏列：事务ID列（6B）和回滚指针列（7B）】

详细描述一下rollback的过程：系统事务表、回滚段、回滚段头、事务槽、事务数据块链表。

我们开启一个事务（start transaction），这个事务会被分配一个事务id：

show engine innodb status \\G  
1  
  
（事务号是1716486，已经活跃20s了）

ibdata里面的 系统事务表：

回滚表空间  
回滚段  
事务  
一个事务是怎样开始的：

1.生成一个事务id；  
2.读取系统事务表，找到一个回滚段（回滚段相对空闲），读取段头块，段头里面找到空闲的一行，把事务ID写进去，一个事务就这样开始了。

解析：  
事务开始时，生成一个事务ID，读取系统事务表，找到一个空闲的undo段，读取段头块，段头里面找到空闲的一行，把事务ID写进去，一个事务就这样开始了。  
当修改数据行时，①事务ID会写到修改的数据行里②数据行的修改前的数据会保存到undo段的数据页③修改的数据行里面的回滚指针同时会指向②所对应的undo页。  
这个事务没有提交，还没结束。此时去修改别的数据行，它们也会有自己对应的undo页，这些同一个事务的undo页会一个个的连起来（事务数据块链表）。而段头的第一个事务槽会指向最后一个undo页（事务数据块链表的末尾），而undo页依次向前指。因为这样rollback的时候就会逆着回滚（修改时是顺序，回滚当然是逆序了…）  
详细描述MySQL如何实现读已提交数据的过程：活动事务、roll pointer、事务ID。

读这行数据的时候，会先找事务ID，看事务有没有提交，读事务槽就可以。（因为当前系统未提交的数据、事务的状态等都在事务槽存着呢）。  
再通过回滚指针roll pointer 找修改前的数据。

页  
常见的页类型有 ：

数据页 （ B-tree Node ）  
Undo页 （ Undo Log Page ）  
系统页 （ System Page ）  
事务数据页 （ Transaction system Page ）  
插入缓冲位图页 (Insert Buffer Bitmap ）  
插入缓冲空闲列表页 (Insert Buffer Free List ）  
未压缩的二进制大对象页 （Uncompressed BLOB Page ）  
压缩的二进制大对象页 （Compressed BLOB Page ）  
InnoDB数据页结构  
我们已经知道页是 InnoDB存储引擎管理数据库的最小磁盘单位。页类型为B-tree node 的页，存放的即是表中行的实际数据了。我们将从底层具体地介绍InnoDB数据页的内部存储结构 。

InnoDB数据页由以下七个部分组成：

File Header （文件头）  
Page Header （ 页头）  
Infimun + Supremum Records  
User Records （用户记录 ，即行记录）  
Free Space （空闲空间）  
Page Directory （页目录）  
File Trailer （文件结尾信息）  
  
File Header 、Page Header 、File Trailer的大小是固定的，用来标示该页的一些信息，如Checksum 、数据所在索引层等 。其余部分为实际的行记录存储空间，因此大小是动态的。  
深刻理解varcahr数据类型，特别是最大长度、M的含义。  
【PS：utf8：一个字符对应2-3个字节；gbk一个字符对应2个字节。】

如果创建varchar长度为65535的表，会报下面的错误：  
  
这是因为还有别的开销，因此实际能存放的长度为65532.  
  
注意！65532指的是所有的varchar列加起来也不能>=65532！！！

varchar（M）  
M：最大字符长度。列存储的字符数不能超过M。  
例如 varchar（20） –》这个列最长可以存储20个字符  
如果用的是gbk字符集：则是40个字节  
如果用的是utf8字符集：则是40-60个字节  
如果用的是ascii字符集：则是20个字节  
====M的范围  
注意：M最大不能超过65532！！65532的含义是字节的含义。  
即，如果我们使用gbk字符集，则M不能超过65532/2；  
===怎么测试：  
①create table t1(name varchar(65529),name1 varchar(2)) CHARACTER SET ascii;  
如果现实ok，则行。否则会报错说超出了。  
②create table t2(name varchar(65532),name1 blob(65535)) CHARACTER SET ascii;  
最大行长度的定义，包括和不包括blob的含义。  
定义行列时要注意：  
整体行的最长长度要小于65535，每个列还要小于65532字节！

blob（Binary Large Object，二进制大对象）  
BLOB类型的字段用于存储二进制数据 ；  
MySQL中，BLOB是个类型系列，包括：TinyBlob、Blob、MediumBlob、LongBlob，这几个类型之间的唯一区别是在存储文件的最大大小上不同。

MySQL的四种BLOB类型 ：

类型 大小(单位：字节)  
TinyBlob 最大 255  
Blob 最大 65K  
MediumBlob 最大 16M  
LongBlob 最大 4G  
blob不占整体行长度！实际blob占用768字节。即行的实际长度=65535-768；  
blob可以存字符、图片、文件。

表设计的时候，最基本的原则。简述垂直拆分，做一个垂直拆分的例子，并对垂直拆分的表进行访问。  
答：  
（1）除了blob以外，行长度不要超出16K。而且要远远的小于16K，甚至于只有几百字节。  
如果一个行的长度确实很长（但肯定不会>16K），我们会对这个表进行垂直拆分。  
确保一个数据页中能够存储足够多的数据行。  
（2）垂直拆分的例子：  
create table t1(id int primary key,name varchar(20),name1 varchar(100));  
拆分为：  
create table t1(id int primary key,name varchar(20));  
create table t1\_name1(id int primary key,name1 varchar(100));  
  
访问的时候，用上图中的where t1.id=t1\_name.id 即可。  
行溢出的情况及应对措施：  
①行太长了，列又多，就会出现多个列溢出；（设计问题，建议重做表，再做垂直拆分）  
②行中有一列是varchar，假设是放描述产品信息的，可能varchar列就长了。（不是设计问题，建议做垂直拆分，把这个列单独拿出去做个新表）  
③行中有blob列（说明存的数据比较大）。一般建议做垂直拆分，把blob列单独拆分出去做个新表。  
但是！如果每次都要访问上面的varchar列或者blob列，就不用管了。  
checksum技术实现以及意义：  
Checksum：【电脑】总和检验码，校验总和。在数据处理和数据通信领域中，用于校验目的的一组数据项的和。这些数据项可以是数字或在计算检验总和过程中看作数字的其它字符串。  
①在Linux中，cksum+具体软件包名，就能得到一串数。如果得到的数与官网的数是一样的，就可以明白是安全的、完整的。  
②在MySQL中，有个参数 innodb\_checksum，该参数为ON时，数据从磁盘到内存时，会做checksum，得出一个值，所得值如果跟数据页的头部一致，则数据页没有损坏。

MySQL五种约束的风险评估：  
数据完整性：

实体完整性；  
域完整性；  
参照完整性；  
五种完整性约束：

primary key；主键约束  
unique key；唯一约束  
foreign key；外键约束  
default；默认值约束  
not null 非空约束  
MVCC特性解读

InnoDB引擎的特点：

1.支持行锁（各干各的活，互补影响）、并发性能好；  
2.支持MVCC（多版本并发控制Multi-Version Concurrency Control）（避免使用锁）；  
3.支持外键；  
4.提供一致性非锁定读，并发性能更强；  
5.能够使用大内存和充分利用cpu资源。  
事务开始时，系统事务表以轮询的方式，找事务表空间里面的空闲undo段（一共128个），把事务写到undo段头的事务槽（共1024个）里，每个段头可以写512个事务，所以，并发时，能写512\*128个事务。  
即：系统事务表（轮询的方式）–回滚段–段头块（1024事务槽、512事务）–事务信息写入事务槽–一个事务开始了。

事务开始后，假设要修改数据块，系统会把修改前的数据放到undo块中，roll pointer回滚指针指向的是对应的undo块，所有的undo块会连起来，而事务槽指向的是最后一个undo块。  
【注意！未提交的事务–》在undo块的事务槽存着！】  
commit提交了，做的事：  
①在事务槽里面，把对应的事务标记为事务已提交。

undo的作用：

①rollback  
②写不阻塞读（防止未提交数据）  
③崩溃恢复（redo前滚，undo回滚）  
在并发读写数据库时，读操作可能会不一致的数据（脏读）。为了避免这种情况，需要实现数据库的并发访问控制，最简单的方式就是加锁访问。由于，加锁会将读写操作串行化，所以不会出现不一致的状态。但是，读操作会被写操作阻塞，大幅降低读性能。  
在MVCC协议下，每个读操作会看到一个一致性的snapshot，并且可以实现非阻塞的读。MVCC允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务ID，在同一个时间点，不同的事务看到的数据是不同的。  
说白了，其实MVCC多版本并发控制，就是在undo里面，只要undo空间足够，就可以保存数据行的不同时刻的修改情况。所谓的版本，可以理解为不同时间的修改。  
即：一直往前找undo。

由于在update操作提交之前，不能影响已有数据的一致性，所以不会改变旧的数据，update操作会被拆分成insert + delete。需要标记删除旧的数据，insert新的数据。只有update提交之后，才会影响后续的读操作。而对于读操作而且，只能读到在其之前的所有的写操作，正在执行中的写操作对其是不可见的。  
上面说了一堆的虚的理论，下面来点干货，看一下mysql的innodb引擎是如何实现MVCC的。innodb会为每一行添加两个字段，分别表示该行创建的版本和删除的版本，填入的是事务的版本号，这个版本号随着事务的创建不断递增。在repeated read的隔离级别下，具体各种数据库操作的实现：  
select：满足以下两个条件innodb会返回该行数据：（1）该行的创建版本号小于等于当前版本号，用于保证在select操作之前所有的操作已经执行落地。（2）该行的删除版本号大于当前版本或者为空。删除版本号大于当前版本意味着有一个并发事务将该行删除了。  
insert：将新插入的行的创建版本号设置为当前系统的版本号。  
delete：将要删除的行的删除版本号设置为当前系统的版本号。  
update：不执行原地update，而是转换成insert + delete。将旧行的删除版本号设置为当前版本号，并将新行insert同时设置创建版本号为当前版本号。  
其中，写操作（insert、delete和update）执行时，需要将系统版本号递增。  
由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。  
通过MVCC很好的实现了事务的隔离性，可以达到repeated read级别，要实现serializable还必须加锁。  
长事务和大事务的危害及处理方式  
大事务（一次修改大量行。生产中很少）的危害：  
会占用过多的undo页，

长事务：开始一个事务，一直不提交。  
长事务的危害：  
假设昨天九点的时候开始了一个事务，但没有提交。由于MVCC机制，一直到今天的九点之前，24小时内所做的所有的DML操作，都不会被看到（原来是1万行，现在看，还是1万行）。而且会产生过多的undo数据（几十G）且不能被清空（purge）。  
怎么看当前系统的长事务和大事务？

information\_schema \#存放的是数据字典  
INNODB\_TRX \#存放的是当前活动的事务  
select \* from INNODB\_TRX \\G \#查询当前有哪些活动的事务

（modified：1000 \#这个事务修改了1000行）  
（trx\_rows\_locked：1022 \#锁了1022行）

如何处理？

大事务的处理：已经影响生产了，因为回滚太慢，所以

①ps -ef|grep mysql  
②kill -9 进程号（不建议）  
长事务的处理：

①select \* from INNODB\_TRX \\G \#查看事务线程的id；  
②kill 线程id  
\---------------------  
作者：请记得我的好  
来源：CSDN  
原文：https://blog.csdn.net/qq\_18312025/article/details/78658171  
版权声明：本文为博主原创文章，转载请附上博文链接！

转载于:https://www.cnblogs.com/xibuhaohao/p/11065305.html