Mysql索引

索引有哪些类型，索引是什么样的一种结构，哪些字段适合索引，聚合索引和非聚合索引的区别？为什么说索引会降低插入、删除、修改等维护任务的速度？B树、B-树、B+树都是什么？什么叫回表

mysql数据库的Innodb引擎所有的表都默认建立在索引之上的，也就是聚集索引，而主键就是聚集索引，所以主键只能建一个。普通索引也就是非聚集索引，可以多个。 索引的数据结构是B+树也就是平衡树。查找数据的时候根据索引查找数据所在位置然后取到数据。查找普通索引的时候是先根据普通索引找到主键再根据主键定位到数据，也就是数据是和主键一起存储的。

对于工作经验丰富的程序员来说，都已经知道了什么是索引  为什么需要索引。使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。这样的方式自然减少了服务器在响应时所需要对数据库扫描的数据量。

逻辑分类

从逻辑的角度来对索引进行划分的话，可以分为单列索引、全文索引、组合索引和空间索引。其中单列索引又可分为主键索引、唯一索引和普通索引。这里的逻辑可以理解为从 SQL 语句的角度，或者是从数据库关系表的角度。下面就简单介绍这些索引的作用和用法，以及在修改表的时候如何添加索引。

主键索引

即主索引，根据主键建立索引，不允许重复，不允许空值；

主键：数据库表中一列或列组合（字段）的值，可唯一标识表中的每一行。

加速查询 + 列值唯一（不可以有null）+ 表中只有一个

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

唯一索引

用来建立索引的列的值必须是唯一的，允许空值。唯一索引不允许表中任何两行具有相同的索引值。比方说，在 employee 表中职员的姓 name 上创建了唯一索引，那么就表示任何两个员工都不能同姓。

加速查询 + 列值唯一（可以有null）

ALTER TABLE 'table_name' ADD UNIQUE index_name('col');

普通索引

用表中的普通列构建的索引，没有任何限制。

仅加速查询

ALTER TABLE 'table_name' ADD INDEX index_name('col');

全文索引

用大文本对象的列构建的索引

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

组合索引

用多个列组合构建的索引，这多个列中的值不允许有空值。

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

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

在对多列组合建立索引时，会遵循「最左前缀」原则。

最左前缀原则：顾名思义，就是最左优先，上例中我们创建了 (col1, col2, col3) 多列索引,相当于创建了 (col1)  单列索引，(col1, col2) 组合索引以及 (col1, col2, col3) 组合索引。

所以当我们在创建多列索引时，要根据业务场景，将 where 子句中使用最频繁的一列放在最左边。

空间索引

对空间数据类型的字段建立的索引，底层可通过 R 树实现。只不过使用较少，了解即可。

 

    实现原理

我们知道，索引的底层本身就是通过数据结构来进行实现的。那么根据其底层的结构，常见的索引类型可分为哈希索引，BTree 索引，B+Tree 索引等。这里我们就主要来介绍这三种索引背后的实现机制。

哈希索引

顾名思义，哈希索引是通过哈希表实现的。哈希表的特点在之前的文章「九大数据结构」中已经详细介绍过。通过哈希表的键值之间的对应关系，能够在查询时精确匹配索引的所有列。哈希索引将所有的根据索引列计算出来的哈希码存储在索引中，同时将指向每个数据行的指针保存在哈希表中。

上图是通过哈希索引查询行数据的示意图，可以发现哈希索引同样会发生哈希冲突，并且是通过链地址法解决冲突的。当发送冲突时，还需要对链表进行遍历对比，才能够找到最终的结果。

在 MySQL 中，只有 Memory 存储引擎显式的支持哈希索引，而innodb是隐式支持哈希索引的。

这里的隐式支持是指，innodb引擎有一个特殊的功能 “自适应哈希索引”，当innodb注意到一些索引值被使用的非常频繁时，且符合哈希特点（如每次查询的列都一样），它会在内存中基于 B-Tree 索引之上再创建一个哈希索引。这样就让 BTree 索引也具有哈希索引的一些有点。这是一个完全自动的、内部的行为。

