java数据库之索引

一、索引简介

1.1什么是索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。在没有索引的情况下，数据库会遍历全部数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。在没有索引的情况下，数据库会遍历全部数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。

1.2 索引的性质分类：

索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

1.3 索引的优点

（1）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

（2）可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

（3）可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

（4）在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

（5）通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

1.4 索引的缺点

（1）创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

（2）索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

（3）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

1.5 为什么需要索引

数据在磁盘上是以块的形式存储的。为确保对磁盘操作的原子性，访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表类似，即它们都包含一个数据段和一个指针，指针指向下一个节点（数据块）的内存地址，而且它们都不需要连续存储（即逻辑上相邻的数据块在物理上可以相隔很远）。

鉴于很多记录只能做到按一个字段排序，所以要查询某个未经排序的字段，就需要使用线性查找，即要访问N/2个数据块，其中N指的是一个表所涵盖的所有数据块。如果该字段是非键字段（也就是说，不包含唯一值），那么就要搜索整个表空间，即要访问全部N个数据块。（在某些情况下，索引可以避免排序操作。）

二、索引的数据结构

1.1 B-Tree索引

由于B-Tree的特性，在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。

B-Tree上查找算法的伪代码如下：

1.2 B+Tree

与B-Tree相比，B+Tree有以下不同点：

每个节点的指针上限为2d而不是2d+1。

内节点不存储data，只存储key；叶子节点不存储指针。

看不懂啊，大哥

1.3 哈希索引

1.4 全文索引

1.5 为什么Mysql用B+树做索引而不用B-树

（1）先从数据结构的角度来答。应该知道B-树和B+树最重要的一个区别就是B+树只有叶节点存放数据，其余节点用来索引，而B-树是每个索引节点都会有Data域。这就决定了B+树更适合用来存储外部数据，也就是所谓的磁盘数据。（2）从Mysql（Inoodb）的角度来看，B+树是用来充当索引的，一般来说索引非常大，尤其是关系性数据库这种数据量大的索引能达到亿级别，所以为了减少内存的占用，索引也会被存储在磁盘上。（3）那么Mysql如何衡量查询效率呢？磁盘IO次数，B-树（B类树）的特定就是每层节点数目非常多，层数很少，目的就是为了就少磁盘IO次数，当查询数据的时候，最好的情况就是很快找到目标索引，然后读取数据，使用B+树就能很好的完成这个目的，但是B-树的每个节点都有data域（指针），这无疑增大了节点大小，说白了增加了磁盘IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO多耗时啊！），而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少。这是优点之一。（4）另一个优点是什么，B+树所有的Data域在叶子节点，一般来说都会进行一个优化，就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能获得全部数据，这样就能进行区间访问啦。

(数据库索引采用B+树的主要原因是 B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）)

（5）B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

（6）B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

三、索引分类

3.1 普通索引

基本的索引，它没有任何限制。

创建方式：

//标准语句：

ALTER TABLE table_name ADD INDEX index_name (column_list)

CREATE INDEX index_name ON table_name (column_list);

//还有建表的时候创建亦可

CREATE TABLE table_name (

ID INT NOT NULL,

column_listVARCHAR(16) NOT NULL,

INDEX [index_name ]

(column_list(length))

);  

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

例子：假如length为10，也就是索引这个字段的记录的前10个字符。

3.2 唯一索引：

与前面的普通索引类似，不同的就是：MySQL数据库索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

它有以下几种创建方式：

ALTER TABLE table_name ADD UNIQUE (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

//还有建表时创建

CREATE TABLE table_name (

 ID INT NOT NULL,

 column_list VARCHAR(16) NOT NULL,

 UNIQUE [index_name ]  

 (column_list(length))

 );  

3.3主键索引：

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE table_name (

ID INT NOT NULL,

 [column] VARCHAR(16) NOT NULL,

 PRIMARY KEY(ID)  

 );  

3.4全文索引：（FULLTEXT）

定义：

全文检索是对大数据文本进行索引，在建立的索引中对要查找的单词进行进行搜索，定位哪些文本数据包括要搜索的单词。因此，全文检索的全部工作就是建立索引和在索引中搜索定位，所有的工作都是围绕这两个来进行的。

此索引关键：

建立全文索引中有两项非常重要，一个是如何对文本进行分词，一是建立索引的数据结构。分词的方法基本上是二元分词法、最大匹配法和统计方法。索引的数据结构基本上采用倒排索引的结构。分词的好坏关系到查询的准确程度和生成的索引的大小。

应用：

FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。

但是要注意：对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。因为！！插入修改删除表的同时也要针对索引做一系列的处理。

创建方法：

//针对content做了全文索引：

CREATE TABLE `table` (

`id` int(11) NOT NULL AUTO_INCREMENT ,

`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

PRIMARY KEY (`id`),

FULLTEXT (content)

);

3.5多列索引（也叫组合索引）

相关概念（适用多列索引的原因）：

MySQL能在多个列上创建索引。一个索引可以由最多15个列组成。（在CHAR和VARCHAR列上，你也可以使用列的前缀作为一个索引的部分)。

一个多重列索引可以认为是包含通过合并(concatenate)索引列值创建的值的一个排序数组。

多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格（获得结果集记录数最少）的索引。

当你为在一个WHERE子句索引的第一列指定已知的数量时，MySQL以这种方式使用多重列索引使得查询非常快速，即使你不为其他列指定值。

适用场景：

1.全字段匹配

2.匹配部分最左前缀

3.匹配第一列

4.匹配第一列范围查询(可用用like a%,但不能使用like %b)

5.精确匹配某一列和和范围匹配另外一列

例子：

//假设只使用单列索引名字

 ALTER TABLE people ADD INDEX name (name);

//使用多列索引：

  ALTER TABLE people ADD INDEX height_name_age (height,name,age);

//相当于创建了(height)单列索引，(height,name)组合索引以及(height,name,age)组合索引

/*

注意：

注：在mysql中执行查询时，只能使用一个索引，如果我们在name,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。

四、索引设计优化：

（1）最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

（2）=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

（3）尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

（4）索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

（5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

（6）定义有外键的数据列一定要建立索引。

（7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

（8）对于定义为text、image和bit的数据类型的列不要建立索引。

（9）对于经常存取的列避免建立索引

五、索引失效的几种情况

（1）索引字段进行判空查询时。也就是对索引字段判断是否为NULL时。语句为is null 或is not null。

（2）对索引字段进行like查询时

（3）判断索引列是否不等于某个值时。‘!=’操作符

（4）对索引列进行运算。这里运算包括+-*/等运算。也包括使用函数

（5）复合索引中的前导列没有被作为查询条件

（6）如果条件中有or，即使其中有条件带索引也不会使用

（7）如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引。

最常见的还有数字类型，1：：NUMRIC这种

六、参考文献

http://blog.codinglabs.org/articles/theory-of-mysql-index.html