说一说数据库-索引

前言 

    索引是对数据库表中的一列或多列的值,进行排序的一种结构 ,使用索引可以快速访问数据库表中的特定信息,是加快数据库查询的技术。通俗理解,数据库索引就是现实生活中字典的索引。

索引的优缺点

  • 优点:

索引可以避免全表扫描;

创建系统唯一性索引,可以保证每一行数据的唯一性;

大大提高数据检索的速度;

加快表与表之间的链接,特别是具有主、外键关系的表;

在针对使用order by和groupby子句进行数据检索时,可以显著地减少分组和排序的时间;

  • 缺点:

创建和维护索引是需要耗费时间的,这种时间会随着数据量的增加而增加;

索引需要占用物理空间,除了数据表占用数据空间外,每一个索引还要占用一定的物理空间,如果要建立聚集索引,那么需要的空间就会更大;

当表中的数据进行增加、删除和修改的时候,索引也要动态地维护,这样就降低了数据的维护速度;

创建索引的建议

    没必要给每个列都创建索引,创建太多的索引反而会影响性能。因此在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,在哪些列上不能了创建索引。下面列出一些经验总结的建议:

  • 应该创建索引的列:

在经常需要搜索的列,也就是Select后跟的列;

在主键的列上;

在经常使用连接外键的列上;

在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;

在经常使用Where子句中的列创建索引,加快条件的判断速度;

  • 不应该创建索引的列:

查询中很少使用得列不应该创建索引。创建的话有些得不偿失;

对于那些只有很少数据值得列也不需要创建索引,因为,这些列的取值很少,例如员工表中的性别列,一般都是bit类型,它有两个值0和1,但是它并不是唯一的,每行都有,索引在搜索的时候会搜索所有的0或1的行,造成搜索范围变大,所以增加索引并不能明显的加快检索速度;

对于text、image、varchar(max)和bit数据类型的列不应该增加索引,这是因为,这些列值的数据量要么量相当大,要么取值很少;
当修改性能远远大于检索性能的时候,不应该创建索引。从上面索引的缺点知道,修改性能和检索性能是互相矛盾的。当增加索引时,就会提高检索性能,但会降低修改性能。当减少索引时,就会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引;

索引的类型

    根据索引的顺序与数据表的物理顺序是否相同,可以把索引分成两种类型:一种是数据表的物理顺序与索引顺序相同的聚集索引,另一种是数据表的物理顺序与索引顺序不同的非聚集索引。

  • 使用聚集索引和非聚集索引的建议:

每个表只能有一个聚集索引,因为表中数据的物理顺序是唯一的

在创建任何非聚集索引之前先创建聚集索引,这是因为聚集索引改变了表中行的物理顺序,数据行按照一定的顺序排列,并且自动维护这个顺序。

聚集索引的平均大小大约是数据表的5%,但是,实际的聚集索引的大小 常常根据索引列的大小而变化,因此一定要保证有足够的空间来创建聚集索引

数据库索引原理

    想要更好的使用索引就得明白索引在数据库中的结构是怎样的?在数据库中怎么存储的?这里将以SQL Server为例讲解。

    在SQL Server系统中,可管理的最小空间单元就是页,一个页是8KB字节的物理空间,在数据文件中全部是分隔成这样的页。在插入数据的时候,数据按照时间顺服放置在数据也上。一般来说,放置数据的顺序和数据本身的逻辑关系之间是没有任何联系的。另外,因为数据是连续存放的,所以还存在一页写不下,再写到下页的情况,这叫做页分解。

    表和索引都是以页为单位存储的,这些表页和索引页又包含在一个或多个分区中。分区是用户定义的数据组织的单元。默认情况下,表或索引只有一个分区,其中包含所有的表页和索引页。该分区驻留在单个文件组中。

    当表或索引使用多个分区时,数据将被水平分区,以便根据指定的列将行组映射到各个分区。分区可以放在数据库中的一个或多个文件组中。对数据进行查询 或更新时,表或索引将视为单个逻辑实体。如下图所示

