MySQL数据库设计规范及SQL规范

一、基本规范

1. 【强制】如非特殊需求，OLTP类系统的所有表应采用Innodb引擎。

说明：5.5 以后的默认引擘，支持事务，行级锁，更好的恢复性，高并发下性能更好，对多核，大内存， SSD等硬件支持更好。

1. 【强制】禁止使用分区表。

说明：MySQL 的分区表实际性能不是很好，且管理维护成本较高。

1. 【强制】如非特殊需求，表字符集建议使用 utf8，如果涉及存储表情符使用utfmb4。

说明：使用 utf8 字符集，如果是汉字，占 3 个字节，但 ASCII 码字符还是 1 个字节；统一，不会有转换产生乱码风险。

1. 【强制】如非特殊需求，禁止在数据库中存储图片、文件等大数据。
2. 【强制】所有表、列需要添加注释，单表数据量建议控制在500万以内;如涉及状态描述的字段修改时,需及时同步字段和PO字段注释,确保一致性。
3. 【推荐】库名与应用名保持一致。
4. 【强制】库名、表名、字段名使用小写字母并采用下划线分割。 
5. 【强制】库名、表名、字段名最长32个字符，易于辨识以及减少传输量。
6. 【强制】库名、表名、字段名禁止使用 MySQL 保留关键字，如match,range等。
7. 【强制】临时库、临时表名以 tmp 为前缀并以日期为后缀。
8. 【强制】备份库、备份表名以 bak 为前缀并以日期为后缀。 
9. 【强制】主键索引名为pk_字段名,唯一索引为uk_字段名,普通索引为idx_字段名。

二、命名规范

说明：pk=primary key;   uk= unique key;    idx=即index的缩写。

1. 【强制】表达“是/否”概念的字段必须使用is_xxxx格式命名（yn字段除外），数据类型为tinyint(1表示是，0表示否)。
2. 【推荐】推荐“业务名_表的作用”形式对表命名。

正例：project_task、project_config

三、表设计规范

1. 【强制】所有字段均定义为 not null。
2. 【强制】表必备四个字段，id,create_time,update_time,is_delete。

说明：id 为主键，步长为1自增的bigint类型；

      create_time、update_time 为创建和修改时间，均为date_time类型；

    is_delete 为删除标识，默认为0，1表示已删除；

1. 【强制】如果存储的字符长度几乎相等，使用char，而非varchar。
2. 【推荐】小数类型使用decimal禁用float和double。

 说明：float和double在存储的时候,存储精度损失问题,很可能在值比较时得不到正确的结果,如果存储数据范围超过decimal的范围,建议将数据拆成整数和小数分开存储;

1. 【推荐】单表字段数控制在 20 个以内，允许一定程度的字段冗余，以提高查询性能，但必须充分考虑数据一致性问题，冗余字段应遵循：

1.非频繁修改字段；

2.非varchar超长字段，更不能是text、BLOB字段；

正例：四级地址名称，字符长度较短，几乎不会产生变，适合冗余以减少关联查询；

1. 【推荐】单表超过500万行或表容量超过2GB，才推荐考虑分库分表。

说明：如果需求调研过程中可预料系统将存储海量数据，参考第五条，否则，如三年后才能达到500万或超2GB的表容量，不推荐设计之初考虑分库分表方案。

1. 【推荐】采用合理的分库分表策略，推荐使用 HASH 进行分表，表名后缀使用十进制数，下标从 0 开始，首次分表做好评估调研，避免二次分表，二次分表的难度和成本较高。
2. 【推荐】使用适合的类型存储数据。

说明：适合的类型，不但节约存储空间，节约索引存储，更重要的是可以提升检索速度；

      如下表,无符号值可避免误存负数,且扩大的表示范围

对象	年龄区间	类型	表示范围
人	150岁以内	unsigned tinyint	无符号值：0到255
龟	数百岁	unsigned smallint	无符号值：0到65535
恐龙化石	数千万年	unsigned int	无符号值：0到42.9亿
太阳	约50亿年	unsigned bigint	无符号值：0到10的19次方

四、索引规范

1. 【强制】单张表中索引数量不超过5个；
2. 【强制】单个索引中的字段数不超过5个；
3. 【强制】禁用外键，外键用来保护参照完整性，可在业务端实现，对父表和子表的操作会相互影响，降低可用性；
4. 【强制】字符串建立索引时，必须使用前缀索引，没必要对全字段建立索引，建议前缀索引长度控制在20个字符以内；

说明：索引长度与区分度是一对矛盾体，一般对于字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名,索引长度))/count(*)的区分度来确定。前缀索引相关内容可参考MySQL前缀索引和索引选择性 - yayun - 博客园

1. 【强制】禁用左模糊或全模糊,如有需要请走搜索引擎来处理。

说明：索引文件具有B-Tree的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。

1. 【推荐】创建索引/复合索引时区分度最高的放在最左边。
2. 【推荐】如果有order by的场景,请注意利用索引的有序性,order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免file sort的情况,影响查询性能。

正例:where a=? and b=? order by c;索引a_b_c。

反例:索引中有范围查找,那么无法复用索引有序性,如where a>100 group by b; 索引a_b无法排序。

1. 【强制】不在低区分度的字段上创建索引，例如：性别。
2. 【强制】避免冗余或重复索引。

