FTS数据库优化（Android）原理与应用详解（1）

在Android的官方开发文档上，有建议在使用文本类的数据库全文搜索（full-text search）时，使用FTS优化查询速度。有关FTS的介绍文章不多，本文调研整理一下有关知识，供在Android上使用FTS之前参考。

1.什么是FTS？

FTS，即full text searches的缩写。是SQLite提供的一个针对文本类模糊查询的优化工具。不出所料，其优化方式也是在索引上做文章，这部分在4中介绍，暂时不展开。FTS并非标准SQL语言支持的功能。Android的数据库底层基于SQLite，所以也支持FTS。

2.如何在Android上使用FTS？——Android官方demo解析

SQLite提供了一种内嵌于SQL语句中的使用FTS的方法，简单地说，需要做两件事：创建FTS的virtual table、在原始数据库发生增删改的时候trigger FTS virtual table同步。这样，对应的查询就可以在FTS virtual table上进行了。至于创建以及使用索引的事情，是SQLite在背后偷偷做的，使用者无需关心。Android官方给出了一个doc和一个project来演示如何使用FTS，本文先从这里入手，分析一下，然后再做补充。

doc：https://developer.android.com/training/search/search.html

demo project：https://github.com/android/platform_development/tree/master/samples/SearchableDictionary/

Android源代码中也提供了这个demo：development/samples/SearchableDictionary
doc内容有限，仅仅是demo project的讲解。

这个demo同时也是Android搜索框架的demo，搜索框架相关内容可以参考另外两篇文章：
如何将自己的App作为外部数据源提供给Android系统搜索？
Android框架/系统服务是怎样管理第三方Search数据源的？

这是一个Eclipse project，如果使用Android Studio，可以使用导入功能：File - New - Import Project，选择工程根目录即可，Android Studo会自动创建一个gradle工程并且将原始Eclipse工程导入。

这个demo的数据库是一个字典数据，有单词和解释两个字段，在代码中是raw res文件res/raw/definitions.txt，数据样例：

abbey - n. a monastery ruled by an abbotabide - v. dwell; inhabit or live inabound - v. be abundant or plentiful; exist in large quantitiesabsence - n. the state of being absentabsorb - v. assimilate or take inabstinence - n. practice of refraining from indulging an appetite especially alcoholabsurd - j. inconsistent with reason or logic or common sense

数据库工具类DictionaryDatabase.java中实现了SQLiteOpenHelper：

    //The columns we'll include in the dictionary table    public static final String KEY_WORD = SearchManager.SUGGEST_COLUMN_TEXT_1;    public static final String KEY_DEFINITION = SearchManager.SUGGEST_COLUMN_TEXT_2;    private static final String DATABASE_NAME = "dictionary";    private static final String FTS_VIRTUAL_TABLE = "FTSdictionary";    private static final int DATABASE_VERSION = 2;    /**     * This creates/opens the database.     */    private static class DictionaryOpenHelper extends SQLiteOpenHelper {        ......        /* Note that FTS3 does not support column constraints and thus, you cannot         * declare a primary key. However, "rowid" is automatically used as a unique         * identifier, so when making requests, we will use "_id" as an alias for "rowid"         */        private static final String FTS_TABLE_CREATE =                    "CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE +                    " USING fts3 (" +                    KEY_WORD + ", " +                    KEY_DEFINITION + ");";        ......        @Override        public void onCreate(SQLiteDatabase db) {            mDatabase = db;            mDatabase.execSQL(FTS_TABLE_CREATE);            loadDictionary();        }        /**         * Starts a thread to load the database table with words         */        private void loadDictionary() {            new Thread(new Runnable() {                public void run() {                    try {                        loadWords();                    } catch (IOException e) {                        throw new RuntimeException(e);                    }                }            }).start();        }        private void loadWords() throws IOException {            Log.d(TAG, "Loading words...");            final Resources resources = mHelperContext.getResources();            InputStream inputStream = resources.openRawResource(R.raw.definitions);            BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));            try {                String line;                while ((line = reader.readLine()) != null) {                    String[] strings = TextUtils.split(line, "-");                    if (strings.length < 2) continue;                    long id = addWord(strings[0].trim(), strings[1].trim());                    if (id < 0) {                        Log.e(TAG, "unable to add word: " + strings[0].trim());                    }                }            } finally {                reader.close();            }            Log.d(TAG, "DONE loading words.");        }        /**         * Add a word to the dictionary.         * @return rowId or -1 if failed         */        public long addWord(String word, String definition) {            ContentValues initialValues = new ContentValues();            initialValues.put(KEY_WORD, word);            initialValues.put(KEY_DEFINITION, definition);            return mDatabase.insert(FTS_VIRTUAL_TABLE, null, initialValues);        }        @Override        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "                    + newVersion + ", which will destroy all old data");            db.execSQL("DROP TABLE IF EXISTS " + FTS_VIRTUAL_TABLE);            onCreate(db);        }    }

