做数据库运维和性能优化这些年我越来越确认一件事oracle、mysql、pgsql就是PostgreSQL老哥们习惯叫pgsql这三类主流数据库虽然内部机制一个比一个复杂但慢SQL排查这条主线其实是相通的。很多人一接到慢SQL告警就慌打开慢查询日志翻半天看到几条耗时高的SQL就丢给开发一句“你优化一下”这基本等于白干。真正的排查应该是从“发现”到“定位”再到“验证”的一套完整闭环今天我就把这三库通用的排查思路、具体命令和踩过的坑一次说清楚不管是DBA还是后端开发照着走能少走很多弯路。这篇文章会覆盖三类数据库各自的慢SQL发现手段、执行计划解读方法、常见慢SQL根因分析和优化实操最后还会整理几个我实际工作中反复遇到的排查陷阱。内容偏实操但原理层面我也会尽量讲透毕竟不知道“为什么”的话换个场景你照样不会排查。1. 慢SQL排查的本质先定位再诊断后验证慢SQL排查不是玄学它本质上是一套标准化的诊断流程。核心就三句话先把出问题的SQL找出来再看它的执行计划最后确认根因并验证优化效果。这一节先把整体思路捋清楚后面才不会被细节带偏。1.1 三个数据库一条排查主线很多人有个误区觉得oracle、mysql、pgsql差异太大排查方式肯定完全不同。但实际上绝大部分慢SQL问题都能归到这几类表扫描方式不对该走索引没走、数据量增长后执行计划老化、SQL写法本身有问题、统计信息过期导致优化器判断失误、还有资源竞争和锁等待。这三类数据库在这条主线上的排查路径高度一致找到慢SQLOracle用AWR/ASH/v$视图MySQL用慢查询日志/performance_schemaPostgreSQL用pg_stat_statements/auto_explain。拿到该SQL的执行计划三库都有EXPLAIN只是格式和侧重不同。分析执行计划里的异常点全表扫描、行数估算偏差、连接顺序问题、额外排序等。定位根因并优化加索引、改写SQL、更新统计信息、调整参数等。回归验证确认执行时间和资源消耗降下来。区别只在细节。Oracle是商业数据库自带一整套诊断工具体系最完善但也最复杂MySQL用户量最大、网上资料最多慢查询日志简单直接但很多人在执行计划解读上卡壳PostgreSQL这几年势头很猛性能和功能都很能打但排查工具相对要自己配置得多很多新手上来连日志都没开更别提什么auto_explain了。1.2 排查前必须先想清楚的四件事我见过太多人一上来就查日志、看执行计划结果忙了半天方向压根就是错的。动手之前先花五分钟问自己几个问题第一这条慢SQL是突发的还是一直就慢如果是某个时间点之后突然变慢那大概率是执行计划变了、统计信息更新了、或者数据量跨过了一个量级门槛重点往这些方向查。如果是一直慢那基本是SQL本身或索引设计的问题属于累积欠账。第二影响范围有多大是一条SQL慢还是一批SQL都慢如果批量的慢那问题可能不在SQL本身而是数据库整体资源出问题了比如CPU打满、磁盘IO异常、连接池耗尽、锁等待严重。这种情况你先排查全局再盯单条SQL不然光优化一条SQL解决不了整体问题。第三有没有最近变更我排查过太多案例最后根因都是前一天有人改了索引、更新了统计信息、升级了版本、甚至调整了一个看似无关的参数。做侦查首先要问“最近动了什么”这个习惯比什么高级命令都管用。第四测试环境能不能复现如果测试环境复现不了基本都是数据量级或数据分布差异导致的执行计划不同。这时候与其死磕本地不如直接去生产采集信息。这些问题想清楚了再开始动手效率会高很多。2. 把慢SQL从数据库里“揪”出来三库各自的手法慢SQL的发现渠道每种数据库不一样但目标一致找出一段时间内消耗资源最多、执行时间最长的SQL语句。下面按库分别说。2.1 Oracle从AWR/ASH到v$视图逐层下钻Oracle的排查体系是三库里最成熟的。正常情况下生产Oracle都会开启AWRAutomatic Workload Repository默认每小时生成一个快照保留8天。AWR报告里有一块“SQL ordered by Elapsed Time”这就是Oracle自己帮你排好序的慢SQL清单包含执行次数、总耗时、平均耗时、CPU耗时、物理读等关键指标非常直观。很多老DBA会直接看AWR的Top Events如果发现某个等待事件占比很高再去关联具体的SQL。比如DB Time里大量时间花在db file sequential read上说明有大量单块读往往和索引扫描有关如果大量时间花在db file scattered read上则通常和大范围扫描或全表扫描有关。如果要看实时或近期的慢SQL可以直接查v$视图。我最常用的几个-- 按总耗时排序取TOP 10 SELECT * FROM ( SELECT sql_id, elapsed_time, cpu_time, buffer_gets, disk_reads, executions, ROUND(elapsed_time/1000000, 2) AS elapsed_sec, SUBSTR(sql_text, 1, 100) AS sql_text FROM v$sqlarea ORDER BY elapsed_time DESC ) WHERE ROWNUM 10;v$sqlarea存的是SQL的汇总信息按SQL文本聚合。如果按单次执行平均耗时排序把排序字段换成elapsed_time/executions即可。更细粒度的可以用v$sql它每条游标一行能对应到具体的plan_hash_value执行计划哈希值。同一个SQL出现多个不同plan_hash_value说明执行计划不稳定这在排查性能抖动时是个重要信号。如果SQL还在运行中可以直接查v$session_wait或v$session去看它在等什么事件。结合gv$active_session_history也就是ASH视图还能看到历史采样定位某一个时间窗口内到底哪些会话在跑什么样的SQL、等待什么资源诊断那种“偶发慢”特别有用。Oracle还有一个专门的自动化工具——SQL Tuning Advisor通过DBMS_SQLTUNE包调用它会主动帮你分析SQL并给出加索引、改写SQL等建议。虽然是黑盒但对一些复杂场景很有启发价值值得一试。2.2 MySQL慢查询日志和performance_schema的组合拳MySQL的慢SQL排查门槛最低核心就是慢查询日志。很多人生产环境不开慢查询日志等出了事才想起来开结果历史数据早没了。提前配置非常关键而且参数设置要合理。# my.cnf 配置示例 slow_query_log ON slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes ON几个参数说一下long_query_time表示超过多少秒就算慢SQL生产环境我建议先设1秒甚至0.5秒太大会漏掉很多“单看不慢、并发就炸”的SQL太小日志量会暴涨磁盘受不了。log_queries_not_using_indexes打开了会记录所有没走索引的查询这个数据量一般比较大适合前期排查用稳定运行期可以关掉不然日志增长太快。日志文件本身是文本格式直接用mysqldumpslow做聚合最方便# 按总耗时排序显示前10条 mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log # 按平均耗时排序 mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log注意mysqldumpslow会把SQL里的具体数字替换成“N”方便同类SQL聚合。不过它只能做简单统计如果想看更细的维度推荐用Percona Toolkit里的pt-query-digest能生成一份非常详细的HTML报告包含每条SQL的响应时间分布、历史趋势、执行计划建议等做深度分析非常好用。除了慢日志MySQL 5.7的performance_schema和sys库也很好用。比如查看实例整体Top SQL-- 查看按总执行时间排序的TOP语句 SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;如果遇到SQL正在运行但日志里还没刷出来的情况可以用SHOW FULL PROCESSLIST直接看当前正在跑的线程和SQL配合KILL命令应急处理堵死的会话。2.3 PostgreSQLpg_stat_statements与auto_explain的配合PostgreSQL可以说是三库里“默认配置最不友好”的——它默认不记录慢SQL也不生成AWR报告商业版有社区版没有需要自己配置扩展。好消息是配置完以后效果很好。第一步启用pg_stat_statements扩展。它是PostgreSQL最核心的SQL统计工具需要在数据库启动时加载# postgresql.conf shared_preload_libraries pg_stat_statements改完这个参数必须重启数据库然后执行CREATE EXTENSION IF NOT EXISTS pg_stat_statements;之后就能通过视图查看SQL统计了SELECT query, calls, total_exec_time, mean_exec_time, rows, shared_blks_hit, shared_blks_read FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;这里total_exec_time单位是毫秒是整个累计执行时间。PostgreSQL 13之前字段名是total_time13之后改成total_exec_time注意区分。pg_stat_statements默认记录的前5000条SQL可以调通过统计信息就能把“总耗时最高”和“平均耗时最高”的SQL拉出来。第二步配置日志记录慢SQL和自动执行计划。如果只想记录超过指定时间的SQL可以设置log_min_duration_statement 1000 # 单位毫秒超过1秒的SQL记录到日志比MySQL方便的地方在于PostgreSQL可以直接把执行计划一起打到日志里这就是auto_explain模块shared_preload_libraries pg_stat_statements, auto_explain auto_explain.log_min_duration 1s auto_explain.log_analyze on auto_explain.log_buffers on这样当一条SQL执行超过1秒时日志里会直接打印带ANALYZE信息的执行计划省去你事后手动EXPLAIN的步骤。生产强烈建议开这个功能代价很小但对问题回溯极其重要。如果SQL还在跑可以用pg_stat_activity查看当前活跃会话SELECT pid, state, query, now() - query_start AS duration FROM pg_stat_activity WHERE state active AND query NOT LIKE %pg_stat_activity% ORDER BY duration DESC;有人会问前面不是说“先看全局再看单条”吗这部分为什么直接讲怎么找慢SQL因为找SQL本身就是“看全局”的一部分——你要先知道当下哪些SQL是慢的才谈得上进一步定位。真正要避免的是一上来就用EXPLAIN盯着一条SQL死磕而忽略了实例整体状况。3. 拿到执行计划才算真正开始诊断找到慢SQL只是第一步真正的诊断从拿到执行计划才开始。执行计划就是优化器这双眼睛看到的“数据访问路径”——它是通过索引点查还是全表扫先关联哪张表在哪一步排序全都写在计划里。三库的执行计划格式差异不小解读重点也各不相同。3.1 Oracle执行计划怎么读Oracle生成执行计划最简单的方式是EXPLAIN PLAN FOR SELECT /* 你的SQL */ ...; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);对于已经跑过的SQL可以直接从共享池里取它的真实执行计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id, 0, ALLSTATS LAST));如果SQL已经不在共享池了但还在AWR快照范围内可以用DISPLAY_AWRSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(sql_id));读Oracle执行计划要把握几个关键点。第一执行顺序是从内到外、从上到下缩进越深的先执行。第二关注Cost代价、Rows估算行数、Time估算时间三列它们都是优化器基于统计信息算出来的估值。第三重点观察是不是出现了TABLE ACCESS FULL全表扫描以及连接方式是什么——NESTED LOOPS适合小表驱动大表且内层走索引的场景HASH JOIN适合大表等值连接MERGE JOIN适合有序数据或非等值连接。最实用的一招是看“Rows”和“A-Rows”的差距。用EXPLAIN PLAN FOR是拿不到实际行数的必须用DBMS_XPLAN.DISPLAY_CURSOR且SQL要开了statistics_levelALL才能看到A-Rows。当估算行数和实际行数差了好几个数量级时优化器选错执行计划的概率极高这就是要重点排查统计信息的地方。3.2 MySQL EXPLAIN的关键信息MySQL的执行计划用EXPLAIN直接看EXPLAIN SELECT * FROM orders WHERE user_id 123 ORDER BY create_time DESC;输出结果里字段很多但核心就看这几列字段含义重点关注type访问类型从好到差system const eq_ref ref range index ALLkey实际使用的索引NULL表示没走索引rows估算扫描行数与表总行数对比判断效率Extra附加信息Using filesort、Using temporary都是性能杀手type这一列怎么理解const表示通过主键或唯一索引查到一行性能最好ref表示通过非唯一索引等值匹配range表示索引范围扫描比如BETWEEN、、index表示全索引扫描虽然用了索引但还是要遍历整棵索引树ALL就是最差的全表扫描。rows是优化器估算的行数不是实际值小表大偏差无所谓大表偏差会导致执行计划质量严重下降此时需要ANALYZE TABLE刷新统计信息。Extra列里最烂的两个词是Using filesort和Using temporary。出现这两个基本说明排序或去重没走索引MySQL在临时表里干的活数据量一大肯定慢。遇到这种情况优先考虑调整索引让排序字段和WHERE条件组成复合索引。3.3 PostgreSQL EXPLAIN ANALYZE的实际成本PostgreSQL的执行计划功能在三库里是最强的关键区别在于它支持EXPLAIN ANALYZE会真实执行SQL并输出实际耗时和实际行数EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id 123;注意ANALYZE是真的会执行SQL的生产环境的UPDATE/DELETE千万别直接EXPLAIN ANALYZE要用事务回滚包住或者只对SELECT用。执行计划里最核心的对比是actual time和rows。如果actual rows和计划里预估的rows差很多说明统计信息不准确。比如估算扫描1000行实际扫了100万行就是统计严重失真。这时候大概率是要跑一次ANALYZE。PostgreSQL里还有个概念叫cost没实际跑SQL时看到的都是估算值。它有两个关键参数影响索引选择seq_page_cost顺序读成本默认1.0和random_page_cost随机读成本默认4.0。如果你的数据库跑在SSD上random_page_cost建议调低到1.1~1.5否则优化器会过于偏向顺序扫描全表扫描宁可全表扫也不用索引这属于“执行计划不合理的隐形原因”。另外PostgreSQL执行计划里经常出现Seq Scan on xxx (cost0.00..1543.45 rows54321)类似的输出cost0.00..1543.45是启动成本到总成本的估值行数是优化器估算值。结合ANALYZE的actual time才能判断真实情况。4. 常见慢SQL场景与优化实战找到慢SQL、读完执行计划接下来就是定位根因并动手优化。这一节我把实际工作中最常见的慢SQL场景分类整理出来每种都给优化思路和示例。4.1 索引失效的几种典型情况花了半天时间建了索引SQL还是慢查一下执行计划发现压根没走索引这是最让人抓狂的场景。常见的索引失效原因就那几类第一对索引列进行了运算或函数操作。典型如-- 假设create_time列有索引 SELECT * FROM orders WHERE TRUNC(create_time) DATE 2024-01-01;TRUNC函数把create_time处理后再比较索引自然失效。改成范围查询就能走索引SELECT * FROM orders WHERE create_time DATE 2024-01-01 AND create_time DATE 2024-01-02;第二隐式类型转换。这个坑在MySQL里特别多。比如user_id是varchar类型查询写成WHERE user_id 123数字MySQL会把字段隐式转成数字再做比较索引直接失效。排查方法很简单看执行计划里key列是不是NULL。修复方法是把SQL改成WHERE user_id 123。第三LIKE前缀模糊匹配。LIKE %abc%这种写法除非用全文索引或者生成列否则常规B-tree索引无能为力。能改成LIKE abc%就会走索引范围扫描。第四OR条件连接了非索引列。比如WHERE status 1 OR user_id 123如果status没索引优化器就没法用user_id的索引走了全表扫描。改写方式是用UNION ALL拆成两条SQL或者给两个列都建索引并用索引合并MySQL 5.0对OR条件有index merge优化但不总是稳定。第五复合索引违反最左前缀原则。比如建了(a, b, c)复合索引查询条件是WHERE b 1 AND c 2完全用不上这个索引。解决方法是调整复合索引的列顺序或者单独为常用查询建新索引。设计复合索引时把等值查询的列放前面范围查询的列放后面通常能最大化命中率。4.2 查询写法的问题与改写套路有些慢SQL执行计划没问题索引也在走但就是慢问题出在查询本身的设计上。首当其冲的是SELECT *。把不需要的列全查出来不仅增加IO和网络传输还会让覆盖索引失效。比如表有(a, b, c)三列建了(a, b)复合索引SELECT a, b能被索引覆盖完全不需要回表但SELECT *就必须回表读整行数据性能差距非常明显。这个在分页和列表查询场景下影响尤其大。深分页问题也是重灾区。MySQL/PostgreSQL常用的LIMIT 100000, 20优化器要先把前100000条数据扫出来再丢掉代价随偏移量线性增长。改写思路是换成“游标分页”或者“延迟关联”-- 普通深分页慢 SELECT * FROM orders ORDER BY id LIMIT 100000, 20; -- 延迟关联先查ID再回表 SELECT o.* FROM orders o JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) t ON o.id t.id;延迟关联的精髓是让子查询只扫描索引而不回表代价低很多。这个技巧在三库里通用我实测在MySQL上能把深分页响应时间从几秒优化到几十毫秒。还有一类经典问题NOT IN子查询和OR条件。NOT IN在小数据量下可能没啥感觉但大表上经常被优化器转换成低效的执行路径。通用的改写策略是-- 原写法 SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blacklist); -- 改写为NOT EXISTS通常更高效 SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.user_id u.id);改写时最好用真实数据对比两条SQL的执行计划再决定避免“为了改写而改写”。子查询改JOIN也是常规操作尤其是MySQL 5.6之前子查询优化还不完善但5.7之后的大多数子查询会被自动优化成semi-join其实不需要手动改太多。PostgreSQL的LATERAL JOIN则很适合“对每行取TOP N”的关联场景算是它的独门绝技遇到这类需求别再用窗口函数硬扛试试LATERAL JOIN效果会很惊喜。4.3 统计信息与执行计划老化问题很多慢SQL其实昨天还跑得好好的今天突然慢了。看执行计划发现访问路径变了但SQL本身一个字都没改。这种“执行计划突变”十有八九是统计信息过期或者不准导致的。数据库优化器做决策依赖统计信息统计信息反映的是表的数据量和数据分布。当表数据量发生剧烈变化比如原来100万行突然删到1万行或者反过来统计信息没及时更新优化器就会按旧认知做决策选择错误的执行计划。对应到三个数据库处理方法分别是OracleEXEC DBMS_STATS.GATHER_TABLE_STATS(ownname SCOTT, tabname ORDERS);生产环境注意加estimate_percent和degree参数避免全量统计太耗资源。MySQLANALYZE TABLE orders;注意InnoDB的统计信息是采样估算的如果innodb_stats_persistentON8.0默认统计信息持久化存储手动ANALYZE后要确认是否真的刷新了。PostgreSQLANALYZE orders;普通表可以手动执行。关键区别是PostgreSQL的autovacuum默认会周期性自动分析阈值由autovacuum_analyze_threshold与autovacuum_analyze_scale_factor控制所以大多数情况下不用手动干预但遇到大事务大批量更新后自动分析可能跟不上手动ANALYZE一把就能快速解决问题。除了统计信息本身PostgreSQL还有一个独有的坑表膨胀。因为MVCC机制PostgreSQL里被更新或删除的行不会立即物理清理而是留在表里需要VACUUM去清理。如果autovacuum跟不上或者长期不清理表膨胀得厉害即使走索引扫描扫描的页面还是变多性能一样退化。排查手段是查pg_stat_user_tables里的n_dead_tup和last_vacuum字段死元组比例过高就手动VACUUM VACUUM FULL。MySQL方面类似的问题是大量更新删除后索引碎片化优化手段是OPTIMIZE TABLE orders;底层会重建表。Oracle的话段空间碎片和行迁移也可能影响性能需要定期整理或使用SHRIINK SPACE。5. 实际排查中踩过的坑与避坑实录最后这部分我把这些年慢SQL排查里踩过的最典型的几个坑整理出来希望能帮大家少花冤枉时间。这些问题在一些书和文档里很少详细写但对实际工作的影响极大。5.1 日志没开或时间设置不对事后只能干瞪眼最痛的一次经历凌晨两点被开发电话叫起来说核心接口响应时间暴涨到了白天想查慢SQL发现生产MySQL的slow_query_log压根没开Oracle的AWR虽然开着但没保存足够长的历史PostgreSQL那边log_min_duration_statement没配置什么都没记录到。最后只能靠猜花了整整一天才定位到是一条临时表查询没走索引。所以第一课永远是这些排查设施一定要提前配好而不是出问题才开。我给团队立的规矩是所有数据库实例上线前检查四项配置——慢查询日志/统计扩展必须开启、日志保留时间不少于7天、采集频率合理、告警阈值根据业务设定MySQL和PostgreSQL的long_query_time/log_min_duration_statement建议先设1秒观察几天如果噪音太大再调高不要一开始就设10秒。如果确实没日志可查也有应急手段。比如MySQL可以用performance_schema的events_statements_history_long前提是打开了PostgreSQL可以用pg_stat_statements前提还是提前装了扩展Oracle可以用ASH但历史数据也只保留有限时间。说到底事后的临时补救手段都很受限提前配置才是王道。5.2 测试环境复现不了生产才有问题开发经常说“我本地跑SQL很快啊怎么生产就慢”这背后的原因一般是环境差异数据量差太远、硬件配置不同、参数设置不同、统计信息不同。本地表几千行生产表上千万行执行计划自然不一样本地走个全表扫描都没感觉生产就会出大事。应对方法是尽量把生产环境的慢SQL信息和执行计划一起采集回来包括绑定变量值、统计信息、执行计划、等待事件。Oracle可以用DBMS_XPLAN.DISPLAY_CURSOR直接取真实计划MySQL可以EXPLAIN后同时导出表统计信息SHOW TABLE STATUS、SHOW INDEX FROM等PostgreSQL可以用EXPLAIN (ANALYZE, BUFFERS)加auto_explain日志。有了这些信息即使本地复现不了也能在分析层面找到问题。5.3 只看单条SQL忽略了并发和资源竞争有一次线上一个查询单独拿出来EXPLAIN ANALYZE跑只要0.2秒执行计划也很健康但线上就是慢平均响应时间超过3秒。查了半天发现是同一张表上同时有大量UPDATE在跑行锁竞争严重SELECT只能排队等锁问题根本不在查询本身。这种场景下最有效的排查手段是看等待事件和锁。Oracle的v$session_wait、MySQL的SHOW ENGINE INNODB STATUS里的事务与锁信息、PostgreSQL的pg_locks视图都能快速定位锁冲突。记住一条经验遇到“单条SQL不慢、线上批量慢”的情况优先看锁等待和并发队列别一条SQL一条SQL地瞎调。5.4 优化完不验证改完等于白改很多人的习惯是加了个索引就完事了不做回归验证过了一个月发现这条SQL又慢了——其实第一次可能压根就没把问题真正解决只是碰巧掩盖了。我做优化的标准动作是优化前记录基线执行时间、执行计划、关键等待事件优化后跑同一场景反复测多次取平均值和P95对比基线看有没有改善然后观察几天线上指标确认没有引入新问题比如加了索引虽然让查询快了但加大了写入负担。如果优化涉及索引或SQL改写最好把优化前后的执行计划保存下来作为存档方便后续追溯。5.5 最终极的坑把责任推给“数据库慢”还有一类问题特别容易让人走偏明明SQL执行计划很健康单条耗时也不高但整体并发一高就卡死。这种情况根因往往不在SQL而是连接池配置太小、应用线程池阻塞、网络延迟、硬件资源不足。排查慢SQL不能只盯着数据库要结合应用的调用链、监控指标一起看。我记得有一次排查一个“慢SQL”问题查到最后发现是数据库服务器所在的宿主机上其他应用疯狂吃磁盘IO数据库本身一点毛病都没有。所以最后想提醒大家的是慢SQL排查虽然是数据库领域的话题但思路一定要开阔必要时要在应用层、系统层、基础设施层一起找证据才能找到真正的根因。我个人这些年的体会是排查慢SQL没有太多玄学就是把流程跑完整把证据链做实。Oracle、MySQL、PostgreSQL三大数据库的工具各家有各家的好但排查思路永远是“先全局后局部、先定位后诊断、先证据后结论”。如果你刚开始接触这块建议从自己最常用的那一种数据库入手把上面的流程完整梳一遍再横向对比其他数据库很快就能建立一套适合自己团队的方法论。