SQL优化（索引、物化视图、分区)

最新推荐文章于 2023-10-09 04:00:19 发布

Mr_蜗牛

最新推荐文章于 2023-10-09 04:00:19 发布

阅读量3.1k

点赞数

分类专栏：数据库 oracle

本文链接：https://blog.csdn.net/u010634288/article/details/54657600

版权

数据库同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

oracle

2 篇文章 0 订阅

订阅专栏

一般sql优化有几种解决方案：

一、索引

二、物化视图

三、分区

一、索引

索引的说明：

索引是与表相关的一个可选结构，在逻辑上和物理上都独立于表的数据，索引能优化查询，不能优化DML操作，Oracle自动维护索引，频繁的DML操作反而会引起大量的索引维护。

如果SQL语句仅访问被索引的列，那么数据库只需从索引中读取数据，而不用读取表，

如果该语句同时还要访问除索引列之外的列，那么，数据库会使用rowid来查找表中的行，

通常，为检索表数据，数据库以交替方式先读取索引块，然后读取相应的表块。

索引的目的是：主要是减少IO,这是本质,这样才能体现索引的效率。

1 大表，返回的行数<5%

2 经常使用where子句查询的列

3 离散度高的列

4 更新键值代价低

5 逻辑AND、OR效率高

6 查看索引在建在那表、列：

select * from user_indexes;
  select * from user_ind_columns;

建立索引的方式(这里只列举了常用的索引)：

1）唯一索引：唯一索引值键值不重复。

create unique index emp_idx on emp1(empno);
--drop index empno_idx;

2)一般索引：索引值键值可以重复

create index emp_idx on emp1(empno)

3)组合索引：绑定2个或更多列的索引

create index job_dep_idx on emp1(job,deptno);
--drop index job_dep_idx ;

索引碎片问题：

查看执行计划：

set autotrace traceonly explain;

索引碎片问题：由于对基表做DML操作，导致索引表块的自动更改操作，尤其是基表的delete操作会引起index表的index_entries的逻辑删除，注意只有当一个索引块中的全部index_entry都被删除了，才会把这个索引块删除，索引对基表的delete、insert操作都会产生索引碎片问题。

