MySQL技术内幕：InnoDB存储引擎（第4章表）

MySQL技术内幕：InnoDB存储引擎（第4章表）

4.1索引组织表

说明

在InnoDB存储引擎中，表都是根据主键顺序组织存放的，称为索引组织表（index organized table）。 每张表都有主键（primary key）。

没有显示创建主键处理

判断是否有非空唯一索引（unique not null），有，则该列为主键。
如果没有，则自动创建一个6字节大小的指针。

补充：会选择第一个定义的非空唯一索引。

4.2InnoDB逻辑存储结构

4.2.1表空间（tablespace）

表空间是逻辑结构的最高层，所有数据都存放在表空间中。
最大为：16K*2^8=64T

• 共享表空间（ibdata1）

  • 存放所有数据

• 独立表空间（innodb_file_per_table）

  • 存放数据

    值存放数据、索引和插入缓冲（bitmap）页

  • 其他类型数据（共享表空间）

    回滚（undo）信息、插入缓冲索引页，系统事务信息，二次写缓冲（double write buffer）

4.2.2段（segment）

• 常见类型

  数据段：B+树的叶子节点（leaf node segment）
  索引段：B+树的非叶子节点（Non-leaf node segment）
  回滚段：undo segment

4.2.3区（extent）（大小1M）

• 注意

  区是由连续页组成的空间，大小为1MB（64个连续页，页大小为16K）；为保证连续性，一次申请4~5个区。

  补充：innodb_page_size可以设置页大小。

• 初始建表默认96K原因？

  先用32个碎片页存放数据，用完后才申请64个连续页。对于小表或undo段，申请较少空间，节省磁盘容量。

4.2.4页（page、block）（磁盘管理最小单位，默认16K）

• 常见页类型

  数据页
  undo页
  系统页
  事务数据页
  插入缓冲位图页
  插入缓冲空闲列表页
  未压缩的二进制大对象页
  压缩的二进制大对象页

4.2.5行（row）（最多运行存放16K/2-200行记录，即7992行）

整体（图）

4.3InnoDB行记录格式

4.3.1Compact行记录格式（紧凑）

• 格式（图）

  • 

• 说明：

  • 变长字段长度列表（不固定）（1~2字节，限制最大长度65535）

    描述该记录中可变列字节长度，安全列的顺序逆序放置：
    小于255，用1字节；
    大于255，用2字节。

  • NULL标志位（不固定）（长度为：列数/8，向上取整）

    使用bit位标识该列数据是否有NULL值：
    1表示 有
    0表示 没有

  • 记录头信息（固定5字节，40位）（图）

    重要属性：
    deleted_flag：删除标志
    record_type：记录类型，000表示普通，001表示B+树节点指针，010,表示infimum，011表示supremum，1xx表示保留
    next_record：页中下一条记录的相对位置
    

    • 实际列存储数据（图）

      NULL不占该部分任何空间；
      真实列存储数据；
      还包括：rowid（没有自定义主键时存在，6字节）、事务ID列（6字节）和回滚指针列（7字节）

      • 

4.3.2Redundant行记录格式（冗余）

• 格式（图）

  • 

• 说明：

  没有非NULL标志记录

  • 字段长度偏移列表（不固定）（1~2字节，限制最大长度65535）

    记录变长字段实际长度，按照列顺序逆序放置；
    1-2字节

  • 记录头信息（固定6字节，48位）

    重要参数：
    deleted_flag：该行是否被删除
    n_fields：记录列中的数量（10字节，最多1023列）
    lbyte_offs_flag：偏移列表为1字节还是2字节
    next_record：页中下一条记录的相对位置

4.3.3行溢出数据

• 限制说明（页大小16K，至少保存2条记录）

  如果将所有数据都保存到B+树中，没有溢出处理，那么B+树将退化为链表，效率低下。
  varchar可能溢出，text/blob可能不溢出，取决实际内容长度。

  • compact和redundant（图）

    对varchar最多保留768字节，多余部分放到溢出页中；
    没有溢出不用处理；
    保证每页至少2行记录

    • 

  • compressed和dynamic

    如果放一条记录能够不溢出，则直接存放。
    当插入第二条记录时，存放不下则进行溢出处理。
    只保留溢出页面的20字节指针，text/blob小于等于40字节的列直接存储。

4.3.4Compressed（压缩）和Dynamic（动态）行记录格式

两者结构和compact类似，对于溢出只存放20字节指针
Compressed：对用户存储内容进行压缩（zlib）

4.3.5Char的行结构存储

1.长度是只字符长度，不同编码单个字符长度对于不同字节长度
2.对于未能占满长度字符使用0x20填充

4.4InnoDB数据页结构

4.4.1File Header（文件头，38字节）

重要属性：
checksum：校验值，用于验证页的完整性
offset：该页在表空间中偏移位置
prev：上一个页指针（双向链表）
next：下一个页指针（双向链表）
LSN：该页最后被修改的日志序列位置
type：页的类型
arch_log_no_or_space_id：所属表空间

