领先的免费Web技术教程，涵盖HTML到ASP.NET

网站首页 > 知识剖析 正文

[规范]数据库SQL规范（数据库sql使用教程）

nixiaole 2024-11-17 14:23:41 知识剖析 19 ℃

1.建库规范

  1. 字符集使用utf8mb4，排序规则使用utf8mb4_unicode_ci
  2. 命名使用小写英文字母、数字及下划线组成，不能以数字开头
  3. 格式：{bu}_{业务信息}，需要分库的在后面追加 "_0001"分库标识，{业务信息}部分是可选，一个du下面有多个库时通过业务信息区分

2.建表规约

  1. 使用InnoDB存储引擎
  2. 数据库和表的字符集必须一致，而且所有表的字符集都要一致，全部使用utf8mb4
  3. 数据表和字段命名禁用保留关键字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留关键字。
  4. 表名、字段名必须使用小写字母、数字、下划线，表名的格式一般为: t_{模块名称}_{详细信息}，尽量使用全称避免缩写，需要分表的在后面追加 "_0001"分表标识。
  • 禁止出现数字开头，禁止两个下划线中间只出现数字，禁止出现下划线结尾
  • 禁止混用无意义有歧义的大小写如大小I和小写l以及数字
  • 禁止表名使用复数名词
  • 中间表和备份表的命名要以特殊标识为前缀，并以日期为后缀；

说明：中间表用于保留中间结果集，名称以“tmp_”开头，中间是源表名称或缩写，以创建日期为后缀，以“_”分隔，如中间表“tmp _account_tbluser_20160320”。

备份表用于备份或抓取源表快照，名称以“bak_”开头，中间是源表名称或缩写，以创建日期为后缀，以“_”分隔，如备份表“bak _ account_tbluser _20160320”

  1. 【强制】表、字段必须有COMMENT属性，注释内容详细且清晰易懂
  2. 【强制】表字段中业务ID 定义禁止使用UUID，必须为业务ID 如user_id ，且id的命名规则需要明确在表字段说明里面
  3. 【强制】金额字段为 bigint unsigned，单位为分，禁止使用 float、double、decimal。
  • 说明：处理金额的时候，为了避免四舍五入，一律使用分作为单位，采用bigint类型。显示元或万元交给前端进行处理。
  1. 【强制】时长字段为 bigint unsigned，单位为秒，禁止使用 float、double、decimal。
  • 说明：对于行程的总时长，等待时长等时长字段进行约定
  1. 【强制】里程字段为 bigint unsigned，单位为米，禁止使用 float、douwble、decimal。
  • 说明：对于行程的总里程，接驾距离等字段进行约定
  1. 禁止在大数据量表(100W)上直接新增字段，会引起锁表，严重影响业务。
  2. 表达是与否概念的字段，数据类型用 unsigned tinyint（1 表示是，0 表示否），值的内容要统一，所有应用值也要统一； 说明：任何字段如果为非负数，都用 unsigned。
  3. 小数类型为 decimal，禁止使用 float 和 double。

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

  1. 如果存储的字符串长度几乎相等，使用 char 定长字符串类型。
  2. varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
  3. 尽量避免使用BLOB/TEXT类型，需要使用BLOB/TEXT时不要有默认值。
  4. 字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：
  • 不是频繁修改的字段。
  • 不是唯一索引的字段。
  • 不是 varchar 超长字段，更不能是 text 字段。正例：各业务线经常冗余存储商品名称，避免查询时需要调用业务服务获取。
  1. 单表行数超过 500 万行或者单表总容量超过50GB，才推荐进行分库分表。说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
  2. 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。正例：无符号值可以避免误存负数，且扩大了表示范围。

对象

类型

字节

表示范围

tinyint

unsigned

1

无符号值：0 ~ 255

smallint

unsigned

2

无符号值：0 ~ 65535

int

unsigned

4

无符号值：0 ~ 43 亿

bigint

unsigned

8

无符号值：0 ~ 10 的 19 次方

【推荐】表中所有字段必须是NOT NULL属性，业务可以根据需要定义DEFAULT值或者 ''

【推荐】表中字段不允许使用ENUM、SET

【推荐】表字段推荐使用bigint类型替代int类型，防止以后范围超限

【推荐】多表关联查询时，保证被关联的字段需要有索引

【推荐】有时间精度要求的业务，推荐使用datetime(6)

3.索引规约

  1. 主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。

说明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称。

  1. 唯一索引要在建表时就指定。
  2. 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
  • 说明：不要认为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
  1. 业务主键唯一索引使用固定的索引名称uk_biz，方便识别业务主键。
  2. 建组合索引的时候，区分度最高的在最左边。

正例：如果 where a=? and b=?，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。

说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=?那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即建立组合索引 idx_d_c。

  1. 禁止在大数据量表(100W)上直接创建索引，会引起锁表，严重影响业务。
  2. 防止因字段类型不同造成的隐式转换，导致索引失效。
  3. 单个表上索引数量不超过7个
  4. 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
  • 说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
  1. 在 varchar 字段上建立索引时，根据实际文本区分度决定索引长度。没必要对全字段建立索引的需要指定索引长度。
  • 说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
  1. 如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
  • 正例：where a=? and b=? order by c; 索引：a_b_c
  • 反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引 a_b 无法排序。
  1. 【推荐】减少不必要的全局索引定义，全局索引维护代价很高，数据的增删改都需要维护全局索引，不适宜大量使用，数据的查询要尽量使用主键或只要数据能在分区内保证唯一即没必要使用全局索引；说明：对于分区表的索引定义，如果默认不加local关键字即为全局索引定义， 所 以 如 果 定 义 本 地 索 引 语 法 为 create index <index_name> on <table_name> (column, column) local。
  2. 【推荐】避免重复索引的创建；说明：多条语句可以共用同一个索引，其中某些语句只要覆盖索引前缀即可。如有两条语句条件分别为：a = ? and b = ?，和b = ?，那么索引idx_b_a就可以同时为两条语句使用

