一：建表规约
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、防止因字段类型不同造成隐私转换，导致索引失效。
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/289763.html