MySQL 索引优化指南

索引的重要性

没有索引的查询如同在没有目录的书里找内容——只能逐页翻找。合适的索引能让查询速度提升百倍甚至千倍。

索引类型

1. B-Tree 索引（最常用）

适用于：等值查询、范围查询、排序

CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age_name ON users(age, name);  -- 复合索引

2. Hash 索引

适用于：精确匹配，不支持范围查询

CREATE INDEX idx_hash USING HASH ON users(email);

3. 全文索引

适用于：文本搜索

CREATE FULLTEXT INDEX idx_content ON articles(content);
SELECT * FROM articles WHERE MATCH(content) AGAINST("keyword");

索引优化原则

最左前缀原则

复合索引 (a, b, c) 的使用规则：

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

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

覆盖索引

查询字段都在索引中，无需回表：

-- 索引：idx_age_name (age, name)
SELECT age, name FROM users WHERE age = 25;  -- 覆盖索引，速度快
SELECT * FROM users WHERE age = 25;          -- 需要回表

常见优化场景

场景 1：避免函数操作

-- ❌ 索引失效
WHERE YEAR(create_time) = 2026
WHERE LEFT(name, 3) = "abc"

-- ✅ 索引有效
WHERE create_time >= "2026-01-01" AND create_time < "2027-01-01"
WHERE name LIKE "abc%"

场景 2：避免类型转换

-- ❌ 索引失效（字符串字段用数字比较）
WHERE phone = 13800138000

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

场景 3：OR 条件优化

-- ❌ 可能全表扫描
WHERE age = 20 OR name = "John"

-- ✅ 使用 UNION
SELECT * FROM users WHERE age = 20
UNION
SELECT * FROM users WHERE name = "John";

场景 4：LIMIT 优化

-- ❌ 深度分页慢
SELECT * FROM orders LIMIT 100000, 20;

-- ✅ 使用子查询或游标
SELECT * FROM orders 
WHERE id > (SELECT id FROM orders LIMIT 100000, 1) 
LIMIT 20;

EXPLAIN 分析

EXPLAIN SELECT * FROM users WHERE age = 25;

关键字段说明：

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

索引维护

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

-- 分析表
ANALYZE TABLE users;

-- 优化表
OPTIMIZE TABLE users;

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

何时不创建索引

1. 表数据量小（<1000 行）
2. 频繁更新的字段
3. 区分度低的字段（如性别）
4. 很少用于查询的字段

总结

索引是双刃剑：加速查询，但降低写入性能。根据实际查询模式设计索引，定期分析优化，才能发挥最大价值。