MySQL学习笔记(一) Schema设计
目录
MySQL学习笔记(一) Schema设计
Schema 设计
1.1. 数据类型的选择
1.1.1. 整数
1.1.2. 实数
1.1.3. 字符串类型
1.1.4. 日期和时间类型
1.2. MySQL Schema 设计中的陷阱
1.3. 范式和反范式
1.4. 缓存表和汇总表
1.4.1. 物化视图
1.5. 总结
Schema 设计
良好的逻辑设计和物理设计是高性能的基石。
1.1. 数据类型的选择
- 更小的通常更好
- 简单就好
- 尽量避免 Null
1.1.1. 整数
整数类型： TINYINT 、 SMALLINT 、 MEDIUMINT 、 INT 、 BIGINT；分别使用 8、16、24、32、64 位存储空间。存储的范围从 -2(N-1) 到 2(N-1)-1。
整数类型有可选的 UNSIGNED，表示不允许负值。有符号和无符号类型使用相同的存储空间，并具有相同的性能。
MySQL 可以为整数类型指定宽度，例如 INT(11)，这实际没有意义：它不会限制值的合法范围。对于存储和计算来说， INT(1) 和 INT(20) 是相同的。
1.1.2. 实数
DECIMAL 类型用于存储精确的小数。CPU 不支持对 DECIMAL 的直接计算。
CPU 直接支持原生浮点计算，所以浮点运算明显更快。
MySQL 5.0 和更高版本中的 DECIMAL 类型运行最多 65 个数字。
浮点类型在存储同样范围的值时，通常比 DECIMAL 使用更少的空间。 FLOAT 使用 4 个字节存储； DOUBLE 占用 8 个字节。
MySQL 使用 DOUBLE 作为内部浮点计算的类型。
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 DECIMAL 。
在数据量比较大的时候，可以考虑使用 BIGINT 代替 DECIMAL ，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。
1.1.3. 字符串类型
VARCHAR
VARCHAR用于存储可变长字符串，比定长类型更节省空间。VARCHAR 需要使用 1 或 2个额外字节记录字符串的长度：如果列的最大长度小于或者等于255字节，则只使用1个字节表示，否则使用 2 个字节。
VARCHAR 类型的最大长度限制到底是多少呢？
InnoDB 更灵活，可以把过长的 VARCHAR 存储为 BLOB。
CHAR
定长，根据定义分配足够的空间。当存储 CHAR 值时，MySQL 会删除所有的末尾空格。CHAR 值会根据需要采用空格进行填充以方便比较。
CHAR适合存储很短的字符串，或者所有值都接近同一个长度，比如密码的 MD5 值。- 对于经常变更的数据，
CHAR也比VARCHAR更好，定长不容易产生碎片。- 非常短的列，
CHAR比VARCHAR在存储空间上更有效率
BLOB和TEXT 类型
BLOB 和 TEXT 都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符串方式存储。
字符串类型： TINYTEXT、 SMALLTEXT、 TEXT、 MEDIUMTEXT、 LONGTEXT 二进制类型： TINYBLOB、 SMALLBLOB、 BLOB、 MEDIUMBLOB、 LONGBLOB
BLOB 是 SMALLBLOB 的同义词； TEXT 是 SMALLTEXT 的同义词。
MySQL 把每个 BLOB 和 TEXT 值当做一个独立的对象处理。InnoDB 会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要 1 ~ 4 个字节存储一个指针，然后在外部存储区域存储实际的值。
BLOB类型存储的是二进制，没有排序规则或字符集
TEXT类型存储的是字符串，有字符集和排序规则
1.1.4. 日期和时间类型
MySQL 能存储的最小时间粒度为秒，也可以使用微秒级的粒度进行临时运算。通常应该尽量使用 TIMESTAMP ，因为它比 DATETIME 空间效率更高
DATETIME保存大范围的值，从 1001 年到 9999 年，精度为秒。把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中，与时区无关，使用 8 个字节的存储空间。
TIMESTAMP保存从 1970 年 1 月 1 日午夜以来的秒数，和 UNIX 时间戳相同。
TIMESTAMP只使用 4 个字节的存储空间，范围是从 1970 年到 2038 年。显示的值也依赖于时区
1.2. MySQL Schema 设计中的陷阱
太多的列
MySQL 的存储引擎 API 工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将解码过的列转换成行数据结构的操作代价是非常高的。 MyISAM 定长行结构正好匹配，不需要转换。MyISAM 的变长行结构和 InnoDB 的行结构则总是需要转换。转换的代价依赖于列的数量。
太多的关联
MySQL 限制了每个关联操作最多只能有 61 张表。一个粗略的经验法则，如果希望查询执行得快速且并发性好，单个查询最好在 12 个表以内做关联。
全能的枚举
注意防止过度使用枚举。修改枚举，就需要 ALTER TABLE
非此发明的 NULL
建议不要存 NULL。但是不要走极端。当确实需要表示未知值时也不要害怕使用 NULL。处理 NULL 确实不容易，但有时候会比它的替代方案更好。
1.3. 范式和反范式
第一范式
符合1NF的关系中的每个属性都不可再分
范式化通常带来的好处：
- 范式化的更新操作通常比反范式化要快。
- 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
- 范式化的表通常更小，可以更好地存放在内存里，所以执行操作会更快。
- 很少有多余的数据意味着检索列表数据时，更少需要
DISTINCT或者GROUP BY语句。
范式化设计的 Schema 的缺点是通常需要关联。
反范式的优缺点
- 反范式化的 Schema 因为所有数据都在一张表中，可以很好地避免关联。
- 单独的表也能使用更有效的索引策略。
1.4. 缓存表和汇总表
有时提升性能最好的方法是在同一张表中保存衍生的冗余数据；有时也需要创建一张完全独立的汇总表或缓存表。
缓存表表示存储那些可以比较简单地从 Schema 其他表获取数据的表。
汇总表表示保存的是使用 GROUP BY 语句聚合数据的表。
一个有用的技巧是对缓存表使用不同的存储引擎。例如：主表用 InnoDB，使用 MyISAM 作为缓存表的引擎将会得到更小的索引占用空间，并且可以做全文检索。
1.4.1. 物化视图
物化视图是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。MySQL 并不原生支持物化视图。
1.5. 总结
- 尽量避免过度设计；
- 使用小而简单的合适数据类型，除非真的需要，否则应尽可能避免使用
NULL；- 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列；
- 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存；
- 尽量使用整型定义标识列；
- 避免使用 MySQL 已经遗弃的特性，例如指定浮点数的精度，或者整型的显示宽度；
- 小心使用
ENUM和SET；- 最好避免使用
BIT。
