MYSQL索引以及索引的底层原理

1、索引分类

索引：索引是创建在表上，是对数据库中的一列或者多列的值进行排序的一种结果。

优点：提高查询的效率

缺点：索引并非是越多越好，过多的索引会导致CPU使用居高不下，由于数据的改动引起索引文件的改动，过多的磁盘IO造成CPU负载过高。

普通索引：没有任何的限制条件，可以给任何类型的字段创建普通索引

唯一性索引：使用unqiue修饰的字段，值不会重复，主键索引隶属唯一性索引

主键索引：使用primray key修饰的字段，会自动创建索引

多列索引：在表的多个字段上创建索引

全文索引：使用FULLTEXT参数可以设置全文索引，只支持char、varchar和text类型的字段，常用于数据量较大的字符类型上，只有MYISAM储存引擎支持

2、索引的创建和删除

2.1 在创建表时指定索引字段

index(属性名)：指定当前的属性名为索引

create table Student(ID INT,
                     Name varchar(10),
                     index(ID));

 2.2 在已经创建表上添加索引

create[unique/fulltext/spatial] index 索引名(index) on table_name(属性)；

create index id on Student(ID);

2.3 删除索引

drop index 索引名 on 表名;

drop index id on Student;

3、索引的执行过程

首先创建一个表，然后在表中插入基础的数据；接着在某一条件下进行查询

通过explain分析SQL执行过程，将表中的某一个字段设置为索引，重复查询的过程（在某一条件下进行查询通过explain分析SQL执行过程）

创建一个Student表，表里的字段分别为：

Student(SID,Sname,Sage,Ssex)

--SID 学生编号,Sname 学生姓名,Sage 年龄,Ssex 学生性别

插入基本数据：

select * from Student;

+-----+----------+------+------+

| SID | Sname    | Ssex | Sage |

+-----+----------+------+------+

|   1 | zhaolei  | nan  |   20 |

|   2 | qiandian | nan  |   20 |

|   3 | sunfen   | nan  |   21 |

|   4 | wulan    | nv   |   18 |

+-----+----------+------+------+

数据库里插入了四条数据：

查询姓名为"zhaolei"的数据记录，通过explain分析SQL执行过程

mysql> explain select * from Student where Sname = 'zhaolei'\G
*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: Student

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 4

        Extra: Using where

1 row in set (0.01 sec)

运行结果的参数说明：

（1）id列：是在QEP中展示的表的连续引用

（2）select_type列：提供了各种表示table列引用的使用方式的类型。最常见的值包括SIMPLE、PRIMARY、DERIVED 和UNION。其他可能的值还有UNION RESULT、DEPENDENT SUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION 以及UNCACHEABLE QUERY。

SIMPLE对于不包含子查询和其他复杂语法的简单查询，这是一个常见的类型。

PRIMARY这是为更复杂的查询而创建的首要表(也就是最外层的表)。这个类型通常可以在DERIVED 和UNION 类型混合使用时见到。

DERIVED当一个表不是一个物理表时，那么就被叫做DERIVED。

DEPENDENT SUBQUERY这个select-type 值是为使用子查询而定义的。

UNION这是UNION 语句其中的一个SQL 元素。

（3）table列：是EXPLAIN命令输出结果中的一个单独行的唯一标识符。这个值可能是表明、表的别名或者一个为查询产生临时表的标识符，如派生表、子查询或集合。

（4）type列：type列代表QEP中指定的表使用的连接方式。

（5）possible_keys 列：指出优化器为查询选定的索引。

（6）key列：指出优化器选择使用的索引。

（7）key_len 列：定义了用于SQL 语句的连接条件的键的长度。

（8）ref 列：可以被用来标识那些用来进行索引比较的列或者常量。

（9） rows 列：提供了试图分析所有存在于累计结果集中的行数目的MySQL 优化器估计值。

