MySQL 索引详解

原文章来自我的语雀知识库

为什么不选择AVL树、红黑树、B树

为什么不是AVL树（平衡二叉树）、红黑树

AVL树与红黑树实际上是二叉搜索树的改进版本。
二叉搜索树利用了二分查找的思想，每次比较都可以排除掉左子树或者右子树。二叉搜索树的问题在于，如果原数组本身就是有序的，那么将原数组转化为二叉搜索树后，二叉搜索树将退化为线性表。

AVL树（平衡二叉树）实际上是一个平衡的二叉搜索树，从定义上来说，AVL树首先必须是二叉搜索树，而且任意一个节点的左右子树的深度差不能大于1。AVL树通过左旋、右旋来实现严格的自平衡，这也导致维护AVL树的平衡性要付出性能代价，所以AVL树比较适合插入删除次数较少（旋转次数较少），但查找多的情况。
红黑树（弱平衡二叉树）是一个弱平衡的二叉搜索树，它需要的旋转次数比AVL树更少，但是同等数量的结点，红黑树的深度比AVL更深。所以红黑树适用于插入、删除、搜索操作较多的情况。
总而言之，红黑树和AVL树都能以O(log2n)的时间复杂度进行增删查操作。但红黑树的任何不平衡都会在三次旋转之内解决，所以红黑树的统计性能更高。
那究竟为什么MySQL选择B+树，而不选择AVL或红黑树呢？
这主要是因为B+树是一种多路平衡查找树,多路平衡查找树与二叉平衡搜索树的差别就在于，B+树的一个非叶结点存放了多个索引键，存放大规模数据的情况下，B+树的层数比AVL树、红黑树要低得多。可以说，B+树是针对磁盘设计的一种平衡查找树，而AVL树、红黑树是针对内存设计的平衡查找树。
我们知道MySQL的数据都是存在磁盘里的，如果树的深度过高，那么就需要相当次数的磁盘IO，根据磁盘查找存取的次数往往由树的高度所决定。举个例子，现在要存放1000000多条数据，那么红黑树存储至少需要20层。假设B+树一个节点可以存100个索引键，那么3层的B+树大约可以容纳1003约等于1000000条数据。所以用B+树可以减少磁盘IO次数。
还有一点可以看出B+树是针对在磁盘上存储大规模数据而设计的，数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。计算机系统是分页读取和存储的，每次读取和存取的最小单元为一页，而**磁盘预读时通常会读取页的整倍数。**这利用了局部性原理。这样的设计也减少了磁盘IO的次数。
**磁盘预读 因为主存和磁盘访问效率的巨大差异，磁盘 I/O 变成了一个很重量级的操作，因此需要尽可能减少磁盘 I/O 的次数，为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存**,这样做的理论依据是局部性原理，即当计算机访问一个地址的数据的时候，通常与其相邻的数据也会很快被访问到。
**InnoDB 数据页的默认大小是 16KB**，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。
顺带一题，既然B+树深度这么低，为什么不在内存中也用B+树呢？我个人认为，因为内存中不太可能像数据库那样存放海量数据，而且内存中对树的结点进行操作并不需要磁盘IO的开销，而且B+树的维护成本和开发成本比红黑树更大，需要处理页分裂、合并、平衡等操作。

为什么是B+树？

1. 磁盘读写代价更低
   B+树的非叶节点没有像B树那样存放数据（B树的非叶节点结构体有Data域），所以相同大小的节点，B+树可以存放更多的索引键，一个结点能存放更多的索引键就意味着其子节点的个数越多，子节点的个数越多就意味着树的高度更低。更低的树高度就意味着更少的磁盘IO。比方说B+树的某个非叶节点存放了100个索引键，那么这个非叶节点最多就有101个子节点。
2. 查询效率更加稳定
   B+树的所有数据存放在同一层叶子节点中，查找不同的数据所需要的磁盘IO次数相当。
3. 范围查询、对于主键的排序查询更快
   B+树的叶子节点是用双向链表连接起来的，且叶子节点存放的数据按照索引键排序，所以B+树的范围查询只需要找到下限（或上限）所在的叶子节点，然后通过双向链表向前（或向后）遍历即可。而B树、AVL树、红黑树的范围查询需要先找到下限，然后再使用中序遍历查找。（因为B树是平衡多路查找树）

B树与B+树的范围查询过程对比

假设查找的元素范围为3~11
B-树：
自顶向下，查找到范围的下限(3),然后进行中序遍历

自顶向下，查找到范围的下限(3)

中序遍历到元素6

中序遍历到元素8

中序遍历到元素9

中序遍历到元素11，遍历结束
B+树范围查询

自顶向下，查找到范围的下限(3)所在的数据页

通过链表指针，遍历到元素6, 8

通过链表指针，遍历到元素9, 11，遍历结束
B+树只需要找到范围下限3,然后遍历链表即可，效率更高。

B+树的操作

B+树的范围查询

