Sphinx 增量索引的方法

之前有寫過一篇 MySQL 全文檢索引擎 - Sphinx 的文章，最近又把它拿出來用了，不過當時即時更新索引的問題，如今則找到解決的方法了，透過更新增量索引的方式達到即時更新。

簡單的說就是利用兩個索引表的合併查詢來做到，一個是完整的索引表，一個是針對當日資料變動的增量索引。

部分的設定檔如下：

# ...

source _source_base
{
    # 來源-共用的設定
}

source people_full : _source_base
{
    sql_query      = \
        SELECT \
            people_profile.id, \
            people_profile.main_name \
        FROM people_profile
        
    sql_query_killlist = \
        SELECT id FROM people_profile \
        WHERE update_date >= CURDATE()
}

# 增量索引來源，這邊只會抓出當日變動的資料
# 建議在 update_date 欄位加上 MySQL INDEX
source people_delta : people_full
{
    sql_query      = \
        SELECT \
            people_profile.id, \
            people_profile.main_name \
        FROM people_profile \
        WHERE people_profile.update_date >= CURDATE()
}


index _index_base
{
    # 索引-共用的設定
}

index people_full : _index_base
{
    source          = people_full
    path            = /var/lib/sphinxsearch/data/people_full
}

# 增量索引
index people_delta : _index_base
{
    source          = people_delta
    path            = /var/lib/sphinxsearch/data/people_delta
}

# 透過 distributed 類型來合併索引
index people
{
    type            = distributed
    local           = people_full
    local           = people_delta
}

# ...

建立 SphinxSE 表
特別注意在 CONNECTION 中所指定索引表為 people。

CREATE TABLE people_sphinx(
    id BIGINT UNSIGNED NOT NULL   COMMENT '搜尋結果的 Id',
    weight INT NOT NULL           COMMENT '搜尋結果的權重',
    query VARCHAR(3072) NOT NULL  COMMENT '搜尋的查詢條件',
  
    INDEX(query)
)ENGINE=SPHINX 
CONNECTION="sphinx://localhost:9312/people" 
COMMENT='People Sphinx搜尋連接介面';

SQL 的查詢測試
這裡使用 INNER JOIN 方式作查詢，這樣對於刪除資料的變動，就不會出現在查詢結果中。

SELECT A.id, A.main_name, B.weight
FROM people_sphinx B
INNER JOIN people_profile A
USING(id) 
WHERE B.query='馬丁尼茲;mode=any;limit=1000'

透過 PHP 更新增量索引
在資料 INSERT 或 UPDATE 時，去呼叫索引更新，這樣在第一時間就可以更新索引。

shell_exec('sudo indexer --quiet --rotate people_delta 2>&1');

如果 Server 是 Ubuntu，請記得在 vim /etc/sudoers 中賦予 apache 使用 indexer 的權限。
www-data ALL=(root) NOPASSWD: /usr/bin/indexer


在 crontab 中加上排程
利用離峰時間來更新完整的索引表，由於刪除資料的變動需要更新完整索引表才有辦法移除。
00 00 * * * indexer --quiet --rotate --all > /dev/null 2>&1