由于哈希结构的特殊性，其用于非常高的检索效率，通过哈希函数的映射可以一步到位。但是同样也是因为结构的特殊，导致哈希索引只适用于某些特定的场合。哈希索引的限制[1]：

1. 不支持范围查询，比如 WHERE a > 5；只支持等值比较查询，包括＝、IN 、<=>

2. 无法被用来避免数据的排序操作；因为经过了哈希函数的映射过程，使得丢失了哈希前后的大小关系，从而无法按照索引值的顺序存储。

3. 不支持部分索引列的匹配查找，因为哈希索引始终使用索引列的全部内容来计算哈希值。例如在数据列 (A, B) 上建立哈希索引，如果查询只有数据列 A，则无法使用该索引。

4. 无法避免表扫描。因为当出现哈希冲突的时候，存储引擎必须遍历链表（拉链法）中所有的行指针，逐行进行比较，直到找到所有符合条件的行。

5. 哈希冲突很多的情况下，其索引维护的代价很高，并且性能并不一定会比 BTree 索引高。

BTree 索引

BTree 实际上是一颗多叉平衡搜索树。从名字可以看出，BTree 首先是一颗多叉搜索树，这意味着它是具有顺序的；其次 BTree 还是平衡的，这意味着它的左右子树高度差小于等于1。

事实上一颗 BTree 需要满足以下几个条件：

每个叶子结点的高度都是一样的；

每个非叶子结点由 n-1 个 key 和 n 个指针 point 组成，其中 d<=n<=2d, key 和 point 相互间隔，结点两端一定是 key；

叶子结点指针都为 null；

非叶子结点的key都是 [key, data] 二元组，其中 key 表示作为索引的键，data 为键值所在行的数据；

一颗常见的BTree树见下图。

这是一颗三阶的BTree，可通过键值的大小排序进行数据的查询和检索，其中叶子节点的指针都为空，因此省略没画。从上图可以发现，BTree 的树形状相较于我们之前常见的二叉树等结构，更为扁平和矮胖。

之所以这样设计，还是跟磁盘读取的特点有关。我们知道在建立索引时，也是需要占据物理空间的。而实际上当数据量比较大的时候，索引文件的大小也十分吓人。考虑到一个表上可能有多个索引、组合索引、数据行占用更小等情况，索引文件的大小可能达到物理盘中数据的1/10，甚至可达到1/3。

这就意味着索引无法全部装入内存之中。当通过索引对数据进行访问时，不可避免的需要对磁盘进行读写访问。同时我们还知道，内存的读写速度是磁盘的几个数量级。因此在对索引结构进行设计时要尽可能的减少对磁盘的读写次数，也就是所谓的磁盘 I/O 次数。

这也就是索引会采用 BTree 这种扁平树结构的原因，树的层数越少，磁盘I/O的次数自然就越少。不仅如此，我们上面提到过磁盘预读的局部性原理。根据这个原理再加上页表机制，能够在进行磁盘读取的时候更大化的提升性能。

BTree 相较于其它的二叉树结构，对磁盘的 I/O 次数已经非常少了。但是在实际的数据库应用中仍有些问题无法解决。

一是无法定位到数据行。通过 BTree 可以根据主键的排序定位出主键的位置，但是由于数据表的记录有多个字段，仅仅定位到主键是不够，还需要定位到数据行。虽然这个问题可以通过在 BTree 的节点中存储数据行或者增加定位的字段，但是这种方式会使得 BTree 的深度大幅度提高，从而也导致 I/O 次数的提高。

二是无法处理范围查询。在实际的应用中，数据库范围查询的频率非常高，而 BTree 只能定位到一个索引位置。虽然可以通过先后查询范围的左右界获得，但是这样的操作实际上无法很好的利用磁盘预读的局部性原理，先后查询可能会造成通过预读取的物理地址离散，使得 I/O 的效率并不高。

