MySQL SQL 调优完整指南

SQL 调优是一个系统性工程，需要从发现问题到解决问题的全流程掌握。下面从方法论到具体技巧详细讲解。

一、调优流程图

二、发现问题：定位慢查询

1. 开启慢查询日志

-- 查看慢查询配置SHOWVARIABLESLIKE'slow_query%';SHOWVARIABLESLIKE'long_query_time';-- 开启慢查询日志SETGLOBALslow_query_log=ON;SETGLOBALlong_query_time=1;-- 超过1秒的记录-- 查看慢查询日志mysqldumpslow-s t-t10/var/lib/mysql/slow-query.log

2. 查看正在执行的慢查询

-- 查看当前正在执行的所有查询SHOWPROCESSLIST;-- 找出执行时间长的SELECT*FROMinformation_schema.PROCESSLISTWHERETIME>5ANDCOMMAND!='Sleep'ORDERBYTIMEDESC;

三、分析问题：使用 EXPLAIN

1. EXPLAIN 基本用法

EXPLAINSELECT*FROMusersWHEREname='张三'\G-- 输出关键字段

2. 关注 Extra 字段

-- ✅ 好Usingindex-- 覆盖索引，不需要回表Usingindexcondition-- 索引下推-- ⚠️ 需要优化Usingfilesort-- 需要额外排序Usingtemporary-- 用了临时表

四、解决问题：核心优化技巧

1. 索引优化

-- 为 WHERE 条件建索引CREATEINDEXidx_nameONusers(name);-- 为 ORDER BY 建索引CREATEINDEXidx_create_timeONorders(create_time);-- 复合索引注意最左前缀CREATEINDEXidx_name_ageONusers(name,age);

2. 避免 SELECT *

-- ❌ 不好SELECT*FROMusersWHEREname='张三';-- ✅ 好（只查需要的字段）SELECTid,nameFROMusersWHEREname='张三';

3. 避免在索引列上使用函数

-- ❌ 无法使用索引SELECT*FROMordersWHEREYEAR(create_time)=2024;-- ✅ 可以走索引SELECT*FROMordersWHEREcreate_time>='2024-01-01'ANDcreate_time<'2025-01-01';

4. 分页优化

-- ❌ 深分页问题SELECT*FROMordersORDERBYidLIMIT100000,10;-- ✅ 使用游标分页SELECT*FROMordersWHEREid>100000ORDERBYidLIMIT10;

5. JOIN 优化

-- 小表驱动大表-- 为 JOIN 字段建索引CREATEINDEXidx_user_idONorders(user_id);

五、高级优化技巧

1. 使用覆盖索引

-- 创建包含所有查询字段的索引CREATEINDEXidx_coveringONusers(name,age,id);-- 查询可以直接从索引获取数据SELECTid,name,ageFROMusersWHEREname='张三';-- Extra: Using index

2. 合理使用 EXISTS 替代 IN

-- IN 在大数据量时可能慢SELECT*FROMusersWHEREidIN(SELECTuser_idFROMordersWHEREamount>1000);-- EXISTS 可能更快SELECT*FROMusers uWHEREEXISTS(SELECT1FROMorders oWHEREo.user_id=u.idANDo.amount>1000);

3. 批量操作优化

-- 批量插入INSERTINTOusers(name)VALUES('张三'),('李四'),('王五');-- 一次插入多条-- 批量更新使用临时表CREATETEMPORARYTABLEtemp_updates(idINTPRIMARYKEY,ageINT);

六、监控和验证

1. 查看索引使用情况

-- 查看索引使用次数SELECTindex_name,rows_selected,rows_insertedFROMperformance_schema.table_io_waits_summary_by_index_usageWHEREobject_schema='db_name';-- 查看从未使用的索引SELECT*FROMsys.schema_unused_indexes;

2. 查看查询缓存命中率

SHOWSTATUSLIKE'Qcache%';SHOWSTATUSLIKE'Handler_read%';

七、总结：SQL 调优 Checklist

• 是否开启了慢查询日志？
• 是否用 EXPLAIN 分析了问题 SQL？
• WHERE 条件字段是否有索引？
• ORDER BY 字段是否有索引？
• 是否避免了 SELECT *？
• 是否避免了在索引列上使用函数？
• JOIN 字段是否有索引？
• 是否小表驱动大表？
• 分页是否过深？
• 是否有冗余或未用的索引？

一句话理解：SQL 调优就像医生看病，先查症状（慢查询日志），再诊断病因（EXPLAIN），最后对症下药（索引优化、SQL重写）。