1. 索引概述

1.1 索引是什么

索引是数据库中一个排序的数据结构，有助于快速查询，更新数据库中的数据

1.2 索引类型

1. 普通索引
   也叫作非唯一索引，最普通的索引，没有任何限制
2. 唯一索引
   要求键值不能重复
3. 主键索引
   特殊的唯一索引，除了唯一索引的特性，还要求键值不能为空
4. 组合索引
   多个字段组合的索引，只有在where条件中使用了第一个字段才能被使用，遵循最左前缀匹配原则
5. 全文索引
   针对大数据，解决like查询效率低的问题，fulltext索引配合match against操作使用，另外只有文本类型字段才可以创建全文索引，比如char、varchar、text

select * from fulltext_test where match(content) against('java编程思想' IN NATURAL LANGUAGE MODE);

2. 索引的数据结构

MySQL中索引的存储类型有两种：B+TREE和HASH，具体和表的存储引擎相关，但是用的比较多是B+Tree。

2.1 哈希（Hash）

以KV的形式检索数据，根据索引字段生成hash码和指针，指针指向数据 特点：

1. 查询速度快，但是由于数据不是顺序存储不能用于排序
2. 查询的时候由于要根据key值计算hash码，所以只能用于等值查询，不支持范围查询
3. 容易出现hash冲突（采用拉链法解决），效率会降低。

2.2 树型结构的选型

由于Hash结构的局限性，我们常使用B+Tree，那么为啥不使用其他的树呢？

2.2.1 二叉查找树

二叉查找树的左子树所有节点都小于父节点，右子树所有节点都大于父节点

二叉查找树既能实现快速查找，又能实现快速插入，但是它的查找耗时是跟深度有关，在最坏的情况下，查找的时间复杂度会退化成O(n)。如下，插入的顺序是有序的，就变成了链表

2.2.2 平衡二叉树

操作演示：www.cs.usfca.edu/~galles/vis…

二叉查找树会形成链表，因为左右子树深度太太，也就是不够平衡，平衡二叉树就是解决这个问题，通过左旋、右旋的方式进行平衡，使得左右子树深度差不超过1.
当用平衡二叉树作为索引时，是怎么查询数据的呢？
一个节点将会存储三块内容：

1. 索引的键值，例如在id这一列上创建了索引，那么将会把name的值放入节点中
2. 数据的磁盘地址，因为索引的作用就是去查找数据的存放地址
3. 左右子节点的引用。

那么，使用这种树型结构会有什么问题呢？
当使用这种方式来存储索引时，因为若是访问的第一个的节点不是所需要的数据将会往下查找，而读取每个节点的数据时候都要进行一次磁盘的I/O，必然会造成性能的下降。
我们前面也说过InnoDB操作磁盘是以页为单位，一个页是16KB,==那么一个树节点就是16kB==。
我们一个节点只存储键值、数据磁盘地址、节点引用。那么该节点的大小肯定达不到16KB的容量，例如整型字段，恐怕也就是用了十几二十个字节，这就浪费了大量的空间
因此我们也能想到如何解决。

1. 让每个节点存储更多数据
2. 节点数据增多，指针数也增多，也就说明有更多的分叉，分叉多了，树的深度也随之而然下降。

2.2.3 B Tree

操作演示：www.cs.usfca.edu/~galles/vis…

Balanced Tree 多路平衡二叉树

和AVL树一样，B Tree在枝节点和叶子节点存储键值、数据地址、节点引用。
有个特点：分叉树永远比关键字树多1.比如我们画下面这棵树时，每个节点都存储两个关键字，name就会有三个指针指向子节点。

查找规则：
我们假如在这张表查找15这个id的数据，首先15<17 走左边，然后15>12走右边，就在磁盘块7中找到15，只用了三次I/O。
B Tree保持平衡的方式是通过分裂和合并
例如当Max Degree = 3时，即子节点数量最多为3，我们插入数据1,2,3式，本来应该在第一个磁盘块，但是节点会有三个关键字，意味着4个指针，变成4路，所以必须进行分裂，把中间2提上去，1,3变成2的子节点
删除即是合并操作。节点分裂和合并都是页的分裂和合并

2.2.4 B+Tree

操作演示：www.cs.usfca.edu/~galles/vis…

B Tree的效率已经很高了，为什么mysql还要改良，最后使用了B+Tree呢 它有以下几个特点

• 关键字数量和路数相等
• 根节点和枝节点不存放数据地址，只有叶节点存放数据地址
• B+ Tree的每个叶子节点增加了指向相邻节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成一个有序链表结构。

与B Tree相比有什么优势：

1. B Tree的变种，B Tree能解决的，它都能解决
2. 扫库扫表能力更强（对表进行全表扫描只需要遍历叶子节点即可，不需要遍历整个B+Tree）
3. 读写能力更强（根节点和枝节点不需要保存数据地址，则可以保存更多的关键字，一次磁盘加载的关键字更多）
4. 排序能力更强，因为叶节点上有指向下一个数据区的指针
5. 效率更稳定，因为B+Tree是在叶子节点拿数据，所以IO次数稳定

