论文部分内容阅读
【摘要】在数据库系统中，一条SQL查询语句的实现可有多种不同的表达方法，而不同的表达方法又会使数据库系统的响应速度大相径庭。深入讨论了在数据库中，怎样正确创建和使用索引，通过合理书写SQL语句来优化查询，充分发挥索引的特性，进一步提高数据查询速度，使系统性能得到更充分有效的改善和提高。
【关键词】数据库；索引；优化；查询；效率
0 引言
在数据库管理系统中，索引是在数据表列上建立的一种数据库对象，是数据库中重要的辅助数据结构。它是可选的，如果不使用索引，所有的数据查询将通过表扫描实现，这就意味着一个表中的所有数据必须都被读入，并且与所请求的数据进行逐行比较，这将产生大量的I/O 操作（除非要从该表中选择绝大多数的行）。对于大型的表，执行表扫描将消耗大量的系统资源。
索引的引用虽然是由系统决定的，但它与创建索引时编写SQL语句的格式密切相关。一个合理的索引和一个好的SQL查询语句往往可以使系统性能提高几倍乃至数十倍，下面就以Oracle为例来讨论索引的创建、使用和查询方法。
1 索引键和唯一索引
1.1 索引键。索引键是用于创建索引的列。如果要通过索引访问一条记录，那就必须在SQL语句的WHERE子句内部包含一个或多个索引键。
1.2 惟一索引。惟一索引就是要求插入到索引字段的值必须是惟一的，不允许有重复的键值被插入。对于复合键，这意味着所有列的值的组合必须是惟一的。
2 创建和使用索引的原则
2.1 选择索引类型应考虑的因素。位图索引适用于那些基数（基数是指某个字段可能拥有的不重复数值的个数，比如sex字段的基数为2，因为性别只能是男或女）比较小的字段。通常如果字段的基数只达到表中记录数的1%，或字段中大部分值都会重复出现100次以上，则对该字段应建立位图索引。此外，某些字段雖然有比较高的基数，也不会出现很多重复值，但经常会被具有复杂查询重要条件的WHERE子句引用，也应为其建立位图索引。而B+树索引则适用于那些具有高基数的字段，特别是那些具有PRIMARY KEY、UNIQUE约束的不能具有重复值的字段。如果字段的特性介于上述两2种情形之间，则（1）考虑是否需要节省存储空间。B+树索引将占用大量的存储空间；而位图索引通常只占用很少的存储空间。（2）对于一些特殊类型的查询语句，位图索引能比B+树索引更有效地提高查询速度。比如，如果在查询的WHERE子句中包含AND、OR等逻辑运算符，使用位图索引可以极大地提高查询的执行速度，因为逻辑运算可以直接在位图索引内部完成。（3）对于另外一些查询语句，B+树索引能比位图索引更有效地提高查询速度。比如，那些经常会使用“<”、“>”等比较运算符进行查询的字段，应当使用B+树索引而不是位图索引，这是由B+树数据结构的搜索特性决定的。（4）在位图索引中可以记录具有NULL值的字段，而在B+树索引中将忽略所有NULL值字段。因此，如果某个字段需要进行与NULL值相关的查询，应当为它创建位图索引。
2.2 创建索引的原则。（1）当表主要是为了查询时，可以考虑创建索引；否则在数据频繁插入、更新、删除时系统要花费大量的时间开销来维护在索引上的操作，反而影响性能。（2）应该为较大的表创建索引。表越大索引的作用就越显著，效率也越高。（3）对一个表可建立多个索引，但建议一般最多不超过5个。因为太多的索引不仅要占用更多的磁盘空间，而且还会由于较大的索引维护工作而降低插入、更新及删除的速度。（4）创建索引时应选择适当的列：1）经常要查询的列；2）选择性比较好的列；3）经常要进行排序和分组的列；4）经常用于多表连接的列；5）在WHERE子句中频繁使用的列。（5）创建索引应选择适当的表空间。在创建索引时可以把索引存放在任何表空间中，默认情况下Oracle会自动把索引和它所对应的基表存放在同一个空间内。但在创建索引时也可以显式地指定存储表空间，可以把索引存放在与其基表不同硬盘上的不同表空间中，这样比和基表同在一个表空间内更能提高查询速度。因为这样Oracle能够并行读取不同硬盘中的数据，避免产生I/O冲突。
2.3 不使用索引的准则。在下列情况下，一般不使用索引：（1）数据记录不多的表；（2）如果查询经常返回的行数超过总行数的10%；（3）如果被索引的列太长（如超过50个字节）。
2.4 复合索引的应用。复合索引允许用户在同一张表上的2个或多个列上建立索引，是具有多列排码的索引。对于经常需要将多个列作为一个整体同时查询的场合，复合索引往往要比创建多个单列索引更加可取。其优点如下：（1）在数据处理过程中比多个单列索引开销要少；（2）对数据修改语句的性能影响较小；（3）能有效地减少表中索引的总数，充分节约磁盘空间；（4）在表中没有单独的任何列能够惟一确定数据行，而是用所有索引列键值的组合来惟一确定数据行时，使用具有多个综合附加列的复合索引可以强化索引的惟一性，因此可以制造更好的机会来加速数据存取。
3 充分利用索引特性提高查询效率
对索引的引用是Oracle自动进行的，数据库系统把已建好的索引作为一类资源，在存取操作时自动判定能否使用这些资源。在这个过程中索引是否能被有效地引用，是Oracle系统优化查询的关键。而一个SQL查询语句又可以有很多种执行策略，Oracle优化器将选择出全部的执行方法中所需时间最少，也就是所谓成本最低的一种方法。SQL语句的书写格式将直接关系到系统对索引的引用，因为Oracle所有优化的进行都是基于SQL语句中的WHERE子句。尽管现在数据库产品在查询优化方面已经做得越来越好，但它对查询数据的对象特性并不十分了解，难免要带有一定的盲目性。为了给数据库应用设计者提供更强更有效的控制手段，以便灵活决定哪些查询启动索引，启动哪些索引；怎样才能更充分地发挥索引的作用，以及如何使查询策略得到最佳的优化，Oracle提供了许多约定，用于根据可能的查询结果选择最佳查询方案。
4 结语
据统计，约有80%以上的性能问题都是由于使用了不恰当的查询语句造成的。因此，了解SQL语句的执行和优化过程，提高SQL语句的质量对提高系统性能有很大帮助。然而查询语句的好坏往往同实际数据库中表的结构、记录的数量以及字段的取值等多种因素有关，所以无法只用几条简单的普遍适用的规律来总结优化查询语句。但首先应对Oracle最基本的工作过程和规律有一个了解；并采取适当的查询策略，充分利用索引特性，为查询提供一个最佳的执行方案，使系统性能得到有效的改善和提高。
参考文献：
[1]顾 诚.Oracle 数据库系统应用开发[M].北京：电子工业出版社，1998.
[2]David Lockman.Oracle8数据库开发[M].北京：电子工业出版社，1999.
[3]林存德.Oracle 8i for NT DBA培训手册[M].北京：北京大学出版社，2001.
[4]Steve Lemme John R.Colby.Oracle 数据库的实施和管理[M].北京：机械工业出版社，2001.
[5]Edward Whalen Mitchell Schroeter.Oracle 性能调整与优化[M].北京：人民邮电出版社，2002.
【关键词】数据库；索引；优化；查询；效率
0 引言
在数据库管理系统中，索引是在数据表列上建立的一种数据库对象，是数据库中重要的辅助数据结构。它是可选的，如果不使用索引，所有的数据查询将通过表扫描实现，这就意味着一个表中的所有数据必须都被读入，并且与所请求的数据进行逐行比较，这将产生大量的I/O 操作（除非要从该表中选择绝大多数的行）。对于大型的表，执行表扫描将消耗大量的系统资源。
索引的引用虽然是由系统决定的，但它与创建索引时编写SQL语句的格式密切相关。一个合理的索引和一个好的SQL查询语句往往可以使系统性能提高几倍乃至数十倍，下面就以Oracle为例来讨论索引的创建、使用和查询方法。
1 索引键和唯一索引
1.1 索引键。索引键是用于创建索引的列。如果要通过索引访问一条记录，那就必须在SQL语句的WHERE子句内部包含一个或多个索引键。
1.2 惟一索引。惟一索引就是要求插入到索引字段的值必须是惟一的，不允许有重复的键值被插入。对于复合键，这意味着所有列的值的组合必须是惟一的。
2 创建和使用索引的原则
2.1 选择索引类型应考虑的因素。位图索引适用于那些基数（基数是指某个字段可能拥有的不重复数值的个数，比如sex字段的基数为2，因为性别只能是男或女）比较小的字段。通常如果字段的基数只达到表中记录数的1%，或字段中大部分值都会重复出现100次以上，则对该字段应建立位图索引。此外，某些字段雖然有比较高的基数，也不会出现很多重复值，但经常会被具有复杂查询重要条件的WHERE子句引用，也应为其建立位图索引。而B+树索引则适用于那些具有高基数的字段，特别是那些具有PRIMARY KEY、UNIQUE约束的不能具有重复值的字段。如果字段的特性介于上述两2种情形之间，则（1）考虑是否需要节省存储空间。B+树索引将占用大量的存储空间；而位图索引通常只占用很少的存储空间。（2）对于一些特殊类型的查询语句，位图索引能比B+树索引更有效地提高查询速度。比如，如果在查询的WHERE子句中包含AND、OR等逻辑运算符，使用位图索引可以极大地提高查询的执行速度，因为逻辑运算可以直接在位图索引内部完成。（3）对于另外一些查询语句，B+树索引能比位图索引更有效地提高查询速度。比如，那些经常会使用“<”、“>”等比较运算符进行查询的字段，应当使用B+树索引而不是位图索引，这是由B+树数据结构的搜索特性决定的。（4）在位图索引中可以记录具有NULL值的字段，而在B+树索引中将忽略所有NULL值字段。因此，如果某个字段需要进行与NULL值相关的查询，应当为它创建位图索引。
2.2 创建索引的原则。（1）当表主要是为了查询时，可以考虑创建索引；否则在数据频繁插入、更新、删除时系统要花费大量的时间开销来维护在索引上的操作，反而影响性能。（2）应该为较大的表创建索引。表越大索引的作用就越显著，效率也越高。（3）对一个表可建立多个索引，但建议一般最多不超过5个。因为太多的索引不仅要占用更多的磁盘空间，而且还会由于较大的索引维护工作而降低插入、更新及删除的速度。（4）创建索引时应选择适当的列：1）经常要查询的列；2）选择性比较好的列；3）经常要进行排序和分组的列；4）经常用于多表连接的列；5）在WHERE子句中频繁使用的列。（5）创建索引应选择适当的表空间。在创建索引时可以把索引存放在任何表空间中，默认情况下Oracle会自动把索引和它所对应的基表存放在同一个空间内。但在创建索引时也可以显式地指定存储表空间，可以把索引存放在与其基表不同硬盘上的不同表空间中，这样比和基表同在一个表空间内更能提高查询速度。因为这样Oracle能够并行读取不同硬盘中的数据，避免产生I/O冲突。
2.3 不使用索引的准则。在下列情况下，一般不使用索引：（1）数据记录不多的表；（2）如果查询经常返回的行数超过总行数的10%；（3）如果被索引的列太长（如超过50个字节）。
2.4 复合索引的应用。复合索引允许用户在同一张表上的2个或多个列上建立索引，是具有多列排码的索引。对于经常需要将多个列作为一个整体同时查询的场合，复合索引往往要比创建多个单列索引更加可取。其优点如下：（1）在数据处理过程中比多个单列索引开销要少；（2）对数据修改语句的性能影响较小；（3）能有效地减少表中索引的总数，充分节约磁盘空间；（4）在表中没有单独的任何列能够惟一确定数据行，而是用所有索引列键值的组合来惟一确定数据行时，使用具有多个综合附加列的复合索引可以强化索引的惟一性，因此可以制造更好的机会来加速数据存取。
3 充分利用索引特性提高查询效率
对索引的引用是Oracle自动进行的，数据库系统把已建好的索引作为一类资源，在存取操作时自动判定能否使用这些资源。在这个过程中索引是否能被有效地引用，是Oracle系统优化查询的关键。而一个SQL查询语句又可以有很多种执行策略，Oracle优化器将选择出全部的执行方法中所需时间最少，也就是所谓成本最低的一种方法。SQL语句的书写格式将直接关系到系统对索引的引用，因为Oracle所有优化的进行都是基于SQL语句中的WHERE子句。尽管现在数据库产品在查询优化方面已经做得越来越好，但它对查询数据的对象特性并不十分了解，难免要带有一定的盲目性。为了给数据库应用设计者提供更强更有效的控制手段，以便灵活决定哪些查询启动索引，启动哪些索引；怎样才能更充分地发挥索引的作用，以及如何使查询策略得到最佳的优化，Oracle提供了许多约定，用于根据可能的查询结果选择最佳查询方案。
4 结语
据统计，约有80%以上的性能问题都是由于使用了不恰当的查询语句造成的。因此，了解SQL语句的执行和优化过程，提高SQL语句的质量对提高系统性能有很大帮助。然而查询语句的好坏往往同实际数据库中表的结构、记录的数量以及字段的取值等多种因素有关，所以无法只用几条简单的普遍适用的规律来总结优化查询语句。但首先应对Oracle最基本的工作过程和规律有一个了解；并采取适当的查询策略，充分利用索引特性，为查询提供一个最佳的执行方案，使系统性能得到有效的改善和提高。
参考文献：
[1]顾 诚.Oracle 数据库系统应用开发[M].北京：电子工业出版社，1998.
[2]David Lockman.Oracle8数据库开发[M].北京：电子工业出版社，1999.
[3]林存德.Oracle 8i for NT DBA培训手册[M].北京：北京大学出版社，2001.
[4]Steve Lemme John R.Colby.Oracle 数据库的实施和管理[M].北京：机械工业出版社，2001.
[5]Edward Whalen Mitchell Schroeter.Oracle 性能调整与优化[M].北京：人民邮电出版社，2002.