索引组织表

在InnoDB存储索引中，表时根据主键顺序组织存放的，这种存储方式的表称为索引组织表。

在InnoDB存储引擎表中，每张表都有个主键，如果在创建表时没有显式地定义主键，则会按照如下方式创建主键：

• 判断表中是否有非空的唯一索引，如果有该列为主键；
• 表中没有非空的唯一索引，InnoDB引擎自动创建一个6字节大小的指针；
  当表中有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键（根据定义的索引顺序，而不是建表时列的顺序）。

InnoDB逻辑存储结构

表空间

表空间是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。
表空间由段（Segment）、区（extent）、页（Page）组成。

在默认情况下，InnoDB存储引擎有一个共享表空间ibdata1，所有的数据都存放在这个表空间内。
如果配置了innodb_file_per_table，则每张表内的数据可以单独存放在一个表空间中。此时每张表的表空间存放的只是数据、索引和插入缓冲的BitMap页，其他类的数据如回滚（undo）、插入缓冲索引页、系统事务信息、二次写缓冲等还是存放在原来的共享表空间。

段

表空间是由各个段组成的，包含数据段、索引段、回滚段等。
数据段就是B+树的叶子节点，索引段就是B+树的非叶子结点。
回滚段？？？
在InnoDB存储引擎中，对段的管理都是由引擎自身完成的，DBA不能对其进行控制。

区

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

页

页是InnoDB磁盘管理的最小单位，在InnoDB存储引擎中，默认每个页的大小为16KB。
可以通过参数 innodb_page_size 来设置页的大小为4K、8K、16K。
如果设置完成，则所有表中的页大小都变成innodb_page_size，不可用对其再次进行修改。除非通过mysqldump导入和导出来生成新的库。

页的类型有：

• 数据页（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 行的记录。

InnoDB行记录格式

Compact 行记录格式

从Mysql5.0开始，设计目的是高效地存储数据。一个页中存放的行数据越多，其性能就越高。

|—|—|—|—|—|—|—|—|—|
| 变长字段长度列表 | NULL标志位 | 记录头信息 | 列1数据 | 列 2 数据 | …|事务ID列|回滚指针列|__rowid|

变长字段长度列表：按照列的顺序，逆序放置。
如果列的长度小于255个字节，占用1字节；
如果列的长度大于255个字节，占用2字节；
变长字段的长度最大不可以超过2个字节，因为Mysql数据库中VARCHAR类型的最大长度限制为65535.
NULL标志位：表示改行数据中是否有NULL值，有用1表示，占用1个字节。
记录头信息：固定占用5个字节。
最后的部分是实际存储没咧的数据。
事务ID列：隐藏列，占用6字节。
回滚指针列:隐藏列，占用7字节。
__rowid：占用6字节。

行溢出数据

InnoDB存储引擎的数据都是存放在页类型为B-tree Node中，当发生行溢出是，数据存放在页类型为uncompress BLOG页中。

InnoDB数据页结构

Named File Formats 机制

###约束

数据完整性

约束的创建和查找

约束和索引的区别

对错误数据的约束

ENUM和SET约束

触发器与约束

外键约束

视图

分区表

分区功能不是在存储引擎层面完成的。
支持分区的存储引擎有：MyISAM、InnoDB、NDB。
不支持分区的存储引擎有：CSV、FEDORATED、MERGE。

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

Mysql支持水平分区，不支持垂直分区（水平分区，将同一表中的不同行的记录分配到不同的物理文件；垂直分区，将同一表中的不同列分配到不同的物理文件）。
Mysql不支持全局分区，只支持局部分区。
局部分区：一个分区中既存放了数据由存放了索引。
全局分区：数据存放在各个区，所有数据的索引存放在一个对象中。

分区主要用来数据库高可用性的管理，对于某些SQL的性能带来提高。

分区类型

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

  1 2 3 4 5 6 7

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

1

alter table t add partition (partition p2 values less than maxvalue);

启用分区后，表是由建立各个分区时的各个分区ibd文件组成。

• List分区：和RANGE分区类似，只是LIST分区面向的是离散的值。

1
2
3
4
5
6
7
8

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分区：根据用户自定义的表达式返回值进行分区，返回值不能为负数。
  Hash分区的目的是将数据均匀地分布到盂县定义的各个区中，保证各分区的数据数量大致都一样。

1
2
3
4
5
6

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

1
2
3
4
5
6

create table t(
  a int,
  b datetime
)engine=innodb
partition by linear hash(year(b))
prtions 4;

LINEAR HASH分区的优点在于，增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有大量数据的表。
缺点在于，与使用HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡。

