自己总结的数据库设计规范

总体方向思维

1.数据架构和容量规划相关
    a.总数据量的预估,一年或两年为期限,直接影响硬盘容量规划
    b.是否关联其他数据库和环境,关联数据会影响数据增长量
    c.数据重要性,是否需要集群,备份级别的要求,不重要数据可选择定期归档或清除旧数据,硬盘要求可降低
        ps:数据重要性大体分为:完全不可丢失,丢一些可以接受,丢一部分可以接受,全丢都不影响

2.性能相关
    a.平均活跃连接数预估,活跃连接数越多,查询也不会少,数据库压力就上来了
    b.预估QPS数据,增删查改的频率,QPS越高,数据库压力越大
    c.代码是否有轮巡和重复查询现象,太过频繁操作数据库显然也会增加压力
    d.代码是否有先查后改和没过滤条件的查询等问题逻辑,人为增加数据库压力的逻辑不可取
    e.代码是否有缓存机制,特别针对静态信息,能显著减少数据库压力,但是要看框架设计思路

3.增长量
    a.数据增长和并发增长的预估,按月和按年预估,初期压力不高可理解,但是别忘了要预估长期压力
    b.直接业务和关联业务增长的预估,算单库压力,同上,要看中长期,而关联业务附带的压力往往被忽略

设计表时要注意

4.表结构是否科学
    a.表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。
    b.使用合适的INT类型,而非无脑BIGINT,如果非负则加上UNSIGNED（这样数值容量会扩大一倍）,当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
    c.如果只是分类字段(例如性别)或者是数据差异较少(例如月份)的字段,建议使用枚举或整型代替字符串类型
    d.尽量使用TIMESTAMP而非DATETIME,因为DATETIME会逐步退出历史
    e.单表不要有太多字段，建议在20以内,或者说是单条记录所有字段加起来控制在8K字节内,因为innodb默认一个数据页是16K,存两行数据,超过就会造成行溢出,影响性能
    f.用整型来存IP,电话号码,身F证等信息,然后代码拼接实现
    g.慎用blog/text大字段,因为容易造成内存溢出,尽量也只用在非常用查询字段,或者查询时主动隐藏该字段,按需显示
    h.慎用存储过程,触发器,函数,因为比较耗费数据库内部资源,用程序实现更好
    i.字符集,注释,字段的关联统一性,字符集和字段统一可以避免类型转换和主从报错,注释统一方便查询其含义

5.索引结构设计规范

    a.索引越多,理论上查询越快,但是占用硬盘空间也越多,数据插入越慢(写完数据还要写索引),需要慎重考虑索引的必要性
    b.不要用外键,删除和修改字段会造成关联锁定,异常麻烦,尽量用程序约束就足够了
    c.建立索引要注意数据差异对比,差异太少不适合建立独立索引,应和其他字段建立联合索引,但要注意最左匹配原则,避免建立重复的索引
    d.尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
    e.字符字段尽量只建前缀索引,且最好不要做主键,因为范围查询性能差,建议建立一个自增整型字段做主键,字符字段做唯一索引
    f.插入操作太多的字段应避免使用唯一索引,因为每次插入都会判断唯一性,耗费不必要的性能,虽然查询也会判断,但是这个性能损耗少很多,可以忽略