SQL优化案例-单表分页语句的优化(八)

分页语句分为单表分页与多表关联分页。分页语句的优化首先要选对分页框架,错误的分页框架会导致扫描表中全部的数据,正确的分页框架会利用到rownum的count stopkey特性较少数据访问。

作者 姚崇·沃趣科技高级数据库技术专家
出品 沃趣科技

分页语句分为单表分页与多表关联分页。分页语句的优化首先要选对分页框架,错误的分页框架会导致扫描表中全部的数据,正确的分页框架会利用到rownum的count stopkey特性较少数据访问。

如下错误的分页框架:

SELECT *
FROM (SELECT T.*, ROWNUM RN
      FROM (SELECT * FROM S_DEPART ORDER BY DEPARTID) T)
WHERE RN >= 1
AND RN <= 10;

逻辑读22558

创建索引:

create index idx_did on S_Depart (departId,0);

因为没有过滤条件,走index full scan,性能反而不如table access full。大量回表逻辑读翻倍,执行计划中有index full scan就不是最优的。

| 下面代入正确的分页框架

SELECT *
FROM (SELECT *
      FROM (SELECT A.*, ROWNUM RN
              FROM (SELECT * FROM S_DEPART ORDER BY DEPARTID) A)
     WHERE ROWNUM < 10)
WHERE RN >= 1;

可以看到时间消耗的sort order by上,那么我们在order by上建立索引消除排序操作。

SELECT *
FROM (SELECT *
      FROM (SELECT A.*, ROWNUM RN
              FROM (SELECT * FROM S_DEPART ORDER BY DEPARTID) A)
     WHERE ROWNUM < 10)
WHERE RN >= 1;

到这里,我们知道了分页语句的优化首先要建立在正确的分页框架上,那么当需要分页的语句中有where条件的时候怎么建立索引呢?

SELECT *
FROM (SELECT *
      FROM (SELECT A.*, ROWNUM RN
              FROM (SELECT *
                      FROM S_DEPART
                     WHERE DEPARTNAME = 'SALES'
                     ORDER BY DEPARTID) A)
     WHERE ROWNUM < 10)
WHERE RN >= 1;

create index idx_name_idon S_DEPART(DEPARTNAME,departId,0);

建立索引的优化法则中我们知道,要在索引中尽量的包含所需要的数据,减少回表的次数,同时利用索引排序的特性,消除order by,因此就需要建立组合索引。组合索引的建立方式where列做引导列,order by部分放在索引后,反之则要边扫描索引边过滤数据,产生的逻辑读是要高于前者,当order by部分有多列数据时候,索引也都需要包含order by的列,并且注意acs和desc。

另外还有其他一些情形,当where条件中既有等值连接又有非等值连接,建立索引的规则如下,为什么非等值连接要放在order by后呢?

因为当非等值条件在前时,按照索引查询出的数据,order by的列要重新排序。

create index idx_name on table_name(=,order by,<>,0);

当where条件中没有等值连接,需要按照如下规则建立索引:

create index idx_name on table_name(order by,<>,0);

当分页语句中没有排序条件时,只需要在where列上建立相关索引即可。

后续我们将会分析多表关联分页语句的优化方法。

| 作者简介

姚崇·沃趣科技高级数据库技术专家

熟悉Oracle、MySQL数据库内部机制,丰富的Oracle、MySQL故障诊断、性能调优、数据库备份恢复、复制、高可用方案及迁移经验。

发表评论

电子邮件地址不会被公开。 必填项已用*标注