SQLServer和Oracle数据库索引介绍.doc

SQL Server和Oracle数据库索引介绍1 SQL Server中的索引 索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构(B 树)中，使 SQL Server 可以快速有效地查找与键值关联的行。表或视图可以包含以下类型的索引：聚集索引聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。每个表几乎都对列定义聚集索引来实现下列功能：1、可用于经常使用的查询。2、提供高度唯一性。在创建聚集索引之前，应先了解数据是如何被访问的。考虑对具有以下特点的查询使用聚集索引：使用运算符(如 BETWEEN、=、 和 SELECT* 2WHEREjob_titlebetweenaandZZZZZZZZZZZ; ExecutionPlan - 0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=1Bytes=33) 10FILTER 21TABLEACCESS(FULL)OFJOBS(Cost=2Card=1Bytes=33) SQLDROPINDEXIDX_jobs_title; SQLCREATEINDEXIDX_jobs_(job_title); SQLSelect*FROM 2Wherejob_titlebetweenaandZZZZZZZZZZZ; ExecutionPlan - 0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=1Bytes=33) 10FILTER 21TABLEACCESS(FULL)OFJOBS(Cost=2Card=1Bytes=33)位图索引位图索引(bitmap index)是从Oracle7.3 版本开始引入的。目前Oracle企业版和个人版都支持位图索引，但标准版不支持。位图索引是为数据仓库/在线分析查询环境设计的，在此所有查询要求的数据在系统实现时根本不知道。位图索引特别不适用于OLTP 系统，如果系统中的数据会由多个并发会话频繁地更新，这种系统也不适用位图索引。位图索引是这样一种结构，其中用一个索引键条目存储指向多行的指针;这与B*树结构不同，在B*树结构中，索引键和表中的行存在着对应关系。在位图索引中，可能只有很少的索引条目，每个索引条目指向多行。而在传统的B*树中，一个索引条目就指向一行。B*树索引一般来讲应当是选择性的。与之相反，位图索引不应是选择性的，一般来讲它们应该“没有选择性“。如果有大量在线分析查询，特别是查询 以一种即席方式引用了多列或者会生成诸如COUNT 之类的聚合，在这样的环境中，位图索引就特别有用 。位图索引使用 CREATE BITMAP INDEX index_name ON table_name(column_name1,column_name2) TABLESPACE tablespace_name命令语法创建。SQL Server查询优化技术及索引From: /lovewindy/archive/2005/02/19/105959.html在数据库原理里面，对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此，所以一个表最多只能有一个聚簇索引。不过这个定义太抽象了。在SQL Server中，索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。如下图：非聚簇索引 聚簇索引 聚簇索引与非聚簇索引的本质区别到底是什么？什么时候用聚簇索引，什么时候用非聚簇索引？ 这是一个很复杂的问题，很难用三言两语说清楚。我在这里从SQL Server索引优化查询的角度简单谈谈(如果对这方面感兴趣的话，可以读一读微软出版的Microsoft SQL Server 2000数据库编程第3单元的数据结构引论以及第6、13、14单元)。一、索引块与数据块的区别 大家都知道，索引可以提高检索效率，因为它的二叉树结构以及占用空间小，所以访问速度块。让我们来算一道数学题：如果表中的一条记录在磁盘上占用1000字节的话，我们对其中10字节的一个字段建立索引，那么该记录对应的索引块的大小只有10字节。我们知道，SQL Server的最小空间分配单元是“页（Page）”，一个页在磁盘上占用8K空间，那么这一个页可以存储上述记录8条，但可以存储索引800条。现在我们要从一个有8000条记录的表中检索符合某个条件的记录，如果没有索引的话，我们可能需要遍历8000条1000字节/8K字节=1000个页面才能够找到结果。如果在检索字段上有上述索引的话，那么我们可以在8000条10字节/8K字节=10个页面中就检索到满足条件的索引块，然后根据索引块上的指针逐一找到结果数据块，这样IO访问量要少的多。二、索引优化技术 是不是有索引就一定检索的快呢？答案是否。有些时候用索引还不如不用索引快。比如说我们要检索上述表中的所有记录，如果不用索引，需要访问8000条1000字节/8K字节=1000个页面，如果使用索引的话，首先检索索引，访问8000条10字节/8K字节=10个页面得到索引检索结果，再根据索引检索结果去对应数据页面，由于是检索所有数据，所以需要再访问8000条1000字节/8K字节=1000个页面将全部数据读取出来，一共访问了1010个页面，这显然不如不用索引快。SQL Server内部有一套完整的数据检索优化技术，在上述情况下，SQL Server的查询计划（Search Plan）会自动使用表扫描的方式检索数据而不会使用任何索引。那么SQL Server是怎么知道什么时候用索引，什么时候不用索引的呢？SQL Server除了日常维护数据信息外，还维护着数据统计信息，下图是数据库属性页面的一个截图：从图中我们可以看到，SQL Server自动维护统计信息，这些统计信息包括数据密度信息以及数据分布信息，这些信息帮助SQL Server决定如何制定查询计划以及查询是是否使用索引以及使用什么样的索引（这里就不再解释它们到底如何帮助SQL Server建立查询计划的了）。我们还是来做个实验。建立一张表：tabTest(ID, unqValue，intValue)，其中ID是整形自动编号主索引，unqValue是uniqueidentifier类型，在上面建立普通索引，intValue 是整形，不建立索引。之所以挂上一个没有索引的intValue字段，就是防止SQL Server使用索引覆盖查询优化技术，这样实验就起不到作用了。向表中录入10000条随机记录，代码如下： CREATETABLEdbo.tabTest( IDintIDENTITY(1,1)NOTNULL, unqValueuniqueidentifierNOTNULL, intValueintNOTNULL )ONPRIMARY GO ALTERTABLEdbo.tabTestWITHNOCHECKADD CONSTRAINTPK_tabTestPRIMARYKEYCLUSTERED ( ID )ONPRIMARY GO ALTERTABLEdbo.tabTestADD CONSTRAINTDF_tabTest_unqValueDEFAULT(newid()FORunqValue GO CREATEINDEXIX_tabTest_unqValueONdbo.tabTest(unqValue)ONPRIMARY GO declareiint declarevint seti=0 whilei= 0 AND panyName LIKE %i% AND u.groupID IN (SELECT g.groupID FROM Groups g WHERE g.groupLabel = Executive)这条语句涉及3个表的联接，并且包括了许多搜索条件比如大小比较，Like匹配等。在没有索引的情况下Mysql需要执行的扫描行数是 77721876行。而我们通过在companyID和groupLabel两个字段上加上索引之后，扫描的行数只需要134行。在Mysql中可以通过 Explain Select来查看扫描次数。可以看出来在这种联表和复杂搜索条件的情况下，索引带来的性能提升远比它所占据的磁盘空间要重要得多。那么索引是如何实现的呢？大多数DB厂商实现索引都是基于一种数据结构B树。因为B树的特点就是适合在磁盘等直接存储设备上组织动态查找表。B树的定义是这样的：一棵m(m=3)阶的B树是满足下列条件的m叉树：1、每个结点包括如下作用域(j, p0, k1, p1, k2, p2, . ki, pi) 其中j是关键字个数，p是孩子指针2、所有叶子结点在同一层上，层数等于树高h3、每个非根结点包含的关键字个数满足m/2-1=j1001) OR order_num=1008 虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句： SELECT FROM orders WHERE customer_num=104 AND order_num1001 UNION SELECT FROM orders WHERE order_num=1008 这样就能利用索引路径处理查询。 4避免相关子查询 一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。 5避免困难的正规表达式 MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：SELECT FROM customer WHERE zipcode LIKE “98_ _ _” 即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT FROM customer WHERE zipcode “98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。 另外，还要避免非开始的子串。例如语句：SELECT FROM customer WHERE zipcode2，3 “80”，在where子句中采用了非开始子串，因而这个语句也不会使用索引。 回复引用查看 #4楼楼主2009-12-09 15:42RockYang6使用临时表加速查询 把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。例如： SELECT ，rcvbles.balance，other columns FROM cust，rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance0 AND cust.postcode“98000” ORDER BY 如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序： SELECT ，rcvbles.balance，other columns FROM cust，rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance0 ORDER BY INTO TEMP cust_with_balance 然后以下面的方式在临时表中查询： SELECT FROM cust_with_balance WHERE postcode“98000” 临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。 注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。 7用排序来取代非顺序存取 非顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。SQL语句隐藏了这一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。 有些时候，用数据库的排序能力来替代非顺序的存取能改进查询。 实例分析 下面我们举一个制造公司的例子来说明如何进行查询优化。制造公司数据库中包括3个表，模式如下所示： 1part表 零件号零件描述其他列 （part_num）（part_desc）（other colum