4-表

1.索引组织表

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

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

2.InnoDB逻辑存储结构

所有数据都被逻辑地存放在一个空间中，称之为表空间（tablespace）。表空间又由段（segment）、区（extent）、页（page）组成。

①表空间

默认情况下所有数据都存放在ibdata1的表空间内。如果启用了参数innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内。需要注意的是每张表的表空间内存放的只是数据、所有、插入缓冲bitmap页，其他类的数据（如回滚信息、插入缓冲索引页、系统事务信息、二次写缓冲等）还是存放在原来的共享表空间内（ibdata1）。

②段

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

因为InnoDB存储引擎表是索引组织的，因此数据即索引，索引即数据。那么数据段即为B+树的叶子节点（上图Leaf node segment），索引段即为B+树的非索引节点（上图Non-leaf node segment）。

③区

区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4~5个区。默认情况下，InnoDB存储引擎页的大小为16KB，即一个区中一共有64个连续的页。

④页

默认每个页大小为16KB。常见的页类型有：

• 数据页（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存储引擎是面向列的（row-oriented），也就是说数据是按行进行存放的。每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2-200行的记录，即7992行记录。

3.InnoDB行记录格式

①Compact行记录格式

MySQL 5.0 中引入的，设计目标是高效地存储数据。简单来说，一个页中存放的行数据越多，其性能就越高。

• 变长字段长度列表：一个非NULL变长字段长度列表，并且其是按照列的顺序逆序放置的，长度为：若列的长度小于255字节，用1字节表示；若大于255个字节，用2字节表示。

• NULL标志位：指示了该行数据中是否有NULL值，有则用1表示。

• 记录头信息（record header），固定占用5字节（40位），每位的含义：

• 最后的部分：实际存储每个列的数据。NULL不占该部分任何空间，即NULL除了占有NULL标志位，实际存储不占任何空间。每行数据除了用户定义的列外，还有两个隐藏列，事务ID列和回滚指针列，分别为6字节和7字节大小。若InnoDB表没有定义主键，且没有唯一索引，每行还会增加一个6字节的rowid列。

注意：InnoDB每行有隐藏列TransactionId和Roll Pointer。

②Redundant行记录格式

MySQL5.0版本之前InnoDB的行记录存储方式，5.0支持Redundant是为了兼容之前版本的页格式。

4.InnoDB数据页结构

页类型为B-tree Node的页存放的即是表中行的实际数据了。

InnoDB数据页由以下7个部分组成：

• File Header（文件头）：38字节

• Page Header（页头）：56字节

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

• User Records（用户记录，即行记录）：实际存储行记录的内容。

• Free Space（空闲空间）：指空闲空间，同样也是个链表数据结构。在一条记录被删除后，该空间会被加入到空闲链表中。

• Page Directory（页目录）：存放了记录的相对位置（不是偏移量），有时候这些记录指针称为Slots（槽）或目录槽（Directory Slots）

• File Trailer（文件结尾信息）：8字节。为了检测页是否已经完整地写入磁盘。

5.约束

InnoDB存储引擎提供了以下几种约束：

• Primary Key： 主键
• Unique Key： 唯一索引
• Foreign Key： 外键
• Default
• NOT NULL

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

6.触发器

约束的一种

触发器的作用是在执行insert、delete、update命令之前或之后自动调用SQL命令或存储过程。

命令：create [definer = {user | current_user}] trigger trigger_name before|after insert|update|delete on tb1_name for each row trigger_stmt

例：设置usercash表金额都是减的。

create table usercash(userid int not null, cash int unsigned not null);

create table usercash_err_log(userid int not null, old_cash int unsigned not null, new_cash int unsigned not null, user varchar(30), time datetime);

delimiter $$

create trigger tgr_usercash_update before update on usercash for each row

begin

if new.cash-old.cash > 0 then

insert into usercash_err_log select old.userid,old.cash,new.cash,user(),now();

set new.cash = old.cash;

