深入剖析-关于分页语句的性能优化

分页语句是数据库开发和应用场景比较常见的需求,即按照特定的where条件进行过滤,然后在按照一个或者多个条件进行排序(如果不进行排序无法确执行时候无法返回相同的结果),最后取其中的前十行或者几十行。

一般分页语句消耗资源的地方有两点:

1、返回where条件过滤的结果集;

2、是对这个结果集进行排序,如果表过大同时对返回的结果集排序势必导致性能严重下降,针对分页语句性能低下的原因。

优化分页语句的核心思想:

1、创建效率高的索引返回尽量少的结果集排序;

2、因为索引是有序的,直接让数据库读取有序索引数据避免进行排序。

下面就针对不同的分页语句场景做如何优化。

1

正确的分页语句框架

分页场景一:针对分页语句的优化

首先我们要确定正确的分页语句框架,如果不按照正确的分页语句框架编写,会严重影响oracle选择正确的执行计划,正确的分页语句框架如下:

select * from ( select * from ( select a.*,rownum rn from ( 写好的sql语句 ) a ) where rownum<=m ) where rn>=n;

针对正确的分页语句和错误的分页语句会产生不同的执行计划,举例如下:

SQL> create table t as select * from dba_objects;

Table created.

SQL> select count(*) from t;

COUNT(*)

----------

497070

我们要写好的sql语句,如下:

select * from t where object_id<1000 返回前10行

针对这个sql语句,如果T表比较大的话,全表扫描就会非常消耗资源,我们针对object_id列创建索引即可。object_id列选择性非常高,对1000列进行排序性能也很高。创建如下索引:

create index t_idx_id on t(object_id);

然后再套用正确的分页语句框架,去执行高级执行计划:

Set linesize 200 pagesize 200

alter session set statistics_level=all;

select * from

( select * from

( select a.*,rownum rn from

( select * from t where object_id<1000 ) a

) where rownum<=10

) where rn>=0;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

采用正确的分页语句框架执行计划走的是T_IDX_ID索引,分页语句显示10行,执行计划中A-ROWS是10行。我们再看看采用其他错误分页语句显示的高级执行计划:

从错误的执行语句框架中我们可以看到,只要不是正确分页语句框架,oracle都会扫描<1000行最后显示10行数据。

针对上述语句的优化方案我们需要注意2点:

1、采用正确的分页语句框架;

2、针对where条件创建选择性高、效率高、索引返回少的结果集。

2

order by 分页

分页场景二: select * from t order by object_id 基于某列排序再分页

因为索引是排序的,我们可以利用索引的排序功能。在排序的分页语句中如果我们让分页语句直接按照升序或者降序扫描索引,这样的话就避免了全表扫描再排序的这种消耗资源操作。但是我们不确定object_id列是否有非空约束,由于索引是不存空值的,为了能够保证可能为空的object_id列也存在索引中,我们要在索引中添加一个组合列的常量索引,创建索引语句如下:

create index T_IDX_ID0 on t (object_id,0);

执行计划如下:

注:如果有些优化器没走索引可以在sql语句中增加一个索引的hint。

3

where等值条件过滤order by分页

分页场景三: select * from t where owner=’SYS’ order by object_id 有where条件过滤,然后基于某列排序再分页

这类分页语句我们要如何创建索引? 因为oracle对这类语句执行过程是先过滤where条件再排序,所以我们创建一个组合索引,给予OWNER,OBJECT_ID列组合(不能颠倒)

create index T_IDX_owner_ID on t (owner,object_id);

以此类推,where owner='SYS' order by object_id, object_name 这类基于 owner,object_id,object_name列建组合索引。

4

where不等值条件过滤order by分页

分页场景四: select * from t where where object_id<100000 order by owner 语句中的where条件是非等值,然后order by 其他列

这种情况我们就不能按照【分页场景三】进行优化,这类语句我们要分两种情况:

第一种where条件过滤后的结果集比较少,我们就采用【分页场景一】进行优化直接创建效率高的索引。 第二种where条件过滤后结果集比较多,这种我们就要 order by列在前,不等值列在后创建组合索引。

create index T_IDX_owner_ID on t (owner,object_id);

