一、基本规范
- 【强制】如非特殊需求，OLTP类系统的所有表应采用Innodb引擎。
说明：5.5 以后的默认引擘，支持事务，行级锁，更好的恢复性，高并发下性能更好，对多核，大内存， SSD等硬件支持更好。
- 【强制】禁止使用分区表。
说明：MySQL 的分区表实际性能不是很好，且管理维护成本较高。
- 【强制】如非特殊需求，表字符集建议使用 utf8，如果涉及存储表情符使用utfmb4。
说明：使用 utf8 字符集，如果是汉字，占 3 个字节，但 ASCII 码字符还是 1 个字节；统一，不会有转换产生乱码风险。
- 【强制】如非特殊需求，禁止在数据库中存储图片、文件等大数据。
- 【强制】所有表、列需要添加注释，单表数据量建议控制在500万以内;如涉及状态描述的字段修改时,需及时同步字段和PO字段注释,确保一致性。
- 【推荐】库名与应用名保持一致。
- 【强制】库名、表名、字段名使用小写字母并采用下划线分割。
- 【强制】库名、表名、字段名最长32个字符，易于辨识以及减少传输量。
- 【强制】库名、表名、字段名禁止使用 MySQL 保留关键字，如match,range等。
- 【强制】临时库、临时表名以 tmp 为前缀并以日期为后缀。
- 【强制】备份库、备份表名以 bak 为前缀并以日期为后缀。
- 【强制】主键索引名为pk_字段名,唯一索引为uk_字段名,普通索引为idx_字段名。
二、命名规范
说明：pk=primary key; uk= unique key; idx=即index的缩写。
- 【强制】表达“是/否”概念的字段必须使用is_xxxx格式命名（yn字段除外），数据类型为tinyint(1表示是，0表示否)。
- 【推荐】推荐“业务名_表的作用”形式对表命名。
正例：project_task、project_config
三、表设计规范
- 【强制】所有字段均定义为 not null。
- 【强制】表必备四个字段，id,create_time,update_time,is_delete。
说明：id 为主键，步长为1自增的bigint类型；
create_time、update_time 为创建和修改时间，均为date_time类型；
is_delete 为删除标识，默认为0，1表示已删除；
- 【强制】如果存储的字符长度几乎相等，使用char，而非varchar。
- 【推荐】小数类型使用decimal禁用float和double。
说明：float和double在存储的时候,存储精度损失问题,很可能在值比较时得不到正确的结果,如果存储数据范围超过decimal的范围,建议将数据拆成整数和小数分开存储;
- 【推荐】单表字段数控制在 20 个以内，允许一定程度的字段冗余，以提高查询性能，但必须充分考虑数据一致性问题，冗余字段应遵循：
1.非频繁修改字段；
2.非varchar超长字段，更不能是text、BLOB字段；
正例：四级地址名称，字符长度较短，几乎不会产生变，适合冗余以减少关联查询；
- 【推荐】单表超过500万行或表容量超过2GB，才推荐考虑分库分表。
说明：如果需求调研过程中可预料系统将存储海量数据，参考第五条，否则，如三年后才能达到500万或超2GB的表容量，不推荐设计之初考虑分库分表方案。
- 【推荐】采用合理的分库分表策略，推荐使用 HASH 进行分表，表名后缀使用十进制数，下标从 0 开始，首次分表做好评估调研，避免二次分表，二次分表的难度和成本较高。
- 【推荐】使用适合的类型存储数据。
说明：适合的类型，不但节约存储空间，节约索引存储，更重要的是可以提升检索速度；
如下表,无符号值可避免误存负数,且扩大的表示范围
| 对象 | 年龄区间 | 类型 | 表示范围 |
| 人 | 150岁以内 | unsigned tinyint | 无符号值：0到255 |
| 龟 | 数百岁 | unsigned smallint | 无符号值：0到65535 |
| 恐龙化石 | 数千万年 | unsigned int | 无符号值：0到42.9亿 |
| 太阳 | 约50亿年 | unsigned bigint | 无符号值：0到10的19次方 |
四、索引规范
- 【强制】单张表中索引数量不超过5个；
- 【强制】单个索引中的字段数不超过5个；
- 【强制】禁用外键，外键用来保护参照完整性，可在业务端实现，对父表和子表的操作会相互影响，降低可用性；
- 【强制】字符串建立索引时，必须使用前缀索引，没必要对全字段建立索引，建议前缀索引长度控制在20个字符以内；
说明：索引长度与区分度是一对矛盾体，一般对于字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名,索引长度))/count(*)的区分度来确定。前缀索引相关内容可参考MySQL前缀索引和索引选择性 - yayun - 博客园
- 【强制】禁用左模糊或全模糊,如有需要请走搜索引擎来处理。
说明：索引文件具有B-Tree的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。
- 【推荐】创建索引/复合索引时区分度最高的放在最左边。
- 【推荐】如果有order by的场景,请注意利用索引的有序性,order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免file sort的情况,影响查询性能。
正例:where a=? and b=? order by c;索引a_b_c。
反例:索引中有范围查找,那么无法复用索引有序性,如where a>100 group by b; 索引a_b无法排序。
- 【强制】不在低区分度的字段上创建索引，例如：性别。
- 【强制】避免冗余或重复索引。
说明：合理创建联合索引（避免冗余）， index(a,b,c) 相当于 index(a) 、 index(a,b) 、 index(a,b,c)。
- 【强制】避免因字段类型不同造成的隐式转换,导致不走索引,全表扫描。
说明：如在user_info表phone_num字段为char(11)。
正例:SELECT * FROM user_info WHERE phone_num=’13599998888’
反例:SELECT * FROM user_info WHERE phone_num=13599998888
- 【强制】不在索引列进行数学运算和函数运算。
- 【强制】不使用反向查询，如not in/not like。
- 【推荐】利用覆盖索引来进行查询操作。
说明:一个包含查询所需的字段的索引称为 covering index 覆盖索引。MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表操作，减少IO，提供效率。 当你对一个sql 使用explain statement 查看一个sql的执行计划时，在EXPLAIN的Extra列出现Using Index提示时，就说明该select查询使用了覆盖索引。
- 【推荐】SQL性能优化的目标:至少达到range级别,建议达到ref级别,最好为consts级别
说明:
consts单表中最多只有一个匹配行(主键或唯一索引)；
ref为普通的索引；
range对索引进行范围查询;
反例:explain表的结果,type=index,索引物理文件全扫描 ,速度非常慢,该index级别比range低,与全表扫描小巫见大巫。
- 【推荐】利用延迟关联或者子查询优化超多分页场景。
说明:Mysql不是跳过offset行,而是取offset+n行,然后返回放弃前offset行,返回n行,那么当offset特别大的时候效率非常低,要么控制返回的总页数,要么对超过特定的阀值的页数进行SQL改写。
正例:先快速定位要获取的id段,然后在关联。
Select a.* from 表1 a,(select id from 表1 where 条件 limit 100000,20)b where a.id=b.id。
- 【参考】创建索引时避免有如下极端误解:
1.宁滥勿缺,误认为一个查询就需要建一个索引；
2.宁缺勿滥,误认为索引会消耗空间,严重拖慢新增和更新速度 ;
3.抵制唯一索引,误认为唯一性需要后台应用通过”先查后插”解决；
五、SQL规范
- 【强制】不要使用count(列名)或count(常量)来替代count(*),count(*)是sql92定义的标准统计行数语法，跟数据库无关，跟null与非null无关。
说明：count(*)会统计值为 NULL的行，而 count(列名)不会统计此列为 NULL值的行。
- 【强制】count(distinctcol) 计算该列除 NULL之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
- 【强制】当某一列的值全是 NULL时，count(col)的返回结果为 0，但 sum(col)的返回结果为NULL，因此使用 sum()时需注意 NPE问题。
正例：可以使用如下方式来避免 sum的 NPE问题：SELECT IF(ISNULL(SUM(g)),0,SUM(g)) from table。
- 【强制】使用 ISNULL()来判断是否为 NULL值。注意：NULL与任何值的直接比较都为 NULL。
1.NULL<>NULL的返回结果是 NULL，而不是 false；
2.NULL=NULL的返回结果是 NULL，而不是 true；
3.NULL<>1的返回结果是 NULL，而不是 true；
- 【强制】禁止使用存储过程、触发器、视图。
说明：让数据库做最擅长的事，降低业务耦合度。
- 【推荐】避免使用大表的 join。
说明：MySQL 最擅长的是单表的主键 / 二级索引查询,Join 消耗较多的内存，产生临时表。
- 【推荐】避免在数据库中进行数学运算
说明：容易将业务逻辑和 DB 耦合在一起,MySQL 不擅长数学运算和逻辑判断,无法使用索引。
- 【强制】拒绝大 SQL ，拆分成小 SQL。
- 【强制】使用 in 代替 or，in值个数控制在1000个以内。
- 【推荐】使用 union all 替代union 。
- 【强制】表查询中,禁用*作为查询列表字段,必须明确写出需要哪些字段。
- 【强制】POJO类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap中进行字段和属性的映射。
- 【强制】sqlmapper.xml配置参数使用：#{}，#param# 不要使用${} 此种方式容易出现 SQL注入。
- 【强制】任何功能的分页查询,必须确保执行的SQL语句是真分页,即”where 条件 LIMIT”.严禁使用类似IBATIS queryForList查询全部数据,之后通过sublist截取所需数据的假分页。
- 【强制】如非特殊需求,所有删除均为逻辑删除,即将is_delete字段由0(默认)至为1(已删除)。严禁使用delete和truncate table。
- 【强制】所有select、update、delete语句务必带有is_delete=0的查询条件。
- 【强制】更新记录时,必须同时更新对应记录的update_time字段值为数据库服务器的当前时间。
六、ORM规范
- 【强制】表查询中,禁用*作为查询列表字段,必须明确写出需要哪些字段。
- 【强制】POJO类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap中进行字段和属性的映射。
- 【强制】sqlmapper.xml配置参数使用：#{}，#param# 不要使用${} 此种方式容易出现 SQL注入。
- 【强制】任何功能的分页查询,必须确保执行的SQL语句是真分页,即”where 条件 LIMIT”.严禁使用类似IBATIS queryForList查询全部数据,之后通过sublist截取所需数据的假分页。
- 【强制】如非特殊需求,所有删除均为逻辑删除,即将is_delete字段由0(默认)至为1(已删除)。严禁使用delete和truncate table。
- 【强制】所有select、update、delete语句务必带有is_delete=0的查询条件。
- 【强制】更新记录时,必须同时更新对应记录的update_time字段值为数据库服务器的当前时间。