数据库设计规范

数据库设计规范数据库的重要性不言而喻。对程序员来说跟数据库打交道更是家常便饭。数据库给开发带来了巨大的便利。我们或多或少的知道一些数据库设计规范,但并不全面。今天我就简单整理一下,帮自己做个总结梳理,也希望可以帮到小伙伴们。数据库设计规范包括命名规范、库表基础规范、字段规范、索引规范和SQL设计规范。1.命名规范1.1库名、表名、字段名禁止使用MySQL保留字。1.2库名、表名、字段名使…

大家好,又见面了,我是你们的朋友全栈君。

数据库的重要性不言而喻。对程序员来说跟数据库打交道更是家常便饭。数据库给开发带来了巨大的便利。我们或多或少的知道一些数据库设计规范,但并不全面。今天我就简单整理一下,帮自己做个总结梳理,也希望可以帮到小伙伴们。

数据库设计规范包括命名规范、库表基础规范、字段规范、索引规范和SQL设计规范。

1. 命名规范

1.1 库名、表名、字段名禁止使用MySQL保留字。

1.2 库名、表名、字段名使用常用英语而不要使用编码,需见名知意,命名与业务、产品线等相关联。
中文词汇的英语翻译可以参考常用术语来选择相应的英文词汇。

1.3 库名、表名、字段名必须是名词的复数形式,并且使用小写字母,多个名词采用下划线分割单词。
MySQL有配置参数lower_case_table_names=1,即库表名以小写存储,大小写不敏感。如果是0,则库表名以实际情况存储,大小写敏感;如果是2,以实际情况存储,但以小写比较。
如果大小写混合使用,可能存在abc、Abc、ABC等多个表共存,容易导致混乱。
字段名显示区分大小写,但实际使⽤时不区分,即不可以建立两个名字一样但大小写不一样的字段。
为了统一规范, 库名、表名、字段名使用小写字母,不允许-号。

1.4 库名、表名、字段名禁止超过32个字符。

库名、表名、字段名支持最多64个字符,但为了统一规范、易于辨识以及减少传输量,禁止超过32个字符

1.5 索引命名规则

索引按照idx_table_column1_column2。其中table是建立索引的表名,column1和column2是建立索引的字段名。
索引名限制在32个字符内。当索引名超过32字符时,可用缩写来减少索引名的长度,如description –> desc;information –> info;address –> addr等。

1.6 主键、外键命名规则

主键按照PK_table的规则命名,其中table为数据库表名。
唯一键按照UK_table_column的规则命名。其中table为数据块表名,column为字段名。
外键按照FK_parent_child_nn的规则命名。其中parent为父表名,child为子表名,nn为序列号。

2. 库表基础规范

2.1 使用InnoDB存储引擎。

MySQL 5.5版本开始默认存储引擎就是InnoDB,5.7版本开始,系统表都放弃MyISAM了。

2.2 表字符集使用UTF8MB4字符集,校验字符集使用utf8mb4_general_ci。

UTF8字符集存储汉字占用3个字节,存储英文字符占用一个字节
校对字符集使用默认的utf8mb4_general_ci。特别对于使用GUI工具设计表结构时,要检查它生成的SQL定义
连接的客户端也使用utf8,建立连接时指定charset或SET NAMES UTF8;。
如果遇到EMOJ等表情符号的存储需求,可申请使用UTF8MB4字符集

2.3 所有表都要添加注释,除主键外的字段都需要添加注释

类status型需指明主要值的含义,如’0-离线,1-在线’

2.4 控制单表字段数量

单表字段数上限30左右,再多的话考虑垂直分表,一是冷热数据分离,二是大字段分离,三是常在一起做条件和返回列的不分离。
表字段控制少而精,可以提高I/O效率,内存缓存更多有效数据,从而提高响应速度和并发能力,后续ALTER TABLE也更快。

2.5 所有表都必须显式指定主键

