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)

相关广告
  • sphinx实现增量索引 分布式索引几个例子 sphinx实现增量索引 分布式索引几个例子 sphinx实现增量索引 分布式索引几个例子
相关阅读

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

2019/10/10 17:32:12 | 谷歌SEO算法 | Google