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故障诊断、性能调优、数据库备份恢复、复制、高可用方案及迁移经验。
点击查看招聘信息
相关链接
初相识 | 全方位认识 information_schema
沃趣微讲堂 | Oracle集群技术(四):集群初始化资源层
更多干货,欢迎来撩~