慢查询优化Explain分析、低效SQL排查与实战优化作者黒漂技术佬适用读者SQL能写但不知道为什么慢的同学关联场景无人售货柜百万级订单查询、智慧农业传感器数据分析一、什么是慢查询为什么要优化慢查询就是执行时间超过阈值的SQL语句。MySQL默认阈值是10秒生产环境通常设为1秒甚至更短。慢查询的危害不止是用户等得久慢SQL会长时间占用连接一个查询占着连接不放连接池被耗尽整个应用就拒绝服务了慢SQL会影响其他查询InnoDB行锁会阻塞其他事务导致连锁反应慢SQL会拖垮数据库大量全表扫描消耗CPU和I/OCPU飙升到100%真实案例某无人售货柜平台的订单报表查询要30秒高峰期数据库CPU飙满导致用户扫码开门都超时。优化后查询降到0.5秒CPU恢复到20%。二、Explain执行计划SQL体检报告Explain是排查慢查询的第一工具。在SQL前面加EXPLAIN关键字MySQL就会返回这条SQL的执行计划告诉你它打算怎么查。EXPLAINSELECT*FROMordersWHEREcabinet_id1ANDcreated_at2024-01-01;输出结果关键字段字段含义重点关注id查询序号越大越先执行select_type查询类型SIMPLE/PRIMARY/SUBQUERY等table表名type访问类型性能关键指标possible_keys可能用到的索引key实际用到的索引NULL表示没走索引key_len使用的索引长度判断联合索引用了几个字段ref索引比较的值rows预估扫描行数越小越好Extra额外信息Using index/temporary/filesort2.1 type字段性能从差到好排列type表示MySQL访问数据的方式性能从最差到最好type级别含义性能说明ALL全表扫描最差扫描整张表的每一行index全索引扫描差扫描整个索引树range范围扫描中BETWEEN, , , INref非唯一索引等值查找好普通索引等值查询eq_ref唯一索引等值查找很好JOIN时被关联表是唯一索引const常量级查询最好主键/唯一索引等值查询黄金标准type至少要达到range级别最好到ref或const。看到ALL就要警惕了。2.2 Extra字段额外信息解读Extra值含义好坏Using index覆盖索引不回表✅ 好Using where在存储引擎返回数据后还在Server层过滤⚠️ 一般Using temporary使用了临时表❌ 差需要优化Using filesort使用了文件排序❌ 差需要优化Using join bufferJOIN没走索引用了缓存块❌ 差Using index condition索引下推(ICP)✅ 好Using temporary和Using filesort是性能杀手说明MySQL需要创建临时表或额外排序。常见于GROUP BY、ORDER BY字段没有索引时。2.3 key_len判断联合索引用了多少key_len表示实际使用的索引字节长度。通过它能判断联合索引用了几个字段。-- 联合索引 idx(cabinet_id INT, product_id INT, created_at DATETIME)-- INT4字节, DATETIME5字节, 可空1字节EXPLAINSELECT*FROMordersWHEREcabinet_id1;-- key_len 5 (4字节INT 1字节可空标记) → 只用了第1个字段EXPLAINSELECT*FROMordersWHEREcabinet_id1ANDproduct_id1001;-- key_len 10 → 用了前2个字段EXPLAINSELECT*FROMordersWHEREcabinet_id1ANDproduct_id1001ANDcreated_at2024-01-01;-- key_len 15 → 3个字段全用上了key_len计算规则INT4字节BIGINT8字节DATETIME5字节VARCHAR变长2字节允许NULL额外1字节。不需要背公式关注相对变化即可——如果联合索引3个字段但key_len只有第1个字段的长度说明只用了第1个字段。三、常见低效SQL模式及优化3.1 全表扫描 → 加索引-- ❌ 没有索引typeALL扫描全表SELECT*FROMordersWHEREcabinet_id1;-- 优化加索引CREATEINDEXidx_cabinetONorders(cabinet_id);-- ✅ 优化后 typeref只扫描匹配行SELECT*FROMordersWHEREcabinet_id1;3.2 子查询 → JOIN-- ❌ 低效子查询每个商品都要执行一次子查询SELECTproduct_name,priceFROMproductWHEREproduct_idIN(SELECTproduct_idFROMordersWHEREpay_status1);-- ✅ 优化改成JOIN一次执行SELECTDISTINCTp.product_name,p.priceFROMproduct pINNERJOINorders oONp.product_ido.product_idWHEREo.pay_status1;MySQL 5.6优化器会自动将某些子查询优化为半连接(Semi-Join)但不是所有场景都能自动优化。写SQL时优先用JOIN。3.3 OR → UNION-- ❌ 低效OR两侧有一侧无索引时整条查询不走索引SELECT*FROMordersWHEREcabinet_id1ORpay_status0;-- ✅ 优化拆成UNION各自走索引SELECT*FROMordersWHEREcabinet_id1UNIONSELECT*FROMordersWHEREpay_status0ANDcabinet_id!1;3.4 LIMIT深分页优化-- ❌ 深分页偏移量越大越慢-- LIMIT 1000000, 20 → MySQL要扫描100万20行丢弃前100万行SELECT*FROMordersORDERBYcreated_atDESCLIMIT1000000,20;-- ✅ 优化方案1用主键定位推荐-- 记住上一页最后一条记录的order_idSELECT*FROMordersWHEREorder_id#{last_order_id}ORDERBYorder_idDESCLIMIT20;-- ✅ 优化方案2延迟关联-- 先用索引查出主键再关联取完整行SELECTt.*FROMorders tINNERJOIN(SELECTorder_idFROMordersORDERBYcreated_atDESCLIMIT1000000,20)tmpONt.order_idtmp.order_id;深分页为什么慢LIMIT 1000000, 20不是跳过100万行而是扫描100万20行然后丢弃前100万行。扫描操作仍然消耗资源。延迟关联方案让子查询走覆盖索引只取主键速度极快然后只回表20次。3.5 GROUP BY优化-- ❌ 低效GROUP BY字段无索引Using temporary Using filesortSELECTcabinet_id,COUNT(*)FROMordersGROUPBYcabinet_id;-- ✅ 优化给cabinet_id加索引CREATEINDEXidx_cabinetONorders(cabinet_id);-- Using index利用索引天然有序无需临时表和排序3.6 ORDER BY优化-- ❌ 低效ORDER BY字段和WHERE条件不是同一个索引SELECT*FROMordersWHEREcabinet_id1ORDERBYcreated_atDESC;-- 如果只有idx(cabinet_id)排序用filesort-- ✅ 优化建立联合索引(cabinet_id, created_at)CREATEINDEXidx_cabinet_createdONorders(cabinet_id,created_at);-- 索引本身按cabinet_id再按created_at排序直接利用索引顺序Using index核心原理利用B树索引的有序性让WHERE过滤和ORDER BY排序都能用到同一个索引避免filesort。3.7 避免SELECT *-- ❌ 低效查询所有列无法利用覆盖索引SELECT*FROMordersWHEREcabinet_id1;-- ✅ 优化只查需要的列SELECTorder_id,cabinet_id,total_amountFROMordersWHEREcabinet_id1;-- 如果(cabinet_id, order_id, total_amount)有联合索引Using index四、慢查询日志找出慢SQL4.1 开启慢查询日志-- 查看慢查询配置SHOWVARIABLESLIKEslow_query%;SHOWVARIABLESLIKElong_query_time;-- 开启慢查询日志运行时生效重启失效SETGLOBALslow_query_logON;SETGLOBALlong_query_time1;-- 超过1秒的记录SETGLOBALslow_query_log_file/var/log/mysql/slow.log;-- 永久生效需在my.cnf配置-- [mysqld]-- slow_query_log 1-- long_query_time 1-- log_queries_not_using_indexes 1 -- 记录不走索引的查询4.2 分析慢查询日志# 用mysqldumpslow工具分析# 按总耗时排序mysqldumpslow-st-t10/var/log/mysql/slow.log# 按次数排序mysqldumpslow-sc-t10/var/log/mysql/slow.log# 输出示例# Count: 52 Time3.2s Lock0.0s Rows500000# SELECT * FROM orders WHERE cabinet_id N AND DATE(created_at) S参数含义Count该SQL出现次数Time平均执行时间Lock平均锁定时间Rows平均扫描行数mysqldumpslow会把具体参数替换成N或S方便聚合相同结构的SQL。按-s t排序优先优化耗时最长的SQL。4.3 使用pt-query-digest更强大# Percona Toolkit提供的分析工具pt-query-digest /var/log/mysql/slow.log# 输出更详细每条SQL的执行次数、总耗时、平均耗时、# 标准差、95%线、执行计划等五、实战案例无人售货柜订单查询优化场景描述无人售货柜平台orders表有500万条数据。运营后台查询某台柜子最近7天的订单列表耗时8秒。分析过程-- 原始SQLSELECTorder_id,cabinet_id,product_id,total_amount,pay_status,created_atFROMordersWHEREcabinet_id5ANDcreated_atDATE_SUB(NOW(),INTERVAL7DAY)ORDERBYcreated_atDESCLIMIT0,20;第一步Explain分析EXPLAINSELECTorder_id,cabinet_id,product_id,total_amount,pay_status,created_atFROMordersWHEREcabinet_id5ANDcreated_atDATE_SUB(NOW(),INTERVAL7DAY)ORDERBYcreated_atDESCLIMIT0,20;执行计划结果typekeyrowsExtrarefidx_cabinet80000Using where; Using filesort分析typeref用了idx_cabinet索引但只匹配cabinet_idrows80000预估扫描8万行——7天数据有8万条Using filesortcreated_at排序没走索引需要额外排序还要回表取product_id、total_amount、pay_status优化步骤优化1建立联合索引CREATEINDEXidx_cabinet_createdONorders(cabinet_id,created_at);再次ExplaintypekeyrowsExtrarefidx_cabinet_created80000Using whereUsing filesort消失了——索引本身按(cabinet_id, created_at)排序但rows还是8万因为7天确实有8万条数据优化2覆盖索引避免回表查询需要6个字段但联合索引只有2个字段。扩展联合索引-- 建立覆盖联合索引CREATEINDEXidx_coveringONorders(cabinet_id,created_at,product_id,total_amount,pay_status,order_id);再次ExplaintypekeyrowsExtrarefidx_covering80000Using where; Using indexUsing index覆盖索引不需要回表但rows还是8万——这是查询范围本身的数据量无法进一步减少优化3精准分页如果用户只看前20条实际上不需要扫描8万行。利用索引有序性-- 最终优化SQLSELECTorder_id,cabinet_id,product_id,total_amount,pay_status,created_atFROMordersFORCEINDEX(idx_covering)WHEREcabinet_id5ANDcreated_atDATE_SUB(NOW(),INTERVAL7DAY)ORDERBYcreated_atDESCLIMIT0,20;实际执行时MySQL会利用索引的有序性从索引末尾倒序扫描找到20条就停止。实际扫描行数远小于8万。优化效果优化阶段耗时说明优化前8.0s全表扫描filesort大量回表加联合索引1.2s消除filesort覆盖索引0.3s消除回表精准分页0.02s利用索引有序性提前终止从8秒到0.02秒400倍提升。这就是索引优化的威力。优化总结流程发现慢SQL → EXPLAIN分析执行计划 → type是否为ALL→ 加索引 → Extra是否有filesort→ 让ORDER BY走索引 → Extra是否有temporary→ 优化GROUP BY → rows是否过大→ 缩小查询范围或覆盖索引 → 是否回表→ 建覆盖索引 → 确认优化后Explain结果改善 → 测试实际执行耗时总结优化手段解决的问题效果加索引全表扫描rows大幅下降JOIN替代子查询多次子查询执行执行计划简化UNION替代OR索引失效各分支独立走索引延迟关联LIMIT深分页避免扫描大量行覆盖索引回表开销Using index省去回表联合索引filesort回表一次索引搞定过滤排序取数据慢查询优化不是玄学核心就三板斧Explain看计划、加对索引、避免回表。掌握了这套方法论大部分慢SQL都能手到擒来。