1200字范文 > 两种索引:MyISAM(非聚集索引)和InnoDB(聚集索引)的介绍

两种索引:MyISAM(非聚集索引)和InnoDB(聚集索引)的介绍

时间：2023-03-06 11:52:22

索引:

索引是帮助Mysql高效获取数据的排好序的数据结构

索引的数据结构

二叉树(早期Mysql用这个)

红黑树(早期Mysql用这个)

Hash表

B-Tree

B+Tree(B-Tree的变种)(现在在用)

=========================================

B+Tree

B+Tree(在内存查找,和磁盘做一次IO交互)

非叶子节点不存储data,只存储索引(冗余),可以放更多的索引(放在内存中查找,不和硬盘做IO交互)

叶子节点包含所有索引字段,存储data(data指的硬盘数据库的地址,先内存找索引,找到之后再和硬盘做IO交互)

叶子节点用指针连接,提高区间访问的性能

进行范围查找方便,比如:主键a>7或者a<7

B+Tree的存储机制

一行规定是16KB , 索引规定是8B(绿色部分) , 下一个磁盘文件地址规定是6B(白色部分) ; 16KB/(8+6)B等于1170 , 一行可以指出1170个下一个磁盘文件地址[在内存里查找] , 找到叶子节点的时候 , 叶子节点包含索引字段和data,保守估计占用内存1KB , 则三行就会有2千多万个数据

=========================================

两种索引

(通过索引文件和数据文件是否分离来分成聚集和非聚集索引)

MyISAM索引实现(非聚集)[索引文件和数据文件分离]

最后的是磁盘文件地址,拿到磁盘文件地址在进行IO操作(相当于回表)

InnoDB索引实现(聚集)[索引文件和数据文件不分离]

表数据文件本身就是按B+Tree组织的一个索引结构文件 (索引页)

聚集索引-叶节点包含了完整的数据记录 (数据页)

为什么建议InnoDB表必须建主键,并且推荐使用整型的自增主键?

如果没有主键,那么B+Tree则维护不了 ; 则数据库会在每一列数据库里面加一个唯一索引(也就是每一列都没有重复的数据[和主键一样]) ; 如果每列都没有唯一索引,那么数据库会建立一个隐藏列(里面装主键,每一行数据都是唯一的),来维护B+Tree

整型:数据量很大的话,用UUID比较的话,会很慢,因为字符串很长 ; 自增:B+Tree的一个机制,如果不是自增的话,他会插入某条数据的中间,存满了之后,可能向上分裂,整个B+Tree都会变化,如果自增的话,变化会小一点

为什么非主键索引结构叶子节点存储的是主键值

一致性和节省存储空间

索引可以用B+Tree和Hash表来存储,为什么大部分情况不用Hash表存储

Hash只能满足"=","IN",不支持范围查询,比如大于,小于,大于等于,小于等于....等等

Hash冲突问题

但很多时候Hash索引要比B+树索引更高效,比如数据爆表的查询

缺点：新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序。缺点：新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序。

两者区别

聚集索引在叶子节点存储的是表中的数据。

非聚集索引在叶子节点存储的是主键和索引列。

=========================================

用索引要遵守最左原则

KEY 'idx_name_age_position' ('name','age','position') USING BTREEEXPLAIN SELECT * FROM employee WHERE name='Bill' and age=31;//用了索引EXPLAIN SELECT * FROM employee WHERE age=30 and position='dev';//没用索引EXPLAIN SELECT * FROM employee WHERE position='manager';//没用索引

原理:因为索引是按照name先进行排序的 , 如果name相同则进行age排序 , 如果age相同则进行position排序 ;

比如查Bill,有很多个Bill,后面查到HanMeimei就不用管了,然后查age,查到31,如果有很多个31,则查position

=========================================

联合索引

创建一个联合索引并搜索值

create index idx_t1_bcd on t1(b,c,d);select * from t1 where b=1 and c=1 and d=1;

叶子节点里面的data是主键(黄色部分),然后在去主键索引里面找数据,这个过程叫做回表

select * from t1; #ALL(全盘)搜索,需要回表select b from t1; #index搜索,不需要回表,因为只找b,我们建立的bcd索引,存的数据比主键索引存的数据少,而我们只找b,所以说从bcd索引找数据更快select c from t1; #index搜索,不需要回表select b,c,d,a from t1; #index搜索,不需要回表select * from t1 where b>1; #ALL搜索,需要回表,因为他选择的是全部的数据,select b from t1 where b>1; #range搜索,不需要回表select b,c,d,a from t1 where b>1; #range搜索,不需要回表,因为叶子节点记录的是bcda,其他的没记录,所以不需要回表select * from t1 order by b,c,d; #ALL搜索,排序(内存)&不用回表,比index搜索,不用排序&多次回表(硬盘) 快;