主键尽量采用自增方式,InnoDB表实际是一棵索引组织表,顺序存储可以提高存取效率,充分利用磁盘空间。还有对一些复杂查询可能需要自连接来优化时需要用到。
只有需要全局唯一主键时,使用外部自增id服务
如果没有主键或唯一索引,UPDATE/DELETE是通过所有字段来定位操作的行,相当于每行就是一次全表扫描
少数情况可以使用联合唯一主键,需与DBA协商
对于主键字段值是从其它地方插入(非自己使用AUTO_INCREMENT生产),去掉AUTO_INCREMENT定义。比如一些31天表、历史月份表上,不要AUTO_INCREMENT属性;还有,必须通过全局id服务获取的主键,也要去掉AUTO_INCREMENT定义。

2.6 不强制使用外键参考

即使2个表的字段有明确的外键参考关系,也不使用FOREIGN KEY,因为新纪录会去主键表做校验,影响性能。

2.7 适度使用视图,禁止使用存储过程、触发器和事件

使用视图一定程度上也是为了降低代码里SQL的复杂度,但有时候为了视图的通用性会损失性能(比如返回不必要的字段)。
存储过程(PROCEDURE)虽然可以简化业务端代码,在传统企业写复杂逻辑时可能会用到,而在互联网企业变更是很频繁的,在分库分表的情况下要升级一个存储过程相当麻烦。又因为它是不记录log的,所以也不方便调试性能问题。如果使用过程,一定考虑如果执行失败的情况。
触发器(TRIGGER)也是同样,但也不应该通过它去约束数据的强一致性,MySQL只支持“基于行的触发”,也就是说,触发器始终是针对一条记录的,而不是针对整个sql语句的,如果变更的数据集非常大的话,效率会很低。掩盖一条SQL背后的工作,一旦出现问题将是灾难性的,但又很难快速分析和定位。再者需要DDL时无法使用pt-osc工具。放在TRANSACTION中执行。
事件(EVENT)也是一种偷懒的表现,目前已经遇到数次由于定时任务执行失败影响业务的情况,而且MySQL无法对它做失败预警。建立专门的 job scheduler 平台。

2.8 单表数据量控制在5000万以内

表字段数量不要超过20个,如果有需要建立主副表,主键一一关联,避免单行数据过多以及修改记录binlog ROW模式导致文件过大。
特别对于有一个text/blob或很大长度的varchar字段时,更应考虑单独存储。但也要注意查询条件尽量放在一个表上。

2.9 尽量只存储单一实体类型的数据

2.10 数据库中不允许存储明文密码

所有的密码、scret key和SSH key等类似的保密信息,必须经过非对称加密,再保存到数据库中。

2.11 尽量符合数据库的几个范式。

3. 字段规范

3.1 char、varchar、text等字符串类型定义

对于长度基本固定的列,如果该列恰好更新又特别频繁,适合char。 utf8mb4字符集下,尽量使用varchar。varchar虽然存储变长字符串,但不可太小也不可太大。UTF8最多能存21844个汉字,或65532个英文

varbinary(M)保存的是二进制字符串,它保存的是字节而不是字符,所以没有字符集的概念,M长度0-255(字节)。只用于排序或比较时大小写敏感的类型,不包括密码存储

text类型与varchar都类似,存储可变长度,最大限制也是2^16,但是它20bytes以后的内容是在数据页以外的空间存储(row_format=dynamic),对它的使用需要多一次寻址,没有默认值。 一般用于存放容量平均都很大、操作没有其它字段那样频繁的值。网上部分文章说要避免使用text和blob,要知道如果纯用varchar可能会导致行溢出,效果差不多,但因为每行占用字节数过多,会导致buffer_pool能缓存的数据行、页下降。另外text和blob上面一般不会去建索引,而是利用sphinx之类的第三方全文搜索引擎,如果确实要创建(前缀)索引,那就会影响性能。凡事看具体场景。另外尽可能把text/blob拆到另一个表中

BLOB可以看成varbinary的扩展版本,内容以二进制字符串存储,无字符集,区分大小写,有一种经常提但不用的场景:不要在数据库里存储图片。
当字段定义为字符串形时建议使用varchar而不用nvarchar。

3.2 int、tinyint、decimal等数字类型定义

