MYSQL系列之 索引的概念

索引的常见模型:

1、哈希表，以key-value存储的数据结构。同样的哈希用链表存储（类hashmap）
优点: 插入很快
缺点: 链表非有序查询会很慢。范围查询遍历整个数据库
2、有序数组，
优点：等值查询（二分）、范围查询性能非常优秀
缺点：二叉搜索树维持有序需要log(N)、大量数据会访问多个数据块，访问会变慢
3、 InnoDB索引模型(B+)： 每一个索引在InnoDB中，对应一颗B+树

索引存储结构

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)，两棵树的示例示意图

索引分类:

主键索引(聚簇索引): 主键索引叶子节点存储整行数据
非主键索引(二级索引): 叶子节点存储的内容是主键的值

索引的区别:

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

索引的维护:

一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。
自增主键的必要性: 每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂

案例:

create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k)
)engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee')(600,6,'ff'),(700,7,'gg');

一条范围查询SQL执行几次搜索树: select * from T where k between 3 and 5

1、在 k 索引树上找到 k=3 的记录，取得 ID = 300；
2、再到 ID 索引树查到 ID=300 对应的 R3；
3、在 k 索引树取下一个值 k=5，取得 ID=500；
4、再回到 ID 索引树查到 ID=500 对应的 R4；
5、在 k 索引树取下一个值 k=6，不满足条件，循环结束。

回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）

覆盖索引(联合索引防止回表查询)

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

最左前缀原则

建立联合索引姓名和年龄时，会根据顺序创建B+树，如图，如果所以只查年龄是无法使用索引的

索引下推

索引找到第一个符合的值时，MySQL5.6之前，找到第一个符合的值后，会一个一个值回表查询，MySQL 5.6之后，会先对索引做判断，直接过滤掉不满足的记录，减少回表的次数。如图: