索引原理与应用

最后更新: 2026-01-22 作者: MySQL Team
页面目录

第五章：索引原理与应用

5.1 索引概述

什么是索引

索引是数据库中一种特殊的数据结构，用于加速数据检索，就像书籍的目录一样。

索引的优点：

  • 加速数据查询，减少扫描行数
  • 唯一索引保证数据唯一性
  • 加速 ORDER BY 和 GROUP BY

索引的缺点：

  • 占用磁盘空间
  • 增加写操作开销（INSERT/UPDATE/DELETE）
  • 过多索引反而降低性能

5.2 索引数据结构

B-Tree 索引（默认）

B-Tree 结构示意：
                    [30]
           /                \
      [10, 20]            [40, 50]
      /    \    \        /    \    \
   [5]  [15] [25]    [35]  [45] [55]

特点：
- 平衡多叉树
- 所有叶子节点在同一层
- 节点按key排序
- 查询复杂度 O(log n)

B-Tree 适用场景：

-- 全值匹配
SELECT * FROM users WHERE email = 'test@example.com';

-- 最左前缀匹配
SELECT * FROM users WHERE name = '张三';              -- 使用 idx_name
SELECT * FROM users WHERE name = '张三' AND age = 25;  -- 使用 idx_name_age

-- 范围查询
SELECT * FROM users WHERE age BETWEEN 18 AND 30;

-- ORDER BY 优化
SELECT * FROM users ORDER BY name;  -- 使用索引避免排序

Hash 索引

特点：

  • 基于哈希表实现
  • 查询复杂度 O(1)
  • 仅支持等值查询
  • 无法排序
  • 无法范围查询
-- MEMORY 存储引擎使用 HASH 索引
CREATE TABLE hash_table (
    id INT,
    name VARCHAR(50),
    INDEX idx_name (name) USING HASH
) ENGINE=MEMORY;

聚簇索引 vs 辅助索引

类型 说明 特点
聚簇索引 主键索引，叶子节点存储完整行数据 每个表只能有一个
辅助索引 非主键索引，叶子节点存储主键值 可以有多个 
聚簇索引结构：
┌─────────────────────────────────┐
│ 主键值 │ 行数据                   │
├─────────────────────────────────┤
│   1    │ id=1, name=张三, age=25  │
│   3    │ id=3, name=李四, age=30  │
│   5    │ id=5, name=王五, age=28  │
└─────────────────────────────────┘

辅助索引结构（以 name 为例）：
┌─────────────────┐
│ name │ 主键值    │
├─────────────────┤
│ 张三  │    1     │
│ 王五  │    5     │
│ 李四  │    3     │
└─────────────────┘

覆盖索引

-- 查询只需索引字段，无需回表
CREATE INDEX idx_name_age ON users(name, age);

-- 覆盖索引查询
SELECT name, age FROM users WHERE name = '张三';  -- 不需要回表

-- 非覆盖索引（需要回表）
SELECT * FROM users WHERE name = '张三';  -- 需要回表获取其他字段

5.3 索引分类

主键索引

-- 创建主键索引
ALTER TABLE users ADD PRIMARY KEY (id);

-- 删除主键索引
ALTER TABLE users DROP PRIMARY KEY;

唯一索引

-- 创建唯一索引
CREATE UNIQUE INDEX uk_email ON users(email);

-- 表创建时定义
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

普通索引

-- 创建普通索引
CREATE INDEX idx_name ON users(name);

-- 或
ALTER TABLE users ADD INDEX idx_name (name);

全文索引

-- 创建全文索引
CREATE FULLTEXT INDEX ft_content ON articles(title, content);

-- 搜索使用 MATCH ... AGAINST
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL 优化');

组合索引

-- 创建组合索引（最左前缀原则）
CREATE INDEX idx_name_age_status ON users(name, age, status);

-- 使用情况分析：
✅ SELECT * FROM users WHERE name = '张三';              -- 使用索引
✅ SELECT * FROM users WHERE name = '张三' AND age = 25;  -- 使用索引
✅ SELECT * FROM users WHERE name = '张三' AND age = 25 AND status = 1;  -- 使用索引
❌ SELECT * FROM users WHERE age = 25;                     -- 不使用索引
❌ SELECT * FROM users WHERE status = 1;                  -- 不使用索引
⚠️ SELECT * FROM users WHERE name = '张三' AND status = 1;  -- 只使用 name 部分

