InnoDB存储引擎--表

1.索引组织表

innodb表是索引组织表，数据即索引，索引即数据。

innodb表都有主键（primary key），如果没显示定义，会以第一个定义的非空唯一索引为主键，如果也没有，自动创建一个6字节大小的指针。

2.InnoDB逻辑存储结构

2.1 表空间

image

文件：共享表空间默认文件为ibdata1；独立表空间文件为表名.ibd。innodb_file_per_table开启独立表空间。
存放内容：使用了独立表空间时，共享表空间存放undo回滚信息、插入缓存索引页、系统事务信息、二次写缓冲等；独立表空间存放该表的数据、索引和插入缓冲bitmap等

2.2 段

段由引擎层管理，一般有三种段：

数据段。B+树的叶子节点
索引段。B+树的非叶子节点
回滚段。

2.3 区

每个区固定1M，由连续的页组成。（16x64 8x128 4x256 2x512）
每个页16K。参数KEY_BLOCK_SIZE可以设置页的大小，压缩页有2K，4K，8K的。
使用独立表空间时，创建的表大小并没有64个连续的页那么大（1M），这是因为在每个段开始时，先用32个页的碎片页，用完才申请64个完整的。

2.4 页

InnoDB磁盘管理的最小单位。也称块。InnoDB页类型：

数据页（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）

2.5 行

InnoDB是面向列的，按行进行存储。一行最多有1023列。
每个页最多存放16K*1024/2 - 200 = 7992行（每行最少2B）。每行最少存储2行，因为InnoDB索引组织表是B+树结构，小于2行就成链表了。

3.InnoDB行记录格式

3.1 Compact行记录格式

设计目标：高效地存储数据。一个页中行数据越多，性能越高。

image

隐藏列：事务回滚ID（6字节）和回滚指针列（7字节）。无定主键时有rowid（6字节）。

页内部是用链表结构串连各个行（next_record）

3.2 Redundant行记录格式

Redundant是为了兼容旧版本保留的。

3.3 行溢出数据

VARCHAR、BLOB都可能把数据存储在数据页之外。
Oracle的VARCHAR2最大4000字节，SQL Server最大8000字节，MySQL的VARCHAR最大存放65535字节，但实际并不能创建65535长的列（实测是65532）。
如果使用多字节编码（GBK,UTF-8），VARCHAR(N)的N是指字符，VARCHAR(65532)也无法达到。
65535字节是指所有VARCHAR列长度的总和限制。
一个页为16K=16384字节，当一个页不够存储2行数据时，存到未压缩的二进制大对象页（Uncompressed BLOB Page）。测试发现VARCHAR长度阈值为8098：
CREATE TABLE t(
a varchar(8098) //超过8098，一个页不够存两条，数据会存到BLOB页
)

3.4 Compressed和Dynamic行记录格式

image

3.5 CHAR的行存储结构

CHAR(N)，N是指字符长度，不同字符集下可能实际存储字节长度不一样。

CHAR_LENGTH(列名)函数：字符长度
LENGTH(列名)函数：字节长度

可以认为多字节字符集下，CHAR和VARCHAR的存储基本没有区别。

4.InnoDB数据页结构

暂跳过该节

4.1 File Header

4.2 Page Header

4.3 Infimum和Supermum Records

4.4 User Records 和 Free Space

4.5 Page Directory

4.6 File Trailer

4.7 示例

5.Name File Formats机制

解决不同版本下页结构的兼容性问题。

innodb_file_format //文件格式参数

6.约束（constraint）

6.1 数据完整性

关系型数据库自身保证数据的完整性，而文件系统需要应用程序控制。

实体完整性：保证表中有一个主键。InnoDB：Primary Key，Unique Index，触发器
域完整性：保证每列的值满足特定条件。InnoDB：数据类型，外键，触发器，DEFAULT
参照完整性：保证两张表间的关系。InnoDB：Primary Key，Unique Index，外键，DEFAULT，NOT NULL

6.2 约束的创建和查找

创建：建表时定义约束或ALTER TABLE创建约束

CREATE TABLE T(
    ID INT,
    NAME CHAR(10),
    PRIMARY KEY(ID),
    UNIQUE INDEX(NAME)
)

查看：

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       CONSTRAINT_NAME,
       CONSTRAINT_TYPE
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

6.3 约束和索引的区别

约束更是一个逻辑概念，用来保证数据完整性。索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储方式。

6.4 对错误数据的约束

默认允许错误数据的插入。设置SQL_MODE=STRICT_TRANS_TABLES，对输入值进行约束。

6.5 ENUM和SET约束

6.6 触发器与约束

触发器：执行INSERT，DELETE，UPDATE前或后调用SQL或存储过程。一个表最多6个触发器，增删改的前和后。

//SUPER权限用户可以执行
CREATE 
[DEFINER = {user | CURRENT_USER}]
TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE
ON tbl_name FOR EACH ROW trigger_stmt 
//MYSQL支持按每行触发，不像DB2的FOR EACH STATEMENT

6.7 外键约束

7.视图

视图（view）是虚表，由一个查询SQL定义，可以当表使用，无物理存储。

7.1 视图的作用

抽象装置，无需关心基表结构进行查改。起到一个安全层的作用。

SELECT *FROM INFORMATION_SCHEMA.VIEWS

7.2 物化视图

Oracle支持物化视图：存储查询结果到磁盘？需要时直接取。

