OracleSQL基础要点（三）

万望

16.2 SQL调优
决定DBMS性能的因素主要有硬件和软件两个部分，如果硬件条件很好，但是书写的SQL质量非常差，则系统的性能不会有明显的提升；相反，即使是略差的硬件条件，但是SQL语句进行了充分的优化，系统的性能提升将是惊人的。

16.2.1 SQL调优的基本原则
20%的代码消耗了80%的系统资源，对所有的SQL进行优化是不切实际的，因此可以使用DBMS中的一些工具先分析出最耗费系统资源的SQL，然后尝试对它们进行优化。

16.2.2 索引
根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种类型：聚簇索引和非聚簇索引。
- 聚簇索引：数据表的物理顺序与索引顺序相同，比如汉语词典的拼音目录；
- 非聚簇索引：数据表的物理顺序与索引顺序不相同，比如汉语词典的偏旁部首目录。
最佳实践是在表中经常搜索的列或者按照顺序访问的列上创建聚簇索引，而且一张表只能创建一个聚簇索引，因为表中记录的物理顺序只有一个。而非聚簇索引可以创建多个。
索引会占据一定的存储空间，会造成数据的插入，更新以及删除操作速度变慢，所以应该只创建必要的索引。
索引会造成碎片的问题，即当删除某条记录的时候，它对应的索引并没有被删除，久而久之，这些碎片会占据大量的存储空间，并在一定程度上降低系统速度。
如果发现索引中存在过多的碎片就要进行“碎片整理”，主流数据库都提供“重建索引”的功能。

16.2.3 表扫描和索引查找
全表扫描和索引查找，数据量少的情况下，全表扫描很有用，使用索引不见得就比全表扫描好。
DBMS中都有查询优化器，由他生成执行计划，决定使用全表扫描还是索引查找。

16.2.4 常用的优化手法
- 创建必要的索引，在经常需要进行检索的字段上创建索引。
- 参数化SQL不仅能够避免SQL注入漏洞攻击，还能让DBMS对该SQL执行预编译，下次再使用该SQL就可以直接使用预编译的结果，加快速度。
- DBMS一般采用自下而上，从右到左的顺序解析where子句，所以，如果Where中如果存在多个过滤条件，并且其中有表连接的过滤条件，请把该条件放在前面，这样，DBMS就会先过滤其它的条件，最后再做连接操作，大大减少了表连接的数量。
- 避免使用select *，即使需要检索所有的列，也不要使用，因为DBMS在解析过程中，会将*依次解析转换成所有的列名，降低查询速度。
- 尽量将多条SQL合并成一条SQL。
- 可以的话，用where替换having，where是在结果之前进行过滤，having是在结果之后过滤。
- 使用表的别名。
- 用exists代替in，因为in会对子查询结果进行排序和合并。
- 使用表连接替换exists。
- 避免在索引列上使用计算，在where子句中，如果索引列是计算或者函数的一部分，DBMS优化器将不会使用索引而使用全表扫描。比如select * from t_employee where salary*12 > 25000优化成select * from t_employee where salary > 25000/12将会从全表扫描转换为走索引。
- 在确定检索结果中不会用重复记录的情况下，使用union all替换union。
- 避免隐式类型转换造成的全表扫描，比如select * from t_person where level = 10，其中level列为字符串类型，且有索引，因为与数字10进行了比较，DBMS进行了隐式类型转换，所以相当于执行了select * from t_person where to_int(level) = 10，可以看出，因为对索引列使用了计算（函数），造成原来走索引的SQL现在变成了全表扫描。应该这样改select * from t_person where level = '10'。
- 如果DBMS优化器认为检索范围过宽，那么它将放弃索引而使用全表扫描。比如，使用is not null或者!=，可能造成优化器假设匹配的记录数太多。

16.3 事务

16.3.1 事务的4个基本特性：ACID。

16.3.2 隔离级别
- 脏读
- 不可重复读
- 幻读
- 使用READ_UNCOMMITED级别，会存在导致脏读、不可重复读、幻读
- 使用READ_COMMITED级别可以解决脏读问题，但会有不可重复读、幻读
- 使用REPEATABLE_READ级别可以解决脏读和不可重复读，但会有幻读
- 使用SERIALIZABLE级别可以解决以上三种问题