使用tinyint来代替enum和boolean。enum类型在需要修改或增加枚举值时,需要在线DDL,成本较高;enum列值如果含有数字类型,可能会引起默认值混淆。tinyint使用1个字节,一般用于status、type、flag的列。
建议使用unsigned存储非负数值,相比不使用unsigned,可以扩大一倍使用数值范围。

int使用固定4个字节存储,int(11)与int(4)只是显示宽度的区别。但是定义是bigint(20), int(11),不要随便改动这个显示宽度,c++里面需要这个长度去截取字段。
使用decimal代替float/double存储精确浮点数。对于货币、金额这样的类型,使用decimal,如 decimal(9,2)。float默认只能精确到6位有效数字。

3.3 timestamp与datetime选择

datetime和timestamp类型所占的存储空间不同,前者5个字节(5.5是8字节),后者4个字节,这样造成的后果是两者能表示的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59,后者范围为 1970-01-01 08:00:01 到 2038-01-19 11:14:07 。所以 timestamp 支持的范围比 datatime 要小。

timestamp可以在INSERT/UPDATE行时,自动更新时间字段(如 set_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP),但一个表只能有一个这样的定义。

timestamp显示与时区有关,内部总是以UTC毫秒来存的,还受到严格模式的限制。
优先使用timestamp,datetime也没问题
默认时间,要么CURRENT_TIMESTAMP,要么’1970-01-02 01:01:01’,不要设置为”或0

WHERE条件里不要对时间列上使用时间函数
如果使用int类型存储时间戳,约定统一使用int unsigned default 0

3.4 建议字段都定义为NOT NULL

如果是索引字段,一定要定义为NOT NULL。因为NULL值会影响cordinate统计,影响优化器对索引的选择
虽然表中允许空(NULL)列,但是,空字段是一种比较特殊的数据类型。数据库在处理的时候,需要进行特殊的处理。如此的话,就会增加数据库处理记录的复杂性。当表中有比较多的空字段时,在同等条件下,数据库处理的性能会降低许多。
如果不能保证INSERT时该字段一定有值过来,解决方法:

通过设置默认值的形式,定义时使用DEFAULT ”或DEFAULT 0,来避免空字段的产生。
若一张表中,允许为空的列比较多,接近表全部列数的三分之一。而且,  这些列在大部分情况下,都是可有可无的。若数据库管理员遇到这种情况,建议另外建立一张副表,以保存这些列。

3.5 字段的默认值

所有字段在设计时,除timestamp、image、datetime、smalldatetime、uniqueidentifier、binary、sql_variant、binary、varbinary这些数据类型外,必须有默认值。字符型的默认值为一个空字符值串”;数值型的默认值为数值0;逻辑型的默认值为数值0;其中,系统中所有逻辑型中数值0表示为假;数值1表示为真。

datetime、smalldatetime类型的字段没有默认值,必须为NULL。

3.6 同一意义的字段定义必须相同

比如不同表中都有user_id字段,那么它的类型、字段长度要设计成一样

4. 索引规范

4.1 索引个数限制

索引是双刃剑,会增加维护负担,增大I/O压力,索引占用空间是成倍增加的
单张表的索引数量控制在5个以内,或不超过表字段个数的20%。若单张表多个字段在查询需求上都要单独用到索引,需要经过DBA评估。

4.2 避免冗余索引

InnoDB表是一棵索引组织表,主键是和数据放在一起的聚集索引,普通索引最终指向的是主键地址,所以把主键做最后一列是多余的。如crm_id作为主键,联合索引(user_id,crm_id)上的crm_id就完全多余
两个索引(a,b,c)、(a,b),后者为冗余索引。可以利用前缀索引来达到加速目的,减轻维护负担

4.3 没有特殊要求,使用自增id作为主键

主键是一种聚集索引,顺序写入。组合唯一索引作为主键的话,是随机写入,适合写少读多的表
主键不允许更新

4.4 索引尽量建在选择性高的列上