• 组成（图）

  • 

• 页类型（图）

  • 

4.4.2Page Header（页头，56字节）

• 组成（图）

  • 

  • 

4.4.3Infimum和Supremum Records（记录开始和结束虚拟行）（图）

每个数据页有两个虚拟的行记录，用来限定记录的边界。
infimum最小值，supremum最大值。

• 

4.4.4User Records（用户记录，即行数据）和Free Space（空闲空间）

User Record在Page内以单链表的形式存在，最初数据是按照插入的先后顺序排列的，但是随着新数据的插入和旧数据的删除，数据物理顺序会变得混乱，但他们依然保持着逻辑上的先后顺序。

• 多Page完整（图）

  • 

4.4.5Page Directory（页目录）（在页内快速二叉查找记录）

存放记录的相对位置，这些指针被称为Slots（槽）或目录槽（Directory Slots），是一个稀疏目录（sparse directory）。按照索引键值顺序存放，可以利用二叉查找快速找到指定记录。
补充：
B+数索引本身并不能找到具体的一条记录，只能找到所在页，然后将整个页加载到内存，再通过page directory进行二叉查找。

4.4.6File Trailer（文件结尾信息，8字节）FILE_PAGE_END_LSN（checksum4+lsn4）

checksum：校验值，和File Header值得checksum进行对比
LSN：和File Header值得checksum进行对比
保证页的完整性。

页结构（图）

• 

4.5Named File Formats（图）

4.6约束（constraint）

4.6.1数据完整性

• 实体完整性保证表中有一个主键（primary key 或 unique key）

• 域完整性保证数据每列的值满足特点条件

  选择合适的数据类型确保一个数据值满足特定条件；
  外键（foreign key）约束；
  编写触发器；
  default约束

• 两张表的完整性（外键）

• 约束

  primary key
  unique key
  foreign key
  default
  not null

4.6.2约束的创建和查找

• 创建（建表时；后续添加alter）

  建表时定义约束；
  alter table命令进行创建约束

• 查找

• 创建（图）

  • 

  • 

4.6.3约束和索引的区别

约束是一个逻辑概念，用来保证数据完整性；
索引时一个数据结构，代表着物理存储方式

4.6.4对错误数据的约束

和SQL_MODE的设置有关

4.6.5ENUM和SET约束

• 创建（图）

  • 

4.6.6触发器与约束（增删改，前后，共6个）

一个表最多建立6个触发器：
分别为insert、update、delete的before和after各定义一个。

4.6.7外键约束

称被引用的表为父表；引用的表称为子表。

• 动作类型

  • CASCADE

    父表delete或update时，子表页delete或update

  • SET NULL

    父表delete或update时，子表设置null，前提子表允许null

  • NO ACTION

    父表delete或update时，抛出错误，不允许该操作

  • RESTRICT

    父表delete或update时，抛出错误，不允许该操作

• 创建语句（图）

  • 

4.7视图

在mysql中是一个虚表，没有实际物理存储

4.7.1视图的作用

作为抽象装置展示数据，不用关系基表结构，起到一定安全作用。
查看视图：
show tables;
或者
select * from information_schema.TABLES where TABLE_NAME=‘xxx’;

• 创建语句（图）

  • 

4.7.2物化视图(mysql不支持)

有物理存储。
mysql不支持，可以通过触发器来简单实现。

4.8分区表

分区是一种表的设计模式。正确的分区可以极大地提升数据库的查询效率，完成更高质量的SQL编程

10.1 分区概述

分区功能并不是在存储引擎层完成的，因此不只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持分区。

• 分区过程

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

• 分区类型（MySQL只支持水平分区，局部分区索引）

  MySQL数据库支持的分区类型为水平分区[插图]，并不支持垂直分区[插图]。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放数据又存放索引。全局分区是指，数据存放各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库暂时不支持全局分区。

• 作用

  分区可能会提高某些SQL语句性能，但是其主要用于高可用性，利于数据库的管理。

• 分区类型

  1.RANGE分区：行数据基于属于一个给定连续区间的列值放入分区。MySQL 5.5开始支持RANGE COLUMNS的分区。
  2.LIST分区：和RANGE分区类型一样，只是LIST分区面向的是离散的值。MySQL 5.5开始支持LIST COLUMNS的分区。
  3.HASH分区：根据用户自定义表达式的返回值来进行分区，返回值不能为负数。
  4.KEY分区：根据MySQL数据库提供的散列函数来进行分区。

• 创建分区限制（是否有唯一索引）

  不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。
  当建表时没有指定主键和唯一索引时，可以指定任何一个列为分区列。

10.2 分区类型