假设查询的范围为[5,11]，走聚簇索引。
首先，将聚簇索引的根节点通过磁盘IO读入内存，根节点是一张大小为16KB的数据页，其中的索引键已经排好序了，所以我们可以使用二分查找，查找这张数据页中的目标值5所在的区间，于是我们找到目标值5所在的子树应该是[-INF,8]对应的指针所指向的子树。就这样递归地自顶向下查找，直到找到目标值5所在的叶子节点，将这个叶子节点读入内存，同样使用二分查找在数据页内找到目标值5，然后向后读取范围内的值，直到达到上限（找到第一条不满足的为止）。如果需要向后读取其它叶子节点，只需要通过双向链表就能找到与其相邻的叶子节点了。

B+树的插入操作

情况1 叶子节点没有满

直接插入目标值28到叶子节点中，无事发生。

情况2 叶子节点满了，且其上层非叶子节点未满

以这个叶子节点的中间值为标准，将这个叶子节点的数据拆成两半，后一半数据放入新创建的叶子节点中，这个中间值作为索引键加入到其上层的非叶子节点中。
这其实是发生了页分裂，可以看到，发生页分裂后，会产生两个有空隙的数据页，这两个数据页并没有填充满数据。避免页分裂的方法，最简单的方法就是使得主键是递增的，只要主键是递增的，那么新插入的数据就会插入到最右边的叶子节点中，或者是在最右边新建一个叶子节点，而不会发生页分裂。
页分裂是B+树用来保持自己平衡的一种方式，但页分裂就意味着会产生磁盘IO操作，所以要尽量避免页分裂操作。

情况3 叶子节点和其上层非叶子节点都满了

这样会由底向上递归地产生页分裂，上层非叶子节点的数据也会按照索引键的中间值拆分成两个的非叶子节点，新产生的那个非叶子节点的索引键会加入到更上一层的非叶子节点，如果更上一层的非叶子节点也满了，也会发生页分裂。这样不断地自底向上产生页分裂递归，直到某个非叶子节点能容纳新产生的索引键为止。

实际上，innodb实现的B+树索引比上述过程要复杂得多，因为要考虑线程安全、而且要考虑减少页分裂造成的空间浪费问题。

B+树的删除操作

索引的添加与删除

索引的类别

① UNIQUE 唯一索引：索引列不允许出现重复值，允许索引列出现多个NULL值。
② INDEX 普通索引：允许索引列出现相同值和NULL值。
③ 联合索引：将多个列作为索引列。
④ FULLTEXT 全文索引
⑤ PRIMARY KEY 聚簇索引：MySQL是索引组织表，所以聚簇索引必须有且仅有一个，如果存在有主键约束的列，那么以这个列作为索引列建立聚簇索引。否则，将使用第一个唯一索引建立聚簇索引。否则，innoDB使用隐藏列rowid建立聚簇索引。
主键可以是包含多个列。创建复合主键的方式如下图。这实际上是给多个列加上了主键约束。

建立索引的语法

使用ALTER TABLE语句

ALTER TABLE 表名 ADD INDEX|UNIQUE [INDEX]|PRIMARY KEY [索引名] (索引列的列表);
其中中括号[]括起来的可选

使用CREATE INDEX语句

CREATE [UNIQUE] INDEX 索引名 ON 表名 (索引列的列表);其中UNIQUE可选，也就是说，CREATE INDEX不能创建聚簇索引。

创建前缀索引、联合索引

删除索引的语法

DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP INDEX 索引名;

索引失效

OR会导致索引失效吗

在MySQL5.0版本及以前，确实会，但在5.1版本及以后，MySQL引入了索引合并优化。
如果OR连接的所有列均建立了索引，那么OR连接的每一个列都用到了各自的索引，最后进行索引合并优化，索引不会失效。
如果OR连接的其中一列没有建立索引，那么就没法进行索引合并，索引失效，走全表查询。
举个例子，SELECT * FROM table WHERE A=1 OR B=2;如果A、B都建立了索引，那么搜索引擎会去A、B的索引中分别找出符合A、B条件的记录，然后对两份记录求并集进行索引合并。但如果B没有建立索引，那就没法进行索引合并了，只能扫描整个表。

索引合并优化

例①：如果有联合索引(key1,key2)的话，那么下面例子中的sql应该走联合索引。可是，在只有索引(key1)和索引(key2)的情况下，就只能先去两个索引中查找，再求交集进行索引合并。求交集是因为这是AND条件。

例②：先去索引(name)和索引(age)分别查出符合条件的记录，然后进行索引合并，由于是OR条件，索引合并的时候取并集。

不符合最左匹配原则

假设某个表只存在联合索引(a,b,c),以此为条件展开讨论
联合索引(a,b,c)意味着B+树的叶子节点先按照a排列、其次按照b排列、最后按照c排列。

等值查询

