增量索引，其实就是增加的内容，例如：存款有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)
二，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}
三,插入数据，并且更新增量索引
1,插入数据
mysql> insert into users (username)values('张三疯'),('张四疯');mysql> insert into orders(user_id, product_name, summary) values ('1311895266', '我了个xx', '苛夺asdfasdfasdf'),('1311895267', 'iasdfasdf', '好苛夺花样百出顶戴要');
2,更新增量索引
/usr/local/sphinx2/bin/indexer --config /usr/local/sphinx2/etc/sphinx.conf --rotate moreorder
更新了增量索引后，新增的二条数据在增量索引中，而不在主索引中。
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)
解决这个问题，有二个办法，一个利用分布式索引，一个把增量索引和主索引进行合并
四，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}
重新启动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)
五，合并增量索引和主索引
1,插入新的数据，并更新增量索引，根上面一样，就不多说了。
2,合并增量索引和主索引
# /usr/local/sphinx2/bin/indexer --config /usr/local/sphinx2/etc/sphinx.conf --merge myorder moreorder --rotate
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)
原文地址：sphinx 增量索引 分布式索引 实例, 感谢原作者分享。