sql编写准则

表的优化设计

1.表达 “是/否”含义的字段，使用is_xxx方式命名，数据类型用unsignedtinyint（1表示是，0表示否）

说明：所有字段为非负数设计，字段类型用unsigned，使用tinyint类型。is_xxx的命名方式是为了明确其取值含义与取值范围。

例：表达逻辑删除的字段名is_deleted 1表示删除，0表示未删除。

2.1表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。

正例：t_book，t_department，t_base_client_manager

反例：BookAdmin，fct_，migration20181215，t_marketingprocessattachment

说明：数据库字段名在一开始的设计就要规范，因为修改的代价比较大（OLTP的业务产生阻塞）。表越大，并发度越高这种代价越明显，所以字段名称在设计时就需要慎重考虑。

MySQL在Windows下不区分大小写，但在Linux下默认是区分大小写。因此数据库名、表名、字段名，都不允许出现任何大写字母，避免不必要的麻烦。

2.2表的命名最好是遵循“t_业务名称_表的作用”

例：t_book，t_department，t_base_client_manager

3.表名不使用复数名词

说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量，应是单数形式，符合表达习惯。

4.表名、字段名禁用保留字

如desc、sql、range、match、delayed等

详情参考MySQL官方保留字。

5.索引命名规则

主键索引名为pk_字段名；

唯一索引名为uk_字段名；

普通索引名则为idx_字段名。

说明：pk_即primary key；uk_即unique key；idx_即index的简称

6.小数 字段设计规则

小数类型用decimal，禁止使用float和double

说明：在存储的时候，float和double都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小数并分开存储。

7.字符串字段设计规则

如果存储的字符串长度相等，使用char定长字符串类型，效率高于varchar变长字符串类型。

Varchar为变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

8.表必备的三字段

id,create_time,update_time

说明：其中id必为主键，类型为bigintunsigned、单表时自增、步长为1。

create_time,update_time的类型均为datetime，前者现在时表示主动式创建，后者过去分词表示被动式更新

9. OLTP的系统字段设计应尽量遵循三范式。业务确有需要，为提高查询性能允许适当冗余，但必须考虑数据一致。

冗余字段需遵循：

1） 不是频繁修改的字段

2） 不是唯一索引的字段

3） 不是varchar超长字段，更不能是text字段。

例（某电商）：各业务线经常冗余存储商品名称，避免查询时需要调用IC服务获取

10.字段设计合适的字符存储长度

不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

例：无符号值可以避免误存负数，且扩大了表示范围

对象	年龄上限	类型	字节	范围
人	160岁内	tinyint unsigned	1	无符号值：0到255
龟	1000岁内	smallint unsigned	2	无符号值：0到65535
恐龙化石	千万年	int unsigned	4	无符号值：0到约43亿
太阳	约50亿年	bigint unsigned	8	无符号值：0到约10的19次方

11.库名与应用名称尽量一致

12. 如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释

13. 单表行数超过2000万行或者单表容量超过10GB，建议进行分库分表。

另，如果预估未来三年数据量根本达不到这个级别，不要过度设计，勿在一开始创建表时就分库分表。

索引的优化设计

1. 善用唯一索引提高查询效率

业务上具备唯一特性的字段，即使是组合字段，也要尽量建成唯一索引。

说明：不要认为唯一索引影响了insert速度，这个速度损耗可以忽略，他提高查找速度是非常明显的；即便在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

2. 多表关联的效率规则,超过三个表禁止join。

需要join的字段，数据类型保持绝对一致；

多表关联查询时，保证被关联的字段需要有索引。

即使双表join也要注意表的索引、SQL性能。

3. varchar字段建立索引规则

在varchar字段上建立索引时须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

4. 怎样善用索引提高order by效率

有order by的场景，注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。

正例：where a=? and b=? order by c; 索引：a_b_c

反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引a_b无法排序

5. 善用覆盖索引来进行查询，避免回表

简单地讲，如果一本书需要知道第11章是什么标题，不用翻开第11章对应的那一页。目录浏览一下就好，这个目录就起到了覆盖索引的作用。

正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种。覆盖索引只是一种查询的效果。用explain的结果，extra列会出现：using index。

6. 利用延迟关联或者子查询优化超多分页场景

分页查询时MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行。当offset特别大的时候，效率就非常的低，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