• KEY分区：根据MYSQL数据库提供的哈希函数来分区。
  与HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用MYSQL数据库提供的函数进行分区。对于NDB Cluster引擎，MYSQL数据库使用MD5函数来分区；对于其他引擎，Mysql数据库使用内部的哈希函数。
  在KEY分区中，使用关键字LINEAR 和在HASH分区中具有同样的效果。

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

以上四种RANGE、LIST、HASH、KEY分区条件是，数据必须是整形，如果不是整型，那应该需要通过函数将其转化成整型，如YEAR（）, TO_DAYS(), MONTH()等函数。

• Column分区，RANGE分区和LIST分区的一种进化。
  COLUMN分区可以直接使用菲整形的数据进行分区，分区根据类型直接比较得到，不需要转化为整形。
  RANGECOLUMNS分区可以对多个列的值进行分区。

COLUMN分区支持
所有的整型：INT、SMALLINT、TINYINT、BIGING。
日期类型：DATE、DATETIME
字符串类型：CHAR、VARCHAR、BINARY、VARBINARY。

1
2
3
4
5
6
7
8

create table t(
  a INT,
  b DATETIME
)engine=innodb
partition by range columns(b)(
  partition p0 values less than ('2020-01-01'),
  partition p1 values less than ('2020-02-01')
)

子分区

子分区是在分区的基础上再进行分区，有时页称为符合分区。 MySQL允许在RANGE和LIST的分区傻姑娘再进行KEY或者HASH的子分区。

1
2
3
4
5
6
7
8
9
10
11

create table t(
   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 (2000),
     partition p2 values less than maxvalue
     );

分区中的NULL值

MysQL允许对NULL值做分区，对于RANGE分区，如果像分区列插入了NULL值，则Mysql数据库将该值放入到最左边的分区。
LIST分区下要使用NULL值，必须显式地指出将该值放入哪个分区。
HASH和KEY分区函数会将NULL值的记录返回0.

分区和性能

当数据量大时，分区可以有效降低B+树的层级。
当数据量小时，分区带来的收益并不明显，并且有可能导致扫描所有分区反而降低效率。

参数文件

动态参数

动态参数可以在Mysql实例运行中进行更改，可以通过SET命令对动态参数值进行修改。
SET [global|session] key = value;

静态参数

静态参数在整个实例生命周期内不得进行更改，需要重新启动实例才会生效。

日志文件

错误日志

记录了对Mysql的启动、运行、关闭过程中所有的错误、警告和正确的信息。

慢查询日志

可以帮助定位可能存在问题的SQL语句，从而进行SQL层面的优化。Mysql在启动时设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。
通过设置 long_query_time 来设置，单位为秒。
log_queries_not_using_indexes: 记录没有使用索引的查询日志。
log_throttle_queries_not_using_indexes:每分钟允许记录到showLog的且未使用索引的SQL语句次数，0表示不限制。

查看慢日志

1
2
3

mysqldumpslow *.log
//查看执行时间最长的10条sql语句
mysqldumpslow -s al -n 10 *.log

查询日志

查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。

查看查询日志：

1

tail *.log

二进制日志

二进制日志记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类对数据本身没有修改的操作。二进制日志还包括了执行数据库更改操作的时间等其他额外信息。

作用

• 恢复
  某些数据的恢复需要二进制日志，如在一个数据库全备文件恢复后，用户可以通过二进制日志进行pint-in-time的恢复。
• 复制
  与恢复类似，通过复制和执行二进制日志使另一台远程的MySQL数据库与当前数据库进行实时同步。
• 审计
  通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

配置

• max_binlog_size: 单个日志文件的最大值，默认1G
• binlog_cache_size: 所有未提交的二进制日志会被记录到一个缓存中，等该事务提交时直接将缓冲中的二进制日志写入二进制日志文件，该缓冲的大小默认为32。该值是基于会话的，每个会话会申请32K的空间。
• binlog_cache_use: 记录了使用临时文件写二进制日志的次数。
• sync_binlog：表示每写缓冲多少次就同步到磁盘，为1时，表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志。默认值为0.
• binlog-do-db：写入哪些库的日志，默认为空表示同步所有库的日志到二进制日志。
• binlog-ignore-db：忽略哪些库的日志
• log-slave-update：当前库为slave时，如果需要将从master取得并执行的二进制日志写入自己的二进制日志文件中时需要设置该值。
• binlog_format：STATEMENT/ROW/MIXED
  STATEMENT：格式基于SQL语句，如果主服务器运行rand、uuid等函数或者触发器等操作时，就会导致主从服务器上表中的数据库表中的数据库不一致。
  ROW：二进制的日志，记录了表的更改情况，解决了Statement下复制的问题。可以设置InnoDB的事务隔离设置为READ_COMMITTED，以获得更好的并发性。
  MIXED：默认采用STATEMENT，但是在特殊情况下会转为ROW。

