每张表都有主键,如果没有定义,则:
1.将Unique NOT NULL设为主键
2.自动创建
逻辑存储结构 表空间:
由段,区,页(块)组成
可以设置所有数据存放在一个共享表空间/每张表的数据单独放到一个表空间
每张表的表空间存放数据、索引、插入缓冲
undo信息、系统事务信息、double write buffer依然存放在共享表空间
rollback事务时,不会回收表空间,而是将这些空间标记为可用,供下次Undo使用
段:数据段(B+树叶节点)、索引段(B+树非索引节点)、回滚段
区:64个连续的页
行:面向行的存储,数据存放按行进行
行记录格式
1.Compact行记录格式
高效存放数据
非NULL变长字节长度列表:按照列顺序逆序放置
NULL标志位:该行数据是否有NULL值
记录头信息:
列数据:NULL不占空间
隐藏列:事务ID列和回滚指针列
如果没有主键,则增加RowID列
链表结构
2.Redundant行记录格式
向前兼容
字段长度偏移列表:逆序放置
记录头信息:
行溢出数据
每个页至少应该有两个行记录
对于大对象会将数据存放在数据页面之外
当发生行溢出时,存放溢出的页类型为Uncompress BLOB Page
1.数据页保存前768个字节
2.数据页不保存前缀
溢出行的数据会进行压缩
Char的行结构存储:多字节字符集情况下,CHAR被视为VARCHAR
数据页结构
B-tree node
数据页组成:
File Header:页的头信息
Page Header:页的状态信息
Infimum和Supremum记录:虚拟行记录,限定记录边界
User Records 与 FreeSpace:实际存储行记录的内容与空闲列表
Page Directory:记录的相对位置
B+数索引查找该记录所在页,将页载入内存,再通过Page directory进行二叉查找
File Trailer:保证页能够完整写入磁盘
约束 数据完整性:
实体完整性:保证有主键
域完整性:保证数据的值满足特定要求
参照完整性:外键
InnoDB实现约束方式:Primary Key
Unique Key
Foreign Key
default
NOT NULL
触发器
ENUM约束:规定值域默认对于非阈值插入警告
需要设置sql_mode报错
外键:当父表发生变化时,子表的操作:
1.CASCADE:子表也进行DELETE/UPDATE
2.SET NULL:子表中的数据被更新为NULL
3.NO ACTION:抛错
4.RESTRICT:抛错;默认
Oracle为延时检查
MySQL为即时检查
在外键建立时自动对该列加一个索引,避免无索引导致死锁的问题
分区表
将一个表或者索引物分解为多个部分
MySQL支持水平分区
一个分区既存放数据又存放索引
分区条件必须是整型,如果不是整型,需要用YEAR()等函数转换为整型
支持的分区类型:1.RANGE分区,基于给定连续区间的列值放入分区
2.LIST分区,面向离散的值
3.HASH分区:根据用户自定义的表达式的返回值进行分区
4.KEY分区:根据MySQL提供的善良的航空函数进行分区
分区列必须是唯一索引的一个组成部分
当没有指定主键、唯一索引时,可以指定任何一个列作为分区列
RANGE分区:表由各个分区文件组成
常见错误:
对于RANGE分区查询,优化器只能对YEAR(), TO_DAYS(), TO_SECONDS(), UNIX_TIMESTAMP()这类函数进行优化
LIST分区:当插入多行数据过程遇到分区未定义的值:
MyISAM:将之前的行数据都插入,之后的数据不会被插入
InnoDB将其视为一个事务,没有任何数据插入
HASH分区:指定将被善良的航空的列值或表达式以及分区数量
LINEAR HASH:快捷,各个分区间数据分布可能不大均匀
KEY分区: COLUMNS分区:支持整形类型,日期类型,字符串类型
RANGE,LIST分区的进化
可以对多个列的值进行分区
子分区:在分区基础上再进行分区
允许再RANGE和LIST分区上再进行HASH/KEY子分区
每个子分区数量相同
如果一个分区表上的任何分区使用子分区,则必须定于所有子分区
NULL值:MySQL将NULL视为小于任何一个非NULL值
Oracle会将NULL放入MAXVALUE分区
LIST分区必须显示指明NULL
HASH和KEY对于NULL的分区函数将返回0
分区性能:对于OLAP应用,分区可以很好地提高查询性能
对于OLTP应用,分区并不一定适合