Home > Database > Mysql Tutorial > sphinx 增量索引 分布式索引 实例

sphinx 增量索引 分布式索引 实例

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Release: 2016-06-07 16:41:12
Original
1287 people have browsed it

增量索引,其实就是增加的内容,例如:存款有100块,今天挣了10块,这10块就是增量了 分布式索引,可以这样理解,想开公司钱不够,需要向很多人借钱。也就是把大家钱集中一起使用。 一,测试表和数据 mysql desc sph_counter;+------------+---------+------

增量索引,其实就是增加的内容,例如:存款有100块,今天挣了10块,这10块就是增量了

分布式索引,可以这样理解,想开公司钱不够,需要向很多人借钱。也就是把大家钱集中一起使用。

一,测试表和数据

mysql> desc sph_counter;
+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| counter_id | int(11) | NO | PRI | NULL | |
| max_doc_id | int(11) | NO | | NULL | |
+------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc orders;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | | NULL | |
| create_time | datetime | NO | | NULL | |
| product_name | varchar(20) | NO | | NULL | |
| summary | text | NO | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> select * from orders;
+----+------------+---------------------+----------------+--------------+
| id | user_id | create_time | product_name | summary |
+----+------------+---------------------+----------------+--------------+
| 9 | 1311895262 | 2014-08-01 00:24:54 | tank is 坦克 | 技术总监 |
| 10 | 1311895263 | 2014-08-01 00:24:54 | tank is 坦克 | 技术经理 |
| 11 | 1311895264 | 2014-08-01 00:24:54 | tank is 坦克 | DNB经理 |
| 12 | 1311895265 | 2014-08-01 00:24:54 | tank is 坦克 | 运维总监 |
+----+------------+---------------------+----------------+--------------+
4 rows in set (0.00 sec)
mysql> desc users;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> select * from users;
+------------+------------+
| user_id | username |
+------------+------------+
| 1311895262 | 张三 |
| 1311895263 | tank张二 |
| 1311895264 | tank张一 |
| 1311895265 | tank张 |
+------------+------------+
4 rows in set (0.00 sec)
Copy after login

二,sphinx.conf配置

