数据库设计&开发规范

  1. 表名、字段名均小写加下划线

  2. 不能使用保留字
    http://dev.mysql.com/doc/refman/5.7/en/keywords.html

  3. 见名识意,最好不要超过32个字符

  4. 临时表以tmp为前缀,日期为后缀,备份表以bak为前缀,日期为后缀

  5. 所有存储相同数据的列名和类型必须一致

  6. 所有表都必须使用Innodb存储引擎

  7. 数据库和表的字符集统一使用UTF8(存Emoji表情用utf8mb4

    注:MySQL的UTF8字符集汉字占3个字节,如果是VARCHAR(255),则共占用255*3=765个字节

  8. 所有表个字段都必须添加注释

  9. 尽量控制单表数据量大小在500W以内

    注:可以使用数据归档、分库分表来控制数据量

  10. 谨慎使用MySQL分区表,谨慎选择分区键(跨分区效率比普通效率更低)

  1. 尽量做到冷热数据分离,减小表的宽度

    注:MySQL限制单表最多存储4096列,且每一行都不能超过65535个字节。
    减少磁盘IO,保证热数据的命中,减少冷数据的读入。
    尽量少用SELECT *,将经常使用的列放到一个表中。

  2. 禁止在表中建立预留字段

  3. 禁止在数据库中存储非结构化数据

  4. 禁止在线上做数据库压力测试

  5. 禁止直接连接生产环境数据库

索引设计规范

  1. 限制索引数量,单表索引建议不超过5个

    注:索引是把双刃剑

  2. 禁止给表中的每一列都建立单独的索引

  3. 每个Innodb表必须有一个主键,建议使用自增ID

    注:不使用更新频繁的列作为主键,不适用多列联合主键

  4. 建议不使用UUID, MD5, HASH, 字符串作为主键

    注: Innodb是索引组织表,数据存储的逻辑顺序和索引顺序一样

  5. 索引列顺序

    • 区分度最高的列放在联合索引的最左侧
    • 尽量把字段长度小的列放到联合索引的左侧
    • 使用频繁的列放在联合索引的左侧
  6. 避免建议冗余索引和重复索引

  7. 对于频繁查询的字段优先考虑覆盖索引(包含了所有查询字段的索引)

  8. 尽量避免使用外键(尽量少用外键约束,但一定在表与表之间关联建上建立索引)

常见索引列建议

  1. SELECT, UPDATE, DELETE 语句的WHERE从句中的列
  2. 包含在ORDER BY, GROUP BY, DISTINCT中的字段
  3. 多表JOIN的列

字段设计规范

  1. 优先选择符合存储需要的最小的数据类型

    比如将IP地址转换为数字类型存储INET_ATON('255.255.255.255') = 4294967295

    1
    2
    select INET_ATON('255.255.255.255')
    select INET_NTOA(4294967295)

    对于非负整数,优先采用无符号整型来存储

    1
    2
    SIGNED INT     -2147483648 ~ 2147483647
    UNSIGNED INT 0 ~ 4294967295

    存IP地址使用无符号的INT,因为255.255.255.255转为整数,刚好就是无符号的上限

注: VARCHAR(N) N是字符数,不是字节数,因此VARCHAR(255)可以存255个中文汉字

VARCHAR长度过长消耗的内存更多
  1. 避免使用TEXT, BLOB数据类型

    TEXT可存64KB的内容,对于类似备注可以不使用TEXT,用VARCHAR(255)

    如果要使用TEXT或BLOB,建议分离到单独的拓展表中

    TEXT或BLOB类型只能使用前缀索引

    TEXT不能使用默认值

  2. 避免使用ENUM数据类型

    修改ENUM需要使用ALTER语句

    ENUM的ORDER BY效率低

    禁止使用数值做欸ENUM的枚举值

  3. 尽可能把所有列定义为NOT NULL

  4. 使用TIMESTAMP或DATETIME类型存储时间

    TIMESTAMP 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07

  5. 同财务相关的金额类数据,必须使用DECIMAL类型

    DECIMAL是精准的数据类型,在计算时不会丢失精度

    DECIMAL占用的空间由定义的宽度决定

    DECIMAL可以存储比bigint还大的数据

数据库SQL开发规范

  1. 建议使用预编译语句进行数据库操作

  2. 一个库一个账号

  3. 禁止跨库查询

  4. 禁止使用SELECT *,必须使用SELECT <字段列表>

  5. 禁止使用不含字段列表的INSERT语句

  6. 避免使用子查询,优化为join操作

  7. 避免使用JOIN关联太多的表

    注:MySQL最多允许关联61个表,建议不超过5个

  8. 减少同库交互次数

  9. 使用IN替代OR

  10. 禁止使用order by rand()进行随机排序

  11. WHERE从句中禁止对列进行函数转换和计算

  12. 在明显不会有重复值时使用UNION ALL而不是UNION

    注: UNION会把所有数据放到临时表中后再进行去重,UNION ALL不会在对结果集进行去重操作

数据库操作行为规范

  1. 超过100W行的批量写操作要分批次进行

  2. 对于大表修改采用pt-online-schema-change修改表结构

    注: pt-online-schema-change会先创建一个新表,在新表中进行修改,修改完成把新表命名成原表并删除原表

  3. 禁止为程序使用的账号赋予super权限

  4. 程序权限最小原则

    注: 程序账号不能有drop权限