mysql--innodb之索引组织表

索引组织表

• 依赖主键关系存储的表被称为索引组织表
• 若建表时未显式定义主键
  • 无唯一且非空的字段，则自动创建主键
  • 有多个唯一且非空字段，则按索引的定义顺序，取第一个为主键
  • select *,_rowid from t1; _rowid可以查看主键数值，但仅限单个主键，多列组成的复合主键不适用

innodb存储逻辑结构

• 表空间 数据都被逻辑的放在一个空间中，也叫做表空间。默认情况下所有数据都会被存储到共享表空间中，如果启动参数innodb_file_per_table，则每张表的数据都会单独存放到一个表空间中
  • 表空间由段(segment)、区(extent)、页(page)组成
  • 独立表空间只存储数据，索引等，其他信息如undo日志，二次写缓存等还是存放到共享表中
  • 创建的表默认大小是96kb。创建新表时一般先使用32个页大小的碎片页来存放数据，使用完后再申请64个连续页(段)的申请
• 段 常见段有数据段、索引段、回滚段等
  • 数据段一般为b+树的叶子节点
  • 索引段位b+树的非叶节点
  • 回滚段记录旧版本数据，用来将数据回滚到事务开始的状态
• 区
  • 区是连续页组成的空间，大小为1M,默认有64个连续页
  • KEY_BLOCK_SIZE 设置压缩页页面大小，设置ROW_FORMAT=COMPRESSED有效
  • innodb_page_size 设置页的默认大小，非压缩数据
• 页 innodb磁盘管理的最小单位，默认页大小16kb，b+树叶节点默认为一页
• 数据页（b+树叶节点）：存储表中的数据记录
• undo页 （undo日志页）：存储事务回滚信息
• 系统页 （system page）: 存储表空间的元数据信息
• 事务数据页（transaction system page）: 存储事务相关的元数据
• 插入缓冲位图页（insert buffer bitmap page）: 存储insert buffer的位图信息
• 插入缓冲空虚列表页（insert buffer free list）: 存储insert buffer的空闲列表信息
• 未压缩的二进制大对象页（uncompressed blob page）: 存储未压缩的blob和text类型数据
• 压缩的二进制大对象页 （compressed blob page）: 存储压缩后的blob和test类型数据
• 行 innodb数据按行存放， 每页最多允许存放16kb/2b-200行的记录，即7992行记录

innodb行记录格式

• 行记录格式类型
  • REDUNDANT: innodb的原始行搁置，兼容性较好，存储率较低
  • COMPACT: 较REDUNDANT，储存率更高，null值不占位
  • DYNAMIC: DYNAMIC是COMPACT的扩展，适合存储可变长度列。只存储实际使用的空间，不预留固定大小
  • COMPRESSED: DYNAMIC格式的变体，提供整页压缩，减少磁盘使用，会增加cpu的使用(数据压缩需要)
• Redundant 行记录格式

create table test (tl varchar(10),t2 varchar(10),t3 char(10),t4 varchar(10)) engine=innodb charset=lantin1 row_format=redundant

• 字段长度偏移列表: 按列的顺序逆序存放字段长度偏移信息 insert into test values ('a', 'bb','bb','ccc') 字段长度偏移列表为23 20 16 14 13 0c 06 ，逆转顺序后为06 0c 13 14 16 20 23,分别代表第一列长度为6，第二列长度为6(0x0c=12-6)，第三列长度为 7(0x13=0x13-0x0c=16+3-12)，第四列长度为1(0x14-0x13)，第五列长度为2(0x16-0x14)，剩下两列以此类推。这里多出来的其中三列是事务id，回滚指针和主键id
• 记录头信息：头信息中会存储该行是否被删除、索引堆中该记录的排序、记录类型、页中下一条记录的相对位置(偏移量)等。n_fields值代表一行中列的数量，占用10位，故mysql支持表中列最多为1023
• 列信息：变长列为空不额外占用空间，但固定长度列为空会占用可能存放的最大字节数
• Compact 行记录格式

create table test (tl varchar(10),t2 varchar(10),t3 char(10),t4 varchar(10)) engine=innodb charset=lantin1 row_format=compact

• 变长字段长度列表：非null边长字段长度列表，按照列的顺序逆序放置的。列长度小于255字节，用1字节表示，列长度大于255字节，用2字节表示。 insert into test values ('a', 'bb','cc','ddd') 变长字段为t1,t3,t4,那么列的顺序逆序的变长字段的长度分别为03 02 01。
• null标志位: 表示改行数据中那些列为null,有则1表示 insert into test('a',null,null,'d')，由于第二列和第三列为空，故null标志位的二进制表示为00000110，也就是06，所以null列不占用额外的存储空间
• 记录头信息：头信息中会存储该行是否被删除、索引堆中该记录的排序、记录类型、页中下一条记录的相对位置(偏移量)
• Dynamic行记录格式

变长列页外存储：将可变长列值存储在页外，索引记录值包含溢出页的20字节指针

• 减少了数据页的大小，替身了容纳行数，适合大量可变长列的表
• 增加了i/o操作，可能导致部分查询性能下降
• Compressed行记录格式 使用zlib算法会对行数据进行压缩，减少磁盘空间使用
• 页外存储和Dynamic相似，会将可变长度的列存储在页外，索引只记录20字节的指针指向溢出页
• 数据压缩会增加cpu使用

拓展

• 不使用undo日志依赖master线程来标记为可用空间，供下次使用
• 表空间的元数据：系统表、系统索引、系统列、插入缓冲位图、事务元数据(事务ID、回滚段)等
• CHAR类型
  • 字符集与字节数CHAR(N)
  • lantin1字符集：定长字符集，每个字符占1字节，最大字节数为N*1
  • utf8字符集(utf8mb3): 变长字符集，每个字符占3个字节，最大字节数为N*3
  • utf8mb4字符集: 变长字符集，每个字符占4个字节，最大字节数为N*4