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 abbot
abide - v. dwell; inhabit or live in
abound - v. be abundant or plentiful; exist in large quantities
absence - n. the state of being absent
absorb - v. assimilate or take in
abstinence - n. practice of refraining from indulging an appetite especially alcohol
absurd - 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。