一:mysql 基本查询命令
// MySQL 数据库的文件存放所在的目录
SHOW VARIABLES LIKE 'datadir';
//开启后:存储的数据、索引等信息单独存储在一个独占表空间
SHOW VARIABLES LIKE 'innodb_file_per_table';
//数据库版本
show variables like '%version%'
//表的行模式
show variables like "innodb_default_row_format";
//表的索引长度限制开关
show variables like "innodb_large_prefix";
ON :Innodb表的行记录格式是Dynamic或Compressed的前提下,单列索引长度上限扩展到3072个字节
OFF:Innodb表的单例索引长度最多为767个字节,索引长度超出后,主键索引会创建失败,辅助索引会被截断成为前缀索引
Tips:
字段varchar(N)中的N是指字符的长度,不是字节数,需要结合字符集换算得出字节数。例如:utf8字符集一个字符占3个字节,若单列索引限制767,则最大可以支持N=255
二:数据库文件认知
![](https://i-blog.csdnimg.cn/blog_migrate/98cd4cf082c97bc9455e6851cec08aae.png)
db.opt:用来存储当前数据库的默认字符集和字符校验规则
表.frm:表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件,该文件是用来保存每个表的元数据信息的,主要包含表结构定义
表.ibd:表数据会保存在这个文件。表数据既可以存在共享表空间文件(文件名:ibdata1)里,也可以存放在独占表空间文件(文件名:表名字.idb)。这个行为是由参数 innodb_file_per_table 控制的,若设置了参数 innodb_file_per_table 为 1,则会将存储的数据、索引等信息单独存储在一个独占表空间,从 MySQL 5.6.6 版本开始,它的默认值就是 1 了,因此从这个版本之后, MySQL 中每一张表的数据都存放在一个独立的 .idb 文件-->通过命令:SHOW VARIABLES LIKE 'innodb_file_per_table';可以查看是否开启
三:行格式了解
InnoDB存储引擎目前共支持四种行格式,如下表:
行格式 | 紧凑存储 | 增强的可变长度列存储 | 大索引键前缀支持 | 支持压缩 | 支持的表空间类型 | 所需的文件格式 |
REDUNDANT | 否 | 否 | 否 | 否 | system,table-per-table,general | Antelope or Barracuda |
COMPACT | 是 | 否 | 否 | 否 | system,table-per-table,general | Antelope or Barracuda |
DYNAMIC | 是 | 是 | 是 | 否 | system,table-per-table,general | Barracuda |
COMPRESSED | 是 | 是 | 是 | 是 | file-per-table,general | Barracuda |
COMPACT和DYNAMIC和COMPRESSED 关于数据溢出时区别:
MySQL 中磁盘和内存交互的基本单位是页,一个页的大小一般是 16KB,也就是 16384字节,而一个 varchar(n) 类型的列最多可以存储 65532字节,一些大对象如 TEXT、BLOB 可能存储更多的数据,这时一个页可能就存不了一条记录。这个时候就会发生行溢出,多的数据就会存到另外的「溢出页」中
COMPACT:
当发生行溢出时,在记录的真实数据处只会保存该列的一部分数据,而把剩余的数据放在「溢出页」中,然后真实数据处用 20 字节存储指向溢出页的地址,从而可以找到剩余数据所在的页
![](https://i-blog.csdnimg.cn/blog_migrate/a667b81c0525e81b33335f2bcdf9740e.png)
Compressed 和 Dynamic 这两个行格式和 Compact 非常类似,主要的区别在于处理行溢出数据时有些区别。
这两种格式采用完全的行溢出方式,记录的真实数据处不会存储该列的一部分数据,只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中,看起来就像下面这样:
![](https://i-blog.csdnimg.cn/blog_migrate/70bb61aafd4effda745d3de1b85f2bbd.png)
四:知识获取
MySQL 的 NULL 值是怎么存放的?
MySQL 的 Compact 行格式中会用「NULL值列表」来标记值为 NULL 的列,NULL 值并不会存储在行格式中的真实数据部分。
NULL值列表会占用 1 字节空间,当表中所有字段都定义成 NOT NULL,行格式中就不会有 NULL值列表,这样可节省 1 字节的空间
MySQL 怎么知道 varchar(n) 实际占用数据的大小?
MySQL 的 Compact 行格式中会用「变长字段长度列表」存储变长字段实际占用的数据大小
varchar(n) 中 n 最大取值为多少?
一行记录最大能存储 65535 字节的数据,但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。
如果一张表只有一个 varchar(n) 字段,且允许为 NULL,字符集为 ascii。varchar(n) 中 n 最大取值为 65532。
计算公式:65535 - 变长字段字节数列表所占用的字节数 - NULL值列表所占用的字节数 = 65535 - 2 - 1 = 65532
行溢出后,MySQL 是怎么处理的?行溢出后,MySQL 是怎么处理的?
如果一个数据页存不了一条记录,InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。
Compact 行格式针对行溢出的处理是这样的:当发生行溢出时,在记录的真实数据处只会保存该列的一部分数据,而把剩余的数据放在「溢出页」中,然后真实数据处用 20 字节存储指向溢出页的地址,从而可以找到剩余数据所在的页。
Compressed 和 Dynamic 这两种格式采用完全的行溢出方式,记录的真实数据处不会存储该列的一部分数据,只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中