文档章节

数据库索引的原理

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
海量数据处理之数据库索引

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

城固如春
2016/10/17
6
0
微服务化的数据库设计与读写分离

本文由 网易云 发布 作者:刘超,网易云解决方案架构师 数据库永远是应用最关键的一环,同时越到高并发阶段,数据库往往成为瓶颈,如果数据库表和索引不在一开始就进行良好的设计,则后期数据...

wangyiyungw
05/15
0
0
PHPer、Laravel常见面试题及答案

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

angkee
2017/09/24
0
0

没有更多内容

加载失败,请刷新页面

加载更多

方之熙博士被任命为RISC-V基金会中国顾问委员会主席,加速RISC-V ISA在中国的应用

中国顾问委员会将就RISC-V基金会的教育和应用推广战略提供指导 今天在中国乌镇举行的世界互联网大会(World Internet Conference)上,RISC-V基金会(RISC-V Foundation)宣布,半导体行业资深人...

whoisliang
22分钟前
0
0
为了用户体验,不要做浏览器兼容

读者看到文章标题也许会感到奇怪,按照通常的经验来说,为了用户体验应该做浏览器兼容,以便让不同的浏览器用户都能有好的体验,从而增加网站的流量,但是我认为做浏览器兼容属于同样的工作重...

Bob2100
23分钟前
0
0
分布式定时任务架构 (二) xxl-job二次开发实践

4个月前,公司有任务调度的需求,需要一周内完成,时间非常紧。 需求有三点: web界面编辑cron表达式,启动,停止任务 接入公司的rpc成本较低,公司有自研的rpc,研发人员希望共用同一套注解 ...

勇哥和你一起学技术
39分钟前
0
0
React和Redux的连接react-redux

通过Redux文档来了解react-redux,在一段时间的实践后准备翻一翻源代码,顺便做些相关的总结。我看的代码的npm版本为v4.0.0,也就是说使用的React版本是0.14.x。 react-redux提供两个关键模块...

前端攻城老湿
今天
1
0
1、Mybatis连接池配置 和 Context.xml的配置

注意: (1)mybatis 3.2 之前 通过 set get 方法 获取相关属性。之后通过属性姓名获取相关关联。 (2)mybatis 映射 优先为 sql 语句中的别名与实体类的属性进行映射。 (3)jndi Java用于调...

KingFightingAn
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部