(27)MySQL 简要规范

一、表规范

1、表、字段名，小写字母, “_”分割, 不需简写，长度< 32个字符    xx_test

2、禁⽤关键字, 如desc、code、name，反例：select name, code from table；正：select table_name, table_code from table

3、临时/备份库、表前缀tmp/bak，后缀日期：create table bak/temp_table_20210109...

4、禁用外键新增、更新、删除，影响性能

5、建表必备字段: id、create_at、update_atid 必为主键，类型为unsigned bigint、单表时自增、步长为1;create_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;

update_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

6、主键索引默认PrimaryKey, 自定义pk_字段名；唯一uniq_；普通索引名idx_：pk_id、idx_code、uniq_code

7、【建议】创建表时, 根据业务需求添加版本号字段用于进行并发控制`version bigint(20) NOT NULL COMMENT '版本号',

8、【建议】存在删除场景时, 添加删除标示字段, 禁止物理删除

二、字段规范

1、日期用datetime
timestamp：1) 只到2038年, 设置默认值 0000-00-00 00:00:00, 2)  mybatis映射会报错, jdbcurl后加zeroDateTimeBehavior=convertToNull

2、小数用decimal，或缩放存为int/long。禁用 float 和 double

3、索引字段禁止为NULL，设置默认值（NULL，索引无法优化）

4、建议将TEXT、BLOB类型字段拆到单独表中（大字段影响查询性能）

三、索引规范

1、唯一特性字段，建唯一索引 ( 业务单据号、基础数据编码等)

2、组合索引，区分度最高放前

    如：where c>? and d=? 即使 c 区分度高：idx_d_c

    同一create_time量级非常小：KEY `status_create_time_index` (`status`,`create_time`)

3、单表索引数 < 5个, 单索引中字段<5个

4、ORDER BY、GROUP BY、DISTINCT利用索引先天顺序避免排序（否则会消耗大量磁盘IO）

联合索引建立如下 a_b_c：

select order_id from ORDER where。a = 'xxx' and。b > 'xxx' order by c;  命中a_b，c要额外排序

select order_id from ORDER where a = 'xxx' and b = 'xxx' order by c;    命中 a_b_c

5、左匹配原则

select * from myTest  where a=3 and b=5 and c=4

select * from myTest  where  c=4 and b=6 and a=3;    where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样

6、like 'test%'。禁用like '%abc'、like '%abc%' 会让索引失效

7、不在低基数列上建索引，如“性别”

例1:  create_time的区分度低，可以把后面两个索引合为一个

        KEY `create_status_index` (`create_time`,`status`) COMMENT '创建时间和状态索引'
        KEY `status_index` (`status`)

        合并为：KEY `create_status_index` (`status`, create_time) COMMENT '创建状态和时间的索引',

例2：order_id 的区分度低，不应该再order_id的后面再增加联合索引，所以前两个索引可以合为一个索引

四、SQL 使用规范

1、尽量不用<if>，没法判断是否可以命中索引，没法做索引的优化（表中数量比较少或者update不强制，搜索逻辑用es）

2、where禁止sql引用（不能一眼看出sql语义）

3、禁用大事务（事务有其他的IO操作、SQL数量过多，导致事务耗时长）

4、新增查询SQL要执行计划，访问类型type：至少range 级别，要求ref，consts 最好。禁止all/index

    1.consts：单表中最多只有一行匹配，在优化阶段即可读取到数据：select id, number from T where number=10 // number唯一索引 

    2.ref ：普通索引：select id, name from T where name='a' // name普通索引

    3.range：对索引进行范围检索：select id, age from T where age in (1,2,4)

    explain type级别参考：https://blog.csdn.net/dennis211/article/details/78170079

5、禁止隐式转换、索引列函数运算（防止索引失效）select table_name from table where id = 1; 不要 = '1';

6、禁止select *（网络磁盘IO开销会变多）

7、禁用select for update

8、优先考虑使用覆盖索引，避免回表, explain 的结果，extra 列会出现：using index