Oracle学习(八) --- SQL优化

1、前置工具：执行计划 Explain Plan

1.1、概念

一条查询语句在 oracle 中的执行过程或访问路径的描述。即就是对一个查询任务，做出一份怎样去完成任务的详细方案。

• 执行计划：用于记录sql执行每一个细节。
• 执行计划目的：通过分析SQL执行每一个细节，从而确定优化方案。

1.2、Oracle执行计划

• 方式1：使用SQL语句进行查询，结果更加详细。

  --运行“执行计划”： explain plan for SQL语句;
  explain plan for select * from dual;
  --查询“执行计划”结果（固定语句）
  select * from table(dbms_xplan.display());
  

• 方式2：使用PL/SQL Dev 工具提供"执行计划窗口"进行查询，内容相对而言少一些。

1.3、执行原则

• 执行计划原则：由上而下、从右向左。
  • 由上至下：在执行计划中一般含有多个节点，相同级别(或并列)的节点，靠上的优先执行，靠下的后执行
  • 从右向左：在某个节点下还存在多个子节点，先从最靠右的子节点开始执行
• 一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的。

2、准备数据

2.1、前置技术演示

• 前置技术：
  • 随机数字、随机字符串
  • 重复执行次数
  • 系统时间处理
  • 根据查询结果创建表

-- 2 准备数据

--- 2.1 获得随机数据  dbms_random
-- dbms_random.value(a,b) 生产[a,b) 之间 一个随机数
-- dbms_random.string(符号,数量) 根据"符号"生产指定“长度”随机字符串
---- 符号：u 大写字母、l 小写字母、x 大写字母和数字、a 混合型(大小写) 、p 可打印
-- 1.1) 获得一个 1-10 随机浮点数
select dbms_random.value(1,10) from dual;

-- 1.2) 获得一个 1-10 随机整数
select round( dbms_random.value(1,10) ) from dual;
select trunc( dbms_random.value(1,10) ) from dual;

-- 2) 获得长度为6的随机字符串
select dbms_random.string("u",6) from dual;
select dbms_random.string("l",6) from dual;
select dbms_random.string("x",20) from dual;
select dbms_random.string("a",6) from dual;
select dbms_random.string("p",6) from dual;


-- 2.2 控制查询条件（重复执行次数）
select dbms_random.string("a",6) from dual 
 connect by level <= 10;

-- 2.3 系统时间
-- 1) 当前系统时间
select to_char(sysdate , "yyyy-mm-dd hh24:mi:ss") from dual;
select to_char(sysdate + 1000000/24/3600 , "yyyy-mm-dd hh24:mi:ss") from dual;

-- 2.4 根据查询结果创建表
-- 语法：  create table 表名 as  查询语句;
--- 1) 查询
select trunc( dbms_random.value(18,120) ) age , dbms_random.string("x",4) name from dual;
--- 2) 创建
create table t_person
as
select trunc( dbms_random.value(18,120) ) age , dbms_random.string("x",4) name from dual;

2.2、准备1千万条数据

• 约耗时3-10分钟

--- 准备1千万条数据
create table t_user
as
select 
  rownum as id,
  to_char(sysdate + rownum / 24 / 3600 , "yyyy-mm-dd hh24:mi:ss") as birthday,
  trunc( dbms_random.value(18,140) ) as age,
  dbms_random.string("x",20) as username
from dual
connect by level <= 10000000;

• 生成表之后，插入一百万条测试数据

insert into t_user(ID, birthday,age,username)
	select 1000000+rownum as id,
        to_char(sysdate + rownum/24/3600, "yyyy-mm-dd hh24:mi:ss") as birthday,
        trunc(dbms_random.value(0, 100)) as age,
        dbms_random.string("x", 20) username
    from dual
  		connect by level <= 1000000;

3、优化

3.1、使用索引(可以大大提高检索速度)

--优化1：使用索引
-- 未使用搜索 2.480
select * from t_user where id = 1;
-- 设置主键，自带唯一索引
alter table t_user add constraint user_pk primary key (id);
-- 创建索引后 0.046
select * from t_user where id = 1;

3.2、避免在WHERE字句中使用NULL

• 使用null，讲放弃索引，进行全表扫描

--优化2：避免在where中使用null
-- 1) 给age添加普通索引
create index user_age_index on t_user(age);
-- 2) 查询age = 18所有信息  0.051
select * from t_user where age = 18;
-- 3) 更新id=1 age为null
update t_user set age = null where id = 1;
commit;
-- 4) 查询null数据
-- 查看age是否为null，0.058
select * from t_user where id = 1;
-- 根据null查询，1.396
select * from t_user where age is null;

3.3、尽量不使用不等于(<>或 !=)

• 使用不等于，将进行全表扫描

3.4、应尽量避免在 where子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描

• 避免部分条件放弃索引，进行全表扫描

-- 优化4 ：
--- 查询所有
select * from t_user;
-- 0.031
select * from t_user where id = 15;
-- 1.282
select * from t_user where username = "NNH250Y9LN7JHA13G1T3";
-- or 操作 1.846
select * from t_user where id = 15 or username = "NNH250Y9LN7JHA13G1T3";

