什么是索引？为什么要建立索引？

认识索引是什么东西非常关键，一个非常恰当的比喻就是书的目录页与书的正文内容之间的关系，为了方便查找书中的内容，通过对内容建立索引形成目录。
索引主要用于快速找出在某个列中有特定值的行，倘若不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据，而不必查找所有数据，那么将会节省很大一部分时间。
同时，索引它也是一个文件，它是要占据物理空间的。

比如对于MyISAM存储引擎来说:

.frm后缀的文件存储的是表结构。
.myd后缀的文件存储的是表数据。
.myi后缀的文件存储的就是索引文件。

对于InnoDB存储引擎来说:

.frm后缀的文件存储的是表结构。
.ibd后缀的文件存放索引文件和数据(需要开启innodb_file_per_table参数)

因此，当你对一张表建立索引时，索引文件的大小也会改变，当你数据表中的数据因为增删改变化时，索引文件也会变化的，只不过MySQL会自动维护索引，这个过程不需要你介入，这也是为什么不恰当的索引会影响MySQL性能的原因。

总结：

索引是按照特定的数据结构把数据表中的数据放在索引文件中，以便于快速查找；

索引存在于磁盘中，会占据物理空间。

索引的优缺点

索引的优点：

通过创建唯一索引，可以保证每一行数据的唯一性
可以大大提高查询速度
可以加速表与表的连接
可以显著的减少查询中分组和排序的时间
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

索引的缺点：

创建索引需要时间，后期创建的索引，创建开销时间与表数据量呈正相关
创建索引时，需要对表加锁，在锁表的同时，可能会影响到其他的数据操作
索引需要磁盘的空间进行存储，如果针对单表创建了大量的索引，可能比数据文件更快达到大小上限
当对表中的数据进行CRUD的时，也会触发索引的维护，而维护索引需要时间，可能会降低数据操作的性能

索引设计的原则

不应该：

索引不是越多越好。索引太多，维护索引需要时间，同时索引也需要占用磁盘资源
频繁更新的数据，不宜建索引。数据频繁更新，触发索引频频维护，降低写速度
数据量小的表没必要建立索引。数据量过小，建索引等于多此一举，还增加了操作复杂度

应该：

重复率小的列建议生成索引。因为重复数据少，索引树查询更有效率
数据具有唯一性，建议生成唯一性索引。在数据库的层面，保证数据正确性
频繁group by、order by的列建议生成索引。可以大幅提高分组和排序效率
经常用于查询条件的字段建议生成索引。通过索引查询，速度更快

索引相关SQL

查看表中的索引

show index from {table_name}

添加索引

# 创建表
CREATE TABLE {table_name}(  
    ...
    INDEX  {index_name}({column_name})  
);

# 修改表
alter table {table_name} add unique index {index_name}({column_name}); # 唯一索引
alter table {table_name} add index {index_name}({column_name});        # 普通索引
create index {index_name} on {table_name}({column_name});              # 普通索引

删除索引

drop index {index_name} on {table_name};
alter table {table_name} drop index {index_name}

查看索引命中情况

explain select * from {table_name} where {column_name} = xxx;

索引的类型

从逻辑角度

1. 普通索引

普通索引是最基本的索引，它没有任何限制。它有以下几种创建方式：

1）直接创建索引
CREATE INDEX index_name ON table(column);

2）修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column);

3）创建表的时候同时创建索引
CREATE TABLE `table` (
 `id` int(11) NOT NULL AUTO_INCREMENT ,
 `title` char(255) CHARACTER SET utf8 NOT NULL ,
 `content` text CHARACTER SET utf8 NULL ,
 `time` int(10) NULL DEFAULT NULL ,
 PRIMARY KEY (`id`),
    INDEX index_name (title)
);

4）删除索引
DROP INDEX index_name ON table;

2. 唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

1）创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column);

2）修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column);

