MySQL表字段类型，表空间，表信息

1. 表简介

数据库其实就是一个有好多表的数据集合。创建表的时候必须遵守一定的原则：

• 不可以使用中文
• 禁止使用字符型做主键
• 禁止无主键或者唯一索引的情况，不过假如使用navicat等工具，这个工具会进行约束。
• 设计表的时候要考虑字段的长度选择，在选择的时候一定要选用最小的范围类型
  以上都是设计表的规范，一定要在注意，不然后续更改特别麻烦。

2. 表字段类型

2.1 整形

类型	长度（字节）	有符号范围	无符号范围（unsigned）
tinyint	1	（-2^7 ,2^7 -1）	(0,2^8 -1 )
smallint	2	（-2^15 ,2^15 -1）	(0,2^16 -1 )
mediumint	3	（-2^31 ,2^31 -1）	(0,2^32 -1 )
int	4	（-2^63 ,2^63 -1）	(0,2^64 -1 )
bigint	8	（-2^1023 ,2^1023 -1）	(0,2^1024 -1 )

• 我们通常看到int作为主键的使用，为什么这么做呢？
  一般我们要作为主键要考虑业务无关性，而递增保证了查询的效率，复合b+tree的顺序结构。在设计表的时候要考虑数据量，假如不够的话可以使用bigint。
• 创建的时候可以增加无符号标志，这样的话范围更广。例如 int unsinged ,但是作为主键不推荐使用。
  因为查询的时候我们不能保证输入的一定时正数，有可能产生id < 0 的情况。
• int(1)和 int(10) 有区别吗？
  在存储的时候 int占用的空间都是4个字节的空间，所以在存储的角度上讲是没有区别的，但是假如需要位数不足的时候补0，这个时候就有区别了，例如 int(4) zerofill 的 1 就是 0001 ，不足的时候会在前面补足0。

2.2 浮点性

float,double, 分别为4字节，8字节，但是浮点型在计算的时候和java中的float 和double一样不能保证计算的精度，生成环境不推荐使用。
一般情况我们会使用decimal(M,D) 来存储金钱字段，这样也是会转换成浮点运算造成不准确的金额。
M是整数部分，D是小数部分。长度假如 M > D 那么长度为 M + 2 字节，否则为 D + 2字节。

我们可以使用int 来存储金额，这样可以解决精度的问题，可以在运行的时候进行转化。

2.3 时间类型

类型	长度（字节）	范围	格式	作用
year	1	（1901-2155）	YYYY	年份
date	3	（1000-01-01,9999-12-31）	YYYY-MM-DD	日期
time	3	（’-838.59:59’ ,‘838.59:59’ ）	HH:MM:SS	时间
datetime	8/5	（1000-01-01 00:00:00,9999-12-31 23:59:59）	YYYY-MM-DD HH:MM:SS	日期+时间
timestamp	4	（1970-01-01 00:00:00,2038-XX:XX XX:XX:XX）	YYYYMMDDHHMMSS	日期+时间，时间戳

timestamp 可以根据时区选择时间，这个是优势，但是mysql 5.6之后也可以根据时区选择时间。

mysql5.6之前是8个字节，之后是5个字节,也就是说datetime 存储的时候多一个字节，datetime范围选择更大。可以在生产环境选择datetime。

也可以使用int来存储，通过unix_timestamp 和 from_unixtime转换成int数据类型。

select unix_timestamp("2020-04-01 00:00:00");
select from_unixtime("1503986951");

2.4 字符串类型

类型	长度	作用
char	0-255	固定长度字符串
varchar	0-255	变动长度字符串
tinyblob	0-255	短二进制字符串
tinytext	0-255	短文本字符串
blob	0-65535	二进制字符串
text	0-65535	文本字符串
mediumblob	0-16777215	中等二进制字符串
mediumtext	0-16777215	中等文本字符串
longblob	0-4294967295	长二进制字符串
longtext	0-4294967295	长文本字符串