Oracle物化视图创建方式：

BUILD IMMEDIATE。创建物化视图时就生成数据
BUILD DEFERRED。根据需要再生成数据
物化视图的刷新：基表发生DML时，物化视图如何进行同步。

刷新模式：ON DEMAND（需要时），ON COMMIT（提交时）

刷新方法：FAST（增量）
COMPLETE（完全）
FORCE（增量，不行就完全）
NEVER（不刷新）

MySQL不支持物化视图，总是虚拟的。只能通过一些其他机制实现，如触发器。

8.分区表

8.1 分区概述

分区功能不是在存储引擎层实现，是在数据库层实现，但有的存储引擎不支持分区。
MySQL支持水平分区（不同行在不同物理文件），不支持垂直分区（不同列在不同物理文件）。一张表对应三个文件：frm（表结构） myd（表数据）myi（表索引）
MSQL分区是局部分区索引（分区中存放数据和索引），而全局分区的所有数据索引是存在一个对象中。

分区的好处

存储更大数据。方便按分区的删除。能过滤分区的查询得到优化。SUM(),COUNT()等可以并行处理，最后汇总。提示吞吐量。

分区表的限制

一个表最多只能有1024个分区
分区表中无法使用外键约束。
如果表有主键、唯一索引，分区列必须是索引的一部分。如果没主键、唯一索引，可以指定任意列为分区列。

8.2 RANGE分区

RANGE分区主要用于日期列的分区。
范围必须连续但是不重叠。
如果没有 LESS THAN MAXVALUE的分区，插入范围外的列值会报错。
5.5版本COLUMNS关键字，可支持非整数和多列

根据数值范围

partition by range(id)(
    //partition 分区名称 values less than ()
    partition p0 values less than (1000), 
    partition p1 values less than maxvalue
);

根据TIMESTAMP范围

partition by range(unix_timestamp(timestamp_column))(
  partition p0 values less than (unix_timestamp('2008-01-01 00:00:00')),
  partition p1 values less than maxvalue
);

根据DATE、DATETIME范围

partition by range columns(date_column)(
  partition p0 values less than ('1970-01-01'),
  partition p1 values less than maxvalue
);

根据多列范围

partition by range columns(a,b)(
  partition p0 values less than (0,10),
  partition p1 values less than (maxvalue, maxvlaue)
);

8.3 LIST分区

插入列表外的列值会报错
5.5版本COLUMNS关键字，可支持非整数和多列

8.4 HASH分区

只能整数或返回整数的表达式。根据整数对分区数取模。

partition by hash(id)
partitions 4; //没指定时默认1

表达式函数越接近线性越有利于均匀分布。y=nx，n为非零常数
Hash分表扩展性差。MYSQL提供了线性Hash分区，使用线性的2的幂运算法则，而不是取模。类似一致性hash。

partition by linear hash(id)
partitions 6;

线性Hash

比如分区数量6，取比6大的2的幂V=8=2^3。

决定该行存到哪个分区N：

//即表达式值与111做与运算（取幂-1就是为了获取最接近的111+）
//保留f(column)的后三位
//这样扩展删除时，不用重新映射不相关分区的？
N = f(column) & (V-1) // V-1=7=111
//如果算出来目标分区大于等于分区数量6，再进行计算：
V=CEILING(V/2) //降幂 向上取舍 再与运算 相当于去掉2进制最高位
N = N & (V-1)

线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000G）数据的表。它的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。

8.5 KEY分区

与HASH分区类似，也有线性key分区方法。

MySQL 簇（Cluster）使用函数MD5()来实现KEY分区

//表存在主键或唯一索引时可省略Key括号内的列名，Mysql将按照主键－唯一索引的顺序选择
//当找不到唯一索引时报错
partition by linear key(id) 
partitions 4;

8.6 子分区

每个分区必须有相同数量的子分区

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH(TO_DAYS(purchased))
    SUBPARTITIONS 2
    (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    )

8.7 对NULL值的处理

NULL插入RANGE和LIST时，会被视为0

8.8 分区性能

数据库应用分为两类:一类是OLTP(在线事务处理)，如博客、电子商务、网游等；另一类是OLAP(在线分析处理)，如数据仓库、数据集市。
对于OLAP的应用，分区的确可以很好的提高查询性能，因为OLAP应用的大多数查询需要频繁的扫描一张很大的表，如果进行分区则只需要扫描相应的部分即可。
而对于OLTP应用一般情况下不可能获取一张大表中10%的数据，大部分都是通过索引返回若干记录。对于一张大表，一般的B+树需要2~3次磁盘IO。举个列子，一张数据量为1000万行的表B+树高度为3，将他分为10个区,100万行的B+树高度是2。单独对于Key索引的查询在基于分区设计的情况下查询开销为2次IO，而原表设计需要2到3次IO。而如果对于其他列索引的查询就可能需要10 * 2 = 20次IO。分区查询耗时将远远大于没有进行分区设计数据表的查询耗时。

8.9 在表和分区间交换数据

ALTER TABLE ... EXCHANGE PARTITION

使用条件和注意事项：

表结构相同，非分区表不能有分区（...废话？）
非分区表中数据必须在分区定义内（如列表分区只有A,B,C，是不能插入D）
不能还有外键，或其他表对该表有外键
需要ALTER、INSERT、CREATE和DROP权限
不会触发两表上的触发器。
AUTO_INCREMENT列会被重置。