SQL书写规范(通用)

SQL语句是访问DB中数据的主要途径和方式，所以SQL的书写对访问数据库时的速度和性能起着至关重要的作用。

本文针对SQL日常书写中遇到的一些常见问题和注意事项做说明。

注：本文基本适用大多类型的RDBMS关系型数据库：Oracle，SQLServer, MySQL...

一.如何使用高效的索引

1. 索引的作用？

举个例子来说明索引的作用：

一本书西游记一共1250页，现在你想找到孙悟空大战黄狮精的章节拿来看，如果你一页页的翻书找可能要翻1000多页纸才能找到对应章节，但是如果你通过目录来查找就很快速了，在目录中很容易的找到 孙悟空大战黄狮精在第836页，直接去书中第836页就能看到 孙悟空大战黄狮精的章节 所有内容了。

数据库中的表就如同一本书，索引如同目录。书的目录存放着章节内容标题和页码的对应关系，而索引存放着数据键值和表中该行对应的位置，知道的数据在表中位置就可以快速的访问到数据行，这样通过索引你就可以快速查出表中想要的数据行了。

2.什么样需求建立索引合理？

前面提到了使用索引可以检索访问表中数据行，那我们是不是把任何栏位都就建立上索引都好使嘛，当然不是。

举个例子:

公司有3万员工，数据库中员工基本信息表EMP，主要栏位有工号EMP_NO,姓名EMP_NAME,性别EMP_SEX,部门DEPT,入职时间WORK_DATE等

需求一： 需要通过工号或者姓名查询员工的详细信息

因为工号是唯一的，姓名相同的人也不是很多，对 EMP_NO和 EMP_NAME 分别建立一个单独的索引都会对检索性能提高很多。

需求二： 查询公司的男员工有多少

因为性别只是二种， 建立索引就不合理了， 为什么？因为索引中存放的是键值和行的物理位置。如果通过索引你需要把索引中所有性别为男的键值对应的物理位置都找出来(光检索索引就消耗大量资源)，再通过这些位置回表到出对应所有行的数据，效率很低了。

以上主要涉及索引selectivity(选择性)的概念，索引选择性计算公式：

索引选择性 = 基数 / 总行数 

基数 :键值在table中行数

总行数:table总行数

意思公式算出值越小选择性越强，类似EMP_NO,EMP_NAME选择性就很强， EMP_SEX 选择性就很弱。

这里结合本人经验及多位专家指出设计和选择索引栏位时，建议选择的栏位索引选择性的值小于1/100 以上，而极端情况下可以到1/10

3.什么样情形用不到索引？

很多时候有人说明明建立了引为何SQL还是很慢，可以通过执行计划看到索引没有被使用。为什么没使用？可能的原因以下：

一.索引栏位使用函数

如索引栏位serial_number使用了函数 upper使用不到索引

正确方法：如果迫不得已需要在索引栏位中使用函数，可以建立函数索引替代普通索引

二.不匹配数据类型做 比较

如WROK_ID栏位是VARCHAR2类型，但是SQL写为 WROK_ID =20190507，字符类型和一个数字去=比较 ， DB的查询优化器会在解析时做一个to_number( WROK_ID) =20190507 的隐式转换，这样也用不到索引

正确方法：

直接使用 WROK_ID ='20190507 '字符做比较

三 . 模糊查询 like ‘%xxx%’ 或‘%xxx’

因为索引Btree数据结构决定，在检索值最前面加% like查询是无法索引索引的

且只能在 检索值最后面加% like查询，可使用索引

正确方法：

避免like‘%xxx%’写法，如有特殊需求查询like ‘%xxx’可参考反向索引文章：
http://blog.itpub.net/25583515/viewspace-2146401/

四 .组合索引没有使用先导列

CREATE INDEX SFISM4.GWL_INDEX ON SFISM4.R_STATION_REC_T

(GROUP_NAME, WORK_DATE, LINE_NAME)

组合索引 (GROUP_NAME, WORK_DATE, LINE_NAME)

因为索引Btree数据结构决定，如果在查询时没有指定先导列( leading column )第一列 GROUP_NAME，只写 WORK_DATE, LINE_NAME 时用不到高性能 索引的 RANGE SCAN，只能使用FULL TAB SCAN或者效能并不高的INDEX SKIP SCAN

正确方法：

写全先导列查询或设计新的索引

五 .不等于查询

<>  != 的不等查询无法使用索引

正确方法：

重新评估业务逻辑，以其它变通方法解决

六 .NULL,空值

Oracle 无法和NULL 及''，NOT NULL 做比较时使用索引

(注，像MySQL innodb的默认定义null栏位 IS NULL是可以使用索引，SQL server非聚族索引的NULL也是可以被索引的)

正确方法：

Oracle中避免和空比较

七 .不合理的where条件

比如SN_DETAIL表保留着三年内的数据，现在你要查询2017年1月到2019年1月的数据，因为DB的优化器会认为数据量过大，使用索引还不如全表扫描效率

正确方法：

重新评估需求，必要时找DBA协助

二 .SQL,PL/SQL编写规范习惯

1>开发首先写好begin..exception..end ; 以免遗漏

2>代码做好缩进，方便查看

3>代码和table栏位做好注解，方便后续他人阅读

4>table和sp等对象定义好命名前缀后缀规则，C_ R_ I_ SP_ _T _I等

5> table和sp等对象定义好命名长度，尽可能简短

6>多次使用值如1/24*60可赋予变量重用，否则每次使用会被运算一次带来开销

7>避免事务执行过程中失败业务数据不一致 exception中可以写上rollback

8>业务逻辑优化，避免死锁，如SP1中第1条更新tab1,第2条更新tab2,SP2中就按照 第1条更新tab1,第2条更新tab2 。避免在其它SP中出现 第1条更新tab2,第2条更新tab1的顺序

9>SQL中能不使用distinct,group by,order by,having等操作就不要使用 ，避免带来负载

10>能使用union all操作就不要使用union，union去重操作也会来带负载

11> SQL 能写短，就不要太长，避免太多表的join，优化器可能会选择错误的执行计划

12>执行完事务记得及时commit,rollback

13>同一个程式 处理 过程中尽可能减少commit频率

14>SQLServer查询时尽量使用nolock，避免lock争用

15>CS架构，Clinet机器名尽可能15位长度之类，方便异常时捕获分析，Oracle11G及之前版本截取主机名长度有限，过长会不利于 捕获 Clinet 机器分析异常

16>在执行过久SQL时，查看执行计划并调整，也 可找资深开发人员或DBA协助分析原因

目前主要总结以上，后续工作学习中再有遇到相关问题也会不断补充进来。

文中如有不准确之处也请大家提出。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25583515/viewspace-2645784/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25583515/viewspace-2645784/