MySQL-schema设计与管理

选择数据库优化的数据类型

常见原则：

更小的通用
简单为好
尽量避免存储NULL

MySQL为了兼容性支持很多别名，例如， INTEGER（映射到INT）、BOOL（映射到TINYINT）和NUMERIC（映射到DECIMAL）。它们都只是别名。这些别名可能令人不解，但不会影响性能。如果建表时采用数据类型的别名，然后用SHOW CREATE TABLE检查，会发现MySQL报告的是基本类型，而不是别名

整数类型

有两种类型的数字：整数（whole number）和实数（real number，带有小数部分的数字）。如果存储整数，可以使用这几种整数类型：TINYINT、 SMALLINT、MEDIUMINT、INT或BIGINT。它们分别使用8、16、24、32 和64位存储空间。可以存储的值的范围从-2（N-1）到2（N-1）-1，其中N是存储空间的位数。
整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如，TINYINT UNSIGNED可以存储的值的范围是0～ 255，而TINYINT的值的存储范围是-128～127。
有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据数据实际范围选择合适的类型。你的选择决定了MySQL在内存和磁盘中保存数据的方式。然而，整数计算通常使用64位的BIGINT整数。（一些聚合函数是例外，它们使用 DECIMAL或DOUBLE进行计算。）
MySQL可以为整数类型指定宽度，例如，INT（11），这对大多数应用毫无意义：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如，MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，INT（1）和INT（20）是相同的

实数类型

实数是带有小数部分的数字。然而，它们不仅适用于带小数的数字，也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型

由于额外的空间需求和计算成本，应该尽量只在对小数进行精确计算时才使用DECIMAL——例如，存储财务数据。但在一些大容量的场景，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据并精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题

字符串类型

VARCHAR和CHAR类型（InnnoDB下的描述）

VARCHAR：
- VARCHAR用于存储可变长度的字符串，是最常见的字符串数据类型。它比固定长度的类型更节省空间，因为它仅使用必要的空间（即，更少的空间用于存储更短的值）
- VARCHAR需要额外使用1或2字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1字节表示，否则使用2字节。假设采用latin1字符集，一个VARCHAR（10）的列需要11字节的存储空间。 VARCHAR（1000）的列则需要1002个字节，因为需要2字节存储长度信息
- VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是可变长度的，在更新时可能会增长，这会导致额外的工作。如果行的增长使得原位置无法容纳更多内容，则处理行为取决于所使用的存储引擎。例如，InnoDB可能需要分割页面来容纳行。其他一些存储引擎也许不在原数据位置更新数据
- 下面这些情况使用VARCHAR是合适的：字符串列的最大长度远大于平均长度；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。 InnoDB更为复杂，它可以将过长的VARCHAR值存储为BLOB
CHAR：
- CHAR是固定长度的：MySQL总是为定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL删除所有尾随空格。如果需要进行比较，值会用空格填充
- CHAR适合存储非常短的字符串，或者适用于所有值的长度都几乎相同的情况。例如，对于用户密码的MD5值，CHAR是一个很好的选择，它们的长度总是相同的。对于经常修改的数据，CHAR也比VARCHAR 更好，因为固定长度的行不容易出现碎片。对于非常短的列，CHAR也比VARCHAR更高效；设计为只保存Y和N的值的CHAR（1）在单字节字符集[1]中只使用1字节，但VARCHAR（1）需要2字节，因为还有一个记录长度的额外字节
BLOB和TEXT类型

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储

BLOB和TEXT家族之间的唯一区别是，BLOB类型存储的是二进制数据，没有排序规则或字符集，但TEXT类型有字符集和排序规则

MySQL不能将BLOB和TEXT数据类型的完整字符串放入索引，也不能使用索引进行排序

日期和时间类型

MySQL中有很多数据类型用以支持各种各样的日期和时间值，比如YEAR 和DATE。MySQL可以存储的最小时间粒度是微秒

DATETIME
- 这种类型可以保存大范围的数值，从1000年到9999年，精度为1微秒。它以YYYYMMDDHHMMSS格式存储压缩成整数的日期和时间，且与时区无关。这需要8字节的存储空间。默认情况下，MySQL以可排序、无歧义的格式显示DATETIME值，例如，2008-01-16 22：37：08。这是ANSI表示日期和时间的标准方式
TIMESTAMP
- 顾名思义，TIMESTAMP类型存储自1970年1月1日格林尼治标准时间（GMT）午夜以来经过的秒数——与UNIX时间戳相同。TIMESTAMP 只使用4字节的存储空间，所以它的范围比DATETIME小得多：只能表示从1970年到2038年1月19日。MySQL提供FROM_UNIXTIME()函数来将UNIX时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数将日期转换为UNIX时间戳

JSON数据类型

选择标识符

一般来说，标识符是引用行及通常使其唯一的方式。例如，如果你有一个关于用户的表，可能希望为每个用户分配一个数字ID或唯一的用户名。此字段可能是主键中的部分或全部

MySQL schema设计中的陷阱

太多的列

MySQL的存储引擎API通过在服务器和存储引擎之间以行缓冲区格式复制行来工作；然后，服务器将缓冲区解码为列。将行缓冲区转换为具有解码列的行数据结构的操作代价是非常高的。InnoDB的行格式总是需要转换的。这种转换的成本取决于列数
太多的联接

所谓的实体属性值（entity attribute value，EAV）设计模式是一种被普遍认为糟糕的设计模式的典型案例，尤其是在MySQL中效果不佳。 MySQL限制每个联接有61个表，而E AV模式设计的数据库需要许多自联接
全能的枚举

在任何具有枚举值类型的数据库中，这可能是一个值得商榷的设计决策，因为它实际上应该是一个整数，会被设计为“字典”或“查找”表的外健
变相的枚举

如果这里真和假两种情况不会同时出现，那么毫无疑问应该使用 ENUM列而不是SET列
NULL不是虚拟值

我们之前说过避免使用NULL的好处，并且建议尽可能考虑其他选择。即使需要在表中存储事实上的“空值”，也可能不需要使用NULL。也许可以使用0、特殊值或空字符串作为代替