MySQL索引优化完全指南：从原理到实战🗃️ 核心知识点
理解B+树索引的底层原理掌握索引设计的黄金法则学会分析和优化慢查询避免常见的索引失效场景📚 什么是索引？索引是数据库中用于加速数据检索的数据结构，类似于书籍的目录。没有索引时，数据库需要全表扫描；有了索引，可以快速定位数据。
🌲 B+树索引原理为什么选择B+树？数据结构查询复杂度范围查询磁盘IO哈希表O(1)❌ 不支持较少二叉树O(log n)✅ 支持较多B+树O(log n)✅ 支持最少B+树结构示意123456789 [根节点: 50] / \ [20, 35] [70, 85] / | \ / | \[10,15] [25,30] [40,45] [60,65] [75,80] [90,95] ↓ ↓ ↓ ↓ ↓ ↓ 数据 数据 数据 数据 数据 数据 叶子节点通过双向链表相连，支持范围查询🎯 索引类型详解1. 主键索引 (Primary Key)12345678-- 创建表时定义主键CREATE TABLE users ( id BIGINT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL);-- InnoDB中，主键索引即聚簇索引，数据存储在叶子节点2. 唯一索引 (Unique Index)12345678-- 保证列值唯一CREATE UNIQUE INDEX idx_email ON users(email);-- 或在创建表时定义CREATE TABLE users ( id BIGINT PRIMARY KEY, email VARCHAR(100) UNIQUE);3. 普通索引 (Normal Index)12-- 最基本的索引类型CREATE INDEX idx_username ON users(username);4. 联合索引 (Composite Index)1234567891011-- 多列组合索引，遵循最左前缀原则CREATE INDEX idx_name_age_city ON users(name, age, city);-- ✅ 能使用索引的查询SELECT * FROM users WHERE name = '张三';SELECT * FROM users WHERE name = '张三' AND age = 25;SELECT * FROM users WHERE name = '张三' AND age = 25 AND city = '北京';-- ❌ 无法使用索引的查询SELECT * FROM users WHERE age = 25; -- 跳过了nameSELECT * FROM users WHERE city = '北京'; -- 跳过了name和age5. 覆盖索引 (Covering Index)12345678-- 当查询的列都在索引中时，无需回表CREATE INDEX idx_name_email ON users(name, email);-- ✅ 覆盖索引，不需要回表SELECT name, email FROM users WHERE name = '张三';-- ❌ 需要回表获取其他列SELECT * FROM users WHERE name = '张三';🔧 EXPLAIN分析查询1EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';EXPLAIN关键字段解读字段说明优化目标type访问类型system > const > eq_ref > ref > range > index > ALLkey实际使用的索引应该使用预期的索引rows预估扫描行数越小越好Extra额外信息避免Using filesort, Using temporarytype类型详解1234567891011-- const: 主键或唯一索引的等值查询EXPLAIN SELECT * FROM users WHERE id = 1;-- ref: 普通索引的等值查询EXPLAIN SELECT * FROM users WHERE username = 'admin';-- range: 范围查询EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;-- ALL: 全表扫描（需要优化！）EXPLAIN SELECT * FROM users WHERE age + 1 = 25;⚠️ 索引失效的常见场景1. 对索引列使用函数12345-- ❌ 索引失效SELECT * FROM users WHERE YEAR(create_time) = 2024;-- ✅ 优化后SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';2. 隐式类型转换123456-- 假设 phone 是 VARCHAR 类型-- ❌ 索引失效（数字与字符串比较）SELECT * FROM users WHERE phone = 13800138000;-- ✅ 正确写法SELECT * FROM users WHERE phone = '13800138000';3. LIKE以通配符开头12345-- ❌ 索引失效SELECT * FROM users WHERE name LIKE '%张';-- ✅ 可以使用索引SELECT * FROM users WHERE name LIKE '张%';4. OR条件未全部建立索引123456789-- 假设只有name有索引，age没有-- ❌ 索引失效SELECT * FROM users WHERE name = '张三' OR age = 25;-- ✅ 解决方案1：给age也建索引-- ✅ 解决方案2：改用UNIONSELECT * FROM users WHERE name = '张三'UNIONSELECT * FROM users WHERE age = 25;5. 不等于操作12345-- ❌ 可能不走索引SELECT * FROM users WHERE status != 'deleted';-- ✅ 如果非删除数据占大多数，可以改写SELECT * FROM users WHERE status IN ('active', 'pending', 'blocked');💡 索引设计最佳实践1. 选择性高的列优先12345678-- 选择性 = 不重复值数量 / 总行数SELECT COUNT(DISTINCT status) / COUNT(*) AS status_selectivity, COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivityFROM orders;-- user_id选择性更高，应该放在联合索引前面CREATE INDEX idx_user_status ON orders(user_id, status);2. 频繁查询的列建索引12-- 分析慢查询日志，找出高频查询条件-- 为WHERE、JOIN、ORDER BY、GROUP BY涉及的列建索引3. 控制索引数量123456789-- ❌ 索引过多会影响写入性能CREATE INDEX idx_a ON table(a);CREATE INDEX idx_b ON table(b);CREATE INDEX idx_c ON table(c);CREATE INDEX idx_ab ON table(a, b);CREATE INDEX idx_bc ON table(b, c);-- ✅ 合理设计联合索引，一个索引覆盖多个查询场景CREATE INDEX idx_abc ON table(a, b, c);4. 长字符串使用前缀索引123456789-- 对于长字符串，使用前缀索引节省空间CREATE INDEX idx_email_prefix ON users(email(10));-- 选择合适的前缀长度SELECT COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS len5, COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS len10, COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS len15FROM users;📊 实战案例：电商订单表优化原始表结构12345678910CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, product_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL, update_time DATETIME NOT NULL);常见查询场景分析12345678910111213141516171819-- 场景1：根据订单号查询（高频，需要快速定位）SELECT * FROM orders WHERE order_no = 'ORD202412010001';-- 建议：唯一索引CREATE UNIQUE INDEX idx_order_no ON orders(order_no);-- 场景2：查询用户的订单列表（高频）SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC;-- 建议：联合索引CREATE INDEX idx_user_time ON orders(user_id, create_time DESC);-- 场景3：后台查询某状态的订单（中频）SELECT * FROM orders WHERE status = 1 AND create_time > '2024-12-01';-- 建议：联合索引CREATE INDEX idx_status_time ON orders(status, create_time);-- 场景4：统计某商品的销售额SELECT SUM(amount) FROM orders WHERE product_id = 500 AND status = 2;-- 建议：联合索引 + 覆盖索引CREATE INDEX idx_product_status_amount ON orders(product_id, status, amount);最终索引设计12345-- 精简后的索引方案CREATE UNIQUE INDEX idx_order_no ON orders(order_no);CREATE INDEX idx_user_time ON orders(user_id, create_time DESC);CREATE INDEX idx_status_time ON orders(status, create_time);CREATE INDEX idx_product_status ON orders(product_id, status);🔍 慢查询优化流程12345678910111. 开启慢查询日志 ↓2. 分析慢查询日志，找出问题SQL ↓3. 使用EXPLAIN分析执行计划 ↓4. 检查是否缺少索引或索引失效 ↓5. 优化SQL或添加/调整索引 ↓6. 测试验证优化效果开启慢查询日志1234567-- 查看当前配置SHOW VARIABLES LIKE 'slow_query%';SHOW VARIABLES LIKE 'long_query_time';-- 开启慢查询日志SET GLOBAL slow_query_log = 'ON';SET GLOBAL long_query_time = 1; -- 超过1秒的查询记录🚀 总结索引优化口诀最左前缀要牢记，联合索引按顺序覆盖索引减回表，查询效率大提升函数运算要避免，索引列保持原样类型转换是大坑，字段类型要匹配选择性高放前面，区分度大更有效性能优化checklist✅ 为高频查询条件建立索引✅ 使用EXPLAIN验证索引使用情况✅ 避免索引失效的写法✅ 定期分析和优化慢查询✅ 控制单表索引数量（建议不超过5-6个）如果这篇文章对你有帮助，欢迎点赞分享！ 💪