对于用惯数据库的我们,多表进行join连接,是非常常见的一个需求,但是在我们的索引中,对join的支持,却不是很完美,当然这并不是由于我们的Lucene或Solr不够强大,而是全文检索与数据库的定位不是在同一个目标上,全文检索,主要定位在搜索引擎上,通常是对一个大的索引进行高效检索,而数据库则是定位在结构化数据的存储于与检索,检索功能比较薄弱,那我们的索引是不是就不支持join了,实事并非如此,Lucene里面支持join操作,这种join定位在同一份索引里,而Solr作为Lucene的扩展,又提供了两core join的功能,下面散仙给出一个例子,尽量简单,清晰的描述出如何使用它们和理解它们的工作方式。
有2个core,分别是collection1,和collection2,里面的数据分别是: collection1:总共有3条数据 collection1:schema 都是字符串string ; 有id,name两个字段 ; collection1: {1, Apple}, {2, Samsung}, {3, HTC} collection2:总共有5条数据 collection2:schema 都是字符串string ;有id,name,brand_id 两个字段; collection2: {1, iPhone, 1}, {2, iPad, 1}, {3, Galaxy S3, 2}, {4, Galaxy Note, 2}, {5, One X, 3} 下面,先来看下单core的join,以collection2作为例子,测试代码如下:- /***
- * join测试
- *
- *
- * ***/
- public static void joinquery2()throws Exception{
- SolrServer server1=new HttpSolrServer("http://localhost:9003/solr/collection2");
- SolrQuery sq=new SolrQuery();
- //sq.set("fl", "id,name");//过滤只需要返回的字段
- sq.set("q", "{!join from=id to=brand_id }brand_id:*");
- QueryResponse qr=server1.query(sq, METHOD.POST);
- SolrDocumentList list=qr.getResults();
- System.out.println("命中结果集:"+qr.getResults().size());
- for(SolrDocument s:list){
- System.out.println(s.toString());
- }
- }
/*** * join测试 * * * ***/public static void joinquery2()throws Exception{ SolrServer server1=new HttpSolrServer("http://localhost:9003/solr/collection2"); SolrQuery sq=new SolrQuery(); //sq.set("fl", "id,name");//过滤只需要返回的字段 sq.set("q", "{!join from=id to=brand_id }brand_id:*"); QueryResponse qr=server1.query(sq, METHOD.POST); SolrDocumentList list=qr.getResults(); System.out.println("命中结果集:"+qr.getResults().size()); for(SolrDocument s:list){ System.out.println(s.toString()); }}运行结果如下:
- 五月 14, 2014 9:03:58 下午 org.apache.solr.client.solrj.impl.HttpClientUtil createClient
- INFO: Creating new http client, config:maxConnections=128&maxConnectionsPerHost=32&followRedirects=false
- 命中结果集:5
- SolrDocument{id=1, name=iPhone, brand_id=1, _version_=1468079557386960896}
- SolrDocument{id=2, name=iPad, brand_id=1, _version_=1468079557408980992}
- SolrDocument{id=3, name=Galaxy, brand_id=2, _version_=1468079557412126720}
- SolrDocument{id=4, name=Galaxy Note, brand_id=2, _version_=1468079557416321024}
- SolrDocument{id=5, name=One X, brand_id=3, _version_=1468079557420515328}
五月 14, 2014 9:03:58 下午 org.apache.solr.client.solrj.impl.HttpClientUtil createClientINFO: Creating new http client, config:maxConnections=128&maxConnectionsPerHost=32&followRedirects=false命中结果集:5SolrDocument{id=1, name=iPhone, brand_id=1, _version_=1468079557386960896}SolrDocument{id=2, name=iPad, brand_id=1, _version_=1468079557408980992}SolrDocument{id=3, name=Galaxy, brand_id=2, _version_=1468079557412126720}SolrDocument{id=4, name=Galaxy Note, brand_id=2, _version_=1468079557416321024}SolrDocument{id=5, name=One X, brand_id=3, _version_=1468079557420515328}改变,条件后,再测:
- /***
- * join测试
- *
- *
- * ***/
- public static void joinquery2()throws Exception{
- SolrServer server1=new HttpSolrServer("http://localhost:9003/solr/collection2");
- SolrQuery sq=new SolrQuery();
- //sq.set("fl", "id,name");//过滤只需要返回的字段
- sq.set("q", "{!join from=id to=brand_id }brand_id:2");
- QueryResponse qr=server1.query(sq, METHOD.POST);
- SolrDocumentList list=qr.getResults();
- System.out.println("命中结果集:"+qr.getResults().size());
- for(SolrDocument s:list){
- System.out.println(s.toString());
- }
- }
/*** * join测试 * * * ***/public static void joinquery2()throws Exception{ SolrServer server1=new HttpSolrServer("http://localhost:9003/solr/collection2"); SolrQuery sq=new SolrQuery(); //sq.set("fl", "id,name");//过滤只需要返回的字段 sq.set("q", "{!join from=id to=brand_id }brand_id:2"); QueryResponse qr=server1.query(sq, METHOD.POST); SolrDocumentList list=qr.getResults(); System.out.println("命中结果集:"+qr.getResults().size()); for(SolrDocument s:list){ System.out.println(s.toString()); }}运行结果如下:
- 五月 14, 2014 9:10:04 下午 org.apache.solr.client.solrj.impl.HttpClientUtil createClient
- INFO: Creating new http client, config:maxConnections=128&maxConnectionsPerHost=32&followRedirects=false
- 命中结果集:1
- SolrDocument{id=5, name=One X, brand_id=3, _version_=1468079557420515328}
五月 14, 2014 9:10:04 下午 org.apache.solr.client.solrj.impl.HttpClientUtil createClientINFO: Creating new http client, config:maxConnections=128&maxConnectionsPerHost=32&followRedirects=false命中结果集:1SolrDocument{id=5, name=One X, brand_id=3, _version_=1468079557420515328}分析运行原理,类似sql中的写法: SELECT * FROM collection1 WHERE brand_id IN (SELECT id FROM collection1 where brand_id = * ) 第一步,先执行子查询SELECT id FROM collection1 where brand_id = * 会返回所有的id分别是,1,2,3,4,5 第二步,执行主查询就是 SELECT * FROM collection1 WHERE brand_id in (1,2,3,4,5) 而brand_id去重完之后,就只有1,2,3了,所以转换成如下查询: SELECT * FROM collection1 WHERE brand_id(1,2,3) in (1,2,3,4,5) 取并集后结果,就会命中brand_id=1,2,3的文档,所以就命中了所有的文档 再来分析下,第二个查询,指定查询id的join: 第一步,先执行子查询SELECT id FROM collection1 where brand_id = 2 会返回所有的id分别是,3,4, 第二步,执行主查询就是 SELECT * FROM collection1 WHERE brand_id in (3,4) 而brand_id去重完之后,就只有1,2,3了,所以转换成如下查询: SELECT * FROM collection1 WHERE brand_id(1,2,3) in brand_id(3,4) 取并集后的结果,就会命中brand_id=3的文档了,所以就会返回ID为5的文档; 下面,来测下,两个core的join,代码如下:
- /***
- * join测试
- *
- *
- * ***/
- public static void joinquery2()throws Exception{
- SolrServer server1=new HttpSolrServer("http://localhost:9003/solr/collection1");
- SolrQuery sq=new SolrQuery();
- //sq.set("fl", "id,name");//过滤只需要返回的字段
- sq.set("q", "{!join from=brand_id to=id fromIndex=collection2}name:iPad");
- QueryResponse qr=server1.query(sq, METHOD.POST);
- SolrDocumentList list=qr.getResults();
- System.out.println("命中结果集:"+qr.getResults().size());
- for(SolrDocument s:list){
- System.out.println(s.toString());
- }
- }
/*** * join测试 * * * ***/public static void joinquery2()throws Exception{ SolrServer server1=new HttpSolrServer("http://localhost:9003/solr/collection1"); SolrQuery sq=new SolrQuery(); //sq.set("fl", "id,name");//过滤只需要返回的字段 sq.set("q", "{!join from=brand_id to=id fromIndex=collection2}name:iPad"); QueryResponse qr=server1.query(sq, METHOD.POST); SolrDocumentList list=qr.getResults(); System.out.println("命中结果集:"+qr.getResults().size()); for(SolrDocument s:list){ System.out.println(s.toString()); }}结果如下:
- 五月 14, 2014 9:30:41 下午 org.apache.solr.client.solrj.impl.HttpClientUtil createClient
- INFO: Creating new http client, config:maxConnections=128&maxConnectionsPerHost=32&followRedirects=false
- 命中结果集:1
- SolrDocument{id=1, name=Apple, _version_=1468079556974870528}
五月 14, 2014 9:30:41 下午 org.apache.solr.client.solrj.impl.HttpClientUtil createClientINFO: Creating new http client, config:maxConnections=128&maxConnectionsPerHost=32&followRedirects=false命中结果集:1SolrDocument{id=1, name=Apple, _version_=1468079556974870528}分析类似如下SQL: SELECT b.* FROM collection1 b INNER JOIN collection2 p ON b.id=p.brand_id WHERE p.name="iPad"; 注意collection名的先后顺序,如上solrj里面的执行,跟上面的sql的运行规则是一样的,所以我们最终的结果里,会返回,如果我们的条件是下面的相反组合:
- public static void joinquery()throws Exception{
- SolrServer server1=new HttpSolrServer("http://localhost:9003/solr/collection2");
- SolrQuery sq=new SolrQuery();
- sq.set("q", "{!join from=id to=brand_id fromIndex=collection1}id:1");
- QueryResponse qr=server1.query(sq, METHOD.POST);
- SolrDocumentList list=qr.getResults();
- System.out.println("命中结果集:"+qr.getResults().size());
- for(SolrDocument s:list){
- //s.toString();
- System.out.println(s.toString());
- }
public static void joinquery()throws Exception{ SolrServer server1=new HttpSolrServer("http://localhost:9003/solr/collection2"); SolrQuery sq=new SolrQuery(); sq.set("q", "{!join from=id to=brand_id fromIndex=collection1}id:1"); QueryResponse qr=server1.query(sq, METHOD.POST); SolrDocumentList list=qr.getResults(); System.out.println("命中结果集:"+qr.getResults().size()); for(SolrDocument s:list){ //s.toString(); System.out.println(s.toString()); }则运行结果如下所示:
- 五月 14, 2014 9:43:46 下午 org.apache.solr.client.solrj.impl.HttpClientUtil createClient
- INFO: Creating new http client, config:maxConnections=128&maxConnectionsPerHost=32&followRedirects=false
- 命中结果集:2
- SolrDocument{id=1, name=iPhone, brand_id=1, _version_=1468079557386960896}
- SolrDocument{id=2, name=iPad, brand_id=1, _version_=1468079557408980992}
五月 14, 2014 9:43:46 下午 org.apache.solr.client.solrj.impl.HttpClientUtil createClientINFO: Creating new http client, config:maxConnections=128&maxConnectionsPerHost=32&followRedirects=false命中结果集:2SolrDocument{id=1, name=iPhone, brand_id=1, _version_=1468079557386960896}SolrDocument{id=2, name=iPad, brand_id=1, _version_=1468079557408980992}原理,依旧与如上的sql一样。只不过位置相反,调整了: SELECT b.* FROM collection2 b INNER JOIN collection1 p ON b.id=p.brand_id WHERE b.id=1;