• 10.2.1 RANGE分区

  最常用的一种分区类型。

  • 样例

    create table t(
    id int
    )engine=innodb
    partition by range (id) (
    partition p0 values less than (10),
    partition p1 values less than (20),
    partition p2 values less than maxvalue
    )

  • 文件说明

    启用分区之后，表不再由一个ibd文件组成，而是由建立分区时的各个分区ibd文件组成，比如下面的t#P#p0.ibd、t#P#p1.ibd。

• 10.2.2 LIST分区（离散，插入值必须在分区中）

  LIST分区和RANGE分区非常相似，只是分区列的值是离散的，而非连续的。
  样例：
  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)
  );

• 10.2.3 HASH分区

  create table t_hash(
  a int,
  b datetime
  )engin=innodb
  partition by hash(year(b))
  partitions 4;

  • LINEAR HASH

    MySQL数据库还支持一种称为LINEAR HASH的分区，它使用一个更加复杂的算法来确定新行插入到已经分区的表中的位置。

• 10.2.4 KEY分区

  KEY分区和HASH分区相似，不同之处在于HASH分区通过用户定义的函数进行分区，KEY分区使用MySQL数据库提供的函数进行分区.

• 查看分区情况

  select table_name, partition_name, table_rows from information_schema.partitions
  where table_schema=database() and table_name=‘t_hash’;

• 10.2.5 COLUMNS分区

  RANGE、LIST、HASH和KEY这四种分区中，分区的条件必须是整型（integer），如果不是整型，那么需要通过函数将其转化为整型，如YEAR（）、TO_DAYS（）、MONTH（）等函数。
  COLUMNS分区，可视为对RANGE分区和LIST分区的一种进化。COLUMNS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得到，不需要转化为整型。

  • 支持以下数据类型

    1.所有的整型类型，如INT、SMALLINT、TINYINT和BIGINT。对FLOAT和DECIMAL则不予支持。
    2.日期类型，如DATE和DATETIME。对其余的日期类型不予支持。
    3.字符串类型，如CHAR、 VARCHAR、BINARY和VARBINARY。对BLOB和TEXT类型不予支持。

实验参考

分区表索引问题

索引方式：
性能依次降低
1.主键分区

主键分区即字段是主键同时也是分区字段，性能最好

2. 部分主键+分区索引

使用组合主键里面的部分字段作为分区字段，同时将分区字段建索引

3.分区索引

没有主键，只有分区字段且分区字段建索引

4.分区+分区字段没有索引

只建了分区，但是分区字段没有建索引

自增主键hash分区不一定均匀（回滚时自增主键不会回滚）

10.5 分区和性能

分区表使用分区键（主键或索引）进行查询效率高，使用分分区键查询效率低需要对所有分区进行扫描

10.6 在表和分区间交换数据alter table … exchange partition pX with table …

MySQL 5.6开始支持ALTER TABLE … EXCHANGE PARTITION语法。该语句允许分区或子分区中的数据与另一个非分区的表中数据进行交换。如果非分区表的数据为空，那么相当于将分区中的数据移动到非分区表中。若分区表的数据为空，则相当于将外部表中的数据导入分区中。

• 必须满足下面的条件

  1.要交换的表须与分区表有相同的表结构，但是表不能含有分区。
  2.非分区表中的数据必须在交换的分区内定义。
  3.被交换的表中不能含有外键，或者其他表中不能含有对该表的外键引用。
  4.用户除了需要ALTER、INSERT和CREATE权限外，还需要DROP的权限。
  此外，有两个小的细节需要注意：
  1.使用该语句时，不会触发交换表和被交换表上的触发器。
  2.AUTO_INCREMENT列将被重置。

• 例如：

  将e表的分区p0中的数据移动到表e2中：
  alter table e exchange partition p0 with table e2;

10.3 子分区（subpartition、复核分区）range、list上在进行hash或key子分区

子分区（subpartitioning）是在分区的基础上再进行分区，有时也称这种分区为复合分区（composite partitioning）。MySQL数据库允许在RANGE和LIST的分区上再进行HASH或KEY的子分区。

• 样例

  create table ts(
  a int,
  b DATETIME
  )ENGINE=INNODB
  PARTITION by range (year(b))
  subpartition by HASH(to_days(b))(
  partition p0 values less than (2000)(
  subpartition s0,
  subpartition s1
  ),
  partition p1 values less than (2010)(
  subpartition s2,
  subpartition s3
  ),
  partition p2 values less than (2020)(
  subpartition s4,
  subpartition s5
  )
  );

  SELECT table_name, partition_name, subpartition_name, table_rows from information_schema.PARTITIONS where table_name=‘ts’;

10.4 分区中的NULL值

1.对于RANGE分区，如果向分区列插入了NULL值，那么MySQL数据库会将该值放入最左边的分区。
2.要在LIST分区下使用NULL值，必须显式地指出向哪个分区中放入NULL值，否则会报错
3.HASH和KEY分区对NULL的处理方式：任何分区函数都会将含有NULL值的记录返回为0