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行缩进一样,那么就先执行上面的。
字段 | 解释 |
---|---|
ID | 一个序号,但不是执行的先后顺序。执行的先后根据缩进来判断。 |
Operation | 当前操作的内容。 |
Rows | 当前操作的Cardinality,Oracle估计当前操作的返回结果集。 |
Cost(CPU) | Oracle 计算出来的一个数值(代价),用于说明SQL执行的代价。 |
Time | Oracle 估计当前操作的时间。 |
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%' )