说一说数据库-索引

前言 

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

索引的优缺点

• 优点：

索引可以避免全表扫描；

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

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

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

在针对使用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 页检索具有单个分区的堆中的数据行。

B-Tree

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

    下图显示了B-Tree的结构

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

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

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

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

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

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

索引的特征

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

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

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

索引与全文索引

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

总结

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

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