MySQL学习笔记(一) Schema设计

MySQL学习笔记(一) Schema设计

https://notes.diguage.com/mysql

目录

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. 数据类型的选择

1. 更小的通常更好

2. 简单就好

3. 尽量避免 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。