MySQL 索引优化实战

作者

MySQL 索引优化实战：让查询飞起来

MySQL索引是数据库性能优化的核心手段，设计合理的索引可以让查询速度提升数十倍甚至数百倍。本文从索引原理出发，介绍索引优化的实战技巧。

一、索引的工作原理

MySQL InnoDB默认使用B+树索引。B+树的特点：所有数据存储在叶子节点；叶子节点通过链表相连，支持范围查询；树高通常只有3-4层，意味着只需3-4次磁盘I/O就能找到数据。相比全表扫描（平均需要N/2次磁盘I/O），索引查找的性能优势在数据量大时极其显著。

二、索引类型

  • 主键索引（Clustered Index）：InnoDB表的数据按主键顺序存储，每张表只有一个
  • 普通索引（Secondary Index）：叶子节点存储索引键+主键值，查到后再回表查完整数据
  • 唯一索引：保证索引列值唯一，写入时有额外校验开销
  • 覆盖索引：查询所需字段全部在索引中，无需回表，性能最佳
  • 联合索引：多列组成的索引，遵循最左匹配原则
  • 前缀索引：对长字符串取前N个字符建索引，节省空间

三、EXPLAIN分析查询

-- EXPLAIN分析查询执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';

-- 关键字段含义
-- type: ALL(全表扫描)、index、range、ref、const（效率从低到高）
-- key: 实际使用的索引
-- rows: 估计扫描的行数
-- Extra: 
--   "Using index" = 覆盖索引，无需回表（最优）
--   "Using where" = 在Server层过滤（可能需要优化）
--   "Using filesort" = 内存/磁盘排序（需要优化）
--   "Using temporary" = 使用临时表（需要优化）

-- EXPLAIN ANALYZE (MySQL 8.0+，显示实际执行时间)
EXPLAIN ANALYZE SELECT ...;

四、联合索引最左匹配原则

-- 创建联合索引 (a, b, c)
CREATE INDEX idx_abc ON users(age, status, created_at);

-- 能用到索引的查询
SELECT * FROM users WHERE age = 25;                          -- 使用idx_abc
SELECT * FROM users WHERE age = 25 AND status = 'active';   -- 使用idx_abc
SELECT * FROM users WHERE age = 25 AND status = 'active' AND created_at > '2024-01-01'; -- 全用

-- 不能完整使用索引
SELECT * FROM users WHERE status = 'active';                 -- 索引失效！跳过了age
SELECT * FROM users WHERE age = 25 AND created_at > '2024-01-01'; -- 只用age部分

五、索引失效的常见场景

-- 1. 对索引列进行函数操作
-- 失效：
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- 优化：
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- 2. 隐式类型转换（字符串列用数字查询）
-- 失效（user_id是varchar型）：
SELECT * FROM users WHERE user_id = 123;
-- 正确：
SELECT * FROM users WHERE user_id = '123';

-- 3. 前导通配符
-- 失效：
SELECT * FROM products WHERE name LIKE '%手机%';
-- 有效（后置通配符可以用索引）：
SELECT * FROM products WHERE name LIKE '苹果%';

-- 4. 负向查询（!=、NOT IN、NOT LIKE）通常导致全表扫描

六、覆盖索引优化

-- 场景：频繁查询用户ID、邮箱和状态
-- 没有覆盖索引：需要回表查完整数据
SELECT id, email, status FROM users WHERE status = 'active';

-- 创建覆盖索引（包含查询所需的所有字段）
CREATE INDEX idx_status_covering ON users(status, id, email);
-- 现在查询只需访问索引，Extra显示"Using index"，无需回表

七、索引设计原则

  1. 高选择性优先：区分度高的列（如用户ID）索引效果好，区分度低的列（如性别）索引效果差
  2. 避免冗余索引：(a,b)已经包含了(a)的索引功能，不需要单独建(a)的索引
  3. 控制索引数量：索引会增加写操作开销，单表建议不超过5-6个索引
  4. 频繁更新的列慎建索引：每次更新都需要维护索引，高频写入场景要权衡
  5. 根据查询建索引：先看慢查询日志，找出最耗时的查询，针对性建索引

八、慢查询日志分析

-- 开启慢查询日志
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;  -- 超过1秒的查询记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 分析慢查询日志（mysqldumpslow工具）
mysqldumpslow -t 10 -s t /var/log/mysql/slow.log  # 最慢的10条

九、总结

索引优化是MySQL性能调优最重要的手段。核心思路：用EXPLAIN发现问题查询→分析执行计划→理解最左匹配和索引失效场景→设计合理的索引结构→验证优化效果。一个好的索引设计往往能让系统吞吐量提升10倍以上，是性价比最高的优化手段。