SQL优化案例-单表分页语句的优化（八）
分页语句分为单表分页与多表关联分页。分页语句的优化首先要选对分页框架，错误的分页框架会导致扫描表中全部的数据，正确的分页框架会利用到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故障诊断、性能调优、资料库备份恢复、复制、高可用方案及迁移经验。
推荐阅读：