mysql数据块_mysql数据表段区块存储关系
索引组织表(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’错误。
varchar2最大长度
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 )

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。