[MySQL] B+树索引

B+树是一种经典的数据结构,由平衡树和二叉查找树结合产生,它是为磁盘或其它直接存取辅助设备而设计的一种平衡查找树,在B+树中,所有的记录节点都是按键值大小顺序存放在同一层的叶节点中,叶节点间用指针相连,构成双向循环链表,非叶节点(根节点、枝节点)只存放键值,不存放实际数据。下面看一个2层B+树的例子:


保持树平衡主要是为了提高查询性能,但为了维护树的平衡,成本也是巨大的,当有数据插入或删除时,需采用拆分节点、左旋、右旋等方法。B+树因为其高扇出性,所以具有高平衡性,通常其高度都在2~3层,查询时可以有效减少IO次数。B+树索引可以分为聚集索引(clustered index)和非聚集索引(即辅助索引,secondary index)。


聚集索引

InnoDB表时索引组织表,即表中数据按主键B+树存放,叶子节点直接存放数据,每张表只能有一个聚集索引。


辅助索引

辅助索引(也称非聚集索引)是指叶节点不包含行的全部数据,叶节点除了包含键值之外,还包含一个书签连接,通过该书签再去找相应的行数据。下图显示了InnoDB存储引擎辅助索引和聚集索引的关系:

从上图中可以看出,辅助索引叶节点存放的是主键值,获得主键值后,再从聚集索引中查找整行数据。举个例子,如果在一颗高度为3的辅助索引中查找数据,首先从辅助索引中获得主键值(3次IO),接着从高度为3的聚集索引中查找以获得整行数据(3次IO),总共需6次IO。一个表上可以存在多个辅助索引。

索引组织表 VS 堆表

MyISAM中的表是以堆表的方式进行存储,堆表没有主键,因此没有聚集索引,辅助索引叶节点不是返回主键值,而是返回行标志符(ROWID),通过ROWID再去查找相应的行。
很显然,对于堆表来说,通过辅助索引访问更快(IO更少),但是如果在OLTP应用下,表中数据经常被修改,辅助索引中的ROWID可能需要经常更新,如果更新影响到物理地址的更改,这种开销比索引组织表要大得多。
因此,索引组织表还是堆表,这取决于你的应用,如果你的应用是OLAP,数据更新很少,堆表更好一些。


复合索引

复合索引是指对表上的多个列做索引,下面是一个复合索引的例子:
alter table t add key idx_a_b(a,b);

下图是B+树结构:



很显然,对于where a = xxx and b=xxx 这样的语句是可以使用这个复合索引的。现在看看对单个列的情况,where a = xxx也是可以使用该复合索引,因为a列在复合索引中也是有序的,但对于where b =xxx 这样的语句是无法使用该复合索引,因为它是无序的。
<think>嗯,用户想比较MySQL中B+索引和哈希索引的优缺点及适用场景。首先,我需要回忆一下这两种索引的基本结构和工作原理。B+索引是一种多路平衡查找,而哈希索引则是基于哈希表实现的。两者的结构差异会导致不同的查询性能表现。 根据引用[3],B+索引适合范围查询和排序,而哈希索引在等值查询上更快。那用户可能需要知道什么情况下选择哪种索引更好。比如,如果应用中有很多范围查询,B+更合适;如果是精确查找,哈希可能更高效。 接下来,要考虑它们的存储结构。引用[4]提到B+的内部节点只存索引,不存数据,这样减少存储空间。而哈希索引可能需要处理哈希冲突,这会影响性能。这点需要详细说明。 性能对比方面,B+的查询时间复杂度是O(log n),哈希则是平均O(1),但最坏情况可能退化为O(n)。但实际中哈希冲突管理好的话,还是高效的。不过B+在范围查询时效率更高,这也是为什么MySQL默认使用B+的原因之一。 使用场景的话,用户可能需要知道何时选择哈希索引。比如内存表或者需要快速查找键值的场景。而B+适用于需要排序、分组或者范围查找的情况,比如订单时间范围查询。 另外,需要提到哈希索引不支持部分索引和覆盖索引,而B+支持,这点在优化查询时很重要。例如,如果查询只需要索引列,B+可以直接返回数据,而哈希需要回表。 还要注意MySQL的存储引擎支持情况,比如InnoDB的自适应哈希索引功能,用户可能不知道这个特性,可以解释一下。 最后,总结两者的优缺点,给出实际应用中的建议,帮助用户根据具体需求选择合适的索引类型。同时,确保回答结构清晰,分点明确,让用户容易理解。</think>### MySQL B+索引哈希索引对比分析 #### 一、核心区别 1. **数据结构** - **B+索引**:多路平衡搜索,数据存储在叶子节点,形成有序链表[^4] 例如:$$ \text{非叶节点}(K_1,K_2,...,K_n) \rightarrow \text{叶节点}(D_1,D_2,...,D_n) $$ - **哈希索引**:基于哈希表实现,通过哈希函数将键值映射到桶位置 2. **查询模式** - B+支持$范围查询$(如`BETWEEN`、`>`、`<`)和排序操作 - 哈希索引仅支持$等值查询$(`=`、`IN()`),无法处理范围查询[^3] #### 二、性能对比 | 维度 | B+索引 | 哈希索引 | |------------|--------------------------|-------------------------| | 查询时间复杂度 | $O(\log N)$ | 平均$O(1)$,最差$O(N)$ | | 范围查询效率 | 高(顺序访问叶子节点)[^4] | 不支持 | | 排序性能 | 天然有序 | 需要额外排序步骤 | | 索引维护成本 | 插入/删除需要平衡 | 哈希冲突需处理链式结构 | #### 三、存储特性 1. **B+优势** - 支持$覆盖索引$(索引包含查询字段) - 内部节点不存储数据,节省空间[^4] 例如:$$ \text{存储效率}=1-\frac{\text{数据节点数}}{\text{总节点数}} $$ - 支持多列联合索引的最左前缀匹配 2. **哈希局限** - 无法使用索引完成排序 - 不支持部分索引(如`LIKE 'prefix%'`) - 哈希冲突会降低性能 #### 四、典型应用场景 1. **优先选择B+** - 订单系统的日期范围查询 ```sql SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-06-30' ``` - 需要`ORDER BY`的报表查询 - 联合索引查询(如`(last_name, first_name)`) 2. **适合哈希索引** - 内存表(MEMORY引擎)的高速等值查询 ```sql SELECT session_data FROM user_sessions WHERE session_id = 'a1b2c3' ``` - 数据仓库的精确匹配ETL过程 - InnoDB自适应哈希索引(自动缓存热点数据)[^1] #### 五、选择建议 1. **OLTP系统**:90%以上使用B+索引 2. **OLAP系统**:在明确只有点查询时考虑哈希索引 3. **混合负载**:使用InnoDB自适应哈希索引,自动平衡两者优势
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值