说明：合理创建联合索引（避免冗余）， index(a,b,c) 相当于 index(a) 、 index(a,b) 、 index(a,b,c)。

1. 【强制】避免因字段类型不同造成的隐式转换,导致不走索引,全表扫描。

说明：如在user_info表phone_num字段为char(11)。

正例:SELECT * FROM user_info WHERE phone_num=’13599998888’

反例:SELECT * FROM user_info WHERE phone_num=13599998888

1. 【强制】不在索引列进行数学运算和函数运算。
2. 【强制】不使用反向查询，如not in/not like。
3. 【推荐】利用覆盖索引来进行查询操作。

说明:一个包含查询所需的字段的索引称为 covering index 覆盖索引。MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表操作，减少IO，提供效率。 当你对一个sql 使用explain statement 查看一个sql的执行计划时，在EXPLAIN的Extra列出现Using Index提示时，就说明该select查询使用了覆盖索引。

1. 【推荐】SQL性能优化的目标:至少达到range级别,建议达到ref级别,最好为consts级别

说明:

consts单表中最多只有一个匹配行(主键或唯一索引)；

ref为普通的索引；

range对索引进行范围查询;

反例:explain表的结果,type=index,索引物理文件全扫描 ,速度非常慢,该index级别比range低,与全表扫描小巫见大巫。

1. 【推荐】利用延迟关联或者子查询优化超多分页场景。

说明: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. 【参考】创建索引时避免有如下极端误解:

1.宁滥勿缺,误认为一个查询就需要建一个索引；

2.宁缺勿滥,误认为索引会消耗空间,严重拖慢新增和更新速度 ;

3.抵制唯一索引,误认为唯一性需要后台应用通过”先查后插”解决；

五、SQL规范

1. 【强制】不要使用count(列名)或count(常量)来替代count(*),count(*)是sql92定义的标准统计行数语法，跟数据库无关，跟null与非null无关。

说明：count(*)会统计值为 NULL的行，而 count(列名)不会统计此列为 NULL值的行。

1. 【强制】count(distinctcol) 计算该列除 NULL之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
2. 【强制】当某一列的值全是 NULL时，count(col)的返回结果为 0，但 sum(col)的返回结果为NULL，因此使用 sum()时需注意 NPE问题。

正例：可以使用如下方式来避免 sum的 NPE问题：SELECT IF(ISNULL(SUM(g)),0,SUM(g)) from table。

1. 【强制】使用 ISNULL()来判断是否为 NULL值。注意：NULL与任何值的直接比较都为 NULL。

1.NULL<>NULL的返回结果是 NULL，而不是 false；

2.NULL=NULL的返回结果是 NULL，而不是 true；

3.NULL<>1的返回结果是 NULL，而不是 true；

1. 【强制】禁止使用存储过程、触发器、视图。

说明：让数据库做最擅长的事，降低业务耦合度。

1. 【推荐】避免使用大表的 join。

说明：MySQL 最擅长的是单表的主键 / 二级索引查询,Join 消耗较多的内存，产生临时表。

1. 【推荐】避免在数据库中进行数学运算

说明：容易将业务逻辑和 DB 耦合在一起,MySQL 不擅长数学运算和逻辑判断,无法使用索引。

1. 【强制】拒绝大 SQL ，拆分成小 SQL。
2. 【强制】使用 in 代替 or，in值个数控制在1000个以内。
3. 【推荐】使用 union all 替代union 。
4. 【强制】表查询中,禁用*作为查询列表字段,必须明确写出需要哪些字段。
5. 【强制】POJO类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap中进行字段和属性的映射。
6. 【强制】sqlmapper.xml配置参数使用：#{}，#param# 不要使用${} 此种方式容易出现 SQL注入。
7. 【强制】任何功能的分页查询,必须确保执行的SQL语句是真分页,即”where 条件 LIMIT”.严禁使用类似IBATIS queryForList查询全部数据,之后通过sublist截取所需数据的假分页。
8. 【强制】如非特殊需求,所有删除均为逻辑删除,即将is_delete字段由0(默认)至为1(已删除)。严禁使用delete和truncate table。
9. 【强制】所有select、update、delete语句务必带有is_delete=0的查询条件。
10. 【强制】更新记录时,必须同时更新对应记录的update_time字段值为数据库服务器的当前时间。

六、ORM规范

1. 【强制】表查询中,禁用*作为查询列表字段,必须明确写出需要哪些字段。
2. 【强制】POJO类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap中进行字段和属性的映射。
3. 【强制】sqlmapper.xml配置参数使用：#{}，#param# 不要使用${} 此种方式容易出现 SQL注入。
4. 【强制】任何功能的分页查询,必须确保执行的SQL语句是真分页,即”where 条件 LIMIT”.严禁使用类似IBATIS queryForList查询全部数据,之后通过sublist截取所需数据的假分页。
5. 【强制】如非特殊需求,所有删除均为逻辑删除,即将is_delete字段由0(默认)至为1(已删除)。严禁使用delete和truncate table。
6. 【强制】所有select、update、delete语句务必带有is_delete=0的查询条件。
7. 【强制】更新记录时,必须同时更新对应记录的update_time字段值为数据库服务器的当前时间。