Mysql规范

基础规约

1.必须使用InnoDB存储引擎
说明：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高。

2.必须使用utf8mb4字符集
说明：真正的utf8，万国码，无需转码，无乱码风险，节省空间
现在绝大部分表使用utf8mb4字符集，course_mapping表使用的是utf8表

3.禁止使用存储过程、视图、触发器、Event
说明：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU计算应该上移。

4.禁止存储大文件或者大照片
说明：大文件和照片等应该存储在文件系统，数据库里存URI

建表规约

1. 表必备三字段：id， create_time， update_time
说明：其中id必为主键， 类型为bigint unsigned、单表时自增、步长为1。

create_time，update_time的类型均为datetime类型， 前者现在时表示主动式创建， 后者过去分词表示被动式更新。

如：CREATE TABLE `stu_answer` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`exercise_id` varchar(32) NOT NULL DEFAULT '' COMMENT '作业ID',
`add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='学生答题数据汇总详细表';

2. 必须把字段定义为NOT NULL，提供默认值，且默认值符合业务含义

如：性别字段，0未知 1男 2女，那么默认值应该为0

说明：

a）null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化

b）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多

c）null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识

d）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录

3. 表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。

数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
说明：MySQL在Windows下不区分大小写， 但在Linux下默认是区分大小写。因此， 数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。

4. 避免使用逻辑删除，如is_del字段

说明：非产品要求保留历史数据、且数据特别重要的场景除外，因为：

1）增加业务复杂度，数据维护起来更麻烦，每次写该表sql都要增加 is_del=0

2）如果别人没有注意未使用了is_del=0进行过滤，易产生bug

3）引用关系表场景（如题组对题目的引用）下，删除题目后重新添加题目，可能导致无法使用唯一索引，而产生脏数据。
4）无效数据一直存在表中，影响sql的执行效率且会一直占用存储

5.  唯一索引名为uk_字段名； 普通索引名则为idx_字段名。
说明： uk_即unique key； idx_即index的简称。

如果class_id是索引，索引名称为：idx_class_id

如果是复合索引，如class_id，lesson_id是索引，索引名称建议为：idx_classid_lessonid，避免过长；

6. 小数类型为decimal， 禁止使用float和double
说明：在存储的时候， float和double都存在精度损失的问题， 很可能在比较值的时候， 得到不正确的
结果。如果存储的数据范围超过decimal的范围， 建议将数据拆成整数和小数并分开存储。

7. 如果存储的字符串长度几乎相等， 使用char定长字符串类型。

8. 不要使用TEXT、BLOB字段类型
会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能；

如果必须要使用则独立出来一张表，用主键来对应，避免影响其它字段索引效率。

9. 表的命名最好是遵循“业务名称_表的作用”命名规则，更易于理解。
如：cpp_project stats_review

10. 每个表和列都要加注释，且要清晰，如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。

说明：方便自己、别人特别是新同学了解业务的真实含义。

11. 当一个表使用另一个表的列时，一定保证类型等属性的完全一致，改动也要保持其完全相同。

说明：遇到过因数据类型不同，导致的关联查询不走索引等问题，映射成dto是导致字段类型也不一致，字段也不可以直接进行比较。

12. 设置合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

如：无符号值可以避免误存负数，且扩大了表示范围。tinyint/int/bigint，分别会占用1/4/8字节，在满足业务时优先选择小的类型。

13. 使用TINYINT来代替ENUM

说明：ENUM增加新值要进行DDL操作

索引规约

1. 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引

反例：course_mapping表记录班级绑定关系，一个班级只能被绑定一次，cla_clazz_id就应该是唯一索引，现在是一般索引。导致问题：

1）表中有300多条脏数据。

2）按照班级查的时候用单个dto接收结果，但是实际结果多条，可能出现 select one but found two的异常；

说明：不要以为唯一索引影响了insert速度， 这个速度损耗可以忽略， 但提高查找速度是明显的；

另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

2. 超过三个表禁止join。需要join的字段， 数据类型保持绝对一致； 多表关联查询时，保证被关联的字段需要有索引。
说明：即使双表join也要注意表索引、SQL性能。

3. 不建议在大字段上建索引，如varchar(256),如果确实需要在大的varchar字段上建立索引时， 必须指定索引长度，  根据实际文本区分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上， 可以使用count(distinct left(列名， 索引长度) ) /count(*) 的区分度来确定。

如：CREATE INDEX idx_comment ON review(comment(3));

注意：无法使用varchar的前缀索引做ORDER BY 、GROUP BY 和覆盖扫描等操作。

4. 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎（如ES）来解决。
说明：索引文件具有B-Tree的最左前缀匹配特性， 如果左边的值未确定， 那么无法使用此索引。

5. 如果有order by的场景， 请注意利用索引的有序性。order by最后的字段是组合索引的一部分， 并且放在索引组合顺序的最后， 避免出现file sort的情况， 影响查询性能。
正例：where a=? and b=? order by c； 索引：a_b_c
反例：索引如果存在范围查询， 那么索引有序性无法利用， 如：WHERE a>10O RDER BY b； 索引a_b无法排序。

6. 建组合索引的时候，区分度最高的在最左边。
正例：如果where a=? and b=?， a列的几乎接近于唯一值， 那么只需要单建idx a索引即可。
说明：存在非等号和等号混合判断条件时， 在建索引时， 请把等号条件的列前置。如：where c>?and d=?
那么即使c的区分度更高， 也必须把d放在索引的最前列， 即建立组合索引idx_d_c。

SQL语句

1.不要使用count(列名) 或count(常量) 来替代count(*) ,  count(*) 是SQL 92定义的标准统计行数的语法， 跟数据库无关， 跟NULL和非NULL无关。
说明：count(*) 会统计值为NULL的行， 而count(列名) 不会统计此列为NULL值的行。

2. 不允许使用外键与级联，一切外键概念必须在应用层解决。
说明：(概念解释) 学生表中的student id是主键， 那么成绩表中的student_id则为外键。

如果更新学生表中的student id， 同时触发成绩表中的student id更新， 即为级联更新。

外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

3. 对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名(或表名)进行限定。
说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名(或表名)，并且操作列在多个表中存在时，就会抛异常。
正例：select a.name from table_first as a， table_second as b where a.id=b.id；
反例：在某业务中，由于多表关联查询语句没有加表的别名(或表名)的限制，正常运行两年后，最近在某个表中增加一个同名字段， 在做数据库变更后， 线上查询语句出现出1052异常：Column'name'in field list is ambiguous。

4. 在表查询中，一律不要使用*作为查询的字段列表，需要哪些字段必须明确写明。
说明：1) 增加查询分析器解析成本。2) 增减字段容易与resultMap配置不一致。3) 无用字段增加网络消耗， 尤其是text类型的字段。

5. 避免索引失效

1）禁止在WHERE条件的上使用函数或者计算
解读：SELECT name FROM t_user WHERE date(create_datatime)='2017-12-15' 会导致全表扫描
推荐的写法是：SELECT name FROM t_user WHERE create_datatime>= '2017-02-15 ' and create_datatime < '2017-02-16 '

2）%开头的模糊查询，不走索引

3）索引字段发生了字符编码转换

4）使用了反向查询 如not in、not exist

5）查询的数量是大表的大部分

6）单独引用复合索引里非第一位置的索引列.

6. like与concat
实际使用的时候，经常like的内容是作为参数从前台页面传递过来的，用CONCAT进行拼接，不要用+；
如：select * from table where name like concat(#{name},'%')

7. between
当数据库字段中存储的是yyyy-MM-dd格式，即date类型；用between and查询参数yyyy-MM-dd格式时，包含头尾，相当于x>=y && x<=z.
当是yyyy-MM-dd HH:mm:ss格式，即datetime类型；用between and查询参数yyyy-MM-dd HH:mm:ss格式时，包含头尾，x>=y && x<=z。参数yyyy-MM-dd格式时，只包含头，相当于x>=y && x<z。

8. sql.xml配置参数使用：#{} ， #param#不要使用${} , 此种方式容易出现SQL注入。

9. 利用延迟关联或者子查询优化超多分页场景。
说明：MySQL并不是跳过offset行， 而是取offset+N行， 然后返回放弃前offset行， 返回N行;那当offset特别大的时候， 效率就非常的低下， 要么控制返回的总页数， 要么对超过特定阈值的页数进行SQL改写。
正例：先快速定位需要获取的id段，然后再关联：
SELECT a.* FROM 表1 as a， (select id from 表1 where条件 LIMIT 100000， 20) as b where a.id=b.id

历史表的处理思路

逐表处理，上面所有原则，同时 业务需做对应同步修改。