InnoDB存储引擎4-表

发表于   |   分类于   |  

索引组织表

InnoDB中，表都是根据主键顺序组织存放的，这种存放方式的表称为索引组织表。
在InnoDB存储引擎表中，每张表都有个主键，如果没有InnoDB回按如下的方式选择或创建主键：

  • 首先判断表中是否有非空的唯一索引，如果有，则该列即为主键。主键选择更具定义索引的顺序，而不是建表时列的顺序。
  • 如果不符合上述条件，InnoSB存储引擎自动创建一个6字节大小的指针。

InnoDB逻辑存储结构

表空间由段（segment）、区（extent）、页（page）。

表空间

段

常见的段有数据段、索引段、回滚段

区

区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4~5个区。在默认情况下，InnoDB存储引擎页的大小为16KB，即一个区中一共有64个连续的页。创建表的默认大小是96KB。其实这是因为在这个段开始时，先用32个页大小的碎片页来存放数据，在使用完这些页之后，才是64个连续页的申请。这样做的目的是，对于一些小表，或者是undo这类的段，可以在开始时申请较少的空间，节省磁盘容量的开销。

页

在InnoDB存储中，默认每个页的大小为16KB。而从1.2开始，可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。设置完成，则所有表中页的大小都为innodb_pae_siez，不可以对其再次进行修改。除非通过mysqldump导入和导出操作来产生新的库。
InnoDB常见页类型：

  • 数据页
  • undo页
  • 系统页
  • 事务数据页
  • 插入缓冲位图页
  • 插入缓冲空闲列表页
  • 未压缩的二进制大对象页
  • 压缩二进制大对象页

    行

    每个页最多存放16KB/2-200行的记录，即7992行记录

    InnoDB行记录格式

    可以通过 show table status like ‘table_name’ 查看表行存储格式的信息。

    Compact行记录格式 P105

    Mysql5.0中引入。一个页中存放行数据越多，其性能就越高。 编程字段长度列表：长度小于255用一个字节，最大65535。
    Null标志位用二进制表示为Null的列
    记录头 没行数据除了用户定义的列外，还有两个隐藏列，事务ID列和回滚指针列，分别为6字节和7字节的大小。若InnoDB表没有定义主键，每行还会增加一个6字节的rowid列。

    Redumdant行记录格式

    Mysql5.0版本记录存储方式

    行溢出数据

    BlOB、TEXT可以不讲数据放在溢出页面，而且即便是Varchar列数据类型，依然有可能被存放为行溢出数据。
    Varchar类型的最大长度65532.Varchar（N）中的N指的是字符的长度。而文档中说明Varchar类型最大支持65535，单位是字节。Mysql官方手册中定义的65535长度是指所有Varchar列的长度总和，如果列的长度总和超出这个长度，依然无法创建。
    InnoDB存储引擎表是索引组织的，即B+Tree的结构，这样每个页中至少应该有两条行记录。Varchar不溢出的最大长度是8098字节。
    数据保存再BLOB页中，数据页只保存数据的钱768字节。

    Compressed和Dynamic行记录格式

    以前支持的compact和redundant格式称为antelope文件格式，新的文件格式称为Barracuda文件格式。Barracuda文件格式下拥有两种新的行记录格式：Compressed和Dynamic。
    新的两种记录格式对于存放在BLOB中的数据采用了完全溢出的方式

    Char的行结构存储

    Mysql4.1版本开始，Char（N）中N指的是字符的长度，而不是之前版本的字节长度。也就说在不同的字符集下，char类型列内部存储的可能不是定长的数据。因此，对于多字节字符编码的char数据类型的存储，InnoDB在内部将其视为变长字符类型。

    InnoDB数据页结构

    InnoDB数据页由一下7部分组成
  • File Header 文件头
  • Page Header 页头
  • Infimum和Supremum Records
  • User Records 用户记录
  • Free Space 空闲空间
  • Page Direcory 页目录
  • File Trailer 文件结尾信息

File Header

Page Header

Infimum和Suprenum Record

在InnoDB存储引擎中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。Innfimum记录是比该页中任何主键都要小的值，Supremum指比任何可能大的值还要大的值。这两个值都在页创建时被建立，并且在任何情况下不会被删除。

User Record 和 Free Space

User Record实际存储行记录的内容。InnoDB存储引擎表总是B+树索引组织的
Free Space很明显指的就是空闲空间，同样也是个链表数据结构。在一条记录被删除后，该空间会被加入到空闲链表中。

Page Directory

指向页内行记录的系数索引，倒序保存。同一个槽位下面的行记录用链表串联

File Trailer

为了检测页已经完整写入磁盘。
前四个字节代表该页的checksum值，最后4个字节和FileHeader中的FIL_PAGE_LSN值进行比较。
InnoDB每次从磁盘读取一个页就会检测该页的完整性，即页是否发生Corrupt。

Named File Formats机制

从InnoDB1.0版本开始，通过Named File Formats机制来解决不同版本下页结构兼容的问题

#约束

数据完整性

InnoDB存储引擎表中，域完整性可以通过以下几种途径来保证：

  • 选择合适的数据类型确保一个数据值满足特定条件
  • 外键约束
  • 编写触发器
  • 还可以开率用Default约束作为强制域完整性的一个方面。默认值。

    约束创建和查找

    1
    2
    3
    4
    5
    6
    7
    8
    9
    create table p (
    id int , uid int,
    primary key (id),
    foreign key (uid) references p (id);
    )

    select constrint_name,constrint_type from
    information_schema.table,constraints 
    where table_schema = '数据库名称' and table_name = '表名称'；