--- 优化方案：使用 uNIOn all 替换 or  -- 1.361
select * from t_user where id = 15
union all
select * from t_user where username = "NNH250Y9LN7JHA13G1T3";

3.5、避免使用 select *

-- 优化5：避免使用 select *
--- id 查询
select * from t_user where id = 6000000;
--- 通过username查询 -- 1.416
select * from t_user where username = "C2Q0Q9INJDTDZ9TLN8JG";
--- 字段替换*  -- 1.309
select id,username,age,birthday from t_user where username = "C2Q0Q9INJDTDZ9TLN8JG";

3.6、尽量不用 like 语句，如果必须使用，优先使用"xx%"

-- 优化6：尽量不用like语句，如果必须使用，优先使用"xx%"
----  "%xx" 和 "%xx%" 不能使用索引
-- 1) 给 t_user username 添加索引
create index user_username_index on t_user(username);
-- 2) 使用 %xx% 进行模糊查询 -- 3.825
select * from t_user where username like "%C2Q0Q9IN%";
-- 3) 使用 xx% 进行模糊查询 -- 0.053
select * from t_user where username like "C2Q0Q9IN%";

3.7、避免在 where 子句中对字段进行表达式

-- 优化7：避免在 where 子句中对字段进行表达式
-- 所有 60 岁人员
select * from t_user where age = 60;
-- 对 age 进行计算
select * from t_user where age / 2 = 30;

3.8、避免在 where 子句使用函数

-- 优化8：避免where子句使用函数
--- 使用substr(开始位置，长度)
select substr(username,2,3) from t_user where id = 6000000;
select length("C2Q0Q9IN") from dual;
--- 判断前缀 -- 2.959
select * from t_user where substr(username, 1, 8) = "C2Q0Q9IN";
select * from t_user where substr(username, 1, length("C2Q0Q9IN")) = "C2Q0Q9IN";
-- 使用like 替换函数
select * from t_user where username like "C2Q0Q9IN%";

3.9、复合索引中，必须使用索引中第一个字段，且尽量字段顺序与索引顺序一致

-- 优化10：复合索引中，必须使用索引中第一个字段，且尽量字段顺序与索引顺序一致
-- 1）创建表（复合主键）
create table t_user2(
  firstname varchar2(20),
  secondname varchar2(20),
  age int,
  constraint user2_fk primary key (firstname,secondname)
);
-- 2）初始化 100w条数据
-- 语法： insert into 表名 select语句;
-- 2.1) 查询结果
select dbms_random.string("x",20) as firstname,
    dbms_random.string("x",20) as secondname,
    trunc(dbms_random.value(0,100) ) as age
from dual
connect by level <= 10;

-- 2.2) insert 100w
insert into t_user2(firstname,secondname,age)
select dbms_random.string("x",20) as firstname,
    dbms_random.string("x",20) as secondname,
    trunc(dbms_random.value(0,100) ) as age
from dual
connect by level <= 1000000;
-- 提交事务
commit;

-- 3）测试：查询第一字段、查询第二字段、查询第一 + 二字段
--- 3.1) 查询id 700000
select * from (
  select rownum r ,t_user2.* from t_user2
) t where t.r = 700000;
--- 3.2) 查询firstname -- 0.023 （速度快一些）
select * from t_user2 where firstname = "PR9AVGGXEJL4SJ8OCZWF";
--- 3.3) 查询secondname  -- 0.068 （相对而言慢一些）
select * from t_user2 where secondname = "93A0SPRCXQBZ45V1S59A";
--- 3.4) 查询firstname + secondname （字段顺序无关，建议顺序一致）
select * from t_user2 where firstname = "PR9AVGGXEJL4SJ8OCZWF" and secondname = "93A0SPRCXQBZ45V1S59A";
select * from t_user2 where secondname = "93A0SPRCXQBZ45V1S59A" and firstname = "PR9AVGGXEJL4SJ8OCZWF";

3.10、构建空表

-- 优化11：创建空表结构
-- 1) select into 不能生成空表结构
-- select 字段1,字段2,... into 新表名 from 查询表 where 1=0;
-- 1.1) select into 在Oracle无法创建表结构
-- select id,birthday,age,username into t_user3 from t_user where id < 10;

-- 2) 创建表结构，但没有数据
create table t_user3
as
select 
  dbms_random.string("x",20) as firstname, dbms_random.string("x",20) as secondname
from dual where 1 = 0;

3.11、根据实际情况创建索引，而不是越多越好。

• 添加索引提供查询速度，同时降低了insert 和 update效率。

3.12、使用 exists 和 in 对比

• 两个表中数据一致的情况下，没有差异
• 如果两个表中一个数据较小A，一个是数据较多B，则子查询B用exists，子查询A用in

--如果部门名称中含有字母A，则查询所有员工信息(使用exists)

-- 1) 查询 部门名称中含有字母A
select * from dept where dname like "%A%";

-- 2) 使用exists
select * from emp where exists ( select * from dept where dname like "%A%" and dept.deptno = emp.deptno )

-- 3) in
select * from emp where emp.deptno in ( select dept.deptno from dept where dname like "%A%" )