oracle统计sql优化与原理详解

r囧r小猫 2022-05-21 00:04 181阅读 0赞

简介：oracle统计类sql的优化，此篇只讲count sum avg max min orderby，主要还是要知道索引存储列值和rowid的特性，我的博客里也有常用索引（树状索引）的详解。

一.count(\*)的优化

直接上例子（假设test表有10w数据，列有10列）：

1.select count(\*) from test

count(\*)我们都知道是统计有多少数量，通常我们建表都会有一个主键id，我们这条语句通过执行计划可以发现它是会走index fast full scan ，已经走了主键这个索引；如果没有主键的情况，会走table access full（全表扫描），不管是耗费IO和逻辑读都会比走索引多，效率自然会慢。

原理：主键也是一个索引，而且不能为空，就意味着有一列包含id值和rowid 的索引块已经产生了，oracle在这一列上就能得到这个表的总数量，就不会选择全表扫描所有的数据块了，减少耗费的IO和逻辑读，从而提高效率。

注意：使用count(\*)走索引，不管你的数据里有没有null值，索引列必须不能为空，不然需要加上 field（列名）is not null 的条件才能让count(\*) 走索引。

二.sum和avg的优化

1.select sum(amount),avg(amount) from test

假设经常要统计amount字段求和或者平均数，可以在amount字段上加一个索引（通常好像没有这个说法），但是从提升效率和索引角度来说，建立amount字段的索引，也可以只在amount索引块上做扫描计算，注意的是用sum和avg也必须字段不为空或者加上 field（列名）is not null 的条件。

原理同count。

三.max和min的优化

1.select max(amount) from test / select min(amount) from test

对于max和min两个，建立索引对优化效率来说尤其体现明显，可以自己看一下执行计划，走的是index full scan。

问题：为什么sum和count走的是 index fast full scan 呢，和 index full scan 有什么区别呢?

原理：理解了索引结构的都知道，索引是有序的，而且索引块的数据 是从左到右依次从小到大，所以min只要扫描最左边那一个索引块，max只要扫描最右边那个索引块，加起来只有2次IO，所以max和min相对来说更快，耗费更小。

如图：![70][]

2.select max(amount),min(amount) from test

这样子写是不会走索引的，因为这样写还是先后 进行max和min运算，不管是先做max还是min运算，在同时数据变化的时候，不全表扫描是不能保证哪个是最大，哪个最小，oracle优化器也就会选择全表扫描了，所以可以改成这样子写，select max,min from (select max(amount) from test) a,(select min(amount) from test) b。

由于排序order by和回表扫描一起说，更好一点，所以order by优化和回表放到另一篇里，有兴趣的朋友可以看另一篇，oracle排序和回表扫描，写得不好，如果能给别人带来参考价值最好了，欢迎转载。

[70]: /images/20220521/664cb260f18746ae808195f7a4bee793.png