数据库学习笔记之索引详解

索引简介

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

索引的一个主要目的就是加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

如果说把数据库看作字典的话，那么索引可以被看作目录。

在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL的查询和运行更加高效。

索引是对数据库表中一个或多个列（例如，employee 表的姓名 (name) 列）的值进行排序的结构。

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

从数据搜索实现的角度来看，索引也是另外一类文件/记录，它包含着可以指示出相关数据记录的各种记录。其中，每一索引都有一个相对应的搜索码，字符段的任意一个子集都能够形成一个搜索码。

这样，索引就相当于所有数据目录项的一个集合，它能为既定的搜索码值的所有数据目录项提供定位所需的各种有效支持 。

索引类型

数据库索引好比是一本书前面的目录，能加快数据库的查询速度。索引分为聚簇索引和非聚簇索引两种，聚簇索引 是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。有关数据库所支持的索引功能的详细信息，请参见数据库文档。

提示：尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键或唯一约束。

1、普通索引（INDEX）

create index index_name on table_name(col_name)

普通索引是最基本的索引，没有什么特别限制，主要是为了增加这个字段的查询速度。

2、唯一索引（UNIQUE）

create unique index user_id_index on user（id）

唯一索引是强调表中数据记录的唯一性，允许NULL值，是不允许其中任何两行具有相同索引值的索引。

当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。

数据库还可能防止添加将在表中创建重复键值的新数据。

例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。

3、主键索引（PRIMAY KEY）

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

不允许null值。

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；

4、组合索引

在多个单列索引中，MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

组合索引是多列值组成一个索引，专门用于组合搜索，效率大于索引合并。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。

5、全文索引（FULLTEXT）

全文索引技术是搜索引擎的关键技术。

试想在1M大小的文件中搜索一个词，可能需要几秒，在100M的文件中可能需要几十秒，如果在更大的文件中搜索那么就需要更大的系统开销，这样的开销是不现实的。

所以在这样的矛盾下出现了全文索引技术，有时候有人叫倒排文档技术。

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；

原理：

原理是先定义一个词库，然后在文章中查找每个词条(term)出现的频率和位置，把这样的频率和位置信息按照词库的顺序归纳，这样就相当于对文件建立了一个以词库为目录的索引，这样查找某个词的时候就能很快的定位到该词出现的位置。

问题在处理英文文档的时候显然这样的方式是非常好的，因为英文自然的被空格分成若干词，只要我们有足够大的词汇库就能很好的处理。但是亚洲文字因为没有空格作为断词标志，所以就很难判断一个词，而且人们使用的词汇在不断的变化，而维护一个可扩展的词汇库的成本是很高的，所以问题出现了。

解决出现这样的问题使“分词”成为全文索引的关键技术。有两种基本的方法：
1.二元法 它把所有有可能的每两两汉字的组合看为一个词组，这样就没有维护词库的开销。
2.词库法 它使使用词库中的词作为切分的标准，这样也出现了词库跟不上词汇发展的问题，除非你维护词库。

实际上现在很多著名的搜索引擎都使用了多种分词的办法，比如“正向最大匹配”+“逆向最大匹配”，基于统计学的新词识别，自动维护词库等技术，但是显然这样的技术还没有做到完美。

聚集索引

聚集索引是指数据库表行中数据的物理顺序与键值的逻辑(索引)顺序相同。

一个表只能有一个聚集索引,因为一个表的物理顺序只有一种情况,所以,对应的聚集索引只能有一个。

如果某索引不是聚集索引,则表中的行物理顺序与素引顺序不匹配。

与非聚集索引相比,聚集索引有着更快的检索速度。如下图,叶节点中直接包含了具体数据。

参考该文章

非聚集索引

与聚集索引不同,非聚集索引的逻辑顺序与磁盘上行的物理存储顺序不同。

磁盘上的数据可以随意分布,而通过非聚集索引,可以在逻辑上为数据排序。

叶节点没有包含具体的数据,而是包含了一个指向具体数据的指针。

索引的底层数据结构的实现

一般来说有如下几种：平衡树(例如B树（多路平衡搜索树）)，B+树，Hashes (哈希)等

关于B数和B+树，可以参考这里。

MYSQL索引的底层数据结构主要是由B+树构成索引，参考这里。

B+Tree对比BTree的优点：
`
1、磁盘读写代价更低
　　一般来说B+Tree比BTree更适合实现外存的索引结构;
　　因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构，即磁盘的最小存储单位是扇区（sector），而操作系统的块（block）通常是整数倍的sector，操作系统以页（page）为单位管理内存，一页（page）通常默认为4K，数据库的页通常设置为操作系统页的整数倍。
　　因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找，已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少。
　　因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。

2、查询速度更稳定
　　由于B+Tree非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。

索引的优缺点

优点

1.大大加快数据的检索速度;
2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
3.加速表和表之间的连接;
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点

1.索引需要占物理空间。
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

关于复合索引的补充

可以参考这里。