不在低基数列上建立索引,例如性别、类型。但有一种情况,idx_feedbackid_type (feedback_id, type),如果经常用type=1比较,而且能过滤掉90%行,那这个组合索引就值得创建。有时候同样的查询语句,由于条件取值不同导致使用不同的索引,也是这个道理。
索引选择性计算方法(基数 ÷ 数据行数)
Selectivity = Cardinality / Total Rows = select count(distinct col1)/count(*) from tbname,越接近1说明col1上使用索引的过滤效果越好
走索引扫描行数超过30%时,改全表扫描

4.5 最左前缀原则

MySQL使用联合索引时,从左向右匹配,遇到断开或者范围查询时,无法用到后续的索引列。比如索引idx_c1_c2_c3 (c1, c2, c3),相当于创建了(c1)、(c1,c2)、(c1,c2,c3)三个索引,WHERE条件包含上面三种情况的字段比较则可以用到索引,但像WHERE c1=a AND c3=c只能用到c1列的索引,像c2=b AND c3=c等情况就完全用不到这个索引
遇到范围查询(>、<、between、like)也会停止索引匹配,比如c1=a AND c2 > 2 AND c3=c,只有c1、c2列上的比较能用到索引,(c1, c2, c3)排列的索引才可能会都用上。

WHERE条件里面字段的顺序与索引顺序无关,MySQL优化器会自动调整顺序。

4.6 前缀索引

对超过30个字符长度的列创建索引时,考虑使用前缀索引,如idx_cs_guid2 (cs_guid(26))表示截取前26个字符做索引,既可以提高查找效率,也可以节省空间
前缀索引也有它的缺点是,如果在该列上ORDER BY或GROUP BY时无法使用索引,也不能把它们用作覆盖索引(Covering Index)
如果在varbinary或blob这种以二进制存储的列上建立前缀索引,要考虑字符集,括号里表示的是字节数

4.7 合理使用覆盖索引减少I/O

InnoDB存储引擎中,secondary index(非主键索引,又称为辅助索引、二级索引)没有直接存储行地址,而是存储主键值。如果用户需要查询secondary index中所不包含的数据列,则需要先通过secondary index查找到主键值,然后再通过主键查询到其他数据列,因此需要查询两次。
覆盖索引则可以在一个索引中获取所有需要的数据列,从而避免回表进行二次查找,节省I/O因此效率较高。
例如SELECT email,uid FROM user_email WHERE uid = xx,如果uid不是主键,适当时候可以将索引添加为index(uid,email),以获得性能提升。

4.8 尽量不要在频繁更新的列上创建索引

如不在定义了ON UPDATE CURRENT_STAMP的列上创建索引,维护成本太高(好在MySQL有insert buffer,会合并索引的插入)

4.9 修改表结构DROP COLUM时要注意

与这个字段相关的索引都会改变,变化是从原索引抽掉该字段定义。这种情况有可能导致部分索引重复或失效。

5. SQL设计规范

5.1 所有关键字的所有字母必须大写

5.2 杜绝直接SELECT *读取全部字段

即使需要所有字段,明确指定所需字段也能减少网络带宽消耗,能有效利用覆盖索引,表结构变更对程序基本无影响。

5.3 能确定返回结果只有一条时,使用LIMIT 1

在保证数据不会有误的前提下,能确定结果集数量时,多使用LIMIT,尽快地返回结果。

5.4 小心隐式类型转换

转换规则

两个参数至少有一个是NULL时,比较的结果也是NULL,例外是使用<、=、>对两个NULL做比较时会返回1,这两种情况都不需要做类型转换
两个参数都是字符串,会按照字符串来比较,不做类型转换
两个参数都是整数,按照整数来比较,不做类型转换
十六进制的值和非数字做比较时,会被当做二进制串
有一个参数是timestamp或datetime,并且另外一个参数是常量,常量会被转换为timestamp

有一个参数是decimal类型,如果另外一个参数是decimal或者整数,会将整数转换为decimal后进行比较,如果另外一个参数是浮点数,则会把decimal转换为浮点数进行比较
所有其他情况下,两个参数都会被转换为浮点数再进行比较。

