YingJoy's Blog
0%

数据库设计&开发规范

Posted on Edited on

  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分区表，谨慎选择分区键（跨分区效率比普通效率更低）

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

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

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

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

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

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

索引设计规范

  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长度过长消耗的内存更多

  2. 避免使用TEXT, BLOB数据类型

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

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

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

    TEXT不能使用默认值

  3. 避免使用ENUM数据类型

    修改ENUM需要使用ALTER语句

    ENUM的ORDER BY效率低

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

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

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

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

  6. 同财务相关的金额类数据，必须使用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权限