三是当数据量一大时，BTree的高度依旧会变得很高，搜索效率还是会大幅度的下降。

问题总是推动改进的前提。基于以上的问题考虑，就出现了对 BTree 的优化版本，也就是 B+Tree。

B+Tree索引

B+Tree 一看就是在 BTree 的基础上做了改进，那么到底改变了什么呢。废话不多说，先上图。

上图实际上是一种带有顺序索引的 B+Tree，与最基本的 B+Tree 的区别就在于叶子节点是否通过指针相连。一般数据库中常用的结构都是这种带有顺序索引的 B+Tree。后文探讨的也都是带顺序索引的 B+Tree 结构。

对比 BTree 和 B+Tree，我们可以发现二者主要在以下三个方面上的不同：

1. 非叶子节点只存储键值信息，不再存储数据。

2. 所有叶子节点之间都有一个链指针，指向下一个叶子节点。

3. 数据都存放在叶子节点中。

看着 B+Tree，像不像是一颗树与一个有序链表的结合体。因而其实 B+Tree 也就是带有树和链表的部分优势。树结构使得有序检索更为简单，I/O 次数更少；有序链表结构使得可以按照键值排序的次序遍历全部记录。

B+Tree 在作为索引结构时能够带来的好处有：

一，I/O 次数更少。这是因为上文也说过，BTree 的节点是存放在内存页中的。那么在相同的内存页大小（一般为4k）的情况下，B+Tree 能够存储更多的键值，那么整体树结构的高度就会更小，需要的 I/O 次数也就越小。

二，数据遍历更为方便。这个优势很明显是由有序链表带来的。通过叶子节点的链接，使得对所有数据的遍历只需要在线性的链表上完成，这就非常适合区间检索和范围查询。

三，查询性能更稳定。这自然是由于只在叶子节点存储数据，所以所有数据的查询都会到达叶子节点，同时叶子节点的高度都相同，因此理论上来说所有数据的查询速度都是一致的。

正是由于 B+Tree 优秀的结构特性，使得常用作索引的实现结构。在 MySQL 中，存储引擎 MyISAM 和 InnoDB 都分别以 B+Tree 实现了响应的索引设计。

 

    物理存储

虽说 B+Tree 结构都可以用在 MyISAM 和 InnoDB，但是这二者对索引的在物理存储层次的实现方式却不相同。InnoDB 实现的是聚簇索引，而 MyISAM 实现的却是非聚簇索引。在介绍聚簇索引之前，我们需要先了解以下啥是佩奇，不对，是啥是「主键索引」和「辅助索引」。

其实概念很简单。我们刚刚不是在讲 B+Tree 的时候说过，树的非叶子节点只存储键值。没错就是这个键值，当这个键值是数据表的主键时，那么所建立的就是主键索引；当这个键值是其它字段的时候，就是辅助索引。因而可以得出，主键索引只能有一个，而辅助索引却可以有很多个。

聚簇索引和非聚簇索引的区别也就是根据其对应的主键索引和辅助索引的不同特点而实现的。

聚簇索引

说回聚簇索引。先丢个定义。

聚簇索引的主键索引的叶子结点存储的是键值对应的数据本身；辅助索引的叶子结点存储的是键值对应的数据的主键键值。

这句话的信息量挺大的。首先，分析第一句话，主键索引的叶子节点存储的是键值对应的数据本身。

我们知道，主键索引存储的键值就是主键。那么也就是说，聚簇索引的主键索引，在叶子节点中存储的是主键和主键对应的数据。数据和主键索引是存储在一起的，一起作为叶子节点的一部分。

然后，分析第二句话，辅助索引的叶子结点存储的是键值对应的数据的主键键值。

我们又知道，辅助索引存储的键值是非主键的字段。那就也就是说，通过辅助索引，可以找到非主键字段对应的数据行中的主键。

