从一个例子看综合搜索的发展

   最近在看《Expert one on one Oracle》第一版，Tom Kyte在《Tuning Strategies》这章举了个他实际例子说明如何针对性地设计DB。这个例子是个很实际的需求，并随着时间发展总结出了更好的作法，在这里分享一下。

    这个例子用一句话简单描述就是“综合搜索”。假定有N张表，每张表上有许多字段，这其中我们只关心某些字段，需求是用户输入某些线索，系统列出所有匹配的记录。比如，用户在综合搜索框中输入"tom"，系统可能返回创作者为"Tom Wang"或更新者为"Tommy"的单据，也可能返回Email为“[e-mail]”的用户及名为"tom and cat"的商品。也就是说，要在多表、多字段中对同一个关键词进行；一般来说，不关心关键字所处的位置和拼写。

  如何尽快返回结果呢？Tom Kyte在书中举的是一个轻量级的例子，只有一张表，67个字段，75000行，并且源表是只读的。为了尽快索引这张表，他采用了某些特殊处理。考虑到这一版的写作年份，其处理是相当巧妙地。下面我们来创建一个相类似的表，并看看其是怎么处理的。

create sequence INC
start with 1
increment by 1
cache 200;

create table objects as select object_name from all_objects;
alter table objects add oid number(10);
update objects set oid =inc.nextval;
alter table objects  add constraint PK_OBJECTS primary key (OID);

create table x
(
 c1 varchar2(30),
 c2 varchar2(30),
 c3 varchar2(30),
 c4 varchar2(30),
 c5 varchar2(30),
 c6 varchar2(30),
 c7 varchar2(30),
 c8 varchar2(30),
 c9 varchar2(30),
 c10 varchar2(30),
 d1 char(1024), 
 d2 char(1024),
 d3 char(1024),
 d4 char(1024),
 d5 char(1024)
);

DECLARE
  i INTEGER;
  s INTEGER := 50000;
  m INTEGER;
BEGIN
  select count(0) into m from objects;

  FOR i IN 1 .. s LOOP
    INSERT INTO x
      (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, d1, d2, d3, d4, d5)
    VALUES
      ((SELECT object_name
         FROM objects
        WHERE OID = MOD(abs(dbms_random.random), m)),
       (SELECT object_name
          FROM objects
         WHERE OID = MOD(abs(dbms_random.random), m)),
       (SELECT object_name
          FROM objects
         WHERE OID = MOD(abs(dbms_random.random), m)),
       (SELECT object_name
          FROM objects
         WHERE OID = MOD(abs(dbms_random.random), m)),
       (SELECT object_name
          FROM objects
         WHERE OID = MOD(abs(dbms_random.random), m)),
       (SELECT object_name
          FROM objects
         WHERE OID = MOD(abs(dbms_random.random), m)),
       (SELECT object_name
          FROM objects
         WHERE OID = MOD(abs(dbms_random.random), m)),
       (SELECT object_name
          FROM objects
         WHERE OID = MOD(abs(dbms_random.random), m)),
       (SELECT object_name
          FROM objects
         WHERE OID = MOD(abs(dbms_random.random), m)),
       (SELECT object_name
          FROM objects
         WHERE OID = MOD(abs(dbms_random.random), m)),
       lpad('*', 1023, '*'),
       lpad('*', 1023, '*'),
       lpad('*', 1023, '*'),
       lpad('*', 1023, '*'),
       lpad('*', 1023, '*'));
  END LOOP;
END;
/



insert into x select * from x;

commit;

 

   这样就创建了一个X表，共有10万行，其中c1到c10是需要被查找列，d1到d5比较大，完全是占位符，作用是模拟一个实际行的大小。

 

   怎么找出所有包含某个内容，比如说"E548D7A9"的行呢？最简单的方法是直接查找，效果显然非常差，在我的机器（E6550,2G）上,以下的查询花了10左右秒才跑完。这还是单用户的情况，可以说完全不能接受。