source myorder
{
 type = mysql
 sql_host = localhost
 sql_user = root
 sql_pass =
 sql_db = test
 sql_query_pre = SET NAMES utf8
 sql_query_pre = SET SESSION query_cache_type=OFF
 sql_query_pre = REPLACE INTO sph_counter SELECT 1, MAX(id) FROM orders
 sql_query = \
 SELECT a.id, a.user_id,b.username, UNIX_TIMESTAMP(a.create_time) AS create_time, a.product_name, a.summary \
 FROM orders a left join users b on a.user_id = b.user_id
 sql_attr_uint = user_id
 sql_field_string = username
 sql_field_string = product_name
 sql_attr_timestamp = create_time
 sql_ranged_throttle = 0
 #sql_query_info = SELECT * FROM orders WHERE id=$id
}
source moreorder : myorder
{
 sql_query_pre = SET NAMES utf8
 sql_query_pre = SET SESSION query_cache_type=OFF
 sql_query = \
 SELECT a.id, a.user_id,b.username, UNIX_TIMESTAMP(a.create_time) AS create_time, a.product_name, a.summary \
 FROM orders a left join users b on a.user_id = b.user_id where a.id > ( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 )
 sql_attr_uint = user_id
}
index myorder   //主索引
{
 source = myorder
 path = /usr/local/sphinx2/var/data/myorder
 docinfo = extern
 mlock = 0
 morphology = none
 min_word_len = 1
 #charset_type = zh_cn.utf-8
 html_strip = 1
 charset_table = U+FF10..U+FF19->0..9, 0..9, U+FF41..U+FF5A->a..z, U+FF21..U+FF3A->a..z,A..Z->a..z, a..z, U+0149, U+017F, U+0138, U+00DF, U+00FF, U+00C0..U+00D6->U+00E0..U+00F6,U+00E0..U+00F6, U+00D8..U+00DE->U+00F8..U+00FE, U+00F8..U+00FE, U+0100->U+0101, U+0101,U+0102->U+0103, U+0103, U+0104->U+0105, U+0105, U+0106->U+0107, U+0107, U+0108->U+0109,U+0109, U+010A->U+010B, U+010B, U+010C->U+010D, U+010D, U+010E->U+010F, U+010F,U+0110->U+0111, U+0111, U+0112->U+0113, U+0113, U+0114->U+0115, U+0115, U+0116->U+0117,U+0117, U+0118->U+0119, U+0119, U+011A->U+011B, U+011B, U+011C->U+011D, U+011D,U+011E->U+011F, U+011F, U+0130->U+0131, U+0131, U+0132->U+0133, U+0133, U+0134->U+0135,U+0135, U+0136->U+0137, U+0137, U+0139->U+013A, U+013A, U+013B->U+013C, U+013C,U+013D->U+013E, U+013E, U+013F->U+0140, U+0140, U+0141->U+0142, U+0142, U+0143->U+0144,U+0144, U+0145->U+0146, U+0146, U+0147->U+0148, U+0148, U+014A->U+014B, U+014B,U+014C->U+014D, U+014D, U+014E->U+014F, U+014F, U+0150->U+0151, U+0151, U+0152->U+0153,U+0153, U+0154->U+0155, U+0155, U+0156->U+0157, U+0157, U+0158->U+0159, U+0159,U+015A->U+015B, U+015B, U+015C->U+015D, U+015D, U+015E->U+015F, U+015F, U+0160->U+0161,U+0161, U+0162->U+0163, U+0163, U+0164->U+0165, U+0165, U+0166->U+0167, U+0167,U+0168->U+0169, U+0169, U+016A->U+016B, U+016B, U+016C->U+016D, U+016D, U+016E->U+016F,U+016F, U+0170->U+0171, U+0171, U+0172->U+0173, U+0173, U+0174->U+0175, U+0175,U+0176->U+0177, U+0177, U+0178->U+00FF, U+00FF, U+0179->U+017A, U+017A, U+017B->U+017C,U+017C, U+017D->U+017E, U+017E, U+0410..U+042F->U+0430..U+044F, U+0430..U+044F,U+05D0..U+05EA, U+0531..U+0556->U+0561..U+0586, U+0561..U+0587, U+0621..U+063A, U+01B9,U+01BF, U+0640..U+064A, U+0660..U+0669, U+066E, U+066F, U+0671..U+06D3, U+06F0..U+06FF,U+0904..U+0939, U+0958..U+095F, U+0960..U+0963, U+0966..U+096F, U+097B..U+097F,U+0985..U+09B9, U+09CE, U+09DC..U+09E3, U+09E6..U+09EF, U+0A05..U+0A39, U+0A59..U+0A5E,U+0A66..U+0A6F, U+0A85..U+0AB9, U+0AE0..U+0AE3, U+0AE6..U+0AEF, U+0B05..U+0B39,U+0B5C..U+0B61, U+0B66..U+0B6F, U+0B71, U+0B85..U+0BB9, U+0BE6..U+0BF2, U+0C05..U+0C39,U+0C66..U+0C6F, U+0C85..U+0CB9, U+0CDE..U+0CE3, U+0CE6..U+0CEF, U+0D05..U+0D39, U+0D60,U+0D61, U+0D66..U+0D6F, U+0D85..U+0DC6, U+1900..U+1938, U+1946..U+194F, U+A800..U+A805,U+A807..U+A822, U+0386->U+03B1, U+03AC->U+03B1, U+0388->U+03B5, U+03AD->U+03B5,U+0389->U+03B7, U+03AE->U+03B7, U+038A->U+03B9, U+0390->U+03B9, U+03AA->U+03B9,U+03AF->U+03B9, U+03CA->U+03B9, U+038C->U+03BF, U+03CC->U+03BF, U+038E->U+03C5,U+03AB->U+03C5, U+03B0->U+03C5, U+03CB->U+03C5, U+03CD->U+03C5, U+038F->U+03C9,U+03CE->U+03C9, U+03C2->U+03C3, U+0391..U+03A1->U+03B1..U+03C1,U+03A3..U+03A9->U+03C3..U+03C9, U+03B1..U+03C1, U+03C3..U+03C9, U+0E01..U+0E2E,U+0E30..U+0E3A, U+0E40..U+0E45, U+0E47, U+0E50..U+0E59, U+A000..U+A48F, U+4E00..U+9FBF,U+3400..U+4DBF, U+20000..U+2A6DF, U+F900..U+FAFF, U+2F800..U+2FA1F, U+2E80..U+2EFF,U+2F00..U+2FDF, U+3100..U+312F, U+31A0..U+31BF, U+3040..U+309F, U+30A0..U+30FF,U+31F0..U+31FF, U+AC00..U+D7AF, U+1100..U+11FF, U+3130..U+318F, U+A000..U+A48F,U+A490..U+A4CF
 ngram_len = 1
 ngram_chars = U+4E00..U+9FBF, U+3400..U+4DBF, U+20000..U+2A6DF, U+F900..U+FAFF,U+2F800..U+2FA1F, U+2E80..U+2EFF, U+2F00..U+2FDF, U+3100..U+312F, U+31A0..U+31BF,U+3040..U+309F, U+30A0..U+30FF,U+31F0..U+31FF, U+AC00..U+D7AF, U+1100..U+11FF,U+3130..U+318F, U+A000..U+A48F, U+A490..U+A4CF
}
index moreorder : myorder   //增量索引
{
 source = moreorder
 path = /usr/local/sphinx2/var/data/moreorder
 docinfo = extern
 mlock = 0
 morphology = none
 min_word_len = 1
 #charset_type = zh_cn.utf-8
 html_strip = 1
 charset_table = U+FF10..U+FF19->0..9, 0..9, U+FF41..U+FF5A->a..z, U+FF21..U+FF3A->a..z,A..Z->a..z, a..z, U+0149, U+017F, U+0138, U+00DF, U+00FF, U+00C0..U+00D6->U+00E0..U+00F6,U+00E0..U+00F6, U+00D8..U+00DE->U+00F8..U+00FE, U+00F8..U+00FE, U+0100->U+0101, U+0101,U+0102->U+0103, U+0103, U+0104->U+0105, U+0105, U+0106->U+0107, U+0107, U+0108->U+0109,U+0109, U+010A->U+010B, U+010B, U+010C->U+010D, U+010D, U+010E->U+010F, U+010F,U+0110->U+0111, U+0111, U+0112->U+0113, U+0113, U+0114->U+0115, U+0115, U+0116->U+0117,U+0117, U+0118->U+0119, U+0119, U+011A->U+011B, U+011B, U+011C->U+011D, U+011D,U+011E->U+011F, U+011F, U+0130->U+0131, U+0131, U+0132->U+0133, U+0133, U+0134->U+0135,U+0135, U+0136->U+0137, U+0137, U+0139->U+013A, U+013A, U+013B->U+013C, U+013C,U+013D->U+013E, U+013E, U+013F->U+0140, U+0140, U+0141->U+0142, U+0142, U+0143->U+0144,U+0144, U+0145->U+0146, U+0146, U+0147->U+0148, U+0148, U+014A->U+014B, U+014B,U+014C->U+014D, U+014D, U+014E->U+014F, U+014F, U+0150->U+0151, U+0151, U+0152->U+0153,U+0153, U+0154->U+0155, U+0155, U+0156->U+0157, U+0157, U+0158->U+0159, U+0159,U+015A->U+015B, U+015B, U+015C->U+015D, U+015D, U+015E->U+015F, U+015F, U+0160->U+0161,U+0161, U+0162->U+0163, U+0163, U+0164->U+0165, U+0165, U+0166->U+0167, U+0167,U+0168->U+0169, U+0169, U+016A->U+016B, U+016B, U+016C->U+016D, U+016D, U+016E->U+016F,U+016F, U+0170->U+0171, U+0171, U+0172->U+0173, U+0173, U+0174->U+0175, U+0175,U+0176->U+0177, U+0177, U+0178->U+00FF, U+00FF, U+0179->U+017A, U+017A, U+017B->U+017C,U+017C, U+017D->U+017E, U+017E, U+0410..U+042F->U+0430..U+044F, U+0430..U+044F,U+05D0..U+05EA, U+0531..U+0556->U+0561..U+0586, U+0561..U+0587, U+0621..U+063A, U+01B9,U+01BF, U+0640..U+064A, U+0660..U+0669, U+066E, U+066F, U+0671..U+06D3, U+06F0..U+06FF,U+0904..U+0939, U+0958..U+095F, U+0960..U+0963, U+0966..U+096F, U+097B..U+097F,U+0985..U+09B9, U+09CE, U+09DC..U+09E3, U+09E6..U+09EF, U+0A05..U+0A39, U+0A59..U+0A5E,U+0A66..U+0A6F, U+0A85..U+0AB9, U+0AE0..U+0AE3, U+0AE6..U+0AEF, U+0B05..U+0B39,U+0B5C..U+0B61, U+0B66..U+0B6F, U+0B71, U+0B85..U+0BB9, U+0BE6..U+0BF2, U+0C05..U+0C39,U+0C66..U+0C6F, U+0C85..U+0CB9, U+0CDE..U+0CE3, U+0CE6..U+0CEF, U+0D05..U+0D39, U+0D60,U+0D61, U+0D66..U+0D6F, U+0D85..U+0DC6, U+1900..U+1938, U+1946..U+194F, U+A800..U+A805,U+A807..U+A822, U+0386->U+03B1, U+03AC->U+03B1, U+0388->U+03B5, U+03AD->U+03B5,U+0389->U+03B7, U+03AE->U+03B7, U+038A->U+03B9, U+0390->U+03B9, U+03AA->U+03B9,U+03AF->U+03B9, U+03CA->U+03B9, U+038C->U+03BF, U+03CC->U+03BF, U+038E->U+03C5,U+03AB->U+03C5, U+03B0->U+03C5, U+03CB->U+03C5, U+03CD->U+03C5, U+038F->U+03C9,U+03CE->U+03C9, U+03C2->U+03C3, U+0391..U+03A1->U+03B1..U+03C1,U+03A3..U+03A9->U+03C3..U+03C9, U+03B1..U+03C1, U+03C3..U+03C9, U+0E01..U+0E2E,U+0E30..U+0E3A, U+0E40..U+0E45, U+0E47, U+0E50..U+0E59, U+A000..U+A48F, U+4E00..U+9FBF,U+3400..U+4DBF, U+20000..U+2A6DF, U+F900..U+FAFF, U+2F800..U+2FA1F, U+2E80..U+2EFF,U+2F00..U+2FDF, U+3100..U+312F, U+31A0..U+31BF, U+3040..U+309F, U+30A0..U+30FF,U+31F0..U+31FF, U+AC00..U+D7AF, U+1100..U+11FF, U+3130..U+318F, U+A000..U+A48F,U+A490..U+A4CF
 ngram_len = 1
 ngram_chars = U+4E00..U+9FBF, U+3400..U+4DBF, U+20000..U+2A6DF, U+F900..U+FAFF,U+2F800..U+2FA1F, U+2E80..U+2EFF, U+2F00..U+2FDF, U+3100..U+312F, U+31A0..U+31BF,U+3040..U+309F, U+30A0..U+30FF,U+31F0..U+31FF, U+AC00..U+D7AF, U+1100..U+11FF,U+3130..U+318F, U+A000..U+A48F, U+A490..U+A4CF
}
Copy after login

