一丶什么是索引

索引是存储引擎快速找到记录的一种数据结构。数据库中的数据可以理解成字典中的单词，而索引就是目录，显而易见这是一种空间换时间的做法，目录占用了空间，但是加快了我们找到单词的速度，正如索引需要空间存储，但是利用索引我们可以快速的找到想要的数据。

二、索引的数据结构

MySQL索引使用的数据结构有BTree索引和hash索引

BTree索引:B-Tree 索引是最常见的索引

Hash索引：对于hash索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景建议选择BTree索引。

我们今天要介绍的是工作开发中最常接触到的 InnoDB 存储引擎中的 B+ 树索引。要介绍 B+ 树索引，就不得不提二叉查找树，平衡二叉树和 B 树这三种数据结构。B+ 树就是从他们仨演化来的。

工具 www.cs.usfca.edu/~galles/vis…

Hash表

索引列，存储引擎会为每行计算一个hashcode，Hash索引中存储的就是hashcode。

例如一个维护了身份证号和姓名的表，根据身份证号查找对应名字，其hash索引如下：

比如我们想查ID_card_n2对应username：

将ID_card_n2通过hash函数算出N
按顺序遍历，找到User2

当然缺点也很明显，不是有序，所以hash索引做区间查询速度很慢。比如要找身份证号在ID_card_X, ID_card_Y区间的所有用户，就须全表扫描。

二叉查找树

我们常说的二叉树：每个节点最多有两个分叉节点，左子树和右子树数据按顺序左小右大。

首先，让我们先看一张图：

从图中可以看到，我们为 user 表（用户信息表）建立了一个二叉查找树的索引。

图中的圆为二叉查找树的节点，节点中存储了键（key）和数据（data）。键对应 user 表中的 id，数据对应 user 表中的行数据。

二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。

如果我们需要查找 id=12 的用户信息，利用我们创建的二叉查找树索引，查找流程如下：

将根节点作为当前节点，把 12 与当前节点的键值 10 比较，12 大于 10，接下来我们把当前节点>的右子节点作为当前节点。
继续把 12 和当前节点的键值 13 比较，发现 12 小于 13，把当前节点的左子节点作为当前节点。
把 12 和当前节点的键值 12 对比，12 等于 12，满足条件，我们从当前节点中取出 data，即 id=12，name=xm。

利用二叉查找树我们只需要 3 次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要 6 次才能找到。

这个时候可以看到我们的二叉查找树变成了一个链表。如果我们需要查找 id=17 的用户信息，我们需要查找 7 次，也就相当于全表扫描了。

显然这种不稳定的情况，我们在选择存储数据结构的时候就会尽量避免这种的情况发生。

平衡二叉树

平衡二叉树采用的是二分法思维，平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。

当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。具体的调整方式这里就不介绍了。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

可以想象到二叉树的节点将会非常多，高度也会极其高，我们查找数据时也会进行很多次磁盘 IO，我们查找数据的效率将会极低！

为了解决平衡二叉树的这个弊端，我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是我们接下来要说的 B 树。

B 树（Balance Tree）

B 树（Balance Tree）即为平衡树的意思，下图即是一棵 B 树：

这种数据结构我们称之为B树，==B树是一种多叉平衡查找树==，如下图主要特点：

B树的节点中存储这多个元素，每个内节点有多个分叉。
节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点中都存储数据。
父节点当中的元素不会出现在子节点中。
所有的叶子节点都位于同一层，叶子节点具有相同的深度，叶子节点之间没有指针连接。

举个简单的例子，在B树中查询数据的情况：

假如我们要查询key等于10对应的数据data，根据上图我们可知在磁盘中的查询路径是：磁盘块1->磁盘块2->磁盘块6

过程如下图所示：

看到上面的情况，觉得B树已经很理想了，但是其中还是存在可以优化的地方：

B树不支持范围查询的快速查找，例如：仍然根据上图，我们想要查询10到35之间的数据，查找到10之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
如果data存储的是行记录，行的大小随着列数的增加，所占空间会变大，这时一页中可存储的数据量就会减少，树相应就会变高，磁盘IO次数就会随之增加，有待优化。

B+树：

B+树，作为B树的升级版，MySQL在B树的基础上继续进行改造，使用B+树构建索引。B+树和B树最主要的区别在于==非叶子节点是否存储数据==的问题。

B+树的大致数据结构：

B树：叶子节点和非叶子节点都会存储数据。
B+树：只有叶子节点才会存储数据，非叶子节点只存储键值key；叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

等值查询实例：

假如我们要查询key为9对应的数据data，查询路径为：磁盘块1->磁盘块2->磁盘块6。

第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，9<15，走左子树，到磁盘寻址定位到磁盘块2。
第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<9<12，到磁盘中寻址定位到磁盘块6。
第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比较，在第三个索引中找到9，取出对应的数据data，如果data存储的是行记录，直接取出data，查询结束；如果存储的是磁盘地址，还需要根据磁盘地址再次寻址定位到指定磁盘取出数据，查询终止。

范围查询实例：

