一个分组统计SQL的优化过程(4)

接: http://mikixiyou.iteye.com/blog/1491153

一个分组统计SQL的优化过程(1)

接: http://mikixiyou.iteye.com/blog/1491177

一个分组统计SQL的优化过程(2)

接: http://mikixiyou.iteye.com/blog/1491283

一个分组统计SQL的优化过程(3)

优化

这个 SQL 优化像交通堵塞疏导一样。我们可以将车站比做磁盘文件，道路比作内存，车子比做数据块，乘客比做数据记录。所有乘客按照进站顺序坐在不同的车子的各个座位上。

假如，一个车站，里面有 5 辆车子，一共进来 100 个乘客。这些乘客会根据车站管理员的要求，按照进站顺序依次坐在车站里 10 辆车子上。我们将这些车子按照 A 、 B 、 C 、 D 、 E 编号，将乘客按照进站顺序按照 1..100 编号。这些乘客去哪里不是上车就座时决定的，而是大家都坐好后由车站管理员的老板决定的。老板决定籍贯为上海的乘客去某地，车站管理员找出籍贯为上海的 1 、 21 、 42 、 63 、 84 号乘客去某地，于是我们得找到这些乘客目前在哪些车辆上。很不幸，这 5 个乘客在 A 、 B 、 C 、 D 、 E 共 5 辆车上，车站管理员不能让这些乘客都下来坐另一辆车，也不能空一辆出来，于是只好 5 辆车同时出门了。上路一看，就两道，得排队在道路上跑。

大家会不会觉得这车站管理员是多么得愚蠢？

车站管理员竟然按照进来乘客进站的顺序将他们依次坐在依次排开的车子上，而不是按乘客的籍贯尽可能地将籍贯相同的乘客安排在同一车子上。问题是管理员事先是不知道老板怎么会以籍贯来决定呢，乘客自己也不知道，决定乘客是否到哪里是老板决定的。从车站管理员的角度看，将乘客按进站顺序放到车子上，是最合理的，这样最快地将乘客安排上车。

老板是怎么决定的呢？如根据籍贯，决定籍贯为上海的都拉出来；如根据收入，决定收入超过 5000 每月的都拉出来。

针对当前的问题，做为车站管理员能想到什么方法呢？

一种方法是将路拓宽，保证这么所有的车辆一起通行；另一种方法是将籍贯相同的乘客在上车时就放在同一辆车子，这样只要一辆车就能将这些乘客拉出去。

回到数据库系统层面，我们先看第二种方法。

这个 SQL 是按照 brhid 进行分组查询，就是说，将 brhid 相同的记录先去出来。如果我们将表中 brhid 相同的记录都尽量保存一个数据块中而不是像现在这样随机地散落在任意的数据块中。记录重新分布后，则搜索一个 brhid 的记录时，就会尽可能少地读取数据块。

我们继续以 brhid=’8088’ 举例，每个数据块能最多存储 17 条记录，一共约 9600 条记录。算一下，我们需要的数据块数也就 500 到 600 个，加上 10% 的空闲空间，最大不会超过 700 个数据块。和现在的 10688 个数据块，相差 12 倍之多。

按照 brhid 排序建立一张临时表，将 brhid 相同的记录尽可能地保存在一起。方法如下 :

Create table cust_info_bk2 nologging as select * from cust_info order by brhid;

在新表中，记录是按照 brhid 排序进入表的，因此相同 brhid 的记录都会保存在一起，空间占用的 block 都是相邻的。

SQL> select count(*)
  2    from (select distinct dbms_rowid.rowid_relative_fno(rowid) f,
  3                          dbms_rowid.rowid_block_number(rowid) b
  4            from  cust_info­_bk2
  5           where brhid = '8088');

  COUNT(*)
----------
       684

这些记录只 保存在 684 个数据块中。

这个优化操作能保证我们优化的 SQL 能从遍历 1W 个数据块降低到 684 个数据块。数据块的减少，也能使得磁盘读降低。没有了这些磁盘读， SQL 执行的性能也将大幅提升。

测试结果也证实，每次查询都在秒级，而原表的查询时间则是几十秒，可见这个优化措施效果明显。这个效果也是可以理解的，读取的数据块小了，性能自然会有提升。而且，读取的数据块完全在内存里，不需要再次去读取磁盘，这才是性能提升的决定性的因素。

这种方法确实是有效果的。但如果同一 brhid 需要的数据块非常多，多到一定程度上，问题就会回到原点。

我们已经很清楚，是内存读很大，导致了磁盘读。又是磁盘度导致了运行时间上升，性能下降。这个表也只有 25W 数据块合计 2GB 的空间，我们完全可以将他全部地放入内存中，使之常驻缓存。这样就不会因为 LRU 机制而被自动清理出缓存，从而导致再次读取时的磁盘读。

若我们将 2GB 的表放入内存中，则读取该表时所涉及的数据块都会在计算机内存中，既使不在所请求实例的内存中，也就是多一次或两次的网络传输，数据块从 holding 到复制到所请求的实例中，再读取出来。这样也比从磁盘读取强 1-2 个数量级。

在 RAC 环境下，进程读取一个数据块比单实例复杂。单实例环境下，进程读取数据块，若在内存中，则直接读取到；若不在内存中，则会去磁盘文件读取数据到内存中，再取到数据块。多节点的 RAC 环境下，进程读取数据块，首先从 GCS 去判断数据块在哪个实例的内存中。若刚好在所请求实例的内存中，则读取到；若不在请求实例的内存，则会通过 GCS 返回的信息去找这个数据块在哪个实例中，找到这个实例，通过心跳网络复制数据块到请求实例；若都不在，则直接去读磁盘文件。

如何将数据库对象缓存进内存而不会因为 LRU 机制而清理出去呢。 Oracle 提供另一种缓存空间，称之为 keep pool 。这个空间管理机制采用的是队列机制，所缓存的对象先进先出。 keep pool 和 defult pool 都是 block buffer ，两个池的大小由参数 db_keep_cache_size 和 db_cache_size 决定。

操作方法如下：

SQL> ALTER TABLE CUST_INFO STORAGE(buffer_pool keep);

SQL> ALTER TABLE CUST_INFO CACHE;

注：这个操作需要先在数据库初始化参数里配置 db_keep_cache_size 项后才能生效。