引言

基于数据库构建的应用是否成功，这取决于如何使用数据库。了解数据库:

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

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

1. 如果无法用一条SQL语句完成，就通过PL/SQL实现（不过，尽可能少用PL/SQL！）。
2. 如果在PL/SQL中也无法做到（因为它缺少一些特性，如列出目录中的文件），可以试试使用Java存储过程来实现。

I  数据库访问表数据的方式

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

1. 全表扫描 : 顺序地访问表中每条记录。

Oracle将数据保存在数据块(database block)中，通过一次读入多个数据块的方式优化全表扫描。

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

2. 通过ROWID访问表

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

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

II 索引概念

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

2.1 索引的代价

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

1. 索引需要磁盘空间存储
2. 执行数据修改操作（INSERT、UPDATE、DELETE）产生索引维护
3. 在数据处理时需额外的回退空间
4. 索引和数据不在一个数据块上，使用索引会增加系统I/O

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

2.2 索引的分类

功能分类: –Primary key(主关键字) –Foreign key(外键) –Unique Index(唯一索引) –Index(一般索引)

按存储方法分类:

–B-树索引 –位图索引 –HASH索引 –索引编排表 –反转键索引 –分区索引 –本地和全局索引

按对象分类:

• 单列索引（表单个字段的索引）
• 多列索引（表多个字段的索引）

• 函数索引（对字段进行函数运算的索引）

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

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

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

Unique Index(唯一索引):创建唯一索引的字段或组合字段在数据上必须唯一。

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

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

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

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

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

单列索引:以单个字段建立的索引

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

函数索引（对字段进行函数运算的索引）:在索引中使用函数或者表达式，这些函数可以是Oracle的函数，也可以是用户自己的PL/SQL函数等 ，函数索引是大小写敏感的。

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

1. 表主关键字: 数据库自动建立索引

2. 字段唯一性约束:

3. 主从表关联:因为在查询中经常会和其他表关联查询

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

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

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

1. 表记录很少的表

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

2. 经常更新的表（不稳定表）: 数据更新会造成索引的维护，影响数据更新时间。

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

III 查看SQL的执行计划

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

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

窗体分为两大部分，上半部分是sql区，可以在此输入要分析的sql，下半部分是SQL的执行计划。

Optimizer goal可以选择优化器，其中：

1. First_Rows:基于CBO的优化器，侧重于返回一条结果记录，大多数使用Single Block IO（类似于Index Scan）
2. All_Rows: 基于CBO的优化器，侧重于返回所有结果记录，大多数使用Mutil Block IO（类似于Full Table Scan）
3. Rule: 基于RBO的优化器
4. Choose: 根据表是否做过分析来选择使用RBO还是CBO。如果有一个或以上的表做过表分析，则使用CBO；如果都没有做过表分析，则使用RBO。用导航按键查看执行步骤，执行步骤是从上到下，从内到外。

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

1. Rule基于规则-- Rule Based Optimizer
2. Cost基于成本-- Cost Based Optimizer
3. Choose选择性。

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

3.1 Rule（基于规则--Rule Based Optimizer）

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

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

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

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

3.2 Cost（基于成本--Cost Based Optimizer）

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

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

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

语法：

analyze   table   table_name   estimate   statistics   sample   x   percent;

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

analyze   table   table_name   compute   statistics;

--完全计算法

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

3.3 Choose（选择性）

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

IV 索引的扫描分类

• 索引唯一扫描(Index unique scan)
• 索引范围扫描(Index range scan)
• 索引全扫描(Index full scan)
• 索引快速扫描(Index fast full scan)
• 索引跳跃扫描(Index skip scan)

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

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

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

Select * from emp where id = 2343;

4.2 索引范围扫描(index range scan)

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

• 在唯一索引列上使用了range操作符(> < <> >= <= between)
• 在组合索引上，只使用部分字段进行查询，导致查询出多行
• 对非唯一索引列上进行的任何查询。

4.3 索引全扫描(index full scan)

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

select id from emp order by id;

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

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

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

select id from emp;

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

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

V 表的关联

在ORACLE中表的关联有三种：

1. Sort merger join（排序合并关联）
2. Nested Loops（嵌套循环）
3. Hash join（哈希关联）

5.1 Sort Merger join（排序合并关联）

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

执行过程：

1. 扫描第一个表返回结果集，然后按照关联列排序；
2. 扫描第二个表返回结果集，然后按照关联列排序；
3. 两边已排序的行被放在一起执行合并操作。

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

5.2 Nested Loops（镶嵌循环）

多出现于小表和大表关联

执行过程：

1. 扫描第一个表（驱动表）Row source1；
2. 循环Row source1；
3. 探索第二个表（被探查表）Row rource2。
4. 重复2～3直到循环完Row source。

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

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

5.3 Hash join（哈希关联）

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

执行过程：

1. 扫描第一个表（驱动表）返回数据集，构建Hash表；
2. 读取第二个表的一条数据，和内存中的数据进行匹配；

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

VI 小结

1. Oracle的优化器
2. 索引的不同分类
3. 表关联的方式
4. 使用Explain Plan Window查看执行计划

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

在PC机上正确创建索引并正确使用索引的情况下，上千万条数据的单表查询应该在0.1秒左右，如果使用多表关联查询不应该超过0.6秒，但是对于分组查询可能超过1秒。

如果你写的查询超过这些参考值，说明你写的SQL还有优化的可能。

see also :数据库的高伸缩性(并行控制)

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

查询死锁:

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;