使用sphinx索引mysql数据-白红宇

使用sphinx索引mysql数据

阅读量：6818 次

发布时间：2019-06-26

本文共 2571 字，大约阅读时间需要 8 分钟。

数据库表如下

mysql> select * from tb_account;+----+-------+------+| id | name | age |+----+-------+------+| 1 | ciaos | 3 || 2 | stone | 6 |+----+-------+------+

安装sphinx

yum install sphinx libsphinxclient-devel

配置sphinx代替mysql全文索引(/etc/sphinx/sphinx.conf)（详细配置可参照）

source account_src{    type            = mysql    sql_host        = localhost    sql_user        = root    sql_pass        =    sql_db          = test    sql_port        = 3306    sql_query_pre   = SET NAMES utf8    sql_query       = \        SELECT * \        FROM tb_account    #sql_attr_uint      = idx_id    sql_field_string = name    sql_attr_uint = age}index account_idx{    source          = account_src    path            = /var/lib/sphinx/account_src    docinfo         = extern    charset_type    = utf-8}indexer{    mem_limit       = 32M}searchd{        listen                  = 127.0.0.1:9312        listen                  = 9306:mysql41        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        max_matches             = 1000        seamless_rotate         = 1        preopen_indexes         = 1        unlink_old              = 1        workers                 = threads # for RT to work        binlog_path             = /var/lib/sphinx}

创建全量索引

indexer --config /etc/sphinx/sphinx.conf --all

启动后台搜索服务

searchd --config /etc/sphinx/sphinx.conf

可设置crontab定期构建索引

indexer --config /etc/sphinx/sphinx.conf --rotate account_idx

测试（自带search工具已不可用，可参照），所以需要源码安装sphinx的php扩展

wget https://pecl.php.net/get/sphinx-1.3.3.tgzphpize && ./configure && make && make install
     setServer("localhost", 9312);     $s->setMatchMode(SPH_MATCH_ANY);     $s->setMaxQueryTime(3);     $result = $s->query("stone");     var_dump($result);?>

运行php test.php

array(10) {  ["error"]=>  string(0) ""  ["warning"]=>  string(0) ""  ["status"]=>  int(0)  ["fields"]=>  array(1) {    [0]=>    string(4) "name"  }  ["attrs"]=>  array(2) {    ["name"]=>    int(7)    ["age"]=>    int(1)  }  ["matches"]=>  array(1) {    [2]=>    array(2) {      ["weight"]=>      int(1)      ["attrs"]=>      array(2) {        ["name"]=>        string(5) "stone"        ["age"]=>        int(6)      }    }  }  ["total"]=>  int(1)  ["total_found"]=>  int(1)  ["time"]=>  float(0)  ["words"]=>  array(1) {    ["stone"]=>    array(2) {      ["docs"]=>      int(1)      ["hits"]=>      int(1)    }  }}

转载于:https://www.cnblogs.com/ciaos/p/5090019.html

你可能感兴趣的文章

北斗有 35 颗卫星，而 GPS 有 24 颗卫星，为什么二者数量不同？