Oracle 表和索引的设计

逑3084

表设计， 是灵活扩展性及性能之间的一个折中。 为了达到灵活扩展以及将来不可预知的加载问题， 那么表设计就应该跟模型设计一样， 能够被演化到第三方的标准形式， 当然， 用户为了核心的业务性能需求， 可以选择性的忽略这点 。

        这些技术的例子有很多， 例如storing tables pre-joined,the addition of derived columns, and aggregate values。 Oracle提供了许多方法存储aggregates and pre-joined数据通过clustering and materialized view功能。这些特性应该在一个表的简单设计中采用。
     
      同样， 我们应该主意关注那些核心的业务表， 这样能够更有效的达到性能要求， 当然， 如果那些非核心表引起了性能瓶颈的话， 因该立刻去修改它们。

     索引的设计， 是一个大的迭代过程， 它基于应用中SQL的产生。 当然， 我们首先应该对那些有主键限制， 以及肯定经常被用得的字段敏感。 比如姓名之类的。随着开发和真是数据测试的进展， 一些确定的查询语句有性能提高要求时， 建立一个更好的index是一个好的方案。  下面列出重建索引时 ， 应该考虑的设计方案。

  

• Appending Columns to an Index or Using Index-Organized Tables
  
• Using a Different Index Type
  
• Finding the Cost of an Index
  
• Serializing within Indexes
  
• Ordering Columns in an Index
  

Appending Columns to an Index or Using Index-Organized Tables
从执行计划中， 提高查询速度的一个最简单的方法就是减少表的访问 （减少逻辑I/O）, 这个可能通过给查询中的column加上索引。 这些columns是在select 对象column, 以及任何需要join和排序的columns. 这个方案，对那些在线及时反映的系统来说特别有用， 因为减少了 I/O的时间。  这个方案， 应该在系统有适当数据， 第一次测试时采用。

对这个技术， 非常多的一种形式是建立一个 index-organized table (IOT)。 但是， 你必须注意， 在IOT中不断增长的leaf， 并不会消弱减少I/O的目标。

Using a Different Index Type
有很多的索引类型， 不同的类型有不用的适用场景。 下面， 介绍下不同 索引的性能特点。

1. B-Tree Indexes
这是标准的索引类型， 非常适合主键和highly-selective （值分布广泛）索引，B树索引可用于获取那些被索引排序过的数据。

2. Bitmap Indexes  
位图索引适合值分布小的数据 （例如性别）， 通过压缩技术， 用最小的I/O可以生成大量的rowids (能够定位到数据的location). 在non-selective的column上联合位图索引， 可以有效地使用AND 和OR操作 （最小的I/O读取大量的rowid）。 对于使用count()的查询， 位图索引也能加快速度， 因为查询能够在索引中获得。


3.Function-based Indexes
       这些索引， allow通过B树索引, 从数据库中经过函数获取数据。 对于使用nulls， 函数索引有一些限制， 而且需要查询优化器开启。
       函数索引， 对于在composite columns上的查询， 特别有效率。比如 (销售价格 - 折扣) x 数量。 销售价格 ， 折扣，  数量都是数据库中的列。 另外一个例子就是UPPER 函数。

4. Partitioned Indexes
Partitioning a global index allows partition pruning to take place within an index access, which results in reduced I/Os. By definition of good range or list partitioning, fast index scans of the correct index partitions can result in very fast query times.

5. Reverse Key Indexes
这个索引的设计是为了降低不断插入的应用中的索引污点。 对于插入的性能， 这种索引是很卓越的。但是， 它不能用于index range scans。

查找索引的Cost
创建和维护索引结构， 是会非常高价的。 它会消耗磁盘空间， CPU， 以及 I/O。 设计者必须要确定索引的好处超过缺点。

使用这么一个简单的法则去判断维护一个索引的cost:
每个维护的索引， 在insert, update, delete上的时间消耗， 差不多是实际DML语句的三倍。这个意思就是说， 如果你插入一个有三个索引的表， 它所消耗的时间， 大约是没有索引的10倍。 对于DML， 特别是插入频繁的应用， 索引的建立需要慎重。需要在查询和插入之间有个折中。
      

Serializing within Indexes
Use of sequences, or timestamps, to generate key values that are indexed themselves can lead to database hotspot problems, which affect response time and throughput. 这是由于，线形增长的key能导致一个right-growing index。为了避免这个问题， try to generate keys that insert over the full range of the index. This results in a well-balanced index that is more scalable and space efficient. You can achieve this by using a reverse key index or using a cycling sequence to prefix and sequence values.

Ordering Columns in an Index

在创建索引时， 设计者需要灵活的在上面创建规则。 这需要看应用环境， 可以使用下面两种方法去对索引的列排序。

• 把频繁查询的列放在前面。 这个方法， 在大部分时候都适用。 因为它能够使最小的I/O（访问真是的rowid）提供最快的访问速度。这个技术主要用在主键和大量的扫描查询。
  
• 通过clustering 和排序， 减少I/O。 在大范围的扫描时， I/Os can usually be reduced by ordering the columns in the least selective order, or in a manner that sorts the data in the way it should be retrieved.详情参考：http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/data_acc.htm#g27061
  

使用视图
视图可以加快和简化应用设计。 一个简单的视图， 对于那些主要就是涉及查询， 显示， 收集和存储得开发人员， 可以掩饰复杂的数据模型。

但是， 在视图提供一个clean 编程接口的同时， 能够导致非优化， resource-intensive的查询。 一个worst type使用view ， 就是当一个视图引用另外一个视图， 且用在查询的join中。 在大多数情况下， 开发人员之间使用数据库表查询就可以了， 因为视图的内在特性， 它是优化器很难选定一个最优的执行计划。

SQL执行效率
在任何系统开发的设计和架构中， 应该是开发人员关注SQL的执行效率。 为了实现这个， 开发环境需要支持下面的特点：
#

• 优秀的连接管理连接数据库， 是一个expensive且 highly unscalable 操作。  因此， 应该尽可能的减少并发的数据库连接。一个简单的应用中， 用户在系统初始化时候连接是比较理想的。 但是， 在一个基于web或者多层的应用中， 设计者应该使用数据库连接池且并不为每个用户重新建立连接。
  
• 好的Cursor 使用和管理SQL的执行有很多步骤， 包括语义分析， 安全检查，生成执行计划以及加载共享的结构到shared pool。 其中，解析有硬解析和软解析。
  硬解析： SQL在首次提交的时候， 在shared pool中不能被匹配。 Hard parses are the most resource-intensive and unscalable, because they perform all the operations involved in a parse.
  
  软解析： SQL 在首次提交， 但在shared pool中能被匹配。 这个匹配的（前面用户执行的结果）内容就作为结果。SQL 被共享能提高性能。 但软解析不是理想的。 因为还是需要语义和安全检查。
  
  所以，解析应该尽可能的简化。 开发者应该设计SQL解析一次， 多次使用。 这通过cursors完成。  有经验的开发者， 应该非常首先重开和重执行cursors的概念。
  
  开发者， 应该使SQL在共享池中。 为了达到这目的， 在查询中， 使用变量邦定可以实现这功能。例如：  
  Statement with bind variables:
  
  SELECT * FROM employees
    WHERE last_name LIKE :1;