可以看到，onCreate()（处理创建数据库）逻辑中，创建FTS virtual table，并且解析字典数据，并且插入到FTS virtual table中。关键是创建FTS virtual table的部分，其SQL为

CREATE VIRTUAL TABLE FTSdictionary USING fts3 (suggest_text_1, suggest_text_2);

这实际上是SQLite为FTS提供的一个语法糖，使得创建FTS virtual table可以和使用标准SQL创建一般的table一样简单，无需破坏编程风格和可读性。
创建完了virtual table，看看增删改，从loadDictionary()方法看到其插入操作与一般的table无异。
再看看查询，在DictionaryDatabase.java中

    /**     * Returns a Cursor over all words that match the given query     *     * @param query The string to search for     * @param columns The columns to include, if null then all are included     * @return Cursor over all words that match, or null if none found.     */    public Cursor getWordMatches(String query, String[] columns) {        String selection = KEY_WORD + " MATCH ?";        String[] selectionArgs = new String[] {query+"*"};        return query(selection, selectionArgs, columns);        /* This builds a query that looks like:         *     SELECT <columns> FROM <table> WHERE <KEY_WORD> MATCH 'query*'         * which is an FTS3 search for the query text (plus a wildcard) inside the word column.         *         * - "rowid" is the unique id for all rows but we need this value for the "_id" column in         *    order for the Adapters to work, so the columns need to make "_id" an alias for "rowid"         * - "rowid" also needs to be used by the SUGGEST_COLUMN_INTENT_DATA alias in order         *   for suggestions to carry the proper intent data.         *   These aliases are defined in the DictionaryProvider when queries are made.         * - This can be revised to also search the definition text with FTS3 by changing         *   the selection clause to use FTS_VIRTUAL_TABLE instead of KEY_WORD (to search across         *   the entire table, but sorting the relevance could be difficult.         */    }    /**     * Performs a database query.     * @param selection The selection clause     * @param selectionArgs Selection arguments for "?" components in the selection     * @param columns The columns to return     * @return A Cursor over all rows matching the query     */    private Cursor query(String selection, String[] selectionArgs, String[] columns) {        /* The SQLiteBuilder provides a map for all possible columns requested to         * actual columns in the database, creating a simple column alias mechanism         * by which the ContentProvider does not need to know the real column names         */        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();        builder.setTables(FTS_VIRTUAL_TABLE);        builder.setProjectionMap(mColumnMap);        Cursor cursor = builder.query(mDatabaseOpenHelper.getReadableDatabase(),                columns, selection, selectionArgs, null, null, null);        if (cursor == null) {            return null;        } else if (!cursor.moveToFirst()) {            cursor.close();            return null;        }        return cursor;    }

看到使用了一个关键字“MATCH”，其SQL语句如下，不同于标准SQL中的LIKE

SELECT <columns> FROM <table> WHERE <KEY_WORD> MATCH 'query*'

在这个demo中，数据库比较简单，只有FTS virtual table本身。看一下数据库中的表的情况，经过上述操作，一共有四个表：
FTSdictionary
FTSdictionary_content
FTSdictionary_segdir
FTSdictionary_segments
可见，虽然在语法上有“virtual table”，但实际上仍然是在数据库中创建了四个表。
如果本身的数据库已经很复杂了，那么需要在对应的数据库表增删改的时候，同步trigger FTS virtual table。