5.4 索引创建原则

应该创建索引的场景

场景 说明 示例
WHERE 条件 经常作为查询条件的字段 WHERE status = 1
JOIN 连接 经常用于连接的字段 ON orders.user_id = users.id
ORDER BY 经常排序的字段 ORDER BY created_at DESC
高选择性 选择性高的字段（区分度大） 身份证号 > 性别
主键/外键 表关联字段 FOREIGN KEY (user_id)

不应该创建索引的场景

场景 说明 示例
低选择性 重复值多 性别、状态
频繁更新 更新频繁的字段 最后修改时间
文本过长 TEXT、BLOB 字段 content、description
少量数据 表数据很少 测试表
不用于查询 不出现在 WHERE 中 存储过程的变量

索引设计建议

-- 1. 优先考虑 WHERE、JOIN、ORDER BY 使用的字段
-- 2. 使用短索引（减少 I/O）
CREATE INDEX idx_name ON users(name(20));  -- 只索引前20个字符

-- 3. 组合索引考虑列顺序（选择性高的放前面）
-- 4. 避免过多索引（增加维护成本）
-- 5. 定期分析表优化索引
ANALYZE TABLE users;

5.5 索引管理

查看索引

-- 查看表的所有索引
SHOW INDEX FROM users;

-- 或
SHOW CREATE TABLE users\G

创建索引

-- 方式1：CREATE INDEX
CREATE INDEX idx_name ON users(name);
CREATE UNIQUE INDEX uk_email ON users(email);
CREATE INDEX idx_composite ON users(name, age, status);

-- 方式2：ALTER TABLE
ALTER TABLE users ADD INDEX idx_name (name);
ALTER TABLE users ADD UNIQUE INDEX uk_email (email);
ALTER TABLE users ADD INDEX idx_composite (name, age);

-- 方式3：创建表时
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    INDEX idx_name (name),
    UNIQUE INDEX uk_email (email)
);

删除索引

DROP INDEX idx_name ON users;
DROP INDEX uk_email ON users;

-- 或使用 ALTER TABLE
ALTER TABLE users DROP INDEX idx_name;
ALTER TABLE users DROP INDEX uk_email;

重命名索引

-- MySQL 不支持直接重命名，需要先删除再创建
DROP INDEX old_name ON users;
CREATE INDEX new_name ON users(name);

5.6 索引优化技巧

前缀索引

-- 对长字符串创建前缀索引
ALTER TABLE articles ADD INDEX idx_title (title(50));
ALTER TABLE articles ADD INDEX idx_content (content(100));

-- 选择合适的前缀长度（区分度 > 0.9 较好）
SELECT 
    COUNT(DISTINCT LEFT(name, 10)) / COUNT(*) AS selectivity_10,
    COUNT(DISTINCT LEFT(name, 20)) / COUNT(*) AS selectivity_20,
    COUNT(DISTINCT LEFT(name, 30)) / COUNT(*) AS selectivity_30
FROM users;

索引下推（ICP）

-- 优化器自动使用 ICP
SET optimizer_switch = 'index_condition_pushdown=on';

-- 示例：查询 name LIKE '张%' AND age = 25
-- ICP 优化：在索引中先过滤 age，再回表
SELECT * FROM users 
WHERE name LIKE '张%' AND age = 25;

MRR（Multi-Range Read）优化

-- 优化范围查询和随机 I/O
SET optimizer_switch = 'mrr=on,mrr_cost_based=on';

-- 示例
SELECT * FROM users WHERE id IN (100, 50, 200);

5.7 SQL 提示（Optimizer Hints）

强制使用索引

-- 强制使用索引
SELECT * FROM users USE INDEX (idx_name) WHERE name = '张三';

-- 忽略索引
SELECT * FROM users IGNORE INDEX (idx_name) WHERE name = '张三';

