mysql 表结构定义 mysql数据库表结构

1.索引组织表

数据的存储是按照主键顺序来的；在InnoDB，每个表都有主键 Primary Key;若没有显示设置，会默认设置主键

• 唯一索引为主键
• 上述不满足 创建一个6byte 的指针

2.InnoDB逻辑存储

2.1 表空间

tablespace 前面说过了如果配置了innodb_file_per_table=ON 就是一张表一个表空间

但是呢 这样每张表的表空间存储的数据有：表数据，索引，insert buffer bitmap

但是，张表的其他的信息，比如回滚信息，插入缓存索引页，二次写缓存还是会存在共享表空间ibdata1;

ibdata1初始大小是58M;后续不够会变大，但是后续不需要这么大的时候不会变小；

2.2 段

数据段 Leaf node segment

索引段 Non-Leaf node segment

回滚段

段是由存储引擎管理

2.3 区

Extent 每次申请磁盘会申请4-5个区 ；一个区1MB对应64个page (16K) ；可以配置page 的大小 ，但是区始终是1MB;

创建表后 表空间为96K?

三个段 一个段 32kB 当32KB 使用完成后才回去申请连续的64个页；节约磁盘开销；

2.4 页

Page 也叫块 block默认是16KB 大小

2.5 行

InnoDB是基于行数据存储的 row-oriented ;存放16Kb/2 - 200 = 7992行数据

3.InnoDB行记录格式

InnoDB存储引擎提供 Compact 和Redundant 两种格式来记录数据；

3.1 Compact

数据存储的格式为

变长字段长度列表：就是一行数据从后面数第一个列是变长的,比如是varchar(50) 类型，然后数据存的是‘aaa’ 就对应00000011

然后倒数第二个 变长的如果是‘eeeee’ 对应 00000101； 以此类推 有几个变长字段，变长字段长度列表就在后面加几个；

null 标志位 一个字节 1 表示行数据内有null；也就是说我的表的数据列设置了可以空的超过8时 ；会多出一个字节来表示null 标志位；

如下表；null 的标志位为 0x035A ==> 0000 0011 0101 1010 ===>反过来就是 下面第三行数据

t1	t2	t3	t4	t5	t6	t7	t8	t9	t10
a	NULL	b	NULL	NULL	c	NULL	d	NULL	NULL
0	1	0	1	1	0	1	0	1	1

记录头信息 5byte

还有事务id 列 6byte

回滚指针列 7byte

如果没有定义主键 还有rowid 列：6byte

测试：

create table test
(
    t1 varchar(10),
    t2 varchar(10),
    t3 varchar(10),
    t4 char(10)
) engine = innodb
  row_format = compact;

数据

t1	t2	t3	t4	变长标志位	null标志位
a	bb	ccc	NULL	03,02,01	00001000:08
a	NULL	bb	NULL	02,01	00001010:0A
NULL	ccc	ddd	NULL	03,03	00001001:09

对应字节文件

解读：

第一行数据对应

03 02 01 ：变长字段长度列表

08 ：null 标志位

00 00 10 00 21 ：Header

00 00 00 00 02 09 :rowID 没有指定主键的roeid

00 00 00 34 db cd : transcationID 事务id

be 00 00 01 40 01 10 : roll pointer 回滚指针

61 :a

62 62 ：bb

63 63 63 :ccc

null 不占用存储部分字节 本行解析结束 其他两行差不多；

3.2 Redundant

兼容MySQL5.0之前的版本；

不同点：变长的使用了偏移量

CHAR 占用固定长度字符；

varchar 不占用

3.3 行溢出数据

MySQL数据库说可以存放 65535 字节，测试下来65535 不行，实际值要偏小一点为65532

###失败
easytaxi> create table charTest
          (
              t1 varchar(65535))
[2021-09-30 10:45:58] [42000][1118] Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

###成功
easytaxi> create table charTest
          (
              t1 varchar(65532))
[2021-09-30 10:46:21] completed in 221 ms

修改编码

easytaxi> create table charTest1
          (
              t1 varchar(65532)
          ) charset =GBK engine=innodb
[2021-09-30 10:51:07] [42000][1074] Column length too big for column 't1' (max = 32767); use BLOB or TEXT instead
[2021-09-30 10:51:07] [42000][1074] Column length too big for column 't1' (max = 32767); use BLOB or TEXT instead
easytaxi> create table charTest1
          (
              t1 varchar(65532)
          ) charset =utf8 engine=innodb
[2021-09-30 10:51:34] [42000][1074] Column length too big for column 't1' (max = 21845); use BLOB or TEXT instead
[2021-09-30 10:51:34] [42000][1074] Column length too big for column 't1' (max = 21845); use BLOB or TEXT instead

可以看出varchar(N) N 指的是字符 不是字节；

easytaxi> create table charTest2
          (
              t1 varchar(65500),
                  t2 varchar(65500)
          ) engine=innodb
