MySQL索引（详细解释）

什么是索引

索引是一种可以加快检索的数据结构，它包含从表或者视图的一列或者多列生成的键，以及映射到指定数据存储位置的指针。
我们可以把数据库看成一本书，索引就是书的目录，借助目录查找信息，显然要比没有目录的书查起来要方便快捷。

索引的作用就是提高查询效率

那么索引能提高查询效率，我们就要创建很多索引么？
不是的，索引最终是存储在硬盘上的，索引越多，那么存储索引的文件就会越大，电脑在使用索引的时候，是先将索引文件加载到内存上，然后再加载到CPU上。所以，索引如果过多，会加重内存和磁盘的交互负担。
并且，数据的改动也会对索引造成影响。

总结：索引可以提高查询效率，但并不是越多越好，过多的索引会引起磁盘I/O的交互频繁，并且数据改动，索引也会受到影响。

索引分类

普通索引：没有任何条件现制，可以给任意字段添加普通索引。

Student表中的字段有SID，Sname，Sage，Ssex，对其中任意的一个字段都可以创建索引，没有任何现制，这就是普通索引

唯一性索引：唯一性索引顾名思义，它可以保证索引中的所有的值都是唯一的，使得表中的每一行在某种方式上都具备唯一性。所以，只有当唯一性是数据本身的特征的时候，指定唯一性索引才有意义。
例如：主键，在表中是不能重复的，主键索引，就是唯一性索引。

SID就是Student表的主键，SID是不会重复的字段，也就是唯一性字段，主键索引就是唯一性索引。
并且，当我们创建表或者修改表中字段属性时，如果字段的完整性约束是primary key 或者 unique ，那么会自动创建索引。

主键索引:使用primary key修饰的字段自动创建索引

单列索引：在一个字段上创建的索引，叫做单列索引。
也就是索引只用了一个字段，例如给Sname创建索引，就是单列索引。

多列索引：在表的多个字段上创建的索引（也叫联合索引）
索引使用了多个字段，例如给Sname，Sage一起创建一个索引这个索引包含了两个字段，这就是多列索引。

全文索引：使用Fulltext参数可以设置全文索引，只支持char、varchar、text类型的字段上。 常用于数据量比较大的字符串类型上，可以提高查询速度，只有MYISAM的存储引擎的支持。

全文索引目前是搜索引擎的关键技术之一。试想在1M大小的文件中搜索一个词，可能需要几秒，在100M文件中可能需要几十秒，在更大的文件中搜索开销会更大。为了加快此类检索的速度，就出现了全文索引技术，也叫做倒排文档技术。原理：先定义一个词库，然后再文章中查找并存储每个词条出现的频率和位置，相当于对文件建立了一个以词库为目录的索引，这样查找某个词的时候就能很快地定位该词出现的位置了。

怎么创建和删除索引

1.创建表时，就创建索引
create table 表名 （属性名(假设是id) 类型（int） 完整性约束 。。。。 index(id)）；

最后index 括号中式什么属性，就是以什么属性为索引。

KEY就表示的是索引，‘id’是索引名，括号是以什么属性为索引。
2.在已经创建好的表上创建索引
create 完整性约束（unique | fulltext | primary key | spatial） index 索引名 on 表名（属性名）；
创建了一个在xx表中的yy类型的给zz属性添加索引。

这里，我们就在Student表上创建了一个以Sname 为索引的索引。

alter table 表名 add 完整性约束 index 索引名 (属性)；

删除索引
drop index 索引名 on 表名;

SQL的执行计划与索引的关系

现在我们就来了解一下SQL的执行过程。

表的信息


简单的写一个SQL语句

那么这条语句是怎么执行的呢？？？
通过 explain 分析SQL执行过程。explain命令可以查看SQL的执行计划，分析SQL是不是正确的使用索引。

explain sql语句；
（tips：语句后面的\G是为了让表看起来更好看）

possible_keys：就是这条SQL可能会运用到的索引
key：表示运用到的索引
rows：表示查询了多少条数据
可以看出这条SQL并没有使用到索引，并且假如我的表的数据是百万级别的呢？那全表查询效率时非常低下的
以上就是没有使用到索引的执行过程。

现在我们看一下有索引的执行过程

在这里我创建了一个给Sname添加一个索引。
这时，可能运用到的索引就是idx_name ,并且命中了这个索引，rows缩减为1。
只查询了一行就查询到了数据，这个效率就非常的高!
那么我们思考一下，我同时查询SID和Sname，会运用到主键索引，还是idx_name索引呢？


