MySQL索引原理

1、页分裂、目录项和目录？

（1）页分裂：在对页中的记录进行增删改操作的过程中,下一个数据页中用户记录的主键值必须大于上一个页中用户
记录的主键值,此过程为页分裂;
（2）目录项：对单个数据页进行记录即当前页最小主键和当前页号;
（3）目录(索引)：针对单个表数据量超过16kb,则将产生多个数据页，每个页通过双向链表进行连接，每个目录项
组成一个目录即索引。

2、目录项记录和用户记录的区别？

（1）目录项记录的record_type值是1，而普通用户记录的record_type值是0;
（2）目录项记录只有主键值和页的编号两个列,而普通的用户记录的列是用户自己定义的,可能包含很多列和
InnoDB自动添加的隐藏列;
（3）记录头信息中的min_rec_mask仅在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值
为1,其他别的记录的min_rec_mask值都是0。

3、索引的创建、修改和删除？

      InnoDB和MyISAM会自动为主键或者声明为UNIQUE的列去自动建立B+树索引,但是自定义其他的列建立索引就需要显式的去指明。存储引擎为了性能和存储空间考虑,不会给每列都自动创建索引。

创建索引：
    创建表时建立索引的单个列或者建立联合索引的多个列：
        CREATE TALBE 表名 (
         各种列的信息 ··· ,
         [KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
        )
        其中的KEY和INDEX是同义词,任意选用一个就可以。
    添加索引：
    ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);
修改(删除)索引：
    ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;
创建联合索引：
给index_demo表中的c2和c3列创建一个联合索引,创建表语句如下：
    CREATE TABLE index_demo(
     c1 INT,
     c2 INT,
     c3 CHAR(1),
     PRIMARY KEY(c1),
     INDEX idx_c2_c3 (c2, c3)
    );
说明：在这个建表语句中创建的索引名是idx_c2_c3,索引名称可以自定义,为了规范建议以idx_为前缀,后边跟着
需要建立索引的列名,多个列名之间用下划线_分隔开。
删除联合索引：
    与单个索引语法一致,如下：
    ALTER TABLE index_demo DROP INDEX idx_c2_c3;

4、聚簇索引、非聚簇索引(二级索引)和联合索引？

聚簇索引：

简单来讲即利用主键作为B+树的构建基石。具体如下：

 （1）使用记录主键值的大小进行记录和页的排序;
    （1）页内的记录即用户记录是按照主键的大小顺序排成一个单向链表;
    （2）各个存放用户记录的页即数据页也是根据页中用户记录的主键大小顺序排成一个双向链表;
    （3）存放目录项记录的页分为不同的层次,在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一
    个双向链表。
 （2）B+树的叶子节点存储的是完整的用户记录。
    指这个记录中存储了所有列的值(包括隐藏列)。

PS：
       InnoDB存储引擎会自动的为我们创建聚簇索引。在InnoDB存储引擎中,聚簇索引就是数据的存储方式 (所有的用户记录都存储在叶子节点),也就是所谓的索引即数据,数据即索引。

非聚簇索引(二级索引)：

非主键列建立的索引列来构造一颗B+树，用于搜索查询(如查询条件非索引列),具体如下：

（1）使用记录c2列的大小进行记录和页的排序;
  （1）页内的记录是按照 c2 列的大小顺序排成一个单向链表;
  （2）各个存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表;
  （3）存放目录项记录的页分为不同的层次,在同一层次中的页也是根据页中目录项记录的c2列大小顺序排成一个
  双向链表;
（2）B+树的叶子节点存储的并不是完整的用户记录,而只是c2列+主键这两个列的值;
（3）目录项记录即非叶子节点中不再是主键+页号的搭配，而变成了c2列+主键+页号的搭配,此处主键是为了保证记
录项记录的唯一性,便于定位到具体页。

PS：
      （1）二级索引根据索引列查找到具体目录项记录后,需要根据其主键值取聚簇索引中查询实际的完整数据即回表;
     （2）覆盖索引则不需要进行回表,索引列可满足当前字段列需要。

联合索引：

同时以多个列的大小作为排序规则,即同时为多个列建立索引,如让B+树按照c2和c3列的大小进行排序。

（1）先把各个记录和页按照c2列进行排序;
（2）在记录的c2列相同的情况下，采用c3列进行排序。

联合索引结构图：其本身也是二级索引，非叶子节点也满足二级索引唯一性要求，即目录项记录图中未画主键。

（1）每条目录项记录都由c2、c3、主键、页号这四个部分组成,各条记录先按照c2列的值进行排序,如果记录的c2
列相同,则按照c3列的值进行排序;
（2）B+树叶子节点处的用户记录由c2、c3和主键c1列组成。

PS：
      以c2和c3列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：
      （1）建立联合索引只会建立如非聚簇索引一样的1棵B+树。
      （2）为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

5、InnoDB与MyISAM索引的区别？

（1）InnoDB中索引即数据,也就是聚簇索引的那棵B+树的叶子节点中包含了完整的用户记录(参照上述聚簇索引)。
（2）MyISAM索引和数据分开存储,其结构如下：
   （1）用户记录按照插入顺序,将其用户记录存储在单独的文件中即数据文件,并不存在多个数据页,但仍然存在
   记录头信息,所有数据都往该文件存储,通过行号访问数据;
   PS：InnoDB是通过主键大小进行排序,可以采用二分法查找,MyISAM则是使用行号来获取数据。
   （2）MyISAM的索引是单独存储在另一个索引文件中,其根据主键和行号组成索引即每次根据索引查询数据时,
  是先在索引文件中找到行号,再根据行号去获取数据记录。

PS：
      MyISAM行号一般为地址偏移量,其建立的全部都是二级索引,需要回表操作获得完整的数据记录,但MyISAM回表操作比InnoDB回表操作快;
     InnoDB的聚簇索引不需要回表操作,其叶子节点就是完整的数据记录,索引即数据。