查询性能“逆袭”—巧用“BETWEEN”

查询性能逆袭巧用BETWEEN 一、问题的背景 有若干个表（暂且认为4个表），这几个表的主要字段一致，即有若干个公共的字段，其中ID具有唯一性，并且四个表中唯一，类型为String.。如下面所示： table1（ID，Name，Type，CommonField1，CommonField12，Table1Ot

查询性能“逆袭”—巧用“BETWEEN”

 

一、问题的背景

有若干个表（暂且认为4个表），这几个表的主要字段一致，即有若干个公共的字段，其中ID具有唯一性，并且四个表中唯一，类型为String.。如下面所示：

 

table1（ID，Name，Type，CommonField1，CommonField12，……Table1OtherField1……）

 

table2（ID，Name，Type，CommonField1，CommonField12，……Table2OtherField1……）

 

table3（ID，Name，Type，CommonField1，CommonField12，……Table3OtherField1……）

 

table4（ID，Name，Type，CommonField1，CommonField12，……Table4OtherField1……）

 

至于这个表为什么设计成这样，是否达到基本范式？这个问题这里暂时不考虑，可以简单的认为这四个表存储了四类非常类似的数据。上级系统有这样的查询需求，暂且叫分页查询，类似一个Session的概念：

 

1、 需要查询的时候会发一个创建查询的消息，里面带了查询条件，并且说明了每次查询返回的数量。

 

2、 应答创建查询的消息中要返回符合查询结果的总数目，并且返回第一页数据。当然如果满足结果的数目小于每次查询的数据，直接将满足的结果返回。

 

3、 如果满足结果的总量大于每次查询的数量，即需要分页查询，那么，上级系统会发查询消息来查询第二页、第三页……依次类推，直至查完。

 

4、 查询完毕后，上级系统会发送一个删除查询的消息。

 

上面的查询条件是非常复杂的，没有使用类似SQL的语法解析，而是通过一个数据结构和查询文档描述。

 

具体例子说明：假如上级系统发一个创建查询的消息，假定总共有10万数据，每次查询为1000条，查询实现端收到该报文后，解析查询条件，查询得到有4999条满足查询条件的数据，那么第一次返回的报文中返回前1000条，并且注明满足条件的总数为4999。接着上级系统会发送查询消息，指定查询第二个1000条，依次查询，直到查到最后的999条，上级系统再发一个删除查询表示查询结束。对上级查询者而言，表的数量对其而言是透明的，默认就是这几个表中查询。查询示意图如下：

 

 

 

 

二、解决问题的思路

1、 缓存查询结果：根据查询条件，一次性将查询结果缓存，在内存中分页返回查询结果。最大的问题是如果满足结果数量级大的情况下很容易OOM。

 

2、  缓存查询符合条件的ID：根据查询条件，创建查询的时候先查询到所有符合条件的ID，将ID分页，每次查询请求来的时候用ID去查询本次需要的数据。构造SQL需要有技巧。

 

3、 缓存查询条件：每次来查询的时候根据查询条件来查询到所有数据，然后根据要求返回某一页。每次要重复解析查询条件，处理起来十分麻烦，也存在数据的不一致性。

 

实际实现中采用了第二种方案（方案二的问题在方案三中同样存在），处理起来简单又方便。但是问题又出现了，如果在一个查询“会话”中，创建查询的时候查询得到4999个ID，然后分5次返回，每次返回的数据中都需要根据1000个ID查询得到这1000行数据，那么我们很自然的会想到用”sellect * form XXXwhere in（…..）”由于SQL语句的长度限制，1000个ID就要查几十次，4999数据的话总共要查近百次，如果数据上几十万的话，查询次数更是吓人，查询时间将大部分花在不停的执行SQL查询上。那怎么来绕开这个弯子？

 

三、减少SQL查询次数的思路

先看个例子，假如数据库中只有这样是个ID的数据【1,2,3,4,5,6,7,8,9,10】，如果要查询ID为【2,3,4,7,8,10】，那么是否可以有这样两种思路来组织SQL查询条件

 

1、 where id in（2,3,4,7,8,10）；

 

2、 where id between ‘2’ and ‘4’ orbetween ‘7’ and ‘8’ or between ‘10’ and ‘10’

 

上面的第二种用between方式构建查询条件，SQL语句基本上可以用一个完成？最坏的情况（也是SQL语句最长的情况）也就是需要查询的数据是ID【1,3,5,7,9】的情况。在实际数量巨大的情况下几乎不会出现这样“最糟糕的情况”，一般需要查询的数据是连续的，因此基本上任何查询都可以用一个between搞定。而如果用第一种方式构造查询条件，那么势必需要查询多次。

 

四、实际处理

以上面的例子为例，如果总数是10万条，符合条件的是4999条，那么可以这么做：

 

1、 将所有的10万个ID取出来缓存在内存并进行排序。

 

2、 将4999个查询结果ID缓存进行排序。

 

3、 每次查询1000（最后一次是999）数据时根据这1000ID和全部的10万个ID关系，通过某个算法构建between条件（这个算法不再详述），最终构造一个SQL查询条（注意只是一个SQL），这样一次查询就可以得到这1000条数据。

 

上面的缓存ID的方案是可行的，一般不会产生OOM。结果就是几百次查询优化成了一个查询，查询性能直接逆袭！