[2021-09-30 10:58:17] [42000][1118] Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
[2021-09-30 10:58:17] [42000][1118] Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
easytaxi> create table charTest2
          (
              t1 varchar(65500),
                  t2 varchar(2)
          ) engine=innodb
[2021-09-30 10:58:24] completed in 201 ms

而且65535 长度是所有varchar列加起来不能成超过；

思考 一个页数据最大就16384字节 怎么可以存放65532字节呢？

一般情况下 ，数据页都是在b_tree 的叶子节点上面，但是呢如果有行数据溢出超过了页的大小，那么溢出数据就会存放在表空间的Uncompress BOLB 页里面

又引入问题：溢出数据量如何控制；varchar 大小有个最大值 8089 ；小于这个都是存放在数据页中；

如果存放的数据导致行溢出，数据也最多只会存放768byte

3.4 Compressed 和Dynamic 行记录格式

Antelope :Compact 和Redundent

Barracuda: Compressed 和Dynamic :BLOB 数据类型相比较就是完全溢出的方案 原本列报错的数据为20z字节的指针 实际的数据存储与OFF page 里面；Compressed 的类一个功能就是数据会zlib压缩

3.5 CHAR 存储结构

编码	字符	字节
latin1	1	1
GBK	1	2
UTF-8	1	3

对于多字节的字符编码 UTF-8 GBK； char存储就是动态的字节大小了；比如utf-8 如果存字母 10字节 汉字的话就是30个字节了，char这个时候就算是变长字符串了；

实例char(5) gbk 20 是补得空格

编码	字节
aaaaa	61 61 61 61 61
我们我们我	c3 d2 c3 c7 c3 d2 c3 c7 c3 d2
aa	61 61 20 20 20
我们	c3 d2 c3 c7 20 20 20 20 20 20

char对满足长度的会补0x20 其他的 对于多字符编码的存储 char 和varchar 没什么区别了，char 也会被认为是变长的；

4.InnoDB数据页格式

• File Header :38byte
• Page Header:56byte
• infimun supremun Records
• User Records
• Free Space
• Page Directory
• File Trailer: 8byte

4.1 File Header

4.2 Page Header

4.3 infimun supremun Records

infimun Records 和supremun Records 是两个虚拟的行记录

4.4 User Records Free Space

User Records 实际行存储的内容

Free Space 空闲空间

4.5 Page Directory

4.3 可以看到一个页里面的数据为

infimun 记录1 记录 2 … 记录n supremum

Page Directory 就是相当于存放了这些记录的相对位置 一个个的slot;n_owned 中的n是slot 的槽位内部存储记录的个数 ；会随着数据插入solt 会进行分裂

开头的slot 存储infimun n=1

中间 的 n = [4,8]

最后supremum n = [1,8]

B+ tree 索引本省并不能找到具体的一条记录，只是能找到记录对应的数据页；找到页后加载到内存，然后再通过Page Directory 二分查找，内存中运行很快，故时间很少；

4.6 File Trailer

主要是检测数据页完整性 这里面的字段fil_page_end_lsn ；前面4byte=checksum 后4byte 和header 的fil_page_lsn 和header里面的file_page_apace_or_checksum 和 fil_page_lsn 比较；默认配置每次读取页的时候都需要对比；

4.7 示例分析

create table t(
    a int unsigned not null primary key  auto_increment,
    b char(10)
)engine =innodb charset =UTF8;

a	b
1	aaaaaaaaaa
2	bbbbbbbbbb
3	cccccccccc
4	dddddddddd
5	eeeeeeeeee
6	ffffffffff
7	gggggggggg
8	hhhhhhhhhh
9	iiiiiiiiii
10	jjjjjjjjjj

初始文件大小t.idb 为96K ;InnoDB最小管理单元是16K ，可以得到6个页；开始位置分别是 地址 00000000；00004000；00008000；0000c000;

00010000,00014000;根据头部偏移量24 字节后 为0x45bf 为数据页定位到数据页开始为0000c000;