如果一个索引建立在string类型上,如果这个字段和一个int类型的值比较,符合第 7 条。如phone定义的类型是varchar,但WHERE使用phone in (098890),两个参数都会被当成成浮点型。发生这个隐式转换并不是最糟的,最糟的是string转换后的float,MySQL无法使用索引,这才导致了性能问题。如果是user_id = ‘1234567’ 的情况,符合第 2 条,直接把数字当字符串比较。

5.5 禁止在WHERE条件列上使用函数

会导致索引失效,如LOWER(email),qq % 4。可放到等号右边的常量上计算
返回小结果集不是很大的情况下,可以对返回列使用函数,简化程序开发

5.6 使用LIKE模糊匹配,%不要放首位

会导致索引失效,有这种搜索需求是,考虑其它方案,如sphinx全文搜索

5.7 涉及到复杂SQL时,务必先参考已有索引设计,先EXPLAIN

简单SQL拆分,不以代码处理复杂为由。
比如OR条件: phone=’10000’ OR mobile=’10000’,两个字段各自有索引,但只能用到其中一个。可以拆分成2个sql,或者UNION ALL。
先EXPLAIN的好处是可以为了利用索引,增加更多查询限制条件

5.8 使用JOIN时,WHERE条件尽量使用充分利用同一表上的索引

如 SELECT t1.a, t2.b * FROM t1, t2 AND t1.a=t2.a AND t1.b=123 AND t2.c= 4,如果t1.c与t2.c字段相同,那么t1上的索引(b, c)就只用到b了。此时如果把WHERE条件中的t2.c=4改成t1.c=4,那么可以用到完整的索引
这种情况可能会在字段冗余设计(反范式)时出现
正确选取INNER JOIN和LEFT JOIN。不允许滥用LEFT JOIN。

5.9 少用子查询,改用JOIN

小于5.6版本时,子查询效率很低,不像Oracle那样先计算子查询后外层查询。5.6版本开始得到优化。

5.10 考虑使用UNION ALL,少使用UNION,注意考虑去重

UNION ALL不去重,而少了排序操作,速度相对比UNION要快,如果没有去重的需求,优先使用UNION ALL

如果UNION结果中有使用LIMIT,在2个子SQL可能有许多返回值的情况下,各自加上LIMIT。如果还有ORDER BY,请找DBA。

5.11 IN的内容尽量不超过200个

超过500个值使用批量的方式,否则一次执行会影响数据库的并发能力,因为单SQL只能且一直占用单CPU,而且可能导致主从复制延迟。

5.12 拒绝大事务

比如在一个事务里进行多个SELECT,多个UPDATE,如果是高频事务,会严重影响MySQL并发能力,因为事务持有的锁等资源只在事务ROLLBACK/COMMIT时才能释放。但同时也要权衡数据写入的一致性。不要再事务里面做除数据库以外的操作。

5.13 避免使用IS NULL, IS NOT NULL这样的比较

5.14 ORDER BY .. LIMIT

这种查询更多的是通过索引去优化,但ORDER BY的字段有讲究,比如主键id与time都是顺序递增,那就可以考虑ORDER BY id而非 time

5.15 c1 < a ORDER BY c2

与上面不同的是,ORDER BY之前有个范围查询,由前面的内容可知,用不到类似(c1,c2)的索引,但是可以利用(c2,c1)索引。另外还可以改写成JOIN的方式实现。

5.16 分页优化

建议使用合理的分页方式以提高分页效率,大页情况下不使用跳跃式分页
假如有类似下面分页语句:

  SELECT FROM table1 ORDER BY ftime DESC LIMIT 10000,10;
这种分页方式会导致大量的I/O,因为MySQL使用的是提前读取策略。
  推荐分页方式:

  SELECT FROM table1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 10
即传入上一次分页的界值。或者:

  SELECT * FROM table as t1 inner JOIN (SELECT id FROM table ORDER BY time LIMIT 10000,10) as t2 ON t1.id=t2.id

5.17 COUNT计数