执行计划如下:

注:以上两种情况没有明显的分界线,特别是针对反对结果集比较适中的情况,还要综合比较两种创建索引方法谁的执行效率更高而采用哪种方案。

5

多表关联的分页语句

分页场景五:多表关联的分页如何优化select * from a,b where a.id=b.id order by a.id;

这类分页语句的优化思想是:既然是多表关联的分页语句,一定是走嵌套循环,不能走hash连接,最后要order by 某个表,一定是 order by的那个表做驱动表,同时驱动表的 order by列必须有索引。

6

无法优化的分页语句

无法优化的分页场景:但是如果是这种需求select * from a,b where a.id=b.id order by a.xx,b.xxx 这种需要对两个表排序情况下就无解了(为什么会搞基于两个表排序的需求,淘宝京东的商品排序大多数是只按照一种属性排序,如按照销量排序,按照价格排序,综合排序),这种情况需要干掉一个 order by 的列。

如果分页语句中有distinct, group by等需要把表数据全部扫描之后再去排序分页的,这种就无法用专门分页语句进行优化了。

7

总结

以上几种分页场景基本包含了目前主要的分页语句的需求和实现,不同的分页语句有一种或者几种优化方案。首先根据【优化场景一】的内容,先选择标准的分页语句框架,然后判断whete条件过滤后的结果集条数是多还是少。如果返回结果集少,则创建效率高的索引;如果返回结果集非常多,则考虑【分页场景二,三,四】,为分页语句创建一个排序过滤好的索引直接返回结果。对于【无法优化的分页场景】,就要考虑其他手段了,比如说调整分页需求,增加where过滤条件,对大表进行分区和瘦身等其他优化方案。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-12-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏架构师之旅

《干货系列》SQL语句-知无不言言无不尽

1.SQL , Structure Query Language,结构化查询语言,是一种申明式的语言。 SQL包括6部分: 1.DQL(Data Query ...

1795
来自专栏张狗蛋的技术之路

Mysql探索(一):B-Tree索引

MySQL是目前业界最为流行的关系型数据库之一,而索引的优化也是数据库性能优化的关键之一。所以,充分地了解MySQL索引有助于提升开发人员对MySQL数...

622
来自专栏小白客

学习SQL【2】-数据库与SQL

一:数据库是什么? 1:定义 ● 将大量数据通过计算机加工而成的可以进行高效访问的数据集合称为数据库(DB)。 ● 用来管理数据库的计算机系统称为数据库管理系...

3249
来自专栏desperate633

MySQL索引实现原理分析

目前大部分数据库系统及文件系统都采用 B-Tree(B 树)或其变种 B+Tree(B+树)作为索引结构。B+Tree 是数据库系统实现索引的首选数据结构。

643
来自专栏王旭的专栏

Web 开发 MYSQL 常用方法整理 (上)

最近在记录Web开发MYSQL常用方法,本篇记录希望数据插入、数据查询这两方面,,后面还会对数据更新以及如何在SQL中实现排行进行整理,希望对初期接触Web 开...

1960
来自专栏大闲人柴毛毛

数据库索引全面解析

索引是什么? 数据库中查找操作非常普遍,索引就是提升查找速度的一种手段。 索引分类 B+树索引 它就是传统意义上的索引,它是最常用、最有效的索引。 哈希索...

3216
来自专栏Kevin-ZhangCG

SQL优化总结之一

1845
来自专栏Java Edge

MySQL索引及其实现原理(基于MyISAM及InnoDB引擎)1 数据结构及算法基础2. MySQL索引实现3. 索引使用策略及优化Hash索引的特点Hash索引的限制

5178
来自专栏谢庆玲的专栏

MySQL 索引及查询优化总结

本文主要讨论 MySQL 索引原理及常用的 sql 查询优化。

8.3K2
来自专栏java进阶架构师

「mysql优化专题」你们要的多表查询优化来啦!请查收(4)

相信这内连接,左连接什么的大家都比较熟悉了,当然还有左外连接什么的,基本用不上我就不贴出来了。这图只是让大家回忆一下,各种连接查询。 然后要告诉大家的是,需要根...

392

扫描关注云+社区