数字	含义
41 71 8b 28	FIL_PAGE_SPACE_CHECKSUM	4：数据页checksum	file header
00 00 00 03	FIL_PAGE_OFFSET	4：页的偏移量	f
ff ff ff ff	FIL_PAGE_PREV	4：没有上一页	f
ff ff ff ff	FIL_PAGE_NEXT	4：没有下一页	f
00 00 00 09 68 8d ce 18	FIL_PAGE_LSN	8：LSN 日志序列	f
45 bf	FIL_PAGE_TYPE	2：代表是数据页	f
00 00 00 00 00 00 00 00		8：无效	f
00 00 00 57	SPACE	4：space id	f
41 71 8b 28	file trailer前四个字节 checksum函数比较	4：	File Trailer
68 8d ce 18	file trailer后四个字节 Lsn 相等	4：	File Trailer
00 03	slot 数量	2：	Page header
70 00 e5 00 63 41	3个槽 一个2字节	2*3：	p
01 cc	空闲开始偏移量 0x0000c000+0x01cc 0x0000c1cc 之后都是空闲	2：确实是	p
80 0c	page_n_heap	2：c:12 条 减去infimun和supremun为10条	p
00 00	page_free	2：没有删除 0	p
00 00	page_garbage:	2：没有删除 0	p
01 b1	page_last_insert	没有删除 0	p
00 02	page_driection	2：连续插入 右 02	p
00 09	page_n_driection	2：连续插入条目	p
00 0a	page_n_recs	2：页中记录
00 00 00 00 00 00 00 00	page_max_trx_id	8：事务id
00 00	page_level	2:叶子节点0
00 00 00 00 00 00 00 fd	page_index_id	8:索引id
00 00 00 57 00 00 00 02 00 f2 00	page_btr_sge_leaf:	10：非页节点segment header
00 02 00 1c 69 6e 66 69 6d 75	page_btr_sge_top	10：段segment header

5.Named File Formats 机制

略略略。。。。。

6.约束

6.1 完整性约束

1. 实体完整性: 主键或者是唯一约束
2. 域完整性 : 字段的约束 合适的类型，外键，触发器，default

6.2 约束的创建和查找

在创建表的时候创建 alter table 创建

6.3 约束和索引的区别

约束是一个逻辑概念，索引是数据结构，存储在物理磁盘

6.4 对错误数据的约束

数据库允许非法的或者不准确的数据插入或者更新；

6.5 Enum 和set 的约束

create table a(
   id int  primary key auto_increment,
   sex ENUM('nan','nv')
)

6.6 触发器和约束

用触发器实现约束不建议；

6.7 外键约束

也不建议；数据库更新风暴

7.视图

可以看做是一个虚表 由一个查询SQL 定义

7.1 视图的作用

虚拟表 查询方便 ；不需要关系原来的表的定义和结构

7.2 物化视图

MySQL本身不支持 触发器自己去实现也不建议

8.分区表

8.1 分区概念

功能不是在存储引擎层面完成的：一个表或者索引分为几个更小的部分；逻辑上看还是一张表，或者一个索引，但是在物理存储的层面可能是分成了很多的物理单元，也叫物理分区，一个分区页可以作为独立的对象处理；

只支持水平分区：不同行的记录分到多个物理文件中；不支持垂直分区；

支持局部分区索引：一个分区中数据和索引在一起； 不支持全局分区

8.2 分区类型

1. RANGE 分区

没有命中分区会报错

测试 1 :根据id 大小分区

create table b(
    id int  primary key auto_increment,
    sex ENUM('nan','nv')
)engine =innodb charset =UTF8
    partition by range(id)(
        partition po values less than(10),
            partition p1 values less than(20)
        )
        
-- b#p#p1.ibd
-- b#p#p0.ibd

每个分区都有对应的ibd 文件；

测试2 按照时间分区

删除数据可以转换为直接删除时间对应的分区

注意：1. 要根据分区特性来编写最优的SQL 语句；

2.优化器对应部分函数有优化选择 分区函数优先使用优化过的函数分区 比如 to_days(),year() 等；

2.List 分区

没有命中分区会报错； 分区值是离散的 不想是renge 一样是会有连续的

create table  c(
    id int  primary key auto_increment,
    sex ENUM('nan','nv')
)engine =innodb charset =UTF8
    partition by List(id)(
        partition po values  in (1,3,4,6),
            partition p1 values in (5,2,7)
        )

3.Hash 分区

数据均匀分布到预先定义的分区中，保证数据量大体一致；by hash ( expr) expr为一个整数列或者运算后的为整数的表达式 partitions 4; mysql 会自动的把数据发到4个分区

create table d
(
    id  int primary key auto_increment,
    sex ENUM ('nan','nv')
) engine = innodb
  charset = UTF8
    partition by hash ( id )
partitions 4;

4.key 分区

和hash 差不多 只是 key 的里面不能时函数了 只能是列了 ，mysql对应有响应的算法自己来计算；

create table d
(
    id  int primary key auto_increment,
    sex ENUM ('nan','nv')
) engine = innodb
  charset = UTF8
    partition by key ( id )
partitions 4;

5.colnum分区

支持所有整型

日期类型

字符串类型

指定多个列分区

8.3 子分区

分区上面在分区 也叫复合服务（在range 和list 上面进行 hash 或key）

8.4 分区处理NULL

插入null 分区函数无法判断

RANGE 分区插入null 放入左边分区

List 分区null 需要指定分区插入

Hash key分区函数会放回0

8.5分区性能

考虑B+tree 的层级的影响在分区 一般千万级别的数和几十万数据 树的层级都是三层，分区并不能带来很好的查询速度提升；

8.6 表和分区交换数据

分区或者子分区的数据和另一个非分区表中的数据进行交换；

alter table …exchange partition