前言很多开发人员知道索引能加速查询但不清楚如何正确使用。错误的索引设计不仅浪费存储空间还会拖慢写入性能。本文将用真实的 EXPLAIN 分析案例带你掌握索引设计的核心原则并解决常见的索引失效问题。一、基础知识索引类型BTree默认、Hash、全文索引最左前缀原则联合索引 (a,b,c) 能支持 (a)、(a,b)、(a,b,c) 的查询但不支持 (b,c) 或 (a,c) 跳过中间列EXPLAIN 关键字段typesystem const eq_ref ref range index ALL最好到最差possible_keys可能用到的索引key实际用到的索引rows扫描行数ExtraUsing index覆盖索引、Using where、Using filesort需要优化二、10个优化案例案例1避免在索引列上使用函数-- 慢不会走 create_time 索引SELECT*FROMordersWHEREDATE(create_time)2025-01-01;-- 优化改为范围查询SELECT*FROMordersWHEREcreate_time2025-01-01ANDcreate_time2025-01-02;案例2隐式类型转换导致索引失效-- 假设 phone 字段是 varchar 类型但传入数字-- 慢全表扫描SELECT*FROMuserWHEREphone13800001111;-- 优化统一类型SELECT*FROMuserWHEREphone13800001111;案例3LIKE 通配符 % 开头失效-- 慢无法使用索引SELECT*FROMarticleWHEREtitleLIKE%MySQL%;-- 优化尽量让 % 在右边或者使用全文索引SELECT*FROMarticleWHEREtitleLIKEMySQL%;案例4最左前缀原则联合索引-- 创建联合索引 idx_name_age (name, age)-- 有效查询where name 张三 (走索引)-- 有效查询where name 张三 and age 20 (走索引)-- 无效查询where age 20 (不走索引)案例5使用覆盖索引减少回表-- 慢需要回表读取完整行SELECT*FROMuserWHEREage25;-- 快如果只查索引中包含的字段Extra 显示 Using indexSELECTid,name,ageFROMuserWHEREage25;-- 前提建立联合索引 (age, name, id) 或 (age, id)案例6分页查询优化深分页-- 慢LIMIT 100000, 10 会扫描前10万10行SELECT*FROMordersORDERBYidLIMIT100000,10;-- 优化使用延迟关联或记录上次位置SELECT*FROMordersWHEREid100000ORDERBYidLIMIT10;案例7避免 OR 导致索引失效-- 慢OR 两边若有一个不走索引就会全表扫描SELECT*FROMproductWHEREprice100ORcategorybook;-- 优化使用 UNIONSELECT*FROMproductWHEREprice100UNIONSELECT*FROMproductWHEREcategorybook;案例8NOT IN / 通常不走索引-- 尽量避免用 EXISTS 改写SELECT*FROMuserWHEREstatus0;-- 可改为如果状态值不多SELECT*FROMuserWHEREstatusIN(1,2,3);案例9排序优化filesort-- 如果 order by 的列没有索引会产生 filesort-- 建立合适的联合索引让索引顺序和 order by 一致-- 例如where age 25 order by create_time可以建索引(age, create_time)案例10索引列不要参与计算-- 慢SELECT*FROMaccountWHEREbalance1002000;-- 优化SELECT*FROMaccountWHEREbalance1900;三、索引设计建议区分度高的列优先如手机号、邮箱尽量使用联合索引代替多个单列索引减少开销频繁更新的列不宜建索引冗余索引要清理例如已有 (a,b) 索引再建 (a) 就是冗余定期用 pt-duplicate-key-checker 或 MySQL Workbench 检查重复索引总结索引优化是 DBA 和开发人员的必修课。每次写 SQL 前先用 EXPLAIN 分析一下。另外不要过早优化——先确认瓶颈真的是查询再动手加索引。如果你有被慢查询折磨的经历欢迎评论区分享