数据库规范

java精彩汇总
2,703 阅读12分钟

数据库规范

命名规范

  1. 数据库、表、字段命名要能表意其对应的业务逻辑。
  2. 表名、列名、索引名，长度不超过32个字符。
  3. 数据库名全小写字母，多个单词以_连接，比如：family_plus,user。
  4. 表名全小写字母，使用单数名词，以t_开头，用_连接多个单词，比如：t_course,t_user_log。
  5. 日志表以_log结尾,统计表以_stat结尾，关联表以_mapping结尾。
  6. 视图用v_开头，比如：v_user。

  7. 唯一索引前缀为uniq_，普通索引前缀为idx_，索引名格式：前缀_列名_列名。

    比如：idx_activityId_targetId，uniq_userId_targetId_stageId。

  8. 字段名使用驼峰命名(camelCase)，比如：buildArea，这条可选，也可以使用_连接多个词语，比如：build_area。
  9. 字段名需要表达是否概念时，不能使用isXxx的方式命名，直接用业务含义命名，同时保持与entity中属性名一致，数据类型是tinyint（1=是,0=否）。
  10. 临时库、表名必须以tmp为前缀，并以日期为后缀。例如：tmp_test01_20180704。
  11. 备份库、表名必须以bak为前缀，并以日期为后缀。例如：bak_test01_20180704。

建表规范

  1. 表必须有主键，推荐使用UNSIGNED⾃增列作为主键。
  2. 表必须要有注释，禁⽌使用分区表。
  3. 表字符集通常指定为UTF8mb4，特殊情况可以使用UTF8等其他字符集，尽量保持字符集一致（字符集不一致可能导致索引失效）。
  4. 表的存储引擎，指定使用InnoDB引擎。
  5. 单表超过500万行数据要考虑做历史归档或分表。
  6. 禁⽌在数据库中存储图片、文件等大数据。
  7. 推荐使⽤HASH进行拆表，表名后缀使用⼗进制数，数字必须从0开始。
  8. 按⽇期时间分表需符合YYYY[MM][DD][HH]格式，例如：2018071601。年份必须用4位数字表示。例如按日分表user_20180709、按月分表user_201807。
  9. 采用合适的分库分表策略。例如千库十表、⼗库百表等。
  10. 表(除了关系表)必须有以下字段:
    `cuser` bigint NOT NULL  COMMENT '创建人',
`muser` bigint NOT NULL COMMENT '修改人',
`state` tinyint NOT NULL DEFAULT '1' COMMENT '记录状态值，1=已创建，2=已审核 ,3=已上架，0=删除'
`ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
`mtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

字段规范

  1. 列必须有注释。
  2. 列不能单独指定字符集。
  3. 列名禁止使用mysql保留字，如desc、 range、 match、 delayed等。
  4. 自增列不能设置默认值，表只能有一个自增列。
  5. 列需要存储emoji表情的，需将表编码设置成utf8mb4。
  6. 禁⽌在数据库中存储明文密码。
  7. 将⼤字段、访问频率低的字段拆分到单独的表中存储，分离冷热数据。

  8. 列必须是NOT NULL(特殊情况可找DBA申请)。

    在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

  9. 列允许适当冗余，以提高性能，但是必须考虑数据同步的情况。

    冗余字段应遵循：

    1）不是频繁修改的字段

    2）不是varchar超长字段，更不能是text字段

字段类型规范

  1. 如果能确定存储的字符串长度都相等或者几乎相等，使用定长char类型。
  2. 建议使用UNSIGNED存储非负数值。
  3. 建议使用INT UNSIGNED存储IPV4。
  4. char长度大于50，必须使用varchar类型。
  5. 禁止使用TEXT、BLOB类型。
  6. 区分使⽤DATETIME和TIMESTAMP。存储年使用YEAR类型，存储日期使用DATE类型。存储时间（精确到秒甚至微妙）建议使用TIMESTAMP类型，注意数据库时区调整对TIMESTAMP列的影响，建议数据库驱动指定时区。
  7. DATETIME和TIMESTAMP类型字段禁止使用0000-00-00 00:00:00作为DEFAUL值。

  8. varchar是可变长字符串，不预先分配存储空间，长度不要超过5000。

    如果存储长度大于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段的索引效率;

  9. 使用VARBINARY存储⼤小写敏感的变⻓字符串或二进制内容。

  10. 小数类型为Decimal，禁止使用 float 和 double。

    float和 double 在存储的时候，存在精度损失的问题，很可能在值比较时，得到不正确的结果。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小数分开存储，比如，跟钱有关的字段，可存储成分

索引规范

  1. 单表的索引不超过5个。
  2. 组合索引的列数不超过5列。
  3. 建组合索引的时候，区分度最高的在最左边(最左前缀原则)。合理创建联合索引，(a，b，c)相当于(a)、(a，b)、(a，b，c)。
  4. 联表查询时，JOIN列的数据类型必须相同，并且要建立索引。
  5. 索引大小不超过766字节。
  6. 索引不能冗余或者重复。
  7. 唯一键由3个以下字段组成，并且字段都是整型时，可使⽤唯⼀键作为主键。其他情况下，建议使⽤⾃增列或发号器作主键。

  8. 搜索严禁使用左模糊或全模糊，如果需要请走搜索引擎来解决。

    说明:索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。

  9. 如果有 order by 的场景,请注意利用索引的有序性。

    order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后,避免出现 file_sort 的情况,影响查询性能。

    正例:where a=? and b=? order by c; 索引:a_b_c

    反例:索引中有范围查找,那么索引有序性无法利用,如:WHERE a>10 ORDER BY b; 索引 a_b 无法排序。

  10. 根据实际查询需要，利用覆盖索引来进行查询操作，避免回表操作。

    一个查询语句的返回字段从索引中就能够取得，而不必从数据表中读取，这种情况称之为实现了索引覆盖(covering index)，比如包含多列的组合索引。

SQL语句规范

  1. INSERT语句必须指定字段列表,禁止使用 INSERT INTO TABLE VALUES(...)。
  2. 使⽤prepared statement（预编译语句），禁止拼接SQL，可以提升性能并避免SQL注入。
  3. 使用isnull()来判断是否为NULL值。
  4. 不得使用外键与级联，一切外键概念必须在应用层解决。
  5. 拆分复杂SQL为多个小SQL，避免⼤事务。
  6. 禁用select *，必须指定列。
  7. 减少与数据库交互次数，尽量采用批量SQL语句。
  8. 禁⽌隐式转换。数值类型禁止加引号，字符串类型必须加引号。
  9. 禁止使用INSERT INTO SELECT和UPDATE a,b等多表关联的DML语句格式。
  10. 禁止使用SELECT LOCK IN SHARE MODE和SELECT FOR UPDATE的预先占用锁资源的语句格式。
  11. 禁止在主库上执⾏后台管理和统计类功能的QUERY，必要时申请统计类从库。
  12. 禁⽌单条SQL语句同时更新多个表。

  13. 禁止负向查询，以及'%'开头的模糊查询。

    负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描。'%'开头的模糊查询，也会导致全表扫描。

  14. 禁⽌使⽤order by rand()。

  15. 禁止在WHERE条件的列上使用函数或者表达式。
  16. SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。
  17. 尽量不要使用存储过程、触发器、函数。
  18. ⽤IN代替OR。SQL语句中IN包含的值不应过多，应少于1000个。
  19. 获取⼤量数据时，建议分批次获取数据，每次获取数据少于5000条，结果集应⼩于1M。
  20. 索引字段上不要使用函数，函数会导致索引失效。
  21. Order By语句中使用的字段需合理的建立索引。

  22. 使用UNION All、IN代替OR。

    如果条件中有or，比如: userName='hehe' or age=14 ,要求所有列有索引才会走索引

  23. 不要使用count(列名)或count(常量)来替代count(*)，count(*)是 SQL92 标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关 。

    count(*)会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行。

  24. WHERE条件中，=与!=两边都不能有null值，否则判断会出错。

    需要对有NULL值的字段作比较时，mysql需要用ifnull(null,‘default_value’)，把NULL值转化为默认值再比较、

  25. 在代码中写分页查询逻辑时,若 count 为 0 应直接返回，避免执行后面的分页语句。

  26. 当某一列的值全是NULL时,count(col)的返回结果为0，但sum(col)的返回结果为NULL，因此使用sum()时需注意NPE问题。

    例如：可以使用如下方式来避免SUM()的NPE问题：SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;

