MySQL 索引优化完全指南

索引是数据库性能优化的核心。正确设计索引可以让查询速度提升 100 倍以上。

索引原理

为什么需要索引？

无索引查询： 全表扫描，O(n) 复杂度

-- 100 万数据，平均查询 50 万次
SELECT * FROM users WHERE email = "test@example.com";

有索引查询： B+ 树查找，O(log n) 复杂度

-- 100 万数据，只需约 20 次比较
SELECT * FROM users WHERE email = "test@example.com";

B+ 树结构

        [根节点]
       /    |    \
   [内部节点] [内部节点] [内部节点]
   /  \      /  \      /  \
[叶子][叶子][叶子][叶子][叶子][叶子]

• 数据存储在叶子节点
• 叶子节点用链表连接（范围查询快）
• 树高度通常 2-4 层

索引类型

1. 普通索引

CREATE INDEX idx_email ON users(email);

2. 唯一索引

CREATE UNIQUE INDEX idx_email ON users(email);
-- 或
ALTER TABLE users ADD UNIQUE (email);

3. 复合索引

CREATE INDEX idx_name_age ON users(name, age);

最左前缀原则：

✅ WHERE name = "张三"           -- 使用索引
✅ WHERE name = "张三" AND age = 25  -- 使用索引
❌ WHERE age = 25                -- 不使用索引

4. 覆盖索引

-- 索引包含查询的所有字段
CREATE INDEX idx_email_name ON users(email, name);

-- 只需查索引，不回表
SELECT email, name FROM users WHERE email = "test@example.com";

索引优化实战

场景 1：WHERE 条件优化

-- ❌ 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- ✅ 索引有效
SELECT * FROM users 
WHERE created_at >= "2024-01-01" 
  AND created_at < "2025-01-01";

场景 2：LIKE 查询

-- ❌ 索引失效（前缀通配符）
SELECT * FROM users WHERE name LIKE "%张%";

-- ✅ 索引有效（前缀匹配）
SELECT * FROM users WHERE name LIKE "张%";

-- ✅ 使用全文索引
SELECT * FROM users WHERE MATCH(name) AGAINST("张三");

场景 3：OR 条件

-- ❌ 可能全表扫描
SELECT * FROM users WHERE email = "a@test.com" OR phone = "123";

-- ✅ UNION 优化
SELECT * FROM users WHERE email = "a@test.com"
UNION
SELECT * FROM users WHERE phone = "123";

场景 4：ORDER BY 优化

-- 创建复合索引
CREATE INDEX idx_created_status ON orders(created_at, status);

-- ✅ 使用索引排序
SELECT * FROM orders 
WHERE status = 1 
ORDER BY created_at DESC;

分析查询性能

EXPLAIN 命令

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

关键字段：

字段	说明
type	访问类型（system>const>eq_ref>ref>range>index>ALL）
key	实际使用的索引
rows	预计扫描行数
Extra	额外信息

优化目标：

• type 至少达到 range
• Extra 出现 "Using index"（覆盖索引）
• 避免 "Using filesort" 和 "Using temporary"

慢查询日志

-- 开启慢查询
SET GLOBAL slow_query_log = "ON";
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录

-- 查看慢查询
SHOW VARIABLES LIKE "slow_query_log_file";

索引维护

查看索引

SHOW INDEX FROM users;

删除无用索引

DROP INDEX idx_old ON users;

索引碎片整理

OPTIMIZE TABLE users;

常见误区

1. 索引越多越好？

错！ 索引有代价：

• 占用存储空间
• 降低写入性能（每次 INSERT/UPDATE 都要更新索引）

建议： 单表索引不超过 5 个

2. 字段都要索引？

错！ 以下情况不需要索引：

• 区分度低的字段（性别、状态）
• 频繁更新的字段
• 小表（< 1000 行）

3. 主键一定要自增 ID？

推荐！ 自增主键优势：

• 顺序写入，减少页分裂
• 叶子节点有序，范围查询快
• 占用空间小

实战案例

问题： 订单查询慢（3 秒）

表结构：

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,
  status TINYINT,
  created_at DATETIME,
  amount DECIMAL
);

慢查询：

SELECT * FROM orders 
WHERE user_id = 123 
  AND status = 1 
ORDER BY created_at DESC 
LIMIT 20;

优化方案：

CREATE INDEX idx_user_status_created 
ON orders(user_id, status, created_at);

结果： 查询时间 3 秒 → 0.03 秒（100 倍提升）

索引优化是数据库性能调优的核心技能，值得深入掌握！