基于合理索引的数据库查询优化研究

第２１卷第３期　广东石油化工学院学报　Ｖ０１．２１　Ｎｏ．３　２０１１年６月　Ｊｏｕｒｎａｌ　ｏｆ　Ｇｕａｎｇｄｏｎｇ　Ｕｎｉｖｅｒｓｉｔｙ　ｏｆ　Ｐｅｔｒｏｃｈｅｍｉｃａｌ　Ｔｅｃｈｎｏｌｏｇｙ　Ｊｕｎ．２０ｌ１　基于合理索引的数据库查询优化研究　莫洪林　（１．重庆大学计算机学院，重庆４０００３０；２．湛江师范学院实验教学管理处，广东湛江５２４０４８）　摘要：随着数据库技术的广泛应用，数据库中存储的数据日积月累不断的增加，数据库查询的效率不可避免的降低，如何提　高数据库的查询效率成为每个数据库管理员必须研究的问题。首先介绍了数据库索引的概念和作用，解析了两种不同索　引结构的区别，再通过实例分析，介绍如何建立合理的数据库索引来优化数据库查询。　关键词：数据库；索引；优化；查询　中图分类号：ＴＰ３１１．１２　文献标识码：Ａ　文章编号：１６７１—６５９０（２０１１）０３—０Ｏ６７—０３　０　引言　索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图　中的一列或多列生成的键。这些键存储在一个结构（Ｂ树）中，使数据库可以快速有效地查找与键值关联　的行。　１　两种基本的索引结构　索引实际上可以理解为一种特殊的目录。一般的数据库提供以下两种索引：聚集索引（ｃｌｕｓｔｅｒｅｄ　ｉｎ—　ｄｅｘ，也称聚类索引、簇集索引）和非聚集索引（ｎｏｎｃｌｕｓｔｅｒｅｄ　ｉｎｄｅｘ，也称非聚类索引、非簇集索引）。下面，我　们举例来解析一下聚集索引和非聚集索引的区别：　其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字　典的前几页，因为“安”的拼音是“ａｎ”，而按照拼音排序汉字的字典是以英文字母“ａ”开头并以“ｚ”结尾的，　那么“安”字就自然地排在字典的前部。如果翻完了所有以“ａ”开头的部分仍然找不到这个字，那么就说明　字典中没有这个字；同样的，如果查“张”字，那字典也要翻到最后部分，因为“张”的拼音是“ｚｈａｎｇ”。也就　是说，字典的正文部分本身就是一个目录，用户不需要再去查其他目录来找到需要找的内容。这种正文内　容本身就是一种按照一定规则排列的目录称为“聚集索引”。　如果用户认识某个字，知道它的发音，他可以快速地从字典中查到这个字。但用户也可能会遇到他不　认识的字，不知道它的发音，这时候，就不能按照刚才的方法找到要查的字，而需要去根据“偏旁部首”查到　要找的字，然后根据这个字后的页码直接翻到某页来找到要找的字。但结合“部首目录”和“检字表”而查　到的字的排序并不是真正的正文的排序方法，比如查“张”字，可以看到在查部首之后的检字表中“张”的页　码是６７２页，检字表中“张”的上面是“驰”字，但页码却是６３页，“张”的下面是“弩”字，页面是３９０页。很　显然，这些字并不是真正的分别位于“张”字的上下方，现在看到的连续的“驰、张、弩”三字实际上就是他们　在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。用户可以通过这种方式来找到所需　要的字，但它需要两个过程，先找到目录中的结果，然后再翻到所需要的页码。这种目录纯粹是目录，正文　纯粹是正文的排序方式称为“非聚集索引”。　通过以上例子，可以理解到什么是“聚集索引”和“非聚集索引”。因为目录只能按照一种方法进行排　收稿日期：２０１１—０４—２０；修回日期：２０１１—０５—０９　作者简介：莫洪林（１９８２一），男，广东湛江人，助理实验师，工程硕士，研究方向：服务器系统管理与网络技术应用。　６８　广东石油化工学院学报　２０１１年　序，所以每个表只能有一个聚集索引。　２何时使用聚集索引或非聚集索引　表１总结了何时使用聚集索引或非聚集索引。事实上，可以通过前面聚集索引和非聚集索引的定义　的例子来理解上表。如：返回某范围内的数据一　项。比如某个表有一个时间列，恰好聚合索引建立　在了该列，这时查询２００４年１月１日至２００４年１０　月１日之间的全部数据时，这个速度就将是很快　的，因为这个数据表正文是按日期进行排序的，聚　类索引只需要找到要检索的所有数据中的开头和　动作描述　列经常被分组排序　一使用聚集索：　可　使用非聚集索引　可　返回某范围内的数据　可　个或极少不同值　不可　小数目的不同值　可　不可　不可　不可　大数目的不同值　频繁更新的列　外键　主键列　频繁修改索引列　不可　不可　司　可　不可　可　可　司　可　可　结尾数据即可；而不像非聚集索引，必须先查到目　录中查到每一项数据对应的页码，然后再根据页码　查到具体内容。　３　实例分析　笔者管理的网站在前段时间出现了访问缓慢甚至失败的情况，失败时出现“［Ｍｉｃｒｏｓｏｆｔ］［ＯＤＢＣ　ＳＱＬ　Ｓｅｒｖｅｒ　Ｄｒｉｖｅｒ］超时已过期／ｍｙｉｎｄｅｘ．ａｓｐ，行９ｌ”，通过对该网页代码的分析，失败超时的代码行都是一些数　据库查询的代码。笔者再对该数据库进行了分析，发现数据库的数据经过多年的积累，数据已突破万条，　而原作者并没有对该数据库的建立合理的索引，导致该数据库查询效率低下，随着网站访问人数的不断增　多，不可避免的出现了超时失败的现象。　访问超时的网页是根据不同板块的需要显示最新的相关文章，其主要的数据库查询代码如下：　ＳＥＬＥＣＴ　ｔｏｐ　ｂ　Ａｒｔｉｃｌｅｓ．　表２该查询相关的属性的数据结构　ＦＲ０Ｍ　Ａｒｔｉｃｌｅｓ　ＷＨＥＲＥ　Ａｒｔｉｃｌｅｓ．ＣａｔａｌｏｇＩＤ＝ａ　ＯＲＤＥＲ　ＢＹ　ＳｅｔＴｏｐ　ＤＥＳＣ，ＡｒｔｉｃｌｅＩＤ　Ｄｅｓｃ　注：ａ，ｂ的数据类型为正整数。　根据上面的语句，与该查询相关的属性在Ａｒｔｉｃｌｅｓ数据表中的数据结构说明如表２所示。　根据查询语句的算法分析，该查询首先是要筛选出相应文章分类号为ａ的数据，再从中提取最新的ｂ　条数据。根据该要求进行索引优化，先在ＣａｔａｌｏｇＩＤ属性上建立一个聚集索引，再在ＡｒｔｃＭＤ属性上建立一　个降序的排列，这样就可以使数据以文章类型分类并且以文章的新旧顺序排好。需要注意的是，数据库会　默认在主键ＡｒｔｉｃＭＤ上建立一个聚集索引，而一个数据表只能建立一个聚集索引，因此要先把默认的聚集　索引删除。建立该索引的语句如下：　ＣＲＥＡＴＥ　ＣＬＵＳ１　ＲＥＤ　ＩＮＤＥＸ［ａ］ＯＮ［ｄｂｏ］．［Ａｒｔｉｃｌｅｓ］（［ＣａｔａｌｏｇＩＤ］，［ＡｒｔｉｃｌｅＩＤ］ｄｅｓｃ）　Ｗ删　ＤＲＯＰ＿ＥＸＩＳｒⅡＮＧ　ＯＮ［ＰＲＩＭＡＲＹ］　建立索引后数据表中数据排列顺序变化的简　单示意图如表３所示。　表３建立索引前后的数据排列顺序变化　建立索引前　ＡｒｔｉｃｌｅＩＤ　１　６　５　４　４　建立索引后　ＡｒｔｉｃＭＤ　假设数据表中有总共Ｉ１条数据，要得最新的ａ　条ＣａｔａｌｏｇＩＤ：ｂ的数据。在建立索引前，完成该查　询需要遍历整个数据表，得到所有ＣａｔａｌｏｇＩＤ＝ｂ的　数据后再从中获取最新的ａ条数据，因此其搜索次　数为ｎ。而建立索引后，只需搜索到第一个ＣＡｍ—　ｌｏｇＩＤ＝ｂ的数据再顺序读取其前ａ条数据即为该查　ｌ　６　３　第３期　莫洪林：基于合理索引的数据库查询优化研究　６９　询的数据，可以得出其查询次数为ｂ＋ａ。因为ｂ＋ａ远小于ｎ的，可以看出，建立索引后搜索效率极大的提　高了。　在实际测试中，在建立索引前后，运行以下代码获得该查询执行花费时间：　ｄｅｃｌａｒｅ＠ｔ　ｄａｔｅｔｉｍｅ　ｓｅｔ＠ｔ＝ｇｅｔｄａｔｅ（）　ＳＥＬＥＣＴ　ｔｏｐ　６　Ａｒｔｉｃｌｅｓ．＊　ＦＲＯＭ　Ａｒｔｉｃｌｅｓ　ＷＨＥＲＥ　Ａｒｔｉｃｌｅｓ．ＣａｔａｌｏｇＩＤ：５　ＯＲＤＥＲ　ＢＹ　ＳｅｔＴｏｐ　ＤＥＳＣ．ＡｒｔｉｃＭＤ　Ｄｅｓｃ　ｓｅｌｃｅｔ［语句执行花费时间（ｍｓ）］＝ｄａｔｅｄｉｆ（ｍｓ，＠ｔ，ｇｅｔｄａｔｅ（））　得到的测试速度结果为：优化前为４２７ｍｓ；优化后为１１９ｍｓ。实践证明，在未优化前执行一条查询需要　４２７ｍｓ的时间，该页面根据不同的版块有多条这样的查询需要执行，所以当同时访问页面的用户超过一定　数量就会出现超时无法访问的情形出现。而在建立合理的索引后，查询效率提升７５％，有效的解决了超　时的问题。并且随着数据量的增多，优化的效率还会更高。　４结束语　“水可载舟，亦可覆舟”，索引也一样。索引有助于提高检索性能，但过多或不当的索引也会导致系统　低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片。　所以说，要建立一个“适当”的索引体系，要多测试一些方案，找出哪种方案效率最高、最为有效，特别是对　聚合索引的创建，更应精益求精，以使得数据库能得到高性能的发挥。　【参考文献］　［１］萨师煊，王珊．数据库系统概论［Ｍ］．北京：高等教育出版社，２０００．　［２］郑科奇．ＳＱＬ　ＳＥＲＶＥＲ应用教程［Ｍ］．北京：人民邮电出版社，２００８．　［３］朱君．基于索引的ＳＱＬ语句查询优化方法［Ｊ］．东莞理工学院学报，２０ｏ３（２）：ｌ８—２ｏ．　［４］荆立夏．关系数据库的查询优化［Ｊ］．郑州航空工业管理学院学报，２Ｏ０３（１）：１０４—１０６　Ａｐｐｌｉｃａｔｉｏｎ　Ｒｅｓｅａｒｃｈ　ｏｆ　Ｅｆｉｆｃｉｅｎｔ　Ｉｎｄｅｘ　ａｎｄ　Ｄａｔａｂａｓｅ　Ｑｕｅｒｙ　Ｏｐｔｉｍｉｚａｔｉｏｎ　ＭＯ　Ｈｏｎｇ—ｌｉｎ　（１．Ｃｏｌｌｅｇｅ　ｏｆ　Ｃｏｍｐｕｔｅｒ　Ｓｃｉｅｎｃｅ，Ｃｈｏｎｇｑｉｎｇ　Ｕｎｉｖｅｒｓｉｔｙ，Ｃｈｏｎｇｑｉｎｇ　４０００３０，Ｃｈｉｎａ；　２．Ｅｘｐｅｒｉｍｅｎｔ　Ｔｅａｃｈｉｎｇ＆Ｍａｎａｇｅｍｅｎｔ　Ｄｅｐａｒｔｍｅｎｔ，Ｚｈａｎｊｉａｎｓ　Ｎｏｒｍａｌ　Ｃｏｌｌｅｇｅ，２ｈａｎｊｉａｎｇ　５２４０４８，Ｃｈｉｎａ）　Ａｂｓｔｒａｃｔ：Ｗｉｔｈ　ｔｈｅ　ｅｘｔｅｎｓｉｖｅ　ａｐｐｌｉｃａｔｉｏｎ　ｏｆ　ｄａｔａｂａｓｅ　ｔｅｃｈｎｏｌｏｇｙ，ｔｈｅ　ｉｎｃｒｅａｓｉｎｇ　ａｍｏｕｎｔ　ｏｆ　ｄａｔａｂａｓｅ　ａｎｄ　ｄｅｃｌｉｎｉｎｇ　ｅｆｆｉｃｉｅｎｃｙ　ｏｆ　ｄａｔａｂａｓｅ　ｑｕｅｒｙ．ｈｏｗ　ｔｏ　ｉｍｐｒｏｖｅ　ｔｈｅ　ｅｆｉｆｃｉｅｎｃｙ　ｏｆ　ａ　ｄａｔａｂａｓｅ　ｑｕｅｒｙ　ｈａｓ　ｂｅｃｏｍｅ　ａ　ｐｏｉｎｔ　ｔｏ　ｄａｔａｂａｓｅ　ａｄｍｉｎｉｓｔｒａｔｏｒ．Ｔｈｉｓ　ｐａｐｅｒ　ｉｎｔｒｏｄｕｃｅｓ　ｔｈｅ　ｃｏｎｃｅｐｔ　ａｎｄ　ｒｏｌｅ　ｏｆ　ｄａｔａｂａｓｅ　ｉｎｄｅｘｅｓ，ｐａｒ￣ｓ　ｔｈｅ　ｄｉｆｅｒｅｎｃｅｓ　ｂｅｔｗｅｅｎ　ｔｗｏ　ｄｉｆｅｒｅｎｔ　ｉｎｄｅｘ　ｓｔｒｕｃｔｕｒｅｓ，ａｎｄ　ｅｘｅｍｐｌｉｉｆｅｓ　ｈｏｗ　ｔｏ　ｅｓｔａｂｌｉｓｈ　ａ　ｒａｔｉｏｎＭ　ｄａｔａ・　ｂａｓｅ　ｉｎｄｅｘｅｓ　ｔｏ　ｏｐｔｉｍｉｚｅ　ｄａｔａｂａｓｅ　ｑｕｅｒｉｅｓ．　Ｋｅｙ　ｗｏｒｄｓ：ｄａｔａｂａｓｅ；ｉｎｄｅｘ；ｏｐｔｉｍｉｚｅ；ｑｕｅｒｙ