sphinx 多服务器 多库 多表 取源数据
sphinx conf 文件实例:
## 基础参数,从数据库中取数据,数据库是mysql
source cgfinal
{
type = mysql
sql_query_pre = SET CHARACTER SET 'utf8'
sql_query_pre = SET NAMES utf8
sql_query_pre = SET SESSION query_cache_type=OFF
mysql_connect_flags = 32
}
## N台服务器 N个库 N个表
## 第一台服务器参数
source host0:cgfinal
{
sql_host = 127.0.0.1
sql_user = user
sql_pass = pass
sql_port = 3306
}
## 第二台服务器参数
source host1:cgfinal
{
sql_host = 127.0.0.2
sql_user = user
sql_pass = pass
sql_port = 3306
}
## 从第一台服务器中取源
source host0_source:host0
{
## 表结构 有3个整型字段 用户id,学科,成绩。 共N个库 每个库N张表;
## id 为 索引document 默认id 。本身不支持自增,需自行拼写。
sql_query = SELECT CONCAT(@prefix, @id:=@id+1) AS id, uid, class , grade, 'tb' as text FROM table0 as kg \
UNION \
SELECT CONCAT(@prefix, @id:=@id+1) AS id, uid, class , grade, 'tb' as text FROM table1 as kg \
UNION \
SELECT CONCAT(@prefix, @id:=@id+1) AS id, uid, class , grade, 'tb' as text FROM table2 as kg \;
sql_attr_uint = uid
sql_attr_uint = class
sql_attr_uint = grade
}
## 取每个库数据源
source host0_db0_source:host0_source
{
## @prefix @id的意义在于使document id不重复,重复的document id会生成索引时报错。
sql_query_pre = set @prefix := 10
sql_query_pre = set @id := 0
sql_db = db0
}
source host0_db1_source:host0_source
{
sql_query_pre = set @prefix := 11
sql_query_pre = set @id := 0
sql_db = db1
}
source host0_db2_source:host0_source
{
sql_query_pre = set @prefix := 12
sql_query_pre = set @id := 0
sql_db = db2
}
## 从第二台服务器中取源
source db_sns_userbadge_base:host1
{
## 表结构同上。 每个库只有一张表;
sql_query = SELECT CONCAT(@prefix, @id:=@id+1) AS id, uid, class , grade, 'tb' as text FROM table;
sql_attr_uint = uid
sql_attr_uint = class
sql_attr_uint = grade
}
## 取每个库数据源
source host1_db0_source:host1_source
{
##@prefix 变量需给不同值
sql_query_pre = set @prefix := 20
sql_query_pre = set @id := 0
sql_db = db0
}
source host1_db1_source:host1_source
{
sql_query_pre = set @prefix := 21
sql_query_pre = set @id := 0
sql_db = db1
}
source host1_db2_source:host1_source
{
sql_query_pre = set @prefix := 22
sql_query_pre = set @id := 0
sql_db = db2
}
## 综合多个源 建立索引
index example_index:cgfinal_index
{
source = host0_db0_source
source = host0_db1_source
source = host0_db2_source
source = host1_db0_source
source = host1_db1_source
source = host1_db2_source
path = /xx
}
cgfinal_index settings ,indexer settings , searchd settings 就略过了。
生成索引后 可以使用 – -groupsort 参数排序出每个用户总成绩的排行。php 中使用SphinxClient::setGroupBy方法。
其他搜索命令可以查看官方文档: http://sphinxsearch.com/docs/current.html#ref-search
## 基础参数,从数据库中取数据,数据库是mysql
source cgfinal
{
type = mysql
sql_query_pre = SET CHARACTER SET 'utf8'
sql_query_pre = SET NAMES utf8
sql_query_pre = SET SESSION query_cache_type=OFF
mysql_connect_flags = 32
}
## N台服务器 N个库 N个表
## 第一台服务器参数
source host0:cgfinal
{
sql_host = 127.0.0.1
sql_user = user
sql_pass = pass
sql_port = 3306
}
## 第二台服务器参数
source host1:cgfinal
{
sql_host = 127.0.0.2
sql_user = user
sql_pass = pass
sql_port = 3306
}
## 从第一台服务器中取源
source host0_source:host0
{
## 表结构 有3个整型字段 用户id,学科,成绩。 共N个库 每个库N张表;
## id 为 索引document 默认id 。本身不支持自增,需自行拼写。
sql_query = SELECT CONCAT(@prefix, @id:=@id+1) AS id, uid, class , grade, 'tb' as text FROM table0 as kg \
UNION \
SELECT CONCAT(@prefix, @id:=@id+1) AS id, uid, class , grade, 'tb' as text FROM table1 as kg \
UNION \
SELECT CONCAT(@prefix, @id:=@id+1) AS id, uid, class , grade, 'tb' as text FROM table2 as kg \;
sql_attr_uint = uid
sql_attr_uint = class
sql_attr_uint = grade
}
## 取每个库数据源
source host0_db0_source:host0_source
{
## @prefix @id的意义在于使document id不重复,重复的document id会生成索引时报错。
sql_query_pre = set @prefix := 10
sql_query_pre = set @id := 0
sql_db = db0
}
source host0_db1_source:host0_source
{
sql_query_pre = set @prefix := 11
sql_query_pre = set @id := 0
sql_db = db1
}
source host0_db2_source:host0_source
{
sql_query_pre = set @prefix := 12
sql_query_pre = set @id := 0
sql_db = db2
}
## 从第二台服务器中取源
source db_sns_userbadge_base:host1
{
## 表结构同上。 每个库只有一张表;
sql_query = SELECT CONCAT(@prefix, @id:=@id+1) AS id, uid, class , grade, 'tb' as text FROM table;
sql_attr_uint = uid
sql_attr_uint = class
sql_attr_uint = grade
}
## 取每个库数据源
source host1_db0_source:host1_source
{
##@prefix 变量需给不同值
sql_query_pre = set @prefix := 20
sql_query_pre = set @id := 0
sql_db = db0
}
source host1_db1_source:host1_source
{
sql_query_pre = set @prefix := 21
sql_query_pre = set @id := 0
sql_db = db1
}
source host1_db2_source:host1_source
{
sql_query_pre = set @prefix := 22
sql_query_pre = set @id := 0
sql_db = db2
}
## 综合多个源 建立索引
index example_index:cgfinal_index
{
source = host0_db0_source
source = host0_db1_source
source = host0_db2_source
source = host1_db0_source
source = host1_db1_source
source = host1_db2_source
path = /xx
}
cgfinal_index settings ,indexer settings , searchd settings 就略过了。
生成索引后 可以使用 – -groupsort 参数排序出每个用户总成绩的排行。php 中使用SphinxClient::setGroupBy方法。
其他搜索命令可以查看官方文档: http://sphinxsearch.com/docs/current.html#ref-search