一、建表 SQLCREATETABLEuser(idintNOTNULLAUTO_INCREMENT,user_idvarchar(32)NOTNULLCOMMENT用户ID,agetinyintNOTNULLCOMMENT年龄,cityvarchar(32)NOTNULLCOMMENT城市,create_timedatetimeNOTNULLCOMMENT创建时间,nicknamevarchar(64)DEFAULTNULLCOMMENT昵称,PRIMARYKEY(id),-- 联合索引最左匹配 (user_id, age, city)INDEXidx_userid_age_city(user_id,age,city))ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 插入测试数据INSERTINTOuser(user_id,age,city,create_time,nickname)VALUES(u001,20,北京,2025-01-01,张三),(u001,22,上海,2025-01-02,张三),(u001,26,广州,2025-01-03,张三),(u001,30,深圳,2025-01-04,张三),(u002,18,北京,2025-02-01,李四),(u002,25,杭州,2025-02-02,李四),(u003,28,成都,2025-03-01,王五),(u003,35,重庆,2025-03-02,王五);二、联合索引规则规则必须从user_id开始必须连续匹配跳过一列后面全失效范围查询 between会截断索引三、16 种最左匹配失效场景1. 跳过最左列 user_id直接查 age、cityEXPLAINSELECT*FROMuserWHEREage20ANDcity北京;结果typeALL全表扫描原因最左匹配必须从第一列 user_id 开始跳过则整个索引失效。2. 跳过中间列 age直接查 user_id cityEXPLAINSELECT*FROMuserWHEREuser_idu001ANDcity北京;结果只用到 user_idcity 失效原因最左匹配要求连续匹配跳过 agecity 无法使用索引。3. 范围查询 age 25导致 city 失效EXPLAINSELECT*FROMuserWHEREuser_idu001ANDage25ANDcity广州;结果用到 user_id、agecity 失效原因范围查询会打断最左链age 用了 后面 city 无法走索引。4. 范围查询 ORDER BY cityEXPLAINSELECT*FROMuserWHEREuser_idu001ANDage25ORDERBYcity;结果Using filesort原因age 是范围索引断裂city 在索引里不再有序无法索引排序。解决方案调整联合索引顺序(user_id,city,age)5. 范围查询 GROUP BY cityEXPLAINSELECTcity,COUNT(*)FROMuserWHEREuser_idu001ANDage25GROUPBYcity;结果Using temporary;Using filesort原因分组依赖有序性age 范围打断后city 无序必须临时表 排序。6. ORDER BY 跳过中间列 ageEXPLAINSELECTcity,COUNT(*)FROMuserWHEREuser_idu001GROUPBYcity;结果Using filesort原因排序必须连续匹配跳过 age 直接排 city索引排序失效。7. GROUP BY 跳过中间列 ageEXPLAINSELECTcity,COUNT(*)FROMuserWHEREuser_idu001GROUPBYcity;结果Using temporary; Using filesort原因分组必须遵循最左连续跳过 age 直接分组索引失效。8. ORDER BY 升降序混用EXPLAINSELECT*FROMuserORDERBYuser_idASC,ageDESC;结果Using filesort原因联合索引是固定有序全升 / 全降混用升降序破坏有序性。9. 索引列使用函数EXPLAINSELECT*FROMuserWHERELENGTH(user_id)5;结果全表扫描原因函数作用于索引列MySQL 无法走索引树。10. 索引列做运算EXPLAINSELECT*FROMuserWHEREage121;结果全表扫描原因索引列参与计算索引直接失效。11. 隐式类型转换EXPLAINSELECT*FROMuserWHEREuser_id111;结果全表扫描原因user_id 是 varchar传入数字触发隐式转换索引失效。12. LIKE 左模糊 % 开头CREATEINDEXidx_nicknameONuser(nickname);EXPLAINSELECT*FROMuserWHEREnicknameLIKE%三;结果全表扫描原因% 开头无法利用索引有序性最左匹配失效。13. OR 连接一边无索引EXPLAINSELECT*FROMuserWHEREuser_idu001ORnicknametest;结果全表扫描原因OR 两边必须都有索引否则优化器放弃索引。14. IS NULL / IS NOT NULLEXPLAINSELECT*FROMuserWHEREuser_idISNOTNULL;结果全表 / 索引失效原因MySQL 认为数据量太大直接走全表。15. 查询数据量过大优化器放弃索引EXPLAINSELECT*FROMuserWHEREage0;结果typeALL原因数据量超过 30%优化器认为全表更快。16. SELECT * 无法使用覆盖索引EXPLAINSELECT*FROMuserWHEREuser_idu001ANDage20ANDcity北京;结果需要回表原因select * 包含索引外字段无法走覆盖索引。四、最经典优化面试必说问题 SQLSELECT*FROMuserWHEREuser_idu001ANDage25ORDERBYcity;优化方案把索引改成 (user_id, city, age)DROPINDEXidx_userid_age_cityONuser;CREATEINDEXidx_userid_city_ageONuser(user_id,city,age);优化后Using filesort 消失city 有序直接用索引排序age 范围最后过滤五、最左匹配万能口诀