导读：最近在线上由于某些不可名状的原因需要在数据库中过滤一些词语,所以线上比较多的语句都是 select c1,c2 fron tb1 where c1 like '%name%'类似与这个样子的模糊查询。开始想到了全文索引来进行分词，但是又想到这些都是线上数据库，并且表中的数据都是几千万行的数据，在线online ddl 或者说使用第三方的工具的话对于在修改期间会阻塞线上的DML操作,并且针对全文索引的维护也需要消耗一定的资源，所以暂时打消了这个想法。在和开发进行讨论并且结合实际业务，最后还是打算选型第三方的全文检索工具，在这里选择了全文检索工具sphinx。
sphinx的安装和测试:
sphinx的安装
yuminstallsphinx-y
2. 配置文件
#
#MinimalSphinxconfigurationsample(clean,simple,functional)
#
sourcesbtest_sbtest1
{
type=mysql
sql_host=localhost
sql_user=root
sql_pass=redhat
sql_db=sbtest
sql_port=3306#optional,defaultis3306
sql_query=\
SELECTuseridasid,\
k,\
c,\
pad\
FROMsbtest1\
whereclike'%03679578678%'\
sql_attr_uint=k#数据类型int或者timestamp
sql_field_string=c#数据类型text或者string
sql_attr_string=pad#数据类型string
}
indexsbtest1
{
source=sbtest_sbtest1
path=/var/lib/sphinx/sbtest1#索引文件的路径
}
indextestrt
{
type=rt
rt_mem_limit=128M
path=/var/lib/sphinx/testrt
rt_field=title
rt_field=content
rt_attr_uint=gid
}
indexer
{
mem_limit=128M
}
searchd
{
listen=9312
listen=9306:mysql41#对外提供服务的端口,类似与mysql中的3306端口
log=/var/log/sphinx/searchd.log
query_log=/var/log/sphinx/query.log
read_timeout=5
max_children=30
pid_file=/var/run/sphinx/searchd.pid
seamless_rotate=1
preopen_indexes=1
unlink_old=1
workers=threads#forRTtowork
binlog_path=/var/lib/sphinx/
}
注意：在配置文件中需要注意的是:
a. 在sql_query参数后面是sql语句,但是这个sql语句的第一个字段必须是id字段，在后面的参数类型定义的时候不需要定义,否则的话sphinx是不能识别这个属性的。
b. 在属性的定义的时候必须包含一个sql_filed_string类型的字段，否则sphinx认为你没有必须使用全文检索这个功能。
3.创建索引
indexersbtest1
4.启动searchd 服务
servicesearchdstart
5.查看searchd服务状态
[root@TiDB-node2data]#searchd--status
Sphinx2.3.2-id64-beta(4409612)
Copyright(c)2001-,AndrewAksyonoff
Copyright(c)-,SphinxTechnologiesInc()
usingconfigfile'/etc/sphinx/sphinx.conf'...
searchdstatus
--------------
uptime:80403
connections:11
maxed_out:0
command_search:7
command_excerpt:0
command_update:0
command_delete:0
command_keywords:0
command_persist:0
command_status:1
command_flushattrs:0
agent_connect:0
agent_retry:0
queries:7
dist_queries:0
query_wall:0.010
query_cpu:OFF
dist_wall:0.000
dist_local:0.000
dist_wait:0.000
query_reads:OFF
query_readkb:OFF
query_readtime:OFF
avg_query_wall:0.001
avg_query_cpu:OFF
avg_dist_wall:0.000
avg_dist_local:0.000
avg_dist_wait:0.000
avg_query_reads:OFF
avg_query_readkb:OFF
avg_query_readtime:OFF
qcache_max_bytes:16777216
qcache_thresh_msec:3000
qcache_ttl_sec:60
qcache_cached_queries:0
qcache_used_bytes:0
qcache_hits:0
6.测试使用：mysql 客户端
[root@TiDB-node2data]#mysql-uroot-predhat-h127.0.0.1-P9306-e"selectid,k,c,padfromsbtest1wherematch('03679578678')"
mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.
+------+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|id|k|c|pad|
+------+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|1|15324329|64733237507-56788752464-03679578678-53343296505-31167207241-10603050901-64148678956-82738243332-73912678420-24566188603|78326593386-76411244360-77646817949-14319822046-41963083322|
+------+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
7.python测试使用
#!/usr/bin/envpython
#coding:utf-8
importpymysql
con=pymysql.connect(host='127.0.0.1',port=9306,user="",passwd="",db="")
withcon.cursor(pymysql.cursors.DictCursor)ascur:
cur.execute("select*fromsbtest1wherematch('%03679578678%')")
print(cur.fetchall())
[root@TiDB-node2~]#pythonsphinx.py
[{u'c':'64733237507-56788752464-03679578678-53343296505-31167207241-10603050901-64148678956-82738243332-73912678420-24566188603',u'k':15324329,u'pad':'78326593386-76411244360-77646817949-14319822046-41963083322',u'id':1}]
8.表结构：
root@mysqldb18:06:[sbtest]>showcreatetablesbtest1\G
***************************1.row***************************
Table:sbtest1
CreateTable:CREATETABLE`sbtest1`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`k`int(11)NOTNULLDEFAULT'0',
`c`char(120)NOTNULLDEFAULT'',
`pad`char(60)NOTNULLDEFAULT'',
PRIMARYKEY(`id`),
KEY`k_1`(`k`)
)ENGINE=InnoDBAUTO_INCREMENT=25000000DEFAULTCHARSET=utf8mb4
测试环境中表sbtest1的数据有2500万行,测试sql是select * from sbtest1 where c like '%03679578678%'。在MySQL执行这个sql的时候由于使用不了索引只能进行全表扫描，花费了将近4分钟，但是在sphinx创建索引的时候也花费了70多秒的样子。在索引创建成功之后基本上面执行python脚本或者使用mysql客户端获取得到的花费基本是在毫秒级别的。性能提升了很多，并且sphinx和MySQL的在创建索引成功之后之间就没有链接了。并不会影响MySQL的操作，sphinx可以独立部署到一台服务器上面。
9.更新索引文件:增量更新索引文件
indexer--rotatetest1
