MySQL开发规范
阿里巴巴开发手册https://developer.aliyun.com/special/tech-java
一、建表规约
1.1表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint(1表示是，0表示否)。
tip：POJO(Domin)类中的任何布尔型的变量，都不要加is前缀，否则部分框架解析会引起序列化错误，因此需要在
1.2表名、字段名必须使用小写字母或数字，禁止出现数字开头禁止两个下划线中间只出现数字。
1.3表名不使用复数名词
1.4禁用保留字
tip：MySQL中的保留字和Java中的保留字一样，因此在命名时不能使用保留字。详细查看MySQL官方文档
【https://dev.mysql.com/doc/mysqld-version-reference/en/keywords-8-0.html】
1.5索引命令规约
| 索引类型 | 命名规约 |
|---|---|
| 主键索引(primary key) | pk_字段名 |
| 唯一索引(unique key) | uk_字段名 |
| 普通索引(index) | idx_字段名 |
1.6小数类型为decimal，禁止使用float和double
tip：decimal详解【https://dev.mysql.com/doc/refman/8.0/en/precision-math-decimal-characteristics.html】
1.7如果存储的字符串长度几乎相等，使用char定长字符串类型
1.8varchar是可变长字符串，不预先分配存储空间，长度不要超过5000
tip：如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索引率。
1.9表必备三字段: id, create_time,update_time
1.10物理删除与逻辑删除
tips：大部分开发场景使用物理删除即可，对于较为重要的数据，如金融方面等要用逻辑删除。
1.11字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：
● 不是频繁修改的字段。
● 不是唯一索引的字段。
● 不是varchar超长字段，更不能是text字段。
1.12分库分表——单表行数超过500万行或者单表容量超过2GB
1.13合适的字符存储长度
tips：不但节约数据库表空间、节约索引存储，更重要的是提升检索速度
二、索引规约
2.1业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引
tips：唯一索引对insert的速度影响可以忽略，其提高的查找速度很明显
2.2超过三个表禁止join。需要join的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引
tips：出于性能考虑，即使双表join也要注意表索引、SQL性能。
开发中常常用分解查询重构关联查询（高并发、高性能的应用中一般使用单表查询）：
- 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。另外对于MySQL的查询缓存来说，如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。
- 将查询分解后，执行单个查询可以减少锁的竞争。
- 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
- 查询本身效率也可能会有所提升。
- 可以减少冗余记录的查询。
- 对于数据量很大时，join联合查询速度很慢
- 单表查询有利于后期数据量大了分库分表，联合查询一旦分库，原来的sql都需要改动
- 分解查询相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套环关联，某些场景哈希关联的效率更高很多
2.3在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度
tips：对字符串类型的数据，长度为20的索引，对记录的区分度一般都会高达90%以上，可以使用count(distinct left(列名，索引长度))/ count(*)的区分度来确定
2.4页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决
tips：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引
2.5如果有order by 的场景，请注意利用索引的有序性。order by最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现filesort的情况，影响查询性能
tips：索引如果存在范围查询，那么索引有序性无法利用，如: WHERE a > 10 ORDER BY b;索引a_b无法排序。
2.6利用覆盖索引来进行查询操作，避免回表
tips：如果需要查询的字段名存在于某个索引上，就可以利用索引直接提供查询结果，不需要回表，减少树的搜索次数，显著提升查询性能
2.7利用延迟关联或者子查询优化超多分页场景
tips：MySQL并不是跳过offset行，而是取 offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，在实际应用中，可以根据当前页数判断是否需要改写SQL：如果当前页数小于特定阈值，可以使用offset和limit分页，控制返回的总页数；如果页数超过阈值，则改为基于ID的查询或其他SQL改写。
2.8SQL性能优化的目标：至少要达到range级别，要求是ref级别，如果可以是const最好
● consts单表中最多只有一个匹配行（主键或者唯一索引)，在优化阶段即可读取到数据
● ref 指的是使用普通的索引 (normal index)
● range 对索引进行范围检索
2.9建组合索引的时候，区分度最高的在最左边
tips：存在非等号和等号混合判断条件时，要把等号条件的列前置
2.10防止因字段类型不同造成的隐式转换，导致索引失效
tips：类型强转，索引失效
2.11创建索引时避免有如下极端误解
● 索引宁滥勿缺。认为一个查询就需要建一个索引。
● 吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。
● 抵制唯一索引。认为唯一索引一律需要在应用层通过“先查后插”方式解决。
三、SQL语句
3.1不要使用count(列名)或count(常量)来替代count(*)
tips：count(*）会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行。
3.2count(distinct col)计算该列除NULL之外的不重复行数，注意count(distinct col1 , col2)如果其中一列全为NULL，那么即使另一列有不同的值，也返回为0
3.3当某一列的值全是NULL时，count(col)的返回结果为0;但sum(col)的返回结果为NULL，因此使用sum()时需注意NPE（空指针异常）问题
tips：可以使用如下方式来避免 sum的 NPE问题：SELECT IFNULL(SUM(column)，0) FROM table;
3.4使用ISNULL()来判断是否为NULL值
tips：NULL与任何值的直接比较都为NULL，不会出现true和false；并且ISNULL(column)相较于column is null效率会更快一些
3.5代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句
3.6不得使用外键与级联，一切外键概念必须在应用层解决
tips：外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。
3.7禁止使用存储过程，存储过程难以调试和扩展，更没有移植性
3.8数据更新要先select，确认无误
3.9尽量避免in操作，in后面的集合元素应控制在1000个以内
3.10TRUNCATE TABLE 比DELETE 速度快，且使用的系统和事务日志资源少
tips：TRUNCATE是DDL语句，用于清空大数据表，不受外键约束，但它无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句
四、ORM映射
4.1 在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明
tips：无脑使用*会有以下问题：
● 增加查询分析器解析成本
● 增减字段容易与 resultMap 配置不一致
● 无用字段增加网络消耗，尤其是 text 类型的字段
4.2 POJO 类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap 中进行字段与属性之间的映射
4.3 不要用 resultClass 当返回参数，即使所有类属性名与数据库字段一一对应，也需要配置完整的映射关系，使字段与DO类解耦，方便维护
4.4 sql.xml 配置参数使用：#{}，#param# 不要使用${} 此种方式容易出现 SQL 注入
4.5 iBATIS 自带的 queryForList(String statementName,int start,int size)不推荐使用
tips：其实现方式是在数据库取到 statementName 对应的 SQL 语句的所有记录，再通过 subList 取 start,size 的子集合，可以做如下修改：
Map<String,Object> map = new HashMap<>();
map.put();
map.put();
list = sqlMapClient.queryForList("statementName",map);
4.6不允许直接拿HashMap与Hashtable作为查询结果集的输出
tips: resultClass="Hashtable"，会置入字段名和属性值，但是值的类型不可控
4.7 @Transactional事务不要滥用。事务会影响数据库的QPS，另外使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等
