MySQL笔记系列之三表

表

表是关于特征实体的数据集合。

InnoDB存储引擎创建主键的方式

首先表中是否有非空的唯一索引，如果有，则该列即为主键，如果没有，则InnoDB存储引擎自动创建一个6字节大小的指针。

InnoDB存储引擎的逻辑结构

InnoDB将所有的数据被放在一个称为表空间的地方，表空间又由段，区，页（页在一些文档中也称为块）。他们关系如图：

⑴表空间

表空间是存储的最高层，所有的信息都存储在表空间，InnoDB存储引擎有一个共享表空间ibdata1,即所有数据都放在这里，但是如果启用innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内，但是，这个单独存放的表只保存数据，索引，插入缓存，其他的数据，如撤销信息，系统事务信息，二次写缓冲在原来的共享表空间内。

⑵段

表空间由各个段组成，常见的段有数据段，索引段，回滚段，InnoDB存储引擎表是索引组织的，即数据即索引，索引即数据，数据段即为B+树的叶节点，索引段即为B+树的非索引节点。注意：并不是每个对象都有段，即表空间是由分散的页和段组成。

⑶区

区由64个连续的页组成，每页大小16KB，每个区的大小为1MB,InnoDB存储引擎最多每次可以申请4个区，来保证数据的顺序性能。注意：启用innodb_file_per_table后，创建表的默认大小是96KB,但区是64个连续的页，则创建表的大小至少是1MB，解释：因为每个段开始时，先有32页大小的碎片页来存放数据，当这些页使用完成才是64连续页的申请。

⑷页

也称块，页是InnoDB磁盘管理的最小单位，InnoDB页的大小默认为16KB,且不可更改，Microsoft SQL Server数据库默认每页大小为8KB.页的类型有：数据页（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)

⑸行

InnoDB存储引擎是面向行的，即数据是按行进行存放，每页最多存放16KB/2-200行的记录，即7992行记录。

InnoDB物理存储结构

从物理角度出发，InnoDB由共享表空间（或innodb_file_per_table启动后的.idb文件），日志文件组，表结构文件（.frm）。

innoDB存储引擎记录是以行的形式存储的，即页中保存的是一行行的数据，MySQL5.1后，InnoDB存储引擎提供了Compact和Redundant两种格式来存放记录数据，Redundant是为了兼容之前的版本而保留的。MySQL5.1默认保存Compact行格式。

可通过 show table status like 'table_name'来查看表的状态，其中row_format代表当前使用的行记录结构。

Compact行记录格式：

变长字段长度列表：按照列的顺序逆序放置的，变长字段的长度大小不可以超过2个字节，当列的长度小于255，用1字节表示，若大于255个字节，用2字节表示。故MySQL中varchar的最大长度为65535，因为2个字节16位，即2的16次方为65535.

NULL标志：表明该行是否有NULL值，用1表示，

记录头信息：占用5个字节，

RowID：当InnoDB表没有定义Primary Key时，每行增加一个6字节的RowID，

事务ID，回滚指针：两个隐藏列，分别占据6个字节和7个字节。

数据列：即每个列的数据，注意NULL不占该部分任何数据，即NULL除了占有NULL标志位，实际存储不占任何空间。

Redundant行记录格式：

字段长度偏移列表：按照列的顺序的逆序放置的，字段的长度大小不可以超过2个字节，当列的长度小于255，用1字节表示，若大于255个字节，用2字节表示。

记录头：Redundant行格式固定占用6个字节，每位都有特定的意义，例如：n_fields值代表每一行中列的数量，占10位，故MySQL中一个行最多的列为1023.

MySQL的sql_mode模式：

show VARIABLES like 'sql_mode';
sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

sql_mode定义了MySQL应该支持的sql语法，数据效验，MySQL5.0以上支持三种sql_mode模式：ANSI,TRADITIONAL和STRICT_TRANS_TABLES.

ANSI:宽松模式，对插入数据进行效验，如果不符合定义类型和长度，则对数据类型进行调整或截断保存，并给出warning.

TRADITIONAL:严格模式，当向数据库插入数据时，进行数据的严格效验，保证错误数据不能插入，并给出error错误。

STRICT_TRANS_TABLES:严格模式，进行数据的严格校验，错误数据不能插入，报error错误。