大家从上面可以看出，我们条件不同，选用的索引也不同，并且一次查询只能运用到一个索引。

创建联合索引

create index （索引名） on 表名（属性1，属性2…）;

在这里我创建了一个联合索引，给Sname和Sage一起创建了一个索引，
现在我们写一个简单的查询

tips：创建一个联合索引（Sname，Sage，Ssex）相当于创建了（Sname，Sage），（Sname）三个索引。这被称为最佳左前缀特性。
因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

索引底层结构

MySQL 支持两种索引，一种时B数，一种时哈希索引。
MySQL的INNODB存储引擎时基于B树存储引擎（MySQL实际使用的是B+树）

建议先读完大佬文章，B树，B+树，B*树讲解

现在解释了索引到底是什么，索引最底层还是一种数据结构。

B+树能很大程度的满足我们的区间查询
B树是一种m阶平衡树，叶子节点都在同一层，每一个节点存储数据量是比较大的，索引在整个的B树上的层数是比较低的，基本上不超过3层。

磁盘的读取也是按 block 块操作的（内存是按page页面操作的），位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理： 当一个数据被用到时，其附近的数据也通常会马上被使用。因此B-树的节点大小一般设置为和磁盘块大小一致。如此以来，一个B树节点就可以通过一次磁盘I/O把一个磁盘块的数据全部存储下来，所以当使用B树存储索引的时候，磁盘I/O的操作次数是最少的（MySQL的读写效率，主要集中在磁盘I/O上）。

为什么MySQL使用B+树？

1.B树上每一个节点存储的都是关键字和对应的地址，而B+树的非叶子节点上至存储关键字，叶子节点存储关键字和关键字对应的地址。所以在相同数据量下，B+树每个节点所存储的关键字个数要比B树多得多，所以，B+树的节点个数就会比B树的节点个数少很多。从树的高度上来讲，B+树的高度就小于B树的高度。

2.查询效率
B树的每一个节点都存储关键字和对应的地址，所以当查询的关键字距离根节点越近，查询速度越快，离根节点的距离越远，查询的效率越慢。
B+树的所有关键字和对应地址都在叶子节点上，所以在B+树上搜索关键字，耗时是均匀的，几乎没有快慢之分

3.区间查找
B+树能很好的满足区间查找，而B树不行，因为每一个节点都是相对孤立的，而B+的叶子节点连成了链表结构，所以做整表查询和区间查询，效率会很高。

哈希索引：哈希索引是通过哈希表来实现的，并且哈希无法对数据进行排序。

MYISAM和INNODB存储引擎的索引结构

MYISAM存储引擎：

主键索引

我们可以看出来MYISAM存储引擎的结构，非叶子节点存储的是关键字，叶子节点上存储的是关键字和对应地址（B+树结构），通过这个数据地址，就可以直接找到这一条记录。

辅助索引

主键索引和辅助索引在结构上没有任何区别，主键索引要求KEY是唯一的，也就是主键的唯一性，但是辅助索引的KEY可以重复。

MYISAM特点：索引文件和数据本身是分开的，所以MYISAM索引方式也叫做非聚集索引

所以如果我们使用的是MYISAM存储引擎，在磁盘上会生成三个文件
假如创建了一个user表，磁盘上创建
user.frm(表结构文件)
user.MYD（表的数据文件）
user.MYI（表的索引文件）

INNODB存储引擎：

主键索引：

从图上我们可以看出，非叶子节点存储关键字，叶子节点存储关键字和数据本身

辅助索引：

非叶子节点存储索引的关键字，叶子节点存储关键字和该数据对应的主键索引。
所以当我们需要通过辅助索引去查询数据时，假如通过name = Bob，查询Bob的所有信息。先从辅助索引上查询到Bob，再通过对应的主键，去主键索引上查找对应的信息。

分析，假如我们查询的只是name 和 主键 ID 呢，它还会去主键索引上查找么?

不会！因为通过辅助索引我们已经得到了他的主键，已经满足了查询结果不需要再去主键索引上查询。

INNODB的索引存储结构又叫做聚集索引，那么当我们使用INNODB存储引擎时，磁盘上会生成两个文件。
以user表为例，
user.frm（表的存储结构）
user.idb(索引和数据)