Oracle的SQL性能优化 （二）基础知识

数据的存储

一般情况下，数据是以表的方式存储到数据库里，表的行越多，或者列越多，越会影响数据的读取速度。在插入数据的时候，数据是按操作顺序插入的，而不是按主键等值的顺序插入的。这是为了防止插入数据时做太多操作而影响性能，例如，当主键是一个字符串时，可能已经有上百万数据，后来有条数据要插入到最前面，不可能因此把上百万数据在数据库中移动位置。同样，在一行数据中，列数据也是顺序存储的，如果列太多，就会影响整个表的存储，因此Oracle会把这种数据拆开，多出的数据存到其他地方，但这种处理方式将会严重影响数据读取的性能。

 

IOT表和聚集表的数据行的存储位置由其主索引值决定，而与插入顺序无关。但它们用得较少，而且在需要进行SQL优化时，大部分情况下涉及到表的关联、与主索引无关的字段，因此可以与标准类型的表同样考虑。

 

为了减少数据的存储空间，Oracle可以对数据进行压缩，或者不存储空的字段，或者把相同值的字段合并，但这些措施并不影响基本的优化模式。

 

全表扫描查询数据是最基本的查询方式，速度也最慢，但大部分情况下只需要读取表的部分数据，因此需要采用一些加速措施。

 

索引

索引是最常用的加速措施，通俗地说，它就是一个字母检字表，字母就是索引，字就是要查的数据。只不过字母检字表只用到了首字母，而索引可以指定表的任意一个或多个字段。在Oracle中，索引以B树的方式存储索引字段对应的键值与该行数据的ROWID。如果键值的顺序与ROWID的顺序越近似，那么索引的效率就越高，遗憾地是一般情况下只有按顺序整数值插入的主键才有如此高的效率。

 

在加索引时特别要注意能让索引更加精确地定位到数据。例如常见的员工表中有部门、性别、生日、是否在职等字段，由于经常需要按部门显示当前员工，所以索引就应该加到部门和是否在职两个字段上。

 

需要注意的是，索引占存储空间，而且每次新增、更新或删除数据时需要更新索引，从而影响这些语句的性能，所以建索引时需要慎重。

 

分区

在数据达到上千万级别时，用分区是一个不错的选择，虽然Oracle的分区特性需要额外收费。例如，费用数据用日期段分区是一个不错的选择，因为经常按月、年统计，而且大多仅统计最近的数据。

 

使用分区的同时也可以使用索引，但只有分区内的索引才能同时利用分区和索引两个特性。

 

分区使用起来很简单，在查询条件中加上分区字段条件就可以了，不过需要DBA配合，因为分区的管理维护是他们的职责。

 

为什么需要我们优化

在大量甚至海量数据中寻找到所需要的数据，并不是一件容易的事情，特别是当数据保存在很慢的硬盘上（内存虽然快一些，但是也没有达到可以不考虑速度的程度），且需要同时支持事务和并发的时候。

以附录的数据库结构为例，在EMP表中按DEPT_ID和EMP_NAME查询数据，数据库会检查表结构与表统计信息，根据查询条件发现索引DEPT_ID可以使用，从而解析SQL，在检查数据是否已读取到内存中，如果没有，则到硬盘上读取，再按EMP_NAME过滤，得到最后的结果，这就是完整的执行过程。可以看出，SQL的执行过程可能会发生变化的。

作为一个比较流行和成功的数据库，Oracle肯定是会在执行前对SQL进行优化的，但非常遗憾的是并不能自动优化到最佳，原因大致包括以下几点：

1．数据库结构设计得很差

2．SQL本身写得很差（这个差不是指因开发人员水平差而写出的SQL很差，而是没有考虑周全写得差），例如可以加上的条件没有加（虽然看起来重复了），把该JOIN的表放到子查询里了

3．没有加合适的索引，例如表emp经常按DEPT_ID查询，但实际上并没有在这个字段上建立索引

4．优化需要资源，包括CPU资源、表的统计信息等，ORACLE只找到了次优方案

5．数据分布不好。例如新成立的公司基本上没有离职员工，那么是否离职字段里的数据就基本相同，这个字段用于SQL条件就没有意义。

6．查询条件的限制。如果SQL语句用到了参数，不同参数情况下执行情况可能有所差异，所以应该找总体最佳的那个，但是Oracle会按第一次的参数优化，不一定能够根据新的参数优化成功。

7． 最重要的一条就是Oracle没有那么聪明

另外，提高SQL的命中率也是一个好的方式，不过主要是DBA的工作

---