设计规范
【推荐】字段允许适当冗余,以提高查询性能,但必须考虑数据一致。冗余字段应遵循:
- 不是频繁修改的字段。
- 不是 varchar 超长字段,更不能是 text 字段
Good: “产品名”适当冗余,可以减少和产品表的关联 **Bad **: “产品修改时间”,产品描述,产品组件数
【推荐】数据库主键不使用自增(手动插入ID容易冲突,系统集成切割、合表自增都会有问题)
【推荐】字段尽量设置为 NOT NULL, 为字段提供默认值(节省空间,索引,count问题,条件需要考虑null还是空白等)
Good:字符型的默认值为一个空字符值串’’,数值型默认值为数值 0
Bad:status允许为null或空白
命名规则
名字默认规则:采用26个英文字母,多个单词下划线’_‘分割。不建议以复数形式进行命名。
【强制】表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型是tinyint ( 1表示是,0表示否)。
Good:逻辑删除的字段名 is_deleted,1 表示删除,0 表示未删除
【强制】表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间只 出现数字。数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。 说明:MySQL 在 Windows 下不区分大小写,但在 Linux 下默认是区分大小写。因此,数据库 名、表名、字段名,都不允许出现任何大写字母,避免节外生枝。
Good:health_user,rdc_config,level3_name Bad:HealthUser,rdcConfig,level_3_name
【强制】表名不使用复数名词。 说明:表名应该仅仅表示表里面的实体内容,不应该表示实体数量,对应于 DO 类名也是单数 形式,符合表达习惯。
【强制】禁用保留字,如 desc、range、match、delayed 等,请参考 MySQL 官方保留字
【强制】表必备以下三个字段。
字段名 类型 描述 必填 creator varchar(50) 创建人 是 creator_id int(12) 创建人ID create_time datetime 创建时间 是 modifier varchar(50) 修改人 是 modifier_id int(12) 修改人ID modify_time datetime 最后更新时间 是 is_delete tinyint(1) 是否删除,1-删除, 0-未删除,默认0 是 【强制】所有命名必须使用全名,有默认约定的除外,如果超过 30 个字符,使用缩写,请尽量名字易懂简短,如 description –> desc;information –> info; address –> addr 等
【推荐】如果修改字段含义或对字段表示的状态追加时,需要及时更新字段注释
类型规范
用尽量少的存储空间来存储一个字段的数据.
- 能用int的就不用char或者varchar
- 能用tinyint的就不用int
- 能用 varchar(20)的就不用varchar(255)
- 业务上必须有值的字段务必设计成必填字段。
- 【强制】小数类型为 decimal,禁止使用 float 和 double。(float 和 double 在存储的时候,存在精度损失的问题,很可能在值的比较时,得到不 正确的结果。如果存储的数据范围超过 decimal 的范围,建议将数据拆成整数和小数分开存储)
- 【强制】如果存储的字符串长度几乎相等,使用 char 定长字符串类型。(这种情况下char效率、存储都优于varchar)
- 【强制】varchar 是可变长字符串,不预先分配存储空间,长度不要超过 5000,如果存储长 度大于此值,定义字段类型为 text,独立出来一张表,用主键来对应,避免影响其它字段索 引效率。
- 【强制】表示boolean类型的都使用TINYINT(1),因为mysql本身是没有boolean类型的,在自动生成代码的时候,DO对象的字段就是boolean类型,例如 is_delete;其余所有时候都使用TINYINT(4)
- 【强制】时间字段使用时间日期类型,不要使用字符串类型存储,日期使用DATE类型,年使用YEAR类型,日期时间使用DATETIME
- 【建议】字符串VARCHAR(N), 其中 N表示字符个数,请尽量减少 N 的大小,例如:code VARCHAR(32);name VARCHAR(32);memo VARCHAR(512);
- 【建议】Blob 和 Text 类型所存储的数据量大,删除和修改操作容易在数 据表里产生大量的碎片,避免使用 Blob 或 Text 类型
- 【推荐】时间统一格式:‘YYYY-MM-DD HH:MM:SS’
关于索引
- 【建议】主键的名称以 pk_ 开头,后面加上唯一键以 uniq_ 或 uk_ 开头,普通索引以 idx_ 开头,一律使用小写格式,以字段的名称或缩写作为后缀。
- 【建议】单个表上的索引个数不能超过8个。
- 【建议】在多表join的SQL里,保证被驱动表的连接列上有索引,这样join执行效率最高。
- 【建议】建表或加索引时,保证表里互相不存在冗余索引(对于MySQL来说,如果表里已经存在key(a,b),则key(a)为冗余索引,需要删除)。
- 【强制】页面搜索严禁左模糊或者全模糊
- 【推荐】防止因字段类型不同造成的隐式转换,导致索引失效
- 【推荐】<>!=无法使用索引,尽量避免使用
SQL编写
- 【强制】程序端insert语句指定具体字段名称,不要写成insert into t1 values(…)
- 【强制】除静态表或小表(100行以内),DML语句必须有where条件,且使用索引查找
- 【强制】where条件里等号左右字段类型必须一致,否则无法利用索引。
- 【强制】WHERE 子句中禁止只使用全模糊的LIKE条件进行查找,必须有其他等值或范围查询条件,否则无法利用索引。
- 【强制】索引列不要使用函数或表达式,否则无法利用索引。 Bad: where UPPER(name)=‘ADMIN’或where user_id+2=10023。
- 【强制】禁止跨db的join语句。
- 【建议】不建议使用子查询,建议将子查询SQL拆开结合程序多次查询,或使用join来代替子查询。
- 【建议】线上环境,多表join不要超过5个表。
- 【建议】在多表join中,尽量选取结果集较小的表作为驱动表,来join其他表。
- 【建议】批量操作数据时,需要控制事务处理间隔时间,进行必要的sleep。
- 【建议】事务里更新语句尽量基于主键或unique key,如update … where id=XX;(否则会产生间隙锁,内部扩大锁定范围,导致系统性能下降,产生死锁。)
- 【建议】减少使用order by,和业务沟通能不排序就不排序,或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU,数据库的CPU资源是极其宝贵的。
- 【建议】包含了order by、group by、distinct这些查询的语句,where条件过滤出来的结果集请保持在1000行以内,否则SQL会很慢。
- 【推荐】每个字段和表必须提供清晰的注释
- 【建议】in操作能避免则避免,若实在避免不了,需要仔细评估 in 后边的集合元素数量,控1000 个之内
- 【建议】针对索引字段使用 >, >=, =, <, <=, IF NULL 和 BETWEEN 将会 使用索引,如果对某个索引字段进行 LIKE 查询,使用 LIKE ‘%abc%’ 不能使用索引,使用 LIKE ‘abc%’ 将能够使用索引
- 【建议】对于大数据量的查询,尽量避免在 SQL 语句中使用 order by 字句
- 【建议】连表查询的情况下,要确保关联条件的数据类型一致,避免嵌套子查询
- 【建议】对于连续的数值,使用 between 代替 in