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）抵制唯一索引，认为业务唯一性一定要通过先查后插来解决。

mysql数据库

标签：explain   模糊   组合索引   creat   类型   建表   sql   使用   官方