MySql索引的分类、实现原理与如何优化详解（哈希索引、B+Tree索引、全文索引）

数据库查询的时候如何提高效率 我相信大部分的人都会回答创建索引 但是索引的数据类型是啥 索引的分类 索引的实现原理、索引的具体优化 我相信大多数人还是一脸懵逼的 那么我们现在就简单地聊一聊索引。

一：索引的分类 

• 主键索引：通常用Id作为主键索引 且会自增，也是最常用的索引（不允许重复、为空）
• 唯一索引：校验数据库唯一性的索引 如果创建了唯一索引再次插入就会报错。（简单地说就是同一列中同一个值只能出现过一次，空值除外）
• 全文索引：使用大文本的时候会使用的索引，创建该索引非常消耗时间和空间。且有专门的查询语句而非Like 添加时使用    FULLTEXT关键字（5.6之前只有MYISAM支持全文索引 5.7之后InnoDB也加入 且目前只支持英文索引 但是在5.7之后可以使用插件ngram支持索引）
• 普通索引：普通的创建索引 对该列无任何限制
• 组合索引：对多列中进行组合索引 多个列中不能有空值

二：索引的优劣

优势：这个话不多说 可以显著的提高查询效率 就像兰博基尼与三轮车的差距 

劣势：索引本身也会占据空间 且会消耗时间 创建索引的时候整个表会被锁死 推荐在数据库不被使用时创建 并会降低增删改表结构的效率 因为对表结构物的更改也会牵扯到索引的更改

三：索引的实现原理

Mysql支持多种索引实现例如：哈希索引、BTree索引、B+Tree索引、全文索引 其中Innodb和MyISAM默认的索引是Btree索引；而Mermory默认的索引是Hash索引

• 哈希索引：讲索引列以key-value形式用hash值做存储 使用的是散列算法 所以速度上会非常的快 但是因为多个数据在存储结构上也是没有任何关系的 所以不支持范围查找 且不支持多列联合索引的最左匹配原则(这个在后续会有说明) 如果hash值有大量重复键值的情况下 效率也会降低 因为会存在hash碰撞的问题。
• BTree索引和B+Tree索引：

B+Tree索引：

BTree是平衡搜索多叉树，设树的度为d（d>1），高度为h，那么BTree要满足以一下条件：

• 每个叶子结点的高度一样，等于h；
• 每个非叶子结点由n-1个key和n个指针point组成，其中d<=n<=2d,key和point相互间隔，结点两端一定是key；
• 叶子结点指针都为null；
• 非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据；

B+Tree索引：

• B+Tree是BTree的一个变种，设d为树的度数，h为树的高度，B+Tree和BTree的不同主要在于：

• B+Tree中的非叶子结点不存储数据，只存储键值；
• B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储键值对应的数据的物理地址；

聚合索引和非聚合索引

在B+Tree下 叶子存储了整行数据的是主键索引 又被称为聚簇索引 存储了主键的值的是非主键索引 又被称为非聚簇索引 相比较之下聚簇索引查询效率比非聚簇快 因为根据主键索引查出来的结果就是需要的数据 但是非聚簇索引查出来的只是索引主键的值 还需要将主键的值再放入数据库中查询一次 再做一次回表操作 （覆盖索引除外 及一个查询所需要的值在索引中即可得到）

四：联合索引与最左前缀匹配

联合索引即为对多列中进行组合索引 一般将使用度最高的放在最左侧  因为索引有一个最左前缀匹配原则 即创建了（1,2,3,）联合索引时 当查询1,12，123时可以用到索引 但是查询2，3，23，13时却不会使用索引 

五：索引下推

在MySQL5.6中 对索引新增了索引下推 在一句select * from table where age = '0' and name like '%aaa%'这个sql中 如果没有使用索引下推 就会先查询年龄等于0的值然后回表在查询name like '%aaa%这样的数据 当采取索引下推后就先查询age = '0' 返回其索引再根据like语句判断索引是否符合条件 符合条件就根据索引定位数据位置 可以减少回表的次数

六：索引使用场景

需要使用索引的情况很多 但是有些时候索引不是尺有所长 寸有所短 并不是所有情况下都是无脑创建索引的 有些情况下并不推荐使用索引

• 经常需要增删的列不要创建索引
• 大量重复数据的列上不建议使用索引
• 表数据较少时不建议频繁简历索引
• 不是所有的索引建立了都会被使用 MySQL内部有优化器 会自动筛选最优的搜索方式 所以并不一定建立索引但一定会被使用

ps: 当需要查询效率时可以在最前方加上explain来提到信息