Sphinx+MySQL5.1x+SphinxSE+mmseg


一.不停止mysql的情况下安装SphinxSE

1.确定mysql版本,下载对应源码包

此处下载5.1.69的mysql源码包

#wget ftp://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-5.1/mysql-5.1.69.tar.gz 

解压


2.下载sphinx:

#wget http://sphinxsearch.com/downloads/sphinx-0.9.9.tar.gz 

解压


3.拷贝mysqlse到mysql5.1.69下的storage/sphinx

#cp -r sphinx-0.9.9/mysqlse/ mysql-5.1.69/storage/sphinx


4.编译

#cd mysql-5.1.69

#sh BUILD/autorun.sh

#./configure

#make


5.mysql插入sphinx引擎

#cd ./mysql-5.1.69

#cd ./storage/sphinx/.libs

#cp ha_sphinx.* /usr/lib64/mysql/plugin


更改所有者

#chown mysql.mysql /usr/lib64/mysql/plugin/*


登录mysql,插入引擎

#mysql -u root -p -h localhost 

#mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';


检查引擎是否安装

#mysql> show engines;


如果出现sphinx引擎,则表明已正常安装


二.安装Coreseek,使其支持中文索引及检索

1.安装autoconf


2.下载coreseek3.2.14.tar.gz,并解压缩


3.安装mmseg分词

#cd coreseek-3.2.14

#./bootstrap

#./configure --prefix=/usr/local/mmseg3

#make

#make install


4.安装csft

#cd ../csft-3.2.14/

#sh buildconf.sh

#./configure --prefix=/usr/local/coreseek  --without-unixodbc --with-mmseg --with-mmseg-includes=/usr/local/mmseg3/include/mmseg/ --with-mmseg-libs=/usr/local/mmseg3/lib/ --with-mysql


===========================================================

##如果出现错误提示:“ERROR: cannot find MySQL include files.......To disable MySQL support, use --without-mysql option.“,可按照如下方法处理:

##请找到头文件mysql.h所在的目录,一般是/usr/local/mysql/include,请替换为实际的

##请找到库文件libmysqlclient.a所在的目录,一般是/usr/local/mysql/lib,请替换为实际的

##configure参数加上:--with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib,执行后,重新编译安装

============================================================


#make

#make install


三.配置并测试

1.进入coreseek目录

#cd /usr/local/coreseek/etc

#ls

可以看到三个文件

example.sql  sphinx.conf.dist  sphinx-min.conf.dist


#cp sphinx.conf.dist sphinx.conf


2.配置sphinx.conf

source src1

{


     type                   = mysql


     sql_host               = localhost


     sql_user               = root


     sql_pass               = 123456


     sql_db                 = test


     sql_port               = 3306   # optional, default is 3306


     sql_sock                              = /var/lib/mysql/mysql.sock


     sql_query_pre = SET NAMES utf8


     sql_query              = \

         SELECT id, group_id, UNIX_TIMESTAMP(date_added) AS date_added, title, content \

         FROM documents


     sql_attr_uint          = group_id


     sql_attr_timestamp     = date_added


     sql_query_info              = SELECT * FROM documents WHERE id=$id


}


index test1


{


     source                      = src1


     path                   = /usr/local/coreseek/var/data/test1


     docinfo                     = extern


     charset_type           = zh_cn.utf-8


     mlock              = 0


     morphology         = none


     min_word_len       = 1


     html_strip         = 0


     charset_dictpath       = /usr/local/mmseg3/etc/


     ngram_len                    = 0


}



indexer


{


     mem_limit              = 32M


}



searchd


{


     port                   = 9312


     log                    = /usr/local/coreseek/var/log/searchd.log


     query_log              = /usr/local/coreseek/var/log/query.log


     read_timeout           = 5


     max_children           = 30


     pid_file               = /usr/local/coreseek/var/log/searchd.pid


     max_matches                 = 1000


     seamless_rotate             = 1


     preopen_indexes             = 0


     unlink_old                  = 1


}



3.创建test数据库,并建立表documents,sql语句如下


DROP TABLE IF EXISTS test.documents;