三,插入数据,并且更新增量索引

1,插入数据

mysql> INSERT INTO users (username)VALUES('张三疯'),('张四疯');
mysql> INSERT INTO orders(user_id, product_name, summary) VALUES ('1311895266', '我了个XX', '苛夺asdfasdfasdf'),
('1311895267', 'iasdfasdf', '好苛夺花样百出顶戴要');
Copy after login

2,更新增量索引

/usr/local/sphinx2/bin/indexer --config /usr/local/sphinx2/etc/sphinx.conf --rotate moreorder
Copy after login

更新了增量索引后,新增的二条数据在增量索引中,而不在主索引中。

sphinx_rotate更新增量索引

sphinx_rotate更新增量索引

mysql> select * from moreorder where match('张');   //在增量索引中
+------+------------+-----------+-------------+--------------+
| id   | user_id    | username  | create_time | product_name |
+------+------------+-----------+-------------+--------------+
|   13 | 1311895266 | 张三疯 |           0 | 我了个XX  |
|   14 | 1311895267 | 张四疯 |           0 | iasdfasdf    |
+------+------------+-----------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> select * from myorder where match('张');   //主索引没有
+------+------------+------------+-------------+----------------+
| id   | user_id    | username   | create_time | product_name   |
+------+------------+------------+-------------+----------------+
|    9 | 1311895262 | 张三     |  1406823894 | tank is 坦克 |
|   10 | 1311895263 | tank张二 |  1406823894 | tank is 坦克 |
|   11 | 1311895264 | tank张一 |  1406823894 | tank is 坦克 |
|   12 | 1311895265 | tank张    |  1406823894 | tank is 坦克 |
+------+------------+------------+-------------+----------------+
4 rows in set (0.00 sec)
Copy after login

