内部MySql使用规范

数据库环境

  • prod:线上环境,只允许管理员操作且要做操作记录方便回滚。
  • dev:开发环境,开发可读写,可修改表结构。且使用版本控制系统记录sql操作记录,方便上线时统一修改数据库。

命名规范

基本命名规则

  • 使用有意义的英文词汇,词汇中间以下划线分隔,避免使用保留字
  • 只能使用英文字母,数字,下划线,并以英文字母开头
  • 库、表、字段全部采用小写,不要使用驼峰式命名
  • 数据库、表,一律使用前缀
  • 同一类型的业务,表前缀尽量相同。
  • 除前缀外,表名单词尽量不要错过4个。

表命名规则

  • 表名建议遵循“业务名称_表的作用”
  • 同功能表前缀需一致,例如:articlearticle_catarticle_usersystem_usersystem_menu
  • 特殊需求的功能模块的表集合可以考虑分库,例如:日志系统,用户系统等

字段命名规则

  • 每个表必须包括字段:主键(xxx_id),创建时间(create_time),修改时间(update_time),状态(status)
  • 各表之间有关联的字段名应相同
  • 字段前缀需有意义,一个表的功能组也可以使用前缀区分,例如:布尔值用is_,计数用num_,商品表中的属性相关字段用attr_,回复xx时用to_

索引命名

  • 非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名
  • 唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名

约束命名

  • 主键约束:pk_表名称。
  • 唯一约束:uk_表名称_字段名。(应用中需要同时有唯一性检查逻辑。)

表设计规范

  • 表引擎取决于实际应用场景;日志及报表类表建议用myisam,与交易,审核,金额相关的表建议用innodb引擎。如无说明,建表时一律采用innodb引擎
  • 默认使用utf8mb4字符集,数据库排序规则使用utf8mb4_general_ci,因为utf8不能存储Emoji表情
  • 所有表、字段应该使用comment描述此含义
  • 所有表需有status字段(0删除,1正常),布尔值字段(is_xx)同理
  • 能用数字(tinyint,int)就不要用字符(varchar,char)
  • 使用UNSIGNED存储非负数值
  • 不建议使用ENUM、SET类型,使用TINYINT来代替
  • 时间使用int类型存储时间戳,年月日使用int类型(20201120)
  • IP地址存储,如果需要计算就存储为int类型,否则用char(15)存储
  • 图片/视频/音频储存路径,尽量使用oss储存,再做个cdn
  • 拆分大字段和访问频率低的字段,分离冷热数据

索引设计规范

索引基本使用规范

  • 需要考虑索引的字段有:WHERE条件列、GRDER BY/GROUP BY/DISTINCT字段、JOIN字段
  • 索引字段不能为null
  • Hash索引不能使用非等值索引,这里要区分hash/b+tree索引

联合索引

  • 联合索引最左匹配原则

索引覆盖

InnoDB存储引擎中,正常的应该是先从索引中查找主键,然后再从主键中查其他数据列,要查询两次。索引覆盖就是直接从索引中就可以拿到需要的数据,不必在进行第二次查询。

语句设计规范

基本使用规则

  • 查询语句使用前必须使用EXPLAIN进行分析
  • 尽量少使用负向查询,如:not null/like等
  • 避免在数据库中进行数学运算,应该在代码中计算好了之后在提交sql
  • 减少与数据库的交互次数,大量sql语句时,尽量使用批处理
  • 不使用select * ,SELECT语句只获取需要的字段
  • 禁止使用子查询,建议将子查询转换成关联查询

特殊使用场景

  • 正确使用分页。第一种是page与size(limit 1000,10),第二种是last_id与size(where id>1000 limit 10)。当page过大是查询效率低,当有数据更新时第一种会有重复,建议使用第二种方式。
  • 经纬度储存规范,使用:经度,纬度储存
  • 国家/省市区/地址储存规范,国家使用区号,省市区使用行政区划码,详细地址使用varchar储存
  • 属性数据储存,使用json储存

分表规范

单表2年内数据量超过500w或数据容量超过10G考虑分表,需提前考虑历史数据迁移或应用自行删除历史数据,采用等量均衡分表或根据业务规则分表均可。

  • 用HASH进行散表,表名后缀使用十进制数,下标从0开始
  • 按日期时间分表需符合YYYY[MM][dd][HH]格式
  • 采用合适的分库分表策略。例如千库十表、十库百表等
  • 禁止使用分区表,分区表对分区键有严格要,分区表在表变大后执行DDL、SHARDING、单表恢复等都变得更加困难。
  • 拆分大字段和访问频率低的字段,分离冷热数据

其他规范

  • 批量操作前必须备份
  • 每日凌晨3-5点备份整库
  • 对非业务数据使用消息队列,将其放在非高峰期执行
  • 全文搜索/列表中大量分类筛选推荐使用Elasticsearch

此处评论已关闭