当前位置:Gxlcms > mysql > 开发成功的oracle数据库

开发成功的oracle数据库

时间:2021-07-01 10:21:17 帮助过:36人阅读

前言 1.基于数据库(或依赖于数据库)构建的应用是否成功,这取决于如何使用数据库。另外,从我的经验看,所有应用的构建都围绕 着数据库。如果一个应用未在任何地方持久地存储数据,很难想象这个应用真的有用。 2.应用总是在“来来去去”,而数据不同,它们

前言

1.基于数据库(或依赖于数据库)构建的应用是否成功,这取决于如何使用数据库。另外,从我的经验看,所有应用的构建都围绕
着数据库。如果一个应用未在任何地方持久地存储数据,很难想象这个应用真的有用。
2.应用总是在“来来去去”,而数据不同,它们会永远存在。从长远来讲,我们的目标并不是构建应用,而应该是如何使用这些应用
底层的数据。
3. 开发小组的核心必须有一些精通数据库的开发人员,他们要负责确保数据库逻辑是可靠的,系统能够顺利构建。如果已成事实(应
用已经部署)之后再去调优,这通常表明,在开发期间你没有认真考虑这些问题。

正文

了解数据库

1.数据库的体系结构,数据库如何工作,以及有怎样的表现。
2.并发控制是什么,并发控制对你意味着什么。
3.性能、可扩缩性和安全性都是开发时就应该考虑的需求,必须适当地做出设计,不要指望能碰巧满足这些需求。
4.数据库的特性如何实现。某个特定数据库特性的实际实现方式可能与你想象的不一样。你必须根据数据库实际上如何工作(而不
是认为它应该如何工作)来进行设计。
5.数据库已经提供了哪些特性,为什么使用数据库已提供的特性要优于自行构建自己的特性。
6.为什么粗略地了解SQL还不够,还需要更深入地学习SQL。
7.DBA和开发人员都在为同一个目标努力,他们不是敌对的两个阵营,不是想在每个回合中比试谁更聪明。

如果可能,尽量利用一条SQL语句完成工作。


1. 如果无法用一条SQL语句完成,就通过PL/SQL实现(不过,尽可能少用PL/SQL!)。
2.如果在PL/SQL中也无法做到(因为它缺少一些特性,如列出目录中的文件),可以试试使用Java存储过程来实现。不过,有了Oracle9i 及以上版本后,如今需要这样做的可能性极小。
3.如果用Java 还办不到,那就在C 外部过程中实现。如果速度要求很高,或者要使用采用C 编写的一个第三方API,就常常使用这种做法。
4.如果在C外部例程中还无法实现,你就该好好想想有没有必要做这个工作了。

SQL优化--基本概念

数据库访问表数据的方式

Oracle虽然可以通过各种执行方式存取数据,但是在最后访问数据表时只有两种方式。

?全表扫描

全表扫描就是顺序地访问表中每条记录。Oracle将数据保存在数据块(database block)中,通过一次读入多个数据块的方式优化全表扫描

数据块是数据库存取数据的最小I/O单位,只有将数据块读到内存中才能查找数据。

?通过ROWID访问表

ROWIDOracle数据库的一个伪列,唯一标识数据表中的数据行,数据一旦插入数据库,该行的ROWID将不能再被改变。

ROWID是访问数据表的最快方法,通过ROWIDOracle可以直接定位到数据块上。

索引除了包含索引列值外还存储对应行的ROWID,所以,提供了快速访问ROWID的方法,因此,基于索引的查询性能很高。

索引的代价

索引虽然是提高数据查询最有效的方法,但是无效的索引会造成数据库空间的浪费,甚至大大降低查询性能。

1.索引需要磁盘空间存储

2.执行数据修改操作(INSERTUPDATEDELETE)产生索引维护

3.在数据处理时需额外的回退空间

4.索引和数据不在一个数据块上,使用索引会增加系统I/O

建议一张表的索引不要超过3个,但是对于稳定表可以多建立索引提高查询速度。

索引的分类

?索引按功能分类

Primary key(主关键字)

Foreign key(外键)

Unique Index(唯一索引)

Index(一般索引)

?索引按存储方法分类

B-树索引

位图索引

HASH索引

索引编排表

反转键索引

分区索引

本地和全局索引

?索引按对象分类

单列索引(表单个字段的索引)

多列索引(表多个字段的索引)

函数索引(对字段进行函数运算的索引)

?Primary key(主关键字)

主关键字是保证在一个表中的数据唯一,在创建主关键字时,数据库自动在主关键字上创建唯一索引。一个表只能创建一个主关键字。

?Foreign key(外键)

外键是表和表之间建立主从关系,又叫父子关系,外键只能关联到主表的主键或唯一索引上(因为关系型数据库不支持多对多关系)。外键并不自动创建索引。

对于一对多的两个表,外键建立在多的表上。必须满足有子必有父的关系,即插入数据必须先插入主表数据后才能插入子表数据,删除主表数据前必须删除子表数据。

?Unique Index(唯一索引)

创建唯一索引的字段或组合字段在数据上必须唯一。

?Index(一般索引)

