酷酷娱乐网

站内广告

SQL书写规范(通用)

时间:2020-09-24 03:00:03   作者:酷酷娱乐网   来源:www.kukuyl.com  
内容摘要:


SQL语句是访问DB中数据的主要途径和方式,所以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可赋予变量重用,否则每次使用会被运算一次带来开销

本文章url:https://www.kukuyl.com/new/30000893.html

相关文章

本栏最新更新

本栏推荐

阅读排行

CopyRight 2018 - 2020 http://www.kukuyl.com 酷酷娱乐网 All Rights Reserved .