SQL优化案例-单表分页语句的优化（八）

秒速五厘米 2022-04-15 03:38 206阅读 0赞

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

如下错误的分页框架：

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70][]

逻辑读22558

创建索引：

create index idx_did on S_Depart (departId,0);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 1][]

因为没有过滤条件，走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;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 2][]

可以看到时间消耗的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;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 3][]

到这里，我们知道了分页语句的优化首先要建立在正确的分页框架上，那么当需要分页的语句中有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);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 4][]

建立索引的优化法则中我们知道，要在索引中尽量的包含所需要的数据，减少回表的次数，同时利用索引排序的特性，消除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故障诊断、性能调优、数据库备份恢复、复制、高可用方案及迁移经验。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70]: /images/20220415/88e1da7d5bd54f408fd3fcb7e0d3db73.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 1]: /images/20220415/04d96724b3d14acfaa8d557efe2f44c0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 2]: /images/20220415/748c5425e10e470fb905a22fe347e66f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 3]: /images/20220415/d14373fc55724f45bee0f19c0525a8c7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 4]: /images/20220415/659724e062014a949968f284e8cf4db4.png