MySQL 索引

索引基础概念

1. 什么是索引

索引是一种能帮助 MySQL 提高查询效率的数据结构，通过特定的存储组织方式，减少数据检索时的磁盘 IO 操作，加速查询过程。

　

2. 索引的优缺点

优点

  • 快速访问数据表中的特定信息，显著提升检索速度；

  • 创建唯一性索引，保障数据表中每一行数据的唯一性；

  • 加速表与表之间的关联查询（如 JOIN 操作）；

  • 优化分组（GROUP BY）和排序（ORDER BY）操作，减少相关计算时间。

缺点

  • 降低表的更新速度（包括 INSERT、UPDATE、DELETE 操作），因为更新数据时需同步维护索引结构；

  • 占用磁盘空间，索引会以独立的索引文件形式存储，数据量越大，索引占用空间越多。

索引的分类与创建

1. 逻辑分类

索引类型 核心特性 适用场景
主键索引 一张表仅能有一个，不允许重复、不允许为 NULL 表的主键字段（如 id），用于唯一标识数据行
唯一索引 数据列不允许重复，允许为 NULL，一张表可多个 需唯一标识但允许空值的字段（如身份证号、卡号）
普通索引 一张表可多个，一个索引可包含多字段，允许重复和 NULL 普通查询场景，无唯一性要求的字段（如用户名、商品名称）
全文索引 针对文本内容的高效关键词搜索 长文本字段的关键词检索（如文章内容、商品描述）

　

2. 物理分类

  • 聚集索引：默认是表的主键索引；若未显式指定主键，会选择第一个非 NULL 的唯一索引；若无，则使用 InnoDB 内置的 6 字节 ROWID 作为聚集索引。索引的逻辑顺序与数据物理存储顺序一致。

  • 非聚集索引：索引逻辑顺序与磁盘数据物理存储顺序不同，非主键字段创建的索引均为非聚集索引，一张表可有多個。

　

3. 索引创建脚本

-- 1. 创建主键索引
ALTER TABLE t ADD PRIMARY KEY (`id`);

-- 2. 创建唯一索引
ALTER TABLE t ADD UNIQUE (`username`);

-- 3. 创建普通索引
ALTER TABLE t ADD INDEX index_name (`username`);

-- 4. 创建全文索引
ALTER TABLE t ADD FULLTEXT (`username`);

-- 5. 创建前缀索引（如手机号前6位）
ALTER TABLE t ADD INDEX index_phone (phone(6));
-- 或
CREATE INDEX index_phone ON t(phone(6));

索引核心特性与查询规则

1. 关键索引对比

主键索引 vs 唯一索引

对比维度 主键索引 唯一索引
唯一性 不可重复 不可重复
空值允许 不允许 允许
数量限制 一张表仅1个 一张表可多个
查询性能 更高（无需回表） 略低（可能需回表）

普通索引 vs 唯一索引（性能差异）

  • 查询操作：性能相近，均通过索引树检索；

  • 更新操作：唯一索引更慢，需先将数据读入内存进行唯一性校验。

　

2. 索引查询相关核心概念

回表查询

普通索引查询时，先通过普通索引树获取主键值，再到聚集索引树（主键索引树）查询完整数据的过程，称为回表查询。

示例：SELECT * FROM t WHERE f=3（f 为普通索引），需先查 f 索引树得 id，再查 id 索引树得完整数据。

最左匹配原则（联合索引核心）

联合索引（复合索引）遵循“最左前缀匹配”，即索引以最左边字段为起点，连续匹配有效，遇到范围查询（>、<、BETWEEN、LIKE）则停止匹配。

示例：联合索引 key(a,b,c) 的有效匹配场景：

  • 有效：WHERE a=1、WHERE a=1 AND b=2、WHERE a=1 AND b=2 AND c=3、WHERE a=3 AND b LIKE 'xx%'；

  • 无效：WHERE b=1、WHERE c=1、WHERE a=1 AND c=3（仅匹配 a 索引）。

索引生效条件

  • 避免在索引列上进行计算（如 f/2=100 需改为 f=200）；

  • 避免索引列使用函数（如 YEAR(date)>2018 不会走索引）；

  • LIKE 查询需避免前缀通配符（%A%、%A 不生效，A% 生效）；

  • NULL 值处理：MySQL 5.6+ InnoDB 支持 NULL 列索引，但建议设置 NOT NULL 并指定默认值，提升兼容性。

特殊查询的索引优化

  • LIKE %abc 生效方案：使用 REVERSE() 函数创建函数索引，示例： 
    SELECT * FROM t WHERE REVERSE(f) LIKE REVERSE('%abc');
  • 多值 OR 查询优化：避免 OR 导致全表扫描，改用 UNION，示例： 
    -- 优化前（可能全表扫描）
