MySQL 数据库设计的“奥秘”
2 MySQL 数据库设计的“奥秘”
【主题】逻辑设计：数据类型与 Schema
所谓“万丈高楼平地起”，一个稳固的建筑离不开扎实的基础。同样，良好的的「逻辑设计」和「物理设计」是数据库高性能的基石。
本文的主要内容总结了一张图，你可以点击查看。
我们需要根据系统需要执行的 SQL 语句设计 schema，这往往权衡各种因素。
[idea]瞧！权衡和取舍不仅仅在系统设计、方案设计时处处体现其价值，在数据库表设计也同样如此。
举个例子，反范式设计可以加快某些类型查询，但可能会使另一些类型查询变慢。如增加汇总表可以优化查询，但这些表维护成本可能很高。
首先，让我们一起来看下 MySQL 的数据类型，这是我们设计数据表前需要事先掌握的。
如何选择优化的数据类型？
MySQL 支持的数据类型很多，为了做出更好的选择，我们来看几个简单的原则：
1.更小的通常更好
尽量使用可以正确存储数据的最小数据类型。
优点：
- 占用更少的磁盘、内存和 CPU 缓存
- 处理时需要 CPU 周期更少
缺点：
- 但是要确保没有低估存储值范围
2.简单就好
简单数据类型需要更少的 CPU 周期。
比如整型比字符串代价低（字符串有字符集和校对规则）。
3.尽量避免 NULL
NULL 列对 MySQL 更难优化，因为为 NULL 的列使得索引、索引统计和值比较都更复杂；并且占用更多存储空间。
todo 为什么占用更多存储空间？
推荐步骤
步骤1：确定合适的大类型
如数字、字符串、时间等。
步骤2：选择具体类型
同类型下只是存储的长度、范围、允许的精度、需要的物理空间（磁盘、内存）不同。
数据类型概览
1.整数类型
有两种类型的数字：「整数」和「实数」。
整数有很多种，如：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别使用 8，16，24，32，64 位存储空间。
-
有可选的 UNSIGNED 属性，表示不允许负值。
-
还可以指定宽度
例如 INT(11)，它不会限制值的合法范围，只是规定了 MySQL 的一些客户端显示字符的个数。
INT(1) 和 INT(11) 是相同的。
2.实数类型
即带有小数部分的数字。MySQL 既支持精确类型，也支持不精确类型。
- 可以指定需要的精度
FLOAT 和 DOUBLE 类型使用浮点运算进行近似计算。
DECIMAL 类型用于存储精确的小数。使用场景：尽量只有对小数需要精确计算时才使用，因为需要额外的存储空间和计算开销。
数据量比较大时，可以使用 BIGINT 代替 DECIMAL，对应单位乘以相应倍数即可。
3.字符串类型
VACHAR 和 CHAR 类型
VACHAR
-
比定长类型更节省空间，因为它只使用必要的空间（如字符串越短使用空间越少）。
-
需要使用 1(<=255) 或 2(>255) 个额外字节记录字符串长度。
-
由于变长，Update 可能使行变得比原来更长，容易产生碎片
如 InnoDB 需要页分裂使得行可以放到页内。
适合 VACHAR 的场景：
- 最大长度比平均长度大得多
- 列的更新很少
- 使用了像 UTF-8 这样的复杂字符集
CHAR
- 定长，会根据需要填充空格
适合 CHAR 的场景：
- 存储很短的字符串或所有值接近同一个长度
- 经常变更数据，不易产生碎片（因为定长不会页分裂）
建议：最好的策略是只分配真正需要的空间。
todo Vachar(5) 和 Vachar(200) 又什么区别？空间开销一样吧？
BLOB 和 TEXT 类型
适合场景：存储很大的数据，前者使用二进制，后者使用字符存储。
特殊之处：MySQL 把 BLOB 和 TEXT 值当作独立对象处理，值太大时会使用专门的“外部”存储区域存储，在行内存储指针指向外部实际值。
[idea]这个设计在程序开发中也可以使用，有时一些大属性占用存储较多但使用频率较低，可以单独存储在其他地方，通过指针进行引用，从而使得该对象更小。
不能将 BLOB 和 TEXT 列全部长度索引，不能用这些索引消除排序。
如果 Explain 执行计划的 Extra 列包含“Using temporary”，说明这个查询使用了「隐式临时表」。
使用枚举（ENUM）代替字符串类型
优点：存储枚举时非常紧凑，节约空间
MySQL 内部将每个值在列表中位置保存为整数，在表的 .frm 文件中保存“数字-字符串”映射关系的“查找表”。
所以，使用数字作为枚举常量，这种双重性容易导致混乱。建议尽量避免这么做。
例如 ENUM(‘1′,’2’,3”)
⚠️枚举字段是按照内部存储整数而不是定义的字符串数字排序。
缺点：
- 字符串列表是固定的，如果未来要改变枚举值则不适合
- 枚举值转为整数值保存有开销
建议通用设计实践：在“查找表”时采用整数主键而避免采用字符串的值进行关联，因为性能更好。
