mysql索引全解_MySQL索引详解

概述

索引的本质就是帮助MySQL高效获数据排序的数据结构; MySQL索引引擎主要包括InnoDB和MyISAM。根据索引节点是否包括数据，又区分为聚簇索引和非聚簇索引。本文将详细讲解MySQL索引底层实现及相关注意事项。

索引数据结构

索引是排序好的数据结构，目的是帮助加快数据检索过程。有很多数据结构均可以用做构建索引，如: 二叉树、红黑树、Hash表、B树和B+树等，下面将详细介绍各种数据结构优缺点。

二叉树

二叉树，准确说是二叉搜索树，如下图所示，特点是:

1. 每个节点最多有两个子节点

2. 左子树节点数据小于父节点数据，右子树节点数据大于父节点数据

其查找效率为log(n)，二叉搜索树主要有两个缺点

1. 对于排序好的数据它会退化为线型搜索，如下图所示。

2. 二叉搜索在索引节点多的情况下，树会变的很高，查找效率和树的高度成正比，查找效率急剧降低，且索引变的很庞大，不能加载到内存。

极端情况下退化为线型结构

红黑树

红黑树本质上也是个二叉搜索树，只是做了各种限制使得二叉搜索尽量达到平衡(任意节点的左右子树的高度差不超过一定范围)，红黑树是近似平衡二叉搜索树，如下所示：

红黑树特点

1．每个节点黑色或者红色

2．叶子NIL为黑色

3．根节点为黑色

4．从一个节点到其子孙叶子节点包含的黑色节点个树一致

5．红色节点必然有两个黑色的子节点

红黑色解决了二叉搜索树极端情况下退化为线型的问题，但是仍然存在树过高索引过大的问题。

Hash表

Hash表将key进行hash计算然后映射到对应空间，在不考虑空间的情况下，能达到O(1)查找效率，但是仍然会存在索引过大的问题。

B树和B+树

B树和B+树本质上就是个多路平衡查找树；以m路平衡查找树为例。

B树特点

1. 根节点至少有一个关键字

2. 每个节点最多有m-1个关键字

3. 非根节点最少有m/2个关键字

4. 所有叶子节点在同一层

5. 节点中的关键字是排序的

6. 节点包含索引和数据

B+树特点

B+树和B树类似，主要区别：

1. 非叶子节点只保存索引，数据存储在叶子节点

2. 叶子节点在同一层，且包括所有的关键字，且叶子节点之间有序且用指针链接

如下图所示：左侧表示B树，右侧表示B+树

B+树的优点

B+树在树高度较低的情况下，也能存储较大量的索引数据。假设树高度h=3，每个节点内存默认大小16k，索引key为8字节，指针为4字节，则每个节点大约存储16k / (8+4) = 1635 个key，3层树满的情况下，假设叶子节点索引加数据为32字节，则每个叶子节点大约可以存放key个数等于16k / 32 = 512，总共叶子节点约存放key个数为1635*1635*512 = 1368691200

正是因为B+树的优点，实际上MySql索引底层数据结构就是用的B+树实现。

MyISAM和InnoDB

MyISAM和InnoDB底层实现都是B+树。两者索引和数据组织又有所不同，根据索引和数据是否都在叶子节点分为:聚簇索引和非聚簇索引

非聚簇索引

索引节点(B+树叶子节点)数据域存放的是指向真实数据的地址，这种索引结构叫做非聚簇索引。

MyISAM索引就是非聚簇索引，因此文件包括一个索引文件和一个数据文件。下图为非聚簇索引示例：

聚簇索引

索引节点数据域存放的是实际的数据，这个索引叫做聚簇索引。

InnoDB索引类型为聚簇索引。如下图所示：

主键索引和辅助索引

对于聚簇索引，主键索引数据行和主键存在一起；辅助键索引叶子节点存放的是辅助键和主键，因此通过辅助键查询数据的时候，需要首先查询辅助索引得到主键，然后查询主键索引得到对应的数据；

对于非聚簇索引，主键索引节点存放的是主键和指向数据的指针，辅助索引节点存放的是辅助键和指向数据的指针；

如下所示，左边表示InnoDB索引维护结构，右边表示的MyISAM索引维护结构

以id为主键，name为辅助键

MyISAM和InnoDB主要区别innode支持事务, myisam不支持

innode数据存储在共享表，myisam存储在文件MYD和MYI

innode支持表级锁和行级锁，myisam支持表级锁

InnoDB支持崩溃后的恢复，MyISAM不支持

InnoDB支持外键，MyISAM不支持；

InnoDB不支持全文索引，MyISAM支持全文索引；

innodb是聚簇索引，myisam是非聚簇索引；

其他问题

1)InnoDB以主键索引进行数据维护，因此必须要有主键，如果没有定义系统会自动添加int类型主键；

2)InnoDB主键最好是int自增类型，原因是如果是其他字符串类型占用空间大，节点存放的数据有限；自增原因是和构建B+树节点分裂有关，如果是自增的那么每次插入都是最后一个叶子节点，使得树分裂次数尽量少，这样树尽量高度低，提升查找效率；

3)InnoDB非主键索引数据域为啥存放的是主键而不是数据。是基于结果一致性和节省空间考虑。如果非主键索引数据域存放的是数据那么一来空间浪费，同时如果主键索引数据变更，那么非主键索引也要改变。

4)为啥用B+树而不用B树实现索引，原因主要是基于范围查找考虑，B+树叶子节点是排序好的而且节点之间互相链接，因此对于范围查询非常高效。