Sphinx Implementation of incremental Index distributed index several examples

The incremental index, in fact, is the added content, for example: Deposit has 100 yuan, today made 10, this 10 is an increment
Distributed index, you can understand that, want to open the company money is not enough, need to borrow money from a lot of people. That is, we use the money together.
one, test tables and data

The code is as follows

Copy Code

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 Tank | Technical Director | | 10 | 1311895263 | 2014-08-01 00:24:54 | Tank is Tank | Technical Manager | | 11 | 1311895264 | 2014-08-01 00:24:54 | Tank is Tank | DNB Manager | | 12 | 1311895265 | 2014-08-01 00:24:54 | Tank is Tank | Director of operation and Maintenance | +----+------------+---------------------+----------------+--------------+ 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 | John | | 1311895263 | Tank Zhang Yi | | 1311895264 | Tank Zhang | | 1311895265 | Tank Zhang | +------------+------------+ 4 rows in Set (0.00 sec)

Second, sphinx.conf configuration

The code is as follows

Copy Code

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 the orders a LEFT join the 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 { 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 = 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 Counte r_id=1) 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 } Index Myorder//Primary index { 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//incremental indexes { 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 }

Three, insert data, and update incremental index
1, inserting data

The code is as follows	Copy Code
Mysql> INSERT into users (username) VALUES (' John Crazy '), (' Zhang Si crazy '); Mysql> INSERT into orders (user_id, Product_Name, summary) VALUES (' 1311895266 ', ' I'm a xx ', ' take Asdfasdfasdf '), (' 1311895267 ', ' iasdfasdf ', ' good Ding Dai ');

2, update incremental index

The code is as follows

Copy Code

/usr/local/sphinx2/bin/indexer--config/usr/local/sphinx2/etc/sphinx.conf--rotate Moreorder After the incremental index has been updated, the new two data is in the incremental index, not in the primary index. Sphinx_rotate Update Incremental Index Sphinx_rotate Update Incremental Index Mysql> SELECT * from Moreorder where match (' Zhang '); In the incremental index +------+------------+-----------+-------------+--------------+ | ID | user_id | Username | Create_time | Product_Name | +------+------------+-----------+-------------+--------------+ | 13 | 1311895266 |           John Crazy | 0 | I have a XX | | 14 | 1311895267 |           Zhang Si Crazy | 0 | IASDFASDF | +------+------------+-----------+-------------+--------------+ 2 rows in Set (0.00 sec) Mysql> SELECT * from Myorder where match (' Zhang '); The primary index does not have +------+------------+------------+-------------+----------------+ | ID | user_id | Username | Create_time | Product_Name | +------+------------+------------+-------------+----------------+ | 9 | 1311895262 |  John | 1406823894 | Tank is Tank | | 10 | 1311895263 |  Tank Zhang Yi | 1406823894 | Tank is Tank | | 11 | 1311895264 |  Tank Zhang | 1406823894 | Tank is Tank | | 12 | 1311895265 |  Tank Zhang | 1406823894 | Tank is Tank | +------+------------+------------+-------------+----------------+ 4 rows in Set (0.00 sec)

To solve this problem, there are two ways to use a distributed index, a combination of the incremental index and the primary index
Four, Sphinx distributed index Configuration
1, modify sphinx.conf, add the following content

The code is as follows	Copy Code
Index MyTest { Type = Distributed Local = myorder//native Local = moreorder//native # Agent = 192.168.10.103:9313:myuser//Remote Agent_connect_timeout = 1000 Agent_query_timeout = 3000 }

Reboot Sphinx
2, insert the new data, and update the incremental index, the same as above, don't say more.
3, test Sphinx increment

The code is as follows

Copy Code

Mysql> SELECT * from Myorder where match (' Zhang '); New data is not +------+------------+------------+-------------+----------------+ | ID | user_id | Username | Create_time | Product_Name | +------+------------+------------+-------------+----------------+ | 9 | 1311895262 |  John | 1406823894 | Tank is Tank | | 10 | 1311895263 |  Tank Zhang Yi | 1406823894 | Tank is Tank | | 11 | 1311895264 |  Tank Zhang | 1406823894 | Tank is Tank | | 12 | 1311895265 |  Tank Zhang | 1406823894 | Tank is Tank | | 13 | 1311895266 |           John Crazy | 0 | I have a XX | | 14 | 1311895267 |           Zhang Si Crazy | 0 | IASDFASDF | +------+------------+------------+-------------+----------------+ 6 rows in Set (0.00 sec) Mysql> SELECT * from Moreorder where match (' Zhang '); New data in incremental index +------+------------+-----------+-------------+--------------+ | ID | user_id | Username | Create_time | Product_Name | +------+------------+-----------+-------------+--------------+ | 15 | 1311895268 |           Steven Cheung Crazy | 0 | I have a XX | | 16 | 1311895269 |           Zhang Six Crazy | 0 | IASDFASDF | +------+------------+-----------+-------------+--------------+ 2 rows in Set (0.00 sec) Mysql> SELECT * from mytest where match (' Zhang '); Here you can think of mytest as a connection pool. +------+------------+------------+-------------+----------------+ | ID | user_id | Username | Create_time | Product_Name | +------+------------+------------+-------------+----------------+ | 15 | 1311895268 |           Steven Cheung Crazy | 0 | I have a XX | | 16 | 1311895269 |           Zhang Six Crazy | 0 | IASDFASDF | | 9 | 1311895262 |  John | 1406823894 | Tank is Tank | | 10 | 1311895263 |  Tank Zhang Yi | 1406823894 | Tank is Tank | | 11 | 1311895264 |  Tank Zhang | 1406823894 | Tank is Tank | | 12 | 1311895265 |  Tank Zhang | 1406823894 | Tank is Tank | | 13 | 1311895266 |           John Crazy | 0 | I have a XX | | 14 | 1311895267 |           Zhang Si Crazy | 0 | IASDFASDF | +------+------------+------------+-------------+----------------+ 8 rows in Set (0.00 sec)

Five, merging incremental indexes and primary indexes
1, insert the new data, and update the incremental index, the same as above, don't say more.
2, merging incremental indexes and primary indexes

The code is as follows	Copy Code
#/usr/local/sphinx2/bin/indexer--config/usr/local/sphinx2/etc/sphinx.conf--merge myorder moreorder--rotate

3, Test Sphinx

The code is as follows

Copy Code

Mysql> SELECT * from Myorder where match (' Zhang '); When we look at the main index, we have two new data. +------+------------+------------+-------------+----------------+ | ID | user_id | Username | Create_time | Product_Name | +------+------------+------------+-------------+----------------+ | 9 | 1311895262 |  John | 1406823894 | Tank is Tank | | 10 | 1311895263 |  Tank Zhang Yi | 1406823894 | Tank is Tank | | 11 | 1311895264 |  Tank Zhang | 1406823894 | Tank is Tank | | 12 | 1311895265 |  Tank Zhang | 1406823894 | Tank is Tank | | 13 | 1311895266 |           John Crazy | 0 | I have a XX | | 14 | 1311895267 |           Zhang Si Crazy | 0 | IASDFASDF | +------+------------+------------+-------------+----------------+ 6 rows in Set (0.00 sec)