Mysql-表的设计合理化

参考阅读： 58到家数据库30条军规解读

 

 

数据库的表结构设计往往会影响应用后期的性能，特别是用户量上来了以后的性能：

1.符合3NF
表的范式，是首先符合1NF, 才能满足2NF , 进一步满足3NF
1NF: 即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF
2NF: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现，主键一般不含业务逻辑，设置为自增长
3NF: 即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放. 比如下面的设计就是不满足3NF:
反3NF : 但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

在表的1对N的情况下，为提高效率，可能会在1这表中设计字段提速

2.字符集
一般来说尽量选择UTF-8
虽然在存储的时候GBK比UTF-8使用的存储空间少，但是UTF-8兼容各国语言，其实我们不必为了这点存储空间而牺牲了扩展性。
事实上，后期如果要从GBK转为UTF-8所要付出的代价是很高的，需要进行数据迁移，而存储空间完全可以用花钱扩充硬盘来解决。

3.主键
在使用mysql的innodb的时候,设计表的时候需要： 增加一个主键，而且最好要自增。
对于插入：
因为自增主键可以让插入的数据按主键顺序插入到底层的B+树的叶子节点中，由于是按序的，这种插入几乎不需要去移动已有的其它数据，所以插入效率很高。
如果主键不是自增的，那么每次主键的值近似随机，这时候就有可能需要移动大量数据来保证B+树的特性，增加了不必要的开销。
对于查询：
原因是innodb的底层存储模型是B+树，它使用主键作为聚簇索引，使用插入的数据作为叶子节点，通过主键可以很快找到叶子节点，从而快速获取记录

4.字段
(1)只含数值信息的字段只使用数字型字段
若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了
(2) 存储小数，建议使用decimal,
不建议使用float、double来存小数，会损失精度
(3)尽量使用varchar/nvarchar 代替 char/nchar 首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。其次，varchar类型长度,建议不要超过8K。
在设计长度时，括号里的数字尽可能少，除了需要进行填充
(4)时间类型建议使用Datetime，
不要使用timestamp，虽然Datetime占用8个字节，而timestamp只占用4个字节，但是后者要保证非空，而且后者是对时区敏感的。
(5)保存大量数据,建议将大文本数据保存在专门的文件存储系统中，mysql中只保存这个文件的访问地址，比如博客文章可以保存在文件中，mysql中只保存文件的相对地址,不建议使用Text/blob来保存大量数据，因为对大文本的读写会造成比较大的I/O开销，同时占用mysql的缓存，高并发下会极大的降低数据库的吞吐量
(6) 在设计字段时，建议字段必须加上not null约束，并且设置default值，尤其是索引字段和查询条件必须设置
(7) 建议表中增加gmt_create和gmt_modified两个字段，用来记录数据创建的修改时间。这两个字段建立的原因是方便查问题。