数据库索引优化：查询提速 100 倍

索引的本质

索引就像书的目录，让你快速定位内容，无需逐页翻阅。正确设计索引可将查询从秒级降至毫秒级。

一、索引类型详解

B-Tree 索引（最常用）

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

-- 创建索引
CREATE INDEX idx_user_email ON users(email);

-- 复合索引
CREATE INDEX idx_user_status_created ON users(status, created_at);

最左前缀原则：

-- 复合索引 (a, b, c)
WHERE a = 1              -- ✅ 使用索引
WHERE a = 1 AND b = 2    -- ✅ 使用索引
WHERE b = 2              -- ❌ 不使用索引
WHERE a = 1 AND c = 3    -- ⚠️ 只用 a 部分

Hash 索引

适用于：精确等值查询

-- MySQL Memory 引擎
CREATE TABLE hash_table (
  id INT,
  data VARCHAR(100),
  INDEX USING HASH (id)
);

全文索引

适用于：文本搜索

-- MySQL
CREATE FULLTEXT INDEX idx_content ON articles(content);

SELECT * FROM articles 
WHERE MATCH(content) AGAINST("数据库优化" IN NATURAL LANGUAGE MODE);

覆盖索引

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

-- 索引
CREATE INDEX idx_status_email ON users(status, email);

-- 查询（覆盖索引）
SELECT email FROM users WHERE status = 1;

-- 查询（需要回表）
SELECT email, name FROM users WHERE status = 1;

二、索引设计原则

1. 选择高选择性列

选择性 = 不同值数量 / 总行数

-- 好：性别（2 个值）不适合索引
-- 好：邮箱（几乎唯一）适合索引
-- 好：状态（几个值）+ 时间 复合索引

2. 前缀索引优化大字段

-- 全文索引太耗时，用前缀
CREATE INDEX idx_email_prefix ON users(email(10));

-- 验证选择性
SELECT 
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) as selectivity
FROM users;

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

❌ 错误：

WHERE YEAR(created_at) = 2026      -- 索引失效
WHERE email LIKE "%@gmail.com"     -- 前缀通配符失效
WHERE price + 10 > 100             -- 列上运算失效

✅ 正确：

WHERE created_at >= "2026-01-01" AND created_at < "2027-01-01"
WHERE email LIKE "%@gmail.com"     -- 后缀可以
WHERE price > 90                   -- 直接比较

三、EXPLAIN 分析查询

EXPLAIN SELECT * FROM orders 
WHERE user_id = 123 AND status = "paid" 
ORDER BY created_at DESC LIMIT 10;

关键字段解读

字段	含义	理想值
type	访问类型	const/ref 最优
possible_keys	可用索引	-
key	实际使用索引	-
rows	扫描行数	越少越好
Extra	额外信息	Using index 最优

type 类型（从优到差）

1. system/const：主键或唯一索引等值查询
2. eq_ref：主键或唯一索引关联
3. ref：非唯一索引查询
4. range：范围查询
5. index：全索引扫描
6. ALL：全表扫描（最差）

四、慢查询优化实战

案例 1：分页优化

❌ 慢查询：

SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 10;

✅ 优化方案：

-- 延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 10) tmp
ON o.id = tmp.id;

-- 或记录上次位置
SELECT * FROM orders 
WHERE created_at < "2025-12-31 23:59:59" 
ORDER BY created_at DESC LIMIT 10;

案例 2：COUNT 优化

❌ 慢查询：

SELECT COUNT(*) FROM orders WHERE status = 1 AND created_at > "2026-01-01";

✅ 优化方案：

-- 使用覆盖索引
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 或使用缓存表
CREATE TABLE order_stats (
  date DATE PRIMARY KEY,
  status TINYINT,
  count INT
);

案例 3：JOIN 优化

-- 确保关联列有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_id ON users(id);

-- 小表驱动大表
SELECT /*+ SMALL_TABLE(u) */ o.* 
FROM users u 
JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;

五、索引维护

1. 监控索引使用

-- MySQL 8.0+
SELECT * FROM sys.schema_unused_indexes;

-- 查看索引统计
SELECT 
  table_name,
  index_name,
  cardinality,
  seq_in_index
FROM information_schema.statistics
WHERE table_schema = "your_db";

2. 重建碎片化索引

-- MySQL
OPTIMIZE TABLE orders;

-- PostgreSQL
REINDEX TABLE orders;

3. 定期 ANALYZE

ANALYZE TABLE orders;

六、常见陷阱

1. 过度索引

-- ❌ 每个列都建索引
CREATE INDEX idx_col1 ON t(col1);
CREATE INDEX idx_col2 ON t(col2);
CREATE INDEX idx_col3 ON t(col3);

-- ✅ 根据查询模式设计
CREATE INDEX idx_col1_col2 ON t(col1, col2);

索引代价：

• 写入性能下降
• 存储空间增加
• 维护成本上升

2. 忽略隐式类型转换

-- phone 是 VARCHAR
WHERE phone = 13800138000  -- ❌ 类型转换，索引失效
WHERE phone = "13800138000" -- ✅ 正确

3. OR 条件陷阱

-- ❌ 可能全表扫描
WHERE id = 1 OR name = "test";

-- ✅ UNION ALL
SELECT * FROM table WHERE id = 1
UNION ALL
SELECT * FROM table WHERE name = "test";

总结

索引优化核心要点：

1. 理解查询模式：根据实际 SQL 设计索引
2. 遵循最左前缀：复合索引顺序很重要
3. 避免索引失效：不在索引列上运算
4. 定期监控维护：删除无用索引
5. 平衡读写性能：索引不是越多越好

记住：没有万能索引，只有最适合的索引！