mysql 建表uk_MySQL 建表规范 以及注意

参考 Alibaba  P3C 规范文档

建表规范

1).在使用判断是否的情况下，使用 is_xxx的命名，并且数据类型是unsigned tinyint(1表示为真 0表示为假)。

2).所有的表的表明必须以小写字母+数字(windows不分大小写，Linux分大小写),且两个下划线中间不出现数字(错误：table1_3_system)。

3).以单数形式写表名，不要使用desc,range,match,delayed等MySQL保留字段,索引名称规范为：

pk_xx 表示primary key(主键索引)； uk_xx表示unique key (唯一索引)；idx_xx表普通索引

4).小数使用 decimal ，可以定义常用的如价格，工资，汇款等等。

5).长度(0-255) 且长度基本一致的使用char，长度(0-5000)不预先设置存储空间的，可以使用varchar，但长度大于5000，则使用text来处理，并且新建一张表来存储，以主键绑定，避免影响其他字段索引的效率。

6).一个表必备的三个字段，id，gmt_create,gmt_modified，id表主键应该是无符号的，可以是int或bigint，后面两个为datetime类型，前者表示创建时间，后者表示被动修改时间

7).命名规范，一般以“业务名_功能名”，库名应当和应用名称一致。

8).表是允许适当的冗余，这样提高查询速度，当数据大时，效果很明显。但也需要注意，应当是不频繁变化的字段，且不应该是varchar超长字符，更不宜是text文本。

9).单表内容超过500W行或者存储大于2GB，才推荐分库分表，如果预计三年内达不到该值，在设计是可以不考虑分库分表。

10).适当的字符存储长度，不但节约存储空间，节约索引存储，同时提升了检索的速度。

2.索引

1).业务上具有唯一特性的字段，即是是组合也应当建成唯一索引。(不要以为唯一索引影响insert的速度，其实可以忽略，与之相比带来的查询速度是非常大的，另外，在应用层即是是做了数据的校验，但是只要是没有唯一的索引，根据墨菲定律，必然产生脏数据。)

2).超过三个表禁止使用join。需要使用join的需要注意索引与sql性能，同时数据类型需要一致。多表关联查询时，需要保证被关联的字段设置了索引。

3).为varchar设置索引需要指定其长度，没必要对全文本进行索引建立，一般在20长度区分度在90%以上，可以使用count(distinct left(列名，索引长度))/count(*)的区分度来判断。

4).页面搜索禁用左模糊和全模糊,如果需要则使用搜索引擎。索引文件具有B-Tree的最左前缀匹配特性，如果左边未确定则不能使用该索引。

5).如果与有order by 的场景，请注意利用索引的有序性。order by最后的字段是组合索引的一部分，请别放到组合索引的最后，避免出现file_sort，影响查询性能。如： where a = ? b = ? order by c; 索引为:a_b_c. 当索引中有范围查找，那么无法利用有序性。如 where a > 10 order by b; 索引为 a_b.

6).利用覆盖索引进行查询。用explain的结果，extra列会出现:using index.

7).利用延迟关联或子查询优化超多分页场景。也就是先定位查询值，再联合查询。

如: select a.* from table1 as a ,(select id from table2  where 条件 limit 1000,100)as b where a.id =b.id

8).SQL 性能优化目标：至少是range级别，要求是ref级别，如果可以的话应当是consts.

consts : 只有一条匹配数据(主键或者唯一索引)

ref: 指的是使用普通的索引查询

range: 对索引进行范围搜索

9). 建立的组合索引，区分度最高的查询在左边。

如果: where a=? b=?,a列基本上能区分出唯一值，那么只需要建立a的索引就可以。

说明： 存在等号和非等号的查询情况下，在建索引的时候，请把等号的前置。如：where a =? and b =? 即是a的区分度更高，也需要把b放在前面，即uk_b_a;

10).防止因字段类型不同，造成隐式的转换，导致索引失效。

11).极端

宁滥勿缺 ： 认为一个查询就需要一个索引。

宁缺毋滥： 认为索引会消耗控件，严重拖慢更新和新增的速度

抵制唯一索引：认为业务的唯一性一律通过应用层检查,并通过”先查后插”方式解决。

3. SQL语句

1). 不要使用count(列名) 或者count(1) 来替代count(*)，count(*)是sql定义的用来统计的标准语法。count(* )会统计值为NULL的行，但是count(列名) 不会统计。

2).count(distinct col) 计算该列值非NULL且非重复的行，count(distinct  col1,col2)如果第一列值为空，那么即是另一列有不同的值，也是返回0.

3).需要注意的一个问题是count(*)未找到行是返回0，而sum(col)则是返回NULL，因此会出现NPE。可以使用 select if(ISNULL(sum(g)),0,sum(g)) from table.

4).ISNULL()是判断是否为NULL，null与任何值比较都是null，而不是true 或者false。

5).当count(*)=0 时，应当结束掉后面需要分页的操作。

6).不使用外键与级联。一切外键概念需要在应用层解决。

以学生表和成绩表为例，学生表的student_id是主键，而成绩表中的student_id则为外键。在更新学生表时，同时需要更新成绩表，那么这是一个级联的更新。外键，级联是单机低并发，不适合分布式，并有影响数据更新，影响插入性能。

7).不使用存储过程。

8).数据修好时，需要先select，在删除。

9).in尽量不使用，如果需要使用则需要控制in后面的数量为1000个以内。