高性能可扩展MySQL数据库设计及架构优化 电商项目_1

一 数据库设计规范

1 命名规范

所有数据库对象名称必须用小写字母并下划线分割

对象名称禁止使用MySQL保留关键字

命名要做到见名识义，不超过32字节

临时表必须tmp前缀 日期后缀

备份库表bak前缀 日期后缀

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

2 数据库基本设计规范

所有表必须用innodb引擎

数据库和表字符集同意使用utf8(全存储中文的可以使用gbk 或是gbk2312)

所有表和字段都使用comment加注释

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

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

谨慎使用MySQL分区表

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

禁止在表中建立预留字段

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

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

禁止从开发环境 测试环境连接生产环境的数据库

3索引设计规范

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

每个innodb表必须有一个主键，不适用频繁更新的列为主键 不适用多留主键

常见索引留建议： 在select update delete语句中where从句中的列

包含在order by 、group by distinct中的字段

多表连接的关联列

如何选择索引列的顺序：

区分度最高的列放在联合索引的最左侧（尽量选主键）

字段小的列放在联合索引的最左侧

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

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

4 数据库字段设计规范

有限选择符合存储需要的最小的数据类型，

比如讲字符串转化的数字类型存储

对于非负数数据采用无符号×××进行存储( unsigned int)

避免使用text blob enum类型

尽量把列定义为not null

使用大datetime 或timestamp类型存储时间

同财务相关的金额类数据 必须使用decimal类型

5 数据库sql开发规范

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

避免使用双%的查询条件 如like %w%

应该对以后的扩展进行考虑

程序连接

禁止使用select *

避免使用子查询 可转化为join操作

避免使用join关联太多的表 建议不少过5个

减少同数据库的交互次数

明显不会有重复值是使用union all代替union

拆分复杂的大sql为多个小sql

6 数据库操作行为规范

超100万行的批量些操作 要分批多次进行操作

对大表使用pt-online-schema-change修改表结构

禁止为程序使用的账号赋予super权限，遵循权限最小原则，不准有drop权限