MySQL: 数据库索引深度解析：B树与哈希索引的结构、应用与优化策略

关注

原创最新推荐文章于 2026-03-06 04:23:03 发布 1.7k 阅读 AI 写同款· GEO 优化 ›

数据库索引 - B树目录 1. 前言 1.1 B树、B+树出现原因？ 1.2 一种新的查找树结构——多路查找树 2. 多路查找树 —— 平衡树—— B树查询插入删除 3、B+树 1. 前言参考：https://blog.youkuaiyun.com/z_ryan/article/details/79685072 二叉查找树的查找时间复杂度为O(log(2,N))，其查找效率已经足够高了，那为什么还有Ｂ树和Ｂ＋树的出现呢？难道它两的时间复杂度比二叉查找树还小吗？ＩＯ操作的效率很低，那么，当在大量数据存. 阅读详情

索引的核心作用与适用场景

1 ) 核心价值

索引本质是存储引擎的寻址导航器，通过减少数据扫描量提升查询效率
数据量少时（可完全缓存至内存），全表扫描性能尚可；
当数据量激增且查询频率升高时，索引性能优势呈指数级增长

2 ) 常见误区

极端一：仅主键索引，无其他索引 → 查询效率低下
极端二：每列均建索引 → 写操作性能骤降，优化器决策成本激增
正确策略：仅在高频查询列和关联字段建立精准索引

这两种策略均会损害性能，仅在正确列建立正确索引才能提升数据库处理能力
核心原则：在正确的列上建立精确匹配查询需求的索引，才能提升数据库性能

MySQL索引类型及存储引擎支持

索引在存储引擎层实现，不同引擎支持类型不同：

索引类型	支持引擎	特点
B树索引	InnoDB、MyISAM	默认索引类型，适用范围广
哈希索引	Memory（默认）、InnoDB（自适应）	仅支持等值查询，内存优化

注：InnoDB的自适应哈希索引由引擎自动管理，无需手动创建

B树索引的机制与实战应用

1 ) 存储结构与原理

B树索引（B+树实现）是最常见的索引类型，其结构特点如下：

存储结构：
- 叶子节点包含指向下一叶子节点的指针，支持高效遍历
  - 叶子节点存储数据指针（MyISAM：物理地址；InnoDB：主键值）
  - 非叶节点存储键值与子节点指针
- 平衡树结构：所有叶子节点到根节点的距离相同
- 有序存储：键值按顺序存放于同一层叶子节点，并通过指针双向链接
  - 顺序存储特性：支持高效范围查询（如 WHERE order_id > 1000）
  - 所有键值按顺序存储于同一层叶子节点，形成平衡查找树（所有叶子到根的距离相同）
- 不同存储引擎实现差异：
  - MyISAM：叶子节点存储数据物理地址
  - InnoDB：叶子节点存储主键值（非物理地址）
核心优势：
- 减少磁盘扫描：索引体积远小于数据，存储引擎从根节点逐层搜索，避免全表扫描
- 范围查询高效：顺序存储特性天然适合范围查询（如 WHERE orderSN > '1000'）
- 覆盖索引优化：若查询仅需索引字段，无需访问数据行（例：SELECT indexed_col FROM table）
```
-- 创建联合索引示例 
CREATE INDEX idx_orders ON orders (orderSN, orderDate);
```
查找流程:

2 ) 适用场景

场景类型	示例SQL	说明
全值匹配	`SELECT * FROM orders WHERE orderSN = '9876432119900';`	匹配索引所有列
最左前缀匹配	`SELECT * FROM orders WHERE orderSN = '9876432119900';`（联合索引`(orderSN, orderDate)`）	仅需匹配联合索引的第一列
列前缀匹配	`SELECT * FROM orders WHERE orderSN LIKE '9876%';`	匹配列的开头部分
范围查询	`SELECT * FROM orders WHERE orderSN > '987600000000' AND orderSN < '987699999999';`	B+树有序特性支持高效范围扫描
精确左列+范围右列	`SELECT * FROM orders WHERE orderSN = '9876432119900' AND orderDate BETWEEN '2023-01-01' AND '2023-12-31';`	联合索引中左列精确匹配，右列范围查询
覆盖索引	`SELECT orderSN, orderDate FROM orders WHERE orderSN = '9876432119900';`	仅访问索引即可获取数据，避免回表
排序优化	`ORDER BY order_sn`（索引列排序）	-

3 ) B树索引的限制

以下场景无法利用B树索引：

未使用最左列：
联合索引 (orderDate, userName, userPhone) 中，仅使用 userPhone 查询无效
跳过左侧列：
同上索引，使用 orderDate 和 userPhone 但跳过 userName 时，仅 orderDate 生效
范围查询后列失效：
若对索引中某列使用范围查询（如 WHERE orderDate > '2023-01-01'），其右侧所有列无法使用索引
非等值查询：
NOT IN、!= 等操作无法触发索引

优化器决策：当索引命中数据超过表总量70%时，优化器可能选择全表扫描而非索引

哈希索引的机制与适用边界

哈希索引基于哈希表实现，仅支持精确等值匹配（如 WHERE id = 123）。其特性如下：

1 ) 存储原理：

对索引所有列计算哈希码（如MD5），存储哈希值+数据行指针
存储紧凑，查找速度极快（O(1) 时间复杂度），适合等值查询（= 或 IN）

