sphinx实现增量索引 分布式索引几个例子

增量索引 分布式索我们可以这样理解增加就是在原基础上增加,分布式索是我们不够多了,要从别人那里拿或放到别人那边去,增量索引,其实就是增加的内容,例如:存款有100块,今天挣了10块,这10块就是增量了.

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

一,测试表和数据,代码如下:

1. mysql>descsph_counter;
2. +------------+---------+------+-----+---------+-------+
3. |Field|Type|Null|Key|Default|Extra|
4. +------------+---------+------+-----+---------+-------+
5. |counter_id|int(11)|NO|PRI|NULL||
6. |max_doc_id|int(11)|NO||NULL||
7. +------------+---------+------+-----+---------+-------+
8. 2rowsinset(0.00sec)
10. mysql>descorders;
11. +--------------+-------------+------+-----+---------+----------------+
12. |Field|Type|Null|Key|Default|Extra|
13. +--------------+-------------+------+-----+---------+----------------+
14. |id|int(11)|NO|PRI|NULL|auto_increment|
15. |user_id|int(11)|NO||NULL||
16. |create_time|datetime|NO||NULL||
17. |product_name|varchar(20)|NO||NULL||
18. |summary|text|NO||NULL||
19. +--------------+-------------+------+-----+---------+----------------+
20. 5rowsinset(0.00sec)
22. mysql>select*fromorders;
23. +----+------------+---------------------+----------------+--------------+
24. |id|user_id|create_time|product_name|summary|
25. +----+------------+---------------------+----------------+--------------+
26. |9|1311895262|2014-08-0100:24:54|tankis坦克|技术总监|
27. |10|1311895263|2014-08-0100:24:54|tankis坦克|技术经理|
28. |11|1311895264|2014-08-0100:24:54|tankis坦克|DNB经理|
29. |12|1311895265|2014-08-0100:24:54|tankis坦克|运维总监|
30. +----+------------+---------------------+----------------+--------------+
31. 4rowsinset(0.00sec)
33. mysql>descusers;
34. +----------+-------------+------+-----+---------+----------------+
35. |Field|Type|Null|Key|Default|Extra|
36. +----------+-------------+------+-----+---------+----------------+
37. |user_id|int(11)|NO|PRI|NULL|auto_increment|
38. |username|varchar(20)|NO||NULL||
39. +----------+-------------+------+-----+---------+----------------+
40. 2rowsinset(0.00sec)
41. --phpfensi.com
42. mysql>select*fromusers;
43. +------------+------------+
44. |user_id|username|
45. +------------+------------+
46. |1311895262|张三|
47. |1311895263|tank张二|
48. |1311895264|tank张一|
49. |1311895265|tank张|
50. +------------+------------+
51. 4rowsinset(0.00sec)

二,sphinx.conf配置,代码如下:

1. sourcemyorder
2. {
3. type=mysql
4. sql_host=localhost
5. sql_user=root
6. sql_pass=
7. sql_db=test
8. sql_query_pre=SETNAMESutf8
9. sql_query_pre=SETSESSIONquery_cache_type=OFF
10. sql_query_pre=REPLACEINTOsph_counterSELECT1,MAX(id)FROMorders
11. sql_query=
12. SELECTa.id,a.user_id,b.username,UNIX_TIMESTAMP(a.create_time)AScreate_time,a.product_name,a.summary
13. FROMordersaleftjoinusersbona.user_id=b.user_id
14. sql_attr_uint=user_id
15. sql_field_string=username
16. sql_field_string=product_name
17. sql_attr_timestamp=create_time
19. sql_ranged_throttle=0
20. #sql_query_info=SELECT*FROMordersWHEREid=$id
21. }
23. sourcemoreorder:myorder
24. {
25. type=mysql
26. sql_host=localhost
27. sql_user=root
28. sql_pass=
29. sql_db=test
30. sql_query_pre=SETNAMESutf8
31. sql_query_pre=SETSESSIONquery_cache_type=OFF
32. sql_query=
33. SELECTa.id,a.user_id,b.username,UNIX_TIMESTAMP(a.create_time)AScreate_time,a.product_name,a.summary
34. FROMordersaleftjoinusersbona.user_id=b.user_idwherea.id>(SELECTmax_doc_idFROMsph_counterWHEREcounter_id=1)
35. sql_attr_uint=user_id
36. sql_field_string=username
37. sql_field_string=product_name
38. sql_attr_timestamp=create_time
40. sql_ranged_throttle=0
41. #sql_query_info=SELECT*FROMordersWHEREid=$id
42. }
44. indexmyorder//主索引
45. {
46. source=myorder
47. path=/usr/local/sphinx2/var/data/myorder
48. docinfo=extern
49. mlock=0
50. morphology=none
51. min_word_len=1
52. #charset_type=zh_cn.utf-8
53. html_strip=1
54. 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
55. ngram_len=1
56. 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
57. }
59. indexmoreorder:myorder//增量索引
60. {
61. source=moreorder
62. path=/usr/local/sphinx2/var/data/moreorder
63. docinfo=extern
64. mlock=0
65. morphology=none
66. min_word_len=1
67. #charset_type=zh_cn.utf-8
68. html_strip=1
69. 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
70. ngram_len=1
71. 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
72. }

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

