mysql数据库设计规范

2016-11-11 11:55:39

写在前面,此文章为转载,略微整理

一、命名规范

[数据库环境介绍]

  • 开发环境(dev):开发人员可读写,在不影响其他开发同事时可随意修改
  • 测试环境(qa):开发和测试可读写,可通过工具修改表结构
  • 模拟环境(sim): 开发可读写,当有上线请求,会在这个环境预执行,这个环境供部署上线演练和压力测试使用
  • 生产数据库从库(stage): 只读环境,不允许修改数据,不允许修改表结构;供线上排查问题,数据查询使用
  • 生产环境(product): 开发不允许直接进行数据库操作,必须通过DBA进行操作并进行记录

不同环境的机器,需要做到权限明确,读写账户分离,能区分具体业务。eg. r_dev,w_dev

[数据库命名规范]

  • 简洁明了,体现数据库的用途,建议使用名词
  • 使用英文小写字母、下划线命名,不宜过长(12个字符以内)
  • 默认字符集统一utf-8,如果需要存储emoj表情,需要使用UTF8mb4

[表命名规范]

  • 具有统一前缀,相关功能表使用相同前缀(前缀名称一般不超过5字),体现相关业务,建议使用名词
  • 避免用ORACLE、MySQL的保留字,如desc,关键字如index
  • 使用英文小写字母、下划线命名,不宜过长(12个字符以内)
  • 引擎默认使用innodb,日志或报表酌情使用myisam(mysql8.0已经移除)
  • 必须有主键,建议使用auto_increment的id作为主键(与业务无关),和业务相关的要做为唯一索引;
  • 所有的表都必须有注释,解释其存放的数据内容
  • 预估数据量,如数据量比较大(char的表>500W行,或int表>1000W)需要考虑分表。分表策略与DBA协商
  • 功能相近的,命名规则应该统一
1
2
3
4
5
6
7
CREATE TABLE `app_store_log` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`appid` int(11) NOT NULL COMMENT '应用ID',
`create_time` int(11) NOT NULL COMMENT '建立时间',
PRIMARY KEY (`id`),
UNIQUE KEY `appid` (`appid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='应用商店记录表'

[字段命名规范]

  • 数据库字段命名与表命名相似
  • 字段应该有注释,描述字段用途及必要内容的解释
  • 外键统一用xxx-id的方式声明
  • 表主键默认约定为id,自增类型
  • 时间字段,除特殊情况一律采用int记录unix_timestamp
  • 网络IP字段,除特殊情况一律采用bigint来记录inet_aton值
  • 默认字段均为非空,最好指定默认值
  • 有些驱动对tingint支持不够好,通常建议按容量来选择字段
  • text字段尽量少用,或者拆分到冗余表
  • 对表加新字段,不允许指定字段位置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE `login_user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`jid` int(10) unsigned NOT NULL,
`user_id` int(10) NOT NULL COMMENT '用户的id',
`username` varchar(50) NOT NULL COMMENT '用户姓名',
`city` smallint(4) NOT NULL COMMENT '城市',
`ip` bigint(14) NOT NULL COMMENT '登陆ip',
`district_id` tinyint(2) NOT NULL COMMENT '所在区域的id',
`district_name` varchar(20) NOT NULL COMMENT '行政区名字',
`street_id` tinyint(2) NOT NULL COMMENT '所在街道(地标)的id',
`street_name` varchar(20) NOT NULL COMMENT '小区名字',
`status` tinyint(2) NOT NULL DEFAULT '1' COMMENT '用户状态:0禁用 1正常',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_jid` (`jid`),
KEY `user_id_index` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户登陆表'


ALTER TABLE T_APP_VERSION ADD COLUMN FSECURITY SMALLINT(5) NOT NULL DEFAULT 0 COMMENT '安全扫描结果';

二、表设计原则

[职责分离原则]
通常指数据的产生和使用,每个系统相互独立,通常取决于以下几点

  • 数据的产生:通常谁产生谁负责,维护数据的正个生命周期,产生,修改,销毁等周期。
  • 使用数据者:谁使用谁维护
  • 考虑高内聚,低耦合:在存放数据的时候如果考虑到数据使用原则导致了相关度非常高的数据存放在多个地方,需要多个系统来维护这个数据就有可能导致系统间的耦合性增强,应当尽量避免

在设计数据库表间的关系时也要遵循相同原则,职责分离降低耦合,但同时要考虑到性能情况,做到适当冗余而不导致修改逻辑复杂

[在线处理和分析分离]

  • 为了保证生产环境数据处理性能,需要将一些分析相关的数据及结果单独库存储,避免在数据分析的时候导致业务数据吞吐量下降,引起系统问题
  • 专门用于存放离线报表数据,并提供线上数据查询方法,建议将统计结果,汇总的数据都从在线处理数据库中移走

原则上要将在线用户请求和后台统计请求分开:

a. 将后台统计与生产库分开(一般使用slave),缺点是数据量大了玩不转。
b. 建立离线报表,专门存放统计结果,计算与展示异步处理,缺点是实时业务响应差。
c. 实时拉取mysql row binlog,做数据的异构处理(tungsten, canal),将增量结果处理后(storm),保存在数据库中,基本实时。

[事物与日志分离]

通常用户生成的内容和用户行为的日志要分开,eg:

游戏DB里存放玩家的基础信息,装备,属性,好友列表等等,这些放到数据库里面。但是玩家的行为日志,比如消耗金币,今天下过哪些副本,买过什么顶级装备,这些属于行为日志,应该单独存放并分析处理。

对于web,有好多用户置顶,刷新,竞价,展示等行为,要求实时并且量很大,一定要和贴子分开。

行为日志,需要做分析处理,并且由于时效性不宜存储在mysql中,后期维护就是地雷。

[历史可追溯]

保障数据可追溯,应当遵循一些简单的约定,事后方便数据的查询和统计:

  • 对于状态数据,应当设计相应状态的字段来保存该数据的最后状态,同时记录下来该数据的初始创建人,时间以及该数据的最后修改人和修改时间;所以在交易数据(如订单合同),广告数据,账户表等都应该默认有状态(status),创建人(creator/creator_name),创建时间(created_at),最后修改人(modifier/modifier_name),最后修改时间(modified_at)等字段用来表明数据的当前状态,创建信息及修改信息。

  • 针对需要跟踪每次修改的数据,需要在数据发生变化的时候记录一张日志表,用于记录该数据发生变化的全生命周期。针对只需要关注关键字段变化的情况,则日志表中只需要记录关键字段变化即可,但操作人,操作类型,时间应当准确记录,日志表数据一旦生成不允许进行修改。如用户账户的充值流水,消费流水都是一些业务紧相关的日志。而审核日志,操作记录等日志则属于与业务关联较小的日志。

  • 针对所有历史需要保留的数据则需要每次变化都生成一个新的版本,比如类目信息等,对原始数据永远只做insert操作,不做delete及update操作。但这种情况仅限于极端数据历史要求极高的情况下使用。

ref

Mysql数据库设计规范
数据库使用的一些规范
MySQL命名、设计及使用规范
数据库设计规范.md


您的鼓励是我写作最大的动力

俗话说,投资效率是最好的投资。 如果您感觉我的文章质量不错,读后收获很大,预计能为您提高 10% 的工作效率,不妨小额捐助我一下,让我有动力继续写出更多好文章。