重点来了。当然主键索引和辅助索引一结合，能干啥呢。当直接采用主键进行检索时，可通过主键索引直接获得数据；而当采用非主键进行检索时，先需要通过辅助索引来获得主键，然后再通过这个主键在主键索引中找到对应的数据行。

举个例子吧。假设有这么一个数据表。

那么采用聚簇索引的存储方式，对应的主键索引为：（主键为ID）

对应的辅助索引为：（键值为Name，大概的意思）：

所以当使用where ID = 7这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主键索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

最后把以上过程整理总结一下，聚簇索引实际上的过程就分为以下两个过程。现在这个图应该能够看懂了吧。

非聚簇索引

学完了聚簇索引，非聚簇索引就简单多啦。同样，先上定义。

非聚簇索引的主键索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点都存储指向键值对应的数据的物理地址。

与聚簇索引来对比着看，上面的定义能够说明什么呢。首先，主键索引和辅助索引的叶子结点都存储着键值对应的数据的物理地址，这说明无论是主键索引还是辅助索引都能够通过直接获得数据，而不需要像聚簇索引那样在检索辅助索引时还得多绕一圈。

同时还说明一个点，叶子结点存储的是物理地址，那么表示数据实际上是存在另一个地方的，并不是存储在B+树的结点中。这说明非聚簇索引的数据表和索引表是分开存储的。

同样，对非聚簇索引的检索过程来个总结。

无论是主键索引还是辅助索引的检索过程，都只需要通过相应的 B+Tree 进行搜索即可获得数据对应的物理地址，然后经过依次磁盘 I/O 就可访问数据。

对比聚簇索引和非聚簇索引，可以发现二者最主要的区别就是在于是否在 B+Tree 的节点中存储数据，也就是数据和索引是否存储在一起。这个区别导致最大的问题就是聚簇索引的索引的顺序和数据本身的顺序是相同的，而非聚簇索引的顺序跟数据的顺序没有啥关系。

 

   索引优化

介绍了这么多的索引，其实最终都是为了建立高性能的索引策略，对数据库中的索引进行优化。索引的优化有很多角度，针对特定的业务场景可采用不同的优化策略。这里考虑到文章篇幅，就不具体介绍，下次再出一篇专门讲索引优化的文章。简单列举一下在进行优化时可以考虑的几个方向：

1 独立的列。索引列不能是表达式的一部分，也不能是函数的参数。

2 前缀索引和索引选择性。这二者实际上是相互制约的关系，制约条件在于前缀的长度。一般应选择足够长的前缀以保证较高的选择性（保证查询效率），同时又不能太长以便节省空间。

3 尽量使用覆盖索引。覆盖索引是指一个索引包含所有需要查询的字段的值，这样在查询时只需要扫描索引而无须再去读取数据行，从而极大的提高性能。

4 使用索引扫描来做排序。要知道，扫描索引本身是很快的，在设计索引时，可尽可能的使用同一个索引既满足排序，又满足查找行数据。

最后，在建立索引时给几个小贴士：

1 优先使用自增key作为主键

2 记住最左前缀匹配原则

3 索引列不能参与计算

4 选择区分度高的列作索引

5 能扩展就不要新建索引

总结

索引在数据库中是一个非常重要的知识点！

上面谈的其实就是索引最基本的东西，

• 最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，MySQL会一直向右匹配直到遇到范围查询 （>,<,BETWEEN,LIKE）就停止匹配。

• 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col)/COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。

• 索引列不能参与计算，尽量保持列“干净”。比如， FROM_UNIXTIME(create_time)='2016-06-06' 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ：create_time=UNIX_TIMESTAMP('2016-06-06')。

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

• 单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引(经指正，在MySQL5.0以后的版本中，有“合并索引”的策略，翻看了《高性能MySQL 第三版》，书作者认为：还是应该建立起比较好的索引，而不应该依赖于“合并索引”这么一个策略)。

• “合并索引”策略简单来讲，就是使用多个单列索引，然后将这些结果用“union或者and”来合并起来