【转载】51CTO-Android全文检索FTS来提高数据库查询速度

原文地址：http://bbs.51cto.com/thread-1033525-1.html

(一) 前言

        当我想总结下这个全文检索功能的时候，居然感觉不知从何说起。想了好久，决定还是从数据库查询速率入手说这个问题吧。我们Android采用sqlite做我们的数据库，很多数据都会存在数据库里面，比如联系人记录等等。那么当联系人数量变多的时候，比如1w条(尽管很少有人有这么多联系人，但是不能排除有人有，比如做销售的)，此时查询速度就是个很大的问题了。

 

(二) 问题提出

        现在我们要做一个智能拨号的功能：就是在拨号盘输入几个数字之后，手机会自动把号码包含这几个数字的联系人查询出来。

ok，那么我们用一般的方法肯定这样做：

1. 得到用户输入的号码数字, 比如135.

2. 用这个数字查询联系人数据库，如果电话号码包含这个数字的联系人，则查询出来，那么sql语句应该基本如此：

select * from xxxx where phone_number like '%135%'

        嗯，没错，这样我们确实完成了功能。但是这里有个问题，就是如果联系人很多的时候，比如1w个。然后用户输入1的时候我们查询一次；接着他输入13，我们又会查询，如此重复。那么很可能会发生查询速度慢的问题，给用户的体验就是当自己输完135之后，发现它还只是匹配出号码为 "1"的联系人。

那么，怎么解决这个问题呢。。

1.  第一种方法，可以在用户输入一定数目的联系人才去查询，比如4个，就是用户必须输入4个数字才开始查询。这样一定程度的减少了查询次数，还因为搜索条件比较苛刻，查询到的联系人数目会变少。但是有个问题，用户说我要是记得那么长的号码，还用你的智能拨号程序做什么?

2.  ok。。 那么，第二种方法，提高每次查询的速度。嗯，这就引出了我们今天的主题 ---- 全文检索

 

(三) 全文检索简介

        全文检索的简称是FTS(full text search), 在sqlite里面的话，我们有FTS3和FTS4可以使用。FTS其实就是创建一张虚拟表以供查询；它的一个很重要的作用就是可以让查询速度变得很快。根据官方统计数据统计显示(俺木有测试过。。)，下面是两种查询的速度比较：

首先，创建两种表：

CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT);     /* FTS3 table */
CREATE TABLE enrondata2(content TEXT);                        /* Ordinary table */

查询速度比较：

SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux';  /* 0.03 seconds */
SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */

嗯，速度差别还是蛮大的吧。。

ok...那么这个东西到底该怎么使用呢？

 

(四) 全文检索的使用

1. 建立全文检索表, 并且创建相关触发器。

如上所说，全文检索要建立一张虚拟表，那么这张虚拟表的数据怎么和原来要搜索的表数据保持一致呢？那么得靠我们的触发器了。具体过程如下：

a. 在数据库创建的时候(一般是onCreate的时候)创建虚拟表和触发器

db.execSQL("create virtual table vir_image using fts3(_id INTEGER PRIMARY KEY, index_text TEXT, source_id INTEGER);");  //创建虚拟表
//创建更新触发器，当imagelist表有更新的时候，更新虚拟表vir_image
db.execSQL("create trigger vir_image_update after update on imagelist begin update vir_image "   
                    + " set index_text = NEW.title where (source_id=NEW._id);"
                    + "  end;");
//创建删除触发器，当imagelist表删除数据的时候，同步删除vir_image一条记录
db.execSQL("create trigger vir_image_delete after delete on imagelist begin delete from vir_image " 
                    + " where source_id=OLD._id;"
                    + " end;");   

b. 那么看到这里，有些午饭就要问了，为什么只有更新和删除的触发器，怎么没有插入imagelist表的触发器呢？因为FTS3里面有个bug，不能使用插入记录的触发器。下面是bug的描述：

  // Don't use a trigger for updating the words table because of a bug
  // in FTS3.  The bug is such that the call to get the last inserted
  // row is incorrect.

那么，对于插入操作怎么同步呢？请看下面步骤.

 

2. 同步源表和虚拟表的插入操作

这里说来也是非常简单的了,就是在插入imagelist这个表的时候，同时也向虚拟表vir_image里面插入一条记录。如：

public long insert(Uri uri, String nullColumnHack, ContentValues values){
        long id = myDb.getWritableDatabase().insert(IMAGES_TABLE, nullColumnHack, values);  //插入一条记录到imagelist表中，同时得到这条记录在imagelist表中的_id
        String title = values.getAsString("title");
        myDb.getWritableDatabase().execSQL("insert into vir_image(index_text,source_id) values('"+title+"',"+ id +")");//插入相关内容到vir_image这个虚拟表中
        return id;
}

3. 查询虚拟表

ok,到这里的话，虚拟表工作已经基本完成，如果我们要查询数据的话，怎么办呢？

我们可以这样写：

return myDb.getWritableDatabase().rawQuery("select * from vir_image where vir_image match 'tit*'", null);

是的，这里使用的是一个新的关键字"match"! 不是我们以前使用的'like'.当然，这种全文检索的语法还有很多，大家可以去问问度娘或者什么的就知道了。