<索引名> ON <表名(关系名)>;
e.g.
CREATE INDEX yearIndex ON movie(year);
而撤销索引:
DROP INDEX <索引名> ON <表名>;
e.g.
DROP INDEX yearIndex ON movie;
1、索引
1.1 索引的概念
我们在数据库查询过程中,即使满足给定条件的记录很少,也需要把整个表关系扫描一遍,当关系非常大时,其开销是很大的。
例如,在电影表中查询迪斯尼公司2000年制作的电影:
SELECT *
FROM movie
WHERE studioName=‘Disney‘ AND year=2000;
假设电影表中有10000条记录,其中2000年制作的电影有200个,这之中制作公司为迪斯尼的只有10个。如果不采取措施,要实现该查询,我们就要把10000个记录都挨个进行检查是否满足条件。如果有某种方法能让我们只取出年份为2000年的200条记录,然后再去找制作公司为迪斯尼的,显然效率要高很多。
确实有这样的方法,就叫做
索引,它是一种
为表中的给定字段提供存取路径的数据结构。
所以什么是索引?上面这个解释完全不明白,我们还是形象一点说明,网友 elysee 在它的博客《数据库优化实践【索引篇】》中讲了一个故事,非常形象,但我总理解起来有些不妥,就按照自己的意思修改了一下,故事如下:
很久以前,在一个古城的的大图书馆中珍藏有成千上万本书籍,但书架上的书没有按任何顺序摆放,因此每当有人询问某本书时,图书管理员只有挨个寻找,每一次都要花费大量的时间。
更糟的是图书馆的图书越来越多,图书管理员的工作变得异常痛苦。
有一天,图书馆来了一个聪明的小伙子,他看到图书管理员的痛苦工作后,想出了一个办法,他说:“你把所有书架,按照英文字母分成26个部分,每本书根据书名的字母顺序,放到相应的书架上去。比如《阿凡提》就放在A书架中,如果有《阿凡达》,也放在A书架,且根据书名字母顺序《阿凡达》(afd)放在《阿凡提》(aft)前面”,这样一来,如果有人指定了书籍的名字,那么图书管理员很快就可以找到它的位置了。(图书按照书名的字母顺序放在对应的字母书架上,就像创建聚集索引,即表中的所有行会在文件系统上根据书名进行物理排序,当查询表中任一行时,数据库首先使用聚集索引找到对应的数据页,就像首先找到对应的字母书架一样;然后按顺序就可以找到目标行,就像找到书架上的书一样)于是图书管理员开始分类整理放置,为此他花了整整一周时间,最后,他发现找书的效率确实大大提高了。
(在一个表上只能创建一个聚集索引,就像书只能按一种规则摆放一样)但问题并未完全解决,有很多人想看某个作者的所有书,图书管理员无奈又只有扫描所有图书的作者,进行挨个寻找,时间又变得太长了,因此他向那个聪明的小伙子求助。
(这就好像你给book表增加了索引bookName,但除此之外没有建立其它索引,当使用bookAuthor进行检索时,数据库引擎又只要进行全表扫描,逐个寻找)聪明的小伙告诉图书管理员,那就创建一个目录文档好了,文档中将图书按照作者分类,将书籍重新排列,并把作者的图书和图书对应的书架位置一起记录下来就好了,不用去动图书真正的位置。这样,一旦有人指定作者,那么根据作者分类的目录文档,就可以找到该作者的书了。
于是图书管理员又花费时间赶紧整理了一个目录文档,果然有效,然后他又
开始了新的思考,读者可能还会根据图书的其它属性来找书,如书的类型,是小说?诗歌?还是其他什么,于是他用这个办法为书的种类创建了目录,现在可以根据书名、作者和种类迅速找到图书了,图书管理员的工作变得轻松了。故事到这里就结束了。其中上面提到了个概念,叫
聚集索引,索引顺序和物理顺序相同,只能有一个,也就是这里的书名;相对的,还有
非聚集索引,可以有多个,索引顺序和物理顺序没有关系,也就是后来的作者目录,种类目录。
1.2 索引的存储
要理解这两者的区别和含义,需要先来聊聊索引的存储。首先,一条索引记录包含:
- 键值(即你定义索引时指定的所有字段的值)
- 逻辑指针(指向数据页或者另一索引页)
另外,索引不论是聚集索引还是非聚集索引,都是数据库另外开辟的空间,并不是依附在原有数据上的。所以当创建索引时,数据库系统会分配一个索引页,每当你往表中插入一行数据,数据库系统也将插入一行索引记录。此时的索引页是
根节点,如果满了,则会进行分裂,将原来指向数据页的逻辑指针,更换为指向子索引页的逻辑指针(如下图)。
1.3 索引的类型
1.3.1 聚集索引
所谓
聚集索引,就是确定表中数据的物理顺序,如上面图书的按书名排列,又或者手机电话簿的联系人按姓氏排列等。它
规定了数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。也就是,索引存储顺序和数据行的存储顺序是一致的。
如上图,我们在名字字段上建立聚集索引,当需要在根据此字段查找特定的记录时,数据库系统会根据特定的系统表查找的此索引的根,然后根据指针查找下一个,直到找到。例如我们要查询“green”,由于它介于[bennet,karsen],据此我们找到了索引页1007,在该页中“green”介于[greane, hunter]间,据此我们找到叶结点1133(也即数据结点),并最终在此页中找以了目标数据行。
1.3.2 非聚集索引
我们说
聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,所以在物理上它就没有所谓的顺序可言,也和物理存储顺序无关。
如上图,可以看到,非聚集索引的索引记录结构发生了一定的变化,它包括:
- 索引字段值(键值)
- 数据页的页指针,以及指针偏移量(相对聚集索引,新增的部分)
- 下一个索引页的指针
因为聚集索引是有顺序的,所以我们最终只需要指向索引页,按顺序就能找到,也正是如此,
聚集索引最终索引页存储的是页指针,而不是行指针。
而非聚集索引,因为无序,那么意味着非聚集索引要为每一个数据行存储一条索引记录,才能进行准确查询。这里就有了“数据页的页指针,和指针偏移量”,类似于数据行的坐标,存储在索引记录中。也就是说,非聚集索引存储的是键值和其对应的数据坐标,又为了索引分页,所以也包含第三个部分,用来存储下一个索引页指针。(就像之前那个故事,如果书籍已经按名字排序,你要找《朝花夕拾》,你知道在Z书架即可;如果书籍还是乱糟糟没有排序,而你手里有个作者分类的目录文档,那你要找鲁迅的《朝花夕拾》,意味着文档上就必须记录书籍具体的位置了)
2、汉语字典的例子
如果你还不明白关于“聚集索引和非聚集索引的区别”,这里还有一个形象的例子:
1)聚集索引汉语字典,其正文本身就是一个聚集索引。
比如,我们要查“安”字,就会很自然地翻开字典的前几页,因为“安”的拼音是“an”,而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的,那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字。
同样的,如果查“张”字,那您也会将您的字典翻到最后部分,因为“张”的拼音是“zhang”。也就是说,字典的正文部分本身就是一个目录,你不需要再去查其他目录来找到您需要找的内容。正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
2)非聚集索引如果您认识某个字,您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字,不知道它的发音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去根据“偏旁部首”查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。
但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法,比如您查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。很显然,这些字并不是真正的分别位于“张”字的上下方,现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。
3、简单总结
- 索引可以提高查询效率
- 聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个
- 聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续
另外,众所周知的是,索引确实可以提高查询速度,但会影响插入、删除和更新的效率,正是因为数据变化的同时,索引也必须进行更新维护,消耗性能。所以不能盲目地创建索引,而是合理地创建和使用。
4、参考链接
- SQL索引一步到位
- 数据库优化实践【索引篇】
- 数据库进阶系列之一:漫谈数据库索引
- 聚集索引和非聚集索引的区别
浅谈SQL优化入门:3、利用索引
标签:数据 结束 html 定义 tty 汉字 10个 指定 一周