1,插入数据,代码如下:

1. mysql>INSERTINTOusers(username)VALUES('张三疯'),('张四疯');
3. mysql>INSERTINTOorders(user_id,product_name,summary)VALUES('1311895266','我了个XX','苛夺asdfasdfasdf'),
4. ('1311895267','iasdfasdf','好苛夺花样百出顶戴要');

2,更新增量索引,代码如下:

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

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

1. mysql>select*frommoreorderwherematch('张');//在增量索引中
2. +------+------------+-----------+-------------+--------------+
3. |id|user_id|username|create_time|product_name|
4. +------+------------+-----------+-------------+--------------+
5. |13|1311895266|张三疯|0|我了个XX|
6. |14|1311895267|张四疯|0|iasdfasdf|
7. +------+------------+-----------+-------------+--------------+
8. 2rowsinset(0.00sec)
10. mysql>select*frommyorderwherematch('张');//主索引没有
11. +------+------------+------------+-------------+----------------+
12. |id|user_id|username|create_time|product_name|
13. +------+------------+------------+-------------+----------------+
14. |9|1311895262|张三|1406823894|tankis坦克|
15. |10|1311895263|tank张二|1406823894|tankis坦克|
16. |11|1311895264|tank张一|1406823894|tankis坦克|
17. |12|1311895265|tank张|1406823894|tankis坦克|
18. +------+------------+------------+-------------+----------------+
19. 4rowsinset(0.00sec)

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

四,sphinx分布式索引配置.

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

1. indexmytest
2. {
3. type=distributed
4. local=myorder//本地
5. local=moreorder//本地
6. #agent=192.168.10.103:9313:myuser//远程
7. agent_connect_timeout=1000
8. agent_query_timeout=3000
9. }

重新启动sphinx.

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

3,测试sphinx 增量,代码如下:

1. mysql>select*frommyorderwherematch('张');//新增数据没有
2. +------+------------+------------+-------------+----------------+
3. |id|user_id|username|create_time|product_name|
4. +------+------------+------------+-------------+----------------+
5. |9|1311895262|张三|1406823894|tankis坦克|
6. |10|1311895263|tank张二|1406823894|tankis坦克|
7. |11|1311895264|tank张一|1406823894|tankis坦克|
8. |12|1311895265|tank张|1406823894|tankis坦克|
9. |13|1311895266|张三疯|0|我了个XX|
10. |14|1311895267|张四疯|0|iasdfasdf|
11. +------+------------+------------+-------------+----------------+
12. 6rowsinset(0.00sec)
14. mysql>select*frommoreorderwherematch('张');//新增数据在增量索引里面
15. +------+------------+-----------+-------------+--------------+
16. |id|user_id|username|create_time|product_name|
17. +------+------------+-----------+-------------+--------------+
18. |15|1311895268|张五疯|0|我了个XX|
19. |16|1311895269|张六疯|0|iasdfasdf|
20. +------+------------+-----------+-------------+--------------+
21. 2rowsinset(0.00sec)
23. mysql>select*frommytestwherematch('张');//在这里可以把mytest当成是连接池
24. +------+------------+------------+-------------+----------------+
25. |id|user_id|username|create_time|product_name|
26. +------+------------+------------+-------------+----------------+
27. |15|1311895268|张五疯|0|我了个XX|
28. |16|1311895269|张六疯|0|iasdfasdf|
29. |9|1311895262|张三|1406823894|tankis坦克|
30. |10|1311895263|tank张二|1406823894|tankis坦克|
31. |11|1311895264|tank张一|1406823894|tankis坦克|
32. |12|1311895265|tank张|1406823894|tankis坦克|
33. |13|1311895266|张三疯|0|我了个XX|
34. |14|1311895267|张四疯|0|iasdfasdf|
35. +------+------------+------------+-------------+----------------+
36. 8rowsinset(0.00sec)

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

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

2,合并增量索引和主索引,代码如下:

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

3,测试sphinx,代码如下:

1. mysql>select*frommyorderwherematch('张');//这时在看主索引时，就有新增的二条数据了
2. +------+------------+------------+-------------+----------------+
3. |id|user_id|username|create_time|product_name|
4. +------+------------+------------+-------------+----------------+
5. |9|1311895262|张三|1406823894|tankis坦克|
6. |10|1311895263|tank张二|1406823894|tankis坦克|
7. |11|1311895264|tank张一|1406823894|tankis坦克|
8. |12|1311895265|tank张|1406823894|tankis坦克|
9. |13|1311895266|张三疯|0|我了个XX|
10. |14|1311895267|张四疯|0|iasdfasdf|
11. +------+------------+------------+-------------+----------------+
12. 6rowsinset(0.00sec)