4.SQL语句

  1. 禁止SELECT语句使用*获取全部字段，查询哪些字段必须明确写明
  2. 禁止INSERT语句不指定字段名
  3. 禁止使用外键、触发器；

说明：业务与数据库逻辑耦合关系太强，业务变更需额外维护外键、触发器，应该在应用层实现外键和触发器的逻辑

  1. 禁止使用自定义函数、自定义类型、存储过程

说明：存储过程不利于定位问题，移植性差，另外从性能角度而言，存储过程SQL一般均是有着多表，或多判断等语句，这些语句本身性能消耗严重，执行耗时长，会导致系统并发性能下降，如果存储过程中有算数运算，也会导致严重的性能问题

  1. 禁止 force index|ignore index|straight_join|SQL_no_cache 在线上使用
  2. 禁止使用BEGIN…END，LOOP...END LOOP，REPEAT...UNTIL...END REPEAT， WHILE...DO...END WHILE等的复合语句；说明：复杂逻辑都应该放在应用内处理，避免写复杂SQL
  3. 页面搜索严禁左模糊或者全模糊；说明：SQL中包含like查询，一定要使用最左前缀匹配
  4. 字段与查询值类型需要保持一致，不要走默认类型转换，例如字段类型是varchar，查询语句条件用int
  5. SQL语句中对分区表的操作条件必须带分区键，禁止不带分区键扫描所有分区
  6. 利用覆盖索引来进行查询操作，来避免回表操作
  7. 禁止使用order by rand()语句
  8. 禁止使用for update或for update wait语句
  9. SELECT语句中使用WHERE或LIMIT，否则会扫全表
  10. 不推荐JOIN三个或三个以上的表
  11. 在多表连接的查询中，驱动表须要选择结果集较小的表
  12. 禁止写成多层子查询嵌套的SQL语句，推荐改写成表顺序连接的格式
  13. 禁止在INSERT|UPDATE|DELETE|REPLACE语句中进行多表连接操作
  14. 生产系统中，强烈不推荐使用外关联，包括左外关联，右外关联和全外关联
  15. 不要使用 count(列名)或 count(常量)来替代 count()，count()就是 SQL92 定义 的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

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

说明：count(distinct col) 计算该列除 NULL 之外的不重复数量。注意 count(distinct col1, col2) 如果其中一列全为NULL，那么即使另一列有不同的值，也返回为0。

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

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

说明：使用 ISNULL()来判断是否为 NULL 值。注意:NULL 与任何值的直接比较都为 NULL。

NULL<>NULL的返回结果是NULL，而不是false。

NULL=NULL的返回结果是NULL，而不是true。

NULL<>1的返回结果是NULL，而不是true。

  1. TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。

说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

  1. 减少使用非逻辑条件查询 !=, <>, not
  2. 减少聚合函数的使用
  3. 控制中间结果集大小

说明：有distinct、order by和group by子句的查询，中间结果集限制1万行以内，对于超过1万行的大结果集的排序、分组，需要放到程序端实现

  1. 数据订正时，删除和修改记录时，要先 select，避免出现误删除，确认无误才能执行更新语句。
  2. in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内。（可以用用 EXISTS ，NOT EXISTS 或 JOIN代替）

5. 表固定列

  id            bigint unsigned NOT NULL COMMENT '  主键  ',   -- 主键ID
  create_time   datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time   datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  del_state     tinyint unsigned NOT NULL DEFAULT '0' COMMENT '0 有效 1 已删除',
  PRIMARY KEY   (id)

id、create_time、update_time、del_state四个字段时每个表都必须有的固定列。

-- 对于策略配置类的表，必须得加上 creator 和 updator两个字段，其他类型表不用添加

 creator varchar(60) NOT NULL COMMENT '添加人', --如果是B端管理的表，则是新增或修改人的标识，如果C端用户表统一定义为System
 updator varchar(60) NOT NULL COMMENT '更新人', --如果是B端管理的表，则是新增或修改人的标识，如果C端用户表统一定义为System

6. 应用开发

  1. 系统变量禁止设置global级别，尤其是超时时间
  2. 禁止一个事务内大批量数据插入和修改；

说明：把大量记录的增删改操作放到一个事务内，不仅提交效率低，更有可能造成租户内存写满，所以要分批次提交这些更新，拆分成多个小事务提交，推荐一个事务处理的记录条数不超过2千行

反例：使用insert into select一次性从一张大表内导入几百万行数据

  1. 执行批量插入操作使用executeBatch的时候，JDBC连接串后要加rewriteBatchedStatements=true参数
  2. 执行批量更新操作使用executeBatch的时候，JDBC连接串后要加allowMultiQueries=true参数
  3. 线上禁止使用queryTimeout

说明：有可能和socketTimeout造成不一致导致得到错误结果

  1. sql条件参数化，禁止拼接sql
  2. 系统变量尽量在语句级别通过HINT方式指定，避免session级别设置
  3. 调用Connection对象的方法，不推荐在程序内直接调用命令

说明：例如完结事务，不推荐执行commit/rollback，推荐调用方法 Connection.commit()／ Connection.rollback()

  1. 业务要有重试逻辑

说明：应用中主动捕获失败事务并加入重试逻辑，防止由于集群合并或者副本切主带来的杀事务的影响

  1. 数据库脚本统一存放在的db目录下，按脚本类型ddl和dml分开存放

Tags：

猜你喜欢

最近发表
标签列表