你了解SQL的聚集索引吗

深入探讨SQL索引的一些内幕
服务器君一共花费了881.645 ms进行了5次数据库查询,努力地为您提供了这个页面。
试试阅读模式?希望听取您的建议

其实对于非专业的数据库操作人员来讲,例如软件开发人员,在很大程度上都搞不清楚数据库索引的一些基本知识,有些是知其一不知其二,或者是知其然不知其所以然。造成这种情况的主要原因我觉的是行业原因,有很多公司都有自己的DBA团队,他们会帮助你优化SQL,开发人员即使不懂优化问题也不大,所以开发人员对这方面也就不会下太多功夫去了解SQL优化,但如果公司没有这样的DBA呢,就只能靠程序员自己了。 最近突然想起前一阵和一朋友的聊天,当时他问我的问题是一个非常普通的问题:说说SQL聚集索引和非聚集索引的区别。

大家可能认为这个问题难度不大,认为太熟悉了,也许不会感兴趣,但你真能说清楚吗?其实要想说明白这两者的差别也不是三两句就说的清的,那天我也是觉的这问题太泛了,就随便说了其中的两个区别:

  1. 聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个,这个跟没问题没差别,一般人都知道。
  2. 聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续,这个大家也都知道。

上面的两点从大的方面讲都是讲的通的,后面我们继续探讨,举一个实际点的例子,一个学生表student,里面是学生号id,学生姓名,学生所在城市ID,学生成绩(总分)。

  • 问:如果想按姓名查询,如何做优化?
  • 答:在姓名字段上建立索引。
  • 问:建立什么类型的索引?
  • 答:建立非聚集索引。
  • 问:为什么?
  • 答:一般有范围查询的需求,可以考虑在此字段上创建聚集索引。
  • 问:学分有重复性,在学分字段上创建聚集索引能行吗? ....沉思,不能创建吗?之前的项目好像真这样做过
  • 答:应该可以吧。
  • 问:聚集索引的约束是什么?
  • 答:唯一性啊?
  • 问:既然是唯一性,那么学分字段上还能创建聚集索引吗?....再次沉思,应该可以啊,但索引的约束又怎么说呢?
  • 答:应该可以的,以前用过。

我自认为是对数据库索引知识有一定研究的,但可能是有两年没实际接触SQL的原因,一时还真想不出具有说服力的解释,朋友们看到这能解答我的问题吗?

其实上面的我们需要搞清楚以下几个问题:

第一:聚集索引的约束是唯一性,是否要求字段也是唯一的呢?

分析:如果认为是的朋友,可能是受系统默认设置的影响,一般我们指定一个表的主键,如果这个表之前没有聚集索引,同时建立主键时候没有强制指定使用非聚集索引,SQL会默认在此字段上创建一个聚集索引,而主键都是唯一的,所以理所当然的认为创建聚集索引的字段也需要唯一。

结论:聚集索引可以创建在任何一列你想创建的字段上,这是从理论上讲,实际情况并不能随便指定,否则在性能上会是恶梦。

第二:为什么聚集索引可以创建在任何一列上,如果此表没有主键约束,即有可能存在重复行数据呢?

粗一看,这还真是和聚集索引的约束相背,但实际情况真可以创建聚集索引,分析其原因是:如果未使用 UNIQUE 属性创建聚集索引,数据库引擎将向表自动添加一个四字节 uniqueifier 列。必要时,数据库引擎 将向行自动添加一个 uniqueifier 值,使每个键唯一。此列和列值供内部使用,用户不能查看或访问。

第三:是不是聚集索引就一定要比非聚集索引性能优呢?

如果想查询学分在60-90之间的学生的学分以及姓名,在学分上创建聚集索引是否是最优的呢?

答:否。既然只输出两列,我们可以在学分以及学生姓名上创建联合非聚集索引,此时的索引就形成了覆盖索引,即索引所存储的内容就是最终输出的数据,这种索引在比以学分为聚集索引做查询性能更好。

第四:在数据库中通过什么描述聚集索引与非聚集索引的?

索引是通过二叉树的形式进行描述的,我们可以这样区分聚集与非聚集索引的区别:聚集索引的叶节点就是最终的数据节点,而非聚集索引的叶节仍然是索引节点,但它有一个指向最终数据的指针。

第五:在主键是创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢?

有了上面第四点的认识,我们分析这个问题就有把握了,在有主键的表中插入数据行,由于有主键唯一性的约束,所以需要保证插入的数据没有重复。我们来比较下主键为聚集索引和非聚集索引的查找情况:聚集索引由于索引叶节点就是数据页,所以如果想检查主键的唯一性,需要遍历所有数据节点才行,但非聚集索引不同,由于非聚集索引上已经包含了主键值,所以查找主键唯一性,只需要遍历所有的索引页就行,这比遍历所有数据行减少了不少IO消耗。这就是为什么主键上创建非聚集索引比主键上创建聚集索引在插入数据时要快的真正原因。

好了,讲这这些,不知道大家是否真的了解SQL的聚焦索引,我也是数据库新手(从使用时间上来讲也不算新了,哈哈),不专业,有什么不对的地方,希望大家批评指正,下篇我会分析一些数据库访问索引的情况,有图的情况下,也许看的更加明白。

本文地址:http://www.nowamagic.net/librarys/veda/detail/822,欢迎访问原出处。

不打个分吗?

转载随意,但请带上本文地址:

http://www.nowamagic.net/librarys/veda/detail/822

如果你认为这篇文章值得更多人阅读,欢迎使用下面的分享功能。
小提示:您可以按快捷键 Ctrl + D,或点此 加入收藏

阅读一百本计算机著作吧,少年

很多人觉得自己技术进步很慢,学习效率低,我觉得一个重要原因是看的书少了。多少是多呢?起码得看3、4、5、6米吧。给个具体的数量,那就100本书吧。很多人知识结构不好而且不系统,因为在特定领域有一个足够量的知识量+足够良好的知识结构,系统化以后就足以应对大量未曾遇到过的问题。

奉劝自学者:构建特定领域的知识结构体系的路径中再也没有比学习该专业的专业课程更好的了。如果我的知识结构体系足以囊括面试官的大部分甚至吞并他的知识结构体系的话,读到他言语中的一个词我们就已经知道他要表达什么,我们可以让他坐“上位”毕竟他是面试官,但是在知识结构体系以及心理上我们就居高临下。

所以,阅读一百本计算机著作吧,少年!

《数据结构与算法分析:C++描述(第3版)》 维斯 (Mark Allen Weiss) (作者), 张怀勇 (译者), 等 (译者)

《数据结构与算法分析:C++描述(第3版)》是数据结构和算法分析的经典教材,书中使用主流的程序设计语言C++作为具体的实现语言。书的内容包括表、栈、队列、树、散列表、优先队列、排序、不相交集算法、图论算法、算法分析、算法设计、摊还分析、查找树算法、k-d树和配对堆等。《数据结构与算法分析:C++描述(第3版)》适合作为计算机相关专业本科生的数据结构课程和研究生算法分析课程的教材。本科生的数据结构课程可以使用《数据结构与算法分析:C++描述(第3版)》第1章~第9章,多学时课程还可以讲解第10章;研究生算法分析课程可以使用第6章~第12章。

更多计算机宝库...