首页 > 资讯 > 数据库 >sql索引优化思路

744

分享到

sql索引优化思路

sql索引优化思路 2020-02-17 04:02:23 744人浏览无得

摘要

【开发】sql优化思路(以oracle为例) powered by wanglifeng https://www.cnblogs.com/wanglifeng717 单表查询的优化思路单表查询是最简单也是最重要的模块，它是多表等查询的

sql索引优化思路

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

单表查询的优化思路

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

避免对数据重复扫描

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

例如：

SELECT COUNT (*)

FROM employees

WHERE salary < 2000;

SELECT COUNT (*)

FROM employees

WHERE salary BETWEEN 2000 AND 4000;

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 无驱动表的概念，较少用到，对于连接键有序。

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

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