0

我的帖子

个人中心

设置

  发新话题
(一) 前言
当我想总结下这个全文检索功能的时候,居然感觉不知从何说起。想了好久,决定还是从数据库查询速率入手说这个问题吧。我们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'.当然,这种全文检索的语法还有很多,大家可以去问问度娘或者什么的就知道了。

好了。。 这个就是全文检索的总结了。。 希望对大家有所帮助。。

附一个参考网站:
http://www.sqlite.org/fts3.html#section_1

本帖最近评分记录
  • MarsBobo 无忧币 +10 优质内容 2013-5-9 17:53



本帖最后由 rongwei84n 于 2013-5-9 18:05 编辑
数据库查询速度确实是一个很纠结的问题。。 :lol1 :lol1
本帖最近评分记录
  • MarsBobo 无忧币 +1 技术门诊发了个帖子~ 有空时进去帮忙给回 ... 2013-5-9 17:54



:lol1 不错...  谢谢分享   还有个以前处理软件查询慢的思路   让一些常用的数据   在软件空闲时去 自动去整理成 XML或者其他形式的...读的时候直接读的是XML  把数据静态化  这样就快很多



引用:
原帖由 林的理想 于 2013-5-8 16:21 发表
:lol1 不错...  谢谢分享   还有个以前处理软件查询慢的思路   让一些常用的数据   在软件空闲时去 自动去整理成 XML或者其他形式的...读的时候直接读的是XML  把数据静态化  这样就快很多 ...
:lol1 还有这样子的呀。。 呵呵, 没想到过。 那如果软件没有及时处理xml,怎么处理呢?



引用:
原帖由 rongwei84n 于 2013-5-8 16:25 发表

:lol1 还有这样子的呀。。 呵呵, 没想到过。 那如果软件没有及时处理xml,怎么处理呢?
如果不存在XML列表 就走直查   也有的处理是按bacth去执行的 每隔多少分钟 或者小时去让数据静态化



引用:
原帖由 林的理想 于 2013-5-9 01:39 发表

如果不存在XML列表 就走直查   也有的处理是按bacth去执行的 每隔多少分钟 或者小时去让数据静态化
原来如此啊, 学习了。。:lol1  话说里面那个逻辑控制应该比较复杂吧?



实用啊,谢谢



官方有FTS3和FTS4 ?   我官网都读了没看到过啊。。。求相关链接啊LZ!!!



╰☆╮听悲伤的歌,看幸福的戏…年少轻狂…幸福时光......
引用:
原帖由 华华世界 于 2013-5-10 14:47 发表
官方有FTS3和FTS4 ?   我官网都读了没看到过啊。。。求相关链接啊LZ!!!
就是文章最后一个链接,官网链接



引用:
原帖由 维尼雷锋 于 2013-5-10 13:53 发表
实用啊,谢谢
不客气



引用:
原帖由 华华世界 于 2013-5-10 02:47 PM 发表
官方有FTS3和FTS4 ?   我官网都读了没看到过啊。。。求相关链接啊LZ!!!
好的,已Mark...有事没事就研究看看...谢谢啦



╰☆╮听悲伤的歌,看幸福的戏…年少轻狂…幸福时光......
不错 verygood 刚刚在查这方面的资料



引用:
原帖由 xfh001 于 2014-3-20 11:44 发表
不错 verygood 刚刚在查这方面的资料
你不回复我这个帖子,自己都忘记了曾经有这么个总结了。。



‹‹ 上一贴:Sencha Touch中创建数据源的问题,急求! ...   |   下一贴:eclipse!各位大虾,求指点!具体点,小的初学 ... ››
  发新话题
快速回复主题
关于我们 | 诚聘英才 | 联系我们 | 网站大事 | 友情链接 |意见反馈 | 网站地图
Copyright©2005-2019 51CTO.COM
本论坛言论纯属发布者个人意见,不代表51CTO网站立场!如有疑义,请与管理员联系:bbs@51cto.com