2

DB2分页SQL优化（宝，我优化了分页，每分钟都想你的夜）

 2 years ago
source link: https://segmentfault.com/a/1190000040180600
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

最近，项目中的一个 DB2分页查询很慢 ，组长将此分页的优化分派给了我；然后一顿优化（乱操作）后，将DB2分页查询耗时降到了比较满意的情况，[ 开森 ]；

然后马上将结果报告了组长，组长查看我的演示后，发现分页查询确实快了很多，可以达到让人“接受的程度”，比优化之前的 页面一直转圈等待 相当可以了呀；

注：优化后的演示环境与发现分页查询慢时的环境基本一致，包括库中数据量、DB2的配置、服务器的配置等。

首先通过查看执行计划发现，SQL语句中的索引都利用上了，那么暂时就不是 索引 的问题了，最后发现是 SQL语句存在问题 ，对SQL进行了优化，查询就快了；

下面就简单描述下DB2的分页SQL是怎么进行优化的，binggou 走起；

本文主线：

  • 必备前提：select 查询SQL的逻辑执行顺序
  • 优化历程：DB2分页SQL优化过程
  • 知识扩展：分享一些导致分页查询慢的特别案例

必备前提：

在对查询SQL语句进行优化时，需要知道其逻辑执行顺序，这对进行SQL优化有很大帮助的；

SQL的 逻辑执行顺序 ，指的是SQL语句按照一定的规则，一整条语句应该如何执行，每一个关键字、子句部分在什么时刻执行；

1、一个简单select查询SQL的逻辑执行顺序如下：

  1. 先执行 from table join table ，获取要操作的表及关联的表，对它们计算笛卡尔积，得到一个虚拟表v1；
  2. 然后执行 on 条件 ，对虚拟表v1进行连接查询筛选，得到一个虚拟表v2；
  3. 然后执行 where 条件 ，对虚拟表v2中的数据进行筛选，得到虚拟表v3；
  4. 然后在执行 group by 语句，将虚拟表v3中的数据进行分组，得到虚拟表v4；
  5. 再执行 Having 条件过滤 ，对分组后的虚拟表v4进行条件筛选，得到虚拟表v5；
  6. 然后对虚拟表v5的数据执行 select 投影列 ，只保留select中的展示的字段，得到虚拟表v6；
  7. 然后再执行 order by 排序语句 ，对虚拟表v6中的数据进行排序，得到虚拟表v7；
  8. 最后才执行 limit 等分页语句（限制条数） ，得到虚拟表v8 。

2、简单分析下查询慢的分页SQL：

上面简单描述了下查询SQL的逻辑执行顺序，下面就来分析下查询慢的分页语句的逻辑执行顺序；

SELECT * FROM
 (
   SELECT B.*, ROWNUMBER() OVER() AS RN FROM
    (
        select
           ts.name,
           ts.age,
           tc.class_name,
           ts.describe,
           ts.birthday
        from t_student ts
        LEFT JOIN t_class tc on tc.class_id = ts.class_id
        where 
            ts.age = 23
            AND ts.birthday >= TO_DATE('2020-06-12','yyyy-MM-dd')
            AND ts.birthday <= TO_DATE('2020-07-15' , 'yyyy-MM-dd')
       order by ts.birthday desc
    ) AS B
)AS A WHERE A.RN BETWEEN 10 AND 20

t_student 表中存在二级索引：index(age, birthday)

t_class 表的主键：class_id

2.1、SQL逻辑执行顺序分析：
  1. 首先将对 t_student ts 和 t_class tc 表计算笛卡尔积，如果ts表中的数据量是10，tc表中数据量是10，那么笛卡尔积就是 10*10 = 100，得到虚拟表v1
  2. 然后进行 on条件筛选，得到虚拟表v2
  3. 然后进行 where条件筛选，得到虚拟表v3
  4. 然后执行select 投影列 ，只保留select中的展示的字段，得到虚拟表v4
  5. 然后根据birthday字段进行排序，得到虚拟表v5
  6. 最后对虚拟表v5中的数据根据行号进行分页，获取当前页需要的数据，得到最终结果集
2.2、通过分析得到的逻辑执行顺序得知：
  • 第一步中计算笛卡尔积，如果两个表中数据量都很大的话，那笛卡尔积就会非常大，对后面的 on条件筛选、where条件筛选都会慢很多，这里可以想办法优化下
  • select投影列中，一定要按需返回字段，特别是前端页面没有用到的大字段，一定不要加到投影列中，像此SQL中 describe个人描述 字段页面中就不需要展示
  • 最后的分页语句是在筛选出全部的数据后才根据行号筛选出当前页需要的数据，心想，可以不可以，不要筛选出全部数据，而是筛选到当前页所需要的数据即可

