MySQL索引优化宝典：10个案例教你分析慢SQL，让查询速度提升100倍

前言

很多开发人员知道索引能加速查询，但不清楚如何正确使用。错误的索引设计不仅浪费存储空间，还会拖慢写入性能。本文将用真实的 EXPLAIN 分析案例，带你掌握索引设计的核心原则，并解决常见的索引失效问题。

一、基础知识

索引类型：B+Tree（默认）、Hash、全文索引

最左前缀原则：联合索引 (a,b,c) 能支持 (a)、(a,b)、(a,b,c) 的查询，但不支持 (b,c) 或 (a,c) 跳过中间列

EXPLAIN 关键字段：

type：system > const > eq_ref > ref > range > index > ALL（最好到最差）

possible_keys：可能用到的索引

key：实际用到的索引

rows：扫描行数

Extra：Using index（覆盖索引）、Using where、Using filesort（需要优化）

二、10个优化案例

案例1：避免在索引列上使用函数

-- 慢：不会走 create_time 索引SELECT*FROMordersWHEREDATE(create_time)='2025-01-01';-- 优化：改为范围查询SELECT*FROMordersWHEREcreate_time>='2025-01-01'ANDcreate_time<'2025-01-02';

案例2：隐式类型转换导致索引失效

-- 假设 phone 字段是 varchar 类型，但传入数字-- 慢：全表扫描SELECT*FROMuserWHEREphone=13800001111;-- 优化：统一类型SELECT*FROMuserWHEREphone='13800001111';

案例3：LIKE 通配符 % 开头失效

-- 慢：无法使用索引SELECT*FROMarticleWHEREtitleLIKE'%MySQL%';-- 优化：尽量让 % 在右边，或者使用全文索引SELECT*FROMarticleWHEREtitleLIKE'MySQL%';

案例4：最左前缀原则（联合索引）

-- 创建联合索引 idx_name_age (name, age)-- 有效查询：where name = '张三' (走索引)-- 有效查询：where name = '张三' and age = 20 (走索引)-- 无效查询：where age = 20 (不走索引)

案例5：使用覆盖索引减少回表

-- 慢：需要回表读取完整行SELECT*FROMuserWHEREage=25;-- 快：如果只查索引中包含的字段，Extra 显示 Using indexSELECTid,name,ageFROMuserWHEREage=25;-- 前提：建立联合索引 (age, name, id) 或 (age, id)

案例6：分页查询优化（深分页）

-- 慢：LIMIT 100000, 10 会扫描前10万+10行SELECT*FROMordersORDERBYidLIMIT100000,10;-- 优化：使用延迟关联或记录上次位置SELECT*FROMordersWHEREid>100000ORDERBYidLIMIT10;

案例7：避免 OR 导致索引失效

-- 慢：OR 两边若有一个不走索引，就会全表扫描SELECT*FROMproductWHEREprice=100ORcategory='book';-- 优化：使用 UNIONSELECT*FROMproductWHEREprice=100UNIONSELECT*FROMproductWHEREcategory='book';

案例8：NOT IN / <> 通常不走索引

-- 尽量避免，用 EXISTS 改写SELECT*FROMuserWHEREstatus<>0;-- 可改为（如果状态值不多）：SELECT*FROMuserWHEREstatusIN(1,2,3);

案例9：排序优化（filesort）

-- 如果 order by 的列没有索引，会产生 filesort-- 建立合适的联合索引，让索引顺序和 order by 一致-- 例如：where age = 25 order by create_time，可以建索引(age, create_time)

案例10：索引列不要参与计算

-- 慢SELECT*FROMaccountWHEREbalance+100>2000;-- 优化SELECT*FROMaccountWHEREbalance>1900;

三、索引设计建议

区分度高的列优先（如手机号、邮箱）

尽量使用联合索引代替多个单列索引，减少开销

频繁更新的列不宜建索引

冗余索引要清理：例如已有 (a,b) 索引，再建 (a) 就是冗余

定期用 pt-duplicate-key-checker 或 MySQL Workbench 检查重复索引

总结

索引优化是 DBA 和开发人员的必修课。每次写 SQL 前，先用 EXPLAIN 分析一下。另外，不要过早优化——先确认瓶颈真的是查询，再动手加索引。如果你有被慢查询折磨的经历，欢迎评论区分享！