️ MySQL 索引优化全景手册
一、核心思想与创建原则 (1-5)
1. 理解索引的本质
索引是数据的 有序副本（B+Tree 为主）。
-- 查看表索引情况
SHOW INDEX FROM users;
2. 为搜索、排序、分组而建
只为出目前 WHERE、ORDER BY、GROUP BY 的列建索引。
CREATE INDEX idx_user_email ON users(email);
3. 最左前缀原则
复合索引 (A, B, C) 相当于 (A)、(A,B)、(A,B,C)。
CREATE INDEX idx_abc ON orders(user_id, status, create_time);
-- 可用：WHERE user_id=1 AND status=1
-- 不可用：WHERE status=1 AND create_time>'2023-01-01'
4. 选择高选择性列
高选择性列优先建索引。
-- 好：几乎唯一
CREATE INDEX idx_phone ON users(phone);
-- 坏：只有男女两个值
CREATE INDEX idx_gender ON users(gender);
5. 避免过度索引
过多索引拖慢写入。
-- 删除冗余索引
DROP INDEX idx_email_duplicate ON users;
二、索引使用技巧与避坑 (6-15)
6. 避免函数操作
-- ❌ 索引失效
SELECT * FROM orders WHERE YEAR(create_time)=2023;
-- ✅ 改写
SELECT * FROM orders
WHERE create_time >= '2023-01-01'
AND create_time < '2024-01-01';
7. 避免隐式类型转换
-- user_id 是 VARCHAR
-- ❌ 索引失效
SELECT * FROM users WHERE user_id = 10086;
-- ✅ 正确
SELECT * FROM users WHERE user_id = '10086';
8. 避免 <> 和 NOT IN
-- ❌ 索引利用差
SELECT * FROM orders WHERE status <> 1;
-- ✅ 改写
SELECT * FROM orders WHERE status IN (0,2,3);
9. LIKE 模糊查询
-- ✅ 走索引
SELECT * FROM products WHERE name LIKE 'abc%';
-- ❌ 不走索引
SELECT * FROM products WHERE name LIKE '%abc%';
10. IN / OR 使用
-- 一般可以用索引
SELECT * FROM users WHERE id IN (1,2,3);
-- 但如果 IN 集合很大，可能全表扫描
11. 覆盖索引
-- 有索引 (a,b)
CREATE INDEX idx_a_b ON t(a,b);
-- ✅ 覆盖索引查询（无需回表）
SELECT a,b FROM t WHERE a=1;
12. 利用索引排序
-- 有索引 (a,b)
-- ✅ ORDER BY a, b 可直接用索引
SELECT * FROM t WHERE a=1 ORDER BY b;
-- ❌ ORDER BY b 无法利用索引
13. IS NULL / IS NOT NULL
CREATE INDEX idx_email ON users(email);
-- ✅ 可用索引
SELECT * FROM users WHERE email IS NULL;
14. 短索引（前缀索引）
-- 只索引前 20 个字符
CREATE INDEX idx_title ON articles(title(20));
15. 慢查询分析
SET GLOBAL slow_query_log = ON;
EXPLAIN SELECT * FROM orders WHERE user_id=100;
三、高级与维护技巧 (16-20)
16. 减少索引碎片化
OPTIMIZE TABLE orders;
17. 复合索引 vs 单列索引
-- ✅ 更优
CREATE INDEX idx_user_status ON orders(user_id, status);
-- ❌ 冗余
CREATE INDEX idx_user ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
18. 索引下推 ICP
-- MySQL 5.6+ 可在存储引擎层过滤
EXPLAIN SELECT * FROM orders WHERE user_id=1 AND status>2;
19. 唯一索引 vs 普通索引
-- 唯一索引（保证唯一性）
CREATE UNIQUE INDEX idx_email ON users(email);
-- 普通索引（允许重复，写入更快）
CREATE INDEX idx_status ON orders(status);
20. FORCE INDEX 谨慎使用
SELECT * FROM orders FORCE INDEX(idx_user_status)
WHERE user_id=1 AND status=1;
四、进阶与场景优化技巧 (21-30)
21. 选择性 vs 条件顺序
-- 如果 status 选择性更高，应调整索引列顺序
CREATE INDEX idx_status_user ON orders(status, user_id);
22. 分页优化
-- ❌ 大偏移分页慢
SELECT * FROM orders WHERE status=1 ORDER BY id LIMIT 10000,20;
-- ✅ 子查询优化
SELECT o.* FROM orders o
JOIN (
SELECT id FROM orders
WHERE status=1 ORDER BY id LIMIT 10000,20
) t ON o.id=t.id;
23. 索引合并
CREATE INDEX idx_a ON t(a);
CREATE INDEX idx_b ON t(b);
-- MySQL 可能使用 Index Merge
SELECT * FROM t WHERE a=1 OR b=2;
24. GROUP BY 隐式排序
-- 索引 (a,b) 可直接加速 GROUP BY
SELECT a,b,COUNT(*) FROM t GROUP BY a,b;
25. 统计信息
ANALYZE TABLE orders;
26. 虚拟列 + 索引
ALTER TABLE orders
ADD COLUMN year_created INT
GENERATED ALWAYS AS (YEAR(create_time)) STORED,
ADD INDEX idx_year(year_created);
SELECT * FROM orders WHERE year_created=2023;
27. 分库分表场景
-- 分库分表路由字段必须建索引
CREATE INDEX idx_user_id ON orders(user_id);
28. JSON 索引 (MySQL 8.0+)
ALTER TABLE orders
ADD COLUMN order_type VARCHAR(20)
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(extra,'$.type'))) STORED,
ADD INDEX idx_order_type(order_type);
29. 监控索引使用
SELECT object_schema, object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL;
30. 索引与锁
-- 如果 status 选择性差，更新可能锁大量行
UPDATE orders SET status=1 WHERE status=0;
-- 优化：增加高选择性条件
UPDATE orders SET status=1 WHERE status=0 AND user_id=123;
五、总结 & 优化闭环
索引优化四大维度
- 创建原则 → 最左前缀、高选择性、复合优于单列
- SQL 使用 → 避免函数/转换、LIKE 前模糊、少用 <>
- 性能提升 → 覆盖索引、索引排序、ICP、虚拟列
- 维护管理 → 碎片整理、冗余索引清理、慢查询分析
优化闭环
慢 SQL → EXPLAIN → 调整索引/SQL → 验证 → 持续监控
至此，一份带有 完整 SQL 示例 的 MySQL 索引优化全景手册（30 条） 就整理完成了，能直接落地使用。
© 版权声明
文章版权归作者所有，未经允许请勿转载。如内容涉嫌侵权，请在本页底部进入<联系我们>进行举报投诉!
THE END
暂无评论内容