当前位置：首页 > java >正文

MySQL：索引

java 2025/7/31 21:21:24

索引我们用数组的时候，可以随机访问很方便

在数据库中也是一样数据库存储数据的地方是存储引擎一班MySQL默认就是InnoDB存储引擎

那么索引就是数据库的存储引擎的用来快速查找的一种数据结构

那么什么结构能够快速查找呢？哈希表二叉搜索树全文索引……

我们MySQL的索引和哈希表有关吗？

先来看看哈希表哈希表可以O(1)查找但是不支持范围查找和排序因为建立的时候就不是有序的而且哈希冲突多了的话维护成本会很高

这里我们的MySQL用的是B+树索引是由二叉搜索树优化而来的

数据库的数据再磁盘里我们平时写算法题的这些数据存在内存里

内存访问速度比磁盘快的多

所以我们尽可能保证访问磁盘要少一点所以用到了多叉的一个结构

越多叉树的层数就越少 io就少了但是同时我每个节点要存的东西就多了通常存“一页” 这样就不用去对磁盘进行io操作

B+树是由B树优化来的属于B树的一种

这是B树：

这是B+树：

在B+树中只有叶子节点才会存储数据非叶子节点不存的每一层下降都会把上一层的索引存好

最后会导致有一点内存冗余但是不多但是优点是已经排好序了这个时候再在叶子节点之间加上双向链表就访问非常方便排序和范围查找也就有了

B+搜索树由二叉搜索树优化而来：

二叉搜索树（左边小右边大）缺点是不平衡如果根节点是2 左边一个1 右边3456789这样就会导致高度差非常多这样的话查找速度就会大大减慢

这个时候平衡二叉树就来了平衡二叉树又叫B二叉树 AVL 红黑树等等都是

缺点就是要经常维护维护成本高

接下来是创建索引的语句：

create index ename_index on emp(ename);

desc select * from emp;或者explain select * form emp;就可以查看type类型

以下是ai对于索引的深度解析：

深入解析 MySQL 索引：从数据结构到实战优化

一、索引的本质与存储引擎

存储引擎与数据存储
MySQL 默认存储引擎 InnoDB 将数据（如emp表）持久化到磁盘，索引作为 “快速查找工具”，由存储引擎实现。不同引擎（如 MyISAM、InnoDB）的索引机制略有差异，InnoDB 的索引与数据紧密结合（聚集索引直接存数据）。
索引的核心目标
减少磁盘 I/O（内存访问速度是磁盘的千倍以上），通过高效数据结构（如 B + 树）将随机 I/O（全表扫描）转化为顺序 I/O（索引遍历），实现 O (log n) 的查询效率。

二、索引数据结构对比

哈希表（Hash Table）
- 优势：O (1) 单值查询（如WHERE ename='Lucy'）。
- 劣势：不支持范围查询（BETWEEN）、排序（ORDER BY），哈希冲突（如拉链法）增加维护成本，不适合数据库索引场景。
二叉搜索树（BST）与平衡树（AVL / 红黑树）
- BST：左小右大，但若数据有序插入（如 1,2,3,4），退化为链表（O (n) 查询），完全失效。
- 平衡树：通过旋转保持平衡（如 AVL 树高度差≤1），但树高仍随数据量增长（千万级需 20 层，磁盘 I/O 达 20 次），且节点仅存 2 个子节点（二叉），磁盘页利用率低（每节点存少量数据，导致多次 I/O）。
B + 树（MySQL 索引核心）
- 多叉优化：每个节点（磁盘页，默认 16KB）存多个关键字（如 InnoDB 每页存约 1000 个索引键），树高≤4（千万级数据只需 4 次 I/O），最大化磁盘页利用率。
- 叶子节点特性：
  - 仅叶子节点存数据（聚集索引）或主键（辅助索引），非叶子节点仅存索引键，压缩空间。
  - 叶子节点通过双向链表连接，天然有序，支持高效范围查询（如ORDER BY sal DESC直接遍历链表）。
- 对比 B 树：B 树非叶子节点存数据，范围查询需回溯，B + 树更优（叶子节点链表直接遍历，无需回溯）。

三、B + 树索引的工作原理

结构示意图
- 非叶子节点：存索引键（如ename值）+ 子节点指针，指导查询路径（如查找ename='Lucy'，从根节点逐层定位到叶子节点）。
- 叶子节点：存完整数据（聚集索引，如emp表行数据）或主键（辅助索引，如ename_index存empno，需回表查询完整数据）。
查询流程
- 单值查询：SELECT * FROM emp WHERE ename='Lucy' → 沿 B + 树快速定位叶子节点（O (log n)），直接获取数据（聚集索引）或主键（辅助索引需回表）。
- 范围查询：SELECT * FROM emp WHERE sal BETWEEN 5000 AND 10000 → 定位起始叶子节点，沿链表遍历所有符合条件的节点（顺序 I/O，高效）。

四、索引的创建与使用

创建索引

sql

-- 单字段索引
CREATE INDEX ename_index ON emp(ename);
-- 组合索引（最左匹配，如deptno=10 AND sal>5000可命中）
CREATE INDEX idx_dept_sal ON emp(deptno, sal);
-- 唯一索引（保证数据唯一性，如主键）
ALTER TABLE emp ADD PRIMARY KEY (empno);

查看索引与执行计划

sql

-- 查看索引状态
SHOW INDEX FROM emp;
-- 分析查询计划（查看type：const/ref/range为优，ALL为全表扫描）
EXPLAIN SELECT * FROM emp WHERE ename='Lucy';

优化建议
- 主键选择：使用自增INT（如AUTO_INCREMENT），插入时顺序写入 B + 树叶子节点，减少分裂，提升写入性能。
- 索引覆盖：设计索引包含查询所需字段（如SELECT ename FROM emp WHERE ename='Lucy'，ename_index已覆盖，无需回表）。
- 避免失效：
  - 不使用OR无索引条件（如WHERE ename='A' OR sal>5000，需两边都有索引）。
  - 避免函数操作（如WHERE UPPER(ename)='A'，改用前缀索引ename(1)）。

五、总结：为何 MySQL 选择 B + 树？

磁盘友好：多叉结构减少树高，降低 I/O 次数（4 次 vs20 次），每页存大量数据，提升磁盘利用率。
范围查询高效：叶子节点链表天然支持排序和范围遍历，无需额外排序操作（如 Filesort）。
平衡与维护：自动分裂 / 合并节点，保证树平衡，维护成本低于平衡二叉树。

通过 B + 树，MySQL 实现了高效的索引机制，支撑亿级数据的快速检索。掌握其原理，可优化索引设计（如组合索引、前缀索引），显著提升数据库性能。

代码示例（索引实战）：

sql

-- 创建组合索引，优化范围查询
CREATE INDEX idx_sal_dept ON emp(sal, deptno);-- 利用索引的范围查询（type: range）
EXPLAIN SELECT * FROM emp 
WHERE sal BETWEEN 5000 AND 10000 
AND deptno=10;-- 查看索引使用情况
SHOW STATUS LIKE 'Handler_read%'; -- 高Handler_read_rnd表示全表扫描多，需优化索引

此内容系统解析了 MySQL 索引的底层逻辑与实战优化，适合作为技术博客分享，帮助读者深入理解数据库性能的核心机制。

查看全文

http://www.xdnf.cn/news/8865.html