项目经验_之_数据库规范
前言
Github：https://github.com/HealerJean
1、DEMO
create table test
(
id bigint(20) unsigned not null auto_increment comment '主键',
uk_name bigint(20) unsigned not null comment 'uk',
idx_name bigint(20) unsigned not null comment 'idx',
ref_item_id bigint(20) unsigned not null comment 'item表主键',
status varchar(32) not null comment '产品状态 字典表 productstatus',
create_user bigint(20) unsigned null default 0 comment '创建人',
create_name varchar(64) null default '' comment '创建人名称',
create_time datetime not null default current_timestamp comment '创建时间',
update_user bigint(20) unsigned null default 0 comment '更新人',
update_name varchar(64) null default '' comment '更新人名称',
update_time datetime not null default current_timestamp on update current_timestamp comment '更新时间',
unique index uk_name (uk_name) using btree comment '唯一索引',
index idx_name (idx_name) using btree comment '索引',
primary key (`id`) using btree
) engine = innodb comment '测试表'
1.2、推荐长度
| 类型 | 名称 | 长度 | 解释 |
|---|---|---|---|
| bigint | 主键 | bigint(20) | |
| varchar | 地址 | varchar(128) | |
| varchar | 状态 | varchar(8) | |
| varchar | 附件，逗号相隔 | varchar(128) | |
| varchar | 备注、描述 | varchar(128/255) | 不要超过255，256会多占内存 |
| varchar | 手机号 | varchar(20) | |
| varchar | 名字 | varchar(64) | |
| varchar | 邮件 | varchar(64) | |
| decimal | 金额 | decimal(20,0) | 以分为单位 |
| decimal | 百分比 | decimal(7,6) | 80% 0.800000 |
| int | 数字 | int/int(11) | 禁止整数类型指定长度，尤其不能使用TINYINT(1)，在大部分ORM层会被当布尔类型处理 |
| tinyint | 布尔 | tinyint(1) |
2、命名规范
1、使用下划线，不要使用大小写组合
2、数据库名,表名，字段名，索引名字避免超过32字符，须见名知意 (减少网段传输)
3、数据库名，表名，字段名，索引名等避免使用MySQL关键字
命名规范：
1、数据库名：尽量使用 项目名(scf)_模块名_表名 ，如果该数据库只会被一个项目使用，则没有必要加项目名
2、普通索引前缀：idx_索引字段名 ,唯一索引前缀：uk__索引字段名
3、数据库用户名命名规则：读写用户：数据库名_wr、只读用户：数据库名_r
3、字段类型
3.1、数字
| 类型 | 字节 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| tintint | 1 | (-128，127) | (0，255) | 小整数值 |
| smallint | 2 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| mediumint | 3 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| int/integer | 4 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| bigint | 8 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| float | 4 | (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度/浮点数值 |
| double | 8 | (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度/浮点数值 |
| decimal | M>D，为M+2否则为D+2 | decimal(M,D)依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
3.1.1、decimal(M,D)
声明语法为
DECIMAL(M,D)D是小数点右侧数位0-30，M最大精度数位，1-65规则：先保证小数点，再保证整数
D：是小数部分的位数，若插入的值未指定小数部分或者小数部分不足D位则会自动补到D位小数，若插入的值小数部分超过了D为则会发生截断，截取前D位小数(四舍五入截取)。
M：是整数部分+小数部分=总长度，也即插入的数字整数部分不能超过M-D位，否则不能成功插入，会报超出范围的错误。
举例说明，11615.23653234568，这个数存你说的三个格式
decimal：11615
decimal(3)：999
decimal(3,2)：9.99
decimal(10,5)11615.23653
超出精度范围的数会被强制进位并只显示数据类型定义的格式
3.1.2、有符号和无符号
mysql 类型有符号范围和无符号范围 ，主键自增Id我们一般都设计为无符号的
unsigned
比如8位的二进制，如果带符号,需要用1位表示符号，剩下7位表示数据.那么表示范围是-128—127
为什么是-128？
最小负数二进制是1000 0000 → 减一： 0111 1111 取反： 1000 0000 = 128 所以应该为 - 128
最大负数二进制是1111 1111 → 减一： 1111 1110 取反： 0000 0001 = 1 所以应该为 - 1
3.1.3、int(M)
这个长度
M并不代表允许存储的宽度,int（M）,也就是int（3）和int（11）能够存储的数据是一样的，只有联合zerofill参数才能有意义，否则int（3）和int（11）没有任何区别。整型定义中无需定义显示宽度，比如：使用INT，而不是INT(4)
⬤ 这个可选的宽度规格说明是用于在数值显示时，对某些值的宽度短于该列宽度的值进行左填补显示的，而不是为了限制在该列中存储值的宽度，也不是为了限制那些超过该列指定宽度的值的可被显示的数字位数
1、不加zeroffill没有区别
create table test_int
(
id int(3) unsigned not null,
uid int(11) unsigned not null,
uuid int unsigned not null
);
#插入数据
insert into test_int
values (4294967295, 4294967295, 4294967295);
#查询数据，发现没有什么区别
select * from test_int;
+------------+------------+------------+
| id | uid | uuid |
+------------+------------+------------+
| 4294967295 | 4294967295 | 4294967295 |
+------------+------------+------------+
1 row in set (0.00 sec)
2、有了zeroffill 不足会自动补0
create table test_int1
(
id int(3) unsigned zerofill not null,
uid int(11) unsigned zerofill not null,
uuid int unsigned zerofill not null
);
#插入数据
insert into test_int1
values (4294967295, 4294967295, 4294967295);
insert into test_int1
values (1, 4294967295, 110000);
#查询数据 发现前面的不足长度的右0了，当然不能使用idea测试，idea没有显示0
mysql> select * from test_int1;
+------------+-------------+------------+
| id | uid | uuid |
+------------+-------------+------------+
| 4294967295 | 04294967295 | 4294967295 |
| 001 | 04294967295 | 0000110000 |
+------------+-------------+------------+
2 rows in set (0.02 sec)
3、当使用zerofill 时，默认会自动加unsigned（无符号），zerofill默认为int(10)
create table test_int2
(
id int(3) unsigned zerofill not null,
uid int zerofill not null,
uuid int unsigned zerofill not null
);
# 下面的不能执行成功，以为无符号的都是正数
insert into test_int2
values (1, -4294967295, 110000);
insert into test_int2
values (1, 12345678, 110000);
mysql> select * from test_int2;
+-----+------------+------------+
| id | uid | uuid |
+-----+------------+------------+
| 001 | 0012345678 | 0000110000 |
+-----+------------+------------+
3.2、字符
1、一般千万不要使用text ，这样从mybatis取出来看似是String类型的，但是在实际使用中却或出现字符问题
| 类型 | 字节 | 用途 |
|---|---|---|
| char | 0-255字节 | 变长字符串 |
| varchar | 0-255字节 | 变长字符串 |
| tinyblog | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| tinytext | 0-255字节 | 短文本字符串 |
| blog | 0-65 535字节 | 二进制形式的长文本数据 |
| text | 0-65 535字节 | 长文本数据 |
| mediumblog | 0-16 777 215字节 16M | 二进制形式的中等长度文本数据 |
| mediumtext | 0-16 777 215字节 16M | 中等长度文本数据 |
| longblog | 0-4 294 967 295字节 4G | 二进制形式的极大文本数据 |
| longtext | 0-4 294 967 295字节 4G | 极大文本数据 |
3.2.1、char(M)、varcahr(M)
char是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是：
char(M)：每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足
varchar(M)：每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）
3.3、时间
| 类型 | 字节 | 取值范围 | 用途 | |
|---|---|---|---|---|
| year | 1 | 1901——2155 | YYYY | 日期值 |
| date | 4 | 1000-01-01——9999-12-31 | YYYY-MM-DD | 时间值或持续时间 |
| time | 3 | -838:59:59——838:59:59 | HH:MM:SS | 年份值 |
| datetime | 8 | 1000-01-01 00:00:00——9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值（精确到秒） |
| timestamp | 4 | 1970-01-01 00:00:01～2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值，int存储（精确到秒） |
3.3.1、timestamp
timestamp列必须有默认值，“0000-00-00 00:00:00”，不能保存，会报错，但不能为null。如果我们在save实体的时候，没有给相关timestamp设置值，那么他就会自动由mysql将当前时间设置进去，所以一般情况下建议使用datetime，但是如果非空，还是强烈建议使用timestamp，因为是int存储的 （前提是这个值在可用期限范围内，2038-01-19 03:14:07）1、
timestamp类型是带有时区信息的。一旦你系统中的时区发生改变，例如你修改了时区，该字段的值会自动变更。这个特性用来做一些国际化大项目，跨时区的应用时，特别注意！2、最小单位是秒
3.3.2、datetime
1、
datetime在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关2、最小单位是秒
3.3.3、bigint 时间戳
8个字节，自己维护一个时间戳，查询效率高，不过数据写入，显示都需要做转换。这种存储方式的具有 Timestamp 类型的所具有一些优点，并且使用它的进行日期排序以及对比等操作的效率会更高，跨系统也很方便，毕竟只是存放的数值。缺点也很明显，就是数据的可读性太差了，你无法直观的看到具体时间。
3.4、布尔
boolean值用1代表TRUE，0代表FALSE。boolean在mysql里的类型为tinyint(1)。
mysql里有四个常量：true,false,TRUE,FALSE分别代表1,0,1,0。
private Boolean loan;
tinyint(1) NOT NULL COMMENT '是否借款 true/false 1/0',
4、基础规范
1、MySQL字符集统一使用utf8，默认排序规则：utf8_general_ci ，表级和列级默认不需要再设置这两项，如果建表指定，需要同时设置这两项
2、不要使用 MySQL 存储过程，视图，触发器，Event, InnoDB外键约束
3、使用 InnoDB 存储引擎，默认事务隔离级别REPEATABLE-READ(可重复读)
4、从 Server，Database，Table三个层都指定为utf8字符集，不单独对字段指定字符集。
5、每个数据表都添加注释 comment, 建议每个字段也添加comment
4.1、库表设计
1、单表数据量控制在 1000W 行以内 ，采用合适的分库分表策略，例如十库百表
2、避免使用表的自增列，请使用外部id生成器处理
3、单表字段上限不超过 50 个，同时尽量避免列长度总和超过 8k
4.2、字段设计
1、非负的数字类型字段，都添加上UNSINGED，表示状态字段（0-255）的使用TINYINT UNSINGED ， 0 避免成为有效状态值
2、时间字段使用时间日期类型，避免使用字符串类型存储，日期使用 DATE 类型，年使用 YEAR 类型，日期时间可使用 DATETIME ，禁止使用 timestamp
3、字符串VARCHAR(N), 其中 N 表示字符个数，请尽量减少 N的大小
4、字段都设置为NOT NULL， 为字段提供默认值，如’’和’0’ ，字符串类型的空串不占内存空间，NULL是占内存空间的（很难优化，可能导致复合索引无效）
5、主键尽量保持增长趋势，建议使用id的生成器，避免使用表的自增列
6、不要在数据库中存储大图片或大文件，尽量使用简单的数据类型，避免使用blob和text类型(性能低于varchar,强制生产硬盘临时表，很难优化)
7、整型定义中无需定义显示宽度，比如：使用INT，而不是INT(4)， 这个可选的宽度规格说明是用于在数值显示时，对某些值的宽度短于该列宽度的值进行左填补显示的，而不是为了限制在该列中存储值的宽度，也不是为了限制那些超过该列指定宽度的值的可被显示的数字位数
8、必须有create_date、modified_date、delete_flag
4.3、索引设计规范
具体可以看另一篇有关索引的文章**
7.3.1、代码层面删除逻辑
唯一索引添加之后，如果是逻辑删除的，如果有可能恢复，记得还原id，没有添加唯一索引，则按照正常的逻辑删除即可
1、没有索引删除的
/**
* 添加字典类型
*
* @return
*/
@Override
public void addDictType(DictionaryTypeDTO typeDTO, LoginUserDTO loginUserDTO) {
SysDictionaryTypeQuery query = new SysDictionaryTypeQuery();
query.setTypeKey(typeDTO.getTypeKey());
query.setStatus(StatusEnum.生效.code);
SysDictionaryType type = sysDictionaryTypeManager.findByQueryContion(query);
if (type != null) {
throw new BusinessException(ResponseEnum.字典类型已存在);
}
type = new SysDictionaryType();
type.setCreateUser(loginUserDTO.getUserId());
type.setCreateName(loginUserDTO.getRealName());
type.setTypeKey(typeDTO.getTypeKey());
type.setTypeDesc(typeDTO.getTypeDesc());
type.setStatus(StatusEnum.生效.code);
type.setUpdateUser(loginUserDTO.getUserId());
type.setUpdateName(loginUserDTO.getRealName());
sysDictionaryTypeManager.insertSelective(type);
}
/**
* 删除字典类型
*/
@Override
public void deleteDictType(Long id, LoginUserDTO loginUserDTO) {
SysDictionaryType type = sysDictionaryTypeManager.findById(id);
if (type == null) {
throw new BusinessException(ResponseEnum.字典类型不存在);
}
type.setStatus(StatusEnum.废弃.code);
type.setUpdateUser(loginUserDTO.getUserId());
type.setUpdateName(loginUserDTO.getRealName());
sysDictionaryTypeManager.updateSelective(type);
}
/**
* 修改字典类型
*/
@Override
public void updateDictType(DictionaryTypeDTO typeDTO, LoginUserDTO loginUserDTO) {
SysDictionaryTypeQuery query = new SysDictionaryTypeQuery();
query.setTypeKey(typeDTO.getTypeKey());
query.setStatus(StatusEnum.生效.code);
SysDictionaryType typeExist = sysDictionaryTypeManager.findByQueryContion(query);
//判断是是否已经存在数据
if (typeExist != null && !typeExist.getId().equals(typeDTO.getId()) ) {
throw new BusinessException(ResponseEnum.字典类型已存在);
}
SysDictionaryType type = BeanUtils.dtoToDictionaryType(typeDTO);
type.setUpdateUser(loginUserDTO.getUserId());
type.setUpdateName(loginUserDTO.getRealName());
sysDictionaryTypeManager.updateSelective(type);
}
2、有索引删除的代码
/**
* 添加域名
*/
@Override
public DomainDTO addDomain(DomainDTO domainDTO, LoginUserDTO loginUserDTO) {
AlimamaInfoDTO alimamaInfoDTO = loginUserDTO.getAlimamaInfo();
SysDomainQuery domainQuery = new SysDomainQuery();
domainQuery.setRefAlimamaInfoId(alimamaInfoDTO.getAlimamaInfoId());
domainQuery.setType(domainDTO.getType());
SysDomain domain = sysDomainManager.findByQueryContion(domainQuery);
if (domain != null) {
if (domain.getStatus().equals(StatusEnum.生效.code)) {
throw new BusinessException(ResponseEnum.域名已存在);
} else {
domain.setRefAlimamaInfoId(alimamaInfoDTO.getAlimamaInfoId());
domain.setStatus(StatusEnum.生效.code);
domain.setValue(domainDTO.getValue());
domain.setCreateUser(loginUserDTO.getUserId());
domain.setCreateName(loginUserDTO.getRealName());
domain.setUpdateUser(loginUserDTO.getUserId());
domain.setUpdateName(loginUserDTO.getRealName());
sysDomainManager.updateSelective(domain);
}
} else {
domain = new SysDomain();
domain.setRefAlimamaInfoId(alimamaInfoDTO.getAlimamaInfoId());
domain.setType(domainDTO.getType());
domain.setValue(domainDTO.getValue());
domain.setStatus(StatusEnum.生效.code);
domain.setCreateUser(loginUserDTO.getUserId());
domain.setCreateName(loginUserDTO.getRealName());
domain.setUpdateUser(loginUserDTO.getUserId());
domain.setUpdateName(loginUserDTO.getRealName());
sysDomainManager.save(domain);
}
domainDTO.setDomainId(domain.getId());
return domainDTO;
}
/**
* 修改域名
*/
@Override
public void updateDomain(DomainDTO domainDTO, LoginUserDTO loginUserDTO) {
AlimamaInfoDTO alimamaInfoDTO = loginUserDTO.getAlimamaInfo();
SysDomainQuery domainQuery = new SysDomainQuery();
domainQuery.setRefAlimamaInfoId(alimamaInfoDTO.getAlimamaInfoId());
domainQuery.setType(domainDTO.getType());
SysDomain domain = sysDomainManager.findByQueryContion(domainQuery);
if(domain != null ){
if(!domain.getId().equals(domainDTO.getDomainId())){
if(domain.getStatus().equals(StatusEnum.生效.code)){
throw new BusinessException(ResponseEnum.域名已存在);
}else {//前提必须是status，否则会出问题
domain.setStatus(StatusEnum.生效.code);
domain.setValue(domainDTO.getValue());
domain.setUpdateUser(loginUserDTO.getUserId());
domain.setUpdateName(loginUserDTO.getRealName());
sysDomainManager.updateSelective(domain);
}
}else {
domain.setValue(domainDTO.getValue());
domain.setUpdateUser(loginUserDTO.getUserId());
domain.setUpdateName(loginUserDTO.getRealName());
sysDomainManager.updateSelective(domain);
}
}else {
throw new BusinessException(ResponseEnum.域名不存在);
}
}
/**
* 删除域名
*/
@Override
public void deleteDomain(DomainDTO domainDTO, LoginUserDTO loginUserDTO) {
AlimamaInfoDTO alimamaInfoDTO = loginUserDTO.getAlimamaInfo();
SysDomain domain = sysDomainManager.findByIdAndAlimamaId(domainDTO.getDomainId(), alimamaInfoDTO.getAlimamaInfoId());
if(domain == null ){
throw new BusinessException(ResponseEnum.域名不存在);
}
if(domain.getStatus().equals(StatusEnum.废弃.code)){
throw new BusinessException(ResponseEnum.重复操作);
}
domain.setStatus(StatusEnum.废弃.code);
domain.setUpdateUser(loginUserDTO.getUserId());
domain.setUpdateName(loginUserDTO.getRealName());
sysDomainManager.updateSelective(domain);
}
4.4、SQL规范
1、在线业务的update和delete的where中是唯一索引或者主键，避免一次修改多条语句的情况，而且这样锁住的是一行数据，大批量的 update 和 delete 操作，避免高峰期操作。
2、避免在MySQL数据库中进行计算操作，尽量由业务来处理运算，数据库，就应该让它做存储数据，查询数据的事情
3、避免使用join，子查询等SQL
1.join的效率是硬伤，一旦数据量很大效率就很难保证，它是走嵌套查询的。小表驱动大表，且通过索引字段进行关联。如果表记录比较少的话，还是OK的。大的话业务逻辑中可以控制处理，强烈推荐分别根据索引单表取数据，然后在程序里面做join，
2.子查询就更别用了，效率太差，执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。
4、避免使用select * , 只返回自己需要的字段，枚举出要返回的字段名称。
1)、无法使用覆盖索引(covering index)；
2) 消耗列多的io, cpu, 网络带宽；
3）因为列的总长度大，可能由于select/join时产生临时表.
5、SQL过滤的where条件尽量不使用OR, NOT IN , NOT EXIST，可以讲OR 改成 IN
1）or的效率O(n)
2) IN的效率 O(log n)
6、使用where IN()过滤时，IN集合个数必须小于500，因为in的数据少的时候，mysql优化器会可能会使用索引，但是当数据太多以后就不一定了，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效
7、分页算法
传统分页 select * from table limit 10000,100;
当limit 越大，效率越低。
推荐使用 select * from table where id>243800 order by id limit 10;
4.5、行为规范
1、生产数据库批量处理,如大量数据更新，导入，导出，请通知DBA，由DBA评估影响并执行相应的SQL.
2、生产数据库数据订正update/delete等，执行前先写成select检查返回的行或结果集是否合理
3、 表结构的修改操作，请使用alter操作，而不是直drop + create
4、考虑使用 UNION ALL，减少使用 UNION，因为 UNION ALL不去重，而少了排序操作，速度相对比 UNION 要快，如果没有去重的需求，优先使用 UNION