具有分区的表组织结构

    SQL Server表使用两种方法来组织分区中的数据页:B-Tree和堆。先来说一下堆


    简单来说,堆就是不含聚集索引的表,表中的数据没有任何顺序。平常我们表的数据在没建立索引之前都是存储在堆上的。(注意,这里的堆不同于.NET CLR上的堆)
    默认情况下,一个堆有一个分区。当堆有多个分区时,每个分区有一个堆结构,其中包含该特定分区的数据。例如,如果一个堆有四个分区,则有四个堆结构,每一个分区都有一个堆结构。
    根据堆中的数据类型,每个堆结构将有一个或多个分配单元来存储和管理特定分区的数据。每个堆中的每个分区至少有一个IN_ROW_DATA分配单元。如果堆中包含大型对象(LOB)列,则该堆得每个分区还将有一个LOB_DATA分配单元。如果堆中包含超过8060字节大小限制的可变长列,则该堆得每个分区还将有一个ROW_OVERFLOW_DATA分配单元。
    SQL Server 使用 IAM 页在堆中移动。堆内的数据页和行没有任何特定的顺序,也不链接在一起。 数据页之间唯一的逻辑连接是记录在 IAM 页内的信息。可以通过扫描 IAM 页对堆进行表扫描或串行读操作来找到容纳该堆的页的扩展盘区。因为 IAM 按扩展盘区在数据文件内存在的顺序表示它们,所以这意味着串行堆扫描连续沿每个文件进行。使用 IAM 页设置扫描顺序还意味着堆中的行一般不按照插入的顺序返回。

    下图说明 SQL Server 数据库引擎 如何使用 IAM 页检索具有单个分区的堆中的数据行。

IAM 页从单个分区堆中检索数据

B-Tree

    聚集索引和非聚集索引都是用B-Tree来创建的,索引 B -Tree中的每一页称为一个索引节点。B-Tree的顶端节点称为根节点。索引中的底层节点称为叶节点。根节点与叶节点之间的任何索引级别统称为中间级。

    下图显示了B-Tree的结构

b-tree-index.png

聚集索引
    聚集索引包括索引页和数据页,索引页存放索引和下一层的指针,数据页用于存放数据,叶节点包含数据页,根节点和中间级节点包含索引页。每级索引页均被链接在双向链接列表中。在聚集索引中,数据值得顺序总是按照升序排列的。

   下图显式了聚集索引单个分区中的结构。

聚集索引的级别

非聚集索引
    非聚集索引与聚集索引具有相同的 B-tree结构,它们之间的显著差别在于以下两点:

  • 基础表的数据行不按非聚集键的顺序排序和存储。
  • 非聚集索引的叶层是由索引页而不是由数据页组成。

    既可以使用聚集索引来为表或视图定义非聚集索引,也可以根据堆来定义非聚集索引。非聚集索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。

    下图说明了单个分区中的非聚集索引结构。

非聚集索引的级别

索引的特征

    索引有两个特征,即唯一性索引和复合索引。

    唯一性索引保证在索引列中的全部数据时唯一的,不会包含重复数据。如果表中已经有一个主键约束或者唯一性约束,那么当创建表或者修改表时,SQL Server自动创建唯一性索引。不过千万不要将创建唯一性索引当成保证数据唯一的方法,而应该创建主键约束或唯一性约束来保证。

    复合索引就是一个索引创建在两个列或者多个列上。在搜索时,当两个或者多个列作为一个关键值时,最好在这些列上创建符合索引。

索引与全文索引

    全文索引与普通的索引不同。普通的索引时以B-Tree结构来维护的,而全文索引是一种特殊类型的基于标记的功能性索引,是由SQL Server全文引擎服务创建和维护的。主要用来提高对大量文本数据搜索的性能。

总结

   聚集索引就是一本书的目录,这个目录上会写 第几章在哪一页,第几节在哪一页,这些都是固定的,你每次去看都一样,这个目录就影响了你要查找的内容的具体位置,比如第101页,也就是比如你要找select语句的章节,那么就是比如第5章的第2节,就是专门讲的select语句的写法。

   而非聚集索引,就是书后面的索引,翻开书的最后的索引,你就能看到 select 这个关键字,出现在了 这个关键字:第100页,第108页,第200页,第202页等等,这样方便当你要找一个关键字的时候,迅速的找到相关的页面。

posted on 2016-08-09 07:47  Khadron  阅读(600)  评论(0编辑  收藏

导航