end if;

end;

$$

delimiter $$

7.视图

MySQL数据库中，视图（View）是一个命名的虚表，它由一个SQL查询来定义，可以当做表使用。与持久表（permanent table）不同的是，视图中的数据没有实际的物理存储。

作用：被用做一个抽象装置，特别是对于一些应用程序，程序本身不需要关心基表（base table）的结构，只需要按照视图定义来取数据或更新数据，因此视图同时在一定程度上起到一个安全层的作用。

8.分区表

分区功能不是在存储引擎层完成的，常见的MyISAM、NDB、InnoDB等都支持，CSV、MERGE等不支持。

分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

MySQL数据库支持的分区类型为水平分区，不支持垂直分区。

• 水平分区：将同一个表中不同行的记录分配到不同的物理文件中。
• 垂直分区：将同一个表中不同列的记录分配到不同的物理文件中。

MySQL分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指数据存放在各个分区，但所有数据的索引放在一个对象中。目前MySQL不支持全局分区。

①分区类型

MySQL支持以下几种类型的分区：

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

  主要用于日期列的分区，可以根据年来分区存放销售记录：

  例：create table sales(money int unsigned not null, date datetime) engine=innodb

  partition by range (year(date)) (partition p2008 values less then (2009), partition p2009 values less then (2010));

  select * from sales where date >= '2008-01-01' and date <= '2018-12-31'，查询时会自动使用分区，只搜索p2008。优化器能对year(),to_days(),to_seconds(),unix_timestamp()这类函数进行优化器选择。

• LIST分区：和RANGE分区相比，只是LIST分区面向的是离散的值，而非连续的。

  不同于range分区中定义的values less than语句，list分区使用values in

  例：create table t (a int, b int)engine=innodb

  partition by list(b) (partition p0 values in (1,3,5,7,9), partition p1 values in (0,2,4,6,8));

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

  partition by hash (expr) expr是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的列名。一般再添加一个 partitions num子句，num是一个非负的整数，表示表将要被分隔成分区的数量，如果没有包含，那么分区的数量将默认为1。

  例：create table t_hash (a int, b datetime)engine=innodb

  partition by hash (year(b)) partitions 4; //如2010-04-01 2010%4=2 将在2分区

• KEY分区：与HASH分区相似，不同之处在于KEY分区使用MySQL数据库提供的哈希函数来进行分区。

  例：create table t_key (a int, b datetime)engine=innodb

  partition by key (b) partitions 4;

如果表中存在主键或唯一索引，分区列必须是唯一索引（可以是允许NULL值的）的一个组成部分。如果表中没有主键和唯一索引，可以指定任何一个列为分区列。

②分区中的NULL值

• range分区，会将该值放入最左边的分区：

  create table t( id int) engine=InnoDB partition by range (id)(parition p0 values less than (10), partition p1 values less than maxvalue);

  NULL会放入p0分区，删除p0分区，删除的将是小于10的记录，并且还有NULL值的记录。

• list分区，必须显示地之处哪个分区中放入NULL值，否则会报错。

  create table t (a int, b int)engine=innodb

  partition by list(b) (partition p0 values in (1,3,5,7,9,NULL), partition p1 values in (0,2,4,6,8));

• hash和key分区，对于NULL的处理方式跟range、list不一样，任何分区函数都会将含有NULL值的记录返回为0

③分区和性能

create table profile (id int(11) not null auto_increment, nickname varchar(20) not null default '', primary key (id)) engine=InnoDB partition by hash (id) partitions 10;

假设表有1000w行数据，如果1000W的B+树的高度是3,100W的B+树的高度是2，那么上面按主键分区的索引可以避免1次IO，从而提高查询效率。但是，如果select * from profile where nickname = 'xxx'，这时对于nickname的查询需要扫描所有的10个分区，即使每个分区的查询开销为2次IO，则一共需要20次IO。而对于原来单表设计，KEY的查询只需要2~3次IO。