MySQL--表
-
索引组织表
-
定义:在innodb存储引擎中,表都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表。
-
在innodb存储引擎表中,每张表都有一个主键
-
如果在创建表的过程中,没有显示的定义主键,则innodb存储引擎会按照如下方式选择或创建主键:
-
首先判断表中是否有非空的唯一索引(unique not null),如果有,则该列即为主键;
-
如果不符合上述条件,innodb存储引擎会自动创建一个6字节大小的指针。
-
当表中有多个非空唯一索引时,innodb存储引擎将选择建表时定义的第一个定义的非空唯一索引为主键,注意:主键的选择根据的是定义索引的顺序,而不是建表时列的顺序;
-
innodb逻辑存储结构
-
innodb存储引擎的逻辑存储结构可以看出,所有数据都被逻辑地存放在一个空间中,称之为表空间;
-
表空间由段(segment)、区(extend)、页(page)组成
-
表空间
-
当事物发生时,如果数据未提交,共享表空间的大小会增大,但是当事务回滚时,共享表空间并不会缩小,innodb不会在rollback时区收缩表空间,innodb会判断这些undo信息是否还需要,如果不需要,则会将这些空间标记为可用空间,供下次undo使用;
-
段:
-
常见的段有数据段、索引段、回滚段等
-
数据段即为B+Tree的叶子节点,索引段即为B+Tree的非索引节点
-
段的管理是有引擎自动完成的
-
区:
-
区是由连续的页组成的空间,在任何情况下每个区的大小都为1MB,为了保证区中页的连续性,innodb一次从磁盘中申请4~5区,在默认情况下,innodb存储引擎页的大小为16k,即一个区中一共有64个连续的页;
-
页:
-
页是innodb磁盘管理的最小单位
-
常见页的类型有:
-
数据页(B-Tree node)
-
undo页(undo log page)
-
系统页(system page)
-
事物数据页(transaction system page)
-
插入缓冲位图页(insert buffer page)
-
插入缓冲空闲列表页(insert buffer free list)
-
未压缩的二进制大对象页(uncompressed blob page)
-
压缩的二进制大对象页(compressed blob page)
-
行:
-
innodb存储引擎是面向列的,也就是说数据是按照行进行存放的
-
每个页存放的行记录也是有硬性定义的,最多允许存放16k-200行记录,即7992行记录
-
innodb行记录格式
-
compact
-
存储方式:
-
变长字段长度列表:null标志位:记录头信息:列1数据:列2数据:.......
-
compact行记录格式的首部是一个非null变长字段长度列表,按照列的顺序逆序放置其长度为
-
若列的长度小于255字节,用1字节表示;
-
若大于255字节,用2字节表示,最大不能超过2字节
-
null标志为:
-
该标志为指明了该行数据是否有null值,有则用1表示,该部分所占用的字节是1字节;
-
记录头信息:固定占用5字节(40位)。每列含义如下:
-
() 1 未知
-
() 1 未知
-
deleted_flag 1 该行是否已经删除
-
min_rec_flag 1 为1,如果该记录是预先定义为最小的记录
-
n_owned 4 该记录拥有的记录数
-
heap_no 13 索引岁中该条记录的排序记录
-
record_type 3 记录类型,000表示普通,001表示B+Tree树节点指针,010表示infimum,011表示 supermum,1xx标出保留
-
next_record 16 页中下一条记录的相对位置
-
total 40
-
不管是char类型,还是varchar类型,在compact格式下null值都不占用任何存储空间
-
一个页中存放的数据越多,其性能就越高
-
redundant
-
存储方式:
-
字段长度偏移列表:记录头信息:列1数据:列2数据:列3数据:.........
-
行记录首部是一个字段长度偏移列表,同样是按照列的顺序逆序存放的
-
记录头信息
-
-
行溢出数据
-
innodb存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。
-
即使是varchar列数据类型,依然有可能被存放为行溢出数据。
-
compressed和dynamic行记录格式
-
以上的文件格式(compact和redundant)称为antelope文件格式,新的文件格式称为barracuda文件格式,barracuda文件格式拥有两种新的行记录格式,compressed和dynamic;
-
新的行记录格式对于存放在blob中的数据采用了完全的行溢出的方式,在数据页中只存放20个字节的指针,实际的数据都存放在off page 中。
-
compressed的另一个功能就是,存储在其中的行数据会以zlib算法进行压缩。
-
char的行结构存储
-
在多字节字符集的情况下,char和varchar的实际行存储基本是没有区别的。
-
innodb数据页结构
-
innodb数据页由以下7部分组成:
-
-
file header(文件头)-------------38字节
-
-
page header(页头)-------------56字节
-
-
infinimum和supermum records
-
在innodb存储引擎中,每个数据页中有两个虚拟的行记录,用来限定记录的边界;
-
infimum记录是比该页中任何主键值都要小的值,supermum指比任何可能大的值还要大的值;
-
这两个值在页创建的时候被建立,并且在任何情况下都不会被删除;
-
在compact和redundant行格式下,两者占用的字节数各不相同
-
user records(用户记录,即行记录)
-
free space
-
page directory(页目录)
-
page directory中存放了记录的相对位置(注意,这里存放的是相对位置,不是偏移量),有些时候这些记录的指针称为slots(槽)或目录槽(dirctory slots);
-
innodb存储引擎的槽是一个稀疏目录,即一个槽中可能包含多个记录;
-
B+Tree索引本身并不能找到具体的一条记录,能够找到的只是该记录所在的页,数据库把页载入到内存中,然后通过page directory在进行二叉查找,只不过二叉查找的时间复杂度很低,同时在内存中的查找很快,因此通常忽略这部分查找所用的时间。
-
file trailer(文件结尾信息)--------8字节
-
为了检测页是否已经完整的写入磁盘,innodb存储引擎页中设置了file trailer部分;
-
file trailer 只有一个fil_page_space_end_lsn部分,占用8字节,前4字节代表该页的checksum值,最后4字节和fiile header中的fil_page_lsn相同。将这两个值与file header中的fil_page_space_or_chksum和fil_page_lsn值进行比较,看是否一致,以此来保证页的完整性。
-
约束
-
数据完整性
-
一般来说,数据完整性有以下三种形式:
-
实体完整性保证表中有一个主键。在innodb存储引擎表中,用户可以通过定义primary key或unique key约束来保证实体的完整性。用户还可以编写触发器来保证数据完整性;
-
域完整性保证数据每列的值满足特定的条件,在innodb存储引擎中,域完整性可以通过以下几种途径保证;
-
选择合适的数据类型确保一个数据值满足特定条件;
-
外键(foreign key)约束;
-
编写触发器;
-
还可以考虑使用default约束作为强制域完整性的一个方面
-
参照完整性保证两个表之间的关系,innodb存储引擎支持外键,因此允许用户定义外键以强制参照完整性,也可以通过触发器强制执行
-
约束类型
-
primary key
-
foreign key
-
unique key
-
default key
-
not null
-
约束的创建和查找
-
方式:
-
表建立时就立即约束定义;
-
利用alter table命令来进行创建约束
-
约束和索引的区别
-
用户创建了一个唯一索引就创建了一个唯一的约束,但是约束和索引的概念还是有所不同的,约束更是一个逻辑概念,用来保证数据的完整性,而索引是一个数据结构,基友逻辑上的约束,在数据库中还代表着物理存储的方式
-
enum和set约束
-
触发器与约束
-
外键约束
-
外键用来保证参照完整性;
-
一般来说,称被引用的表为父表,引用的表称为子表;
-
外键定义时的on delete和on update表示在对父表进行delete和update操作时,对子表所做的操作,可定义的子表操作有:
-
cascade
-
set null
-
no action
-
restrict
-
视图
-
在mysql中,视图(view)是一个命名的虚表,它由yigesql查询来定义,可以当作表使用。
-
与持久表相比视图中的数据没有实际的物理存储
-
视图的作用
-
物化视图
-
分区表
-
分区概述:
-
mysql支持的分区类型为水平分区,并不支持垂直分区
-
mysql数据库的分区是局部分区索引,一个分区中既存放了数据又存放了索引;
-
全局分区是指:数据存放在各个分区中,但是所有数据的索引放在一个对象中,mysql5.6不支持全局分区
-
分区类型:
-
range分区:行数据基于属于一个给定连续区间的列值被放入分区
-
list分区:和range分区类似,list分区面向的离散的值
-
hash分区:根据用户自定义的表达式的返回值来进行分区
-
key分区:根据mysql数据库提供的hash函数来进行分区
-
columns分区
-
注意:不论创建何种类型的分区,如果表中存在主键或唯一索引,分区列必须是唯一索引的一个组成部分;
-
唯一索引可以是允许null值的,并且分区列只要是唯一索引的一个组成部分,不需要整个唯一索引都是分区列;
-
如果建表时没有指定主键,唯一索引,可以指定任何一个列为分区列
-
子分区
-
子分区是在分区的基础上再进行分区,有时也称这种分区为复合分区
-
mysql允许在range分区和list分区的基础上再进行hash或key分区
-
注意:
-
每个子分区的数量必须相同
-
要在一个分区表的任何分区上使用subpartitioin来明确定义任何子分区,就必须定义所有的子分区
-
每个subpartition子句必须包括子分区的一个名字
-
子分区的名字必须是唯一的
-
子分区可以用于特别大的表,在多个磁盘间分别分配数据和索引。由于innodb使用表空间自动地进行数据和索引的管理,因此会忽略data directory和index directory 语法。
-
分区中的null值
-
mysql数据库允许对null值做分区,mysql数据库的分区总是视null值小于任何一个非null值,因此对于不同的分区类型,mysql数据库对于null值 的处理方式也是不一样的
-
range分区:mysql会将null值放入最左边的分区,删除分区时也会一并将null值记录删除;
-
list分区:在list分区下使用null值,必须显式地指出哪一个分区中放入null值,否则报错;
-
hash分区和key分区:任何分区函数都会将含有null值的记录返回为0;
-
在表和分区间交换数据
-
alter table ... exchange partition语法
-
允许分区或子分区中的数据与另一个非分区的表中的数据进行交换;
-
如果非分区表中的数据为空,相当于将分区的数据移动到非分区表中
-
若分区表的数据为空,相当于将外部表的数据导入到分区表中
-
使用上述语句必须满足如下条件:
-
要交换的表和分区表有着相同的表结构,但是表不能含有分区
-
在非分区表中的数据必须在交换的分区定义内
-
被交换的表中不能含有外键,或者其他的表含有对该表的外键引用
-
用户除了需要alter、insert、create权限外,还需要drop权限
-
注意;
-
使用该语句时,不会触发交换表和被交换表上的触发器
-
auto_increment列将被重置
-
分区并不总是适用于OLTP应用
Nothing is true,Nothing is fake.