一般索引没有数据约束限制,目的是为了加快查询速度。

不能对字段或组合字段重复创建索引,组合字段重复是指字段和字段顺序完全相同。

?B-树索引

B-树索引是最常用的索引,其存储结构类似书的索引结构,有分支和叶两种类型的存储数据块,分支块相当于书的大目录,叶块相当于索引到的具体的书页。一般索引及唯一约束索引都使用B-树索引。

?位图索引

位图索引储存主要用来节省空间,减少数据块的访问,它采用位图偏移方式来与表的行ID号对应,采用位图索引一般是重复值多的表字段。位图索引在OLTP(数据事务处理)中用得比较少,因为OLTP会对表进行大量的删除、修改、新建操作。在OLAP(数据分析处理)中应用位图有优势,因为OLAP中大部分是对数据库的查询操作,而且一般采用数据仓库技术,所以大量数据采用位图索引节省空间比较明显。

注:B-树索引中包含ROWIDOracle可以在行级别上锁定索引。位图索引被存储为压缩的索引值,是一个范围内的ROWID,因此ORACLE必须针对一个给定值锁定所有范围内的ROWID,极易造成死锁。

单列索引

以单个字段建立的索引

多列索引

已多个字段组合建立的索引

函数索引(对字段进行函数运算的索引)

在索引中使用函数或者表达式,这些函数可以是Oracle的函数,也可以是用户自己的PL/SQL函数等 ,函数索引是大小写敏感的。

在什么情况下应该建立索引

表主关键字

数据库自动建立索引

字段唯一性约束

主从表关联

因为在查询中经常会和其他表关联查询

经常查询字段或组合查询字段

查询中排序或分组的字段

索引是排序的,所以,排序字段或分组字段如果通过索引去访问将大大提高查询速度

在什么情况下不应该建立索引

表记录很少的表

数据库使用索引,必须先访问索引表,再通过索引表访问数据表,一般索引与数据表不在同一个数据块,Oracle至少要读取数据块两次。如果表数据很少,Oracle会将所有的数据一次读出,处理速度显然会比用索引快。

经常更新的表(不稳定表)

数据更新会造成索引的维护,影响数据更新时间。

数据重复且分布均匀字段

对于大数据表,如果一个字段只有少量的值,并且分布平均,建立该字段的索引一般不会提高数据库的查询速度。

SQL优化--索引概念

通过Developer看索引使用

Developer提供了Explain plan Window图形窗口,可以方便地看到SQL的执行计划。

启动Explain plan Window有两种方法,第一种在SQL Window下输入sql,按F5,第二种,使用菜单new/Explain plan Window

?窗体介绍

窗体分为两大部分,上半部分是sql区,可以在此输入要分析的sql,下半部分是SQL的执行计划。Optimizer goal可以选择优化器,其中:

