文档章节

数据库索引的原理

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
PHPer、Laravel常见面试题及答案

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

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

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

城固如春
2016/10/17
6
0
读《面向程序员的数据库访问性能优化法则》

刚刚看了一下2014年中国数据库技术大会(DTCC)PPT,网易杭州研究院的一位同学介绍了一下数据库的优化与调优。 网易的Mysql优化做的很好,而且还写了本书和大家分享这些经验《深入浅出MySQL...

ifeixiang
2015/01/04
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Univalsal_ImageLoader源码结构与创建者模式 初步小结

最近在回归看Univalsal_ImageLoader源码,本想自己也实现试试写一个,看源码是为了学习看能否使用,助于自己可以写出有自己逻辑结构的代码。 首先我们初始化ImageLoader的配置初始化的时候,...

DannyCoder
41分钟前
0
0
计算卷积神经网络浮点数运算量

前言 本文主要是介绍了,给定一个卷积神经网络的配置之后,如何大概估算它的浮点数运算量。 相关代码:CalFlops,基于MXNet框架的 Scala 接口实现的一个计算MXNet网络模型运算量的demo。 正文...

Ldpe2G
今天
3
0
Sql语言与MySql数据库

1. 数据库简介 1. 数据库,就是存储数据的仓库,只能通过sql语言来访问,数据库也是一个文件系统。通常,MySQL、Oracle等数据库,也被称为关系型数据库,其保存的不仅仅只是数据,还包括数据...

江左煤郎
今天
2
0
IDEA 取消自动import .*

打开设置 > Editor > Code Style > Java > Scheme Default > Imports ① 将 Class count to use import with "*" 改为 99 (导入同一个包的类超过这个数值自动变为 * ) ② 将 Names count ......

乔老哥
今天
3
0
PostGIS学习笔记(开篇)

PostGIS事实上算是笔者开始写博客的第一篇内容。而事实上那篇博文的内容并不丰富,笔者对PostGIS的了解仍然不多,然而17年在OSGeo课程学习时对PostGIS又有了进一步了解,并逐步发现它的强大。...

胖胖雕
今天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部