MySQL 索引优化：让查询速度提升 100 倍

为什么需要索引？

没有索引：全表扫描，O(n) 复杂度 有索引：B+ 树查找，O(log n) 复杂度

100 万数据：全表扫描 100 万次 vs 索引查找 20 次

索引类型

1. 主键索引（PRIMARY KEY）

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
);

• 唯一且非空
• 每张表只能有一个
• 推荐使用自增 ID

2. 唯一索引（UNIQUE）

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

• 唯一但可空
• 适合邮箱、手机号等

3. 普通索引（INDEX）

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

• 允许重复
• 最常用的索引类型

4. 复合索引（联合索引）

CREATE INDEX idx_name_age ON users(name, age);

最左前缀原则：查询条件必须从最左列开始

-- ✅ 使用索引
SELECT * FROM users WHERE name = "张三" AND age = 25;
SELECT * FROM users WHERE name = "张三";

-- ❌ 不使用索引
SELECT * FROM users WHERE age = 25;

5. 覆盖索引

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

-- 查询只使用索引，不查表
SELECT name, email FROM users WHERE name = "张三";

EXPLAIN 显示：Extra = "Using index"

EXPLAIN 分析查询

EXPLAIN SELECT * FROM users WHERE name = "张三";

关键字段

字段	说明	优化目标
type	访问类型	system > const > eq_ref > ref > range > index > ALL
key	实际使用的索引	显示索引名
rows	扫描行数	越少越好
Extra	额外信息	Using index > Using where > Using temporary > Using filesort

type 详解

-- ALL：全表扫描（最差）
SELECT * FROM users;

-- index：全索引扫描
SELECT name FROM users;

-- range：范围扫描
SELECT * FROM users WHERE id > 100;

-- ref：非唯一索引查找
SELECT * FROM users WHERE name = "张三";

-- eq_ref：唯一索引查找
SELECT * FROM orders WHERE user_id = 1;

-- const：主键或唯一索引常量查询
SELECT * FROM users WHERE id = 1;

-- system：表只有一行

索引优化实战

1. WHERE 子句优化

-- ✅ 使用索引
SELECT * FROM users WHERE email = "test@example.com";

-- ❌ 索引失效
SELECT * FROM users WHERE LEFT(email, 4) = "test";  -- 函数操作
SELECT * FROM users WHERE email LIKE "%test%";      -- 前缀通配符
SELECT * FROM users WHERE email != "test@example.com";  -- 不等于
SELECT * FROM users WHERE age + 1 = 26;  -- 列上计算

2. ORDER BY 优化

-- 创建索引
CREATE INDEX idx_name_age ON users(name, age);

-- ✅ 使用索引排序
SELECT * FROM users ORDER BY name, age;

-- ❌ 索引失效
SELECT * FROM users ORDER BY age, name;  -- 顺序不对
SELECT * FROM users WHERE name > "张" ORDER BY age;  -- 范围查询后

3. JOIN 优化

-- 确保 JOIN 字段有索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_id ON users(id);

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;

4. 分页优化

-- ❌ 慢分页（深度分页）
SELECT * FROM users LIMIT 100000, 10;

-- ✅ 优化 1：子查询
SELECT * FROM users 
WHERE id >= (SELECT id FROM users LIMIT 100000, 1)
LIMIT 10;

-- ✅ 优化 2：延迟关联
SELECT u.* FROM users u
INNER JOIN (SELECT id FROM users LIMIT 100000, 10) tmp
ON u.id = tmp.id;

索引设计原则

1. 选择合适的列

• ✅ WHERE、JOIN、ORDER BY、GROUP BY 的列
• ✅ 高基数列（区分度高）
• ❌ 低基数列（性别、状态等）

2. 控制索引数量

• 每张表 3-5 个索引为宜
• 索引不是越多越好（影响写入性能）

3. 前缀索引

-- 长字符串使用前缀索引
CREATE INDEX idx_email_prefix ON users(email(10));

4. 避免冗余索引

-- ❌ 冗余
INDEX idx1 (a)
INDEX idx2 (a, b)  -- 包含 idx1

-- ✅ 保留
INDEX idx2 (a, b)

监控和维护

查看索引使用情况

-- 查看表索引
SHOW INDEX FROM users;

-- 查看慢查询
SHOW VARIABLES LIKE "slow_query_log";
SET GLOBAL slow_query_log = "ON";

-- 分析表
ANALYZE TABLE users;

删除无用索引

-- 查看索引使用统计（MySQL 5.6+）
SELECT * FROM sys.schema_unused_indexes;

-- 删除索引
DROP INDEX idx_name ON users;

性能对比

-- 100 万数据测试

-- 无索引
SELECT * FROM users WHERE email = "test@example.com";
-- 执行时间：2.5 秒，扫描 100 万行

-- 有索引
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = "test@example.com";
-- 执行时间：0.02 秒，扫描 1 行

-- 提升：125 倍！

总结

索引优化口诀：

查询条件建索引，最左前缀要记牢 函数计算会失效，覆盖索引性能高 EXPLAIN 来分析，type 至少到 range 深度分页要优化，JOIN 字段加索引

合理使用索引，让你的 MySQL 飞起来！🚀