一个实现IQ结果集分页SQL查询的调优

   近日遇到一个SQL调优问题：用户的一个应用为了实现IQ数据结果集的分页，写了一个查询，用来对一个有4千多万记录的表中返回一百六十多万记录的结果集进行分页(每页100条记录)。取一页数据大约20秒左右，用户觉得太慢了。

   出于保密的考虑，这里用tpchdb(我的博文中有多其的详细描述)来模拟问题和相应查询，然后跟大家分享对其进行优化的方法。希望能对大家实现类似功能时有所帮助。

 

1. 模拟问题

 

(1) 机器环境

    用于模拟问题的机器配置如下：

        Linux 4core/8G的虚拟机，CPU主频2.5GHZ
    IQ 版本 15.2 ESD#1

    IQ CFG配置： -iqmc=1024  -iqtc=1024

 

(2) 数据库

    采用的是TPCHDB，尺寸10GB。使用表lineitem。

    lineitem表总记录数: 59986052

   
(3) 问题查询

drop view if exists LINEITEM_VIEW;
create  view LINEITEM_VIEW

  as select rowid(lineitem) as idd , *

     from lineitem where 1=1  and l_linestatus = 'F';

SELECT * FROM ( SELECT RANK() OVER (ORDER BY idd ) rowid, * from LINEITEM_VIEW ) AS TMP WHERE TMP.rowid>0 AND TMP.rowid<=100;
   

    说明：使用l_linestatus = 'F'条件后，返回的结果集记录数为： 29998258. 然后对这个结果集进行分页，取出第1页的100条记录。在我的模拟环境下，这个查询执行的时间是113秒!看来是够慢的，比用户的问题查询还要慢不少。

 

2.进行优化

  对这个问题，尝试了几种方法，下面就分别介绍这些方法，最后得出优化的结论。

 

(1) 优化方法1：使用“rowid视图”

    下面是具体的优化实现：

drop view if exists LINEITEM_IDS_VIEW;
create view LINEITEM_IDS_VIEW as select rowid(lineitem) as idd  from lineitem where 1=1 and l_linestatus='F';
SELECT * FROM ( SELECT * FROM ( SELECT RANK() OVER (ORDER BY idd ) as rowid,idd FROM LINEITEM_IDS_VIEW) as TMP WHERE TMP.rowid>0 AND TMP.rowid<=100) A, lineitem B WHERE A.idd=rowid(B) AND B.l_linestatus='F';

 

     采用这个方法，执行时间提高到：15秒!.

     这个方法还可以该为不使用视图，而直接使用原表，具体的查询如下（查询执行时间不变)：

SELECT *
FROM (
  SELECT *
  FROM (
       SELECT RANK() OVER (ORDER BY idd ) AS rowid,rowid(lineitem) AS idd 
       FROM lineitem
       WHERE 1=1 AND l_linestatus='F'
  ) AS TMP_IDS
  WHERE TMP_IDS.rowid>0 AND TMP_IDS.rowid<=100
) A, lineitem B
WHERE A.idd=rowid(B) AND 1=1 AND B.l_linestatus='F';

 

      这个方法虽然提升了性能(大概提升了7倍多)感觉还是不够快，能不能进一步提升性能呢？经过尝试答案是可以的，请大家继续往下看。

 

(2) 优化方法2：使用“临时表”

    下面是这个方法的实现：

drop table if exists #table_rowid;
select rowid(lineitem) as rowid into #table_rowid from lineitem where 1=1 and l_linestatus = 'F';
select rowid(a) as rowid, a.rowid as idd, b.* from #table_rowid a,lineitem b where a.rowid = rowid(b) and 1=1 and b.l_linestatus='F' and rowid>0 and rowid<=100;

     采用这个方法，执行时间提高到：7.7秒! 提升了14倍多!