对错误数据的约束

1
2
set sql_mode = 'strict_trans_tables'
#不满足约束报错，而不是警告

Enum和set约束

表上有一个性别类型，规定域的范围只能是male或者female，在这种情况下用户可以通过enum进行约束。

触发器和约束

1
2
3
4
5
6
7
8
9
10
11
12
create trigger 触发器名称 before|after |insert|update|delete
on 表名 for each row 触发器语句

create trigger tgr_usercash_update before update on usercash
for each row 
begin 
if new.cash -old.cash >0 then
    insert into usercash_error_log
    select old.userid,old.cash,new.cash;
    set new.cash = old.cash;
end if;
end;

外键约束

一般来说，成被引用表为父表，引用的表称为子表。外键定义时的 on delete 和 on update 表示在对父表进行delete和update操作时，对子表所做的操作。

  • cascade表示 通过操作
  • set null 父表改变，子表更新为null
  • not action 抛出无措，不允许这类操作发生
  • restrict 与no action 相同。默认值

    视图

    视图作用

    视图主要用途之一被用作一个抽闲该装置，特别是对于一些应用程序，程序本身不需要关心基表的结构，只需要按照视图定义来取数据或更新数据，因此，视图同事在一定程度上起到一个安全层的作用。
    虽然视图是基于基表的一个虚拟表，但是用户可以对某些视图进行更新操作，其本质就是通过视图的定义来更新基本表、。
    1
    create view v_t as select * form t where id < 10 with check option;

with check option 检查SQL，防止不满足视图条件的数据进入。

物化视图

Oracle对视图物化提供良好的支持。Mysql数据库本身并不支持物化视图，换句话说，Mysql数据库中的视图总是虚拟的。用户可以定时把数据导入到另一张表。触发器实现。

分区表

分区概述

分区功能并不是在存储u引擎曾完成的，因此不是只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持。CSV、Merge等不支持
Mysql数据库在5.1版本添加对分区的支持，分区的过程是将一个表或者索引分解为多个更小、更可管理的部分。Mysql数据库支持水平分区，不支持垂直分区。此外，mysql数据库的分局是局部分区索引，一个分区中既存放数据又存放索引。目前不支持全局分区。
在OLTP应用中，对于分区的使用应该非常小心。

分区类型

range分区

1
2
3
4
5
create table t( id int) engine=innodb
partition by range(id)(
partition p0 values less than(10),
partition p1 values less than(20)
);

maxvalue 代表正无穷
然哥分区主要用于日期列的分区

1
2
3
4
5
create table sales( mony int not null ,date datetime) engine=innodb
partition by range(to_days(date))(
partition p201001values less than(to_days('2010-02-01')),
partition p201002values less than(to_days('2010-02-02'))
);

explain partition 查看语句分区使用情况。
range分区查询，优化器只能对year，to_days,.to_seconds,unix_timestamp这类函数进行优化选择。

List分区

list分区与range相似，只是分区列的值是离散的

1
2
3
4
5
create table t( id int) engine=innodb
partition by list(id)(
partition p0 values  in (1,3,5,7,8),
partition p1 values in (2,4,6,8)
);

在用insert插入多个行数据的过程中遇到分区未定义的值时，MyISAM和InnoDB存储引擎的处理完全不同。myIsam将之前的行数据都插入，但之后的数据不会被插入。innodb将其视为一个事务，因此没有任何插入

###　hash分区
hash分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致是一样的。Mysql自动完成这些工作，用户所要做的只是基于将要进行哈希分区的列值顶一个列值或表达式，一集指定被分区的表将会被分割成分区数量.

1
2
3
create table t_linear_hash(a int)engine=innodb
partitiion by linear hash(a)
partitions 4;

mysql数据库还支持一种称为linearhash的分区，它使用一个更加复杂的算法来确定新行插入到已经分区的表中的位置。P165

key分区 p166

key分区和hash分区相似，不同之处在于hsah分区使用用户定义的函数进行分区，key分区使用mysql数据库提供的函数进行分区。

columns 分区

前面四中分区中，分区的条件是：数据必须是整形。columns不需要

子分区

Mysql数据库允许在range和list分区上再进行hash或key子分区。
每个子分区的数量必须相同
要在一个分区表的任何分区上使用subpartition来明确定义任何字分区，就必须定义所有的子分区
每个subpartition自居必须包括子分区的一个名字
子分区的名字必须是唯一的

分区中null值

mysql 数据库的分区总是视null值小于任何一个非null值，这和order by操作一样。Hash和key分区对于null的处理是将含有null值得记录返回0。

分区和性能 P176

对于OLTP应用，分区应该非常小心，在这种应用下，通常不可能会获取一张 大表10%的数据，大部分都是铜鼓欧索引返回几条记录即可。根据B+树索引的原理克制，对于一张达标，一般的B+树需要2~3次的磁盘IO。一次B+树可以很好的完成操作，不需要分区的帮助。
1000w行的表B+树只有两层。设计不好的索引得不偿失。10个分区，如果设计不好就要进行20次IO。

表和分区间 交换数据