MySQL数据库设计规范

数据库命名规范

• 所有数据库对象名称必须使用小写字母并用下划线分隔
  
  • 数据库对象是以文件形式存储的。在Linux系统中字母的大小写是敏感的。
• 禁止使用mysql保留关键字（https://dev.mysql.com/doc/refman/5.7/en/keywords.html）
• 数据库对象的命名要见名识义，并且最好不要超过32个字符
• 临时库表必须以tmp为前缀并以日期为后缀
• 备份库表必须以bak为前缀并以日期为后缀
• 所有存储相同数据的列名和列类型必须一致

数据库基本设计规范

• 所有表必须使用InnoDB存储引擎
  
  • 5.6以后的默认引擎
  • 支持事务，行级锁，更好的恢复性，高并发下性能更好
• 数据库和表的字符集统一使用utf-8。
  
  • 兼容性好
  • 统一字符集可以避免由于字符集转换产生的乱码
  • MySQL中utf8字符集汉字占3个字节，ASCII码占1个字节
• 所有表和字段都要添加注释
  
  • 使用comment从句添加表和列的备注

CREATE TABLE `t_building` (
  `id` varchar(255) NOT NULL DEFAULT '' COMMENT '主键_id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='建筑表';

• 尽量控制单表数据量的大小，建议控制在500万以内。
  
  • 500万并不是MySQL数据库的限制，这种限制取决于存储设置和文件系统
  • 数据太大的时候修改表结构，备份，恢复都会有很大问题
  • 可以用历史数据归档，分库分表等手段来控制数据量大小
• 谨慎使用mysql分区表
  
  • 分区表在物理上表现为多个文件，在逻辑上表现为一个表
  • 谨慎选择分区键，跨分区查询效率可能更低
  • 建议采用物理分表的方式管理大数据
• 尽量做到冷热数据分离，减少表的宽度
  
  • 减少磁盘IO，保证热数据的内存缓存命中率
  • 利用更有效的利用缓存，避免读入无用的冷数据
  • 经常一起使用的列放到一个表中
• 禁止在表中建立预留字段
  
  • 预留字段的命名很难做到见名识义
  • 预留字段无法确认存储的数据类型，所以无法选择合适的类型
  • 对预留字段类型的修改，会对表进行锁定
• 禁止在数据库中存储图片，文件等二进制数据
• 禁止在线上做数据库压力测试
• 禁止从开发环境，测试环境直连生产环境数据库

数据库索引设计规范

• 不要滥用索引，限制每张表上的索引数量，建议单张表索引不超过5个
  
  • 索引并不是越多越好，索引可以提高效率同样可以降低效率
  • 索引可以增加查询效率，但同样也会降低插入和更新的效率
  • 禁止给表中的每一列都建立单独的索引
• 每个InnoDB表必须有一个主键
  
  • InnoDB是按照主键的顺序来组织表的
  • 不使用更新频繁的列作为主键，不使用多列主键
  • 不使用uuid，md5,hash,字符串列作为主键
  • 主键建议选择使用自增id值
• 在哪些列上建立索引
  
  • 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加快查询效率
• 避免使用外键
  
  • 不建议使用外键约束，但一定在表与表之间的关联键上建立索引
  • 外键可用于保证数据的参照完整性，但建议在业务端实现
  • 外键会影响父表和子表的写操作从而降低性能

数据库字段设计规范

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

  • 将字符串转化为数字类型存储（ip地址应存储为数字类型）

  • INET_ATON(‘255.255.255.255’)=4294967295

  • INET_NTOA(4294967295)=’255.255.255.255’

  • 对于非负型的数据来说，要优先使用无符号整型来存储。无符号相对有符号可以多出一倍的存储空间

  • signed int -2147483648~2147483647

  • unsigned int 0~4294967295

• varchar(N)中的N代表是字符数，而不是字节数

  • 使用utf8存储汉字varchar(255)=765个字节
  • 过大的长度会消耗更多的内存

• 避免使用text，blog数据类型

  • text分为四种tinyText,text,midumtext,longtext
  • 建议把blob或是text列分离到单独的扩展表中
  • text或blob类型只能使用前缀索引

• 避免使用enum数据类型

  • 修改enum值需要使用alter语句
  • enum类型的order by操作效率低，需要额外操作
  • 禁止使用数值作为enum的枚举值

• 尽可能把所有列定义为not null

  • 索引null的列需要额外的空间来保存，所以要占用更多的空间
  • 进行比较和计算时要对null值做特别的处理

• 用字符串存储日期型的数据是不正确的

  • 无法使用日期函数进行计算和比较
  • 用字符串存储日期要占用更多的空间
  • 使用timestamp（4个字节）或datetime（8个字节）类型存储时间
  • timestamp占用4字节和int相同，但比int可读性高
  • 超出timestamp取值范围的使用datetime类型

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

  • decimal类型为精准浮点数，在计算时不会丢失精度
  • 占用空间由定义的宽度决定
  • 可以存储比bigint更大的整数数据

数据库SQL开发规范

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

  • 只传递参数，比传递SQL语句更高效
  • 相同语句可以一次解析，多次使用，提高处理效率

• 避免数据类型的隐式转换，如果定义的为字符串类型一定要加单引号

  • 隐式转换会导致索引失效

• 合理利用存在索引，而不是盲目增加索引。充分利用表上已经存在的索引

  • 避免使用双%号的查询。比如a like ‘%123%’
  • 一个SQL只能利用到复合索引中的一列进行范围查询
  • 使用left join或not exists来优化not in操作

• 程序连接不同的数据库使用不同的账号，禁止跨库查询

  • 为数据库迁移和分库分表留出余地
  • 降低业务耦合度
  • 避免权限过大而产生的安全风险

• 禁止使用select *必须使用select 字段列表查询

  • 消耗更多的CPU和IO以及网络宽带资源
  • 无法使用覆盖索引
  • 可以减少表结构变更带来的影响
• 禁止使用不含字段列表的insert语句
  
  • 可以减少表结构变更带来的影响

insert into t values('a','b','c');
insert into t(c1,c2,c3) values('a','b','c');

• 避免使用子查询，可以把子查询优化为join操作
  
  • 子查询的结果集无法使用索引
  • 子查询会产生临时表操作，如果子查询数据量大则严重影响效率
  • 消耗过多的CPU及io资源
• 避免使用join关联太多的表
  
  • 每join一个表会多占用一部分内存（join_buffer_size）
  • 会产生临时表操作，影响查询效率
  • mysql最多允许关联61个表，建议不超过5个
• 减少同数据库交互次数
  
  • 数据库更适合处理批量操作
  • 合并多个相同的操作到一起，可以提高处理效率
• 使用in代替or
  
  • in的值不要超过500个
  • in操作可以有效的利用索引
• 禁止使用order by rand()进行随机排序
  
  • 会把表中所有符合条件的数据装载到内存中进行排序
  • 会消耗大量的CPU和io及内存资源
  • 推荐在程序中获取一个随机值，然后从数据库中获取数据的方式
• where从句中禁止对列进行函数转换和计算
  
  • 对列进行函数转换或计算会导致无法使用索引
• 在明显不会有重复值时使用union all而不是union
  
  • union会把所有数据放到临时表中后再进行去重操作
  • union all不会再对结果集进行去重操作
• 拆分复杂的大SQL为多个小SQL
  
  • mysql一个SQL只能使用一个CPU进行计算
  • SQL拆分后可以通过并行执行来提供处理效率

数据库操作行为规范

• 超过100万行的批量写操作，要分批多次进行操作
  
  • 大批量操作可能会造成严重的主从延迟
  • binlog日志为row格式时会产生大量的日志
  • 避免产生大事务操作
• 对大表数据结构的修改一定要谨慎，会造成严重的锁表操作。尤其是生产环境，是不能忍受的。
• 对大表使用pt-online-schema-change修改表结构
  
  • 避免大表修改产生的主从延迟
  • 避免在对表字段进行修改时进行锁表
• 禁止为程序使用的账号赋予super权限
  
  • 当达到最大连接数限制时，还允许1个有super权限的用户连接
  • super权限只能留给DBA处理问题的账号使用
• 对于程序连接数据库账号，遵循权限最小原则
  
  • 程序使用数据库账号只能在一个DB下使用，不准跨库
  • 程序使用的账号原则上不准有drop权限