SELECT *
  FROM x
 WHERE UPPER(C1) LIKE '%E548D7A9%'
    OR UPPER(C2) LIKE '%E548D7A9%'
    OR UPPER(C3) LIKE '%E548D7A9%'
    OR UPPER(C4) LIKE '%E548D7A9%'
    OR UPPER(C5) LIKE '%E548D7A9%'
    OR UPPER(C6) LIKE '%E548D7A9%'
    OR UPPER(C7) LIKE '%E548D7A9%'
    OR UPPER(C8) LIKE '%E548D7A9%'
    OR UPPER(C9) LIKE '%E548D7A9%'
    OR UPPER(C10) LIKE '%E548D7A9%';

   给每个字段加上索引会不会快一点?

 

create index IX_X_C1 on X (UPPER(C1));
create index IX_X_C2 on X (UPPER(C2));
create index IX_X_C3 on X (UPPER(C3));
create index IX_X_C4 on X (UPPER(C4));
create index IX_X_C5 on X (UPPER(C5));
create index IX_X_C6 on X (UPPER(C6));
create index IX_X_C7 on X (UPPER(C7));
create index IX_X_C8 on X (UPPER(C8));
create index IX_X_C9 on X (UPPER(C9));
create index IX_X_C10 on X (UPPER(C10));

   结果和刚才几乎没有区别，还是10s。实际上，由于LIKE是两端模糊，使用这些索引的代价太高了，看一下执行计划就会发现，Oracle选择的仍然是对X表的full table scan，而不会用 IX_X_C?去做fast full scan。这些索引白作了。

   同时扫描这么多列速度比较慢，在X上冗余一列保存需搜索的信息又会不会好一点呢？

 

alter table X add cs varchar2(400);
UPDATE X
   SET cs = upper(C1) || '|' || upper(C2) || '|' || upper(C3) || '|' ||
            upper(C4) || '|' || upper(C5) || '|' || upper(C6) || '|' ||
            upper(C7) || '|' || upper(C8) || '|' || upper(C9) || '|' ||
            upper(C10);
commit;
SELECT *
  FROM x
 WHERE CS LIKE '%E548D7A9%';

   很不幸的，基本没有变化，还是10S左右。同样地，即使在cs上加索引也不会被用到。

   问题出在什么地方？

   Tom Kyte在书中指出，这里的问题不是 全表扫描。在目前的架构下，全表扫描是不可避免的。问题是表太大 了。由于d1~d5的存在，每行包含很多不需要被搜索的内容，在full table scan时这些内容都会被读到SGA中，这会造成所谓的“缓存颠簸”：每次搜索中每个block都被physical read，后面的数据会挤出前面的缓存，缓存失效了。

    找到了问题所在，相应的对策就是减少被缓存的数据量，使所有数据都能被缓存。Tom 的方法是另外构建一张专用的查询表，并设置其cache选项，为full table scan专门优化。

 

 CREATE TABLE FAST_X
PCTFREE 0
CACHE
AS
SELECT upper(C1)||'|'||upper(C2)
||'|'||upper(C3)
||'|'||upper(C4)
||'|'||upper(C5)
||'|'||upper(C6)
||'|'||upper(C7)
||'|'||upper(C8)
||'|'||upper(C9)
||'|'||upper(C10) CONTENT,
ROWID ROW_ID
FROM X;

   用以下语句试试查询，效果非常好，基本是1s左右，算是可用了

 

SELECT *
  FROM X
 WHERE ROWID IN (SELECT ROW_ID FROM FAST_X WHERE CONTENT LIKE '%E548D7A9%')

   由于fast_x cache在buffer中，避免了disk i/o, full table scan在这样的数量级下也没有那么可怕了。

 

--------------------------------------------------------------------------------------------------------------------------------

以上是tom在书中给出的解决方案。 如果你的数据量和它类似，那么这种方法非常值得借鉴。

