【Mysql】索引原理

一、索引的分类

    普通索引index
    唯一索引：主键索引primary key、唯一索引unique
    联合索引：联合主键索引primary key(id, name)、联合唯一索引unique(id, name)、联合普通索引index(id, name)

二、MyISAM索引的实现原理

    通常使用B+树来实现的索引，示例如下：

    1）非叶子节点仅存储索引，不存储任何真实的数据值，所有的数据值，均存储于叶子节点中，且每个叶子节点都有一个指针指向下一个叶子节点
    2）图中每一个大方框代表一个磁盘块，每个磁盘块中数据项的数量越多，一次检索能够缩小的范围越多。而磁盘块的大小一定，为此数据项的size越小，每个磁盘块中存储的数据量越多（这就是为何索引列字段size应该尽量小）。
    3）树的高度越小，越快到达叶子节点，搜索越快

    2.2.1、复合索引

    复合索引（name,id），以name建立B+树，并将索引id存放于叶子节点中

2.3、InnoDB索引的实现原理

    基于B+树实现，但是与MyISAM有所不同。示例如下：

    1）InnoDB的主键索引的叶子节点中，存储的是记录的所有数据列，也就是说数据文件本身就是索引文件。MyISAM的叶子节点中存储的是索引列，并且存储记录的地址，索引文件和数据文件是分开的。
    2）InnoDB的普通索引（非主键索引）的叶子节点中，存储的是索引列，以及对应的主键。使用普通索引时，先找到主键，再去主键索引中取信息。
    思考1：为何InnoDB的非主键索引中存储的是主键索引，而不是和MyISAM一样存储记录的地址？InnoDB的非主键索引为何要采用这种二次查找的方式？
    InnoDB在设计之初就考虑到数据的修改问题，每次修改，如果采用MyISAM的方式，就需要更新数据表中所有的索引中该记录的地址信息。而采用主键存储记录的方式，修改记录只需要修改主键的相关叶子节点即可，不用再去维护普通索引的信息。所以MyISAM适合查找信息，而InnoDB适合修改信息。

2.4、索引相关优化

    为什么使用LIKE %a会导致索引失效？
    如果使用%a这种前置模糊匹配的方式，每一条数据信息，不管前面是什么，后面都有很能和a后缀匹配，这个时候只能全表匹配，以防漏掉某条数据。

    为何建立索引的数据列，其数据大小需要尽量小一些？
    结合上面的索引示意图，每一个非叶子节点都是一个磁盘块，一个磁盘块中的索引节点越多：a、索引树的高度会越小，搜索也会越快到达叶子节点；b、每经过一次搜索能够锁定的范围也越小。而磁盘块的大小是一定的，故当每个数据的大小越小，磁盘块能够存储的索引节点越多。

如有不当之处，欢迎指正不胜感激