假如我们想要查找9和26之间的数据，查找路径为：磁盘块1->磁盘块2->磁盘块6->磁盘块7

前三次磁盘IO：首先查找到键值为9对应的数据（定位到磁盘块6），然后缓存大结果集中。这一步和前面等值查询流程一样，发生了三次磁盘IO。
继续查询，查找到节点15之后，底层的所有叶子节点是一个有序列表，我们从磁盘块6中的键值9开始向后遍历筛选出所有符合条件的数据。
第四次磁盘IO：根据磁盘块6的后继指针到磁盘中寻址定位到磁盘块7，将磁盘块7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将数据data缓存到结果集中。
逐渐具备唯一性（后面不会再有<=26的数据），不需要再向后查找，查询结束，将结果集返回给用户。

==由上述实例可知：B+树可以保证等值和范围查询的快速查找，MySQL的索引采用的就是B+树的结构。

三、索引–物理存储区分

聚簇索引

- 将数据存储和索引放到了一块，找到了索引也就找到了数据
- 一般情况下主键会默认创建聚簇索引，如果没有定义主键，InnoDB会选择非空的唯一索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。
- 一张表只允许存在一个聚簇索引,将数据存储和索引放到了一块，找到了索引也就找到了数据

非聚簇索引

- 将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行
- 非聚簇索引也称为辅助索引

innoDB的非聚簇索引将数据存储、索引分开，索引结构的叶子节点指向了数据的对应行。非聚簇索引访问数据总是需要二次查找，第一次找到主键值，第二次根据主键值找到行数据

以user_innodb的age列为例，age列的辅助索引结构如下图：

使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后根据主键到主键索引中检索获得数据记录

非聚簇索引等值查询的情况：

select * from t_user_innodb where age=19;

在辅助索引树中获取到主键id，再根据主键id到主键索引数中检索数据的的过程称为回表查询。

磁盘IO数（从根节点开始）：辅助索引3次 + 回表过程3次。

自增主键会把数据自动向后插入，避免了插入过程中聚集索引节点分裂的问题。节点分裂会带来大范围的数据物理移动，带来磁盘IO的性能损耗，并且我们一般建议尽量不要改动主键，主键的更改也会带来page分裂，产生碎片。

四、索引–类型区分

1、类型区分

主键索引

设定为主键后，数据库自动建立索引，InnoDB为聚簇索引，主键索引列值不能为空（Null）。

唯一索引

索引列的值必须唯一，但允许有空值（Null），但只允许有一个空值（Null）。

组合索引

一个索引可以包含多个列，多个列共同构成一个组合索引。

全文索引

Full Text（MySQL5.7之前，只有MYISAM存储引擎引擎支持全文索引）。
全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在Char、VarChar 上创建。

前缀索引

在文本类型为char、varchar、text类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

2、组合索引

基本概念

组合索引也称联合索引，组合索引是指对表上的多个列建立索引

如上图表存在四个字段id,address,name,age，我们在name和age上建立索引，上图我们粗略的展示了联合索引的B+树结构。我们可以观察到在叶子节点中name是有序的，但是age无序，联合索引是按照索引定义的顺序排序的，这就导致select xxx from table where name='b'是可以根据上面定义的联合索引查找数据的，但是select xxx from table where age=12是无法走上面定义的联合索引的。这就是常说的最左前缀匹配原则的原理。

组合索引可以减少回表

如果我们执行select age,id from table where name='a' and age=10,这个时候由于我们定义的聚集索引一级包含了需要的数据就不需要进行回表操作了（这其实也被称为覆盖索引，即非聚集索引中可以查询到全部需要的列，那么就不需要走聚集索引回表查询数据）

联合索引可以优化排序

上图中的联合索引，我们可以看到，名称相同的节点，其年龄是有序的

也就是说select * from table where name='a' order by age这个语句将避免多一次的排序操作（select* from table where id=1 order by age会走主键索引拿到所有符合数据进行排序，这里说的避免一次排序操作指拿到的数据本身就是有序的所有不需要再次排序）

那么如何可以让order by更快昵——创建一个 name和 age的联合索引

最左匹配原则

（a,b）联合索引， B+ 树会先比较a列来确定下一步应该检索的方向，往左还是往右。如果a列相同再比较b列，但是如果查询条件中没有a列，B+树就不知道第一步应该从那个节点开始查起。

**假如创建的复合索引为三个字段，按顺序分别是(name,age,sex)

在查询时能利用复合索引的查询条件如下：**

1、最左前缀原则(如下四种都满足条件)

select * from user where name = ? select * from user where name = ? and age = ? select * from user where name = ? and sex = ? select * from user where name = ? and age = ? and sex = ?

12、如下是不满足最前左缀的条件（但是不是全部都不生效，如下第2原则解释）

select * from user where name = ? and sex = ? and age = ? select * from user where age = ? and sex = ? and name = ? select * from user where sex = ? and age = ? and name = ? select * from user where age = ? and sex = ? …………等等

可以说创建的idx_(name,age,sex)索引，相当于创建了(name)、(name，age)、(name,age,sex)三个索引。