操作行为规范

  1. 表结构变更必须通知DBA进⾏审核。
  2. 禁⽌有SUPER权限的应⽤程序账号存在。
  3. 禁⽌有DDL、DCL权限的应⽤程序账号存在。
  4. 重⼤项⽬的数据库⽅案选型和设计必须提前通知DBA参与。
  5. 批量导⼊、导出数据必须通过DBA审核，并在执行过程中观察服务。
  6. 批量更新数据，如UPDATE、DELETE操作，必须DBA进⾏审核，并在执⾏过程中观察服务。
  7. 产品出现非数据库导致的故障时，如被攻击，必须及时通DBA，便于维护服务稳定。
  8. 业务部⻔程序出现BUG等影响数据库服务的问题，必须及时通知DBA，便于维护服务稳定。
  9. 业务部⻔推⼴活动或上线新功能，必须提前通知DBA进行服务和访问量评估，并留出必要时间以便DBA完成扩容。
  10. 出现业务部门⼈为误操作导致数据丢失，需要恢复数据的，必须第一时间通知DBA，并提供准确时间点、误操作语句等重要线索。
  11. 提交线上建表改表需求，必须详细注明涉及到的所有SQL语句(包括SELECT、INSERT、DELETE、UPDATE)，便于DBA进行审核和优化。
  12. 对同⼀个表的多次ALTER操作必须合并为⼀次操作。
  13. 不要在MySQL数据库中存放业务逻辑。

数据库账号规范

  1. 业务程序必须实现读写分离，读写账号以后缀_w命名，只读账号以后缀_r命名。
  2. 程序账号权限只能是SELECT、INSERT、UPDATE、DELETE，除非特殊情况可以授予CREATE和DROP权限。
  3. DBA的账号采用每人一个，以贝壳网注册的邮箱前缀为名，例如：huangyong010，权限为ALL并且WITH GRANT OPTION。
  4. 开发以及其他业务人员的账号，以贝壳网注册的邮箱前缀为名，例如：zhangsan01，权限为SELECT且只允许访问申请的库表。
  5. 不同后端平台以及运维程序，需要使用不同的数据库账号，例如备份程序采用mysqlbackup命名，主从同步账号采用replication命名等。
  6. 数据库授权采用白名单IP方式，除非特殊情况否则不能授予带%的IP范围。
  7. 数据库授权粒度为库级别，除非特殊情况可以授权到表级别；MySQL的系统库如mysql、sys等不对外授权。
  8. 数据库中不允许有名字为"dba"的账号，初始化数据库后需要将该用户删除。
  9. 所有数据库账号的密码均不能为空，密码采用16位大小写字母和数字的组合。
  10. 数据库账号/密码不允许在邮件、企业微信群、家信群中公开，一经发现，DBA有权立即进行重置。
  11. 相同数据库账号的权限必须保持一致；主从数据库的账号授权保持一致。

SQL脚本自助审查

发送数据库脚本给DBA之前，开发者可自助检查SQL脚本是否符合规范，以免被DBA打回。


数据库设计原则

一. 核心原则

  • 不在数据库做运算
  • cpu计算务必须移至业务层
  • 控制列数量
  • 平衡范式与冗余
  • 拒绝大sql语句、大事务、大批量操作

二. 字段类原则

  • 用好数值类型
  • 字符转化为数字
  • 避免使用NULL字段
  • 少用text类型

三. 索引类原则

  • 合理使用索引
  • 字符字段必须建前缀索引
  • 不在索引做列运算
  • innodb主键推荐使用自增列
  • 不用外键

其他(Miscellaneous)

建表示例

CREATE TABLE IF NOT EXISTS`t_course` (  
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '课程ID',  
  `name` varchar(100) NOT NULL COMMENT '课程名称',  
  `cuser` bigint NOT NULL  COMMENT '创建人',  
  `muser` bigint NOT NULL COMMENT '修改人',  
  `state` tinyint NOT NULL DEFAULT '1' COMMENT '记录状态值，1=已创建，2=已审核 ,3=已上架，0=删除',  
  `ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',  
  `mtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',  
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '课程实体表'

explain 命令

使用explain分析SQL查询，根据explain分析结果进行索引优化。

explain返回各列的含义：

table：表名 
type:显示连接使用了何种类型。最好到最差的连接类型依次为const、eq_reg、ref、rang、index和All；
possible_keys:显示可能应用的这张表的索引。如果为空，没有可能的索引；
key：实际使用的索引。如果为NULL,则没有使用索引；
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好；
ref:显示索引的哪一列被使用了，如果可能的话，是一个常数；
rows：mysql认为必须检查的用来返回请求数据的行数。

SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好。

说明:

1) consts 单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。

2) ref 指的是使用普通的索引(normal index)。

3) range 对索引进行范围检索。

反例: explain 表的结果,type=index，索引物理文件全扫描，速度非常慢,这个 index 级别比 range 还低,与全表扫描是小巫见大巫


avatar
文章
阅读
粉丝