MySQL数据库六大设计规范总结1

数据库命名规范

所有数据库对象名称必须小写，因为其对大小写是敏感的。例如table跟Table是两张不同的表

命名要做到见名识意，禁止使用MySQL保留关键字。

临时表以tmp_开头，备份表以bak_开头并以时间戳结尾。

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

数据库基本规范

所有表和字段都需要添加注释

    使用comment从句添加表和列的备注

    从一开始就进行数据字典的维护

数据库和表的字符统一使用UTF8

    统一字符集可以避免由于字符集转换产生的乱码

    MySQL中UTF8字符集汉字占3个字节，ASCII码占1个字节

所有表必须使用InnoDB存储引擎

    5.6以后的默认引擎

    支持事务、行级锁、更好的恢复性、高并发下性能更好

尽量控制单表数据量的大小，建议控制在500万以内

    500万并不是MySQL数据库的限制，具体MySQL能存储多少万数据量呢？取决于存储设置和文件系统

    可以用历史数据归档，分库分表等手段来控制数据量大小

谨慎使用MySQL分区表

    分区表在物理上表现为多个文件，在逻辑上表现为一个表

    谨慎选择分区键，跨分区查询效率可能更低

    建议采用物理分表的方式管理大数据

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

    减少磁盘IO，保证热数据的内存缓存命中率

    利用更有效的利用缓存，避免读入无用的冷数据

    经常一起使用的列放到一个表中

禁止在表中建立预留字段

    预留字段的命名很难做到见名识义

    预留字段无法确认存储的数据类型，所以无法选择合适的类型

    对预留字段类型的修改，会对表进行锁定

禁止在数据库中存储图片、文件等二进制数据

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

禁止从开发环境，测试环境直连生产环境数据库

数据库索引规范

限制每张表上的索引数量，建议单张表索引不超过5个

    索引并不是越多越好，其可以提高效率同样也可以降低效率

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

    每个InnoDB表必须有一个主键

常见索引列建议

    SELECT、UPDATE、DELETE语句的WHERE从句中的列

    包含在ORDER BY、GROUP BY、DISTINCT中的字段

    多表JOIN的关联列

如何选择索引列的顺序

    区分度最高的列放在联合索引的最左侧

    尽量把字段长度小的列放在联合索引的最左侧

    使用最频繁的列放到联合索引的左侧

避免建立冗余索引和重复索引

    primary key(id)、index(id)、unique index(id)

    index(a,b,c)、index(a,b)、index(a)

对于频繁的查询优先考虑使用覆盖索引

    覆盖索引：就是包含了所有查询字段的索引

    避免InnoDB表进行索引的二次查找

    可以把随机IO变为顺序IO加快查询效率

尽量避免使用外键

    不建议使用外键约束，但一定在表与表之间的关联键上建立索引

    外键可用于保证数据的参照完整性，但建议在业务端实现

    外键会影响父表和子表的写操作从而降低性能