可通过set @@sql_mode=ANSI; 语句来设置sql_mode变量的值。

varchar类型数据：

刚提到，varchar数据类型最大可以存放65535个字节，但是如果将varchar设置为65536，则会报错，在MySQL官方手册中定义的65536长度是指所有varchar列的长度总和，即，如果列的长度总和超出这个长度，依然无法创建。

问题：数据都存放在页中，页的大小是16KB，即16384个字节，如何存放varchar变量的65535左右个字节，一般情况下，数据存放在B-tree Node的页类型中，但当发生行溢出时，则这个存放行溢出的页类型为Uncompress BLOB Page。

行溢出数据：

InnoDB存储引擎将一条记录中的某些数据存储在真正的数据页面之外，即作为行溢出数据，一般BLOB,LOB这类大对象列类型的存储会把数据存放在数据页之外。

InnoDB Plugin引入新的文件格式，以前的Compact和Redundant格式将其称为Antelope文件格式，新的文件格式称为Barracuda.Barracuda文件格式拥有两种新的行记录格式Compressed和Dynamic两种。

Char的行结构存储：

从MySQL4.1开始，CHRN中的N指的是字符的长度，而不是字节长度，不同的字符集下，CHAR的内部存储的不是定长的数据。例如英语字符，1个字母字符，长度为1，而1个汉字字符，长度为2.

约束

约束提供了一种途径保证数据库的数据的完整性，数据完整性有三种形式：

⑴实体完整性：保证表中有一个主键，可通过定义primary key 或者Unique Key约束来保证实体的完整性

⑵参照完整性：保证两张表之间的关系，InnoDB存储引擎支持外键，外键约束保证一个或两个表之间的参照完整性。

⑶域完整性：保证数据值满足特定的条件，在InnoDB存储引擎中，域完整性有以下几种方式：选择合适的数据类型，外键约束，编写触发器，考虑DEFAULT约束。

对InnoDB存储引擎，提供几种约束：Primary Key； Unique Key ; Foreign Key ; Default ; NOT NULL;

创建约束

约束的建立，可在表建立时进行，也可以在使用ALTER TABLE命令来进行创建，Unique Key的约束，可通过Create Unique Index建立，主键约束，通过PRIMARY KEY，Unique Key约束，默认的约束名和列名一样，当然也可以指定一个名字，Foreign Key约束，通过指定其他表的主键。

eg.

CREATE TABLE uu ( id INT ,name VARCHAR(20),id_seq CHAR(20),PRIMARY KEY(id),UNIQUE KEY (name));

eg.

ALTER TABLE uu add UNIQUE KEY  un_id_card(id_seq);

可查看表的创建语句：

