MySql原理（二）InnoDB索引原理

1.聚簇索引（主键索引）

结构图

InnoDB将主键的值组织成一颗B+树，行的具体数据存在叶子节点中，叶子节点形成一个链表。每个节点存储一页的数据(16K)。

InnoDB的数据文件本身就是索引文件，B+Tree的叶子节点上的data就是数据本身，key为主键，这是聚簇索引。

索引数据和叶子节点存储到一起，这样它们是一起被载入内存的，找到叶子节点也就直接返回数据，不需要另外寻址。

page页
页是InnoDB存储引擎管理数据库的最小磁盘单位。InnoDB中的页大小为16KB，所以一个B+树的节点并不止存储一条数据，而是存储一页数据。
页可以空或者填充满（100%），行记录会按照主键顺序来排列。例如在使用AUTO_INCREMENT时，你会有顺序的ID 1、2、3、4等。

页还有另一个重要的属性：MERGE_THRESHOLD。该参数的默认值是50%页的大小，它在InnoDB的合并操作中扮演了很重要的角色。

当你插入数据时，如果数据（大小）能够放的进页中的话，那他们是按顺序将页填满的。
若当前页满，则下一行记录会被插入下一页（NEXT）中。

每一页都有next指针和pre指针，便于快速查找下一页和上一页。

页合并
当你删了一行记录时，实际上记录并没有被物理删除，记录被标记为删除(flaged)，并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到MERGE_THRESHOLD（默认页体积的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

在示例中，页#6使用了不到一半的空间，页#5又有足够的删除数量，现在同样处于50%使用以下。从InnoDB的角度来看，它们能够进行合并。

合并操作使得页#5保留它之前的数据，并且容纳来自页#6的数据。页#6变成一个空页，可以接纳新数据。

页分裂
前面提到，页可能填充至100%，在页填满了之后，下一页会继续接管新的记录。但如果有下面这种情况呢？

页#10没有足够空间去容纳新（或更新）的记录。根据“下一页”的逻辑，记录应该由页#11负责。然而：

页#11也同样满了，数据也不可能不按顺序地插入。怎么办？
InnoDB的做法是（简化版）：

• 创建新页
• 判断当前页（页#10）可以从哪里进行分裂（记录行层面）
• 移动记录行
• 重新定义页之间的关系

为什么主键使用自增id比较好？

其实就是上面的会导致页分裂。
当顺序递增插入的时候，只有最后一个节点会在满掉的时候引起索引分裂，此时无需移动记录，只需创建一个新的节点即可。而当非递增插入的时候，会使得旧的节点分裂，还可能伴随移动记录，以便使得新数据能够插入其中。

2.非聚簇索引（辅助索引，二级索引）

必须要有主键索引存在。
b+树结构，每个节点存储索引的值，每个节点存储一页的数据(16K)，与主键索引不同的是：叶子节点存储的是主键索引主键的值（并不是地址）。

因为叶子节点是存储的主键的值，所以主键长度越小，二级索引占用的空间也就越小，所以要避免使用过长的字段作为主键。

为什么存储的主键的值，并不是地址？
如果聚集索引数据记录发生了索引分裂导致数据地址变了，那辅助索引也要更新。

回表
先通过非聚簇索引的值定位聚簇索引主键值值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，叫回表。

覆盖索引
就是select的字段就是非聚簇索引。不需要进行回表查询，一次就能在非聚簇索引中查到。
explain 显示 Using index就是走了覆盖索引。

3.联合索引

假设，我们对(a,b)字段建立索引，那么入下图所示

如图所示他们是按照a来进行排序，在a相等的情况下，才按b来排序。
因此，我们可以看到a是有序的1，1，2，2，2，3，3。而b是一种全局无序，局部相对有序状态! 什么意思呢？

从全局来看，b的值为4，1，2，1，4，1，2，是无序的，因此直接执行b = 2这种查询条件没有办法利用索引。

从局部来看，当a的值确定的时候，b是有序的。例如：a=1，b为1,2是从左到右排列的。因此，你执行a = 1 and b = 2是a,b字段能用到索引的。而你执行a > 1 and b = 2时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b值不是有序的，因此b字段用不上索引。

题目实战
1.如果有一个sql为

SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;

如何为其建立索引?
(a,b,c)或者(c,b,a)或者(b,a,c)都可以， 重点要的是将区分度高的字段放在前面，区分度低的字段放后面。像性别、状态这种字段区分度就很低，我们一般放后面。 （区分度越低，树的查找的时间越长）

2.有一个sql

SELECT * FROM table WHERE a > 1 and b = 2;

如何为其建立索引?
如果是(a,b)索引， 因为a>1是范围，b就不会走索引。
如果是对(b,a)建立索引，优化器会把where的b=2提前，就都会走索引。

4.不走索引几种情况

(1) 对索引列使用了函数 不会走索引，如：

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引,因为所有索引列参与了计算
SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会使用索引,因为使用了函数运算,原理与上面相同

(2) like “%xx” 不走索引：

SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引

(3) 发生隐式转换时 不走索引：

CREATE TABLE `a` (`a` char(10));
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引

(4) or条件中只要有一个不是索引时 不走索引：

create_activity_id 和 item_id都是 有索引的，上面索引类型是index_merge（索引合并），简单说就是在用OR，AND连接的多个查询条件时，可以分别使用前后查询中的索引，然后将它们各自的结果合并交集或并集。