第8章-11 索引的形成过程

        上一篇：《第8章-10 慢日志查询》，接着回顾下索引的形成过程

索引的形成过程

数据库的 IO 是以页(16KB)为单位的，且页内地址是连续的。

示例表t

create index idx_t on t(c1);

创建叶子节点

1. 提取数据，提取索引列数据：(c1列+行记录地址或主键)

2. 将提取的数据进行排序，然后存入页中。

大于16KB后，会形成页链表

页内：单链表结构，记录从小到大

页间：双链表结构，下一页的最小记录大于上一页的最大记录。

创建页目录

1，提取每个页中的最小记录及页号形成新的条目

2，排序(页内、页间)

页目录

页子节点

数据量很多，页目录也会跟多

创建根节点

最左前缀原则

索引树的排序规则

单列索引：c1

        order by c1;

组合索引：c1,c2

        先对c1排序，在c1相同的情况下，再对c2排序

组合索引：c1,c2,c3

        先对c1排序，在c1相同的情况下，再对c2排序，在c1,c2相同的情况下，再对c3排序

组合索引：c2,c1,c3

        先对c2排序，在c2相同的情况下，再对c1排序，在c2,c1相同的情况下，再对c3排序

规律：

        先对索引第一列排序，然后依次排序（最左优先）

索引树的检索过程

        1. 确定where条件中是否含有索引的 前导列，含有则进行2，否则停止检索

        2. 在索引树中进行检索。检索规则：先比较c1，在c1相等的情况下再比较c2，在c1,c2相等的情况下再比较c3

最左前缀规则

c1=4 and c2=6 and c3=1

检索示例(c1,c2,c3)

全匹配

• where c1=4 and c2=6 and c3=1

先在根节点中通过二分法确定[4.6,1]条目所在的目录节点：

(2,1,7)< [4.6,1] < (7,1,2) ---> 页48

在页目录中继续使用二分法确定[4,6.1]条目所在的叶子节点：

(4.5,2) < [4.6,1] < (5,2,6) ---> 页56

在叶子节点中继续使用二分法定位[4.6.1]条目，获取行记录地址或主键：

(4,6,1)--->行记录地址或主键

部分匹配(含前导列)

• where c1=6

先在根节点中通过二分法确定[6。]条目所在的目录节点：

(2,1,7) <[6,,]<(7,1,2) ---> 页48

在页目录中继续使用二分法确定[6.条目所在的叶子节点：

(5,2,6) < [6,,] ---> 页58

在叶子节点中继续使用二分法定位[6.]条目：

(6,1,7) ---> 行记录地址或主键

• where c1=5 and c3=3

先在根节点中通过二分法确定[5.3]条目所在的目录节点：

(2,1,7）< [5,,3] < (7,1,2) ---> 页48

在页目录中继续使用二分法确定[5，3]条目所在的叶子节点：

(4,5,2) <[5,,3] == (5,2,6) ---> 页56,58

在叶子节点中继续使用二分法定位[5.3]条目：

(5,1,3) ----> 行记录地址或主键

(5,2,6) ----> 行记录地址或主键

(5,2,8) ----> 行记录地址或主键

无前导列

• where c2=1 and c3=3

 没有c1项，无法使用索引

        最左前缀规则：索引是按最左优先排序规则创建索引树的，所以where条件中必须含有索引的前导列才能通过索引进行检索。

二级索引和聚簇索引

二级索引

特点

        排序规则：按索引列排序

        叶子节点：索引列+行记录地址(innodb存储引擎的表是主键值)

        页目录条目：索引列+页号

需要单独创建：create index idx on t(c1);

c1， c2, c3

前面三列是索引值

优缺点

        优势：占用存储空间小；对同一个表可以创建多个二级索引

        劣势：多数情况下需要回表进行二次查询少数情况下： c1,c2,c3 直接在索引内。如果是查c4，就要进行回表查询

聚簇索引

特点

        排序规则：按主键排序

        叶子节点：行记录

        页目录条目：主键+页号

        对于Innodb引擎，默认就是聚簇索引表，不需要单独创建。

聚簇索引表 T

create table t (
c1,c2,c3 primary key ('c1') 
)engine=innodb;

优缺点

        优势： 根据主键查询条目时，不用回表，叶子节点就是行记录。

        劣势： 如果碰到不规则主键数据插入时，会造成频繁的页分裂，影响系统性能。

插入：

        假设这是聚簇索引中的第一个叶子节点页10，它中已经存放了3条记录，此时需要再插入一条记录到表中。假设页10最多只能存放3条记录，所以需要分配一个新页来存放记录。

第一步： 将主键值为5的记录移动到页28

第二步：将主键值为4的记录移动到页10

总结：

        熟悉索引的形成过程，有利于更进一步了解索引，如何使用索引提升查询效率。

         上一篇：《第8章-10 慢日志查询》

         下一篇：《第9章-1 复制》