优化历程：

通过上面SQL的逻辑执行顺序分析得知有三处地方可以尝试进行下优化，看看查询是不是变快了；

1、减小笛卡尔积：

因为后面的where条件筛选中，都是对主表 t_student 表进行的筛选，所以可以提前使用where条件对t_student表进行筛选，得到虚拟表，

然后使用这个虚拟表和连接的表 t_class 计算笛卡尔积，此时笛卡尔积已经小很多了；

SQL语句如下：

SELECT * FROM
 (
   SELECT B.*, ROWNUMBER() OVER() AS RN FROM
    (
    select
       ts.name,
       ts.age,
       tc.class_name,
       ts.describe,
       ts.birthday
    from (select class_id, name, age, describe, birthday
          from t_student
         where
              age = 23
              AND birthday >= TO_DATE('2020-06-12','yyyy-MM-dd')
              AND birthday <= TO_DATE('2020-07-15' , 'yyyy-MM-dd')
       ) ts
  LEFT JOIN t_class tc on tc.class_id = ts.class_id
  order by ts.birthday desc
  ) AS B
)AS A WHERE A.RN BETWEEN 10 AND 20

2、不需要的大字段禁止返回：

在select投影列中，将不需要的大字段 describe 不要放入其中，因为如果放到投影列中的话，查询时会造成其占用较多的缓冲池空间，如果导致缓冲池空间满了的话，就要进行磁盘IO了，磁盘IO非常耗时的；

还有就是如果响应中带有大字段的内容的话，在进行网络IO时，会造成传输速度变慢，页面加载数据时也会变慢的；

SQL语句如下：

SELECT * FROM
 (
   SELECT B.*, ROWNUMBER() OVER() AS RN FROM
    (
    select
       ts.name,
       ts.age,
       tc.class_name,
       ts.birthday
    from (select class_id, name, age, birthday
          from t_student
         where
              age = 23
              AND birthday >= TO_DATE('2020-06-12','yyyy-MM-dd')
              AND birthday <= TO_DATE('2020-07-15' , 'yyyy-MM-dd')
       ) ts
  LEFT JOIN t_class tc on tc.class_id = ts.class_id
  order by ts.birthday desc
  ) AS B
)AS A WHERE A.RN BETWEEN 10 AND 20

3、分页时限制数据的筛选：

分页查询都是根据 pageNo（页号），pageSize（一页的数量）进行的所需页面数据筛选；

例如，当前页面是第二页的话，每页展示数据10条，当前页数据的筛选，就是 rownum between ((pageNo-1) pageSize) and (pageNo pageSize) ，那么在分页前的数据筛选时，可以使用 fetch first (page * rows) rows only 限制筛选的数据量，别全部筛选出来了，而是最多只筛选到当前页面中最大的行号前即可；

由于使用了 fetch first (page * rows) rows only ，减少了很多的筛选操作，速度会快很多，特别是点击前几页时，速度都是非常快的，可能越往后翻页，响应会相应慢些，但是分页时，基本都是查看前几页的，后面的几乎很少看，所以此时效果看起来是十分好的

SQL语句如下：

select
     ts.name,
     ts.age,
     tc.class_name,
     ts.birthday
  from (
      select aa.class_id, aa.name, aa.age, aa.birthday
       from(
           select class_id, name, age, birthday, ROWNUMBER() OVER () AS RN
            from t_student
            where
                age = 23
                AND birthday >= TO_DATE('2020-06-12','yyyy-MM-dd')
                AND birthday <= TO_DATE('2020-07-15' , 'yyyy-MM-dd')
            order by birthday desc
            fetch first  20 rows only
       ) aa where aa.RN BETWEEN 10 AND 20
     ) ts
LEFT JOIN t_class tc on tc.class_id = ts.class_id

至此DB2分页SQL已经基本完成了优化，查询速度提升了一大半了，但是此分页SQL还有优化的余地，可以通过修改SQL语句和索引 index(age, birthday) 进一步提升查询速度；

下面在知识扩展部分简单描述下怎么再次进行优化；

知识扩展：

1、分页SQL的进一步优化：

先来看下面这段SQL语句：

