sql索引优化思路是什么_数据库优化索引

sql索引优化思路是什么_数据库优化索引【开发】SQL优化思路(以oracle为例) powered by wanglifeng https://www.cnblogs.com/wanglifeng717 单表查询的优化思路 单表查询是最简

sql索引优化思路

【开发】SQL优化思路(以oracle为例)

powered by wanglifeng https://www.cnblogs.com/wanglifeng717

单表查询的优化思路

单表查询是最简单也是最重要的模块,它是多表等查询的基础。

避免对数据重复扫描

能一次扫描拿到的数据,不要重复扫描,查一次库能解决的问题,最好不要多次查。数据的读取非常消耗资源,减少对数据块的扫描。

例如:

1.SELECT COUNT (*)

FROM employees

WHERE salary < 2000;

2.SELECT COUNT (*)

FROM employees

WHERE salary BETWEEN 2000 AND 4000;

3.SELECT COUNT (*)

FROM employees

WHERE salary>4000;

统计任务经常用的语句。其实每个语句基本都把全表或索引扫了一遍,既然要全扫,就把握机会,能一次搞定的就一次搞定。

改写成

SELECT COUNT (CASE WHEN salary < 2000 THEN 1 ELSE null END) count1,COUNT (CASE WHEN salary BETWEEN 2001 AND 4000 THEN 1 ELSE null END) count2,COUNT (CASE WHEN salary > 4000 THEN 1 ELSE null END) count3 FROM employees;

严格来说,我们不推荐写过度复杂“炫技”的SQL,不要生搬硬套示例,只是为了让大家有个“节省持家”的意识。

例如如下经典写法,通过object_id字段上的索引全扫一遍,拿到了多种类别信息,不要分三次查询。

select max(object_id),min(object_id),sum(object_id),avg(object_id),count(object_id) from t where object_id is not null;

从大表中获取少量数据

从大选小,索引是你的不二选择。

例如:select t.name,t.status from t where t.pay_order_id = 101803309910017574;

索引利用B+树的原理可以快速找到某条数据,所以如果你想在大表中找到某条数据,索引是你必须要使用的技术。如上例所示,通过在pay_order_id上索引快速锁定这条数据的rowid,通过回表找到其他字段 t.name,t.status。这条语句就可以迅速执行,即使是千万级别表。原因还是全表扫描读的块非常多,而索引锁定数据快,读的块非常少,所以时间很快。

如果表记录数很少,使用索引效率反而低。例如,只有几十条记录,所有数据在一个

block 内。则全表扫描只需 1 个 block 的 io,而索引读由于回表等可能需要几个 block。

从大表中获取部分数据

例如:select t.name,t.status from t where t.pay_order_id < 101803309910017574;

上例所示,执行计划可能是全表扫描,也可能走索引。

主要决定因素之一是oracle的代价计算(cost),如果数据量比较大,走索引读,每条数据都伴随着一次回表操作。而全表扫描可以一次读多个块进内存。两种方式相比之下,哪条路径的代价低,oracle就会选择哪条。

所以,全表扫描的速度不一定慢。如果上述的SQL没有满足你的性能需求,且需求不能变,导致SQL已经不能修改时,我们可以考虑能否消除索引的回表操作。无论表多大,结果集多大,一旦所要的数据在索引块中都能找到,就不需要回表。因为索引全扫的块肯定比全表扫的块少的多的多,oracle肯定走索引全扫。

例如:

create index t_union_uuid_order_id on t(pay_order_id,uuid);

select uuid,pay_order_id from t where t.pay_order_id<101803300910017574;

如上例所示,所要字段数据在组合索引块中都能找到,所以没有回表操作。而索引块的数量远远小于全表数据的块数量,即使索引全扫,性能也非常好。

绝大多数情况下,这条select t.name,t.status from t where t.pay_order_id < 101809910017574语句我们可以控制下结果集,让索引即使回表,代价也远低于全表扫描。

组合索引不推荐三个及以上的字段建立组合索引,如果需要的字段非常多,不方便建立组合索引,建议控制结果集,少量快速多次,索引或两字段组合索引,多手段结合使用。具体使用要具体问题具体分析。宗旨就是控制结果集,使得走索引的代价低于全表扫描,然后利用索引快速,读块少的优点提高效率。这样分批几次拿数据,可能速度比一次全拿还快。事实是结果集控制的好,往往全表扫描的效率都能满足需求,更何况是索引扫描。

从大表中获取大量数据

这种场景首先要反问的就是这个需求是否存在问题,是否真的适合用关系型数据库?如果确实有这种需求。大表的数据量往往是惊人的,只能分页去拿。而ORACLE的三层select分页会越分越慢。

SELECT *

FROM (SELECT TA.*, ROWNUM ROW_NUM

FROM (select UUID, pay_order_id

from t

order by pay_order_id) TA

WHERE ROWNUM <= 100)

WHERE ROW_NUM > 0;

主要矛盾就是内层的WHERE ROWNUM <= 100,随着页数增加,结果集越来越大。2.order by的排序非常耗费性能,尤其大结果集的排序。3. 外层的WHERE ROW_NUM > 0随着页数越来越大,需要过滤的结果集也越来越大。

