sphinx 增量索引(id)

最新推荐文章于 2021-01-19 02:36:46 发布

被遗忘的区域

最新推荐文章于 2021-01-19 02:36:46 发布

阅读量242

点赞数

西西工作室

本文链接：https://blog.csdn.net/songxi_bo/article/details/108464274

版权

案例1：
配置sphinx.conf

source goods_src
{

type = mysql
sql_host = localhost
sql_user = root
sql_pass = root
sql_db = test
sql_port = 3306 # optional, default is 3306

sql_query_pre = SET NAMES utf8
sql_query_pre = SET SESSION query_cache_type=OFF
sql_query_pre = REPLACE INTO sph_counter SELECT 1, MAX(goods_id) FROM goods

sql_query =
SELECT goods_id,goods_id as gid, UNIX_TIMESTAMP(created_at) AS created_at, title, content
FROM goods WHERE goods_id<=( SELECT max_id FROM sph_counter WHERE id=1 )

sql_attr_uint = gid
sql_attr_timestamp = created_at
}

source goods_delta_src : goods_src
{
sql_query_pre = SET NAMES utf8
sql_query_pre = SET SESSION query_cache_type=OFF
sql_query_pre =
sql_query = SELECT goods_id,goods_id as gid, UNIX_TIMESTAMP(created_at) AS created_at, title, content
FROM goods WHERE goods_id > ( SELECT max_id FROM sph_counter WHERE id=1 )
sql_query_post = UPDATE sph_counter SET max_id=(SELECT MAX(goods_id) FROM goods) where id=1

sql_attr_uint     = gid
sql_attr_timestamp = created_at

}

index goods
{
source = goods_src
path = E:/sphinx-data/data/goods

charset_table = 0…9, A…Z->a…z, _, a…z,U+410…U+42F->U+430…U+44F, U+430…U+44F

ngram_len = 1
ngram_chars = U+3000…U+2FA1F

}
index goods_delta : goods
{
source = goods_delta_src
path = E:/sphinx-data/data/goods_delta

ngram_len = 1
ngram_chars = U+3000…U+2FA1F
}
#############################################################################

indexer settings

#############################################################################

indexer
{
mem_limit = 128M
}

#############################################################################

searchd settings

#############################################################################

searchd
{

listen = 9312
listen = 9306:mysql41
log = E:/sphinx-data/log/searchd.log
query_log = E:/sphinx-data/log/query.log
read_timeout = 5
client_timeout = 300
max_children = 30
persistent_connections_limit = 30
pid_file = E:/sphinx-data/log/searchd.pid
preopen_indexes = 1
unlink_old = 1
max_packet_size = 8M
max_filters = 256
max_filter_values = 4096
max_batch_queries = 32
workers = threads # for RT to work
}

#############################################################################

common settings

#############################################################################

common
{

}
添加索引

indexer.exe goods goods_delta
启动searchd
searched
实时更新

定时任务

indexer.exe --rotate goods_delta

案例2

1.Sphinx 优点
高速索引（接近10M/S）
高速搜索（2-4G文本搜索耗时不到0.1秒）
高可用性（单CPU支持100GB文本，100M文档）
提供相关性排名、分布式搜索、文档摘要（高亮显示）
2.Sphinx 缺点
必须有主键
主键必须是整形
不负责数据存储
配置复杂
3.官网下载最新版本http://sphinxsearch.com/downloads/current/
4.解压进入sphinx/etc/,新建sphinx.conf配置文件

Minimal Sphinx configuration sample (clean, simple, functional)

#主索引data_index
source 主索引data_index
{
type = mysql

sql_host        = localhost #数据库地址
sql_user        = root #数据库账号
sql_pass        = 123456 #数据库密码
sql_db          = table #表名
sql_port        = 3306  # optional, default is 3306

#数据库对应mysql.sock的安装路径
sql_sock        = /var/lib/mysql/mysql.sock

sql_query_pre   = SET NAMES utf8
#sql_query_pre  = SET SESSION query_cache_type=OFF

#创建一个c_counter表来记录增量索引的标记(字段counter_id, max_id)，data_table为数据表
sql_query_pre   = REPLACE INTO c_counter SELECT 1, MAX(id) FROM data_table

sql_query_range = SELECT MIN(id), MAX(id) FROM data_table \
                  WHERE id<=(SELECT max_id FROM c_counter WHERE counter_id=1)#counter_id标识counter_id=1

#区段查询的步进
sql_range_step      = 1000

#分区查询的间隔时间 单位是毫秒
sql_ranged_throttle = 0

############ 需要新增一个config数据表来记录当前sphinx的id，当然也可以找其他方式做新增记录
sql_query           = SELECT * FROM data_table \
                    WHERE id<=(SELECT max_id FROM c_counter WHERE counter_id=1) AND id >= $start AND id <= $end

############ 查询的字段，根据不同的表结构修改
sql_attr_bigint     = id
sql_field_string    = username
sql_field_string    = name
sql_attr_bigint     = starttime
sql_attr_bigint     = endtime
sql_attr_uint       = type
sql_attr_bigint     = value
sql_attr_uint       = system
sql_attr_bigint     = addtime

}

