使用sphinx索引mysql資料

標籤：

資料庫表如下

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)（詳細配置可參照 sphinx 設定檔全解析）

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

測試，源碼安裝sphinx的php擴充

wget https://pecl.php.net/get/sphinx-1.3.3.tgzphpize && ./configure && make && make install<?php     $s = new SphinxClient;     $s->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)    }  }}

使用sphinx索引mysql資料