# 索引组织表 ### 主键 - 判断表中是否有非空的 Unique 索引，有的话选择第一个定义的非空 Unique 索引作为主键 - 没有的话自动创建 6 字节大小的指针。 _rowid 可以显示表的主键，仅限于单列主键 # InnoDB 逻辑存储结构 ### 区 每个区的大小都是 1 MB。创建表初始先用 32 个页大小的碎片页来存放数据，在使用完这些页之后才是 64 个连续的页的申请。 ### 页 - 数据页 - undo 页 - 系统页 - 事务数据页 - 插入缓冲位图页 - 插入缓冲空闲列表页 - 未压缩的二进制大对象页 - 压缩的二进制大对象页 最多允许 16KB / 2~200 行的记录，即 7992 行记录 # InnoDB 行记录格式 ### Compact 行记录格式 - 变长字段长度列表(col<255，1字节；col>255，2字节) - NULL 标志位(1 字节)：指示是否有 NULL 值，有则为 1。 - 记录头信息(5 字节，40 位) - Compact 记录头 - 未知(1 bit) - 未知(1 bit) - deleted_flag(1 bit)：改行是否已被删除 - min_rec_falg(1 bit)：为 1，如果该记录是预先被定义为最小的记录 - n_owned(4 bit)：该记录拥有的记录数 - heap_no (13 bit)：索引堆中该记录的排序记录 - record_type(3 bit)：记录类型 - 000：普通 - 001：B+ 树节点指针 - 010：Infimum - 011：supremum - 1xx：保留 - next_record(16 bit)：页中下一条记录的相对位置 - 列 1 数据 - rowid(6 字节) - 事务id(6 字节) - 回滚指针列(7 字节) - 列 2 数据 ``` 20 00 10 00 11 00 2B 30 30 30 35 30 36 34 30 62 31 39 65 31 31 65 39 61 63 64 30 30 30 35 30 35 36 38 63 38 66 36 66 00 ``` - 20 00 10：变长字段长度列表(逆序) - 00：NULL 标志位，第一行没有 NULL 值 - 11 00 2B 30 30：固定 5 字节长度 - rowid（主键dictid？） - 事务id(6 字节) - 回滚指针列(7 字节) ### 行溢出数据 MySQL 数据库的 VARCHAR 类型可以存放的列长度总和为 65535 字节。除去其他开销，最大长度为 65532。 存储引擎页为 16KB， 当阈值长度超过 8098 字节，则会出现行溢出，此时只保留前 768 字节+偏移量，指向行溢出页 ### Compressed 和 Dynamic 行记录格式 以前的格式称为 Antelope，支持 Copmact 和 Redundant，新的文件格式称为 Barracuda，拥有两种新的行记录格式：Compressed 和 Dynamic。采用了完全行溢出的方式，只存放 20 个字节的指针，实际数据都存放在 Off Page 中，而且会以 zlib 的算法进行压缩。 # InnoDB 数据页结构 ### File Header(38 字节) - FIL_PAGE_SPACE_OR_CHKSUM(4 字节)：代表页的checksum值 - FIL_PAGE_OFFSET(4 字节)：页的偏移值。 - FIL_PAGE_PREV(4 字节)：当前页的上一个页 - FILE_PAGE_NEXT(4 字节)：当前页的下一个页 - FIL_PAGE_LSN(8 字节)：该页被最后修改的日志序列位置 LSN - FIL_PAGE_TYPE(2 字节)：存储引擎页的类型 - 45BF：B+ 树叶节点 - 0002：Undo Log 页 - 0003：索引节点 - 0004：Insert Buffer 空闲列表 - 0000：该页为最新分配 - 0005：Insert Buffer 位图 - 0006：系统页 - 0007：事务系统数据 - 0008：File Space Header - 0009：扩展描述页 - 000A：BLOB 页 - FIL_PAGE_FILE_FLUSH_LSN(8 字节)：系统表空间的一个页中定义，代表文件至少被更新到该 LSN 值。对于独立表空间，该值为 0 - FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID(4 字节)：代表该页属于哪个表空间。 ### Page Header(56 字节) - PAGE_N_DIR_SLOTS(2 字节)：在 Page Directory 中的 Slot 数 - PAGE_HEAP_TOP(2 字节)：堆中第一个记录的指针，记录在页中是根据堆的形式存放的 - PAGE_N_HEAP(2 字节)：堆中的记录数。第15 位表示行记录格式 - PAGE_FREE(2 字节)：指针可重用空间的首指针 - PAGE_GARBAGE(2 字节)：已删除记录的字节数，即行记录结构中 delete flag 为 1 的记录大小的总数 - PAGE_LAST_INSERT(2 字节)：最后插入记录的位置 - PAGE_DIRECTION(2 字节)：最后插入的方向 - 01：PAGE_LEFT - 02：PAGE_RIGHT - 03：PAGE_SAME_REC - 04：PAGE_SAME_PAGE - 05：PAGE_NO_DIRECTION - PAGE_N_DIRECTION(2 字节)：一个方向连续插入记录的数量 - PAGE_N_RECS(2 字节)：该页中记录的数量 - PAGE_MAX_TRX_ID(8 字节)：修改当前页的最大事务 ID，注意该值仅在 Secondary Index 中定义 - PAGE_LEVEL(2 字节)：当前页在索引树中的位置 - 00 代表叶节点 - PAGE_INDEX_ID(8 字节)：表示当前页属于哪个索引 - PAGE_BTR_SEG_LEAF(10 字节)：B+ 树数据页非叶节点所在的段的 segment header。仅在 B+ 树的 Root 页中定义 - PAGE_BTR_SEG_TOP(10 字节)：B+ 树数据页所在段的 segment header。仅在 B+ 树的 Root 页中定义 ### Infimum 和 Supremum Records 每个数据页中有两个虚拟的行记录，用来限定行记录的边界。Infimum记录是比该页中任何主键值都要小的值，Supremum指比任何可能大的值还要大的值。 ### User Records 和 Free Space - User Records：存储行记录的内容 - Free Space：空闲空间 ### Page Directory 存放了记录的相对位置，这些记录指针称为槽(Slots)，InnoDB 存储引擎的槽是一个稀疏目录，即一个槽中可能包含多个纪录。伪记录 Infimum 的 n_owned 值总是为 1，记录 Supremum 的 n_owned 的取值范围为 [1, 8]，其他用户记录 n_owned 的取值范围为 [4, 8]。 二叉查找的结果只是一个粗略的结果，因此 InnoDB 存储引擎必须通过 recorder header 中的 next_record 来继续查找相关记录。 B+ 树索引本身并不能找到具体的一条记录，能找到只是该记录所在的页。 ### File Trailer(8 字节) 用于检测是否已经完整地写入磁盘。每次读取页都会进行检测。由参数 innodb_checksums 来控制。innodb_checksum_algorithm 来控制检测函数的算法。 File Trailer 只有一个 FIL_PAGE_END_LSN 部分，占用 8 字节。前 4 字节代表该页的 checksum 值，最后 4 字节和 File Header 中的 FIL_PAGE_LSN 值进行比较，看是否一致，以此来保证页的完整性。 # 约束 - ENUM - SET - 触发器：最多为一个表建立 6 个触发器，即分别为 INSERT、UPDATE、DELETE 的 BEFORE 和 AFTER 各定义一个。只支持 FOR EACH ROW 的触发模式 - 外键约束：及时检查 - CASCADE：表示父表发生 DELETE 或 UPDATE 操作时，对相应的子表中的数据也进行 DELETE 或 UPDATE 操作。 - SET NULL：表示当父表发生 DELETE 或 UPDATE 操作时，相应的子表中的数据被更新为 NULL 值。 - NO ACTION - RESTRICT：表示当父表发生 DELETE 或 UPDATE 操作时，抛出错误，不允许这类操作发生。 元数据 - information_schema.TABLE_CONSTRAINTS - information_schema.REFERENTIAL_CONSTRAINTS 参数： - sql_mode - STRICT_TRANS_TABLES：对于输入值的合法性进行约束 # 视图 ### 视图 - WITH CHECK OPTION：对更新视图插入的数据进行检查，对不满足视图定义条件的，将会抛出一个异常，不允许视图中数据更新。 查看 ```mysql SELECT * FROM information_schema.TABLES WHERE table_type='BASE TABLE' AND table_schema=database()\g; ``` ```mysql SELECT * FROM information_schema.VIEWS WHERE table_schema=database()\G; ``` # 分区表 ### 查看 ```mysql SHOW VARIABLES LIKE '%partition%'\G; SHOW PLUGINS\G; ``` ```mysql SELECT * FROM information_schema.PARTITIONS WHERE table_schema=database() AND table_name='t'; ``` ```mysql EXPLAIN PARTITION ``` ### 类型 不论创建何种类型的分区，如果表中存在主键或 Unique 索引，分区列必须是 Unique 索引的一个组成部分。如果建表时没有指定主键、 Unique 索引，可以指定任何一个列为分区列。 - RANGE 分区：只能对 YEAR()、TO_DAYS()、TO_SECONDS()、UNIX_TIMESTAMP() 这类函数进行优化选择。 t#P#p0.ibd, t#P#p1.ibd - LIST 分区 - HASH 分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数 - KEY 分区：根据 MySQL 数据库提供的哈希函数来进行分区。 - COLUMNS 分区：相当于 RANGE 和 LIST 功能的升级，支持多列、非整数等分区 - 所有的整形类型 - 日期类型 - 字符串类型 ### 子分区 - RANGE 和 LIST 的分区上再进行 HASH 和 KEY 的子分区 - 每个子分区的数量必须相同 - 要在一个分区表的任何分区上使用 SUBPARTITION 来明确定义任何子分区，就必须定义所有的子分区。 - 每个 SUBPARTITION 子句必须包括子分区的一个名字。 - 子分区的名字必须是独一无二的。 ### 子分区中的 NULL 值 - RANGE：视 NULL 值小于任何的一个非 NULL 值 - LIST：必须制定哪个区放 NULL 值 - HASH & KEY：任何分区函数都会将含有 NULL 值的记录返回为 0 ### 在表和分区间交换数据 - 要交换的表需和分区表有着相同的表结构，但是表不能含有分区 - 在非分区表中的数据必须在交换的分区定义内 - 被交换的表中不能含有外键，或者其他的表含有该表的外键引用。 - 用户除了需要 ALTER、INSERT 和 CREATE 权限外，还需要 DROP 的权限 - 使用该语句时，不会触发交换表和被交换表上的触发器 - AUTO_INCREMENT