萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

Coreseek (Sphinx) 建立即時索引 (RT Index) 與從 MySQL 同步索引完整教學

Coreseek 基於 Sphinx + MMSEG 中文分詞,提供 MySQL 協議介面 (Port 9306)。本文完整記錄:RT 索引建立、資料寫入、從 MySQL 表同步建立普通索引的完整配置與操作流程。

架構認知

  • Sphinx 啟動後監聽 9306 埠,支援 MySQL 客戶端連線
  • SHOW TABLES 顯示的是索引而非真實表
  • RT (Real-Time) 索引:支援即時 INSERT/REPLACE/DELETE,無需重建
  • Plain 索引:從 MySQL 批量抓取建立,需定期 indexer --rotate 重建

第一部分:建立 RT 索引 (即時寫入)

1. 修改 sphinx.conf

index tank_test
{
    type = rt
    path = /usr/local/sphinx/var/data/rt
    charset_dictpath = /usr/local/mmseg3/etc/
    charset_type = zh_cn.utf-8
    ngram_len = 0
    rt_field = name
    rt_field = title
    rt_field = sub_title
    rt_attr_uint = user_id
    rt_attr_uint = uid
}

rt_field = 全文檢索欄位;rt_attr_uint = 回傳/過濾屬性欄位

2. 重啟 Sphinx

pkill -9 searchd
/usr/local/sphinx/bin/indexer --config /usr/local/sphinx/etc/sphinx.conf --all
/usr/local/sphinx/bin/searchd --config /usr/local/sphinx/etc/sphinx.conf

3. 連線 9306 測試

mysql -h 127.0.0.1 -P 9306
DESC tank_test;
INSERT INTO tank_test VALUES (3,'坦克','tank is 坦克','技術總監',1311895260,33);
SELECT * FROM tank_test WHERE MATCH('坦克');
SHOW META;

第二部分:從 MySQL 同步建立 Plain 索引

1. MySQL 建立業務表並寫入資料

CREATE TABLE orders (...);
INSERT INTO orders ...;

2. sphinx.conf 新增 source + index

source order
{
    type = mysql
    sql_host = localhost
    sql_user = root
    sql_pass =
    sql_db = test
    sql_query_pre = SET NAMES utf8
    sql_query = SELECT id, user_id, username, UNIX_TIMESTAMP(create_time) AS create_time, product_name, summary FROM orders
    sql_attr_uint = user_id
    sql_attr_timestamp = create_time
    sql_ranged_throttle = 0
    sql_query_info = SELECT * FROM orders WHERE id=$id
}

index myorder
{
    source = order
    path = /usr/local/sphinx/var/data/myorder
    docinfo = extern
    mlock = 0
    morphology = none
    min_word_len = 1
    charset_dictpath = /usr/local/mmseg3/etc/
    charset_type = zh_cn.utf-8
    ngram_len = 0
    html_strip = 0
}

3. 重建索引並測試

pkill -9 searchd
/usr/local/sphinx/bin/indexer --config /usr/local/sphinx/etc/sphinx.conf --all
/usr/local/sphinx/bin/searchd --config /usr/local/sphinx/etc/sphinx.conf

mysql -h 127.0.0.1 -P 9306
SELECT * FROM myorder WHERE MATCH('坦克');

關鍵提醒

  • PHP 連線 Port 3306 (MySQL) vs 9306 (Sphinx) 勿混淆
  • RT 索引適合即時性高、寫入頻繁場景;Plain 索引適合批量、夜間重建
  • MMSEG 詞典路徑 charset_dictpath 必須正確,否則中文分詞失效
copyright © 萬盛學電腦網 all rights reserved