(复制粘贴的时候结构乱了,先这样看吧,后期考虑使用markdown写可能会好些)
(以下数据库引擎默认为InnoDB)
0 命名相关
0.1.库表名
库名与应用名称尽量一致[阿里推荐]
表名,字段名必须使用小写字母或数字;禁止出现数字开头,禁止两个下划线中间只出现数字.[阿里强制]
表名不使用复数名词.[阿里强制]
表的命名最好加上"业务名称_表的作用",避免上云梯后,再与其他业务表关联时混淆[阿里推荐](尚未接触过云梯,此处仅供参考)
0.2.字段名
禁用保留字,如desc,range.[阿里强制](对于一些非官方的保留字,如name,password也是不推荐使用的,这类文字,可以试着写一个包含全部字段的查询sql放在Navicat上但不要执行,看看是否是蓝色,如为蓝色的字段名最好不要使用)
表必备三字段:id,gmt_create,gmt_modified(id为主键,类型为unsigned bigint,单表时自增1,分表时改为从tddl sequence取值,确保分表之间的全局唯一. gmt_create,gmt_modified均为datetime类型)[阿里强制]
个人认为直接将表格的id名称设为"id"不是一个明智的选择,原因在于没有区分度,尤其在涉及多表联合查询时不能从id上直接获取表的信息,十分容易混淆.一般而言,可以考虑将表格名格式设为t_xxx,xxx表示这个表的逻辑,而id的命名为xxx_id是不错的选择,如果xxx过长,可以考虑采取简称.如企业表t_group,id命名即为group_id.
唯一索引名为uk_字段名,普通索引名为idx_字段名.[阿里强制]
单表行数超过500万行或者单表容量超过2G,才推荐进行分库分表.[阿里推荐](尚未接触过这么大的数据量)
尽量避免出现null值的出现(即尽量将字段设为not null),原因是单独查询某一列的值时,如果一些行该字段为null,则返回结果集中不会体现.
1 code类型
1.1.综述
code类(含账号)设计本身的目的之一,是为了确定一行数据的唯一指向/联合唯一指向,以字母数字为主,如商品code,单据code,账号.
虽然id也能唯一指向,但id仅是一个数字,使用id有两个问题:1.没有额外的信息可供保存;2.对外展示不友好;所以除了一些较特殊的,仅在内部使用的表格,如关联表,对外展示数据的表格中会将id和code都包含.
code的字段,一般设为char或varchar类型,not null(作为指向的必要条件),长度则应在要求允许范围内尽量少,10长度能满足就不要设为15长度,因为code类型都是要设为索引的,长度过长既不利于保存,也不利于查询.
code大致上还可分为两类,可自定义类和规则自动生成类.前者如账号,后者如单据code.
1.2.自定义类
对于可自定义类,建议字段类型设为varchar,不论是插入性能还是查询性能,都是不如规则自动生成类的.插入方面,每次都需要将所有或条件范围内的code查重(即使你加入了unique key,但在插入前至少需要进行一次判断,提前告诉code输入人,否则根据你的前台设计会带来不同等程度的不好的用户体验).
1.3.规则自动生成类
对于规则自动生成类,建议字段类型设为char,虽然在InnoDB引擎上,varchar的许多性能特性要优于char类型,但规则自动生成类具有长度固定且不会太长的特点,是比较适用于char类型的.(如果存储的字符串长度几乎相等,使用char定长字符串类型.[阿里强制])目前我所用到的常见规则自动生成类,格式一般为:功能说明字段+日期(可选)+后缀数字,如其他出库单据号QTCK20170918000001,数据字典DICT0001.
不同于自定义字段每次需要查重需要较高的查询消耗,规则自动生成类可利用索引数字自增的方法较快获取后缀数字.具体有两种策略:
第一种是创建一个单独的记录最大后缀数字表,当如记录每个企业的岗位code,对于每个企业只需要一个这样的后缀记录,可以创建一个t_group_suffix,每个企业一行数据,在相关企业对应行中记录这样的最大后缀值,每次增加岗位时令这个最大后缀值自增1,然后获取这个值再和其他信息组成最终需要生成的code.该策略的优点在于,当原表数据量较大时,使用后缀表查询能免除大量的计算,缺点是条件范围较多时(如要考虑企业code,插入日期,单据类型等多种范围)的后缀表插入消耗略多,逻辑上也随之更加复杂.
第二种是不需要额外的后缀数字记录表,直接在原表中增加专门记录后缀数字的字段,每次获取插入数据的code时,获取全部或条件范围内的后缀数字最大值,加1即是要插入的后缀.两种策略都不适合批量插入数据,且都必须保证条件范围的一致性,即譬如插入单据类的code,必须保证企业编码和日期在查询到的最大值与插入的code的信息是一致的.该策略在使用时必须保证将查询(缓存表的形式,数字格式化使用lpad()函数)与插入合并在一条sql中以规避同步问题,优点即对应于第一种策略的缺点,即在条件范围较多时是个不错的选择,特别是条件范围中有日期的时候,同时不依赖于其他表,缺点即是当条件范围内的数据量较大时(如果条件范围没有日期,则很容易导致这点),在查询的速度上是不如第一种策略的.
另外,一个比较特殊的情况是,如果使用第一种策略,还可以用于模糊查询某些数据的数量,但这个只是附加功能,不应单纯为了这个功能而去使用第一种策略.
1.4.索引的设置
业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引(unique key).[阿里强制]
一般对于唯一指向或联合唯一指向的code而言,设置unique key(可能是联合)是一个不错的选择.一个比较纠结的问题是,对于伪删除数据的处理问题.
常用的伪删除有两种处理方式,一种是通过删除触发器的方式直接将数据挪移到另一个删除备份表中,这个对于原表没有任何影响,不用过多考虑;另一种是通过增加字段限制的方式,如增加is_del字段的方式来标记区分,处理起来就有些麻烦.譬如插入账号user001,再伪删除后重新插入同样的账号user001,这种情况就无法使用unique key.这种问题主要出现在自定义类code上,规则自动生成类可以将是否伪删除的数据都一视同仁.
对于自定义类code,如果实在不愿意舍弃unique key特性,可以利用账号本身在java层的正则限制,加上规则外的特殊符号和原id修改要删除的code.还是以账号为例,一般账号的规则为字母数字下划线,最多可能有括号斜杠等符号的支持,找到一个规则外的符号(尽可能确保生僻,以避免后续因为测试要求扩充规则而带来符号冲突),如特殊符号取∈(alt+41418),譬如原本账号是user001,id是117,伪删除之后将user001改为"user001∈117",也可以为了批量恢复方便设置为"user001∈00000117",账号的长度要考虑留有余裕.
另有一点需要说明:unique key只是最后保证code不重复的手段,不能作为日常手段通过try..catch..的方式来达到不重复的目的.
2 名称等其他字符串类
varchar是可变字符串,不预先分配存储空间,长度不要超过5000,如果存储长度大于此值,定义字段类型为text,独立出来一张表,用主键来对应,避免影响其他字段索引效率;[阿里强制]
2.1.通过冗余设计减少join表的数量(以提升查询速率)
超过三个表禁止join.需要join的字段,数据类型保持绝对一致;多表关联查询时,保证被关联的字段需要有索引.[阿里强制]
在一些较为牵扯表格数量较多的模块中,影响查询性能的一个重要因素是join表数量过多,特别是join到的表数据量较大时,查询速度更会大幅提升.这个问题在开发时,很容易不被关注,因为开发时数据量往往较少,往往都是上线后数据量猛增到一定程度,查询速度慢的问题才逐渐暴露出来.我见到一组sql外联表数量最多的时候达到十几个表,这种sql在上线一段时间后的查询效率可想而知.事实上对于很多表的join数量过多的问题,都可以考虑使用冗余来解决,也可称为反范式.
字段允许冗余,以提高性能,但是必须考虑数据同步的情况:1)不是频繁修改的字段.2)不是varchar超长字段,更不能是text字段;[阿里推荐]
举个例子,一张销售单据,含有明细数据,在数据库是t_sale_form和t_sale_form_detail,这是典型的两表连接.但在主表和明细中,往往会包含有许多外联的表信息,如主表中连接客户code,业务员id,明细表中连接中商品code,商品表中还连接着字典表中的品牌id,系列id,单位id,颜色id.如果写一个查询列表,全部使用join的话,至少要join上8个表,不是一个明智的选择.与之相对的,可以考虑在主表中存储客户姓名,业务员姓名,商品表中存储品牌名称,系列名称,单位名称,颜色名称.这样,查询sql中就只需要主表join明细表join商品表,三个表即可满足查询要求,即可大幅优化查询速度,特别是数据量较大时更能体现优化效果.当然,这种做法,同样要求客户表,用户表,数据字典表中的对应名称变更时同时对这个单据的冗余名称进行变更.考虑到客户表,用户表,数据字典表的名称都不是频繁变更的字段,正常情况下还是利大于弊的.
冗余不是越多越好,除非业务逻辑上要求某个表的数据名称不允许变更,否则建议仅在需要的时候保留冗余.虽然有些字段的变更并不频繁,但最好保留一个接口,用以需要使用冗余的开发人员在接口方法上修改冗余修改时需要变更的sql,在上例中,即是修改对应主表,明细表或是商品的对应字段值.
上述,即范式与反范式之间的均衡.
2.2.密码
一般设计为避免直接保存或获取密码本身,可考虑使用md5或sha1转码.
2.3.其他
在varchar字段上建立索引,必须制定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度.[阿里强制]
页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决.[阿里强制](暂时尚无十分完美的临时简易替代方案)
3 数值类
仅考虑本人接触过的数值类型,有商品数量,以及金钱.大致遵循以下原则:
任何字段如果为非负数,必须是unsigned.[阿里强制]
小数类型为decimal,禁止使用float或double.[阿里强制]
3.1.商品数量
商品数量大致分两种:1)单据商品数量;2)库存商品数量;
不论是前者还是后者,都要考虑余料问题,因此不能是整数,设计的数据类型是decimal(11,1),允许存在一位小数点,是可能存在半支料,即0.5支.
单据商品数量,一般是要考虑负数量的,即非unsigned;库存商品数量,一般是不需要考虑负数量的,除了某些特殊情况,一般设计时建议为unsigned.
2.2.金钱
金钱大致有两种设计策略,一种是decimal(11,2)(此处长度可能不够,仅供参考),参考数量的展示;一种是bigint类型,最小单位1表示分.
两种设计策略对于数据库而言,并没有太大差别,关键的区别在于java层的计算.decimal在java的返回类型,一般考虑设为BigDecimal或double,前者在计算上能做到准确计算,但稍微复杂,至少在代码层面如此,后者则可能在计算上出现偏差,如果是有效数字较少的情况下还是可以使用的(数值收敛的地方需要确保是四舍五入).bigint在java的返回类型取long即可,不论是在计算上准确性还是方便程度上都没有什么问题,只需要在前台转化的时候注意下即可.
4 时间类
对于时间的存储,目前已经用过的方式有bigint存储时间戳,varchar/char或data/datetime存储日期,分别就这几点说说优劣点:
bigint存储时间戳的优点在于可将精度保持在ms级(数据从java获取),这是目前mysql的各类现成日期格式都无法达到的精度,缺点在于转换麻烦;
varchar/char存储时间的优点在于灵活,可将日期存成各类已知的格式,但实际使用的时候这点很少用到,反而可能由于开发人员的标准不同问题造成困扰,缺点在于存储占空间较大,且无法保证格式统一,有时候反而需要考虑的更多,如对于没有记录的日期,既要考虑null,又要考虑空字符串,实际是最不推荐的一种方式;
date/datetime是mysql的官方日期时间存储格式,在于java层交互的时候为了方便可以考虑使用String来接收,综合而言是最方便的一种.只是前不久用datetime测试,接收到的字符串在秒上多了一位小数点(如2017-06-08 10:00:00.0),不知道是不是spring框架配置的问题.
5 状态类
5.1.概述
表达是否的概念,必须使用is_xxx的方式命名,数据类型为unsigned tinyint(1表示是,0表示否).[阿里强制]
如果修改字段含义或对字段表示的状态追加时,需要及时更新字段解释.[阿里推荐]
5.2.对于伪删除的字段
伪删除的字段,建议重新创建一个完全独立的字段来记录,不要与其他状态标识符混淆使用.
一个反例是:之前曾在设计状态标示字段时候,将表示已保存,已提交,已审核等状态的字段中加入了已删除(-1),存在的一个问题是,如果对于删除前的状态不进行限制,即如果允许已保存之外的状态下可以删除,则在修复数据的时候就无法判断已删除数据原本的状态是什么.且考虑到已删除状态往往与现有业务逻辑状态并不相关,独立开来是最好的选择.
6 其他类
6.1.ip记录
此前的ip记录,大多数是使用字符串记录,一个可选择的优化是,使用inet_aton()和inet_ntoa()来获取和存储ip地址.
7 查询相关
(以下内容更多是与查询有关,简单参考如下,更多内容将整理为一个查询优化建议发布)
如果有order by的场景,请注意利用索引的有序性.order by最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现file_sort的情况,影响查询性能.[阿里推荐]
利用覆盖索引来进行查询操作,来避免回表操作.[阿里推荐]
利用延迟关联或子查询优化超多分页场景.[阿里推荐]
sql性能的优化目标:至少要达到range级别,要求是ref级别,如果可以是consts最好.[阿里推荐]
建组合索引的时候,区分度最高的在最左边.[阿里推荐]
参考:
1.高性能MySQL(第三版);
2.阿里java开发手册正式版;