3 B+Tree落地形式

之前我们说过不同的存储引擎文件不一样 InnoDB 的表有两个文件（.frm 和.ibd），MyISAM 的表有三个文件（.frm、.MYD、.MYI）。

user_innodb.frm
user_innodb.ibd
user_myisam.frm
user_myisam.MYD
user_myisam.MYI

3.1 InnoDB

在 InnoDB 里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在.ibd 文件里面
在 InnoDB 的主键索引的叶子节点上，它直接存储了我们的数据。

聚集索引（聚簇索引）：就是索引的键值的逻辑顺序与表数据行的物理存储顺序一致（例如字典的目录按照拼音排序，内容也按照拼音排序，按照拼音排序的这种目录就是聚集索引）

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

那么除主键之外的索引是怎么存储和检索数据的呢？ 在InnoDB中，主键索引和辅助索引有主次之分
辅助索引存储的是辅助索引和主键值，如果使用辅助索引查询，会根据主键值在主键索引中查询，最终获得数据

例如使用了name = ‘alice’作为查询条件，首先会在辅助索引中找到主键值，然后在主键索引中找到对应的叶子节点拿到数据

总结：一个表中主键id是该表的聚集索引、name为非聚集索引；表中的每行数据都是按照聚集索引id排序存储的；比如要查找name='Arla'和name='Arle'的两个同学，他们在name索引表中位置可能是相邻的，但是实际存储位置可能差的很远。name索引节点按照name排序，检索的是每一行数据的主键。聚集索引表按照主键id排序，检索的是每一行数据的真实内容，通过name作为辅助索引查到主键值，到主键索引中拿到数据。

3.2 MyISAM

MyISAM里面有两个文件：一个是.MYD 文件，D 代表 Data，是 MyISAM 的数据文件，存放数据记录。一个是.MYI 文件，I 代表 Index，是 MyISAM 的索引文件，存放索引。

MyISAM 的 B+Tree里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI中找到键值后，会到数据文件.MYD 中获取相应的数据记录。 如果是辅助索引，有什么不一样呢？

辅助索引和主键索引存储和检索数据方式没任何区别，一样是在name索引文件中找到磁盘地址。然后到数据文件里获取数据

4. 索引使用原则

4.1 列的离散度

count(distinct(column_name)) : count(*)

列的不同值的行数比去列的所有行数，分子越大，离散度越高，如果列的重复值越多，离散度就越低。

当我们在性别字段上创建索引时候，由于重复值太多，扫描行数更多，因此创建索引并不能提高查询效率

4.2 组合索引与最左前缀

我们前面所说都是针对单列索引，但是我们进行多条件查询的时候也会建立组合索引。例如查询成绩时候同事输入身份证号和考号。

我们现在给name和phone建立组合索引 联合索引在 B+Tree中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的（name 在左边，phone 在右边）。

从这张图可以看出来，name 是有序的，phone 是无序的。当 name 相等的时候，phone 才是有序的。

当我查询name =mic的时候，先去比较name来确定下一步查询方向，name相同时候再比较phone。但是如果查询条件没有name，就不知道，第一步该查哪个节点，因为建立搜索树的时候name是第一个比较因子。 我们也可以通过explain指令来查看对应的sql的执行计划，来确定是否命中索引等。

EXPLAIN SELECT * FROM user_innodb WHERE name= '张三'

4.3 覆盖索引

回表： 非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

select * from user_innodb where name = '青山';

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免 了回表。

ALTER TABLE user_innodb add INDEX `comixd_name_phone` (`name`,`phone`);

这三个查询语句都用到了覆盖索引

EXPLAIN SELECT name,phone FROM user_innodb WHERE name= '张三' AND phone = ' 13666666666';
EXPLAIN SELECT nameFROM user_innodb WHERE name= '张三' AND phone = ' 13666666666';
EXPLAIN SELECT phone FROM user_innodb WHERE name= '张三' AND phone = ' 13666666666';

==select * ，此处用不到覆盖索引。==
假如一个主键索引，其他 3 个字段建立辅助索引，select *也属于覆盖索引。

5 索引的使用原则

1. 在用于 where 判断 order 排序和 join的（on）字段上创建索引
2. 索引的个数不要过多，会浪费磁盘空间
3. 过长的字段，建立前缀索引。
4. 区分度低的字段，例如性别，不要建索引
5. 频繁更新的值，不要作为主键或者索引。（会经常性的进行页分裂操作）
6. 随机无序的值，不建议作为主键索引，例如身份证、UUID。（无序，页分裂）
7. 组合索引把散列性高（区分度高）的值放在前面
8. 创建复合索引，而不是修改单列索引