数据库设计规范
前言
- 制定规范的直接目的是约束设计行为,最终目的是确保设计的合理统一。
- 约定优先于配置(Convention Over Configuration)。
数据库命名规范、数据库基本设计规范、数据库索引设计规范、数据库字段设计规范、数据库SQL开发规范、数据库操作行为规范
数据库规范
库名规范
- 命名有意义,只能使用英文字母、数字、下划线,使用小写字母,长度32个字符以内
- 避免用MySQL的保留字如:
call、group
等
编码
- 采用 UTF8 编码。 只能是
utf8
或者utf8mb4
分库
- 名称:库通配名+编号(从0开始递增),比如:
wenda_001
- 把一个数据库分成多个,建议做个读写分离,做分库会加大开发成本,不推荐使用。
- 采用分库策略的,库的数量
不能超过1024
数据表
表名
- 命名有意义,只能使用英文字母、数字、下划线,使用小写字母,表名不使用复数名词,长度
32
个字符以内 - 避免用MySQL的保留字如:
call、group
等 - 同一模块使用的表名尽量使用统一前缀。
模块前缀+表名
,如师资系统采用”sz”作为前缀,渠道系统采用”qd”作为前缀等。 - 相关模块的表名与表名之间尽量体现join的关系,如user表和user_login表。
- 临时表(运营、开发或数据库人员临时用作临时进行数据采集用的中间表)命名规则:加上tmp前缀和8位时间后缀(tmp_test_user_20181109)
临时库表必须以tmp为前缀并以日期为后缀
- 备份表(DBA备份用作保存历史数据的中间表)命名规则:加上bak前缀和8位时间后缀(bak_test_user_20181109)
备份库、备份表必须以bak为前缀并以日期为前缀
主键
- id必须是主键,每个表必须有主键,且保持增长趋势的, 小型系统可以依赖于 MySQL 的自增主键,大型系统或者需要分库分表时才使用内置的 ID 生成器
- id类型没有特殊要求,必须使用
bigint unsigned
,禁止使用int,即使现在的数据量很小。id如果是数字类型的话,必须是8个字节。主键尽量用自增字段类型,推荐类型为INT
或者BIGINT
类型。 - 如果一定要使用 UUID 作为主键,让应用程序来产生。(
UUID()
,USER()
这样的 MySQL INSIDE 函数对于复制来说是很危险的,会导致主备数据不一致,所以请不要使用。) - ①强制要求主键为id,类型为
int
或bigint
,且为auto_increment
。②标识表里每一行主体的字段不要设为主键,建议设为其他字段如user_id,order_id等,并建立unique key索引(可参考cdb.teacher表设计)。因为如果设为主键且主键值为随机插入,则会导致innodb内部page分裂和大量随机I/O,性能下降。
外键
- 禁止使用外键
必须字段
- 核心表(如用户表,金钱相关的表)必须有行数据的创建时间字段
create_time
和最后更新时间字段update_time
,便于查问题。
大型字段
- 建议对表里的
blob
、text
等大字段,垂直拆分到其他表里,仅在需要读这些对象的时候才去select。 - 同一表中,所有
varchar
字段的长度加起来,不能大于65535
. 如果有这样的需求,请使用TEXT/LONGTEXT
类型。
冗余字段
- 适当的时候,可以有一定的字段容余
- 经常需要join查询的字段,在其他表里冗余一份。如user_name属性在user_account,user_login_log等表里冗余一份,减少join查询。
- 不是频繁修改的字段。
- 不是 varchar 超长字段,更不能是 text 字段。
字段数量
- 50
- 表不应该有重复字段
- 禁止在表中建立预留字段
- 单表物理大小限制在
16GB
,表中数据控制在2000W
内 - 尽量做到冷热数据分离,减小表的宽度 mysql限制最多存储4096列,为了是减少磁盘IO,保证热数据的内存缓存命中率,利用更有效的利用缓存,避免读入无用的冷数据 经常使用的列放在一个表中
- 尽量控制单表数据量的大小,建议控制在
500万以内
,500万并不是mysql数据库的限制 - 当表的字段数非常多时,可以将表分成两张表,一张作为条件查询表,一张作为详细内容表 (主要是为了性能考虑)。
字段类型
- 禁止在数据库中存储图片、文件等二进制数据
- 禁止使用复杂数据类型(数组、自定义等)
编码
- 显式指定字符集为
utf8
或utf8mb4
。
注释
- 所有表和字段都需要添加注释,使用
comment从句
添加表和列的备注 - 每个字段和表必须提供清晰的注释
引擎
- 如无特殊需求,一律为
InnoDB
。
结构
- Mysql 的表尽量设置成 KV (Key-Value) 结构,这样便于扩展和维护。
设计规则
- 不同应用间所对应的数据库表之间的关联应尽可能减少,不允许使用外键对表之间进行关联,确保组件对应的表之间的独立性,为系统或表结构的重构提供可能性
- 表设计的角度不应该针对整个系统进行数据库设计,而应该根据系统架构中组件划分,针对每个组件所处理的业务进行数据库设计
- Stored procedure (包括存储过程,函数,触发器) 对于 MYSQL 来说还不是很成熟, 没有完善的出错记录处理,不建议使用。
定期操作
- 需要定期删除(或者转移)过期数据的表,通过分表解决
分表
- 无特殊需求,严禁使用分区表
- 如果数据量或数据增长在前期规划时就较大,那么在设计评审时就应加入分表策略
- 分区表在物理上表现为多个文件没在逻辑上表现为一个表 谨慎选择分区键,跨分区查询效率可能更低 建议采用物理分表的方式管理大数据
- 尽量控制单表数据量的大小,建议控制在
500万以内
,500万并不是mysql数据库的限制 - 单表行数超过
500 万行
或者单表容量超过2GB
,才推荐进行分库分表。 说明:如果预计2年后的数据量根本达不到这个级别,请不要在创建表时就分库分表。 - 当表的字段数非常多时,可以将表分成两张表,一张作为条件查询表,一张作为详细内容表 (主要是为了性能考虑)。
- 分表就是把一张大表,按照如上过程都优化了,还是查询卡死,那就把这个表分成多张表,把一次查询分成多次查询,然后把结果组合返回给用户。
- 分区表的分区字段(partition-key)必须有索引,或者是组合索引的首列。
- 单个分区表中的分区(包括子分区)个数
不能超过1024
。 - 单个分区文件不超过2G,总大小不超过50G。建议总分区数
不超过20个
。 - 对于分区表执行alter table操作,必须在业务低峰期执行。
- 采用分表策略的,表的数量
不能超过4096
- 单个分表
不超过500W行
,ibd文件大小不超过2G
,这样才能让数据分布式变得性能更佳。 - 水平分表尽量用取模方式,日志、报表类数据建议采用日期进行分表。
数据表字段
名称
- 命名有意义,只能使用英文字母、数字、下划线,使用小写字母,长度32个字符以内
- 避免用MySQL的保留字如:
call、group
等 - 各表之间相同意义的字段必须同名,类型也必须相同
- 布尔值类型的字段命名为
is + 描述
。如 member 表上表示是否为 enabled 的会员的字 段命名为 IsEnabled。
默认值
- 表中所有字段必须都是
NOT NULL
属性,业务可以根据需要定义DEFAULT值
。因为使用NULL值
会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。 - 字段尽量设置为
NOT NULL
, 为字段提供默认值。 如字符型的默认值为一个空字符值串’’;数值型默认值为数值 0;逻辑型的默认值为数值 0; - 表字段避免 null 值出现,null 值很难查询优化且占用额外的索引空间,推荐默认数字 0 代替 null。
类型要求
- 需要多表 join 的字段,数据类型保持绝对一致。
- 字段的类型为枚举型或布尔型时,建议使用
char(1)
类型。 - 表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型是
unsigned tinyint
( 1表示是,0表示否)。表达逻辑删除的字段名is_deleted
,1 表示删除,0 表示未删除。 - 业务中选择性很少的状态
status
、类型type
等字段推荐使用tinytint
或者smallint
类型节省存储空间。 - 不推荐使用
enum
,set
。 因为它们浪费空间,且枚举值写死了,变更不方便。推荐使用tinyint
或smallint
。 - 任何字段如果为非负数,必须是
unsigned
- TEXT字段必须放在独立的表中,用PK与主表关联。如无特殊需要,禁止使用
TEXT
、BLOB
字段 - 表示boolean类型的都使用
TINYINT(1)
,因为mysql本身是没有boolean
类型的,在自动生成代码的时候,DO对象的字段就是boolean
类型,例如is_delete
;其余所有时候都使用TINYINT(4)
- 优先选择符合存储需要的最小的数据类型 将字符串转化为数字类型存储
VARCHAR字段
- 同一表中,所有 varchar 字段的长度加起来,
不能大于65535
. 如果有这样的需求,请使用TEXT/LONGTEXT
类型。 - 字符串
VARCHAR(N)
,其中N
表示字符个数,请尽量减少N
的大小,参考:code VARCHAR(32)
;name VARCHAR(32)
;memo VARCHAR(512)
; - 文本数据尽量用varchar存储。因为varchar是变长存储,比char更省空间。MySQL server层规定一行所有文本最多存65535字节,因此在utf8字符集下
最多存21844个字符
,超过会自动转换为mediumtext
字段。而text utf8字符集下最多存21844个字符
,mediumtext最多存2^24/3个字符
,longtext最多存2^32个字符
。一般建议用varchar类型,字符数不要超过2700。 - 所有动态长度字符串全部使用VARCHAR类型,类似于状态等有限类别的字段,也使用可以比较明显表示出实际意义的字符串,而不应该使用INT之类的数字来代替;VARCHAR(N),N表示的是字符数而不是字节数。比如VARCHAR(255),可以最大可存储255个字符(字符包括英文字母,汉字,特殊字符等)。但N应尽可能小,因为MySQL一个表中所有的
VARCHAR字段最大长度是65535个字节
,且存储字符个数由所选字符集决定。如UTF8存储一个字符最大要3个节,那么varchar在存放占用3个字节长度的字符时不应超过21845个字符。同时,在进行排序和创建临时表一类的内存操作时,会使用N的长度申请内存。(如无特殊需要,原则上单个varchar型字段不允许超过255个字符) - 如果存储的字符串长度几乎相等,使用 char 定长字符串类型。
TEXT 字段
- 仅仅当字符数量可能超过20000个的时候,才可以使用TEXT类型来存放字符类数据,因为所有MySQL数据库都会使用UTF8字符集。所有使用TEXT类型的字段必须和原表进行分拆,与原表主键单独组成另外一个表进行存放。如无特殊需要,严禁开发人员使用MEDIUMTEXT、TEXT、LONGTEXT类型
时间字段
- 时间统一格式:
YYYY-MM-DD HH:MM:SS
- 尽量使用 TIMESTAMP 而非 DATETIME
- 时间字段使用时间日期类型,不要使用字符串类型存储,
日期使用DATE类型
,年使用YEAR类型
,日期时间使用DATETIME
- 所有需要精确到时间(时分秒)的字段均使用DATETIME,不要使用TIMESTAMP类型
- 时间类型尽量选取
timestamp
。因为datetime占用8字节
,timestamp仅占用4字节
,但是范围为1970-01-01 00:00:01到2038-01-01 00:00:00。更为高阶的方法,选用int来存储时间,使用SQL函数unix_timestamp()
和from_unixtime()
来进行转换。
IP字段
- 业务中IP地址字段推荐使用int类型,不推荐用char(15)。因为int只占4字节,可以用如下函数相互转换,而char(15)占用至少15字节。一旦表数据行数到了1亿,那么要多用1.1G存储空间。 SQL:select inet_aton(‘192.168.2.12’); select inet_ntoa(3232236044); PHP: ip2long(‘192.168.2.12’); long2ip(3530427185);
INT字段
- 尽量使用 INT 而非 BIGINT,如果非负则加上 UNSIGNED(这样数值容量会扩大一倍),当然能使用 TINYINT、SMALLINT、MEDIUM_INT 更好。
- 如无特殊需要,存放整型数字使用
UNSIGNED INT
型。整型字段后的数字代表显示长度 - 合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。
类型 | 字节 | 表示范围 |
---|---|---|
tinyint | 1 | 无符号值: 0~255 ; 有符号值: -128~127 |
smallint | 2 | 无符号值: 0~65536 ; 有符号值: -32768~32767 |
mediumint | 3 | 无符号值: 0~16777215 ; 有符号值: -8388608~8388607 |
int | 4 | 无符号值: 0~4294967295 ; 有符号值: -2147483648~2147483647 |
bigint | 8 | 无符号值: 0~((2³²×²)-1) ; 有符号值:-(2³²×²)/2 ~ (2³²×²)/2-1 |
自增字段
- 表中的自增列(
auto_increment
属性),推荐使用bigint
类型。因为无符号int
存储范围为-2147483648~2147483647
(大约21亿左右),溢出后会导致报错。
金钱字段
- 存储金钱的字段,建议用int,程序端乘以100和除以100进行存取。因为int占用4字节,而double占用8字节,空间浪费。
带小数字段
- 对于精确浮点型数据存储,需要使用
DECIMAL
,严禁使用FLOAT
和DOUBLE
- 小数类型为
decimal
,禁止使用 float 和 double。 说明:float 和 double 在存储的时候,存在精度损失的问题,很可能在值的比较时,得到不 正确的结果。如果存储的数据范围超过 decimal 的范围,建议将数据拆成整数和小数分开存储。
索引
命名
- 命名有意义,只能使用英文字母、数字、下划线,使用小写字母,长度32个字符以内
- _ind,各部分以下划线 () 分割。
- 如: sample 表 member_id 上的索引:sample_mid_ind。
基本原则
- 索引占磁盘空间,不要重复的索引,尽量短
- 只给常用的查询条件加索引
- 过滤性高的列建索引,取值范围固定的列不建索引
- 唯一的记录添加唯一索引
- 频繁更新的列不要建索引
- 不要对索引列运算
- 同样过滤效果下,保持索引长度最小
- 合理利用组合索引,注意索引字段先后顺序
- 多列组合索引,过滤性高的字段最前
order by
字段建立索引,避免 filesort- 组合索引,不同的排序顺序不能使用索引
<>!=
无法使用索引
索引其他规范
- 索引必须创建在索引选择性选择性较高的列上,选择性的计算方式为:
select count(distinct(col_name))/count(*) from tb_name;
如果结果小于0.2
,则不建议在此列上创建索引,否则大概率会拖慢SQL执行 - 组合索引的首字段,必须在
where
条件中,对于确定需要组成组合索引的多个字段,建议将选择性高的字段靠前放 - Text类型字段如果需要创建索引,必须使用前缀索引
- 单张表的索引数量理论上应控制在
5个以内
。经常有大批量插入、更新操作表,应尽量少建索引 ORDER BY
,GROUP BY
,DISTINCT
的字段需要添加在索引的后面,形成覆盖索引- 尽量使用
Btree
索引,不要使用其它类型索引 - 业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。
- 超过三个表禁止 join。需要 join 的字段,数据类型必须绝对一致;多表关联查询时, 保证被关联的字段需要有索引。即使双表 join 也要注意表索引、SQL 性能。
- 在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据 实际文本区分度决定索引长度即可。 说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90%以上,可以使用
count(distinct left(列名, 索引长度))/count(*)
的区分度来确定。 - 【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
- 索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。
- 如果有 order by 的场景,请注意利用索引的有序性。
order by
最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现 file_sort 的情况,影响查询性能。正例:where a=? and b=? order by c;
索引:a_b_c 反例:索引中有范围查找,那么索引有序性无法利用,如:WHERE a>10 ORDER BY b;
索引 a_b 无法排序。 - 利用覆盖索引来进行查询操作,避免回表。 说明:如果一本书需要知道第11章是什么标题,会翻开第11章对应的那一页吗?目录浏览一下就好,这个目录就是起到覆盖索引的作用。正例:能够建立索引的种类:主键索引、唯一索引、普通索引,而覆盖索引是一种查询的效果,用explain的结果,extra列会出现:using index。
- 利用延迟关联或者子查询优化超多分页场景。 说明:MySQL并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回 N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写。正例:先快速定位需要获取的 id 段,然后再关联:
SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
- SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好。说明:
- consts 单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。
- ref 指的是使用普通的索引(normal index)。
- range 对索引进行范围检索。 反例:explain 表的结果,
type=index
,索引物理文件全扫描,速度非常慢,这个 index 级 别比较 range 还低,与全表扫描是小巫见大巫。
- 建组合索引的时候,区分度最高的在最左边。 正例:如果
where a=? and b=?
,a 列的几乎接近于唯一值,那么只需要单建 idx_a 索引即可。说明:存在非等号和等号混合判断条件时,在建索引时,请把等号条件的列前置。如:where a>? and b=?
那么即使 a 的区分度更高,也必须把 b 放在索引的最前列。防止因字段类型不同造成的隐式转换,导致索引失效。 - 创建索引时避免有如下极端误解
- 认为一个查询就需要建一个索引。
- 认为索引会消耗空间、严重拖慢更新和新增速度。
- 认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。
- 单个索引中每个索引记录的长度不能超过64KB。
- 单个表上的索引个数不能超过7个。
- 在建立索引时,多考虑建立联合索引,并把区分度最高的字段放在最前面。如列userid的区分度可由
select count(distinct userid)
计算出来。 - 在多表join的SQL里,保证被驱动表的连接列上有索引,这样join执行效率最高。
- 建表或加索引时,保证表里互相不存在冗余索引。对于MySQL来说,如果表里已经存在
key(a,b)
,则key(a)
为冗余索引,需要删除。 - 限制每张表上的索引数量,建议单张表索引不超过5个 索引并不是越多越好的,索引可以提高效率同样可以降低效率 索引可以增加查询效率,但同样也会降低插入和更新的效率
- 禁止给表中的每一列都建立单独的索引
- Innodb是按照主键索引的顺序来组织表的。每个
Innodb
表必须有一个主键 不使用更新频繁的列作为主键,不使用多列主键 不使用uuid、MD5、hash,字符串列作为主键 主键建议使用主键自增 - 常见的索引列建议:
select
、update
、delete
语句的where从句
中的列 包含在order by
、group by
、destinct
中的字段 多表join的关联列 - 如何选择索引列的顺序: 联合索引是从做到右的顺序来实现的
- 区分度最高的列放在联合索引的最左侧
- 尽量把字段长度小的列放在联合索引的最左侧
- 使用最频繁的列放到联合索引的左侧
- 避免建立冗余索引和重复索引 重复索引:如
primary key(id)
、index(id)
、unique index(id)
冗余索引:如index(a,b,c)
、index(a,b)
、index(a)
- 对于频繁的查询优先使用覆盖索引 覆盖索引:就是包括了所有查询字段的索引 优点:避免Innodb表进行索引的二次查找,可以把随机IO变为顺序IO加快查询效率
- 尽量避免使用外键 不建议使用外键约束,但一定在表与表之间的关联键上建立索引 外键可用于保证数据的参照完整性,但建议在业务端实现 外键会影响父表和子表的写操作从而降低性能
约束
- 不要使用外键约束,如果数据存在外键关系,请在程序层面实现。
- 主键最好是无意义的,由 Sequence 产生的 ID 字段,类型为 number,不建议使用组合主键。
- 若要达到唯一性限制的效果,不要创建 uniqueindex,必须显式创建普通索引和约束 (pk 或 uk),即先创建一个以约束名命名的普通索引,然后创建一个约束,用 using index … 指定索引。
- 当删除约束的时候,为了确保不影响到 index,最好加上 keep index 参数。
- 主键的内容不能被修改。
- 外键约束一般不在数据库上创建,只表达一个逻辑的概念,由程序控制。
- 当万不得已必须使用外健的话,必须在外健列创建 INDEX。
- 主键的内容不能被修改。
- PK应该是有序并且无意义的,尽量由开发人员自定义,且尽可能短,使用自增序列。
- 表中除PK以外,还存在唯一性约束的,可以在数据库中创建以“uidx_”作为前缀的唯一约束索引。
- PK字段不允许更新。
- 禁止创建外键约束,外键约束由应用控制。
- 如无特殊需要,所有字段必须添加非空约束,即not null。
- 如无特殊需要,所有字段必须有默认值。
- 主键约束: pk 结尾,pk;
- unique 约束:uk 结尾,uk;
- check 约束: ck 结尾,ck;
- 外键约束: _fk 结尾,以 pri 连接本表与主表,pri_fk;
其他
本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 学弟不想努力了!
评论