Schema设计与管理

schema 数据类型优化原则

越小的约好
- 占用的磁盘、内存、cpu缓存的空间少
- 但要确保不要低估值的范围
简单的数据类型
- 简单的数据类型的操作需要更少的cpu执行周期
- 整形笔字符串更好，是因为字符集和排序规则字符串更加的复杂
- 尽可能使用内置的日期类型
- 存储IP地址尽可能使用整形
尽量避免存储NULl类型
- null是索引和索引统计以及值的比较都更加的复杂
- 但是设置为not null也不会带来很大的性能提升，具体根据业务情况设定列是否包含空置

有两种类型的数字：整数和实数（带有小数的数字）。

存储整数的数据类型有 TINYINT、SMALLINT、MEDIUMINT、INT或BIGINT。

整数类型还可以设置无符号选项 UNSIGNED ，可以使正书的大小上限提高一倍，例如，TINYINT UNSIGNED可以存储的值的范围是0～255，而TINYINT的值的存储范围是-128～127。无符号和有符号性能一致，可以根据具体业务来选择。

MySQL还可以为整形提供宽度，例如我们常看到的INT(11)，这种形式不会限制值的大小范围只是在一些交互工具中显示值的字符的个数。对于存储和计算来说，INT（1）和INT（20）是相同的。

实数是带小数的数字，FLOAT 和 DOUBLE 支持使用标准的浮点运行进行近似运算；FLOAT 占用4字节的存储空间，DOUBLE 占用8字节的存储空间。

如果需要对小数进准计算应使用 DECIMAL 来存储实数。例如货币信息等。

也可以使用BIGINT来代替大数据量的DECIMAL，例如根据货币的小数的位数乘以相应的倍数。假设要存储财务数据需要精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

VARCHAR存储可变长度的字符串，比使用固定长度类型的字符串更加的节省空间，需要注意的是他需要额外的1到2字节存储字符串的长度。VARCHAR 节省了空间性能也有所提升，但是可变长度的字符串当长度增长时，InnoDb需要分割页面来容纳新行。

字符串长度定义的不是字节数，是字符数。多字节字符集可能需要多个字节来存储1个字符。

使用VARCHAR的场景：

CHAR是固定长度的，存储CHAR值时会删除尾部的空格，需要进行比较会使用空格填充。

使用CHAR的场景：

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。它们分别属于两组不同的数据类型家族：

BLOB 是 SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。

MySQL把每个BLOB和TEXT值当作一个具有自己标识的对象来处理。存储引擎通常会专门存储它们。当BLOB和TEXT值太大时，InnoDB会使用独立的“外部”存储区域，此时每个值在行内需要1～4字节的存储空间，然后在外部存储区域需要足够的空间来存储实际的值。

BLOB和TEXT的唯一区别就是BLOB没有排序规则和字符集而TEXT有。

MySQL只对BLOB和TEXT列的最前max_sort_length字节而不是整个字符串做排序。也不能使用索引进行排序。

以前经常将图像数据存储为BLOB，当数据量过大时，就会导致某方面性能下降，所以在存储图像时尽量将图像存储到专门的对象存储中去，只在数据库中存储对应的地址或者文件名。

有时还可以使用枚举对象来代替字符串，MySQL会根据列表值的数量压缩到1或者2字节中。在内部会将每个值在列表中的位置保存为整数。所以存储枚举时应尽量使用字符串而不是数字字符串。另一个需要注意的是：枚举的值排序不是根据枚举字符串排序的，是根据内部的整数值排序的。

DATETIME 可以存储很大的时间，从1000年到9999年，精度为一微秒。它以YYYYMMDDHHMMSS格式存储压缩成整数的日期和时间，且与时区无关。这需要8字节的存储空间。默认情况下，MySQL以可排序、无歧义的格式显示DATETIME值，例如，2023-08-18 15：39：08。

TIMESTAMP 类型存储自1970年1月1日格林尼治标准时间（GMT）午夜以来经过的秒数——与UNIX时间戳相同。TIMESTAMP只使用4字节的存储空间，所以它的范围比DATETIME小得多：只能表示从1970年到2038年1月19日。

MySQL提供FROM_UNIXTIME()函数来将UNIX时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数将日期转换为UNIX时间戳。

这两种类型从技术上来说都是字符串类型。

BIT 可以存储一个或者多个true/false值，BIT(1)代表一位的字段，BIT(3)代表2为的字段，MySQL在处理时会将BIT视为字符串类型，而不是数字类型。当检索BIT（1）的值时，结果是一个包含二进制值0或1的字符串，而不是ASCII码的“0”或“1”。最好避免使用这种类型。

SET 集合存储类型，可以将多列组合成一列，这在MySQL内部是以一组打包的位的集合来表示的。有对应的FIND_IN_SET()和FIELD()等函数，使其易于在查询中使用。

在文章的测试中 sql模式的性能总是大于json模式的性能，当数据量更大时，差距会更加的明显。所以我们在选择使用json模式时，取决于在数据库中存储json的便捷性是否大于性能。

当我们创建一个用户表时，我们通常会为每个用户分配一个唯一值或者唯一标识符，标识符列的类型选择更为重要。因为他通常扮演了与其他列进行比较、查找的索引、外键的功能。

关于为标识符选择类型的一些建议：

整数类型：通常是最佳选择，因为速度快、可以递增，等等。
ENUM和SET类型：不建议。
字符串类型：也不建议，因为既消耗空间，速度也比较慢。
- 像是 MD5()和UUID()生成的随机字符，也不建议，因为当使用这些字符串时索引的值会随机的插入到某个部分，使得查询很慢，也会导致页的分裂，磁盘随机访问，聚集索引产生聚集索引随机碎片。
如果存储通用唯一标识符（UUID）值，则应该删除破折号，或者更好的做法是，使用UNHEX()函数将UUID值转换为16字节的数字，并将其存储在一个BINARY（16）列中。可以使用HEX()函数以十六进制格式检索值。