2 ) 适用引擎：

Memory引擎：默认哈希索引
InnoDB：自适应哈希索引（无需手动创建）

查找流程：

SELECT * FROM users WHERE hash_code = MD5('user_id=123'); -- 伪代码示例

显示制定哈希索引

-- Memory引擎表显式指定哈希索引 
CREATE TABLE user_memory (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=MEMORY;
CREATE INDEX idx_hash_name USING HASH ON user_memory(name);

3 ) 优势与局限

特性	优势	局限
查询速度	等值查询极快（O(1)复杂度）	范围查询/模糊查询（LIKE）完全失效
存储效率	存储紧凑，内存占用低	哈希冲突时性能退化
适用列	高选择性列（如身份证号）	低选择性列（如性别）禁用

3 ）适用场景与限制

限制类型	原因
仅支持等值查询	哈希码无法支持范围查询（如 `BETWEEN`）或模糊匹配（如 `LIKE 'a%'`）。
二次读取数据	索引仅存哈希码和指针，需额外读取数据行。
无法用于排序	哈希值无序，不支持 `ORDER BY` 操作。
哈希冲突影响性能	高重复值列（如性别）会导致大量冲突，降低效率。
不支持部分列匹配	必须使用索引所有列（如联合索引需全字段匹配）。

选型建议：高唯一性列（如身份证号）适合哈希索引，低区分度列（如状态标志）避免使用

注：InnoDB的自适应哈希索引触发条件：当某B树索引被频繁访问时，引擎自动在内存中创建哈希索引优化查询

4 ) InnoDB自适应哈希索引

自动为频繁访问的B树索引构建哈希索引，无需手动创建
触发条件：索引命中频率超过阈值时自动启用

索引的收益与代价分析

1 ) 核心收益

减少数据扫描量：索引体积远小于数据，减少磁盘 I/O
避免排序临时表：B树索引天然有序，优化 ORDER BY/GROUP BY
随机I/O转顺序I/O：索引将离散数据访问转为顺序磁盘读取

2 ) 使用代价

写操作成本增加：
- 数据增删改需同步更新索引，InnoDB通过插入缓冲（Change Buffer）合并写操作优化
- 重要误区：索引会显著降低数据导入速度，快速导入需先删除非必要索引
- 每次 INSERT/UPDATE/DELETE 需更新索引（InnoDB 插入缓冲优化部分场景）
```
-- 数据导入优化：删除非必要索引  
ALTER TABLE orders DROP INDEX idx_order_date;  
```
查询优化器负担加重：
- 过多索引增加优化器选择成本，可能生成次优执行计划
数据导入优化：导入前删除非必要索引，完成后重建
优化器决策成本：过多索引增加查询计划分析时间，建议单表索引数 ≤ 5
关键结论：索引仅在减少查询开销 > 维护成本时有效

索引优化策略

选择性原则：
优先为高区分度列建索引（计算公式：COUNT(DISTINCT col)/COUNT(*)）。
覆盖索引优先：
频繁查询字段尽量纳入索引，避免回表查询。
前缀索引优化：
长文本字段使用前缀索引（如 INDEX (title(20))）。
联合索引左缀匹配：
按查询频率从左到右排列联合索引字段。

-- 联合索引优化示例：高频字段前置 
CREATE INDEX idx_user_optimized ON users (last_name, first_name, email);

InnoDB最佳实践：主键宜用自增整型，避免页分裂；二级索引存储主键值，需保证主键紧凑

索引优化实战示例

1 ) 联合索引设计

最左前缀原则：联合索引(a, b, c)仅对WHERE a=?、WHERE a=? AND b=?生效
避免冗余索引：已有(a, b)时，(a)为冗余索引

2 ) SQL索引创建规范

-- 联合索引（最左前缀优先）
CREATE INDEX idx_orders ON orders (order_sn, order_date);

-- 覆盖索引优化查询 
CREATE INDEX idx_cover ON orders (order_sn, product_id);

-- 查询仅访问索引  
SELECT order_sn, order_date FROM orders WHERE order_sn = '9876432119900';

2 ) 自适应哈希索引管理（InnoDB）

-- 查看自适应哈希索引状态  
SHOW ENGINE INNODB STATUS;  

-- 关键参数（需在my.cnf配置）  
innodb_adaptive_hash_index = ON     -- 启用自适应哈希  
innodb_adaptive_hash_index_parts = 8 -- 分区数（减少锁竞争）

3 ) NestJS 索引管理（TypeORM）

import { Entity, PrimaryGeneratedColumn, Column, Index } from 'typeorm';

@Entity('orders')
@Index(['orderSn', 'orderDate']) // 联合索引
export class Order {
  @PrimaryGeneratedColumn()
  id: number;

  @Index({ unique: true }) // 唯一索引
  @Column()
  orderSn: string;

  @Column()
  orderDate: Date;

  @Index('idx_product_id') // 单列索引 
  @Column()
  productId: number;
}

// 查询优化：强制使用覆盖索引  
const orders = await orderRepository  
  .createQueryBuilder('order')  
  .select(['order.orderSn', 'order.orderDate'])  
  .where('order.orderSN = :sn', { sn: '9876432119900' })  
  .useIndex('idx_cover') // 指定索引  
  .getMany();