CREATE TABLE test.documents

(

        id                      INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,

        group_id        INTEGER NOT NULL,

        group_id2       INTEGER NOT NULL,

        date_added      DATETIME NOT NULL,

        title           VARCHAR(255) NOT NULL,

        content         TEXT NOT NULL

);


REPLACE INTO test.documents ( id, group_id, group_id2, date_added, title, content ) VALUES

        ( 1, 1, 5, NOW(), 'test one', 'this is my test document number one. also checking search within phrases.' ),

        ( 2, 1, 6, NOW(), 'test two', 'this is my test document number two' ),

        ( 3, 2, 7, NOW(), 'another doc', 'this is another group' ),

        ( 4, 2, 8, NOW(), 'doc number four', 'this is to test groups' );


DROP TABLE IF EXISTS test.tags;


CREATE TABLE test.tags

(

        docid INTEGER NOT NULL,

        tagid INTEGER NOT NULL,

        UNIQUE(docid,tagid)

);


INSERT INTO test.tags VALUES

        (1,1), (1,3), (1,5), (1,7),

        (2,6), (2,4), (2,2),

        (3,15),

        (4,7), (4,40);



4.生成索引

#/usr/local/coreseek/bin/indexer -c /usr/local/coreseek/etc/sphinx.conf --all

其中参数--all表示生成所有索引


当然也可以是索引的名字例如:#/usr/local/coreseek/bin/indexer -c /usr/local/coreseek/etc/sphinx.conf test1

执行后可以在/usr/local/coreseek/var/data目录中看到多出一些文件,是以索引名为文件名的不同的扩展名的文件


在不启动sphinx的情况下即可测试命令:


#/usr/local/coreseek/bin/search -c /usr/local/coreseek/etc/sphinx.conf number

 

#/usr/local/coreseek/bin/search -c /usr/local/coreseek/etc/sphinx.conf number --filter group_id 2


限定group_id 为2 返回一条记录


我们插入几条数据来测试中文

set names utf8  之前一定要设置字符集


INSERT INTO `test`.`documents` (


`id` ,


`group_id` ,


`group_id2` ,


`date_added` ,


`title` ,


`content`


)


VALUES (


NULL , '2', '3', '2011-02-01 00:37:12', '研究生的故事', '研究生自主创业'


), (


NULL , '1', '1', '2011-01-28 00:38:22', '研究', '为了创业而研究生命科学'


);


插入数据后需要重新索引

#/usr/local/coreseek/bin/indexer -c /usr/local/coreseek/etc/sphinx.conf test1


进行测试

#/usr/local/coreseek/bin/search -c /usr/local/coreseek/etc/sphinx.conf 研究生创业




四.实际使用

1.作为守护进程启动

#/usr/local/coreseek/bin/searchd -c /usr/local/coreseek/etc/sphinx.conf


2.建立一个sphinx引擎的表

CREATE TABLE t1


(


    id          INTEGER UNSIGNED NOT NULL,


    weight      INTEGER NOT NULL,


    query       VARCHAR(3072) NOT NULL,


    group_id    INTEGER,


    INDEX(query)


) ENGINE=SPHINX CONNECTION="sphinx://localhost:9312/test1";


搜索表前三列的类型必须是INTEGER,INTEGER和VARCHAR,这三列分别对应文档ID,匹配权值和搜索查询。查询列必须被索引,其他列必须无索引。列的名字会被忽略,所以可以任意命名,参数CONNECTION来指定用这个表搜索时的默认搜索主机、端口号和索引,语法格式:CONNECTION="sphinx://HOST:PORT/INDEXNAME"。


执行SQL语句 select d.id,d.title,d.content from t1 join documents as d on t1.id = d.id and t1.query = '研究生创业';


即可查询结果


3.在不停止searchd的情况下主索引

插入数据后创建主索引

#/usr/local/coreseek/bin/indexer -c /usr/local/coreseek/etc/sphinx.conf test1 --rotate


对于实际应用,应该是主索引+增量索引





posted @ 2013-10-18 18:57  rorshach  阅读(243)  评论(0编辑  收藏  举报