解决这个问题,有二个办法,一个利用分布式索引,一个把增量索引和主索引进行合并

四,sphinx分布式索引配置

1,修改sphinx.conf ,加上以下内容

index mytest
{
        type                    = distributed
        local                   = myorder     //本地
     local                   = moreorder   //本地
#        agent                   = 192.168.10.103:9313:myuser  //远程
        agent_connect_timeout   = 1000
        agent_query_timeout     = 3000
}
Copy after login

重新启动sphinx

2,插入新的数据,并更新增量索引,根上面一样,就不多说了。

3,测试sphinx 增量

mysql> select * from myorder where match('张');   //新增数据没有
+------+------------+------------+-------------+----------------+
| id   | user_id    | username   | create_time | product_name   |
+------+------------+------------+-------------+----------------+
|    9 | 1311895262 | 张三     |  1406823894 | tank is 坦克 |
|   10 | 1311895263 | tank张二 |  1406823894 | tank is 坦克 |
|   11 | 1311895264 | tank张一 |  1406823894 | tank is 坦克 |
|   12 | 1311895265 | tank张    |  1406823894 | tank is 坦克 |
|   13 | 1311895266 | 张三疯  |           0 | 我了个XX    |
|   14 | 1311895267 | 张四疯  |           0 | iasdfasdf      |
+------+------------+------------+-------------+----------------+
6 rows in set (0.00 sec)
mysql> select * from moreorder where match('张');  //新增数据在增量索引里面
+------+------------+-----------+-------------+--------------+
| id   | user_id    | username  | create_time | product_name |
+------+------------+-----------+-------------+--------------+
|   15 | 1311895268 | 张五疯 |           0 | 我了个XX  |
|   16 | 1311895269 | 张六疯 |           0 | iasdfasdf    |
+------+------------+-----------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> select * from mytest where match('张');   //在这里可以把mytest当成是连接池
+------+------------+------------+-------------+----------------+
| id   | user_id    | username   | create_time | product_name   |
+------+------------+------------+-------------+----------------+
|   15 | 1311895268 | 张五疯  |           0 | 我了个XX    |
|   16 | 1311895269 | 张六疯  |           0 | iasdfasdf      |
|    9 | 1311895262 | 张三     |  1406823894 | tank is 坦克 |
|   10 | 1311895263 | tank张二 |  1406823894 | tank is 坦克 |
|   11 | 1311895264 | tank张一 |  1406823894 | tank is 坦克 |
|   12 | 1311895265 | tank张    |  1406823894 | tank is 坦克 |
|   13 | 1311895266 | 张三疯  |           0 | 我了个XX    |
|   14 | 1311895267 | 张四疯  |           0 | iasdfasdf      |
+------+------------+------------+-------------+----------------+
8 rows in set (0.00 sec)
Copy after login

五,合并增量索引和主索引

1,插入新的数据,并更新增量索引,根上面一样,就不多说了。

2,合并增量索引和主索引

# /usr/local/sphinx2/bin/indexer --config /usr/local/sphinx2/etc/sphinx.conf --merge myorder moreorder --rotate
Copy after login

3,测试sphinx

mysql> select * from myorder where match('张');  //这时在看主索引时,就有新增的二条数据了
+------+------------+------------+-------------+----------------+
| id   | user_id    | username   | create_time | product_name   |
+------+------------+------------+-------------+----------------+
|    9 | 1311895262 | 张三     |  1406823894 | tank is 坦克 |
|   10 | 1311895263 | tank张二 |  1406823894 | tank is 坦克 |
|   11 | 1311895264 | tank张一 |  1406823894 | tank is 坦克 |
|   12 | 1311895265 | tank张    |  1406823894 | tank is 坦克 |
|   13 | 1311895266 | 张三疯  |           0 | 我了个XX    |
|   14 | 1311895267 | 张四疯  |           0 | iasdfasdf      |
+------+------------+------------+-------------+----------------+
6 rows in set (0.00 sec)
Copy after login
Related labels:
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template