但是，随着数据量的上升，此方法会逐渐显示出了不足之处。下面我们来看看还有什么解决方案。

还是这张表，假如不是10w行，而是80w行，这个方法还可行吗？

insert into x select * from x;
insert into x select * from x;
insert into x select * from x;
commit;

drop table FAST_X;
 CREATE TABLE FAST_X
PCTFREE 0
CACHE
AS
SELECT upper(C1)||'|'||upper(C2)
||'|'||upper(C3)
||'|'||upper(C4)
||'|'||upper(C5)
||'|'||upper(C6)
||'|'||upper(C7)
||'|'||upper(C8)
||'|'||upper(C9)
||'|'||upper(C10) CONTENT,
ROWID ROW_ID
FROM X;

SELECT *
  FROM X
 WHERE ROWID IN (SELECT ROW_ID FROM FAST_X WHERE CONTENT LIKE '%E548D7A9%')

 

 

   尽管采用了缓存FAST_X的策略，查询时间还是到了10s左右。可以想见，full table scan是O(n)的复杂度，就算能够在内存完成，随着数据量的上升，查询时间也将线性的上升。

   实际应用中超百万的行数是很常见的，所以需要找到更好的处理方法。所谓更好的方法，在数据库来说无非就是想法搞一个能用上的索引，这时候Oracle Text就派上用场了。Oracle Text 是在Oracle 9i中才完善的一个文本搜索引擎，Oracle 8时代的名称好像是interMedia Text（不确定），按照Tom的说法，interMedia Text连%ABC%这样的查询都不支持，所以他们没用。但到了Oracle Text，随着功能的完善，用来实现综合简直是大材小用了。

   对fast_x上的 content作context索引

create index fast_x_content on
FAST_X(CONTENT)
indextype is CTXSYS.CONTEXT;

 

   并用以下语句作查询

 

SELECT * FROM X
WHERE ROWID IN
(
SELECT ROW_ID FROM FAST_X
WHERE contains (CONTENT,'%E548D7A9%')>0
)

    执行时间在0.5m左右，快了很多。 更好的是，content 索引的复杂度为 O(ln(n))，即使数据量再大上一倍，执行时间也不会增加太多（实测约为0.7s）。

    对综合查询来说，Oracle Text的推出解决了核心的性能问题。除此之外，还有一些问题需要解决：

    1）数据来源不只是单表(如例中的X)，可能是X1,X2,X3...。在每张源表单独建context index不是一个好主意，比较好的方法是把所有表所有字段都放到辅助表（如例中的FAST_X)中去。这样能减少索引数，减少相应时间

    2） 一般的源表不会如例中那样只读，这就有个数据刷新的问题。有两部分数据需要更新，一是将X的数据刷新到FAST_X，另一个是更新FAST_X上的索引。前者可以通过同步或者异步的方式实现，同步方式是指Trigger，异步则需要使用MQ。若数据量较大，请还是用MQ吧，性能上要好很多；后者则取决于用户对于内容延迟的忍耐时间，零容忍需要立刻更新index，而在用户能接受情况下5或10分钟更新一次index对于系统性能会很有帮助。

    解决了以上问题，一个使用的综合搜索基本能搭建好了。

    以上是综合搜索功能在Oracle数据库上实现的一个过程。近年来，Lucene等基于Java的全文搜索引擎日益流行，现在的趋势是将这一块从数据库中剥离出来，由于APP Sever在成本和扩展性上都要优于DB Sever，把负载从DB SERVER转移到APP Sever上去很划算。在APP Sever上利用文件系统和java处理搜索，虽然结构更复杂点，但效果比Oracle Text还要好些，甚至如果搜索需求很大，完全可以建一个单独的Searching Server，扩展性也很好。

    最后提一句，引入Lucene后多了个问题，采用什么策略把元数据从DB更新到APP Server?不过那是另外一个话题，不在本文讨论的范围之列了。

评论

1 楼 imp860124 2010-08-25  

看完收获不小。学习了。。。