欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-06-14 17:08:28
...

查询性能逆袭巧用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条,上级系统再发一个删除查询表示查询结束。对上级查询者而言,表的数量对其而言是透明的,默认就是这几个表中查询。查询示意图如下:

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

二、解决问题的思路

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。结果就是几百次查询优化成了一个查询,查询性能直接逆袭!