MySQL索引原理与应用

InnoDB的索引模型

在InnoDb中，表是根据主键顺序，以B+树的模型建立索引的形式存放的，这种存储方式的表称为索引组织表。

所以，每一个索引在InnoDb里面都对应一棵B+树。

假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下。

根据索引组织结构图可以看出，主键索引这棵B+树的key是主键，value是该主键对应的整行数据。而普通索引（其实不仅仅是普通索引，而是所有非主键索引的索引）这棵B+树的key是索引字段，value是主键id。

所以我们可以想象一下根据不同的索引来检索数据的过程是怎样的。

根据主键索引查找数据：最简单，在主键索引对应的B+树中按顺序遍历，查找到符合where条件（where条件中是主键的时候用主键索引）的key，然后将value返回即可。
根据其他索引查找数据：在相应的B+树中按顺序遍历，查找到符合where条件的节点，获取value，value中是主键的值，然后拿value作为key去主键索引中去查找，然后返回主键索引中的value。这个过程称为回表。
- 覆盖索引：如果select后面的字段只有主键的话，就不需要回表了，可以直接在普通索引的value中获取返回。
- 联合索引：个人认为联合索引就是将多个字段按一定顺序合并成一个字段，以后只要有从最左边开始就符合这个顺序的字段序列作为查询条件，就能用到这个联合索引。比如：有A,B,C三个字段按顺序组成联合索引(A,B,C)，那么如果查询条件中有A,AB,ABC这三种情况都是可以使用的，但是如果是AC，这就破坏了“顺序”，就不能使用了。这个叫做最左前缀原则。
  - 索引下推：有了联合索引之后，还是上面A,B,C的例子，如果查询条件是A,C会怎么样呢，首先根据最左前缀原则，使用联合索引的A部分来查询条件A，然后再判断C是否满足查询条件，如果满足就可以拿value值作为主键索引的key去获取数据返回，如果不满足，则继续下一条判断，这叫做索引下推。

索引是按页存储的，其实机械硬盘就是按页存储的，也是按页读取的。

所以当pageA满了之后再往里面出入数据，将会发生页分裂，如图：

页分裂会影响插入的性能，所以我们要使用自增主键作为表的主键，这样每次插入都会在后面插入，不会导致页分裂。 MySQL会特意留一下空间，减少页分裂的次数。在删除数据的时候，并不是真的把数据删除，只是把这个索引位标记为可以使用。

这样就会导致一个后果：删除了大量的数据，表所占的硬盘空间还是不变。

解决这个问题的办法就是重建表。

个人见解：表一定要用自增主键作为主键，同时还要有业务主键作为唯一索引，当然可以不设置为唯一索引的，但是开发人员必须要知道。这样可以应对重建表时主键发生变化的问题。

声明:图片来自极客时间