套接字文件

名为mysql.sock,可以通过Unix域套接字方式进行与本地MySQL进行连接。套接字文件由参数socket控制。

PID文件

MySQL实例启动时，会将自己的进程ID写入一个文件中，该文件为PID文件。

表结构定义文件

因为MySQL插件式存储引擎的体系结构关系，MySQL数据的存储是根据表进行的，每个表都会有与之对应的文件。但不论表采用哪种存储引擎，MySQL都有一个以frm为后缀名的文件，记录了这个表结构或者视图的定义。

Innodb存储引擎文件

表空间文件

InnoDB采用将存储的数据按照表空间进行存放的设计。默认配置喜爱会有一个初始大小为10MB，名为ibdata1的默认表空间文件。

将ibdata1,ibdata2两个文件用来组成表空间。

1

innodb_data_file_path=/db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

如果该两个文件位于不同的磁盘上，磁盘的负载可能被平均，因此可以提高数据库的整体性能。
设置innodb_data_file_path后，所有给予innoDB存储引擎的表的数据都会记录带该共享表空间中。

1

innodb_file_per_table=ON //每个基于InnoDB存储引擎的表产生一个独立表空间。命名规则是：表名.ibd

单独的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还存放在默认的表空间中。

重做日志文件

默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件。
他们记录了对于InnoDB存储引擎的事务日志。

重做日志用于由于主机宕机导致实例失败时，恢复到失败前的状态，一次来保证数据的完整性。

每个InnoDB存储引擎至少有1个重做日志文件组，每个文件组下至少有2个重做日志文件。为了得到更高的可靠性，用户可以设置多个的景象日志组。将不同的组存放在不同的磁盘上，以此提高重做日志的高可用性。在日志组中每个重做日志文件的大小一致，并以循环写入的方式运行。
InnoDB存储引擎献血重做日志文件1，当达到文件的最后时，会切换到重做日志文件2，再当重做日志文件2也被写满时，再切换到重做日志文件1中。

1
2
3
4

innodb_log_file_size:每个重做日志文件的大小
innodb_log_files_in_group: 日志文件组中重做日志文件的数量，默认为2
innodb_mirrored_log_groups: 日志镜像文件组的数量，默认为1，表示没有镜像。
innodb_log_group_home_dir: 日志文件组所在的路径，默认为./

重做日志文件的大小对InnoDB存储性能有非常大的影响。
如果设置太大，在恢复时可能需要很长的时间。
如果设置太小，会导致一个事务的日志需要多次切换重做日志文件。同时会导致频繁地发生 async checkpoint， 导致性能抖动。

二进制文件与重做日志

层次不同：
二进制日志：记录所有与MySQL数据库有关的日志记录，包括InnoDB，MyISAM，Heap等其他存储引擎的日志。
重做日志：值记录InnoDB存储引擎本身的事务日志。

内容不同：
二进制日志文件记录的是一个事务的具体操作内容，为逻辑日志。
重做日志记录的是关于每个页的更改的物理情况。

写入时间不同：
二进制文件仅在事务提交前进行，无论事务大小，只写磁盘一次。
在事务进行的过程后再难过，不断有重做日志条目被写入到重做日志文件中。

重做日志条目结构

redo_log_type | space | page_no | redo_log_body
redo_log_type ： 占用1字节，表示重做日志的类型
space： 表示表空间的ID，采用压缩的方式，占用空间可能小于4字节
page_no：表示页的偏移量，压缩方式
redo_log_body:每个重做日志的数据部分，恢复时需要调用相应函数解析。

重做日志写入

重做日志的写入不是直接写，而是先写入重做日志缓冲（redo log buffer）中，然后按照一定的条件顺序地写入日志文件。

从重做日志缓冲网磁盘写入时，按照写入的最小单位（扇区大小：512个字节）进行写入，因此可以保证写入必定是成功的，因此在重做日志的写入过程不需要有doublewrite。

写入时机：
1） 不论事务已经提交，主线程每秒都会将重做日志缓冲写入到磁盘的重做日志文件中。
2） 事务提交时，设置了innodb_flush_log_at_trx_commit。
值为0时，不将事物的重做日志写入磁盘上的日志文件，
1表示在执行commit时将重做日志缓冲同步写到磁盘，
2表示将重做日志异步写到磁盘（也就是写到文件系统的缓存中）

因此，为了保证ACID中的D（持久性），必须将innodb_flush_log_at_trx_commit设置为1，当数据库因为意外宕机时，可以通过重做日志文件恢复，并保证可以恢复已经提交的事务。