MySQL 索引

本文介绍了MySQL的索引概念,包括索引的分类和数据结构,如B+树与B-tree、红黑树的区别。讨论了B+树在范围查询中的优势,以及与Hash表的对比。此外,还详细阐述了物理存储中的聚簇索引和二级索引,解释了回表查询、覆盖索引和前缀索引的概念,并简单提及了MyISAM存储引擎的索引特点。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1. 什么是索引?

客户端-》【缓存-》语法解析器-》查询优化器-》查询执行引擎】-》索引和数据

索引是辅助存储引擎高效获取数据的一种数据结构,索引是数据的目录,便于搜索引擎快速定位数据

2. 索引的分类

数据结构: B+ tree、Hash、Full-text索引

物理存储:聚簇索引和二级索引(辅助索引)

字段特性:主键索引、唯一索引、普通索引、前缀索引

字段个数: 单列索引和联合索引(复合索引)

2.1 数据结构

InnoDBMyISAMMemory
B+tree 索引YesYesYes
Hash索引NoNoYes
Full-text索引YesYesNo

3. 类型对比

3.1 B+树与B-tree的区别?

相对于B-tree,B+tree只在叶子节点存储数据,而B-tree的非叶子节点也存储数据,索引B+tree单个节点的数据量更小,在相同的磁盘I/O次数下,能查询更多的节点,另外我们注意到B+tree叶子节点采用单链表连接,适合MySQL中常见的基于范围的顺序检索场景,而B-tree无法做到这一点。

3.2 B+树和红黑树的对比?

对于有N个叶子节点的B+tree,其搜索复杂度为O(logdN),其中d(degree)为B+tree的度,表示节点允许的最大子节点个数为d个,在实际的运用过程中d值是大于100的,即时数据达到千万级别时B+tree的高度依然维持在3-4左右,保证了3-4次磁盘I/O操作就能查询到目标数据,而从红黑树的示意图中,可以看到红黑树是二叉树,节点的子节点个数为两个,意味着其搜索复杂度为O(logN),比B+tree树高出不少,因此红黑树检索到目标数据所需的磁盘I/O次数更多。

3.3 B+树相对于Hash表

范围查询是MySQL中常见的场景,Hash表不适合做范围查询,它更适合做等值查询,另外Hash表还存在Hash函数选择和Hash值冲突等问题(因为这些原因,B+树索引要比Hash表索引有更广的适用场景)

4. 物理存储

InnoDB表的索引按叶子节点存储,是否为完整表数据分为聚餐索引二级索引,全表数据就是存储在聚簇索引中的,聚簇索引以外的索引叫做二级索引,我们结合实际的例子来介绍下这两类索引,在测试数据库中创建一个包含销售员信息的测试表workers。

CREATE TABLE `workers` (
	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工工号',
	`name` varchar(36) NOT NULL COMMENT '员工名字',
	`sales` int(11) DEFAULT NULL COMMENT '员工销售业绩',
	PRIMARY KEY(`id`)
) ENGINE = INNODB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

插入数据:

insert into workers(id, name, sales) values (1, 'Georgi', 12744);
...

在这个例子中workers表的聚簇索引建立在字段id上的,它的B+tree的示意图是这样的。聚簇索引的每个叶子节点存储了一行完整的表数据,叶子节点间采用单向链表按id列递增连接,可以方便的进行顺序检索。(InnoDB表必须要有聚簇索引,默认在主键字段上建立聚簇索引,在没有主键字段情况下,表的第一个NOT NULL的唯一索引被建立为聚簇索引,在前两者都没有的情况下,InnoDB将自动生成一个隐式递增id并在此列上建立聚簇索引)。

在name字段上添加二级索引:

ALTER TABLE `workers` ADD INDEX index_name(`name`);

二级索引的示意图可以看出,二级索引的叶子节点并不存储一行完整的表数据,而是存储了聚簇索引所在的列的值,业务就是workders表中的id列的值。

回表查询

由于二级索引的叶子节点不存储完整的表数据,索引当通过二级索引查询到聚簇索引列值后,还需要回到聚簇索引也就是进一步获取数据。

select * from workers where name='Parto'

这条语句中,在二级索引index_name中查询到主键id=3,接着需要带着id=3这个条件,进一步回到聚簇索引查询后获取到完整的数据,很显然回表需要额外的B+tree搜索过程,必然增大查询耗时。需要注意的是,通过二级索引查询时,回表不是必须的过程,当query的所有字段在二级索引中能够查找到时,就不需要回表。MySQL称此时的二级索引为覆盖索引或者触发了索引覆盖。

覆盖索引
select id, name from workers where name='Parto';

通过explain命令查看这条SQL的执行计划,可以看到Extra中使用了Using index,表名查询触发了二级索引index_name的索引覆盖。

对比如下语句:

explain select id, name, sales from workers where name='Parto';

可以看到Extra中显示的是NULL,并没有Using index,表明没有触发索引覆盖,需要回表查询。

前缀索引

对字符类型字段的前几个字符或对二进制类型字段的前几个bytes字段建立的索引而不是在整个字段上建立索引。

create index index_name on persons (name(5)) comment '前缀索引';

例如可以对persons表中的name(varchar(16))字段,name字段的前5个字段建立索引,通过命令show index from persons,可以看到索引信息中的索引key的Sub_part中的长度为5。前缀索引可以建立的类型有:char、varchar、binary、varbinary的列上,可以大大减少索引占用的存储空间,也能提升索引的查询效率。

MyISAM介绍

MyISAM中不存在聚簇索引的表,MyISAM中的主键索引和非主键索引的结构是一样的,从图中可以看出他们的叶子节点是不存储数据的,存放的是表数据的地址,索引MyISAM表可以没有主键,MyISAM表的数据和索引是分开的,是单独存放的。

MyISAM表中的主键索引和非主键索引的区别,仅在于主键索引B+tree上的key必须符合主键的限制,非主键索引B+tree上的key只要符合相应字段的特性就可以了。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值