coreseek sphinx 创建表和索引

前面说了，coreseek sphinx mmseg mysql等的安装，下面说一下怎么使用。

一,coreseek sphinx启动后，会多出一个端口，并且可以像mysql一样登录，但不是登录mysql

[root@localhost tank]# mysql -h 127.0.0.1 -P 9306      //不是真的连接mysql，而连接了sphinx index
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 1.11-id64-dev (r2540)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from tank_test where match('坦克') ;   //这种写法，根原装的sphinx不一样
+------+--------+------------+------+
| id   | weight | user_id    | u_id |
+------+--------+------------+------+
|    3 |   2230 | 1311895260 |   62 |
|    5 |   2230 | 1311895260 |   33 |
|    4 |   1304 | 1311895262 |    0 |
|    6 |   1304 | 1311895262 |   34 |
+------+--------+------------+------+
4 rows in set (0.00 sec)

mysql> show META;     //上次检索的信息
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.000 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
+---------------+-------+
6 rows in set (0.00 sec)

mysql> show tables;    //这里的表其实不是真表，也不是create table创建出来的，是sphinx索引
+--------------+-------------+
| Index        | Type        |
+--------------+-------------+
| dist1        | distributed |
| myorder      | local       |
| rt           | rt          |
| tank_test    | rt          |
| test1        | local       |
| test1stemmed | local       |
+--------------+-------------+
6 rows in set (0.00 sec)

二，创建sphinx索引

1,修改/usr/local/sphinx/etc/sphinx.conf

# vim /usr/local/sphinx/etc/sphinx.conf   //添加以下内容

index tank_test
{
 type            = rt
 path            = /usr/local/sphinx/var/data/rt
 charset_dictpath     = /usr/local/mmseg3/etc/
 charset_type         = zh_cn.utf-8
 ngram_len            = 0
 rt_field        = name
 rt_field        = title
 rt_field        = sub_title
 rt_attr_uint        = user_id
 rt_attr_uint        = uid
}

在这里要注意，rt_field是检索字段，rt_attr_uint是返回字段

2,重启sphinx

# pkill -9 searchd

# /usr/local/sphinx/bin/indexer --config /usr/local/sphinx/etc/sphinx.conf --all
# /usr/local/sphinx/bin/searchd --config /usr/local/sphinx/etc/sphinx.conf

3，插入数据，并查看

mysql> show tables;
+--------------+-------------+
| Index        | Type        |
+--------------+-------------+
| dist1        | distributed |
| rt           | rt          |
| tank_test    | rt          |      //新增加的索引
| test1        | local       |
| test1stemmed | local       |
+--------------+-------------+
5 rows in set (0.00 sec)

mysql> desc tank_test;
+-----------+---------+
| Field     | Type    |
+-----------+---------+
| id        | bigint  |
| name      | field   |
| title     | field   |
| sub_title | field   |
| user_id   | integer |
| u_id      | integer |
+-----------+---------+
6 rows in set (0.00 sec)

mysql> insert into tank_test values (3,'坦克','tank is 坦克','技术总监',1311895260,33);

mysql> insert into tank_test values (4,'tank张','tank is 坦克','技术总监',1311895262,34);

mysql> select * from tank_test where match('坦克');    //匹配搜索的字段是rt_field
+------+--------+------------+------+
| id   | weight | user_id    | u_id |                 //返回的字段是rt_attr_uint
+------+--------+------------+------+
|    3 |   2230 | 1311895260 |   33 |
|    4 |   1304 | 1311895262 |   34 |
+------+--------+------------+------+
2 rows in set (0.00 sec)

id和weight是系统自带的返回字段

到这儿索引就创建好了，show tables的时候是可以看新建的tank_test，用phpmyadmin或者其他mysql数据库连接工具根本看不到，原因是他根本不是真实的表。sphinx到底能不能用真实的表呢？

三，创建表，并添加索引

1,创建真实的表,插入数据

CREATE TABLE IF NOT EXISTS `orders` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user_id` int(11) NOT NULL ,
 `username` varchar(20) NOT NULL,
 `create_time` datetime NOT NULL,
 `product_name` varchar(20) NOT NULL,
 `summary` text NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO  `orders` (`user_id` ,`username` ,`create_time` ,`product_name` ,`summary`) VALUES
('1311895262','张三','2014-08-01 00:24:54','tank is 坦克','技术总监'),
('1311895263','tank张二','2014-08-01 00:24:54','tank is 坦克','技术经理'),
('1311895264','tank张一','2014-08-01 00:24:54','tank is 坦克','DNB经理'),
('1311895265','tank张','2014-08-01 00:24:54','tank is 坦克','运维总监');

在这里要注意，是连接mysql的3306端口，不是连接coreseek sphinx的9306

2，修改/usr/local/sphinx/etc/sphinx.conf，添加以下内容

source order
{
 type            = mysql
 sql_host        = localhost
 sql_user        = root
 sql_pass        =
 sql_db            = test
 sql_query_pre        = SET NAMES utf8
 sql_query        = \
 SELECT id, user_id, username, UNIX_TIMESTAMP(create_time) AS create_time, product_name, summary  \
 FROM orders
 sql_attr_uint        = user_id
 sql_attr_timestamp    = create_time
 sql_ranged_throttle    = 0
 sql_query_info    = SELECT * FROM orders WHERE id=$id
}

index myorder
{
 source            = order
 path            = /usr/local/sphinx/var/data/myorder
 docinfo        = extern
 mlock            = 0
 morphology        = none
 min_word_len        = 1
 charset_dictpath    = /usr/local/mmseg3/etc/
 charset_type        = zh_cn.utf-8
 ngram_len            = 0
 html_strip        = 0
}

3,重启sphinx

# pkill -9 searchd

# /usr/local/sphinx/bin/indexer --config /usr/local/sphinx/etc/sphinx.conf --all
# /usr/local/sphinx/bin/searchd --config /usr/local/sphinx/etc/sphinx.conf

4，切换到9306，检索测试

mysql> show tables;
+--------------+-------------+
| Index        | Type        |
+--------------+-------------+
| dist1        | distributed |
| myorder      | local       |
| rt           | rt          |
| tank_test    | rt          |
| test1        | local       |
| test1stemmed | local       |
+--------------+-------------+
6 rows in set (0.00 sec)

mysql> desc myorder;
+--------------+-----------+
| Field        | Type      |
+--------------+-----------+
| id           | bigint    |
| username     | field     |
| product_name | field     |
| summary      | field     |
| user_id      | integer   |
| create_time  | timestamp |
+--------------+-----------+
6 rows in set (0.00 sec)

mysql> select * from myorder where match('坦克');
+------+--------+------------+-------------+
| id   | weight | user_id    | create_time |
+------+--------+------------+-------------+
|    5 |   1304 | 1311895262 |  1407081600 |
|    6 |   1304 | 1311895263 |  1406823894 |
|    7 |   1304 | 1311895264 |  1406823894 |
|    8 |   1304 | 1311895265 |  1406823894 |
+------+--------+------------+-------------+
4 rows in set (0.00 sec)