First_Rows:基于CBO的优化器,侧重于返回一条结果记录,大多数使用Single Block IO(类似于Index Scan

All_Rows: 基于CBO的优化器,侧重于返回所有结果记录,大多数使用Mutil Block IO(类似于Full Table Scan

Rule: 基于RBO的优化器

Choose: 根据表是否做过分析来选择使用RBO还是CBO。如果有一个或以上的表做过表分析,则使用CBO;如果都没有做过表分析,则使用RBO

用导航按键查看执行步骤,执行步骤是从上到下,从内到外。

优化器

目前Oracle的优化器共有三种:

Rule基于规则-- Rule Based Optimizer

Cost基于成本-- Cost Based Optimizer

Choose选择性。

数据库在默认安装情况下,Oracle使用Choose优化器。

Rule(基于规则--Rule Based Optimizer

From子句从外侧向内侧的顺序检索表,Where子句从下向上解析条件。

根据From规则,在多表查询时,将返回结果集最少的表作为基础表,写在From最外侧(并不是最小的表,应该是查询限定最强的表,一般情况下是查询主体表),然后根据表之间的限定依赖关系依次从外侧写到内侧。Oracle执行时,首先扫描最外侧表(驱动表),并对结果集进行排序,然后扫描内侧表(被探测表),将第二个表检索出的结果集(用Row Source2表示)与第一个表中相应结果集(用Row Source1表示)进行合并后再次向内侧表扫描合并,直到全部表被检索合并,返回结果集。

根据Where规则,先写表连接,表连接从内侧向外侧的顺序写,可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾(结合From规则,末尾的查询条件应该是最外侧的表)。写查询条件时应该按照使用的索引字段顺序从上写到下。

当查询表有N个,表关联应该是N-1个。

Cost(基于成本--Cost Based Optimizer

CBO是依赖表的统计信息来选择最低成本的执行计划, 这些统计使用ANALYZE命令完成,在使用CBO之前必须将Table分析好。

CBO最多只能排列2000种表的排列关系,也就是说当超过6个以上的表(27次方大于2000)进行关联时,CBO就不能把所有的排列关系都计算到,会遗漏一些,所以这个时候生成的执行计划据可能不是最优的。

对于数据变化比较大的表,你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性,以提高系统效率。

语法:

analyze table table_name estimate statistics sample x percent;

--抽样估算法,使用x%抽样率对table_name表分析

analyze table table_name compute statistics;

--完全计算法

对表作完全计算所花的时间相当于做全表扫描,抽样估算法由于采用抽样,比完全计算法的生成统计速度要快,如果不是要求要有精确数据的话,尽量采用抽样分析法。建议对表分析采用抽样估算,对索引分析可以采用完全计算。

Choose(选择性)

根据表是否做过分析来选择使用RBO还是CBO。如果有一个以上的表做过表分析,则使用CBO;如果都没有做过表分析,则使用RBO

索引的扫描分类

?索引唯一扫描(Index unique scan)

?索引范围扫描(Index range scan)

?索引全扫描(Index full scan)

?索引快速扫描(Index fast full scan)

?索引跳跃扫描(Index skip scan)

索引唯一扫描(Index unique scan)

通过唯一索引返回单行数据的查询方法称为索引唯一扫描。如果存在UNIQUE PRIMARY KEY 约束(它保证了语句只存取单行)的话,Oracle经常实现唯一性扫描。

EMP表创建了Id主关键字,如果使用Id作为条件查询,Oracle使用Index Unique Scan

Select * from emp where id = 2343;

索引范围扫描(index range scan)

使用一个索引返回多行数据的查询方法称为索引范围扫描。使用索引范围扫描的情况有:

在唯一索引列上使用了range操作符(> < <> >= <= between)

在组合索引上,只使用部分字段进行查询,导致查询出多行

对非唯一索引列上进行的任何查询。

索引全扫描(index full scan)

全索引扫描只发生在CBO模式下。当CBO根据统计数值判断全索引扫描比全表扫描更有效时,才进行全索引扫描,而且此时查询出的数据都必须从索引中可以直接得到。

select id from emp order by id;

因为查询字段属于索引字段同时需要索引字段排序,Oracle使用Index full scan

索引快速扫描(index fast full scan)

扫描索引中的所有的数据块,与 index full scan很类似,但是一个显著的区别就是它不对查询出的数据进行排序,即数据不是以排序顺序被返回。在这种存取方法中,可以使用多块读功能,也可以使用并行读入,以便获得最大吞吐量与缩短执行时间。

select id from emp;

索引跳跃扫描(Index skip scan)

当查询条件没有使用组合查询的前导列,优化器可以使用跳跃扫描,跳跃扫描比全扫描性能高。

表的关联

在ORACLE中表的关联有三种

Sort merger join排序合并关联)

Nested Loops嵌套循环)

Hash join哈希关联)

Sort Merger join排序合并关联)

多出现于大表和大表以索引的方式连接。

执行过程:

1.扫描第一个表返回结果集,然后按照关联列排序;

2.扫描第二个表返回结果集,然后按照关联列排序;

3.两边已排序的行被放在一起执行合并操作。

此连接方式建立在排序的基础上,而排序操作消耗的系统资源很大,所以,这种方式对于结果集已经排序的连接比较有效。

Nested Loops镶嵌循环

出现于小表和大表关联

执行过程:

1.扫描第一个表(驱动表)Row source1

2.循环Row source1

3.探索第二个表(被探查表)Row rource2

4.重复23直到循环完Row source

此连接方式的关键是驱动表的返回数据集要少,同时被探查表的匹配要有索引支持。另外,此连接是最快得到第一个匹配行的方式,所以,可以实现快速的响应时间,因为此连接不必等待所有的连接操作处理完才返回数据。

如果驱动表的数量比较大,查询性能不如全表扫描.

Hash join哈希关联)

只能用于相等连接,且只能在CBO优化器模式下,多出现于小表和大表关联

执行过程:

1.扫描第一个表(驱动表)返回数据集,构建Hash表;

2.读取第二个表的一条数据,和内存中的数据进行匹配;

此连接方式对于CPU的消耗比较大,并取决于内存的大小,因为Oracle在内存中建立Hash表。

SQL优化--小结

有关SQL的优化涉及的范围很广,相关的知识需要在实践中不断总结和积累。学习完本节需要掌握的知识:

Oracle的优化器

索引的不同分类

表关联的方式

如果使用Explain Plan Window查看执行计划

DML时应该用不同的sql看性能优劣再确定sql

PC机上正确创建索引并正确使用索引的情况下,上千万条数据的单表查询应该在0.1秒左右,如果使用多表关联查询不应该超过0.6秒,但是对于分组查询可能超过1秒。如果超过你写的查询超过这些参考值,说明你写的SQL还有优化的可能。

数据库的高伸缩性--并行控制

死锁:

当两个事务需要一组有冲突的锁,而不能将事务继续下去的话,就出现死锁

查询死锁

select b.username,b.sid,b.serial#,logon_time

from v$locked_objecta,v$session b

where a.session_id = b.sid order by b.logon_time;

也可以在Developer中可以使用tools\Sessions...中的Lock查看锁定情况

杀死死锁

SQL>alter system kill session 'sid,serial#';

--如果有ora-00031错误,则在后面加immediate;

SQL>alter system kill session 'sid,serial#' immediate;

总结

人气教程排行