在Oracle文档里并没有清晰的给出索引碎片的量化标准,Oracle建议通过Segment Advisor(段顾问）解决表和索引的碎片问题，如果你想自行解决，可以通过查看index_stats视图，当以下三种情形之一发生时，说明积累的碎片应该整理了（仅供参考）。

 1.HEIGHT >=4   
2 PCT_USED< 50%   
3 DEL_LF_ROWS/LF_ROWS>0.2

这里举个例子，我创建一个表里面循环插入一百万条记录。

建立表、索引：

create table t (id int);
create index ind_1 on t(id);

执行插入记录：

begin
  for i in 1..1000000 loop
insert into t values (i);
if mod(i, 100)=0 then
commit;
end if;
  end loop;
end;
create table t (id int);
create index ind_1 on t(id);

执行下面语句分析索引：

analyze index ind_1 validate structure;
select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats;

上图所示表示我这张表里面的索引并没有满足
1.HEIGHT >=4 2.PCT_USED< 50% 3.DEL_LF_ROWS/LF_ROWS>0.2三者的任意一个条件所以我不需要整理索引碎片

我再执行下面语句，删除七十万条数据

delete t where rownum<700000;

上图所示其中一个条件已经满足需要整理的条件了

我们可以执行下面语句来进行碎片的整理

alter index ind_1 rebuild [online] [tablespace name];

整理完成之后，该表并未满足索引碎片需要整理的条件。

二、物化视图
物化视图和普通视图的区别：
1、物化视图自动刷新或者手动刷新的，普通视图不用刷新。
2、物化视图也可以直接update，但是不影响基础表，对普通视图的update反映到基础表上。
3、物化视图主要用于远程数据访问，物化视图中的数据需要占用磁盘空间，普通视图中不保存数据。
具体语法：

create materialized view [view_name]  
refresh [fast|complete|force] 
 [ 
on [commit|demand] | start with (start_time)  next (next_time)
 ] 
as 
{创建物化视图用的查询语句}

1.refresh [fast|complete|force] 视图刷新的方式:
fast: 增量刷新.假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据.为了记录这种变化，建立增量刷新物化视图还需要一个物化视图日志表。create materialized view log on （主表名）。
complete:全部刷新。相当于重新执行一次创建视图的查询语句。
force: 这是默认的数据刷新方式。当可以使用fast模式时，数据刷新将采用fast方式；否则使用complete方式。
2.MV数据刷新的时间：
on demand:在用户需要刷新的时候刷新，这里就要求用户自己动手去刷新数据了（也可以使用job定时刷新）
on commit:当主表中有数据提交的时候，立即刷新MV中的数据；

start ……：从指定的时间开始，每隔一段时间（由next指定）就刷新一次；

举个例子：

创建2张表，并且插入数据：

create table test_a 
(
aid varchar2(10),
aname varchar2(10),
bid varchar(2)
)
create table test_b
(
 bid varchar2(10),
 bname varchar2(10) 
)
insert into test_a values('1','张三','1');
insert into test_a values('2','李四','2');
insert into test_a values('3','王五','3');
insert into test_b values('1','三班');
insert into test_b values('2','四班');
insert into test_b values('3','五班');

创建一个物化视图，使用fast模式（数据刷新将采用fast方式；否则使用complete方式）。当主表中有数据提交的时候，立即刷新MV中的数据；

create materialized view test_a_b
refresh force
on commit  
as 
select  a.aid,a.aname,b.bid,b.bname from test_a a,test_b b where a.bid=b.bid

如果创建物化视图出现权限不足的情况需要先登录到sysdba然后进行授权

select * from test_a_b;

这里我进行更新test_a之后物化视图的数据就马上发生改变了

update test_a a set a.aname='张三丰' where a.aid=1

create materialized view test_a_b
refresh force
on demand
start with sysdate next sysdate+1/1440
as 
select  a.aid,a.aname,b.bid,b.bname from test_a a,test_b b where a.bid=b.bid ;

表示从当前时间开始，每隔一分钟我进行一次刷新物化视图。

增量刷新（刚刚我创建物化视图都是全量刷新），现在我要做的是fast: 增量刷新

create materialized view log on test_a with rowid;
create materialized view log on test_b with rowid;


create materialized view mv_test_a_b
refresh force
on demand
start with sysdate next sysdate+1/1440
as 
select a.rowid as arowid,b.rowid as browid,a.aid,a.aname,b.bid,b.bname from test_a a,test_b b where a.bid=b.bid ;

这里我

select * from mv_test_a_b;

这样我们就完成了定时的增量刷新。

三、分区
Oracle提供了多种分区。
1、Range分区
2、hash分区
3、list分区
4、复合分区
5、间隔分区

1、Range分区(范围分区)

create table sale
  (  
  product_id varchar2(10),  
  sale_count number(10,2)
)  
partition by range(sale_count)  
(  
  partition p1 values less than(1000),
  partition p2 values less than(2000), 
  partition p3 values less than(3000)
)  
insert into sale values(1,500);
insert into sale values(2,1500);
insert into sale values(3,2500);

查看分区情况：

select * from user_tab_partitions;

查看分区数据：

select * from sale partition(p1);

以上分区如果插入3500或者更大的数的时候是无法插入的，这个时候我们需要添加分区。

修改分区：
添加：alter table tableName add partition p4 values less than(maxvalue);
删除：alter table tableName drop partition p4;

添加之后再执行insert into sale values(4,3500);
然后查询分区数据：

现在product_id为1的数据库，SALE_COUNT数据为500
我想把SALE_COUNT修改为1500，是无法做到的

这个时候我们只需要修改：

alter table tableName enable row movement;

1.2 分区索引：
分区之后虽然可以提高效率，但也解决是提高了数据的范围，所以我们在有必要的情况下需要建立分区索引，分区索引可以分为2类：一类叫local，一类叫global。
local：在每个分区建立索引。
global：是在全局建立索引，这种方式方式和建立不建立分区一样一般不使用，还有一种就是自定义数据区间的索引，也叫做前缀索引，这个非常有意义，自定义区域值主要必须要maxvalue。
还有要注意一点：在分区上建立的索引必须是分区字段。

local 方式语法

create  index idxname on table(field) local;

查看分区索引：

select * from user_ind_partitions;

global 自定义全局（前缀索引）索引方式方法：

create  index idxname on table(field)global ;
partition by range(field)
(
partition p1 values less than(value);
partition p2 values less than(maxvalue);
)

global 全局索引方式方法：

create  index idxname on table(field) global ;

2、hash分区(散列分区)

create table sale
  (  
  product_id varchar2(10),  
  sale_count number(10,2)
)  
partition by range(sale_count)  
(  
  partition p1  
  partition p2 
  partition p3  
)

散列分区建立之后，我们插入数据之后，他会将所有的数据在分区中平均分布。

3、list分区(列表分区)：根据具体字段的值进行分区，比如我们的数据库sale_count字段的数据是500,1500,2500,那么这些数据就被分在三个不同的分区里面。

create table sale
  (  
  product_id varchar2(10),  
  sale_count number(10,2)
)  
partition by range(sale_count)  
(  
  partition p1  values('500'),  
  partition p2 values('1500'),  
  partition p3  values('2500'),  
)

4、复合分区（范围-散列分区，范围-列表分区）

1)、范围-散列分区

