SQL书写规范有哪些

本篇文章给大家分享的是有关SQL书写规范有哪些，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

一.如何使用高效的索引

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协助分析原因

以上就是SQL书写规范有哪些，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。