#增量索引
source data_index_add : data_index {
sql_query_pre = SET NAMES utf8

sql_query_range     = SELECT MIN(id), MAX(id) FROM data_table \
                        WHERE id > (SELECT max_id FROM c_counter WHERE counter_id=1)

sql_query           = SELECT * FROM data_table \
                        WHERE id<=(SELECT max_id FROM c_counter WHERE counter_id=1) AND id >= $start AND id <= $end

#后索引查询
sql_query_post_index = set @max_id :=(SELECT max_id FROM c_counter WHERE counter_id=1)
sql_query_post_index = REPLACE INTO c_counter SELECT 2, IF($maxid, $maxid, @max_id)

}

#主索引
index data_index
{
source = data_index
path = /usr/local/sphinx/index/data_index #主索引存储路径

mlock           = 0
morphology      = none
min_word_len    = 1
html_strip      = 1

html_remove_elements = style, script

ngram_len       = 1
ngram_chars     = U+4E00..U+9FBB, U+3400..U+4DB5, U+20000..U+2A6D6, U+FA0E, U+FA0F, U+FA11, U+FA13, U+FA14, U+FA1F, U+FA21, U+FA23, U+FA24, U+FA27, U+FA28, U+FA29, \
U+3105..U+312C, U+31A0..U+31B7, U+3041, U+3043, U+3045, U+3047, U+3049, U+304B, U+304D, U+304F, U+3051, U+3053, U+3055, U+3057, U+3059, U+305B, U+305D, U+305F, U+3061, \
U+3063, U+3066, U+3068, U+306A..U+306F, U+3072, U+3075, U+3078, U+307B, U+307E..U+3083, U+3085, U+3087, U+3089..U+308E, U+3090..U+3093, U+30A1, U+30A3, U+30A5, U+30A7, \
U+30A9, U+30AD, U+30AF, U+30B3, U+30B5, U+30BB, U+30BD, U+30BF, U+30C1, U+30C3, U+30C4, U+30C6, U+30CA, U+30CB, U+30CD, U+30CE, U+30DE, U+30DF, U+30E1, U+30E2, U+30E3, \
U+30E5, U+30E7, U+30EE, U+30F0..U+30F3, U+30F5, U+30F6, U+31F0, U+31F1, U+31F2, U+31F3, U+31F4, U+31F5, U+31F6, U+31F7, U+31F8, U+31F9, U+31FA, U+31FB, U+31FC, U+31FD, \
U+31FE, U+31FF, U+AC00..U+D7A3, U+1100..U+1159, U+1161..U+11A2, U+11A8..U+11F9, U+A000..U+A48C, U+A492..U+A4C6

}

#增量索引
index data_index_add : data_index {
source = data_index_add
path = /usr/local/sphinx/index/data_index_add #增量索引存储路径
}

#分布式索引，利用分布式索引来实现本地同库增量索引和主索引的合并查询
index data_index_master {
type = distributed
local = data_index
local = data_index_add
}

indexer
{
mem_limit = 256M
}

searchd
{
listen = 9312
listen = 9306:mysql41 #Used for SphinxQL

log                 = /usr/local/sphinx/log/searchd.log #日志存储路径
query_log           = /usr/local/sphinx/log/query.log #日志存储路径

read_timeout        = 5
max_children        = 30
pid_file            = /usr/local/sphinx/log/searchd.pid
seamless_rotate     = 1
preopen_indexes     = 1
unlink_old          = 1

max_packet_size     = 8M
max_filters         = 256
max_filter_values   = 4096
max_batch_queries   = 32

workers             = threads #for RT to work

binlog_path         = /usr/local/sphinx/data #存储路径

}
创建配置文件里的索引目录，并设置访问权限
mkdir -p /usr/local/sphinx/var/data
mkdir -p /usr/local/sphinx/var/log
chmod 777 -R /usr/local/sphinx

5.启动/usr/local/src/sphinx-3.1.1/bin/searchd -c /usr/local/src/sphinx-3.1.1/etc/sphinx.conf #端口监听./searchd服务
关闭/usr/local/src/sphinx-3.1.1/bin/searchd -c /usr/local/src/sphinx-3.1.1/etc/sphinx.conf --stop
6.创建索引/usr/local/src/sphinx-3.1.1/bin/indexer -c /usr/local/src/sphinx-3.1.1/etc/sphinx.conf --all --rotate
7.–all 所有文件 --rotate 热加载
8.创建指定索引/usr/local/src/sphinx-3.1.1/bin/indexer -c /usr/local/src/sphinx-3.1.1/etc/sphinx.conf 索引名称 --rotate
合并索引/usr/local/src/sphinx-3.1.1/bin/indexer -c /usr/local/src/sphinx-3.1.1/etc/sphinx.conf --merge 主索引名称增量索引名称 --rotate
9.直接连接sphinx端口mysql -h 127.0.0.1 -P9306，show tables;查看创建的索引