MySQL（三）——索引及索引的底层原理及MyISAM和InnoDB的索引实现

一、索引的介绍

索引是创建在数据库表中，是对数据库表中的一列或者多列的值进行排序的一种结果，索引是一种提高查询效率的数据结构（B树或者是哈希结构）。

• 索引优点：
  提高查询效率
• 索引缺点：
  索引并非越多越好，过多的索引会导致CPU使用率居高不下，由于数据的改动会引起索引文件的改动，过多的磁盘I/O造成CPU负荷太重。

二、索引的分类

• 普通索引：
  没有任何限制条件，可以给任意类型的字段创建普通索引
• 唯一性索引：
  使用unique修饰的字段，值不能重复，主键索引就属于唯一性索引
• 主键索引：
  使用primary key修饰的字段会自动创建索引
• 单列索引：
  在一个字段上创建索引
• 多列索引：
  在表的多个字段上创建索引
• 全文索引：
  使用fulltext参数可以设置全文索引，只支持char、varchar、text类型的字段上，常用于数据量较大的字符串类型上，可以提高查询速度；只有MyIsam存储引擎支持

三、索引的创建与删除

1.创建表的时候创建索引

create table table_name (
 id int,
 name varchar(12),
 index(id)
);

2.在已经创建的表上添加索引

//第一种
create [ unique | fulltext | spatial | primary key ]  index 索引名 on 表名 (属性名[ asc | desc]);
索引名一般命名为idx_name

//第二种
alter table 表名 add [ unique | fulltext | spatial | primary key ] index  索引名(属性名); 

3.删除索引

drop index 索引名 on 表名;

四、索引的执行过程

有student表如下

里面存储的数据如下：

用explain查看下面SQL语句的执行计划：

从结果可知，当前查询possible_keys、key都为null，则未命中索引，rows=5表示查询了5条数据，即进行了全表查询，当前查询操作对每一行数据都进行比较。当数据量较大时，全表扫描效率很低。

explain分析SQL执行过程时：

• possible_key： 表示SQL执行可能会命中的索引
• key： 表示执行过程真正使用的索引名称
• rows： 表示查询数据影响的行数

给name字段添加索引：

用explain查看上述同样语句的执行过程：

可以看出，添加索引之后，再次查询"ZhaoLei"信息，只在表中查询一行数据即可得到，而且当前查询命中 idx_name索引。

explain命令可以查看SQL的执行计划，分析SQL是否正确使用索引！！

五、索引的底层原理

MySQL支持两种索引，一种是B-树（即B树）索引，一种是哈希表索引，这两种索引的查询效率较高。
MYSQL中InnoDB存储引擎是基于B-树 (实际MYSQL采用的是B+树) 的索引结构。

B-树的特点：
B-树是一种m阶平衡树，叶子节点都在同一层，由于每一个节点存储的数据量比较大，索引整个B-树的层数是非常低的，基本上不超过三层
B-树、B+树的详细介绍参考此博客 B树、B-树、B+树、B*树之间的关系

由于磁盘的读取也是按block块操作的（内存是按page页面操作的），因此B-树的节点大小一般设置为和磁盘块大小一致，这样一个B-树节点，就可以通过一次磁盘I/O把一个磁盘块的数据全部存储下来，所以当使用B-树存储索引的时候，磁盘I/O的操作次数是最少（MySQL的读写效率，主要集中在磁盘I/O上）。

MySQL为什么要采用B+树存储索引结构呢而不是B-树？？

1. B-树的每一个节点，存了关键字和对应的数据地址，而B+树的非叶子节点只存关键字，不存数据地址。
   因此B+树的每一个非叶子节点存储的关键字是远远多于B-树的，B+树的叶子节点存放关键字和数据，
   从树的高度上来说，B+树的高度要小于B-树，使用的磁盘I/O次数少，因此查询会更快一些。
2. B-树由于每个节点都存储关键字和数据，因此离根节点近的数据，查询的就快，离根节点远的数据，查询的就慢；
   B+树所有的数据都存在叶子节点上，因此在B+树上搜索关键字，找到对应数据的时间是比较平均的，没有快慢之分。
3. 在B-树上如果做区间查找，遍历的节点是非常多的；
   B+树所有叶子节点被连接成了有序链表结构，因此做整表遍历和区间查找是非常容易的。

哈希索引是由哈希表实现的，哈希表对数据并不排序，因此不适合做区间查找，效率非常低，需要搜索整个哈希表结构。

六、常用存储引擎的索引结构

1、MyISAM索引实现

• 主键索引：
  MyISAM存储引擎使用B+树作为索引结构，叶节点的data域存放的是数据记录地址。
  MyISAM主键索引原理图如下图示：
  
  图源网络，侵删
  非叶子节点存储的都是关键字
  假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

• 辅助索引：
  MyISAM中，主键索引和辅助索引在结构上没有任何区别，主键索引要求key是唯一的，而辅助索引的key可以重复。
  
  图源网络，侵删

由上面两张图可知，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出data域的值，然后以data域的值为地址，读取相应的数据记录。
可以看出：MyISAM存储引擎的索引结构叶子节点存储关键字和数据地址，也就是说索引关键字和数据没有放在一起存放，体现在磁盘上，就是索引在一个文件存储，数据在另一个文件存储。
例如一个user表，会在磁盘上存储三个文件：user.frm（表结构文件）、user.MYD（表的数据文件）、user.MYI（表的索引文件）

MyISAM的索引方式也叫做非聚集索引

2、InnoDB索引实现

• 主键索引：
  InnoDB存储引擎的主键索引，叶子节点中索引关键字和数据是在一起存放的，非叶子节点存存储主键信息
  
  图源网络，侵删
• 辅助索引:
  InnoDB的辅助索引，叶子节点上存放的是索引关键字和对应的主键，如图：
  
  图源网络，侵删

辅助索引的B+树，先根据关键字找到对应的主键，再取主键索引树上找到对应的行记录数据。

从索引树上可以看到，InnoDB的索引关键字和数据都是在一起存放的，体现在磁盘存储上。
例如创建一个user表，会在磁盘上只存储两种结构：user.frm（存储表的结构）、user.idb（存储索引和数据）

InnoDB的索引树叶子节点包含了完整的数据记录，这种索引叫做聚集索引。
因为InnoDB的数据文件本身要按照主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有指定显示，则MyISAM系统会自动选择一个可以唯一标识的数据记录列作为主键，如果不存在这种列，则Mysql会自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

该部分也可参考此博客MyISAM与InnoDB两类存储引擎的索引实现

七、索引的设计原则

1. 给区分度高的字段创建索引 eg：学号
2. 给经常需要排序、分组和多表联合操作的字段创建索引
3. 经常作为查询条件的字段创建索引
4. 索引的数据不宜过多
5. 使用数据量少的索引（如前缀索引，主要针对字符串索引，字符串类型尽量创建前缀索引）
6. 对于多列索引，优先指定最左边的列集
7. 删除不在使用或者很少使用的索引