推荐方式:

SELECT t.*

FROM (select uuid, pay_order_id

from t

where t.pay_order_id is not null【*注】如果没有非空约束必须显示标明,否则索引失效

and t.pay_order_id >= “101809020001428452”

order by t.pay_order_id) t

WHERE ROWNUM <= 100;

pay_order_id 字段的需求是只增不减,为了不重不漏必须排序。索引是有序的,我们想用索引抵消掉排序,所以要查看执行计划,必须要走到索引。WHERE ROWNUM <= 100在oracle优化中会被推到内层语句中。所以实际结果集是t.pay_order_id >= “101809020001428452”之后的100条数据。所以结果集控制住了,索引代价肯定低于全表扫描,肯定走索引,索引又抵消了排序,同时 WHERE ROWNUM <= 100;每页都是100,rownum的性能损耗也控制住了。

这样额外的代价是,程序每次要记住最后一条pay_order_id,下次分页的时候将其带入。

推广到其他应用则可以选择表中的create_time字段代替pay_order_id。

多表查询的优化思路

多表连接把握住连接方式

多表查询和单表查询,唯一不同的就是把握住连接方式,只要连接方式把握住,多表查询其实就是多次单表查询。

三种连接方式:

nested loops join拿驱动表的结果集,去连接另外一个表,类似于两重嵌套循环(典型使用:小表驱动大表)。

hash join 拿驱动表的结果集去做hash表,PGA区,结果集大了,会到磁盘里。

merge join 无驱动表的概念,较少用到,对于连接键有序。

sql索引优化思路是什么_数据库优化索引
powered by wanglifeng https://www.cnblogs.com/wanglifeng717

从原理图可以看出,循环嵌套连接和hash连接中驱动表非常关键,准确说驱动表的结果集非常关键。循环嵌套连接的结果集大了,双层循环非常低效,哈希连接结果集大了可能导致排序开销变大,PGA区放不下等问题。

驱动表是oracle自动选择的,默认是加了过滤条件后,结果集小的那个表。如果查看执行计划,驱动表不如你所愿,你需要检查结果集是否相比另一个表结果集来说,明显是小结果集。或者自动收集信息不准确,需要更新。

如果是多表连接查询少量数据,推荐走循环嵌套连接。

create index n_index_order_id on n(order_id);

create index t_index_query_id on t(query_id);

select t.id ,t.name,n.address from n, t where t.pay_order_id=n.order_id and t.query_id=”261801163544557068″;

在驱动表的过滤条件上建立索引,快速锁定需要的少量数据行,在被驱动表的连接字段上建立索引,方便连接条件迅速匹配。这样的配合,就算两个表都是千万级别的表,只要索引不失效,速度都非常快。

如果是多表连接要查询出一部分数据,推荐走哈希连接

首先过滤条件过滤出小结果集,小结果集是个相对的概念,有时1000条算小结果集,有时10条也算大结果集,这里的小结果集一般在百条量级。

哈希连接的特点就是,无论驱动表的结果集在一定范围内如何变化,理论上,一次查询的时间近似等于扫一遍被驱动表的时间。性能表现相当高效和稳定。

控制驱动表的结果集,在被驱动表的连接字段上建立索引,忽略回表等细节,确认走到索引,这样一次查询的时间近似等于被驱动表的索引全扫时间,而我们知道,索引块相对全表块是非常少的,索引全扫非常高效。

走哪种连接方式,是oracle自动选择的,oracle选择的规则就是基于上述原理,所以我们决定不了走哪种执行计划,但是我们能让oracle”不得不走”哪种执行计划。

控制住结果集

控制结果集,不仅体现在单表查询的索引选择问题,还有体现在多表查询的连接方式和效率上。

除此之外还存在很多误区。结果集的概念并不是简单的数据量,而是一种意识,有控制结果集的意识,而不是教条主义的定义多少数量算大结果集。

结果集经典示例:

把in换成exists就完事了,性能就优化了,这是常犯的误区。

in是判断一个值是否在某个列中,而exists是判断一个值是否存在

Select * from tab where id in ( select id from tabel );

In 是先产生子查询结果集,然后主查询区结果集中寻找符合要求的字段列表,符合要求的输出。

Exists不返回列表值,而是true或者false,运行方式为,先运行主查询一次,在去子查询中查询与之对应的结果,如果子查询返回true则输出,反之不输出,在根据主查询的每一行去子查询中查询。

从原理可以看出,如果in的子查询结果集很大,外层的结果集也很大,相当于两个大结果集在连接运算,很耗性能。

Exists的运算比in优化了,但是就是搜索内层子查询的时候优化了,但是关键点是要把握住内外层的结果集,如果结果集很大,exists同样很慢,结果集控制的好,in操作也能符合要求。

总结:不管你多有把握,请一定要看下执行计划,一定要看下执行计划,一定要看下执行计划。。。。

本文来自思创斯聊编程,作者:wanglifeng,转载请注明原文链接:https://www.cnblogs.com/wanglifeng717/p/15847101.html

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/5528.html

(0)
上一篇 2023-05-05
下一篇 2023-05-05

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注