Solr与MySQL查询性能对比

柔光的暖阳◎ 2022-07-12 12:38 231阅读 0赞

随笔 - 142   文章 - 0   评论 - 556

测试数据量：10407608

Num Docs: 10407608

在项目中一个最常用的查询，查询某段时间内的数据，SQL查询获取数据，30s左右

SELECT * FROM `tf_hotspotdata_copy_test` WHERE collectTime BETWEEN '2014-12-06 00:00:00' AND '2014-12-10 21:31:55';

对collectTime建立索引后，同样的查询，2s，快了很多。

Solr索引

<field name="CollectTime" type="tdate" indexed="true" stored="true"/>
    <field name="IMSI" type="string" indexed="true" stored="true"/>
    <field name="IMEI" type="string" indexed="true" stored="true"/>
    <field name="DeviceID" type="string" indexed="true" stored="true"/>

Solr查询，同样的条件，72ms

"status": 0,

"QTime": 72,

"params": \{

"indent": "true",

"q": "CollectTime:\[2014-12-06T00:00:00.000Z TO 2014-12-10T21:31:55.000Z\]",

"\_": "1434617215202",

"wt": "json"

好吧，查询性能提高的不是一点点，用Solrj代码试试：

Solrj查询并获取结果集，结果集大小为220296，返回5个字段，时间为12s左右。

为什么需要这么长时间？上面的"QTime"只是根据索引查询的时间，如果要从solr服务端获取查询到的结果集，solr需要读取stored的字段（磁盘IO），再经过Http传输到本地（网络IO），这两者比较耗时，特别是磁盘IO。

时间对比：

<table style="max-width:100%; background-color:rgb(254,254,254); border-spacing:0px; width:605px; margin-bottom:1.5em; border-width:1px 1px 1px 0px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-color:rgb(221,221,221); border-right-color:rgb(221,221,221); border-bottom-color:rgb(221,221,221); border-left-style:initial; border-left-color:initial; font-size:14px; line-height:1.3em; color:rgb(51,51,51); font-family:&quot;Helvetica Neue&quot;,Helvetica,Tahoma,Arial,STXihei,&quot;Microsoft YaHei&quot;,微软雅黑,sans-serif"> 
 <tbody> 
  <tr style="line-height:1.3em"> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:0px; border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> 查询条件</p> </td> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:0px; border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> 时间</p> </td> 
  </tr> 
  <tr style="line-height:1.3em"> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> MySQL（无索引）</p> </td> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> 30s</p> </td> 
  </tr> 
  <tr style="line-height:1.3em"> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> MySQL（有索引）</p> </td> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> 2s</p> </td> 
  </tr> 
  <tr style="line-height:1.3em"> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> Solrj（select查询）</p> </td> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> 12s</p> </td> 
  </tr> 
 </tbody> 
</table>

如何优化？看看只获取ID需要的时间：

SQL查询只返回id，没有对collectTime建索引，10s左右

SELECT id FROM `tf_hotspotdata_copy_test` WHERE collectTime BETWEEN '2014-12-06 00:00:00' AND '2014-12-10 21:31:55';

SQL查询只返回id，同样的查询条件，对collectTime建索引，0.337s，很快。

Solrj查询只返回id，7s左右，快了一点。

id Size: 220296

Time: 7340

时间对比：

<table style="max-width:100%; background-color:rgb(254,254,254); border-spacing:0px; width:605px; margin-bottom:1.5em; border-width:1px 1px 1px 0px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-color:rgb(221,221,221); border-right-color:rgb(221,221,221); border-bottom-color:rgb(221,221,221); border-left-style:initial; border-left-color:initial; font-size:14px; line-height:1.3em; color:rgb(51,51,51); font-family:&quot;Helvetica Neue&quot;,Helvetica,Tahoma,Arial,STXihei,&quot;Microsoft YaHei&quot;,微软雅黑,sans-serif"> 
 <tbody> 
  <tr style="line-height:1.3em"> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:0px; border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> 查询条件（只获取ID）</p> </td> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:0px; border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> 时间</p> </td> 
  </tr> 
  <tr style="line-height:1.3em"> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> MySQL（无索引）</p> </td> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> 10s</p> </td> 
  </tr> 
  <tr style="line-height:1.3em"> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> MySQL（有索引）</p> </td> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> 0.337s</p> </td> 
  </tr> 
  <tr style="line-height:1.3em"> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> Solrj（select查询）</p> </td> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> 7s</p> </td> 
  </tr> 
 </tbody> 
</table>

继续优化。。

关于Solrj获取大量结果集速度慢的一些类似问题：

http://stackoverflow.com/questions/28181821/solr-performance\#

http://grokbase.com/t/lucene/solr-user/11aysnde25/query-time-help

http://lucene.472066.n3.nabble.com/Solrj-performance-bottleneck-td2682797.html

这个问题没有好的解决方式，基本的建议都是做分页，但是我们需要拿到大量数据做一些比对分析，做分页没有意义。

偶然看到一个回答，solr默认的查询使用的是"/select" request handler，可以用"/export" request handler来export结果集，看看solr对它的说明：

It's possible to export fully sorted result sets using a special rank query parser and response writer  specifically designed to work together to handle scenarios that involve sorting and exporting millions of records. This uses a stream sorting techniquethat begins to send records within milliseconds and continues to stream results until the entire result set has been sorted and exported.

Solr中已经定义了这个requestHandler：

<requestHandler name="/export" class="solr.SearchHandler">
      <lst name="invariants">
        <str name="rq">{!xport}</str>
        <str name="wt">xsort</str>
        <str name="distrib">false</str>
      </lst>
      <arr name="components">
        <str>query</str>
      </arr>
    </requestHandler>

使用/export需要字段使用docValues建立索引：

使用docValues必须要有一个用来Sort的字段，且只支持下列类型：

Sort fields must be one of the following types: int,float,long,double,string

docValues支持的返回字段：

Export fields must either be one of the following types: int,float,long,double,string

使用Solrj来查询并获取数据：

SolrQuery params = new SolrQuery();
            params.set("q", timeQueryString);
            params.set("fq", queryString);
            params.set("start", 0);
            params.set("rows", Integer.MAX_VALUE);
            params.set("sort", "id asc");
            params.setHighlight(false);
            params.set("qt", "/export");
            params.setFields(retKeys);
            QueryResponse response = server.query(params);

一个Bug：

org.apache.solr.client.solrj.impl.HttpSolrClient$RemoteSolrException: Error from server at http://192.8.125.30:8985/solr/hotspot: Expected mime type application/octet-stream but got application/json.

Solrj没法正确解析出结果集，看了下源码，原因是Solr server返回的ContentType和Solrj解析时检查时不一致，Solrj的BinaryResponseParser这个CONTENT\_TYPE是定死的：

public class BinaryResponseParser extends ResponseParser {
        public static final String BINARY_CONTENT_TYPE = "application/octet-stream";

一时半会也不知道怎么解决这个Bug，还是自己写个Http请求并获取结果吧，用HttpClient写了个简单的客户端请求并解析json获取数据，测试速度：

String url = "http://192.8.125.30:8985/solr/hotspot/export?q=CollectTime%3A[2014-12-06T00%3A00%3A00.000Z+TO+2014-12-10T21%3A31%3A55.000Z]&sort=id+asc&fl=id&wt=json&indent=true";
        long s = System.currentTimeMillis();
        SolrHttpJsonClient client = new SolrHttpJsonClient();
        SolrQueryResult result = client.getQueryResultByGet(url);
        System.out.println("Size: "+result.getResponse().getNumFound());
        long e = System.currentTimeMillis();
        System.out.println("Time: "+(e-s));

同样的查询条件获取220296个结果集，时间为2s左右，这样的查询获取数据的效率和MySQL建立索引后的效果差不多，暂时可以接受。

因为Export fields只支持int,float,long,double,string这几个类型，如果你的查询结果只包含这几个类型的字段，那采用这种方式查询并获取数据，速度要快很多。

下面是Solr使用“/select”和“/export”的速度对比。

时间对比：

项目中如果用分页查询，就用select方式，如果一次性要获取大量查询数据就用export方式，这里没有采用MySQL对查询字段建索引，因为数据量每天还在增加，当达到亿级的数据量的时候，索引也不能很好的解决问题，而且项目中还有其他的查询需求。

我们来看另一个查询需求，假设要统计每个设备（deviceID）上数据的分布情况：

用SQL，需要33s：

SELECT deviceID,Count(*) FROM `tf_hotspotdata_copy_test` GROUP BY deviceID;

同样的查询，在对CollectTime建立索引之后，只要14s了。

看看Solr的Facet查询，只要540ms，快的不是一点点。

SolrQuery query = new SolrQuery();
    query.set("q", "*:*");
    query.setFacet(true);
    query.addFacetField("DeviceID");
    QueryResponse response = server.query(query);
    FacetField idFacetField = response.getFacetField("DeviceID");
    List<Count> idCounts = idFacetField.getValues();
    for (Count count : idCounts) {
        System.out.println(count.getName()+": "+count.getCount());
    }

时间对比：

<table style="max-width:100%; background-color:rgb(254,254,254); border-spacing:0px; width:605px; margin-bottom:1.5em; border-width:1px 1px 1px 0px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-color:rgb(221,221,221); border-right-color:rgb(221,221,221); border-bottom-color:rgb(221,221,221); border-left-style:initial; border-left-color:initial; font-size:14px; line-height:1.3em; color:rgb(51,51,51); font-family:&quot;Helvetica Neue&quot;,Helvetica,Tahoma,Arial,STXihei,&quot;Microsoft YaHei&quot;,微软雅黑,sans-serif"> 
 <tbody> 
  <tr style="line-height:1.3em"> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:0px; border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> 查询条件（统计）</p> </td> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:0px; border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> 时间</p> </td> 
  </tr> 
  <tr style="line-height:1.3em"> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> MySQL（无索引）</p> </td> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> 33s</p> </td> 
  </tr> 
  <tr style="line-height:1.3em"> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> MySQL（有索引）</p> </td> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> 14s</p> </td> 
  </tr> 
  <tr style="line-height:1.3em"> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> Solrj（Facet查询）</p> </td> 
   <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top:1px solid rgb(221,221,221); border-left:1px solid rgb(221,221,221); font-size:14px"> <p style="margin-top:0px; margin-bottom:0.75em; font-size:16px; line-height:1.7em; text-indent:1em"> 0.54s</p> </td> 
  </tr> 
 </tbody> 
</table>

如果我们要查询某台设备在某个时间段上按“时”、“周”、“月”、“年”进行数据统计，Solr也是很方便的，比如以下按天统计设备号为1013上的数据：

String startTime = "2014-12-06 00:00:00";
        String endTime = "2014-12-16 21:31:55";   
        SolrQuery query = new SolrQuery();
        query.set("q", "DeviceID:1013");
        query.setFacet(true);
        Date start = DateFormatHelper.ToSolrSearchDate(DateFormatHelper.StringToDate(startTime));
        Date end = DateFormatHelper.ToSolrSearchDate(DateFormatHelper.StringToDate(endTime));
        query.addDateRangeFacet("CollectTime", start, end, "+1DAY");
        QueryResponse response = server.query(query);
    
        List<RangeFacet> dateFacetFields = response.getFacetRanges();
        for (RangeFacet facetField : dateFacetFields{
            List<org.apache.solr.client.solrj.response.RangeFacet.Count> dateCounts= facetField.getCounts();
            for (org.apache.solr.client.solrj.response.RangeFacet.Count count : dateCounts) {
                System.out.println(count.getValue()+": "+count.getCount());
            }
        }

2014-12-06T00:00:00Z: 58

2014-12-07T00:00:00Z: 0

2014-12-08T00:00:00Z: 0

2014-12-09T00:00:00Z: 0

2014-12-10T00:00:00Z: 3707

2014-12-11T00:00:00Z: 8384

2014-12-12T00:00:00Z: 7803

2014-12-13T00:00:00Z: 2469

2014-12-14T00:00:00Z: 142

2014-12-15T00:00:00Z: 34

2014-12-16T00:00:00Z: 0

Time: 662