读《高性能MySQL》笔记---数据库结构设计

字段类型的选择

数据类型选择原则

1. 尽量使用占用空间小的数据类型

更小的数据类型通常更快，因为他们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更小。

2. 尽量使用简单的数据类型

简单的数据类型（内建类型<整形<字符串）通常需要更小的CPU周期。例如：使用timestamp或者datatime存储时间、使用整数存储IPV4。

3. 尽量避免NULL

如果查询中包含为NULL的列，对MySQL难以优化：因为可以为NULL的列使得索引、索引统计和值比较都更复杂。可以为NULL的列会使用更多的存储空间，在MySQL中也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至可能导致固定大小的索引变成可变大小的索引。

整数类型

MySQL可以为整数类型指定宽度。int(M)，其中M为该字段的值显示的长度，M<=255,255于存储大小和类型包含的值范围无关；如果存储的值长度范围>M，这这个显示返回的约束就失效了。

小数类型

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算；DECIMAL用于存储精确的小数。
MySQL将DECIMAL数字打包保存到一个二进制字符串中（没4个字节存9个数字），所以decimal(18,9)，说明该小数一个18个数字，小数点后9个数字，该数值一共占4+4+1（小数点占一个字节）=9个字节，decimal最多允许65个数字，在计算时会转化为DOUBLE计算。
FLOAT占用4个字节，DOUBLE占用8个字节，存储同样范围的值，DECIMAL使用的空间更小。
DECIMAL一般用于财务数字存储，其他一般使用FLOAT或者DOUBLE。

字符串类型

定长使用char,不定长使用varchar。
varchar类型更节省空间，因为他仅使用必要空间；varchar需要使用1个或者2个字节来记录字符串的长度（值长度小于255字节是，使用1个字节表示，否则使用2个字节）。字符串的最大长度比平均长度大很多，列更新很小，建议使用varchar。
char类型是定长的，mysql总是根据定义的字符串长度分配足够的空间。char适合存储很短的字符串，或者所有值接近同一个长度。
注意：char(3),varchar(3)中的3指的是字符串长度。

BLOB和TEXT类型

尽量避免使用。

枚举ENUM

枚举列可以把一些不重复的字符串存储成一个预定义的集合，在mysql的内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。
枚举字段内部存储的整数而不是定义的字符串进行排序的。

日期和时间

DATETIME时间范围广，与时区无关，使用8个字节空间。
TIMESTAMP存储最大值为2038年，显示的值依赖时区。

标识符的选择

整形通常是最好的选择。

MySQL schema设计中的陷阱

1. 太多的列

表结构的列不能过多，一般限制在32个左右。过多会造成CPU和内存占用高。因为操作系统从行缓冲区中将编码过的列转为行数据结构的操作代价很高。

2. 太多的关联

单个查询最好在12个表以内做关联。

3. 枚举滥用

enum只适用某个字段的可能结果集不变的情况，其他情况不要使用enum。

4. NULL

尽量给每个字段加上not null和一个默认值，减少程序和mysql的意外情况。

范式和反范式

◆ 第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。
◆ 第二范式（2NF）：首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
◆ 第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

范式的优缺点

有点：

1. 范式化的更新操作通常比反范式化要快。
2. 更新操作，修改的数据更小更高效。
3. 范式的表通常更小，可以更好的放在内存中，执行操作更快。

缺点：

1. 查询需要关联，影响查询效率。

反范式的优缺点

优点：

1. 因为数据几种在一个表上，查询数据不需要关联查询，或者更少的关联查询。

缺点：

1. 插入和更新操作影响数据行多，容易造成数据不一致。

混合使用范式和反范式

1. 可以把父表的值冗余到子表做排序作用。
2. 缓存衍生值也是有必要的，比入统计某个用户的登录次数。

加快ALTER TABLE操作的速度

1. 修改.frm文件

移除一个列的AUTO_INCREMENT属性；增加、移除或者更改enum和set常量。

2. 快速创建MyISAM索引

为了高效的载入数据到MyISAM表中，有一个常用的技巧是：先禁用索引（对唯一索引不生效），再载入数据，然后再重新启用索引。

alter table test.load_data disable keys;
#load the data
alter table test.load_data enable keys;