MySQL_12_Schema设计标准

Schema设计

数据类型

数据类型：指定分配的存储空间（影响优化器）

数据类型的选择应遵循以下标准：

（1）最小数据类型：确定合适数据类型后，选择其中占用存储空间少的

1）原因：占用系统资源更少（需要CPU周期也更少）

2）需保证存储值的范围和正确性（避免后期添加数据类型的范围）

//添加数据类型的范围是非常耗时和麻烦的操作

（2）简单：多个数据类型可存储相同数据时，选择更为简单的

1）原因：需要CPU周期更少

//应使用整型数据类型存储IP（而不是字符串）

（3）避免NULL：避免列中含NULL（默认属性）

1）原因：含NULL的列较难优化（且占据更多存储空间）

2）尽量在设计字段时指定其具有NOT NULL限制（特别是索引字段）

//当含NULL的列被索引时，每个索引需额外占1个字节

类型优化

小数型

（1）避免使用小数型计算

1）原因：占用较多的存储空间和系统资源，同时计算不精确；

2）优化方案：使用倍数调整，以存储至整数型

字符型

（1）CHAR类型适用的场景

1）字段的长度都近似平均长度；

2）较短的字符串；

3）字段经常更新；

//字符串长度是指字符数（而非字节数）

（2）VARCHAR类型适用的场景

1）每个字符都使用不同的字节数进行存储

2）字段中最大长度比平均长度大很多；

3）字段更新次数较少；

若字符常被比较，应使用BINARY/VARBINARY类型

1）BINARY/VARBINARY类型通过字节码进行比较（而非字符）

2）字节码比较具有大小写敏感，且更快

//避免使用BLOB和TEXT类型

内容有限时，可使用枚举类型代替字符类型

1）枚举类型存储较为紧凑，通常会压缩到1或2个字节中；

2）MySQL会生成“数字-字符”映射关系并存储；

3）枚举类型排序依据是数字（而非存储的字符）；

4）避免枚举类型和字符类型关联查找；

//可使用FIELD（）函数显示指定排序依据（但会导致无法利用索引消除排序）

//字符和字符，枚举和枚举的关联查找效率更高（尽量采用整数主键关联）

日期型

（1）尽量使用TIMESTAMP类型

1）TIMESTAMP占用存储空间更少，且特性更多；

2）TIMESTAMP类型会根据时区变化；

标识符列

标识符列（自增列）：常用于寻找其他列、进行值比较和关联的列

1）外键一定是标识符列（反之不一定）；

2）用于关联时，需保证标识符列的数据类型精确匹配；

优秀的标识符列具有以下特性：

1）列中不存在重复值；

2）不带小数的数据类型（常为整数型）；

3）进行插入操作时，列的值是由系统自动生成的（非空）；

标识符列的数据类型尽量避免为字符类型

1）字符类型占用存储空间较多，且比较效率低；

2）字符类型可能导致SELECT/INSERT语句的执行速度变慢；

设计原则

Schema设计应遵循以下原则：

（1）避免过多的列

1）存储引擎API通过行缓冲格式在服务器层和存储引擎层之间拷贝数据；

2）服务器层间缓冲内容解码成各个列（解码的代价非常高，应减少）；

（2）少关联

1）单个查询的关联表限制在3个以内（保证快且并发性高）；

2）MySQL限制关联操作只能有61张表；

（3）避免过度枚举

1）枚举过多会导致Schema设计的凌乱；

2）集合可理解为变相的枚举，也应避免过度的使用；

（4）减少NULL列

1）含NULL的列较难优化（且占据更多存储空间）；

2）可使用对应数据类型的零值代替（但无需极端的替换）；

范式优点：

1）更新操作更快；

2）表更小，操作执行较快；

3）冗余数据更小，且数据修改涉及的更少；

范式缺点：

1）较多关联；

2）索引易失效；

//尽量范式和反范式混用

加快ALTER TABLE

ALTER TABLE的默认执行流程：

1）根据原表结构创建新空表，并修改表；

2）锁定原表并将原表数据导入新表；

3）删除旧表，应用新表；

//大部分ALTER TABLE操作会导致MySQL服务中断

//可通过主从库切换或影子拷贝实现无中断

通过重建.frm文件实现快速ALTER TABLE

1）创建相同结构的空表，并修改表；

2）执行FLUSH TABLES WITH READ LOCK上锁；

3）交换新旧表的.frm文件；

4）执行UNLOCK TABLES释放锁；

//移除AUTO_INCREMENT属性和增加/移除或更改ENUM和SET不需要重建