select class_id, name, age, birthday, ROWNUMBER() OVER () AS RN
   from t_student
   where
      age = 23
      AND birthday >= TO_DATE('2020-06-12','yyyy-MM-dd')
      AND birthday <= TO_DATE('2020-07-15' , 'yyyy-MM-dd')
      order by birthday desc
     fetch first  20 rows only

这段SQL语句执行时会使用到 二级索引 index(age, birthday) ，由于在此索引上无法查询到全部的字段值，所以需要回表查询 class_id, name 字段的值，由于多了回表操作，会导致查询变慢，所以此时要想办法避免回表；

1.1、避免回表方法一：

只修改索引，将 二级索引 index(age, birthday) 改为 index(age, birthday，name，class_id) 即可，SQL语句不用变动；

但是索引字段变多后，对新增、更新、删除SQL的操作影响比较大，会导致其执行耗时变长，因为需要对索引进行维护；所以如果这两张表写操作比较多的话， 不建议直接修改索引 ；

1.2、避免回表方法二：

此方法其实也是需要回表的，但是此时回表次数会比之前回表的次数少的多得多，几乎可以忽略不计；

此方法不需要修改索引，而是对SQL语句进行修改， 首先进行 二级索引 index(age, birthday) 查询时不再需要返回 class_id, name, age, birthday 字段了，而是只返回 t_student 表的 主键 stu_id ，此时就不需要回表了；

然后会使用到二级索引 index(age, birthday)中birthday默认排序，最后根据 fetch first 20 rows only 只返回前20条数据，此时返回了20条 主键 stu_id 值，然后可以根据主键stu_id左外连接t_student，获得其它的字段值；

此时相当于只需要回表20次而已，不需要全部进行回表了；

SQL语句如下：

select stu2.class_id, stu2.name, stu2.age, stu2.birthday, ROWNUMBER() OVER () AS RN
  from (
    select stu_id
     from t_student
     where
        age = 23
        AND birthday >= TO_DATE('2020-06-12','yyyy-MM-dd')
        AND birthday <= TO_DATE('2020-07-15' , 'yyyy-MM-dd')
        order by birthday desc
       fetch first  20 rows only ) stu1
  left join t_student stu2 on stu1.stu_id = stu2.stu_id

最终优化后的分页SQL如下：

select
     ts.name,
     ts.age,
     tc.class_name,
     ts.birthday
  from (
      select aa.class_id, aa.name, aa.age, aa.birthday
       from(
           select stu2.class_id, stu2.name, stu2.age, stu2.birthday, ROWNUMBER() OVER () AS RN
            from (
              select stu_id
               from t_student
               where
                  age = 23
                  AND birthday >= TO_DATE('2020-06-12','yyyy-MM-dd')
                  AND birthday <= TO_DATE('2020-07-15' , 'yyyy-MM-dd')
                  order by birthday desc
                 fetch first  20 rows only ) stu1
             left join t_student stu2 on stu1.stu_id = stu2.stu_id
       ) aa where aa.RN BETWEEN 10 AND 20
     ) ts
LEFT JOIN t_class tc on tc.class_id = ts.class_id

2、分页查询时的特殊情况：

在进行分页查询时，一般是先查询下符合条件的总数据量，这个数据量用于分页，得到分页数的；

那么我们可能会遇到一些非常特别情况，查询总数据量的SQL如下：

 select
   count(*)
from t_student ts
LEFT JOIN t_class tc on tc.class_id = ts.class_id
where
    ts.age = 23
    AND ts.birthday >= TO_DATE('2020-06-12','yyyy-MM-dd')
    AND ts.birthday <= TO_DATE('2020-07-15' , 'yyyy-MM-dd')

上面这个SQL语句其实可以进行优化的，它其实不用关联 t_class 表的， 具体原因如下：

  • 一是因为where条件中都是对主表的 t_student 的筛选
  • 二是主表 t_student 与 外表 t_class 中数据关系是一对一

直接查询主表中满足条件的数据即可，因此可以将多余的表连接去掉，这会大大提升SQL的查询速度；

八卦下，分析分析为什么有人会这么写呢？

猜想主要是为了图方便，直接复制的分页SQL语句进行改的 [哭笑不得] ；

❤ 点赞 + 评论 + 转发 哟

如果本文对您有帮助的话，请挥动下您爱发财的小手点下赞呀，您的支持就是我不断创作的动力，谢谢啦！

您可以微信搜索 【木子雷】 公众号，大量Java学习干货文章，您可以来瞧一瞧哟！


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK