前言

Github:https://github.com/HealerJean

博客:http://blog.healerjean.com

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)没有任何区别。

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存储的

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类型,日期时间可使用DATETIMETIMESTAMP, 能用TIMESTAMP(int)就不用DATETIME/DATE(char)

3、字符串VARCHAR(N), 其中N表示字符个数,请尽量减少N的大小

4、字段都设置为NOT NULL, 为字段提供默认值,如’’和’0’ ,字符串类型的空串不占内存空间,NULL是占内存空间的(很难优化,可能导致复合索引无效)

5、主键尽量保持增长趋势,建议使用id的生成器,避免使用表的自增列

6、不要在数据库中存储大图片或大文件,尽量使用简单的数据类型,避免使用blob和text类型(性能低于varchar,强制生产硬盘临时表,很难优化)

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

ContactAuthor