Mysql：数据类型与Schema的设计_mysql 时区 schema 数据库设计-CSDN博客

本文链接：https://blog.csdn.net/sodawoods/article/details/107803129

1 数据类型选择原则

尽量小。应当尽量选择能够正确存储数据的最小的数据类型，因为更小的数据类型会占用更少的CPU、内存和磁盘，从而带来更高的性能。
尽可能简单。简单的数据类型的操作通常需要更少的CPU周期，从而带来更高的性能。例如整数就比字符串简单。
尽量避免NULL。NULL是列的默认属性，也就是如果定义列时没有指定NOT NULL，默认都是NULL的。查询中包含可为NULL的值，对Mysql来讲更难优化。

2 数据类型

2.1 整数类型

INT，包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别使用8、16、24、32、64位进行存储。
整数类型有可选的UNSIGNED属性，可以将正数的上限提升一倍。
INT(10)指定了宽度，只对例如Mysql命令行客户端的显示有用，对于计算和存储来讲，INT(10)和INT(1)是没有区别的。

2.2 实数类型

FLOAT，使用4个字节存储。
DOUBLE，使用8个字节存储
DECIMAL，最多允许65个数字。会有额外的存储和计算开销，需要精确计算时才应该使用，例如财务数据。数据量大时可以考虑使用BIGINT代替。

2.3 字符串

VARCHAR，用于存储可变长度的字符串，比定长更节省空间。
如果Mysql表使用ROW_FORMAT=FIXED创建，每一行都会使用定长存储，会浪费空间。
VARCHAR使用额外的1个字节（列的最大长度小于等于255字节，例如VARCHAR(10)需要11个字节存储，VARCHAR(1000)需要1002个字节存储）或2字节记录字符串长度（列的最大长度大于255字节）
InnoDB会把过长的VARCHAR转化成BLOB进行存储
CHAR，用于存储定长字符串。
VARBINARY，用于存储可变长度的二进制字符串，存储的是字节码而不是字符
BINARY，用于存储定长的二进制字符串
TEXT，用于存储很大的字符串，采用字符方式存储，包括TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT。TEXT相关类型的不能设默认值
BLOB，用于存储很大的字符串，采用二进制方式存储，包括TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。

Mysql对TEXT和BLOB的排序方式使不同的，它只对每个列最前的max_sort_length字节而不是整个字符串进行排序。如果只需要排序前面的一小部分字符，则可以减小max_sort_length或者使用ORDER BY SUBSTRING(column, length)。
Mysql不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。
如果EXPLAIN执行计划的EXTRA列包含Using Temporary，则说明这个查询使用了临时表。

2.4 枚举类型

ENUM。有时候可以使用枚举类型代替字符串类型，Mysql内部将每个值在列表中的位置保存为整数。
枚举字段按照内部存储的整数而不是定义的字符串进行排序。
枚举不易扩展，使用需谨慎。
CREATE TABLE TEST e ENUM(‘A’, ‘B’, ‘C’) NOT NULL;

2.5 日期与时间类型

DATETIME。保存1001年到9999年，精度是秒，与时区无关，占8个节。
TIMESTAMP。保存从1970年1月1日午夜（格林尼治时间）以来的秒数，范围是1970年到2038年，占4个字节，与时区有关（Mysql服务器、操作系统和客户端连接都有时区设置）。
可以使用BIGINT存储微妙级别的时间戳，例如使用BIGINT存储java中System.currentTimeMillis()的值。也可以使用DOUBLE存储秒后面的小数部分。

2.6 位数据类型

BIT。Mysql把BIT当做是字符串类型，而不是整数类型。
SET。多个值的集合。

3 Schema的设计陷阱

太多的列。Mysql的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，从行缓冲中将编码过的列转换成行数据结构的开销很大。有几千行的宽表是不适合存储在Mysql中的，可以考虑Clickhouse等OLAP引擎。
太多的关联。Mysql中每个表最多能join 61个表。一个经验法则是如果想查询的快速且并发性好，则单个查询join表的数量要小于12。
轻易不要使用ENUM、SET、BIT。

4 范式和反范式

范式化。在范式化的数据库中，每个事实数据出现且只出现一次。
- 优点。更新操作少、更新时修改的数据少、范式化的表通常更小。
- 缺点。因为字段没有冗余，因此通常需要进行join操作。
反范式化。在反范式化的数据库中，字段是冗余的，可能存储在多个表中。
- 优点。因为所有的数据都在一张表中，因此可以很好的避免关联。
- 缺点。冗余、更新时改动的行更多。

通常会混用范式化和反范式化的表。