使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)等就会停止匹配。

3、全文索引

1、基本概念

全文索引是为了解决需要基于相似度的查询，而不是精确数值比较。

虽然使用 like + % 也可以实现模糊匹配，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like 快 N 倍，速度不是一个数量级。

　　 SELECT * FROM table_name 　　 WHERE MATCH(column1, column2) AGAINST('word1', 'word2', 'word3')

上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。

2、特点

MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引
MySQL 5.7.6 中，提供了支持中文、日文和韩文（CJK）的内置全文 ngram 解析器，以及用于日文的可安装 MeCab 全文解析器插件
全文索引只能用于InnoDB或MyISAM表，只能为CHAR、VARCHAR、TEXT列创建
导致磁盘资源的大量占用。全文索引本身就是一个利用磁盘空间换取性能的方法。全文索引创建速度慢，而且对有全文索引的各种数据修改操作也慢
全文索引大的原因是，按照某种语言来进行分词，可能和like到的不一致

3、使用规则

MySQL 内置的修饰符，上面查询最小搜索长度时，搜索结果 ft_boolean_syntax 变量的值就是内置的修饰符，下面简单解释几个，更多修饰符的作用可以查手册

必须包含该词 - 必须不包含该词 > 提高该词的相关性，查询的结果靠前 < 降低该词的相关性，查询的结果靠后 (*)星号通配符，只能接在词后面

4、前缀索引

1、基本概念

有时需要在很长的字符列（如BLOB、TEXT或很长的VARCHAR类型的列）上创建索引，这会造成索引特别大且慢。

为了避免产生大且慢的索引，一种策略是使用MySQL索引类型中提到过的模拟哈希索引，另一种策略就是使用前缀索引。

2、优缺点

前缀索引就是选择索引列的最左n个字符来建立索引。这样就大大节约了索引空间，进而提高索引效率。但其缺点就是：MySQL无法使用前缀索引做ORDER BY 、GROUP BY 和覆盖扫描。

alter table User add index index1(email); alter table User add index index2(email(6));

第一句SQL创建的索引就是将email整个字符串作为索引；第二个SQL语句创建的索引，只取email字符串的前6个字节作为索引。

email(6)这个索引结构中每个邮箱字段只取前6个字节，所以占用的空间更少，这就是使用前缀索引的优势。

缺点：可能会额外的增加记录扫描的次数。

select id,name,email from User where email =' zhangsan@xx.com';

使用前缀索引的执行过程

从index2的索引树上，找到满足索引值是“zhangs”的记录，找到第一个是ID1；到主键索引树上查到ID1这一行，判断email的值满不满足where后的条件，不满足这一行丢弃。继续回到index2这个索引树上查下一条记录，发现如果还是"zhangs"，取出ID2，再回到ID2索引树上进行判断，如果值正确，将结果返回结果集中。重复执行以上流程，直到从index2索引树上取出的数据不是“zhangs”,循环结束。通过以上执行流程的分析你就可以知道，前缀索引会导致扫描的行数变多，这和你所指定前缀的长度有关。或许email(7)中的区分度就比email(6)高，就不会扫描那么多行。

五、执行计划

1、explain 关键字


字段	描述	补充以及示列
id	查询执行顺序： id 值相同时表示从上向下执行 id 值相同被视为一组如果是子查询，id 值会递增，id 值越高，优先级越高
select_type	主要用来分辨查询的类型，是普通查询还是联合查询还是子查询
table	显示这一行的数据是关于哪张表的。
type	system > const > eq_ref > ref > range > index > ALL	一般情况下，得保证查询至少达到range级别，最好能达到ref
possible_keys	查询条件字段涉及到的索引，可能没有使用。
key	实际使用的索引。如果为 NULL，则没有使用索引。
key_len	显示了MySQL在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。key_len 是根据表定义计算而得的，不是通过表内检索出的。
ref	显示索引的哪一列被使用了，如果可能的话，是一个常量 const。
rows	大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好
Extra	--using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置

六、总结

1、索引并非越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能，因为在表中的数据更改的同时，索引也会进行调整和更新，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。

2、如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

3、MySQL查询一张表只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。如果需要排序最好给这些列创建复合索引。

select count(1) from table1 where column1 = 1 and column2 = 'foo' and column3 = 'bar'

所以与其说是数据库只支持一条查询语句只使用一个索引，倒不如说N条独立索引同时在一条语句使用的消耗比只使用一个索引还要慢。所以如上条的情况，最佳推荐是使用index(column1,column2,column3）这种联合索引，此联合索引可以把b+tree结构的优势发挥得淋漓尽致：一条主二叉树（column=1），查询到column=1节点后基于当前节点进行二级二叉树column2=foo的查询，在二级二叉树查询到column2=foo后，去三级二叉树column3=bar查找。

4、避免filesort排序，mysql中无法利用索引完成的排序称为“文件排序”，执行计划Extra中若出现Using filesort，说明mysql会对数据使用一个外部的索引排序，而不是按照表内索引顺序进行读取，最左匹配原则也适用于排序，

MySQL索引详解（一文搞懂）