聚簇索引
B+树本身就是一个目录
1.使用记录主键值的大小进行记录和页的排序
2.B+树的叶子节点存储的是完整的用户记录
具有这两种特性的B+树称为聚簇索引
二级索引 (辅助索引)
用c2列 作为数据页,页中记录的排序规则页内记录按照c2列大小排成单项链表,存放用户记录页按照c2列大小排成双向链表,存放目录项记录页按照c2列大小排成双向链表。
2.B+树的叶子节点存储的并不是完整的用户记录,而只是c2列+主键这两个列的值
3. 目录项记录中不再是主键+页号的搭配,而变成了c2列+主键+页号的搭配
如果想得到完整的用户记录 需要在二级索引中 找到对应的记录的主键 再根据主键值去聚簇索引中再查找一遍完整的用户记录 这个过程叫做 回表
联合索引
以使用c2和c3建立联合索引为例
1.每条目录项记录是由c2,c3和页号组成,先按照c2排序,c2相同,再对c3排序;
2.B+树的叶子节点的用户记录是由c2,c3和主键c1组成;
一个页面最少存储2条记录
MyISAM
MyISAM中建立的索引相当于全部都是二级索引
InnoDB中的索引即数据,数据即索引,而MyISAM中却是索引是索引、数据是数据。
叶子节点 存储的是 行号+主键+(地址偏移)
如果 MyISAM的行格式有定长记录格式(Static) 直接计算然后在偏移出取数据
如果使用的是 变长记录格式 ,那么会在索引叶子节点处存储该条记录在数据文件中的地址偏移量,然后再取数据。
索引的使用
InnoDB和MyISAM会自动为主键或者声明为UNIQUE的列去自动建立B+树索引
CREATE TALBE 表名 (
各种列的信息 ··· ,
[KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)
ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);
ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;
创建index_demo表的时候就为c2和c3列添加一个联合索引
CREATE TABLE index_demo(
c1 INT,
c2 INT,
c3 CHAR(1),
PRIMARY KEY(c1),
INDEX idx_c2_c3 (c2, c3)
);
总结对于InnoDB存储引擎来说,在单个页中查找某条记录分为两种情况:以主键为搜索条件,可以使用Page Directory通过二分法快速定位相应的用户记录。
以其他列为搜索条件,需要按照记录组成的单链表依次遍历各条记录。
2.没有索引的情况下,不论是以主键还是其他列作为搜索条件,只能沿着页的双链表从左到右依次遍历各个页。
3.InnoDB存储引擎的索引是一棵B+树,完整的用户记录都存储在B+树第0层的叶子节点,其他层次的节点都属于内节点,内节点里存储的是目录项记录。InnoDB的索引分为两大种:聚簇索引
以主键值的大小为页和记录的排序规则,在叶子节点处存储的记录包含了表中所有的列。
二级索引
以自定义的列的大小为页和记录的排序规则,在叶子节点处存储的记录内容是列 + 主键。
4.MyISAM存储引擎的数据和索引分开存储,这种存储引擎的索引全部都是二级索引,在叶子节点处存储的是列 + 页号。