SELECT * FROM t WHERE num=10 OR num=20;
-- 优化后（走索引）
SELECT * FROM t WHERE num=10 UNION SELECT * FROM t WHERE num=20;

　

3. 索引查询相关操作

  1. 查询表的所有索引：SHOW INDEX FROM 表名;

  2. 强行指定查询索引：SELECT * FROM t FORCE INDEX(索引名);

  3. 索引列数量限制：MySQL 最多支持 16 个索引列。

特殊索引类型：前缀索引

定义

前缀索引（局部索引）：仅对字段的前 N 个字符创建索引（如身份证前 10 位、手机号前 6 位）。

优缺点

1. 优点

减小索引文件大小，提升索引查询速度（减少磁盘 IO）。

2. 缺点

  • 无法在 ORDER BY 或 GROUP BY 中触发；

  • 不能作为覆盖索引使用。

适用场景

  • 字符串字段较长（如长度超过 20）；

  • 字段前 N 个字符已具备较高区分度（即前 N 位开始不重复）。

索引底层原理

1. 存储相关基础概念

页

计算机管理存储器的逻辑块，主存与磁盘以“页”为单位交换数据。InnoDB 将索引节点大小设为一页（默认 16KB），确保一次磁盘 IO 即可载入一个节点，减少 IO 开销。

索引存储算法

算法类型 核心原理 优缺点 适用场景
哈希存储法 键值对存储，通过哈希值定位数据，冲突时用链表解决 查询快，无顺序，区间查询差 单值快速查询
有序数组存储法 数据按顺序存储，二分法查询 查询快，更新效率极低 静态数据（无更新）
搜索树 树形结构存储（如 B+ 树） 查询、更新效率均衡 动态数据（数据库核心选择）

　

2. InnoDB 索引存储结构：B+ 树

为何选择 B+ 树（而非 B 树、Hash、红黑树、二叉树）

数据结构 核心缺陷
B 树 非叶子节点存储数据，扇出（指针数量）少，树高增加，IO 次数增多
Hash 无顺序，区间查询效率极低，IO 复杂度高
二叉树 高度不均匀，查找效率依赖数据分布，IO 代价高
红黑树 数据量增加时树高快速增长，IO 开销大

B+ 树的优势

  • 非叶子节点仅存储索引指针，扇出大，树高低（通常 2-4 层），减少磁盘 IO；

  • 叶子节点存储完整数据，且通过指针串联，支持高效区间查询（如查询 18-49 区间数据，找到 18 后可顺指针遍历）；

  • 所有数据集中在叶子节点，查询效率稳定。

　

3. 索引基数与扫描行数

1. 索引基数（Cardinality）

索引列中不同值的数量，用于 MySQL 判断索引选择性（区分度）。可通过 SHOW INDEX FROM 表名; 查询。

2. 扫描行数估算

MySQL 通过“采样统计”获取索引基数（非精确值）：InnoDB 默认采样 N 个数据页，统计页中不同值的平均值，再推算整体基数，以此估算扫描行数。

索引优化实践

1. 优化器选择索引的影响因素

优化器以“执行代价最小”为目标，核心参考因素：

  1. 扫描行数（越少代价越低）；

  2. 是否需要创建临时表；

  3. 是否需要排序操作。

　

2. 索引失效与解决方案

索引失效常见原因

  • 索引列参与计算或函数操作；

  • LIKE 查询前缀含通配符；

  • OR 语句连接非索引列；

  • 联合索引不满足最左匹配原则；

  • MySQL 优化器错选索引。

解决方案

  • 避免索引列计算/函数操作，改写 SQL；

  • 调整 LIKE 查询格式，或使用函数索引（如 REVERSE）；

  • OR 改 UNION，确保各条件字段均为索引列；

  • 联合索引字段顺序与查询条件匹配；

  • 错选索引处理：删除无效索引、使用 FORCE INDEX 指定索引、改写 SQL 引导优化器。

　

3. 特殊场景索引优化

身份证字段索引优化

  • 问题：身份证前 6 位为地区码，重复率高，前缀索引区分度低；

  • 方案：将身份证倒序存储（如 REVERSE(id_card)），再创建前缀索引（如前 6 位），提升区分度。

覆盖索引优化

联合索引可实现覆盖查询，避免回表。示例：联合索引 key(a,b,c)，查询 SELECT a,b,c FROM t WHERE a=1 AND b=2，无需回表，直接从索引获取数据，减少 IO。

相关文章