以下三个条件都会正常走联合索引，因为WHERE子句中的等值条件都符合最左匹配原则
WHERE a=1; a=1使用索引(a,b,c)
WHERE a=1 AND b=2; a=1和b=2使用索引(a,b,c)
WHERE a=1 AND b=2 AND c=3a=1和b=2和c=3使用索引(a,b,c)
但以下三个条件就不能使用这个联合索引了，因为WHERE子句中的等值条件不符合最左匹配原则
WHERE b=2;不使用联合索引(a,b,c)
WHERE c=3;不使用联合索引(a,b,c)
WHERE b=2 AND c=3;不使用联合索引(a,b,c)

>、<或者like ‘%j%’

WHERE a>1 AND b=2只有a>1用到了联合索引
这是因为a>1不存在等值查询，无法匹配联合索引(a,b,c)中的第一列。在扫描的时候，首先定位到符合a>1的第一条记录，然后从这条记录向后扫描。b=2这个条件无法用来缩小扫描区间。

>=或者<=或者like 'j%'或者BETWEEN AND

WHERE a>=1 AND b=2a、b都用到了联合索引。这是因为，a>=1存在等值查询，在扫描的时候，先定位到符合条件a>=1的第一条记录，如果这条记录符合a=1，那么在a=1的记录的范围内，b是有序的，所以可以在a=1的范围内通过二分找到a=1且b=2的第一条记录，然后再往后扫描剩余的记录。

存在联合索引(name,age)条件为WHERE name like 'j%' AND age=22;name和age都用到了联合索引(name，age)，原理同上，在name='j’的范围内，可以通过二分法找到第一条满足age=22的记录，然后往后扫描。

WHERE a BETWEEN 2 AND 8 AND b = 2a、b都用到了联合索引，因为在 MySQL 中，BETWEEN 包含了 value1 和 value2 边界值，类似于 >= and =<。

联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配

索引列上有计算

WHERE id+1=2索引列id使用了计算，索引失效

索引列使用了函数

WHERE COUNT(amount)=100;索引列amount使用了函数，索引失效

两个索引列作对比

WHERE id=height;索引失效，因为不能通过id=height来确定某个确切的扫描空间。

ORDER BY子句

WHERE a=1 ORDER BY b,c;a=1和按b、c排序用到了联合索引，因为满足最左匹配原则。
WHERE a=1 ORDER BY c;只有a=1用到联合索引，因为a=1的范围内c是无序的。
WHERE a>1 ORDER BY b,c;只有a>1用到联合索引，因为a>1不是等值查询。
ORDER BY a,b,c先后按照a、b、c排序用到了联合索引。
ORDER BY a,d;d不存在于联合索引中，所以不能用联合索引来排序
ORDER BY a DESC, b ASC;a、b的排序方向相反，不能用联合索引来排序
WHERE a=1 AND b IN(1,2) ORDER BY c;虽然a=1 AND b IN(1,2)可以用联合索引，但是ORDER BY c不能用联合索引排序，因为在a=1且b=1、a=1且b=2这两个范围内的数据，c是无序的。

IN关键字

只使用IN也会走索引，但同样要满足最左匹配原则。

Cardinality值

并不是在所有的查询条件中出现的列都需要添加索引，一般为高选择性的列建立索引才有意义。对于性别字段、地区字段、类型字段，它们可取值的范围很小，称为低选择性。
按性别进行查询时，可取值的范围一般只有’M’、‘F’。因此上述SQL语句得到的结果可能是该表50%的数据（假设男女比例1:1)，这时添加B+树索引是完全没有必要的。相反，如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，则此时使用B+树索引是最适合的。例如，对于姓名字段，基本上在一个应用中不允许重名的出现。
其中的原因在于，建立的索引的目的是希望减少扫描数据的范围。比方说我希望查询name=‘John’，如果整个表只有聚簇索引，那么就只能全表查询，找出所有name是John的人。但如果为name建立了索引，由于索引键name在每个数据页中是排序的，所以可以利用二分查找的思想，经由3到4次磁盘IO找到目标记录的主键ID，然后再用这个主键ID回到聚簇索引去查到相应的记录。但假设说，如果表中一半个人都叫John，由于维护索引是有代价的、而且很多叶子节点都有目标记录的话，由于数据页之间虽然是用双向链表连通的，但它们只是逻辑上连续而不是物理上连续，就会进行更多次磁盘IO，那倒不如直接去聚簇索引进行全表查询。
Cardinality表示索引中不重复索引键数量的预估值，比方说，对某张有10000条记录的表的name列建立索引，那么我们希望name索引的Cardinality值为10000，因为这表示name的选择性很高。但如果精确地统计Cardinality值，付出的代价会很高，所以MySQL采用了抽样去平均数的方法。随机取八个叶子节点，分别统计每个叶子节点中不重复索引键的数量，相加后除以8，预估所有叶子节点的不重复索引键的数量都是这么多。当然，为了避免频繁地统计Cardinality值，MySQL设定1/16的数据发生变化、或者是发生了2000 000 000次写操作，会触发统计Cardinality值。
可以使用SHOW INDEX命令来查看某个表中的所有索引。