show create table uu;
uu	CREATE TABLE `uu` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `id_seq` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `un_id_card` (`id_seq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

约束与索引：

约束的创建和索引的创建方法有些相同，的确，当创建一个唯一索引，就创建了一个唯一的约束，但约束和索引的概念不同，约束更像是从逻辑层面保证数据的完整性，索引是一个数据结构，有逻辑上的概念，在数据库中更是一个物理存储方式。

对于错误数据的约束：

默认情况下，MySQL数据库允许非法或者不正确的数据的插入或更新，或内部转化为一个合法的值，之所以能够成功，因为sql_mode的设置为宽松模式，如果想严格按照规定的参数，可以将sql_mode设置为严格模式。

ENUM和SET约束：

ENUM和SET类型可以解决部分约束，即某个数据类型，它的取值范围只能是特定的几个值，例如： 性别，取值为男，女。我们可以通过ENUM类型来进行约束：

CREATE TABLE bb ( id INT ,sex ENUM('MALE','FEMALE'))

触发器与约束：

完整性约束通常也可以使用触发器来实现，触发器的作用是在INSERT,DELETE,UPDATE命令之前，或之后自动调用SQL语句或存储过程，触发器的创建命令是：

CREATE TRIGGER TRIGGER_NAME BEFORE|AFTER  INSERT|UPDATE|DELETE ON tab_name FOR EACH ROW TRIGGER_STAMENT

最多可以创建6个触发器，即分别是INSERT ,UPDATE ,DELETE的BEFORE和AFTER各定义一个，BEFORE,AFTER定义触发器发生的实践，MySQL只支持FOR EACH ROW的触发方式，即按每行记录进行触发。

外键：

外键用来保证参照完整性，MySQL默认的MyISAM存储引擎本身不支持外键，对于外键的定义只是一个注释的作用而已，InnoDB存储引擎则支持外键。我们可以再创建表的时候添加外键，也可以在ALTER TABLE命令时添加外键。

CREATE TABLE test1 (id INT NOT NULL,
PRIMARY KEY (id)
)ENGINE=INNODB;

CREATE TABLE test2 ( id INT , test2_id INT,
INDEX t2_id(test2_id),
PRIMARY KEY (id),
FOREIGN KEY (test2_id) REFERENCES test1(id)
)ENGINE = INNODB;

被引用的表称作父表，另一个引用称为子表，外键定义为ON DELETE和ON UPDATE表示父表做DELETE和UPDATE操作时子表所做的操作，有以下几种选项：

CASCADE:当父表发生DELETE和UPDATE操作时，相应的子表中的数据也被DELETE和UPDATE;

SET NULL:当父表发生DELETE和UPDATE操作时，相应的子表中的数据被更新为NULL;

NO ACTION:当父表发生DELETE和UPDATE操作时，抛出错误，不允许此操作发生。

RESTRICT:当父表发生DELETE和UPDATE操作时，抛出错误，不允许此操作发生。当定义外键的时候没有指定ON DELETE 或 ON UPDATE，这是默认的外键设置。在MySQL中，NO ACTION和RESTRICT功能相同。

注意：在创建外键的时候，不要忘记给这个列添加一个索引，InnoDB存储引擎在外键建立时，会自动地对该列加一个索引，可以很好的防止外键列上无索引导致死锁问题。

视图

视图是一个虚拟的表，由一个查询来定义，视图中的数据没有物理表现形式，创建语法是：

CREATE 
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MEGRE | TEMPTABLE}]
[SQL SECURITY {DEFINER | INVOKER}]
VIEW view_name 
AS SELECT_STATEMENT
[WITH [CASCADE}LOCAL|CHECK OPTION]

使用WITH CHECK OPTION可以对视图定义条件的插入数据进行检查，如果不满足条件，则抛出异常。

查看当前数据库下的基表，可以使用以下语句：

select * from information_schema.`TABLES` WHERE table_type='BASE TABLE' and table_schema=database();

也可以访问information_schema架构下的VIEWS视图，该视图有视图的详细信息。

分区表

MySQL数据库在5.1版本添加了对分区的支持，分区，即将一个表或索引物分解为多个更小，更利于管理的部分，分区的作用就是更快的查询。对于一个表，从物理存储上看，它有可能存储在数十个物理分区上，每个分区都是独立的对象，可独自处理，MySQL支持的水平分区，不支持垂直分区。分区的意义：将一个表跨文件系统分配单个表的多个部分，这里的区以什么来衡量?这里的分区通过一些规则来决定如何进行分区的划分，注意，不要和磁盘的扇区混淆。

MySQL数据库支持一下几种类型的分区：

RANGE分区：行数据基于属于一个给定连续区间的列值放入分区，

LIST分区：和RANGE分区类似，但LIST分区面向的是离散的值，

HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数，

KEY分区：根据MySQL数据库提供的哈希函数来进行分区。

eg.

CREATE TABLE tptest(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL
)engine=innodb
PARTITION BY HASH(col3)
PARTITIONS 4;

注意：无论何种分区，如果表中存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分，如果表中没有指定主键，则分区列可以为任意一个列。

⑴RANGE分区

最常用的一种分区类型，eg.创建一个id列的区间分区表，当id<10,数据存入p0分区，当10<id<90,数据存入p1分区，当90<id<100放入p2分区.eg:

CREATE TABLE TESTP2(
id INT NOT NULL)
ENGINE = INNODB
PARTITION BY RANGE (id)(
PARTITION p0 VALUES LESS THAN (10),
PARTITION P1 VALUES LESS THAN (90),
PARTITION P2 VALUES LESS THAN (MAXVALUE));

可通过下面语句查询每个分区的具体信息，当然也可以在information_schema架构下的PARTITIONS表来查看每个分区的具体信息。

SELECT * FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='testp2';

RANGE分区主要用于日期列的分区，分区对于删除操作来说，不需要执行DELETE FROM table_name WHERE XXX,而是只需要删除相应的分区即可完成对应于特定条件的分区。同时对于查询操作来说，SQL优化器不需要去搜索所有分区，只需要搜索特定的分区即可。

⑵LIST分区

分区列是离散值，不是连续值。eg.

CREATE TABLE testp3(
a INT,
b INT)ENGINE = INNODB
PARTITION BY LIST(b)(
PARTITION p0 VALUES IN (1,3,5,7,9),
PARTITION p1 VALUES IN (2,4,6,8,10)
);

如果插入的值不在分区定义中，MySQL数据库会抛出异常。

如果插入多个行数据时遇到分区未定义的值时，MyISAM引擎会将之前的数据的行都插入，但之后的数据不会被插入，InnoDB存储引擎将其视为一个事务，报错的同时，不会对任何一条记录插入到表中。

⑶HASH分区

将数据均匀的分散到预先定义的各个分区，不同之前的是，HASH分区中，不需要指定分区名和给定确定的列值或列值集合，MySQL自动完成这些工作，我们要做的是指定一个列值或表达式用来作为哈希函数的Key值，以及分区表需要被分割的数量。eg.

CREATE TABLE TESTP4(
a INT NOT NULL,
b DATETIME)ENGINE=INNODB
PARTITION BY HASH (YEAR(b))
PARTITIONS 4;

MySQL还支持一种为LINEAR HASH的分区，使用更加复杂的算法来确定新行插入到已经分区的表中的位置，与HASH分区的语法相似，只需改动关键字即可。

⑷KEY分区

HASH使用用户定义的函数进行分区，KEY使用MySQL数据库提供的函数进行分区。用法和HASH相同，只需修改关键字即可。

⑸COLUMNS分区

前面介绍的分区，分区条件是必须是整型，如果不是，则会将其通过函数转换为整型，MySQL5.5开始支持COLUMNS分区，COLUMNS可以直接使用非整型的数据进行分区。同时，RANGE COLUMNS分区可以对多个列的值进行分区，

子分区

在分区的基础上在进行分区，又称复合分区，eg.

CREATE TABLE testSubpart(a INT ,b DATE)ENGINE = INNODB
PARTITION BY RANGE (YEAR(b))
SUBPARTITION BY HASH (TO_DAYS(b))
SUBPARTITIONS 2(
PARTITION P0 VALUES LESS THAN (1990),
PARTITION P1 VALUES LESS THAN (2018),
PARTITION P2 VALUES LESS THAN MAXVALUE
);

先对testSubpart进行RANGE分区，然后再进行了一次HASH分区，分区的数量为3*2 = 6 个。

2018/12/18  14:15           114,688 testsubpart#p#p0#sp#p0sp0.ibd
2018/12/18  14:15           114,688 testsubpart#p#p0#sp#p0sp1.ibd
2018/12/18  14:15           114,688 testsubpart#p#p1#sp#p1sp0.ibd
2018/12/18  14:15           114,688 testsubpart#p#p1#sp#p1sp1.ibd
2018/12/18  14:15           114,688 testsubpart#p#p2#sp#p2sp0.ibd
2018/12/18  14:15           114,688 testsubpart#p#p2#sp#p2sp1.ibd

注意：每个子分区的数量必须相同；如果在一个分区表上的任何分区上使用SUBPARTITION来明确定义任何子分区，那必须定义所有的子分区；每个SUBPARTITION子句必须包括子分区的一个名称，在每个子分区内，子分区名字必须是唯一的。

分区中的NULL值处理

MySQL数据库允许对NULL值做分区，MySQL数据库的分区把NULL值视为小于任何一个非NULL的值，对于不同的分区类型，MySQL数据库对于NULLd的处理是不一样的。

对于RANGE分区，如果对于分区列插入NULL值，则MySQL数据库会将该值放入到最左边的分区；

LIST分区中，使用NULL值，必须显式的指出哪个分区中放入NULL值，否则报错；

HASH和KEY分区对于NULL处理，将含有NULL值的记录返回为0；

参考《MySQL技术内幕InnoDB存储引擎》  姜承尧著，建议读此书加深理解。