sphinx with mysql_【Sphinx】MySQL+Sphinx 全文检索的使用和测试

导读：最近在线上由于某些不可名状的原因需要在数据库中过滤一些词语,所以线上比较多的语句都是 select c1,c2 fron tb1 where c1 like '%name%'类似与这个样子的模糊查询。开始想到了全文索引来进行分词，但是又想到这些都是线上数据库，并且表中的数据都是几千万行的数据，在线online ddl 或者说使用第三方的工具的话对于在修改期间会阻塞线上的DML操作,并且针对全文索引的维护也需要消耗一定的资源，所以暂时打消了这个想法。在和开发进行讨论并且结合实际业务，最后还是打算选型第三方的全文检索工具，在这里选择了全文检索工具sphinx。

sphinx的安装和测试:sphinx的安装yum install sphinx -y

2. 配置文件#

# Minimal Sphinx configuration sample (clean, simple, functional)

#

source sbtest_sbtest1

{

type= mysql

sql_host= localhost

sql_user= root

sql_pass= redhat

sql_db= sbtest

sql_port= 3306# optional, default is 3306

sql_query= \

SELECT userid as  id, \

k, \

c, \

pad \

FROM sbtest1 \

where c like '%03679578678%' \

sql_attr_uint   = k  #数据类型int或者timestamp

sql_field_string         = c  #数据类型text或者string

sql_attr_string         = pad #数据类型string

}

index sbtest1

{

source= sbtest_sbtest1

path= /var/lib/sphinx/sbtest1 #索引文件的路径

}

index testrt

{

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 # for RT to work

binlog_path= /var/lib/sphinx/

}

注意：在配置文件中需要注意的是:

a. 在sql_query参数后面是sql语句,但是这个sql语句的第一个字段必须是id字段，在后面的参数类型定义的时候不需要定义,否则的话sphinx是不能识别这个属性的。

b. 在属性的定义的时候必须包含一个sql_filed_string类型的字段，否则sphinx认为你没有必须使用全文检索这个功能。

3.创建索引indexer sbtest1

4.启动searchd 服务service searchd start

5.查看searchd服务状态[root@TiDB-node2 data]# searchd --status

Sphinx 2.3.2-id64-beta (4409612)

Copyright (c) 2001-2016, Andrew Aksyonoff

Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/etc/sphinx/sphinx.conf'...

searchd status

--------------

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-node2 data]# mysql -uroot -predhat -h127.0.0.1 -P9306 -e "select id,k,c,pad from sbtest1 where match('03679578678')"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+

| 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/env python

#coding:utf-8

import pymysql

con = pymysql.connect(host='127.0.0.1', port=9306, user="", passwd="", db="")

with con.cursor(pymysql.cursors.DictCursor) as cur:

cur.execute("select * from sbtest1 where match('%03679578678%')")

print(cur.fetchall())

[root@TiDB-node2 ~]# python sphinx.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@mysqldb 18:06:  [sbtest]> show create table sbtest1 \G

*************************** 1. row ***************************

Table: sbtest1

Create Table: CREATE TABLE `sbtest1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`k` int(11) NOT NULL DEFAULT '0',

`c` char(120) NOT NULL DEFAULT '',

`pad` char(60) NOT NULL DEFAULT '',

PRIMARY KEY (`id`),

KEY `k_1` (`k`)

) ENGINE=InnoDB AUTO_INCREMENT=25000000 DEFAULT CHARSET=utf8mb4

测试环境中表sbtest1的数据有2500万行,测试sql是select * from sbtest1 where c like '%03679578678%'。在MySQL执行这个sql的时候由于使用不了索引只能进行全表扫描，花费了将近4分钟，但是在sphinx创建索引的时候也花费了70多秒的样子。在索引创建成功之后基本上面执行python脚本或者使用mysql客户端获取得到的花费基本是在毫秒级别的。性能提升了很多，并且sphinx和MySQL的在创建索引成功之后之间就没有链接了。并不会影响MySQL的操作，sphinx可以独立部署到一台服务器上面。

9.更新索引文件:增量更新索引文件indexer --rotate test1