程序员布欧

简体中文

English

简体中文

English

外观

索引类型与使用策略

约 1129 字大约 4 分钟

布欧-Lewyon

2026-05-15

首页 › MySQL › 索引与性能 › 索引类型与使用策略

MySQL 支持多种索引类型和创建方式。正确理解复合索引、最左前缀原则以及索引失效场景，是写出高效 SQL 的关键。

索引类型

类型说明适用场景
BTREEB+ Tree 索引（默认）大多数场景
HASH哈希索引（仅 Memory 引擎，或自适应哈希）等值查询
FULLTEXT全文索引大文本搜索
SPATIAL空间索引地理坐标数据

InnoDB 的索引默认为 BTREE。HASH 仅限 Memory 引擎的显式声明，InnoDB 的自适应哈希索引（Adaptive Hash Index, AHI）由内部自动管理。

索引的分类

-- 普通索引（不是唯一约束，允许重复值）
CREATE INDEX idx_name ON user(name);

-- 唯一索引（值不可重复）
CREATE UNIQUE INDEX idx_email ON user(email);

-- 主键索引（特殊的唯一 + 非空）
ALTER TABLE user ADD PRIMARY KEY (id);

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON article(body);

-- 复合索引（多列组合）
CREATE INDEX idx_dept_salary ON employee(dept_id, salary);

复合索引与最左前缀原则

复合索引按定义的列顺序建立 B+ Tree。查询只能使用索引中从最左侧开始的连续列。

-- 假设复合索引：idx_dept_salary (dept_id, salary, age)

-- ✅ 用到索引的 (dept_id) 部分
SELECT * FROM employee WHERE dept_id = 1;

-- ✅ 用到索引的 (dept_id, salary) 部分
SELECT * FROM employee WHERE dept_id = 1 AND salary > 15000;

-- ✅ 用到索引的 (dept_id, salary, age) 全部
SELECT * FROM employee WHERE dept_id = 1 AND salary = 15000 AND age = 28;

-- ❌ 无法使用索引（跳过了第一列）
SELECT * FROM employee WHERE salary = 15000;

-- ❌ 无法使用索引（跳过了中间列）
SELECT * FROM employee WHERE dept_id = 1 AND age = 28;
-- dept_id 可用索引过滤，但 age 不能直接利用复合索引的第二跳

最左前缀原则记忆：复合索引就像电话簿——按(姓氏, 名字)排序，只查名字没用。

覆盖索引（Covering Index）

当查询所需的所有列都在索引中时，MySQL 无需回表，这就是"覆盖索引"。

-- 索引 idx_dept_salary (dept_id, salary)

-- ❌ 需要回表（name 不在索引中）
SELECT name FROM employee WHERE dept_id = 1;

-- ✅ 覆盖索引（dept_id 和 salary 都在索引中）
SELECT dept_id, salary FROM employee WHERE dept_id = 1;

-- ✅ 也可以覆盖（count 直接用索引统计）
SELECT COUNT(*) FROM employee WHERE dept_id = 1;

Extra 字段为 Using index 表示使用了覆盖索引。

索引下推（Index Condition Pushdown, ICP）

MySQL 5.6+ 的优化：在存储引擎层使用索引过滤数据，减少回表次数。

-- 复合索引 (dept_id, salary)
-- 无 ICP：先按 dept_id 找到所有行，再回表后过滤 salary
-- 有 ICP：在索引层同时过滤 dept_id 和 salary，减少回表
SELECT * FROM employee WHERE dept_id = 1 AND salary LIKE '15%';

Extra 字段出现 Using index condition 表示 ICP 生效。

索引失效的常见场景

-- 1. 隐式类型转换
SELECT * FROM user WHERE phone = 13800138000;    -- phone 是 VARCHAR，等号右侧用了数字
-- MySQL 隐式将 phone 转为数字，相当于 CAST(phone AS SIGNED)，索引失效

-- 2. 对索引列使用函数
SELECT * FROM employee WHERE MONTH(join_date) = 5;
-- 应改为范围查询
SELECT * FROM employee WHERE join_date >= '2026-05-01' AND join_date < '2026-06-01';

-- 3. LIKE 以 % 开头
SELECT * FROM employee WHERE name LIKE '%七';   -- 索引失效
SELECT * FROM employee WHERE name LIKE '张%';   -- 索引可用

-- 4. 不等条件
SELECT * FROM employee WHERE salary <> 15000;   -- 索引降级

-- 5. OR 条件中有非索引列
SELECT * FROM employee WHERE dept_id = 1 OR name = '张三';   -- 如果 name 无索引，全表扫描

-- 6. 联合索引跳跃中间列（已在上文最左前缀中说明）

索引选择建议

小结

  • 索引类型：BTREE（默认）、UNIQUE、FULLTEXT、SPATIAL。
  • 复合索引遵循最左前缀原则：过滤条件必须从索引最左列开始连续使用。
  • 覆盖索引避免回表，Using index 是重要优化信号；索引下推（ICP）减少回表次数。
  • 索引失效场景：隐式类型转换、函数包裹、LIKE '%...'、不等条件、OR 含非索引列。

上一节：索引基础与 B+ Tree 下一节：EXPLAIN 解读

贡献者: 布欧-Lewyon