MySql学习系列 -- 数据库、数据类型设计注意点

本篇博文接着来学习数据库以及表设计过程中要注意的一些关键点

1、表尽量使用InnoDB存储引擎（系统表有些默认是使用MyISAM、Memory存储引擎）

2、建议混合使用范式和反范式设计数据库；范式化数据库的缺点是查询操作需要做关联，而且代价昂贵，还可能导致一些索引策略无效

3、选择简单而且长度最小的数据类型可以减少磁盘以及内存的存储空间，还可以提升CPU的处理时间

4、对于有些信息可以通过类型转换实现复杂类型存储到简单类型存储的转换，如IP。使用无符号整数来存储IP，通过MySql内置的INET_ATON 、INET_NTOA函数实现IP与整形之间的转换。

5、对于计划添加索引的列，应该避免其默认为NULL

6、int(1) 与int(20)存储时占用的存储空间是一样的，可存储的数据大小也是一样的；1、20 只是表示数据的位数，方便数据库里数据的查看、展示

7、保持外键数据类型与主键的数据类型完全一致，包括类型、位数、有无符号等

8、主键建议使用自增型无符号正整数，可以节省存储空间，提高插入、更新的效率

9、对于有精度存储要求的小数而言，可以将他们乘以相应的倍数转换为整数存储

10、对于varchar类型的数据而言，还需要1-2个额外的字节来记录字符串的长度；他要比char定长要节省存储空间，因为他仅使用必要的空间，但是它有可能产生碎片；使用varchar类型，其在使用临时表和排序时可能悲观的按照最大长度分配内存；char不会有碎片的问题，如果可以确定长度，尽量使用char而不要使用varchar。

11、对于varchar char Text类型的字段可以设置其字符编码（charset）和校对规则（collate），也可以在表级别设置；这两项的设置会影响到是否区分大小写以及排序方式。

12、创建索引时建议将区分度高的列放在前面，但同时也要考虑避免随机I/O和使用文件排序

13、update 某列之后的值比varchar char定义的大的话，则在数据存储的时候会自动进行截断操作

14、有时候可以使用枚举（enum）来替换常用的字符串类型，适用于字符串数量较少而且不会更新的环境中；使用枚举时数据存储非常紧凑，会将数据压缩到一到两个字节中；列表值在数据库中实际存储的是整数值，同时在.frm文件中存储了列表值与整数值的映射关系，enum('lily','lucy','sony')分别对应的是1 2 3 整数；另外一点，枚举字段是按照内部存储的整数而不是定义的字符串进行排序的(最好字符串值的顺序与按字符排序的内容一致，这样才不会有歧义)；

15、MySql执行大部分修改表结构操作的方法都是用新的结构创建一个空表，从旧表中查处所有的数据插入到新表中，然后删除旧表。但是，也不是所有的Alter table都会引起表的重建；而且修改表结构时有可能到导致锁表操作。

16、表结构的定义是在.frm文件（MySql服务层）中定义的，理论上可以通过直接修改.frm文件实现表结构的变更。如果要修改day字段的默认值，有如下两种方式：

alter table film modify column day tinyint(1) not null defalut 5;  # 会引起表的重建
alter table film alter column day defalut 5;  # 不会引起表的重建，只会修改.frm结构

 

好的数据库设计是高性能的基础，在设计之初一定要最好规划