首先COUNT()、COUNT(1)、COUNT(col1)是有区别的,COUNT()表示整个结果集有多少条记录,COUNT(1)表示结果集里以主键统计数量,绝大多数情况下COUNT()与COUNT(1)效果一样的,但COUNT(col1)表示的是结果集里col1列NOT NULL的记录数。优先采用COUNT()

大数据量COUNT是消耗资源的操作,甚至会拖慢整个库,查询性能问题无法解决的,应从产品设计上进行重构。例如当频繁需要COUNT的查询,考虑使用汇总表
遇到DISTINCT的情况,GROUP BY方式可能效率更高。

5.18 DELETE、UPDATE语句改成SELECT再EXPLAIN

SELECT最多导致数据库慢,写操作才是锁表的罪魁祸首

5.19 减少与数据库交互的次数,尽量采用批量SQL语句

INSERT … ON DUPLICATE KEY UPDATE …,插入行后会导致在一个唯一索引或主键中出现重复值,则执行旧行UPDATE,如果不重复则直接插入,影响1行。

REPLACE INTO类似,但它是冲突时删除旧行。INSERT IGNORE相反,保留旧行,丢弃要插入的新行。

INSERT INTO VALUES(),(),(),合并插入。

5.20 杜绝危险SQL

去掉WHERE 1=1这样无意义或恒真的条件,如果遇到UPDATE/DELETE或遭到SQL注入就恐怖了
SQL中不允许出现DDL语句。一般也不给予CREATE/ALTER这类权限,但阿里云RDS只区分读写用户

5.21 是否应该ORDER BY主键

许多排序的场景,如果主键id是增长的,如果ORDER BY create_time查询慢,有可能使用了filesort,此时最简单的办法是看能否换成ORDER BY id,因为id作为主键是递增的,并且附带在了每个二级索引后面。
但是也要谨慎使用 ORDER BY id,特别是在EXPLAIN结果看到filesort的情况下,优化器极有可能放弃这个filesort,而选择了它所认为更高效的扫描方式,实则更慢。

5.22 使用正确的表

比如要统计昨天的数据这类业务较多,是否可以设计一个昨天表,不在31天表上统计,在月份表上统计也行。或者其它组已经有“半统计”的数据,从他们那抽取数据,而不是在原始数据上统计。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/158096.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 前端缓存方案「建议收藏」

    前端缓存方案「建议收藏」前端几种本地缓存机制_蜗牛小前的博客-CSDN博客_前端本地缓存在漫长的前端开发过程中,我们常用的几种本地缓存机制:Cookie,LocalStorge,SessionStorge1.Cookie的特点1)cookie的大小受限制,cookie大小被限制在4KB,不能接受像大文件或邮件那样的大数据。2)只要有请求涉及cookie,cookie就要在服务器和浏览器之间来回传送(这解释为什么本地文件不能测试cookie)。而且coo…https://blog.csdn.net/weixin_397170..

    2022年10月28日
  • Socat虚拟pty

    命令:socatptypty问题:从pty1到pty2的数据,也会被pty1所接收。

  • 最近需要看的博客

    最近需要看的博客

  • linuxtop命令详解(xargs命令详解)

    查看多核CPU命令 mpstat-PALL 和 sar-PALL  说明:sar-PALL>aaa.txt  重定向输出内容到文件aaa.txt top命令经常用来监控linux的系统状况,比如cpu、内存的使用,程序员基本都知道这个命令,但比较奇怪的是能用好它的人却很少,例如top监控视图中内存数值的含义就有不少的曲解。本文通过一个运行中的WEB服务器的top监控截图,讲

  • OutputStreamWriter_input用法

    OutputStreamWriter_input用法InputStreamReader和OutputStreamWriter类用法简介。一、InputStreamReader类InputStreamReader将字节流转换为字符流。是字节流通向字符流的桥梁。如果不指定字符集编码,该解码过程将使用平台默认的字符编码,如:GBK。构造方法:InputStreamReaderisr=newInputStreamRead…

  • Python–socketserve源码分析(一)

    classThreadingTCPServer(ThreadingMixIn,TCPServer):pass实现原理:s=socketserver.ThreadingTCPServer(参

    2021年12月18日

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号