mysql数据库规约

一、建表规约

1、如果表达是否概念时，字段名使用is_xxx，0表示是，1表示否，如果确定字段一定是非负数使用unsigned。

2、表名和字段名必须使用小写或数字，禁止数字开头，避免两个下划线中间只有数字。

3、表名不使用复数名词，表名只代表实体，并不代表实体个数

4、禁止使用保留字，如：desc/match/range，参考mysql官方保留字（http://blog.csdn.net/xfg0218/article/details/42713281）。

5、主键索引名以pk_字段名，唯一索引用uk_字段名，普通索引用idx_字段名。

6、小数使用decimal，禁止使用float和double，有可能存在损失精度的问题，如果超过了decimal的表示范围使用整数部分和小数部分分开储存。

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

8、varchar是可变字符串长度，不预先分配存储空间，如果长度超过5000，使用text类型，独立出来一张表，使用主键对应，避免影响索引效率。

9、表必备三个字段：id，gmt_create,gmt_modified，类型为date_time。

10、表名最好加上业务名称_表的作用，如：trigger_task,mmp_config

11、库名和应用名称尽量一致。

12、如果修改字段含义或者字段状态有追加时，及时更新字段的注释。

13、字段允许适当冗余，但必须考虑数据一致，冗余字段应该遵循：

1）不是频繁修改的字段。

2）不是varchar超长字段更不是text字段。

14、单表行数超过500万或者单表容量超过2G才考虑分库分表。

15、合适的字段长度不但节约表空间，更加重要的是提高检索速度。

 

二、索引规约

1、业务上具有唯一特性的字段，即使是多个字段组合，也必须建成唯一索引。不要以为唯一索引降低了insert速度，但是它提高了查询速度，而且避免脏数据的产生。

2、超过三表禁止join，需要join的字段数据类型必须绝对一致，多表关联查询时保证关联字段一定要有索引，即使双表join也要注意索引和sql性能。

3、在varchar字段上建立索引时必须指定索引的长度，没必要对全字段进行索引。

4、页面搜索严禁使用左模糊和全模糊查询，如果要使用请走搜索引擎。

5、如果有order by，注意索引的有序性order by最后的字段是组合索引的一部分，并且放在索引组合顺序的最后。比如：where a=？ and b=？ order by c 索引：a_b_c

6、利用覆盖索引来查询，避免回表。能够建立索引的种类：主键索引、唯一索引、普通索引。覆盖索引是一种查询的一种效果，用explain ，extra列会出现using index。

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

mysql并不是跳过offset行是取offset + N行，然后放弃前offset行，返回N行，那么当offset特别大时就会效率低下，要么控制返回的总页数，要么对超过阈值的页数进行sql改写。

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

consts：单表只有一个匹配行，在优化阶段就可以拿到数据

ref：使用普通索引

range：对索引进行范围检索

9、建立组合索引时，区分度最高的放在最左边，如：where a=? and b=? a的列几乎接近唯一值，只需要建idx_a即可。

10、防止因字段类型不同而造成隐式转换导致索引失败。

11、创建索引时避免以下误区：

1）宁滥勿缺：认为一个查询就需要建立一个索引

2）宁缺毋滥：认为索引会严重影响更新和新增的速度

3）抵制唯一索引，认为业务唯一性一定要通过先查后插来解决。

12、索引列不用于计算。

13、禁止重复索引，比如在主键上建立索引，主键本身就有索引的作用。

14、不要再低基数列上建立索引，如：性别。

15、索引的选择性：是指 不重复数据/记录总数 的比值，索引的选择性越高则查询的效率越高，唯一索引的比值是1，所以效率最高。

16、前缀索引：对应text、blob、很长的varchar类型必须使用前缀索引，因为mysql不允许索引这些列的完整长度。

17、组合索引：不是将所有查询条件中的字段都放入索引中，而是要让一个索引尽量多的被多个查询使用，尽量减少一个表上的索引数目，减少因数据更新带来的索引更新成本，和索引存储空间。

18、尽量避免null：

1）可以把字段的默认值设置为‘’或者0；

2）null列会使索引统计和值更加复杂；

3）null列需要更多的存储空间，还需要mysql内部进行特殊处理；

4）null列加索引，每条记录都需要一个额外的字节；

 

三、sql语句

1、不要使用count（列名）或者count（常量）来代替count（*），count（列名）不会统计该列为null的记录。

2、使用count（distinct col）来统计该列除null以外不重复行数。

3、当某一列的值全是null时，count（col）结果为0，sum（col）结果为null，所以要防止NPE问题，正确的方式：select if(isnull(sum(g)),0,sum(g)) 。

4、使用isnull判断是不是空值，null和任何值比较结果都是null。

5、代码分页查询逻辑，如果count为0直接返回结果，避免执行分页查询。

6、不得使用外键和级联，一切外键的概念必须在应用层解决，外键与级联适用于单机低并发，不适用于分布式高并发。

7、禁止使用存储过程，存储过程难以调试和扩展，更加没有可移植性。

8、in操作能避免则避免，如果不能避免要保证后面的元素个数不超过1000个。

9、如果需要全球化，所有的字符存储均以utf8的形式，如果是表情使用utfmb4。

10、truncate table比delete更加快，没有事务，不触发触发器，但是容易造成事故，不建议使用。