(10)Extra 列：提供了有关不同种类的MySQL 优化器路径的一系列额外信息。Extra 列可以包含多个值，可以有很多不同的取值，并且这些值还在随着MySQL 新版本的发布而进一步增加。

       可以通过possible_keys、key没有用到索引，并且通过rows: 4可以看出直接全表扫描一遍才找到"zhaolei"的信息，假设表中4百万数据，那么查找"zhaolei"同学就需要扫描4百万的数据，效率比较低。

以名字创建索引

create index idx_name on Student (Sname);

mysql> explain select * from Student where Sname = 'zhaolei'\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: Student

         type: ref

possible_keys: idx_name

          key: idx_name

      key_len: 27

          ref: const

         rows: 1

        Extra: Using index condition

1 row in set (0.01 sec)

注意：这次查询"zhaolei"同学的信息，只在表中查询一行数据就可以得到（rows: 1）

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

4、索引的底层原理

       MYSQL支持两种索引，B-数索引和哈希索引，这两种索引的查询效率高。

       MYSQL存储引擎默认的是INNODB存储引擎，基于B-树（MYSQL实际采用是B+树）结构。

       B-树是一种M阶平衡树，叶子节点都在同一层，由于每一阶存储的数据量比较大，索引的整个B-树的层数是比较低的，基本上不超过三层。

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

为什么MYSQL最终使用B+树而不是B-树，它们在存储结构上有什么不同？（重点）

1、B-树的每一个节点，存储的是关键字和对应的数据地址，而B+树的非叶子节点存储关键字，因此B+树的每一个非叶子节点存储的关键字数量远远多于B-树，B+树叶子节点存储关键字和数据

因此：从树的高度来说，B+树的高度要小于B-树，使用磁盘的次数少

2、B-树由于每一个节点存储关键字和数据，因此离根节点近的数据查询比较快，离根节点比较远的数据，查询就比较慢

     B+树上所有的数据都存储在叶子节点上，因此B+树查询关键字，找到对应数据的时间上是比较均匀的，没有快慢之分

3、B-树如果做区间查找，遍历的节点比较多的，B+树所有的叶子节点被连接成有序链表结构，因此做整表遍历和区间查找是非常容易的

       哈希索引是由哈希表实现，哈希表无法对数据做到排序，因此无法做区间查找，效率非常低，需要查询整个哈希结构。

主键索引、辅助索引、聚集索引、非聚集索引

主要研究的存储引擎是MyISAM和InNoDB存储引擎的索引结构

1、MyISAM存储引擎-主键索引

       MyISAM引擎使用B+树作为索引结构、叶节点的data域存放的是数据记录地址，下图四MyISAM主键索引的原理图：

2、MyISAM存储引擎-辅助索引

       MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复，如果给其他字段创建辅助索引，结构图如下：

       根据上面两张图，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

       可以看出：MYISAM存储引擎，索引结构叶子节点存储关键字和数据地址，也就是说索引关键字和数据没有在一起存放，体现在磁盘上，就是索引在一个文件存储，数据在另一个文件存储。

       MYISAM的索引方式也叫做非聚集索引，之所以这么称呼为了与InNoDB的聚集索引区分

3、INNODB存储引擎-主键索引

       INNODB存储引擎的主键索引，叶子节点中，索引关键字和数据是在一起存放的，如图：

 

       可以看到，索引关键字和数据一起存储在叶子节点上。

4、INNODB存储引擎-辅助索引

       INNODB的辅助索引，叶子节点上存放的是索引关键字和对应的主键，如图：

 

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

       从索引树上可以看到，INNODB的索引关键字和数据都是在一起存放的，体现在磁盘存储上。

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

       可以看出，使用索引是有能提高查询效率，但是给表创建过多的索引，效率反而会降低，因此在设计表索引的时候，需要遵循以下的设计原则：

1、给区分度高的字段创建索引                    eg：学号、省份证号

2、给经常需要排序，分组和多表联合操作的字段创建索引

3、经常作为查询条件的字段创建索引

4、索引的数据不宜过多

5、使用数据量少的索引（如前缀索引，主要针对字符串索引，字符串类型尽量创建前缀索引）

6、对于多列索引，优先指定最左边的列集

7、删除不在使用或者很少使用的索引