文档章节

数据库索引的原理

fengsehng
 fengsehng
发布于 2016/11/09 09:12
字数 1794
阅读 1
收藏 0

介绍:

索引是对数据库表中一个或多个列(例如,employee 表的姓名 (name) 列)的值进行排序的结构。如果想按特定职员的姓来查找他或她,则与在表中搜索所有的行相比,索引有助于更快地获取信息。

例如这样一个查询:select * from table1 where id=10000。如果没有索引,必须遍历整个表,直到ID等于10000的这一行被找到为止;有了索引之后(必须是在ID这一列上建立的索引),即可在索引中查找。由于索引是经过某种算法优化过的,因而查找次数要少的多。可见,索引是用来定位的。

分类:

唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。
当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如,如果在employee表中职员的姓(lname)上创建了唯一索引,则任何两个员工都不能同姓。

主键索引

数据库表经常有一列或多列组合,其值唯一标识表中的每一行。该列称为表的主键。
在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问。

聚集索引

在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。
如果某索引不是聚集索引,则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比,聚集索引通常提供更快的数据访问速度。

优缺点:

优点:

创建索引可以大大提高系统的性能。

第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

第二,可以大大加快数据的检索速度,这也是创建索引的最主要的原因。

第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

第四,在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

缺点:

建立索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价的

一是增加了数据库的存储空间。

二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录 。

使用场景:

在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;

1.在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;

2.在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;

3.在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

同样,对于有些列不应该创建索引。一般来说,不应该创建索引的这些列具有下列特点:

第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

第二,对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。

第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少,不利于使用索引。

第四,当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改操作远远多于检索操作时,不应该创建索引。

技术原理:

数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。

索引的实现通常使用B树及其变种B+树。

关于B+,B-树,以及搜索树的原理,看我之前相关的博客,链接如下:

B-树,B+树,B*树详解

数据结构-红黑树详解

数据结构-自平衡二叉查找树(AVL)详解

在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

这里写图片描述

总结:

上图展示了一种可能的索引方式。左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的)。为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

我的微信二维码如下,欢迎交流讨论

这里写图片描述

欢迎关注《IT面试题汇总》微信订阅号。每天推送经典面试题和面试心得技巧,都是干货!

微信订阅号二维码如下:

这里写图片描述

参考:
http://baike.baidu.com/view/2079871.htm
http://blog.csdn.net/kennyrose/article/details/7532032

© 著作权归作者所有

共有 人打赏支持
fengsehng
粉丝 4
博文 284
码字总数 214494
作品 0
朝阳
程序员
私信 提问
T-SQL查询进阶--理解SQL Server中索引的概念,原理以及其他

简介 在SQL Server中,索引是一种增强式的存在,这意味着,即使没有索引,SQL Server仍然可以实现应有的功能。但索引可以在大多数情况下大大提升查询性能,在OLAP中尤其明显.要完全理解索引的...

Zhao-Qian
2012/08/20
0
0
MySQL索引与Index Condition Pushdown

大约在两年前,我写了一篇关于MySQL索引的文章。最近有同学在文章的评论中对文章的内容提出质疑,质疑主要集中在联合索引的使用方式上。在那篇文章中,我说明联合索引是将各个索引字段做字符...

whc20011
2016/11/02
8
0
【58沈剑 架构师之路】数据库索引,到底是什么做的?

问题1. 数据库为什么要设计索引? 图书馆存了1000W本图书,要从中找到《架构师之路》,一本本查,要查到什么时候去? 于是,图书管理员设计了一套规则: (1)一楼放历史类,二楼放文学类,三楼...

张锦飞
2018/12/10
0
0
PHPer、Laravel常见面试题及答案

最近社区里面有一篇文章引起了最多程序猿的关注,分享一下 Laravel、PHPer 面试可能会遇到的问题,看评论区不少小伙伴们被难倒,对于一些问题同样难倒了我(其实有很多啦),趁着周末有空,又...

angkee
2017/09/24
0
0
海量数据处理之数据库索引

前言:本文第一部分讨论数据库的索引及其优化,主要以sql server为例,第二部分我们从Mysql讨论它背后的数据结构和算法原理。 第一部分,数据库索引及其优化 一,什么是索引   数据库索引好...

城固如春
2016/10/17
6
0

没有更多内容

加载失败,请刷新页面

加载更多

Java并发编程基础(二)

线程安全与数据同步

chendom
23分钟前
1
0
在Centos7 上安装SVN

在Centos7 上安装SVN 2017年11月16日 17:07:54 crossangles_2017 阅读数:2543 1、安装 使用yum安装非常简单: yum install subversion 2、配置 创建仓库 我们这里在/opt下建立一个名为svn的...

linjin200
25分钟前
1
0
牛津词典 2018 年度词汇 ——「有毒」!

简评:本文并没有「标题党」,牛津词典公布的 2018 年度词汇就是 Toxic. 意为「有毒的」。 2018 was toxic. Toxic 这个词是什么意思呢? 牛津词典(Oxford Dictionaries)在 Word of the Da...

极光推送
31分钟前
1
0
浅谈Service Mesh体系中的Envoy

https://blog.csdn.net/yunqiinsight/article/details/81019255

易野
40分钟前
1
0
嵌入式应用选择合适的微控制器

准备所需硬件接口列表 使用微控制器的基本硬件框图,准备一份微控制器需要支持的所有外设接口的列表。微控制器中有两种常见的接口类型需要列出。第一种是通信接口,这些是外围设备,如USB,S...

linuxCool
48分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部