Sphinx Search 實現多表關聯全文檢索：配置、建索引、查詢語法完整示例

Sphinx（及其分支 Manticore）支援 sql_query 中編寫 JOIN SQL，將多表關聯結果平鋪為單一文檔索引，實現跨表全文搜索。本文以 orders 主表關聯 users 為例，演示完整配置與測試。

一、測試數據準備

-- users 表
CREATE TABLE users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(20) NOT NULL
);
INSERT INTO users VALUES
(1311895262, '張三'),
(1311895263, 'tank張二'),
(1311895264, 'tank張一'),
(1311895265, 'tank張');

-- orders 表
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  create_time DATETIME NOT NULL,
  product_name VARCHAR(20),
  summary TEXT
);
INSERT INTO orders VALUES
(9, 1311895262, '2014-08-01 00:24:54', 'tank is 坦克', '技術總監'),
(10, 1311895263, '2014-08-01 00:24:54', 'tank is 坦克', '技術經理'),
(11, 1311895264, '2014-08-01 00:24:54', 'tank is 坦克', 'DNB經理'),
(12, 1311895265, '2014-08-01 00:24:54', 'tank is 坦克', '運維總監');

二、sphinx.conf 核心配置

source order
{
  type = mysql
  sql_host = localhost
  sql_user = root
  sql_pass =
  sql_db = test
  sql_query_pre = SET NAMES utf8
  # 關鍵：LEFT JOIN 將 username 合併進文檔
  sql_query = \
    SELECT a.id, a.user_id, b.username, \
           UNIX_TIMESTAMP(a.create_time) AS create_time, \
           a.product_name, a.summary \
    FROM orders a LEFT JOIN users b ON a.user_id = b.user_id
  sql_attr_uint = user_id
  sql_field_string = username
  sql_field_string = product_name
  sql_attr_timestamp = create_time
  sql_ranged_throttle = 0
  # sql_query_info 在新版已移除
}

index myorder
{
  source = order
  path = /usr/local/sphinx/var/data/myorder
  docinfo = extern
  mlock = 0
  morphology = none
  min_word_len = 1
  charset_table = ...  # 中文 ngram/charset_table 配置
  ngram_len = 1
  html_strip = 0
}

關鍵點：

• sql_query 中 ON a.user_id = b.user_id 等號兩側**必須有空格**，否則解析報錯
• sql_field_string：將字段同時作為全文檢索字段與屬性返回
• UNIX_TIMESTAMP 將 datetime 轉整數時間戳，配合 sql_attr_timestamp 支援時間範圍過濾

三、構建索引與啟動 searchd

# 1. 構建索引
/usr/local/sphinx/bin/indexer --config /usr/local/sphinx/etc/sphinx.conf --all

# 2. 啟動 searchd（MySQL 協議端口 9306）
/usr/local/sphinx/bin/searchd --config /usr/local/sphinx/etc/sphinx.conf

四、MySQL 協議測試查詢

mysql -h 127.0.0.1 -P 9306
-- 全文匹配「張」
SELECT * FROM myorder WHERE MATCH('張');
-- 結果：4 行，username 含張三/張二/張一/張

-- 精確匹配「張三」
SELECT * FROM myorder WHERE MATCH('張三');
-- 結果：1 行，id=9

返回欄位：id, weight, user_id, username, create_time, product_name，weight 為相關度分數。

五、進階查詢語法

• 布爾模式：MATCH('+張三 -張二') 必含張三、排除張二
• 字段限定：MATCH('@username 張三') 僅搜 username 字段
• 屬性過濾：WHERE user_id = 1311895262 AND MATCH('坦克')
• 排序：ORDER BY create_time DESC、ORDER BY WEIGHT() DESC
• 分頁：LIMIT 0, 20

六、生產環境架構建議

• 主索引 + 增量索引 + 分布式索引（見下文 179671）
• 實時索引（RT Index）替代批量重建，支援 INSERT/REPLACE/DELETE 即時可見
• 監控 searchd 狀態：SHOW META、SHOW STATUS
• 高可用：多 searchd 節點 + HAProxy/VIP，索引文件共享存儲或 rsync 同步