实践_数据库规范

数据库规范

规范背景与目的

MySQL数据库与oracle、sqlserver等数据库相比，有其内核上的优势与劣势。我们在使用MySQL数据库的时候需要遵循一定规范，扬长避短。本规范旨在帮助或指导技术人员做出适合线上业务的数据库设计。在数据库表设计、SQL编写等方面予以规范，从而为业务系统稳定、健康地运行提供保障。

数据库设计

以下所有规范会按照【高危】、【强制】、【建议】三个级别进行标注，遵守优先级从高到低。

数据库规范

命名规范

• 【强制】MySQL数据库表名、字段名必须使用小写字母或数字，禁止出现数字开头禁止两个下划线中间只出现数字。说明：MySQL在Windows下不区分大小写，但在Linux下默认是区分大小写。
• 【强制】不要使用数据库保留关键字和其它语言的保留关键字。如：desc、range、match。
• 【强制】字段名的名称采用有含义的单词编写，单词之间用“_“分割。如：client_id。
• 强制】各表之间相同含义的字段、类型定义要完全相同（包括默认值等）。不然连表查询的时候用不上索引。
• 【强制】建表必须有comment说明表及字段含义。
• 【强制】临时库、临时表名必须以 tmp_ 为前缀并以日期为后缀。
• 【强制】备份库、备份表名必须以 bak 为前缀并以日期为后缀。
• 【建议】关联字段建议命令规则是 “关联表名” +“关联字段“，中间用“_”分开。如：role_id，user_id。
• 【建议】普通索引，建议使用 IDX+表名+索引字段名，中间用“_”分开。
• 【建议】唯一索引，建议使用 UK+表名+索引字段名，中间用“_”分开。

表设计规范

• 【强制】禁止在表中建立预留字段 。
• 【强制】禁止在数据库中存储图片，文件等大的二进制数据 。
• 【建议】需要关联查询时，关联的表建议不超过3张，尤其减少大数据量表之间的关联。为减少表之间的关联，可以适当考虑反范式设计，不会变化的字段冗余保存。

字段设计规范

• 【强制】凡是可能建索引的字段、必须定义为not null，可以设置默认值；
• 【强制】任何字段如果为非负数，必须是unsigned，对于boolean类型，MYSQL使用 tinyint unsigned ，0代表false，1代表true，字段非空，根据业务要求设置默认值；说明：无符号值可以避免误存负数，且扩大了表示范围 例如：tinyint取值范围-128~127，tinyint unsigned取值范围是0~255
• 【强制】小数类型为decimal，禁止使用float和double。 说明：在存储的时候，float和double都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小数并分开存储。
• 【强制】如果存储的字符串长度几乎相等，使用char定长字符串类型，如open_id字段。如果字符串长度不确定使用varchar，字段长度根据需要指定。如果varchar长度大于5000，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
• 【强制】数据库主键优先考虑使用递增数字，例如雪花算法生成ID或数据库自带自增序列生成ID；
• 【强制】mysql使用 tinyint代替enum类型。
• 【强制】审计字段：凡是实体信息表，都强制要求记录审计信息，表中必须包含creator_id，created_time，modifier_id，modified_time，deleted字段； deleted设置为非空，默认值0
• 【建议】时间类型，MYSQL使用datetime；因为timestamp 最多到2038年。
• 【建议】业务数据使用逻辑删除。说明：逻辑删除在数据删除后可以追溯到行为操作。不过会使得一些情况下的唯一主键变得不唯一，需要根据情况来酌情解决。

索引设计规范

• 【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
• 【建议】不建议在低基数列上创建索引，如：性别，状态等。
• 【建议】使用排序，分组的字段尽量创建索引。
• 【建议】优先选择数据类型较短的字段，经常更新的列（如update_time）不建议创建索引，不要表中的每一列都建立单独的索引。

sql规范

• 【 强制】禁止使用select * ，避免不必要的查询。
• 【强制】in里面参数的数量不超过1000。in字句中查询结果太多，也可以使用exists来替代，如：

   select name from A where id in(select id from A)
   可以修改为：
   select name from A where exists(select id from B where id = A.id)
  

• 【强制】避免在大表中使用模糊查询(like ‘%xxxx’或者like ‘%xxxx%’)，无法用到索引。
• 【强制】不要使用count(列名) 或count(常量) 来替代count()，count() 是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。
• 【强制】尽量避免在 where 子句条件字段中使用运算符表达式，如：where num/2 = n 可修改为 where num = 2*n。
• 【强制】避免update表锁，where使用的字段如果没有使用索引，会触发表锁。
• 【强制】对同一表的多次alter操作必须合并为一次操作。如：alter table t add column b
• 【建议】同字段or条件使用in代替，如：status=1 or status=2 写成 status in（1,2）或 status >= 1 and status <= 2
• 【建议】拆分复杂的大 SQL 为多个小 SQL；在明显不会有重复值时使用 UNION ALL 而不是UNION。

生产程序禁止使用的SQL语句

