数据库学习日志

1SQL 优化的意义

数据库作为信息管理的重要组成部分，主要用来存储大量信息，实现资源的贡献，供相关人员查阅用的。查询操作在各种操作中占的比例最大，与系统运行状态有着直接联系。如果数据量过大，会加大系统的压力，减缓操作速度，严重者可能引起系统瘫痪。操作过程其实就是SQL语句的访问过程，SQL语句是应用程序的具体体现，决定着系统的性能。对整个系统而言，其性能好坏主要取决于应用程序。而应用程序的优化经常从两个方面进行，一是源代码，二是SQL语句，其中，后者尤为关键，消耗的数据库资源最大，因此，为了保证数据库系统的高效运行，必须对SQL进行优化^[1]。

2SQL解析过程

首先来讲，数据库操作最频繁的就是查询的操作，如何实现高效的查询时数据库相关人员一直关注的重点。Oracle对于数据库语句的处理过程如下：（1）查看高速缓存。如果高速缓存中刚好有其他用户使用过这个查询语句，则服务器进行就会直接执行这个SQL语句，省去后续工作。（2）语句合法性检查。（3）语言含义检查。（4）获得对象解析锁。语法、语义都正确后，系统就会对需要的查询的对象加锁。（5）数据库访问权限的核对。（6）确定最佳执行计划。服务器进程会根据一定的规则，对这条语句进行优化。当服务器进程的优化器确定这条查询语句的最佳执行计划后，就会将这条SQL语句与执行计划保存到数据告诉缓存中。等以后还有这个查询时，就会省略以上的语法、语义与权限的检查步骤，而直接执行SQL语句，而提高SQL语句处理效率。（7）最后一步是语句执行，当被选择的数据块已经被读到数据缓冲区，服务器会直接取这块数据，如果不在缓冲区，服务进程会将从数据库文件查询相关数据，并把这些数据放到数据缓冲区^[2]。

3 对于内存区的优化措施

1)     对于数据缓冲区，应该根据具体的数据量，调整数据缓冲区的大小，使数据缓冲区的空闲率应该接近于0，否则就可以减少数据缓冲区大小。同时，也需要保证命中率应在90%以上，否则就需要分配更多的数据缓冲区。

2)     通常情况下，共享池的空闲率应该高于20%，库缓存命中率应该高于99%，而数据字典缓冲命中率应该高于90%，否则需要调整共享池的大小。

3)     PGA区主要由私有会话区以及排序区构成。通常情况下，排序区的命中率应该高于95%，即保证绝大多数的排序操作都在内存中进行，否则就调整排序区的大小^[6]。

4  对于磁盘I/O的优化

1)     数据库的物理文件尽量分散到不同的磁盘空间，避免相互之间的磁盘竞争，同时还可以实现均衡磁盘负载。

2)     将同一表空间的数据文件尽量平均分配到多个磁盘，实现磁盘之间的负载均衡。

3)     将表和索引分散到不同的表空间，并尽量将表数据和索引数据存储到不同磁盘，减少数据文件和索引数据文件对磁盘的竞争。

4)     为不同的应用创建不同的表空间，并将表空间所对应的数据文件存放到不同的磁盘，减少不同应用之间对磁盘的竞争。

5)     系统表空间尽量不要再分配给其他应用使用，减少数据库系统与应用之间的磁盘竞争。

6)     根据表的特点以及数据量大小等，采用分区表、分区索引、索引化表、聚簇等结构，合理地将数据分散到不同的数据文件中，提高I/O性能^[6]。

5对于索引的优化

1)     在经常进行连接，但是没有指定为外键的列上建立索引。

2)     在频繁进行排序或分组的列上建立索引。

3)     在条件表达式经常用到的不同值较多的列上建立索引，在不同值少的列上不建立索引。

4)     对于多个待排序的列，可以在这多个列上建立复合索引。

5)     不能在包含null值的列上建立索引。

6)     对于查询型的表，建立多个索引；对于更新型的表，适度建立索引^[7]。

7)     避免在索引列上使用计算。因为优化器进行查询的，如果遇到索引列上存在计算，就会丢弃索引而选择全表扫描，会造成查询效率低下。

6对于SQL语句的优化

1)     在SELECT子句中，尽量不要使用‘*’，因为在ORACLE把‘*’转换为表中对应的列名涉及到对数据库所有字典的查询，这会消耗大量时间。

2)     应该恰当地选择COMMIT来完成事务的提交工作，以清理缓存中的垃圾，释放系统资源，提升系统的性能。

3)     对于不同的查询优化规则，涉及多个表的查询，多个表之间的连接顺序也会影响查询效率，当选择不同的优化器模式的时候，应该对于表的顺序进行调整。

4)     在WHERE子句中写的列名，应该遵循索引列写在最前面、能够将大量数据过滤掉的条件写在子句末尾的规则。

5)     尽量用EXISTS代替IN子句的查询。因为IN子句进行的是全表扫描，如果换做EXISTS，能够明显提升查询效率。

6)     WHERE子句中，尽量避免在索引列上进行计算^[1]。

7)     尽量在索引列上不使用LIKE等方式进行的查询

8)     不要使用NOT。NOT运算符包含在另外一个逻辑运算符中，这就是不等于(<>)运算符。在使用NOT时应该用<和>来代替，NOT不允许对列使用索引，而逻辑运算符<、>允许列使用索引。、

9)     在海量数据查询的时候尽量少用格式转换。

10)  IN、OR子句会使工作表的索引失效。如果不产生大量重复值，可以考虑把子句拆开，拆开后的子句中应该包含索引。

7其他的优化措施

1)     使用临时表加速查询。当查询涉及到多张表的时候，把表的一个子集进行排序并创建临时表，有时能加速查询。有利于避免多重排序操作，而且在其他的地方还能简化优化器的工作^[7]。

2)     应该尽量避免过于复杂的嵌套查询，当需要的时候，后面的子查询数量应该为较大的数据^[8]。

3)     应当简化或避免对大型表进行重复排序。当能够利用索引自动以适当的次序产生输出的时候，优化器就可以避免排序的步骤^[7]。

4)     尽量避免大事务操作，提高系统的并发能力[8]。

5)     在系统中，对功能相同的代码要保持代码的一致性，以及要在程序中多使用绑定变量，尽量避免SQL语句的硬解析，尽量使用软解析^[8]。