001、性能优化基础：慢SQL诊断与执行计划分析

昨天凌晨又被告警短信吵醒了线上某核心接口的P99响应时间飙到了3秒。登录服务器一看MySQL的CPU已经跑满processlist里堆了二十几个相同的查询——又是慢SQL惹的祸。这种场景咱们做后端开发的太熟悉了今天就来聊聊怎么系统性地抓出这些“性能杀手”。慢SQL从哪里来先别急着看代码第一件事是确认问题范围。MySQL自带的慢查询日志是最直接的线索源但很多人配置得不对。我习惯这样设置-- 临时开启立即生效SETGLOBALslow_query_logON;SETGLOBALlong_query_time1;-- 超过1秒就算慢查询SETGLOBALslow_query_log_file/var/log/mysql/slow.log;SETGLOBALlog_queries_not_using_indexesON;-- 这个特别有用-- 永久配置得改my.cnf记得重启这里踩过坑生产环境别把long_query_time设得太低否则日志文件瞬间爆炸。通常从1秒开始压测时可以调到0.1秒抓细节。日志有了怎么分析直接看原始日志太累用mysqldumpslow工具# 按累计时间排序看最耗时的SQL类型mysqldumpslow-st /var/log/mysql/slow.log# 按出现次数排序找最频繁的慢查询mysqldumpslow-sc /var/log/mysql/slow.log# 最近10条慢查询mysqldumpslow-t10/var/log/mysql/slow.log更高级点可以用pt-query-digest它能生成HTML报告直接告诉你哪些SQL是“罪魁祸首”。我一般先看“总耗时占比前3”的查询优化它们往往能解决80%的问题。EXPLAIN是你的显微镜找到可疑SQL后别急着改先让它“现出原形”。EXPLAIN命令是MySQL给的诊断神器但很多人看不懂输出。咱们拆开说EXPLAINSELECT*FROMordersWHEREuser_id10086ANDstatuspendingORDERBYcreate_timeDESC;看输出要盯紧这几个关键字段type列这是访问类型性能从好到坏大概是system const eq_ref ref range index ALL看到ALL就是全表扫描赶紧加索引index是全索引扫描虽然比ALL好点但数据量大时也慢key列实际用到的索引。如果这一列是NULL说明没用到索引——大问题。rows列MySQL估计要扫描的行数。这个数跟实际数据量差太多时说明统计信息过期了跑个ANALYZE TABLE更新下。Extra列这里藏了很多细节。看到Using filesort说明有额外的排序操作Using temporary用了临时表都是性能瓶颈点。那些年我们踩过的索引坑上周就遇到个典型案例开发同学加了索引但查询还是慢。EXPLAIN一看索引确实用了但rows扫了50万行。原来他的查询条件是SELECT*FROMuser_logWHEREDATE(create_time)2023-10-01;问题出在DATE()函数上——对字段做函数操作会让索引失效。改成范围查询就快了WHEREcreate_time2023-10-01ANDcreate_time2023-10-02类似的坑还有隐式类型转换WHERE user_id 123如果user_id是整型字符串转换会让索引失效前导模糊匹配LIKE %keyword%用不了索引LIKE keyword%可以OR条件不当WHERE a1 OR b2如果a和b字段都有索引可能会走index_merge但效率往往不如单独索引执行计划会骗人注意了EXPLAIN显示的是“预估”执行计划不是实际执行的。有时候它说用索引A实际跑了用索引B。MySQL 8.0有个好东西EXPLAINANALYZESELECT...;这个会真正执行查询小心生产环境然后给出实际耗时和预估的对比。我遇到过预估扫描1000行实际扫了10万行的情况原因是统计信息太久没更新。个人工具箱里的私货诊断时一定要带真实数据测试环境数据量太小执行计划可能跟生产完全不一样。用真实数据脱敏后复现或者用pt-query-playback模拟生产负载。关注索引选择性性别字段加索引基本没用因为就两种值。索引选择性不重复值/总行数低于0.1的索引要慎重考虑。联合索引注意顺序(a,b,c)索引能查a、(a,b)、(a,b,c)但查不了b或(b,c)。把高频查询字段放前面。别迷信索引覆盖SELECT *大概率回表用EXPLAIN看Extra列有没有Using index。如果频繁查询某几个字段考虑建包含这些字段的联合索引。定期检查索引使用情况跑这个查询看看哪些索引从来没用过SELECT*FROMsys.schema_unused_indexes;无用索引该删就删每个索引都有维护成本。最后说个心态问题慢SQL优化不是一劳永逸的。业务数据量变了查询模式变了今天快的SQL明天可能就慢了。养成定期巡检的习惯把慢查询监控做成dashboard放在团队大屏上比救火式优化管用得多。下次咱们聊索引设计原则你会看到更多“我当初怎么就没想到”的案例。