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。