比较大的文本字段不建议和业务存储到一块。
char 和 varchar单位是字节，其他的单位是字符。
示例 varchar(100),常用的字符集是utf8 和 gbk，utf8中 存储的是字节数 100*3+1 = 301
gbk 字节数是 100*2+1 = 201，utf8 的超集utf8mb4字节数 是100*4 +1 = 401。
char(100) 就是100个字符，长度和编码无关。

char 和 varchar 有什么区别呢？

• char的长度固定，varchar长度不固定
• char中存储的字节长度和字符集无关，varchar长度与字符集有关并且会有字节保存长度。
• char 最大255字符，varchar最大65532个字符，varchar的长度和字符集有关。

3. 字符集

常见的字符集是 latin1,gbk,utf8,utf8mb4。
目前latin1已经不再使用，gbk汉字存储的字母或汉字是2个字节，utf8是3个字节，utf8mb4是4个字节。
gbk的通用性不如utf8,utf8mb4是utf8的超集，例如utf8无法存储的表情符号，超集utf8mb4可以存储。

4. 表空间

4.1 表空间介绍

• 系统表空间

我们知道数据是存放在表空间中的，在MySQl5.6之前所有的数据和表结构都存储在系统表空间中。具体的文件名称是 ibdata1,在MySQL初始化之后可以在MySQL文件中查询到，具体在配置的data目录中。以下可以在MySQL5.7的data目录文件中看到。

系统默认的扩充大小是64M。

• 独立表空间

目前的默认都是使用的独立表空间。
设置参数 innodb_file_per_table=1即可。独立表空间即为每个表都有自己的空间，不用一起存储在ibdata1中。存储的内容主要有B+tree 结构，索引和插入缓冲等，除了这些其他仍然存储在系统表空间中。

• 系统表空间和独立表空间的区别

1. 系统表空间数据和文件一起，不易回收和备份。特别是统计分析和日志系统不适合系统表空间。
2. 独立表空间每张表都有自己的表空间，易于数据的转移和回收，单表增长过大可能出现性能问题。每张表都有单独的 .frm 和 .idb文件。
3. 总体来讲独立的表空间的效率和性能比共享表空间高一点，索引目前使用的都是系统表空间。

• 临时表空间

MySQL5.7之后临时表的数据从系统表空间抽离，形成临时表空间。
存储地址为 information_schema 下 innodb_temp_table_info表中。

4.1 表空间划分

• 表、段、区、页

表空间是由段组成，常见有数据段，回滚段，索引段。
段是按照页扩展，每个段由多个区和32个零散的页组成。

区的大小固定是1MB，物理上连续存储。
页的大小默认是16KB,也就是一个区64个页。目前可以向上调整和向下调整大小。
一个页最少有两行数据，用于限定行的范围，
真正记录数据的是行，InnoDB存储引擎的基本单位是行。

• 行文件格式和行记录格式

行文件格式有两种：Antelope 和 Barracuda
行记录格式有四种： compact,dynamic,redundant和compressed。最常用的是compact格式。
对应关系为：Antelope 有行记录格式 compact 和 redundant，Barracuda有行记录格式dynamic 和compressed。
redundant 和 compressed 都不推荐使用。
MySQL5.7 之后默认使用 Barracuda 文件格式 ，dynamic 行记录格式。
dynamic 和 compact 的区别在于大字符串字段 例如 blob 和 text ,溢出的数据放在其他页，dynamic 会放置所有，compact 只会放前768个前缀字节。

5. 表信息查询

统计库表的大小：

-- 单位k
select
	table_schema,
	sum(data_length)/1024/1024 as data_length, 
	sum(index_length)/1024/1024 as index_length,
	sum(data_length+index_length)/1024/1024 as data_index_lengt
from
	 information_schema.tables 
where
	table_schema != 'information_schema' and
	table_schema != 'mysql' 
group by
	table_schema;

结果：

test库中的统计结果：

select
  table_schema,
  sum(data_length)/1024/1024 as data_length,
  sum(index_length)/1024/1024 as index_length,
  sum(data_length+index_length)/1024/1024 as data_index_length
from information_schema.tables
where table_schema = 'test'
group by table_name;

结果：

其中table_schema 是 库的名称，table_name 是表的名称。