-- 强制使用索引（MySQL 8.0+）
SELECT * FROM users FORCE INDEX (idx_name) WHERE name = '张三';

优化器提示

-- 禁止某种访问类型
SELECT * FROM users STRAIGHT_JOIN ... WHERE ...;

-- 指定连接顺序
SELECT /*+ JOIN_FIXED_ORDER() */ ...;

-- 指定 join_buffer_size
SELECT /*+ SET_VAR(join_buffer_size = 256M) */ ... FROM t1 JOIN t2;

5.8 索引失效场景

-- 1. 使用函数或运算
❌ SELECT * FROM users WHERE YEAR(created_at) = 2024;
✅ SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 2. 类型转换
❌ SELECT * FROM users WHERE id = '1';  -- id 是 INT，字符串 '1'
✅ SELECT * FROM users WHERE id = 1;

-- 3. LIKE 开头通配符
❌ SELECT * FROM users WHERE name LIKE '%三%';
❌ SELECT * FROM users WHERE name LIKE '%三';
✅ SELECT * FROM users WHERE name LIKE '三%';

-- 4. OR 条件
❌ SELECT * FROM users WHERE name = '张三' OR age = 25;
✅ SELECT * FROM users WHERE name = '张三' UNION SELECT * FROM users WHERE age = 25;

-- 5. NOT EQUAL
❌ SELECT * FROM users WHERE status != 0;
✅ 考虑其他方案或使用覆盖索引

-- 6. IS NULL / IS NOT NULL
⚠️ 可能不使用索引（取决于数据分布）

-- 7. 组合索引不使用最左前缀
❌ SELECT * FROM users WHERE age = 25 AND status = 1;  -- 未使用 name

5.9 索引分析与维护

ANALYZE TABLE

-- 分析表（更新索引统计信息）
ANALYZE TABLE users;

CHECK TABLE

-- 检查表是否有错误
CHECK TABLE users;

OPTIMIZE TABLE

-- 优化表（整理碎片、回收空间）
OPTIMIZE TABLE users;

CHECKSUM TABLE

-- 校验表数据一致性
CHECKSUM TABLE users;

5.10 索引实战案例

案例1：用户表优化

-- 创建用户表并添加索引
CREATE TABLE users (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    username    VARCHAR(50) NOT NULL,
    email       VARCHAR(100) NOT NULL,
    phone       VARCHAR(20),
    status      TINYINT DEFAULT 1,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email),
    INDEX idx_status (status),
    INDEX idx_created (created_at),
    INDEX idx_phone (phone)
);

-- 查询优化示例
-- 场景1：登录查询
SELECT * FROM users WHERE username = 'xxx' OR email = 'xxx';
-- 建议：添加组合索引
ALTER TABLE users ADD INDEX idx_login (username, email);

-- 场景2：状态筛选+排序
SELECT * FROM users WHERE status = 1 ORDER BY created_at DESC LIMIT 100;
-- 建议：添加组合索引
ALTER TABLE users ADD INDEX idx_status_created (status, created_at DESC);

案例2：订单表优化

CREATE TABLE orders (
    id              INT PRIMARY KEY AUTO_INCREMENT,
    order_no        VARCHAR(32) NOT NULL,
    user_id         INT NOT NULL,
    status          TINYINT DEFAULT 0,
    total_amount    DECIMAL(10,2),
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_order_no (order_no),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created (created_at),
    INDEX idx_composite (user_id, status, created_at)
);

-- 查询优化：用户订单列表
SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY created_at DESC 
LIMIT 20;
-- 使用 idx_composite 索引

-- 查询优化：待处理订单统计
SELECT COUNT(*) FROM orders WHERE status = 0;
-- 使用 idx_status 索引

5.11 本章小结

✅ 理解 B-Tree 索引原理和聚簇/辅助索引区别
✅ 掌握各类索引创建方法：主键、唯一、普通、全文、组合
✅ 遵循索引设计原则，避免索引滥用
✅ 理解最左前缀原则和覆盖索引
✅ 识别索引失效场景并优化查询
✅ 定期维护索引：ANALYZE、OPTIMIZE

📖 下章预告：视图与存储过程