3）创建表的时候直接指定
CREATE TABLE `table` (
 `id` int(11) NOT NULL AUTO_INCREMENT ,
 `title` char(255) CHARACTER SET utf8 NOT NULL ,
 `content` text CHARACTER SET utf8 NULL ,
 `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title)
);

3. 主键索引

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

CREATE TABLE `table` (
 `id` int(11) NOT NULL AUTO_INCREMENT ,
 `title` char(255) NOT NULL ,
 PRIMARY KEY (`id`)
);

4. 组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀匹配原则。

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

最左前缀匹配原则：
在MySQL建立组合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从组合索引的最左边开始匹配。

5. 全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，先将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

1）创建表的时候添加全文索引
CREATE TABLE `table` (
 `id` int(11) NOT NULL AUTO_INCREMENT ,
 `title` char(255) CHARACTER SET utf8 NOT NULL ,
 `content` text CHARACTER SET utf8 NULL ,
 `time` int(10) NULL DEFAULT NULL ,
 PRIMARY KEY (`id`),
 FULLTEXT (content)
);

2）修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content);

3）直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content);

从物理存储角度

聚集索引（clustered index）
非聚集索引（non-clustered index）

简单概况一下：

聚集索引就是以主键创建的索引
非聚集索引就是除了主键以外的索引。
非聚集索引也叫做二级索引，不用纠结那么多名词，将其等价就行了。
非聚集索引在建立的时候也未必是单列的，可以多个列来创建索引。

本质区别：
表记录的排列顺序和索引的排列顺序是否一致。

从数据结构角度

1. B-Tree和B+Tree

B+树索引是B+树在数据库中的一种实现，是最常见也是数据库中使用最为频繁的一种索引。B+树中的B代表平衡（balance），而不是二叉（binary），因为B+树是从最早的平衡二叉树演化而来的。在讲B+树之前必须先了解二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree），B+树即由这些树逐步优化而来。

二叉查找树

二叉树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值。
如下图所示，就是一颗二叉查找树：

二叉查找树可以任意地构造，同样是2,3,5,6,7,8这六个数字，也可以按照下图的方式来构造：

但是这棵二叉树的查询效率就低了。因此若想二叉树的查询效率尽可能高，需要这棵二叉树是平衡的，从而引出新的定义——平衡二叉树，或称AVL树。

平衡二叉树（AVL Tree）

平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。下面的两张图片，左边是AVL树，它的任何节点的两个子树的高度差<=1；右边的不是AVL树，其根节点的左子树高度为3，而右子树高度为1；

如果在AVL树中进行插入或删除节点，可能导致AVL树失去平衡，这种失去平衡的二叉树可以概括为四种姿态：LL（左左）、RR（右右）、LR（左右）、RL（右左）。它们的示意图如下：

这四种失去平衡的姿态都有各自的定义：
LL：LeftLeft，也称“左左”。插入或删除一个节点后，根节点的左孩子（Left Child）的左孩子（Left Child）还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡。

RR：RightRight，也称“右右”。插入或删除一个节点后，根节点的右孩子（Right Child）的右孩子（Right Child）还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡。

LR：LeftRight，也称“左右”。插入或删除一个节点后，根节点的左孩子（Left Child）的右孩子（Right Child）还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡。

RL：RightLeft，也称“右左”。插入或删除一个节点后，根节点的右孩子（Right Child）的左孩子（Left Child）还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡。

AVL树失去平衡之后，可以通过旋转使其恢复平衡。下面分别介绍四种失去平衡的情况下对应的旋转方法。

LL的旋转。LL失去平衡的情况下，可以通过一次旋转让AVL树恢复平衡。步骤如下：

将根节点的左孩子作为新根节点。
将新根节点的右孩子作为原根节点的左孩子。
将原根节点作为新根节点的右孩子。

LL旋转示意图如下：

RR的旋转：RR失去平衡的情况下，旋转方法与LL旋转对称，步骤如下：

将根节点的右孩子作为新根节点。
将新根节点的左孩子作为原根节点的右孩子。
将原根节点作为新根节点的左孩子。

RR旋转示意图如下：

LR的旋转：LR失去平衡的情况下，需要进行两次旋转，步骤如下：

围绕根节点的左孩子进行RR旋转。
围绕根节点进行LL旋转。

LR的旋转示意图如下：

RL的旋转：RL失去平衡的情况下也需要进行两次旋转，旋转方法与LR旋转对称，步骤如下：

围绕根节点的右孩子进行LL旋转。
围绕根节点进行RR旋转。

RL的旋转示意图如下：

平衡多路查找树（B-Tree）

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。因此在讲B-Tree之前先了解下磁盘的相关知识。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：

show variables like 'innodb_page_size';

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到内存时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data]，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

一棵m阶的B-Tree有如下特性：

每个节点最多有m个孩子。
除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。（Ceil(m/2)为小数向上取整，如果是个3阶B-Tree，那每个节点至少有2个孩子，和下图磁盘块3和磁盘块8不符？此处没有理解）
若根节点不是叶子节点，则至少有2个孩子
所有叶子节点都在同一层，且不包含其它关键字信息
每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
ki(i=1,…n)为关键字，且关键字升序排序。
Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

B-Tree

每个节点占用一个盘块的磁盘空间（实际上，每个节点为一个页，包含若干地址连续的磁盘块），一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17,35），找到磁盘块1的指针P2。
根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26,30），找到磁盘块3的指针P2。
根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

B+Tree

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

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

非叶子节点只存储key键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。
每个节点的指针上限为2d而不是2d+1。

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

B+Tree

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

数据库中的B+Tree索引可以分为聚集索引（clustered index）和非聚集索引（non-clustered index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过非聚集索引来查询数据时，InnoDB存储引擎会遍历非聚集索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

这一点，在MyISAM和InnoDB的主键之间表现是不同的。MyISAM使用的是非聚集索引，最好的表现在MyISAM的存储文件分为索引文件(.MYI)和数据文件(.MYD)，而InnoDB使用的是聚集索引，索引和数据在一个文件里。

2. hash索引

一句话概括：基于哈希表实现，对于每一行数据，存储引擎都会对所有的索引列计算出一个哈希码，将所有的哈希码存储在哈希表中作为key，同时在哈希表中保存指向每个数据行指针，作为value存储在哈希表中。

假设有下表：
CREATE TABLE student(
    first_name varchar(20) not null,
    last_name varchar(20) not null,
    age tinyint(3) not null,
    created_at timestamp not null,
 key using hash(first_name)
) engine=memory;

如果我们要执行select last_name from student where first_name='zhang';，MySQL会先计算zhang的哈希值，然后用该值寻找对应的记录指针，最后再去比较first_name是否等于zhang。
因为哈希索引只存储对于的哈希值和行指针，所以哈希索引的结构很紧凑，查询速度非常快。但是也有一些缺点。

因为哈希索引只有哈希值与指针，所以每次查询必须回表去读取数据行。
因为哈希索引不是按照索引值顺序存储的，所以哈希索引也不能用于排序。
哈希索引不支持部分索引列查询，比如将student表的索引改为hash(first_name,last_name)，那么查询必须用到first_name,last_name才会使用到索引。
哈希索引只支持等值比较，所以<,>等范围查询是不会使用到索引的。
哈希索引也会存在哈希冲突，当出现冲突的时候，查询效率就很降低很多。
主流存储引擎不支持该类型，比如MyISAM和InnoDB。哈希索引只有Memory引擎支持。（但InnoDB有另一种实现方法：自适应哈希索引。InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引。）

3. 空间数据索引(R-Tree)

空间索引可用于地理数据存储，它需要GIS相关函数的支持，由于MySQL的GIS支持并不完善，所以该索引方式在MySQL中很少有人使用。

4. 全文索引（FULLTEXT）

全文索引主要用于海量数据的搜索，比如淘宝或者京东对商品的搜索，你不可能使用like进行模糊匹配吧，MySQL从5.6开始支持InnoDB引擎的全文索引，功能没有专业的搜索引擎比如Sphinx或Solr丰富，如果你的需求比较简单，可以尝试一下MySQL的全文索引，否则建议使用专业的搜索引擎。

总结:
1.B+Tree索引使用最广泛，主流引擎都支持。
2.哈希索引性能高，适用于特殊场合。
3.R-Tree不常用。
4.全文索引适用于海量数据的关键字模糊搜索。

索引和存储引擎之间的关系

上面讲述了索引有不同的类型，存储引擎也有不同的类型，那么索引和存储引擎之间有什么关系呢？
首先你需要知道，在MySQL中，索引是在存储引擎中实现的，并不是所有的存储引擎都支持所有的索引类型，比如哈希索引，MyISAM和InnoDB是不支持的；同样，即使对于同一类型的索引，不同的存储引擎实现的方式也可能是不同的，比如MyISAM和InnoDB对B+Tree索引，具体的实现是有差别的。

总结:
1.不同的存储引擎可能支持不同的索引类型；
2.不同的存储引擎对同一中索引类型可能有不同的实现方式。

聚集索引，非聚集索引，覆盖索引的原理

想要理解索引原理必须清楚一种数据结构「平衡树」(非二叉)，也就是b tree或者 b+ tree，重要的事情说三遍：“平衡树，平衡树，平衡树”。当然，有的数据库也使用哈希桶作用索引的数据结构，然而，主流的RDBMS都是把平衡树当做数据表默认的索引数据结构。

我们平时建表的时候都会为表加上主键，在某些关系数据库中，如果建表时不指定主键，数据库会拒绝建表的语句执行。事实上，一个加了主键的表，并不能被称之为「表」。一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐，跟我认知中的「表」很接近。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构，换句话说，就是整个表就变成了一个索引。没错，再说一遍，整个表变成了一个索引，也就是所谓的「聚集索引」。

这就是为什么一个表只能有一个主键，一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

上图就是带有主键的表（聚集索引）的结构图。其中树的所有结点（底部除外）的数据都是由主键字段中的数据构成，也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。假如我们执行一个SQL语句：

select * from table where id = 1256;

首先根据索引定位到1256这个值所在的叶结点，然后再通过叶结点取到id等于1256的数据行。这里不讲解平衡树的运行细节，但是从上图能看出，树一共有三层，从根节点至叶节点只需要经过三次查找就能得到结果。如下图：

假如一张表有一亿条数据，需要查找其中某一条数据，按照常规逻辑，一条一条的去匹配的话，最坏的情况下需要匹配一亿次才能得到结果，用大O标记法就是O(n)最坏时间复杂度，这是无法接受的，而且这一亿条数据显然不能一次性读入内存供程序使用，因此，这一亿次匹配在不经缓存优化的情况下就是一亿次IO开销，以现在磁盘的IO能力和CPU的运算能力，有可能需要几个月才能得出结果。

如果把这张表转换成平衡树结构（一棵非常茂盛和节点非常多的树），假设这棵树有10层，那么只需要10次IO开销就能查找到所需要的数据，速度以指数级别提升，用大O标记法就是O(log n)，n是记录总树，底数是树的分叉数，结果就是树的层次数。换言之，查找次数是以树的分叉数为底，记录总数的对数，用公式来表示就是：

用程序来表示就是Math.Log(100000000,10)，100000000是记录数，10是树的分叉数（真实环境下分叉数远不止10），结果就是查找次数，这里的结果从亿降到了个位数。因此，利用索引会使数据库查询有惊人的性能提升。

然而，事物都是有两面的，索引能让数据库查询数据的速度上升，而使写入数据的速度下降，原因很简单的，因为平衡树这个结构必须一直维持在一个正确的状态，增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构，因此，在每次数据改变时，DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。

聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引，如果还是没有的话，就采用Innodb存储引擎为每行数据内置的6字节ROWID作为聚集索引。

讲完聚集索引，接下来聊一下非聚集索引，也就是我们平时经常提起和使用的常规索引。

非聚集索引和聚集索引一样，同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段，假如给user表的name字段加上索引，那么索引就是由name字段中的值构成，在数据改变时，DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引，那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。如下图：

每次给字段建一个新索引，字段中的数据就会被复制一份出来，用于生成索引。因此，给表添加索引，会增加表的体积，占用磁盘存储空间。

非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据，如下图：

不管以任何方式查询表，最终都会利用主键通过聚集索引来定位到数据，聚集索引（主键）是通往真实数据所在的唯一路径。

然而，有一种例外可以不使用聚集索引就能查询出所需要的数据，这种非主流的方法称之为「覆盖索引」查询，也就是平时所说的复合索引或者多字段索引查询。文章上面的内容已经指出，当为字段建立索引以后，字段中的内容会被同步到索引之中，如果为一个索引指定两个字段，那么这个两个字段的内容都会被同步至索引之中。

先看下面这个SQL语句

//建立索引
create index index_birthday on user_info(birthday);

//查询生日在1991年11月1日出生用户的用户名
select user_name from user_info where birthday = '1991-11-1';

这句SQL语句的执行过程如下:

首先，通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值
然后，通过得到的主键ID值执行聚集索引查找，找到主键ID值对就的真实数据（数据行）存储的位置
最后，从得到的真实数据中取得user_name字段的值返回，也就是取得最终的结果

我们把birthday字段上的索引改成双字段的覆盖索引：

create index index_birthday_and_user_name on user_info(birthday, user_name);

这句SQL语句的执行过程就会变为:

通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容，然而，叶节点中除了有user_name表主键ID的值以外，user_name字段的值也在里面，因此不需要通过主键ID值的查找数据行的真实所在，直接取得叶节点中user_name的值返回即可。通过这种覆盖索引直接查找的方式，可以省略不使用覆盖索引查找的后面两个步骤，大大的提高了查询性能，如下图：

覆盖索引可以完美的解决二级索引回表查询问题。但是前提是一定得注意查询时候索引的最左侧匹配原则。

使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。

聚集索引两点关键信息：根据主键值创建了 B+ 树结构，每个叶子节点包含了整行数据。

MySQL中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

总的来说，在MySQL中MyIsAm使用的是B+tree索引结构，叶节点的data仅仅存放的是指向数据记录的一个地址，在MyIsAm中主键索引和辅助索引没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复。

在InnoDB中使用的也是B+tree索引结构，但是在实现方式来说和MyIsAm完全不同，MyIsAm的数据文件和索引文件以及表定义文件都是分开的，MyIsAm中索引文件只是存储一个指向具体数据的一个指针。但是在innodb中，Btree可以分为两种：主键索引和二级索引（也叫辅助索引）。

innodb中主键索引一定是聚集索引，表数据文件本身就是一个B+tree结构，这个树的叶节点保存了每一条记录的完整数据，这个叶节点的key就是数据的主键，innodb中二级索引保存的是索引列值以及指向主键的指针，所以我们使用覆盖索引优化其实说白了就是对MySQL的innodb索引加速的。

MyISAM引擎中leaf node存储的内容：
主键索引仅仅存储行指针；二级索引存储的也仅仅是行指针。
InnoDB引擎中leaf node存储的内容：
主键索引：聚集索引存储完整的数据（整行数据）；二级索引：存储索引列值+主键信息。

后记

这篇文章，目的主要是对MySQL的索引有个概念上的认识，以及了解索引的类型，索引和存储引擎之间的关系。

因为我本人对MySQL的使用属于菜鸟级别，更没有太多数据库调优的经验，在这里大谈数据库索引调优有点大言不惭，就当是我个人的一篇学习笔记了。

其实数据库索引调优是一项技术活，不能仅仅靠理论，因为实际情况千变万化，而且MySQL本身存在很复杂的机制，如查询优化策略和各种引擎的实现差异等都会使情况变得更加复杂。但同时这些理论是索引调优的基础，只有在明白理论的基础上，才能对调优策略进行合理推断并了解其背后的机制，然后结合实践中不断的实验和摸索，从而真正达到高效使用MySQL索引的目的。