SQL大内高手：问一个简单代码的执行问题

[旭日阳刚]

我的理解是：如果执行下面这段代码，一共有三次扫描：
1. 第一次扫描整张表格whatever，将不同的行按照jobcode的值是否相同归在一起。
2. 第二次扫描上次得到的结果，计算每个分组的均值.
3. 第三次扫描上次的结果，过滤掉平均工资低于5万的组。

这种理解是否正确？
另外，如果最后一句是：HAVING avg(salary)>50000, 是直接过滤第二次的结果，还是重复第二次扫描，计算均值，然后做判断？

SELECT jobcode, avg(salary) as x
FROM whatever
GROUP BY jobcode
HAVING x>50000;

 

[mooncake]

我的理解是：如果执行下面这段代码，一共有三次扫描：
1. 第一次扫描整张表格whatever，将不同的行按照jobcode的值是否相同归在一起。
2. 第二次扫描上次得到的结果，计算每个分组的均值.
3. 第三次扫描上次的结果，过滤掉平均工资低于5万的组。

这种理解是否正确？
另外，如果最后一句是：HAVING avg(salary)>50000, 是直接过滤第二次的结果，还是重复第二次扫描，计算均值，然后做判断？

SELECT jobcode, avg(salary) as x
FROM whatever
GROUP BY jobcode
HAVING x>50000;

每一个DBMS可能有不同实现，不过我觉得至少可以优化成一次扫描：
先完成：

SELECT jobcode, avg(salary) as x
FROM whatever
GROUP BY jobcode

这个操作显然一次扫描就可完成。
然后在结果集里进行第二步HAVING x>50000;

 

[旭日阳刚]

嗯这样更节省时间。

 

[heureux]

我的理解是：如果执行下面这段代码，一共有一次DISK扫描， 一次MEMORY扫描：

1. 第一次扫描整张表格whatever （DISK access），将不同的行的 salary 按照jobcode的值是否相同“加”在一起。缓存于内存里 （SGA)

This is called full table scan!

2. 扫描并计算缓存里每组的 average salary！
IF average > 50000 Then out.

 

[旭日阳刚]

如果最后一句是这个呢？HAVING avg(salary)>50000

 

[heureux]

如果最后一句是这个呢？HAVING avg(salary)>50000

sorry, 少了个 0.

每个数据库都有个 Query Optimizer. 目前基本都是用 Cost based optimiser, 讲究时间最短，用内存最少。

所以，你那个 query 一次DISK扫描，一次MEMORY扫描应该是最少的了。 应该看到这样的扫描是狗熊捡棒子，扫到一行，用完就丢不需再用！

 

[旭日阳刚]

何仙威武！算是全明白了。
感谢！！

 

[mooncake]

我的理解是：如果执行下面这段代码，一共有一次DISK扫描， 一次MEMORY扫描：

1. 第一次扫描整张表格whatever （DISK access），将不同的行的 salary 按照jobcode的值是否相同“加”在一起。缓存于内存里 （SGA)

This is called full table scan!

2. 扫描并计算缓存里每组的 average salary！
IF average > 50000 Then out.

这样内存使用不是最优。更优的算法（普遍情况下）是：先index jobcode，然后顺序计算每个jobcode的avg(salary)。结果就出来了。同样是一次扫描，内存需求和最后结果集大小相同。

 

[heureux]

这样内存使用不是最优。更优的算法（普遍情况下）是：先index jobcode，然后顺序计算每个jobcode的avg(salary)。结果就出来了。同样是一次扫描，内存需求和最后结果集大小相同。

you are right 月饼V5

index jobcode 是必须的。 这样就一个 DISK scan，用很小很小的内存就完事了

 

[旭日阳刚]

除非这个数据库要多次用到，否则，用index应该更浪费时间吧？

 

[heureux]

除非这个数据库要多次用到，否则，用index应该更浪费时间吧？

不浪费啊！ 到DISK上找数据块是COST最高的，最好一次阅读就能搞定。

现在数据库一般一次DISK阅读就是 16 - 32 个 block (可以设想成一次读 16 行或32行）。这样 jobcode 一样的行基本读一次就搞定了！

 

[旭日阳刚]

Ok，感谢何仙和月饼。