• 【高危】禁用update|delete t1 … where a=XX limit XX; 这种带limit的更新语句。因为会导致主从不一致，导致数据错乱。建议增加其他索引字段作为条件，分段删除。
• 【高危】禁止使用关联子查询，如update t1 set … where name in(select name from user where…); 效率极其低下。建议程序中先通过子查询的SQL，查询出结果，再带入到update语句中。
• 【强制】禁止联表更新语句，如update t1,t2 where t1.id=t2.id…
• 【强制】生产上如果需要创建临时表，强制使用 temporary 建表，避免因为临时表的大量操作导致只读库同步慢。示例 ：

CREATE TEMPORARY TABLE user (
`id` bigint(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站用户基本信息';

orm框架使用规范

• 【强制】避免sql注入，禁止依赖前端传递sql参数直接拼接sql，mybatis框架 xml中参数获取禁止使用${xx}。
• 【强制】避免硬编码，硬编码是指将可变变量用一个固定值来代替的方法，这样会造成以后修改变得非困难，有可能替换的时候漏换的情况。如以下情况：
  如果数据库字段修改，程序中也要修改，编辑器不会提示，其次不知道程序那些地方使用了该字段， 当类名改变，或者属性名改变，会导致该sql报错，且编辑器不会提示。
  解决方案：
  现在主流的ORM框架 Mybatis-plus 提供通过实体对象动态获取字段名称的方法，可以直接使用实体类字段名称属性，修改了数据库字段名，只需要修改对应po实体属性名。
• 【强制】避免循环批量操作数据库。
• 【强制】避免大事务, 或行数超过6万行，或者load data源文件大小超过128M事务将被数据库强制回滚。
  常见处理方案如：
  将查询select方法放到事务外；事务中避免远程调用；事务中避免一次性处理太多数据；非事务执行的数据库操作不用放在事务中；批处理程序避免在一个事务中执行

数据库大表操作规范

• 【强制】针对大表的操作的SQL语句需经过评审，识别风险。大部分的DDL操作都属于高风险操作，此类操作将引起长时间的锁表，如：修改字段类型、新增/删除字段、修改字段长度、转换字符集、修改null/not null操作、Optimize table等。
• 【建议】多个操作建议合并执行，如：alter table tb_test add (column1 char(1), column2 int)。
• 【强制】大表操作错开业务高峰期。

数据库连接配置

• 对于MySql数据库，原则上包含以下参数：

failOverReadOnly=false            #自动重连成功后，连接是否设置为只读
secondsBeforeRetryMaster=0        #连接转移到从库之后，驱动程序在尝试退回到主库之前等待的时间，0表示关闭，不退回到主库
queriesBeforeRetryMaster=0        #连接转移到从库之后，驱动程序在尝试退回到主库之前执行的查询数，0表示关闭，不退回到主库
serverTimezone=Asia/Shanghai      #时区配置，8.0必须配置
useUnicode=true                   #是否使用Unicode字符集
useSSL=false                      #关闭ssl协议
connectTimeout=10000              #数据库服务器建立socket连接时的超时，单位：毫秒
socketTimeout=60000               #socket操作（执行SQL的）超时，单位：毫秒

完整配置如：
jdbc:mysql://127.0.0.1:6379/?characterEncoding=UTF-8&failOverReadOnly=false&secondsBeforeRetryMaster=0&queriesBeforeRetryMaster=0&serverTimezone=Asia/Shanghai&useUnicode=true&useSSL=false&connectTimeout=10000

连接池配置示例如下：

druid 连接池配置示例：

spring:
   datasource:
      druid:
         max-active: 5    # 连接池最大容量，当使用中连接数量已经达到了连接池最大容量，后续线程获取连接需要等待其他线程释放连接
         min-idle: 1
         initial-size: 1   # 初始化大小
         max-wait: 10000 #获取连接等待超时时间
         test-while-idle: true  #当test-on-borrow为false时，连接池会判断连接是否处于空闲状态，如果是，则校验这条连接是否可用。
         test-on-borrow: true   #每次从连接池获取连接时校验，影响性能，但是DBA建议配置。可结合项目情况评估
         time-between-eviction-runs-millis: 30000  # 官方解释为空闲连接驱逐进程运行时间间隔毫秒值，实际测试为执行select 1的时间间隔（不同版本可能存在差异）。
         validation-interval: 30000   # 执行select 1校验时间间隔。不同版本，可能此配置无效，保险起见建议配置。
         min-evictable-idle-time-millis: 180000  #空闲连接驱逐进程运行时，被驱逐连接的最小空闲时间毫秒值。time-between-eviction-runs-millis + min-evictable-idle-time-millis <= 240000。
         validation-query: select 1  # 连接校验时执行的SQL
         validation-query-timeout: 1  # 执行校验（select 1）的超时时间。

hikari 连接池配置示例

spring:
   datasource:
      hikari:
      max-lifetime: 1600000 #1600 秒，单位毫秒，空闲连接最大存活时间，需要小于MySQL参数wait_timeout值(默认 1800 秒)
      minimum-idle: 1
      maximum-pool-size: 10
      auto-commit: true
      idle-timeout: 200000  #连接最大空闲时间，单位ms。
      validation-timeout: 1000  #执行校验（select 1）的超时时间，单位ms
      connection-timeout: 10000  #等待连接池分配连接的长时间，单位ms。
      keep-alive-time: 90000 #90 秒，单位毫秒，探活周期,需要配置小于 MySQL 参数wait_timeout 值（默认 1800 秒）且要小于 acs 超时时间 240 秒