create table sale
  (  
  product_id varchar2(10),  
  sale_count number(10,2)
)  
partition by range(sale_count)  
subpartition by hash(product_id)  
(  
  partition p1 values less than(1500)  
            (  
               subpartition sp1,subpartition sp2  
            ),  
  partition p2 values less than(maxvalue)  
            (  
               subpartition sp3,subpartition sp4  
            )  
);

上面的语句是：范围-散列分区，我根据sale_count进行范围分区，然后进行hash分区

2)、范围-列表分区

create table MobileMessage
(
 ACCT_MONTH VARCHAR2(6), -- 帐期 格式：年月 YYYYMM
 AREA_NO VARCHAR2(10), -- 地域号码 
 DAY_ID VARCHAR2(2), -- 本月中的第几天 格式 DD
 SUBSCRBID VARCHAR2(20), -- 用户标识 
 SVCNUM VARCHAR2(30) -- 手机号码
)
partition by range(ACCT_MONTH,AREA_NO) subpartition by list(DAY_ID)
(
  partition p1 values less than('200705','012')
  (
    subpartition shangxun1 values('01','02','03','04','05','06','07','08','09','10'),
    subpartition zhongxun1 values('11','12','13','14','15','16','17','18','19','20'),
    subpartition xiaxun1 values('21','22','23','24','25','26','27','28','29','30','31')
  ),
  partition p2 values less than('200709','014')
  (
    subpartition shangxun2 values('01','02','03','04','05','06','07','08','09','10'),
    subpartition zhongxun2 values('11','12','13','14','15','16','17','18','19','20'),
    subpartition xiaxun2 values('21','22','23','24','25','26','27','28','29','30','31')
  ),
  partition p3 values less than('200801','016')
  (
    subpartition shangxun3 values('01','02','03','04','05','06','07','08','09','10'),
    subpartition zhongxun3 values('11','12','13','14','15','16','17','18','19','20'),
    subpartition xiaxun3 values('21','22','23','24','25','26','27','28','29','30','31')
  )
)


insert into MobileMessage values('200701','010','04','ghk001','13800000000');
insert into MobileMessage values('200702','015','12','myx001','13633330000');
insert into MobileMessage values('200703','015','24','hjd001','13300000000');
insert into MobileMessage values('200704','010','04','ghk001','13800000000');
insert into MobileMessage values('200705','010','04','ghk001','13800000000');
insert into MobileMessage values('200705','011','18','sxl001','13222000000');
insert into MobileMessage values('200706','011','21','sxl001','13222000000');
insert into MobileMessage values('200706','012','11','tgg001','13800044400');
insert into MobileMessage values('200707','010','04','ghk001','13800000000');
insert into MobileMessage values('200708','012','24','tgg001','13800044400');
insert into MobileMessage values('200709','014','29','zjj001','13100000000');
insert into MobileMessage values('200710','014','29','zjj001','13100000000');
insert into MobileMessage values('200711','014','29','zjj001','13100000000');
insert into MobileMessage values('200711','013','30','wgc001','13444000000');
insert into MobileMessage values('200712','013','30','wgc001','13444000000');
insert into MobileMessage values('200712','010','30','ghk001','13800000000');
insert into MobileMessage values('200801','015','22','myx001','13633330000');

列表分区这里我就无耻的复制黏贴了一份别人的代码：原文地址：http://love-flying-snow.iteye.com/blog/573303
分区p1查询结果如下：

4、间隔分区：Interval Partitioning 是一种分区自动化的分区，可以指定时间间隔进行分区。
Interval Partitioning 实际上是一种range分区的引申，最终实现了range分区自动化。
语法：

create table interval_sale
(sid int,sdate timestamp)

partition by range(sdate)
interval(numtoyminterval(1,'month'))
(
  partition p1 values less than (TIMESTAMP '2014-02-01 00:00:00.00')
)
insert into interval_sale values(1,TO_TIMESTAMP('2014-01-07 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF'));
insert into interval_sale values(1,TO_TIMESTAMP('2014-02-05 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF'));
insert into interval_sale values(1,TO_TIMESTAMP('2014-03-06 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF'));

从2014-02-01 00:00:00.00以前建立一个分区，在2014-02-01 00:00:00.00以后每个月建立一个分区。
查看分区情况：

select * from user_tab_partitions;

select * from interval_sale partition(SYS_P22); --查看分区数据

Mr_蜗牛

关注

0
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
SQL优化（索引、物化视图、分区)

一般sql优化有几种解决方案：一、索引二、分区三、物化视图四、并行查询一、索引索引的说明：索引是与表相关的一个可选结构，在逻辑上和物理上都独立于表的数据，索引能优化查询，不能优化DML操作，Oracle自动维护索引，频繁的DML操作反而会引起大量的索引维护。如果SQL语句仅访问被索引的列，那么数据库只需从索引中读取数据，而不用读取表，如果该语句
复制链接

扫一扫