浅析索引在SQL语句中使用技巧.docVIP

浅析索引在SQL语句中使用技巧 　　摘 要：随着信息化的不断深入发展，数据的量呈现出几何级增长。而伴随着数据量的不断攀升，如何提高对数据库的访问性能成为每个信息系统的重点优化方向。索引技术是数据库性能优化中最常用的技术手段。以目前最流行的Oracle数据库为例，对数据库中索引的使用进行重点说明和举例，使读者对SQL语言中的索引技术有较深的了解。 　　关键词：索引；SQL语言；优化；数据库 　　中图分类号：TN911 文献标识码：A 文章编号：2095-1302（2015）04-00-03 　　0 引 言 　　在DBMS中，索引是访问数据库中数据的重要手段，特别是在大型系统中，更是必备方法。如果说数据库是一本字典，其中的数据是字典的内容，那么，索引就是这本字典的目录。查字典可以使用偏旁部首、汉语拼音、笔画等不同的方法进行查找，查询数据库中的数据也有各种各样的索引可供使用。在数据库中，索引使用DML操作可以迅速找到表数据，不用对整张表逐行访问。索引是一种结构很强的方案对象，它将数据和存放数据的位置对应起来。对于数据量非常大的表来说，通过全表扫描来访问和通过索引来访问，速度可能有是数量级上的差别。 　　索引能通过事先保存的索引键，按照一定顺序记录数据的位置，由此替代DML操作原本要进行的全表扫描，通过“以空间换时间”的方法，牺牲少量的存储空间，换取快速的反应时间。在一张表上是否该建立索引、建立怎样的索引、怎样才能有效利用建立起的索引是数据库优化中最常见的问题。 　　1 SQL语句的执行过程 　　是否会使用索引要从Oracle执行查询的机制开始分析。在Oracle进行必要的语法检查和语义分析之后，会自动对语句进行优化，其目的就是找到最高效的运行路径。可能会使用的优化器有两种：RBO基于规则的优化器，CBO基于成本的优化器。默认情况下是CBO优化器，它会快速统计数据量的大小， 选择开销最小 （尽量消耗最少的CPU和I/O）的执行计划。确定了执行计划之后，Oracle会将SQL语句格式化为内部执行代码。 　　由此可见，想要提高SQL语句的性能，就要从优化器会选择怎样的执行计划这块考虑。如果表上没有建立索引，执行计划就是全表扫描，它会根据PGA中的系统设定，读取一批数据块，如果表数据较小，全表可以一次性全部读入；如果数据量很大，则需要多次读取数据块。如果在数据量稍大的表上建立了索引，优化器会比较利用索引和全表扫描的读取数据次数，如果利用索引代价小，则选择利用索引，否则还是选择全表扫描。一般来说，查询语句要读取10%以上的数据量的话，优化器就宁愿选择全表扫描，而不是索引。因为一个索引项指向了一个数据块，所要读取的数据块太多，就有大量的I/O操作要进行，反而降低性能。 　　2 索引的分类 　　建立怎样的索引。常见的索引有以下几种：B*索引（包含唯一索引），位图索引，函数索引，本地前缀分区索引，全局范围分区索引等。 　　（1）B*树索引 　　B*树索引就是利用建立B树，将索引列和指向表中各行的rowid组织起来，按照顺序建立一颗平衡树，根据索引列的数量，自动建立合理高度的B树。在B树索引中，所有叶子的深度一样，结构自动保持平衡，在增删改之后都由Oracle自动维护。更重要的是，B树索引可以适应多种查询条件，包含范围查找、=、=、like和完全匹配。由于树的高度是基于索引列的数量，当访问的数据量占总数据的10%以上的时候，需要访问B树的范围太大，这个时候，其优势无法体现。 　　（2）位图索引 　　当某一列上的值可选择性较低的时候，一般使用位图索引。所谓的“可选择性”就是指查询这一列的distinct值与这一列所有值的比例，这个比值越高，说明可选择性越高。对于 “性别”列，只存在两种值，可选择性低；“姓名”列，可选择性一般较高。 　　（3）函数索引 　　通过在某一列上利用函数来创建索引。例如：create INDEX idx_func_sub on emp substr（empno，1，2）；就是利用substr函数，在empno上建立索引。 　　3 使用索引的方法和技巧 　　（1）对于索引的建立，我们一般选择在可选择性高的列上建立索引。以地税系统的一张登记表为例，这张表记录了纳税人的登记信息，一个纳税人识别号对应一条记录，纳税人所属的区县对应字段dwid。在识别号sbh和区县dwid上分别建立索引IDX_SWJ_SBH和IDX_SWJ_dwid。如果有查询语句： 　　select * from glfw_swdj_tempwyp where sbh=’12345’ and dwid=’54321’； 　　查看执行计划如图1所示，执行计划没有使用IDX_SWJ_dwid，因为sbh列的可选性要远高于dwid