正例：先快速定位需要获取的id段，然后再关联： SELECT t1.* FROM 表1 as t1, (select id from 表1 where 条件 LIMIT 100000,20 ) as t2 where t1.id=t2.id

7.SQL性能优化的目标

在执行计划中最少要达到 range 级别，要求是ref级别，如果可以是consts最好。

说明：

1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。

2） ref 指的是使用普通的索引（normal index）。

3） range 对索引进行范围检索。

反例：explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，当然比全表扫描还是快很多

8. 创建组合索引规则

建组合索引的时候，区分度最高的在最左边。

正例：如果where a=? and b=?，a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。

说明：存在非等号和等号混合判断条件时，在建索引时，把等号条件的列前置。如：where c>? and d=? 那么即使c的区分度更高，也必须把d放在索引的最前列，即建立组合索引idx_d_c。

9. 避免隐式转换 隐式转换，导致索引失效。

在多表关联时要避免因字段类型不同造成的隐式转换。

10. 索引创建的误区

1） 索引宁滥勿缺。认为一个查询就需要建一个索引。因此在一张表内创建了8个以上索引，导致优化器不知该选择哪个正确的执行计划而影响效率。

2） 吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。

3） 抵制唯一索引。认为唯一索引一律需要在应用层通过“先查后插”方式解决。

SQL语句规约

1. 禁止使用select *，只获取必要字段

Select col1, col2 from table_name;

select *会增加cpu/io/内存/带宽的消耗

指定字段查询，在表结构变更时，能保证对应用程序无影响

2. Insert必须指定字段

禁止使用insert into Tablename values()

Insert into tablename (col1, col2) values(1,’name’) ;

指定字段插入，在表结构变更时，能保证对应用程序无影响

3. 禁止使用属性隐式转换

比如：varchar、char类型的‘123’与 int 123 互转 等等

隐式类型转换会使索引失效，导致全表扫描。

4. 禁止在where条件列使用函数或者表达式

比如：where to_date(col1,’yyyy-mm-dd’)=‘2021-11-05’

会使索引失效，导致全表扫描。

5. 禁止负向查询

负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等

原因：会导致全表扫描

6. 范围查询给出确定启始值

>2 改写成 >=1

7. 禁止以%开头的模糊查询

%开头的模糊查询，会导致全表扫描

8. 禁止大表JOIN 和 子查询

会产生临时表，消耗较多内存与CPU，极大影响数据库性能。

9. 同一个字段上的OR必须改写成IN，IN的值尽量少于50个

in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，最大范围控制在1000个之内

会使索引失效，导致全表扫描。

10. 尽量使用join 代替子查询

更不要使用多级嵌套查询

使用join时，MySQL 不会在内存中创建临时表

11. 正确使用exist和in，必须小表驱动大表，即小的数据集驱动大的数据集

举例：

A，B 两表通过id 字段关联。

当B 表的数据集小于A 表时，用in更优化。使用in，两表执行顺序是先查B 表，再查A 表

select * from A where id in (select id from B)

当A 表的数据集小于B 表时，用exist更优化。使用exists，两表执行顺序是先查A 表，再查B 表

select * from A where exists (select 1 from B where B.id = A.id)

行业规约

1. 应用程序必须捕获SQL异常

方便定位问题，并及时作出相应处理。

2. NULL与任何值的直接比较都为NULL。

1） NULL<>NULL的返回结果是NULL，而不是false。

2） NULL=NULL的返回结果是NULL，而不是true。

3） NULL<>1的返回结果是NULL，而不是true。

3. 不得使用外键与级联

一切外键概念必须在应用层解决。

例：学生表中的student_id是主键，成绩表中的student_id为外键。如果更新学生表中的student_id，同时触发成绩表中的student_id更新，即为级联更新。

外键与级联更新适用于单线程场景；级联更新是强阻塞，存在数据库更新风暴的风险；

外键影响数据库的插入速度。

4. 禁止使用存储过程

存储过程难以调试、扩展，更没有移植性故在行业中是规避使用的。

5. 修正生产数据前必须先查询出结果集

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

6. 多表关联每个字段必须有别名

数据库中表记录的查询、变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。

说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。

正例：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;

反例：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出1052异常：Column 'name' in field list is ambiguous。

7. Truncate和Delete的注意事项

TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但TRUNCATE无事务且不触发trigger，有可能造成事故，故在开发代码中禁止使用此语句。

说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同

更多消息资讯，请访问昂焱数据。https://www.ayshuju.com/home