数据库索引优化实战

为什么需要索引？

没有索引的查询 = 全表扫描，数据量大时性能灾难。

-- 无索引：O(n) 复杂度
SELECT * FROM users WHERE email = "test@example.com";
-- 100 万数据 = 100 万次比较

-- 有索引：O(log n) 复杂度
-- 100 万数据 = 约 20 次比较

B+ 树索引原理

        [50]
       /    \
    [20]    [80]
   /   \   /   \
 [10][30][60][90]

• 多路平衡查找树
• 数据都在叶子节点
• 叶子节点有序链表连接
• 适合范围查询

索引类型

1. 主键索引（PRIMARY KEY）

CREATE TABLE users (
  id INT PRIMARY KEY,  -- 自动创建聚簇索引
  name VARCHAR(100)
);

2. 唯一索引（UNIQUE）

CREATE UNIQUE INDEX idx_email ON users(email);

3. 普通索引（INDEX）

CREATE INDEX idx_name ON users(name);

4. 复合索引（COMPOSITE）

CREATE INDEX idx_name_age ON users(name, age);
-- 最左前缀原则：先 name 后 age

5. 覆盖索引

-- 索引包含查询所需所有字段
CREATE INDEX idx_email_name ON users(email, name);
SELECT email, name FROM users WHERE email = "test@example.com";
-- 无需回表

索引优化原则

1. 选择性高的列适合建索引

-- ✅ 好：性别只有 2 个值，选择性低，不适合
-- ❌ 差：email 几乎唯一，选择性高，适合
CREATE INDEX idx_gender ON users(gender);  -- 效果差
CREATE INDEX idx_email ON users(email);    -- 效果好

2. 最左前缀原则

CREATE INDEX idx_a_b_c ON users(a, b, c);

-- ✅ 可以使用索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- ❌ 无法使用索引
WHERE b = 2
WHERE c = 3
WHERE a = 1 AND c = 3  -- 跳过 b，c 无法使用索引

3. 避免在索引列上做运算

-- ❌ 索引失效
WHERE YEAR(created_at) = 2024
WHERE price * 2 > 100
WHERE name LIKE "%john%"  -- 前缀通配符

-- ✅ 索引有效
WHERE created_at >= "2024-01-01" AND created_at < "2025-01-01"
WHERE price > 50
WHERE name LIKE "john%"  -- 前缀匹配

4. 避免类型隐式转换

-- ❌ 索引失效（字符串 vs 数字）
WHERE phone = 13800138000

-- ✅ 索引有效
WHERE phone = "13800138000"

EXPLAIN 分析查询

EXPLAIN SELECT * FROM users WHERE email = "test@example.com";

关键字段：

• type：访问类型（system > const > eq_ref > ref > range > index > ALL）
• key：实际使用的索引
• rows：预估扫描行数
• Extra：额外信息（Using index = 覆盖索引，Using filesort = 需要优化）

常见优化场景

场景 1：分页优化

-- ❌ 慢：深度分页
SELECT * FROM users ORDER BY id LIMIT 100000, 20;

-- ✅ 快：子查询优化
SELECT * FROM users 
WHERE id > (SELECT id FROM users ORDER BY id LIMIT 100000, 1)
ORDER BY id LIMIT 20;

场景 2：ORDER BY 优化

-- 确保 ORDER BY 列有索引
CREATE INDEX idx_created_at ON users(created_at);
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

场景 3：JOIN 优化

-- 确保 JOIN 条件列有索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_id ON users(id);

SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

索引维护

-- 查看索引使用情况
SHOW INDEX FROM users;

-- 删除无用索引
DROP INDEX idx_unused ON users;

-- 分析表
ANALYZE TABLE users;

结语

索引是数据库性能优化的利器，但不是银弹。合理使用索引，定期分析慢查询，才能让数据库保持高性能。