MySQL 开发手册规约

一：建表规约

1、表达是与否的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint(1表示是，0表示否）

　　注意：POJO类中的任何布尔类型的变量，都不要加is前缀，需要在<resultMap>设置is_xxx到XXX的映射关系。数据库表示是与否的值，使用tinyint类型，坚持is_XXX的命名方式为了明确其取值含义和范围。

　　例子：表达逻辑删除is_deleted,1表示删除,2表示未删除。

2、表名、字段名必须使用小写字母或数字，禁止出现数字开头禁止两个下划线中间只出现一个数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。

　　说明：MYSQL在windows下不区分大小写，但在Linux下默认是区分大小写的。因此数据库名、表名、字段名、都不允许出现任何大写字母。

3、表名不使用复数名词。

4、禁用保留字，如desc、range、match、delayed等。

5、主键索引名pk_字段名(primary_XXX)；唯一索引名为 uk_XXX；普通索引名为  idx_XXX

6、小数类型为decimal，禁止使用float和double。

在存储的时候，float和double都存在精度损失的问题，很可能在比较值的时候，得不到正确结果。

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

8、varchar是可变长字符串，不预先分配存储空间，长度不要唱过5000，如果存储超过了这个值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其他字段的索引率。

9、表必备的3个字段：id、create_time、update_time

10、在数据库中不能物理删除，都是逻辑操作。

11、表名一般使用“业务名称_表的作用”

例子alipay_tast   /    force_project       /   trade_config

12、字段允许适当冗余，以提高查询性能，但必须考虑数据一致性。冗余字段应该遵循：

　　1、不是频繁修改的字段

　　2、不是唯一索引的字段

　　3、不是varchar超长字段，更不能是text字段。

13、单表行数超过500万行或者单标容量超过2GB，推荐进行分库分表。

说明：如果预计三年后的数据量根本达不到这个级别，不要在创建表的时候就分库分表。

 

 

二、索引规约

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

说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略的，但提高查找速度是明显的；另外即使在应用层做了非常完善的校验，只要没有唯一索引，根据墨菲定律，必定有脏数据产生。

2、超过三个表禁止join，需要join的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引；

3、在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。count(distinct left(列名，索引长度))/count(*)的区分度来确定。

4、如果有order by的场景，请注意利用索引的有序性。order by最后的字段是组合索引的一部分，并且放在组合顺序的最后，避免出现filesort的情况，影响查询性能。

5、页面搜索严禁左模糊或者全模糊，如果需要应该是用搜索引擎(Elastic Search)来解决。

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

6、利用覆盖索引来进行查询操作，避免回表

extra列会出现：using index；

7、利用延迟关联或者子查询优化超多分页场景。

说明：MYSQL并不是跳过offset行，而是取OFFSET+N行，然后返回放弃前offset行，返回N行，当offset特别大的时候，效率就非常低下。

正例：先快速定位需要获取的id段，然后再关联。

select id form where limit，（select id from 表1 where 条件 limit 10000,20） as t2 where t1.id == t2.id

8、SQL性能优化目标：至少要达到range级别，要求是ref级别，如果是const最好。

explain 表的结果，type = index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全 表扫描是小巫见大巫。

9、建组合索引的时候，区分度最高的在最左边

where a=区分度高 and b=? a列几乎接近与唯一值，那么只需要单建inx_a索引即可。

说明：存在非等号和等号混合判断条件时，在建索引时，需要把等号条件的列前置。如:where c>? and d = ? 那么C区分度更高，也应该把d放在最前列

10、防止因字段类型不同造成隐私转换，导致索引失效。