16.4 自动增长字段
ORACLE中使用自动增长字段需要首先定义一个SEQUENCE：

1. <span style="background-color: white;">create sequence squence_id increment by 1 start with 1;</span>

复制代码

然后如下使用：

1. <span style="background-color: white;">insert into t_person(FId,FName,FAge)
   
2. values(squence_id.nextval,'mason',19);</span>

复制代码

缺点是每次向表中插入记录的时候都要显式得到，这里可以使用一个触发器：

1. <span style="background-color: white;">create or replace trigger trigger_personIdAutoIncre
   
2. before insert on t_person
   
3. for each row
   
4. declare
   
5. begin
   
6. select squence_id.nextval into :new.FId from dual;
   
7. end trigger_personIdAutoIncre;</span>

复制代码

16.5 业务主键和逻辑主键
- 建议尽量不要使用业务字段作为主键，而是使用没有业务意义的逻辑主键。
- 逻辑主键的生成主要分为自增类型和UUID。自增类型的主键在表合并的时候可能会发生错误，UUID可以确保逻辑主键的唯一性。

16.6 NULL
NULL不是表示没有值，而是表示值未知。
- 比较运算符并不能检索出NULL，只能使用is null。
- 计算字段中如果有NULL，那么计算结果永远为NULL。（包括与数值，字符串，函数等的计算）
- 聚合函数中如果出现NULL，那么会被忽略。

16.7 开窗函数
与聚合函数一样，开窗函数也是对行集组进行聚合计算，但区别是，聚合函数每组只返回一个值，开窗函数可以为每组返回多个值，开窗函数所执行聚合计算的行集组是窗口。开窗函数也叫做分析函数。

TIPS：所有不包含在聚合函数中的列必须声明在GROUP BY字句中，如下写法是错误的：

1. <span style="background-color: white;">select FCITY,FAGE,COUNT(*) from t_person where FSALARY < 5000
   
2. </span>

复制代码

应该改为：

1. <span style="background-color: white;">select FCITY,FAGE,COUNT(*) from t_person where FSALARY < 5000 group by FCITY,FAGE</span>

复制代码

这样的话就是按照FCITY和FAGE进行分组，然后在每个小的分组内进行select操作。

16.7.1 开窗函数的使用
开窗函数是在聚合函数后面增加一个OVER关键字，其使用格式为：
函数名（列） OVER（选项）
其中OVER关键字表示把函数当成开窗函数而不是聚合函数。SQL标准允许将所有的聚合函数用作开窗函数，仅仅使用OVER来区分。OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围，如果选项为空，则表示对结果集中所有行进行聚合运算。

16.7.2 PARTITION BY
PARTITION BY是用来定义行的分区来供进行聚合计算，与GROUP BY不同的是，PARTITION BY创建的分区是独立于结果集的，它创建的分区仅仅供聚合计算使用，而且不同的开窗函数中的PARTITION BY所创建的分区互不影响。

16.7.3 ORDER BY
ORDER BY用来指定排序规则，对结果集按照指定的规则进行排序，并且在一个指定范围内进行聚合运算。
ORDER BY 字段名 RANGE|ROWS BETWEEN 上界 AND 下界
其中RANGE表示按照值的范围进行范围定义，ROWS表示按照行的范围进行范围的定义。
上界和下界可取值有：
- CURRENT ROW ：当前行
- N PRECEDING : 前n行
- UNBOUNDED PRECEDING ： 一直到第一条记录
- N FOLLOWING ： 后n行
- UNBOUNDED FOLLOWING ：一直到最后一条记录

TIPS:”ORDER BY FNAME RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”可以省略为”ORDER BY FNAME”。

16.7.4 高级开窗函数
- 排名，RANK(),DENSE_RANK(),ROW_NUMBER()
- 百分比，RATIO_TO_REPORT()
- NTILE(n)，将结果集平均分割成n个部分，返回当前行所属于的部分号
- LEAD()，LAG()，访问当前行之前或者之后某个指定列
- FIRST_VALUE(),LAST_VALUE()
……

16.8 WITH子句与子查询
为了避免多次使用类似的子查询，于是就有了WITH子句，用法：

with 别名(l1,l2,l3) as (子查询)
select …

其中别名后面的参数可以省略，不省略的话代表子查询结果中的列，需要一一对应。

张兴康

张兴康

陈晓龙

fteair