赞
踩
Sql优化对于开发人员来说,必不可少。但是我通过网上查阅资料发现,针对Oracle优化的文章相对较少,大多数都是针对Mysql的优化。由于我工作中以Oracle为主,因此这里结合索引数据结构、执行计划,给出一些sql优化的思路
索引的数据结构有:B+树、位图索引、散列(hash)、kd树等。其中B+树最为经典。
我们平常开发,定义的主键索引、普通索引、唯一索引等。默认都是B+树,因此本文重点讲解下B+树的数据结构。
B+树,官方资料简介:索引和按索引组织的表 (oracle.com)
B+trees 是平衡树的缩写,是最常见的数据库索引类型。B+tree 索引是一个有序的值列表,分为多个范围。通过将键与行或行范围相关联,B+树为广泛的查询提供了出色的检索性能,包括精确匹配和范围搜索。
这里以一个简单的案例推演B+树的形成过程
1、假设对如下的表进行B+树的构建,这里假设对主键索引C1列进行构建
create table BTree(
c1 number primary key ,
c2 varchar2(100),
c3 varchar2(100)
)
2、索引底层数据结构是存储在数据块上的,假设一条数据的详细信息如下
B+树的rowid:在oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的,rowid确定了每条记录是在oracle中的哪一个数据文件、块、行上。
存储在数据库中的每一行都有一个地址。Oracle 数据库使用一种ROWID
数据类型来存储数据库中每一行的地址(rowid)。
访问数据库表行的最快方法是通过其地址或rowid来唯一标识它。
3、这里假设一个数据块可以存放3条数据,那么一个数据块的数据案例如下
其中数据从小到大排列,采用单链表的形式连接
4、如果构建两层B+树,案例如下
叶子节点的块与块之间通过双链表连接
父亲节点的每条数据存储叶子节点的块号,和该块的最小值,并且也是从小到大排列
结合这个两层的B+树,当我们查找C1=5的数据时,可以很快的在块4中确定具体的数据在块02中
5、如果构建三层B+树,案例如下
同理,如果是三层的B+数据,父亲节点每条数据存储叶子节点的块号,和该块的最小值,并且也是从小到大排列
结合这个三层的B+树,当我们查找C1=15的数据时,可以根据块21知道该数据在块12的孩子中,再根据块12知道该数据在块05中
从这里也可以看出,如果通过索引查询数据,可以通过三个数据块即9条数据,就可以查询到结果,如果不通过索引,则需要查询9个数据块,27条数据查询。说明了通过索引查询效率是远高于全表查询。
6、结合上图的B+树,很容易理解为什么通过索引查询数据时非常快
真实场景中,一个数据块存储1000条数据是没有问题的,这时如果我们的B+树也是三层,那么它的总数据量就是 1000*1000*1000=10亿
,
即如果我们用索引查询数据时,最少可以通过3个数据块(3000条数据)查询,如果不用索引,则需要10亿条数据,查询效率差异是非常明显的。
了解索引的数据结构后,我们知道,索引的优点就是加快查询速度,那么索引的缺点有哪些?
耗费时间
,并且随着数据量的增加,所耗费的时间也会增加。磁盘空间
,除了数据表占数据空间之外,每一个索引还要占一定的物理空间存储在磁盘上
,如果有大量的索引,索引文件就可能比数据文件更快达到最大文件尺寸。降低更新表的速度
。当对表中的数据进行增加、删除和修改的时候,索引也要动态地维护,这样就降低了数据的维护速度。1. 字段的数值有唯一性的限制
索引本身可以起到约束的作用,比如唯一索引、主键索引都可以起到唯一性约束的,因此在我们的数据表中,如果某个字段是唯一的
,就可以直接创建唯一性索引
,或者主键索引
。这样可以更快速地通过该索引来确定某条记录。
业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引。(来源:Alibaba)
说明:不要以为唯一索引影响了insert速度,这个速度损耗可以忽略,但提高查找速度是明显的。
2. 频繁作为 WHERE 查询条件的字段
某个字段在SELECT语句的 WHERE 条件中经常被使用到,那么就需要给这个字段创建索引了。尤其是在数据量大的情况下,创建普通索引就可以大幅提升数据查询的效率。
3. 经常 GROUP BY 和 ORDER BY 的列
索引就是让数据按照某种顺序进行存储或检索,因此当我们使用 GROUP BY 对数据进行分组查询,或者使用 ORDER BY 对数据进行排序的时候,就需要对分组或者排序的字段进行索引
。如果待排序的列有多个,那么可以在这些列上建立组合索引
。
4. UPDATE、DELETE 的 WHERE 条件列
对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作,如果对 WHERE 字段创建了索引,就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录,然后再对它进行更新或删除。如果进行更新的时候,更新的字段是非索引字段,提升的效率会更明显,这是因为非索引字段更新不需要对索引进行维护。
5.DISTINCT 字段需要创建索引
有时候我们需要对某个字段进行去重,使用 DISTINCT,那么对这个字段创建索引,也会提升查询效率。
6. 多表 JOIN 连接操作时,创建索引注意事项
首先,连接表的数量尽量不要超过 3 张
,因为每增加一张表就相当于增加了一次嵌套的循环,数量级增长会非常快,严重影响查询的效率。
其次,对 WHERE 条件创建索引
,因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下,没有 WHERE 条件过滤是非常可怕的。
最后,对用于连接的字段创建索引
,并且该字段在多张表中的类型必须一致
。
7. 使用列的类型小的创建索引
我们这里所说的类型大小
指的就是该类型表示的数据范围的大小。
放下更多的记录
,从而减少磁盘I/O
带来的性能损耗,也就意味着可以把更多的数据块缓存在内存中,从而加快读写效率。8. 使用最频繁的列放到联合索引的左侧
9. 在多个字段都要创建索引的情况下,联合索引优于单值索引
1. 在where中使用不到的字段,不要设置索引
2. 数据量小的表最好不要使用索引
3. 有大量重复数据的列上不要建立索引
在条件表达式中经常用到的不同值较多的列上建立索引,但字段中如果有大量重复数据,也不用创建索引。比如在学生表的“性别
”字段上只有“男”与“女”两个不同值,因此无须建立索引。如果建立索引,不但不会提高查询效率,反而会严重降低数据更新速度
。
举例1:要在100万行数据中查找其中的50万行(比如性别为男的数据),一旦创建了索引,你需要先访问50万次索引,然后再访问50万次数据表,这样加起来的开销比不使用索引可能还要大。 举例2:假设有一个学生表,学生总数为100万人,男性只有10个人,也就是占总人口的10万分之1。这样加索引才有意义
结论:当数据重复度大,比如
高于10%
,也不需要对这个字段使用索引
4. 避免对经常更新的表创建过多的索引
第一层含义:频繁更新的字段不一定要创建索引。因为更新数据的时候,也需要更新索引,如果索引太多,在更新索引的时候也会造成负担,从而影响效率。
第二层含义:避免对经常更新的表创建过多的索引,并且索引中的列尽可能少。此时,虽然提高了查询速度,同时却会降低更新表的速度。|
5. 不建议用无序的值作为索引
例如身份证、UUID(在索引比较时需要转为ASCII,并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。
6. 删除不再使用或者很少使用的索引
7. 不要定义冗余或重复的索引
冗余:一个字段定义了多种索引,有联合索引,又定义普通索引
执行计划描述数据库引擎执行sql语句时实施的操作,那么如何获取执行计划?
display函数返回计划表中存储的执行计划,返回值是dbms_xplan_type_table集合的实例。此函数有以下输入参数。
--针对 sql 生成对应的执行计划,并存入 计划表
explain plan set statement_id='nTOhnPUxPgL17qc1g6BHZA==' for select * from STUDENT;
--从计划取出执行计划
select * from table(DBMS_XPLAN.DISPLAY('PLAN_TABLE','nTOhnPUxPgL17qc1g6BHZA=='));
plan_table
。如果指定null,则使用默认值explain plan
语句的时候,作为可选参数给出。默认为null。如果使用了默认值,则显示最近一次插入执行计划表的执行计划。display_cursor函数返回库缓存中存储的执行计划。所以使用该方法,必须实际执行sql才可以。
--设置当前会话数据库和操作系统统计信息的收集级别
ALTER SESSION SET STATISTICS_LEVEL=all;--代表 系统收集所有的统计信息 basic 关闭了所有性能数据的收集 typical 部分收集 (默认为typical)
select * from V$SQL where sql_text LIKE '%select * from STUDENT where AGE = 24%';
select * from STUDENT where AGE = 24;
select * from table(dbms_xplan.display_cursor(sql_id=>'3k8xft0cbvmqf', format=>'iostats'));
关于 PLAN_TABLE 的 OPERATION 和 OPTION 列详细信息,参考
根据执行计划,我们可以看出一个复杂sql的真正执行顺序是怎样的,那么面对执行计划,我们的阅读顺序是什么?
口诀:最右最上先执行,
并且执行计划可以看做一颗树,用来描述sql的执行顺序
例如:
CREATE TABLE t (
id NUMBER,
val NUMBER,
pad VARCHAR2(1000),
CONSTRAINT t_pk PRIMARY KEY (id)
);
CREATE INDEX i ON t (val);
UPDATE t
SET val = (SELECT /*+ index(t) */ max(val) FROM t WHERE id BETWEEN 6 AND 19),
pad = (SELECT pad FROM t WHERE id = 6)
WHERE id IN (SELECT id FROM t WHERE id BETWEEN 6 AND 19);
了解父-子关系对于理解执行计划执行各个操作的顺序十分关键。实际上,为了完成它们的任务,父操作需要由它们的子操作提供的数据。因此,虽然执行是从树的根部开始的,第一个被完全执行的操作是没有子操作的那个。
例如:下面的场景,可以明显看到操作1返回的行数最多,但是我们优化的思路是在操作2和操作3上,因为操作1返回的行数是由操作2和操作3叠加得来的。
发送到数据库的每个SQL语句在由SQL引擎处理之前都要转化成执行计划。事实上,应用程序只是通过SQL语句指定了什么样的数据必须处理,而未指定如何处理。查询优化器的目标不仅是提供执行计划来描述如何处理数据,同时最重要的是,交付高效的执行计划。如果做不到这一点可能会导致槽糕的性能。
在Oracle数据库中,提供两个主要的查询优化器:基于规则的优化器(RBO)和基于成本的优化器(CBO)。从Oracle Database 10g开始,已经不再支持基于规则的优化器,所以谈到查询优化器这个术语时,始终指的是基于成本的优化器。
说白了就是一个sql的执行计划,是由查询优化器提供的,但是执行计划的生成并没有固定的规律,查询优化器器需要根据最优访问路径、连接顺序、是否需要并行等各方面因素,来提供执行计划。即我们写的sql不是说用到索引字段,就一定会走索引,由于sql的书写不规范,会出现索引失效的场景。
下面列出一些常见的索引失效的案例,索引失效的规则并非百分之百,具体情况还需要具体分析
准备数据
create table class ( id number(10) primary key, className varchar2(30), address varchar2(40), monitor number ); create table student ( id number(10) primary key, stuno number(20), name varchar2(30), age number(4), classId number(20), year date ); -- 随机产生字符串 select dbms_random.string('u',20) from dual; -- 随机数 SELECT ABS(MOD(DBMS_RANDOM.RANDOM,30)) FROM DUAL; --随机时间 视图 create or replace view getRamDate as SELECT to_date(TRUNC(DBMS_RANDOM.VALUE( to_number(to_char(to_date('20000101','yyyymmdd'),'J')), to_number(to_char(to_date('20200501','yyyymmdd')+1,'J')))),'J')+ DBMS_RANDOM.VALUE(1,3600)/3600 prize_time FROM dual; --往 student 表插入1W数据 DECLARE TEMP number(20) := 10000; BEGIN for i in 1..TEMP loop INSERT INTO STUDENT (ID, STUNO, NAME, AGE, CLASSID,year) VALUES (i, 10000 + i, (select dbms_random.string('u', 10) from dual), (SELECT ABS(MOD(DBMS_RANDOM.RANDOM,30)) FROM DUAL), (SELECT ABS(MOD(DBMS_RANDOM.RANDOM,50000)) FROM DUAL),(select prize_time from GETRAMDATE)); end loop; END; --往 class 表插入50 W数据 DECLARE TEMP number(20) := 500000; BEGIN for i in 1..TEMP loop INSERT INTO CLASS (ID, CLASSNAME, ADDRESS, MONITOR) VALUES (i, (select dbms_random.string('u', 8) from dual), (select dbms_random.string('u', 8) from dual),(SELECT ABS(MOD(DBMS_RANDOM.RANDOM,1000)) FROM DUAL) ); end loop; END;
--查询索引
select * from all_indexes WHERE table_name in ('STUDENT','CLASS');
--普通的查询,没有用到索引字段
select * from student;
select * from student where STUNO = 10002;
建立联合索引时会遵守最佳左前缀匹配原则,即最左优先,在检索数据时从联合索引
的最左边开始匹配。
CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);
--虽然我们创建的联合索引中有classId,但根据最佳左前缀法则,下面的sql没有最左字段age。因此,这个联合索引用不上
SELECT * FROM student WHERE student.classid=1 AND student.name = 'abcd';
--或者我们只用了第一个和第三个字段,那么最终索引也只能用到第一个age,而name不走索引
SELECT * FROM student WHERE student.age=30 AND student.name = 'abcd';
--删除本例索引,为后面测试做准备
drop index idx_age_classid_name;
结论:可以为多个字段创建索引。对于多列索引,**过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。**如果查询条件中没有使用这些字段中第1个字段时,多列(或联合)索引不会被使用。
CREATE INDEX idx_sno ON student(stuno);
SELECT id, stuno, NAME FROM student WHERE stuno+1 = 900001;--做了+1运算,用不上索引
SELECT id, stuno, NAME FROM student WHERE stuno = 900000;
CREATE INDEX idx_year ON student(YEAR);
--不走索引
select * from STUDENT where YEAR+1 = sysdate;
select * from STUDENT where to_char(YEAR,'yyyy-MM-dd') = '2020-01-01';
--走索引
select * from STUDENT where YEAR = to_date('2020-01-01','yyyy-MM-dd');--走索引,原因是
select * from STUDENT where YEAR = sysdate+1;
如果一定要用函数查询,则创建函数索引
drop index idx_year;
CREATE INDEX idx_year_tochar ON student(to_char(YEAR,'yyyy-MM-dd'));
--这样就又走索引了,
select * from STUDENT where to_char(YEAR,'yyyy-MM-dd') = '2020-01-01';
--但是注意,使用函数时需要和创建索引的一致,否则不走索引
select * from STUDENT where to_char(YEAR,'YYYY-MM-dd') = '2020-01-01';
--删除本例索引,为后面测试做准备
drop index idx_sno ;
drop index idx_year_tochar ;
很好理解,不等于,只能全表扫描了。
CREATE INDEX idx_sno ON student(stuno);
select * from STUDENT where STUNO != '123'
--删除本例索引,为后面测试做准备
drop index idx_sno ;
create index idx_name on STUDENT(NAME);
SELECT * FROM student WHERE NAME IS NULL;
SELECT * FROM student WHERE NAME IS not NULL;
--删除本例索引
drop index idx_name;
注:MySql在使用is null 也会走索引
结论:最好在设计数据表的时候就将
字段设置为 NOT NULL 约束
,比如你可以将INT类型的字段,默认值设置为0。将字符类型的默认值设置为空字符串(‘’)
create index idx_name on STUDENT(NAME);
SELECT * FROM student WHERE NAME LIKE 'ab%';--使用索引
SELECT * FROM student WHERE NAME LIKE '%ab%';--不使用索引
--删除本例索引
drop index idx_name;
拓展:Alibaba《Java开发手册》
【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_cid ON student(classid);
--如果只建立一个字段的索引,无法走索引,必须建立两个字段
SELECT * FROM student WHERE age = 10 OR classid = 100;
--删除本例索引
drop index idx_age;
drop index idx_cid;
总之,书写SQL 语句时,尽量避免造成索引失效的情况。
结论:
- 尽量在连接字段建立索引
- 多表关联最好不要超过3张表
- 减少驱动表需要查询的自动个数(不要用select *)
多表联接分类:Joins (oracle.com)
由嵌套循环联接处理的两组数据称作外循环(也称作驱动行源)和内循环。外循环是左输人,而内循环则是右输入。
嵌套循环联接拥有的具体特征如下所示
select * from CLASS cc left join STUDENT ss on cc.ID = ss.CLASSID
where ss.ID in (1) ;
类比,具体哪一张表为外层循环(驱动表),由oracle自动选择,取决于表的大小,以及联接字段的索引情况
for( id : STUDENT ){
for(id :class){
.....
}
}
缓冲区缓存预取
当索引或表块不在缓冲区缓存中并且需要处理连接时,需要物理 I/O。数据库可以批处理多个物理 I/O 请求,并使用矢量 I/O(数组)而不是一次处理一个请求。数据库将 rowid 数组发送到操作系统,操作系统执行读取。
数据库使用哈希联接来联接较大的数据集。
优化程序使用两个数据集中较小的一个在内存中的联接键上构建哈希表,使用确定性哈希函数指定哈希表中存储每一行的位置。然后,数据库扫描较大的数据集,探测哈希表以查找满足连接条件的行。
Hash Join是做大数据集连接
时的常用方式,优化器使用两个表中较小(相对较小)的表利用Join Key在内存中建立散列值
,然后扫描较大的表并探测散列值,找出与Hash表匹配的行。
select * from CLASS cc left join STUDENT ss on cc.ID = ss.CLASSID;
排序合并连接是嵌套循环连接的变种。
如果两个数据集还没有排序,那么数据库会先对它们进行排序,这就是所谓的sort join操作。
对于数据集里的每一行,数据库会从上一次匹配到数据的位置开始探查第二个数据集,这一步就是Merge join操作。
select * from CLASS cc left join STUDENT ss on cc.ID > ss.CLASSID;
优化器什么时候会考虑使用排序合并连接?
合并联接的使用并不是很频繁。原因是,在大多数的情况下,无论是嵌套循环联接还是散列联接,都执行得比合并联接要更好。
简单说就是,索引列+主键
包含SELECT 到 FROM之间查询的列
。
CREATE INDEX idx_cid_n ON student(NAME,CLASSID);
SELECT * FROM student WHERE NAME ='123';
--索引快速扫描
SELECT CLASSID,NAME FROM student WHERE NAME !='123';
SELECT CLASSID,NAME FROM student WHERE NAME like '%123%';
首先要了解in是否走索引也分情况,如果in的取值范围特别大时,也会导致索引失效。
select * from CLASS where id in (1,2,3,4,5,998);
select * from CLASS where id in (select CLASSID from STUDENT);
参考别人的说法
推荐使用not exists
并且如果子查询有null字段,两者的查询结果不同,参考下面的例子
select * from t1;
select * from t2;
create table t1(c1 int,c2 int);
create table t2(c1 int,c2 int);
insert into t1 values(1,2);
insert into t1 values(1,3);
insert into t2 values(1,2);
insert into t2 values(1,null);
select * from t1 where c2 not in(select c2 from t2);-->执行结果:无
select * from t1 where not exists(select 1 from t2 where t2.c2 = t1.c2)
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。