SQL编写规范

1.命名规范：

1.库名、表名、字段名，必须使用小写字母或数字，不得超过30个字符。

2.库名、表名、字段名，禁止出现数字开头，禁止两个下划线中间只出现数字。（说明： MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名 ，都不允许出现任何大写字母，避免节外生枝 正例： aliyun _ admin ， rdc _ config ， level 3_ name 反例： AliyunAdmin ， rdcConfig ， level 3 name）

3.库名、表名、字段名，必须见名知意，使用下划线分割，禁止使用MySQL保留字 ，如 desc 、 range 、 match 、 delayed 等，请参考 MySQL 官方保留字。

4.临时库、表名，必须以tmp为前缀，以日期为后缀，例如tmp_product_20130704 。

5.普通索引必须按照“idx_字段名称[_字段名称]”进行命名，例如idx_creator_id

6.唯一索引必须按照“uniq_字段名称[_字段名称]”进行命名，例如 uniq_creator_id。

7.索引名必须全部使用小写，过长的字段名可以采⽤缩写形式，例如 idx_creator_id_time。

8.表的命名最好是加上“业务名称_表的作用”。

9.库名与应用名称尽量一致。

2.库表设计：

1.所有字段及表都必须有注释，存储引擎必须使用InnoDB。

2. 建表时表必须有主键，使用bigint unsigned类型，并使用auto_increment自增 标记，且不要修改主键的值。（说明：线上数据库均采用InnoDB存储引擎，其为聚簇索引组织表，自增主键可以避免插入数据过程中page的分裂 合并，减少表碎片化，提高空间和内存使用，提高插入数据的性能。另外避免在row模式下主从复制异常。）

3.必须使用DECIMAL代替FLOAT和DOUBLE，以存储精确浮点数，例如支付相关数据。（说明： float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存 储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。）

4.必须使用TINYINT系类型代替ENUM类型，前者只要自己定义，后者却要修改表。

5.禁止使用BLOB类型。

6.所有字段必须定义为NOT NULL，默认值定义为default 0或default ‘’。

7. 表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint（1 表示是，0 表示否）（阿里巴巴实体类命名不建议对于boolean型变量命名以is开头，原始是：部分框架解析会引起序列化错误。）。

8.禁止隐式转换，数值类型禁止加引号，字符和日期类型必须加引号。（说明：当关联条件数据类型不一致的时候走不了索引。）

9.禁止使用外键，防止死锁，避免隐藏的数据逻辑，一切外键概念必须在应用层解 决。

10.禁止使用存储过程及视图，其难以调试和扩展，更没有移植性。

11.建议最多更改和查询的字段放在基础表内，方便完整载入内存。

12.建议访问频率低的或大字段放到扩展表里，分离冷热数据。

13.尽可能不使用TEXT类型。

14.在满足需求的前提下，varchar字段尽量使用最少的字符数量，越少越有利，建议 最多不要超过500个字符。

15.如果存储的字符串长度几乎相等，建议使用char定长字符串类型。

16.数据量随时间增长的表，需要考虑做好历史数据的归档。

3.查询数据：

1.只查询需要的字段，禁止使用select * ，子查询只允许返回主键和必须字段，禁 止select *。（说明：禁用select *，1、避免表结构变更导致程序因找不到相关字段报错，2、尽可能减少查询需要传输的IO流， 加快查询速度。）

2.统计行数时，使用COUNT(*)或COUNT(1)，禁止使用count(字段名)。（说明： count( * ) 会统计值为 NULL 的行，而 count( 列名 ) 不会统计此列为 NULL 值的行，造成统计不准确。）

3.禁止使用order by rand()实现乱序效果，会导致CPU过高。

4.分批获取大量数据时，禁止大偏移量的limitM,N语句，使用主键游标where PK>…limit N或利用延迟关联、子查询优化超多分页场景。

5.必须使用ISNULL()来判断是否为NULL值。

6.在代码中写分页查询逻辑时，若 count 为 0 必须直接返回，避免执行后面的分 页语句。

7.需要 join 的字段，数据类型必须绝对一致，多表关联查询时，必须保证被关联的 字段需要有索引。

8.多张关联表之间，建议适当的冗余字段，可以减少JOIN查询。

9.尽量避免使用反向匹配，例如notin、!=、notlike，无法用到索引。

10.同字段OR条件，用IN代替，包含的值个数应少于300个。

11.尽量减少与数据库交互次数，尽量采用批量递交、块插入和缓存(memcache)。

12.使用prepared statement批量递交语句，可以提升性能，且避免SQL注⼊。

13.尽量避免在SQL中进行算术和函数计算，应放置到应用服务器端。

14.可以拆分复杂的JOIN为多个小SQL，避免大语句。

4.修改数据：

1.同一张表ALTER多个字段，必须将需要修改的字段拼接成一条sql执行，如ALTER TABLE table_name drop column …, add column…,…。

2.写入语句中禁止出现结果不确定的函数，如sysdate()、rand()、current_user() 等。

3.数据订正（特别是删除、修改记录操作）时，必须先select，避免出现误删除， 确认无误才能执行更新语句。

4.INSERT语句必须指定字段列表，禁止使用INSERT INTO xxx values ()。

5.DELETE和UPDATE语句，必须要有where条件，不要产生全表更新和删除的语 句。

6.禁止单条SQL语句同时更新多个表，拆分成多条SQL，放在一个事务里。

7.有批量写入数据需求，尽量使用INSERT INTO xxx values (…),(…),(…)…形式，且 保证一次批量的数据在1M以内。

8.程序应有捕获SQL异常的处理机制，必要时通过rollback显式回滚。

9.尽量避免大事务，这会锁住更多的资源，引发更多的等待和竞争。

10.不同事务对同一批表的操作，要前后顺序一致。

5.索引创建：

1.执行频率高的SQL和重要功能的SQL，都必须能有索引可用。

2.多表JOIN的字段，都必须建有索引。

3.页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

4.禁止冗余索引，如已有(a,b)索引，则无需(a)索引。

5.组合索引中，区分度大(高筛选度)的字段必须放在最前。

6.业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

7.如多个字段组合有唯一性需要，可以创建唯一索引。

8.尽可能利用索引完成排序，即排序的字段在索引里，且不使用降序排序。

9.适度将组合索引提升为覆盖索引，避免回表，减少IO。

10.对较长字符串可使用前缀索引，前缀索引长度由数据区分度确定。

11.建议不在低基数(低筛选度)的列上建立索引，例如“性别”。

12.合理创建组合索引，(a,b,c)相当于(a)、(a,b)、(a,b,c),组合索引的组成字段数尽量 不超过3个。