1. 一条慢SQL引发的思考优化到底在优化什么先说个我自己的真实经历。几年前接手过一个内部报表系统数据量大概几百万行不算夸张但每天凌晨的统计任务总是跑不完经常早上上班一看报表还是空的。当时第一反应是加索引、改SQL折腾了两天效果有但不明显。后来慢慢排查才发现问题根本不在SQL本身而是应用层每次查询都把所有结果拉到内存里再在Java代码里做分组和排序白白浪费了几百兆网络和内存开销。那次之后我明白了一个道理SQL性能优化从来都不是单纯地改一条语句而是一个从需求分析到执行计划再到应用层交互的系统工程。很多刚入行的同学一听到“SQL性能优化”就以为是要背一堆技巧比如少用SELECT *、用EXISTS代替IN、加索引等等。这些技巧没错但碎片化的技巧解决不了系统性的问题。这篇文章我就从一条具体的慢SQL出发完整地走一遍优化的整个流程从定位问题、阅读执行计划、改写语句到索引设计、参数调整再到应用层的配合。你能看到每个步骤背后的判断依据而不是单纯地背结论。我假设你已经会写基本的SQL知道JOIN、GROUP BY、子查询大概是什么但对“为什么这条SQL慢”还没有系统的排查思路。这篇文章不会堆砌命令和参数而是用一条真实场景中会遇到的查询把优化的每一步拆开给你看同时把那些文档里不会写的坑和判断经验一并交代清楚。2. 优化前先搞清楚三件事成本、基数和执行计划2.1 不要凭感觉找慢SQL先量化成本慢SQL的“慢”是一个模糊的说法。一条查询跑3秒算不算慢在OLTP在线事务处理系统里单次查询超过500毫秒可能就该警惕在OLAP联机分析处理报表场景里一个复杂聚合跑30秒可能也能接受。所以在动手优化前第一步永远是量化。我最常用的做法是先看数据库自己的统计信息。以MySQL为例打开慢查询日志和性能分析-- 查看是否开启慢查询日志 SHOW VARIABLES LIKE slow_query_log; -- 查看慢查询阈值(单位:秒) SHOW VARIABLES LIKE long_query_time; -- 开启慢查询日志(重启后失效,生产环境慎用) SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;还有一种快速定位办法直接查数据库的当前连接和运行中的语句SELECT id, user, host, db, command, time, state, info FROM information_schema.processlist WHERE command Query AND time 5 ORDER BY time DESC;这条SQL能让你在数据库卡顿的时候第一时间看到哪条语句在跑、跑了多久、当前处于什么状态比如Sending data、Sorting result、Copying to tmp table。这些状态信息本身就是定位问题的线索如果大量连接卡在Sending data说明存储引擎在扫数据如果卡在Sorting result说明排序压力大如果卡在Creating sort index或Copying to tmp table说明临时表用得太多了。顺带提一句Oracle的做法。Oracle里可以通过V$SQL视图按执行次数、逻辑读、物理读等维度排序找出Top SQLSELECT sql_id, executions, disk_reads, buffer_gets, elapsed_time FROM v$sql WHERE parsing_schema_name 你的用户名 ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;这一步的核心目的是把“感觉慢”变成“数字上慢”。有了量化数据你才知道哪些SQL真正值得优化避免在无关紧要的语句上浪费时间。2.2 学会看基数Cardinality这是索引判断的基础“基数”这个词如果听起来陌生你可以把它理解成一个字段里不同值的数量。比如一个“性别”字段只有“男”“女”两个不同值基数就是2非常低一个“用户ID”字段基本每条记录都不一样基数接近总行数非常高。为什么要关注基数因为数据库的优化器Optimizer决定是否使用索引、使用哪个索引最重要的依据就是基数估算。如果一个字段的基数很低比如性别、状态类型就算你在上面建了索引优化器也很可能不用——因为通过索引去访问大量重复值还不如直接全表扫描来得快。这就像你查一本书里所有“的”字出现在哪一页用索引一个个找反而比从头翻一遍更费劲。具体到实操里我会用这条SQL来了解一张表的基本分布SELECT COUNT(*) AS total_rows, COUNT(DISTINCT user_id) AS user_id_cardinality, COUNT(DISTINCT order_status) AS status_cardinality, COUNT(DISTINCT created_date) AS date_cardinality FROM order_table;如果一个查询条件里的字段基数只有几十那别指望单靠这个字段的索引解决性能问题如果基数和总行数接近那这个字段非常适合做索引。这个判断是后续所有选择的前提。注意基数可以通过ANALYZE TABLE或UPDATE STATS让统计信息更新但要记住统计信息本身也是“估算值”。数据变化很快的大表统计信息很容易过期这也是优化器偶尔“犯傻”的原因之一。2.3 执行计划优化器告诉你它打算怎么干量化和基数判断还只是准备工作真正的主角是执行计划。就像你请了一位导航执行计划就是导航规划出来的具体路线先走哪条路、在哪做排序、在哪做连接、有没有绕路。MySQL查看执行计划最简单的方式就是EXPLAIN SELECT ...;如果你想看更细的成本估算和优化器改写后的语句用EXPLAIN FORMATJSON SELECT ...;Oracle里则是EXPLAIN PLAN FOR SELECT ...; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);拿到执行计划后我习惯先看几个关键字段type访问类型。从好到差大致是system、const、eq_ref、ref、range、index、ALL。看到ALL全表扫描就要高度警惕特别是大表。key实际使用的索引。如果为NULL说明没走索引。rows优化器预估需要读取的行数。这个数字和实际返回行数差距越大统计信息越可能过期。Extra这里的信息最容易被忽略也最值钱。看到Using filesort说明有额外排序看到Using temporary说明建了临时表看到Using where说明存储引擎返回后还要过滤。举个例子像下面这样一条查询EXPLAIN SELECT order_id, customer_id, total_amount FROM orders WHERE order_status PAID AND created_date 2024-01-01 ORDER BY total_amount DESC;如果回显里type是ALL、rows显示上百万、Extra里还有Using filesort那基本可以断定这条SQL会拖垮你的业务。执行计划的价值在于它把数据库内核的决策过程摊开给你看。你不需要完全理解优化器内部的成本计算公式但至少要知道它选择了哪条路线、为什么这条路线慢然后针对性地引导它换一条更好的路。2.4 明确优化目标是IO慢了、CPU忙了还是锁等待了同一个慢SQL在不同阶段可能有完全不同的瓶颈。我自己的排查习惯是先把瓶颈归类再动手。比如如果CPU占用率高可能是大量计算、函数操作、复杂排序导致的。如果IO延迟高可能是扫描了大量数据页或者索引失效导致回表太多。如果数据库的锁等待时间长可能是事务没提交、行锁范围过大。这里有一个反直觉的经验有时候SQL本身并不慢是并发环境下的锁把它拖累了。你单独跑这条SQL可能只要50毫秒但在线上业务高峰期它要等别的事务释放锁一等就是几秒。这种情况你光优化SQL没用得优化事务逻辑缩短锁的持有时间。所以在动手改SQL之前至少先回答下面三个问题这条SQL是频繁执行的单点查询还是跑一次就要几分钟的批量报表它是被并发事务影响还是自身确实消耗了过多资源用户能接受的响应时间是多少优化到什么程度算达标回答完这些问题再进入下一步。3. 核心细节解析与实操要点3.1 最容易被忽视的隐式转换问题先看一个非常常见的坑。有一张用户表phone字段是VARCHAR类型但是查询的时候用了数字SELECT * FROM users WHERE phone 13800138000;这里系统和数据库都会把phone的值转换为数字再比较导致索引失效变成全表扫描。它的执行计划里type会从ref变成ALLrows暴增。解决方式很简单查询参数的类型要和字段类型一致或者强制写成字符串字面量SELECT * FROM users WHERE phone 13800138000;这个坑太常见了尤其在使用ORM框架时数字型参数被框架自动转为Long类型后拼进SQL非常隐蔽。排查时看到执行计划里明明有索引却没用先检查一下字段类型和查询条件是否匹配。3.2 函数包裹字段索引直接失效另一个高频问题是在索引字段上套了函数。比如SELECT * FROM orders WHERE DATE(created_at) 2024-06-01;这里的DATE(created_at)让索引列无法直接参与索引定位数据库只能对每一行的created_at都做一次函数计算再比较索引自然失效。优化方式是改写为范围查询SELECT * FROM orders WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00;改写后的SQL不仅能用上索引而且语义更清晰查询条件表达的是“从这一秒到下一秒之前”的时间区间而不是模糊的“某一天”。类似的还有在字段上做加减乘除运算、字符串拼接等操作。我以前排查过一条慢SQL里面写了WHERE total_price * 0.9 100业务本意是“折扣后金额大于100”但这样写索引永远用不上。正确的写法是先把常量算好WHERE total_price 100 / 0.9。只要能让索引列独立出现在比较运算符的一侧优化器就有机会用索引。3.3 SELECT * 不只是浪费带宽还会破坏覆盖索引很多人知道SELECT *不好但说不清楚为什么。其实核心有两个原因。第一它把不需要的列都查出来了增加了网络传输和内存占用。对于动辄几十个字段的宽表这个浪费非常明显。第二更关键的是它破坏覆盖索引。所谓覆盖索引就是查询需要的所有列都能从索引中直接获取不需要回表访问实际数据行。比如有一张表有(status, create_time)的联合索引如果查询是SELECT status, COUNT(*) FROM orders WHERE status PAID GROUP BY create_time;那么数据库可能在索引里就把活干完了不需要回表。但如果你顺手加了SELECT *每个匹配的索引项都得回表拿整行数据IO开销立刻翻倍。所以我的习惯是只要不是真的需要整行数据就明确列出所需字段。这个习惯在数据量大时收益尤其明显。3.4 分页查询深翻页问题LIMIT 100000, 20到底慢在哪经典的分页场景SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 20;这条SQL的逻辑是先按created_at倒序排序然后跳过前面100000条取后面的20条。问题在于数据库必须先把前100000条都找出来排序再扔掉这个过程浪费了大量IO和CPU。页数越深浪费越严重。优化的思路不是让数据库去“跳”而是让它按某种可比较的条件直接定位到目标位置。比如利用主键或唯一键SELECT * FROM orders WHERE id 上一页最后一条记录的id ORDER BY id DESC LIMIT 20;这种“基于游标”的分页方式每次查询都从已知位置向后取数据排序和扫描范围都小得多响应时间随页数加深基本保持平稳。如果分页时确实需要按非唯一字段排序可以考虑在排序字段上建联合索引让排序动作在索引中完成避免Using filesort。但索引不是万能的这一点我在后面索引设计的章节里会详细展开。3.5 子查询和JOIN优化器改写之后问题可能还在IN、EXISTS、JOIN之间的选择堪称SQL优化圈的经典辩题。我见过很多帖子说“用EXISTS代替IN性能更好”但实际上现代数据库优化器在绝大多数情况下都会把子查询改写成连接或半连接执行计划往往一样。真正的差异不在于你写了哪个关键字而在于子查询本身的执行方式。比如SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE level VIP);如果customers表很大IN后面的子查询可能被优化成临时表关联如果customers表很小且主键索引可用就可能直接作为驱动表循环查找。这类查询的优化重点应该是子查询的表能否用到索引以及驱动顺序是否合理。有一次我优化一条慢SQL业务方坚持要用NOT IN结果跑了50秒。改成LEFT JOIN ... WHERE ... IS NULL之后秒回。原因在于NOT IN在子查询包含NULL值时会有语义陷阱而且优化器很难把它改写为高效的半连接。所以遇到“取反”类查询我通常会优先考虑LEFT JOIN写法避免一脚踩进坑里。注意NOT IN和NOT EXISTS在子查询结果里有NULL时的行为不一样这是个经典坑。如果你在使用这类查询一定要确认子查询的列没有NULL值或者干脆用NOT EXISTS后者在语义上通常更安全。4. 实操过程与核心环节实现4.1 一个模拟案例从5秒到50毫秒的完整优化过程为了把前面的理论串起来我构造一个贴近真实业务的模拟场景。假设有一张交易流水表每天增加几十万行累计几千万行。表结构大致如下CREATE TABLE trade_record ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, product_id BIGINT NOT NULL, order_no VARCHAR(64) NOT NULL, pay_amount DECIMAL(10,2) NOT NULL, pay_status TINYINT NOT NULL DEFAULT 0, pay_time DATETIME NOT NULL, remark VARCHAR(255) DEFAULT NULL, INDEX idx_user_id (user_id), INDEX idx_pay_time (pay_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;业务需求是查询某个用户在最近30天内的已支付订单按支付金额降序排列并分页返回。我最初的写法是这样的SELECT id, user_id, order_no, pay_amount, pay_time, remark FROM trade_record WHERE user_id 12345 AND pay_status 1 AND pay_time 2024-05-01 00:00:00 ORDER BY pay_amount DESC LIMIT 20;这条SQL在测试环境跑大约是5秒。我们一步步看怎么优化。第一步看执行计划。EXPLAIN SELECT ...;执行计划显示key用的是idx_user_idrows估算可能是几千行但Extra里出现了Using filesort。也就是说查询通过user_id索引找到候选行之后还需要在内存或磁盘上按pay_amount做一次额外排序。而且这几千行需要回表获取整行数据再对pay_amount做排序所以慢。第二步分析排序字段和查询字段考虑联合索引。用户查询的过滤条件是user_id和pay_time排序条件是pay_amount。如果建立一个联合索引把过滤和排序都覆盖进去理论上排序就不需要额外做了。比如ALTER TABLE trade_record ADD INDEX idx_user_pay_time_amount (user_id, pay_time, pay_amount);user_id用于等值过滤pay_time用于范围过滤pay_amount用于排序。这种设计让索引中的记录已经按user_id、pay_time、pay_amount排好序了数据库可以直接从索引里顺序读取而不再有Using filesort。第三步尝试覆盖索引。如果要查询的列全部在索引里就可以避免回表。联合索引(user_id, pay_time, pay_amount)已经覆盖了id、user_id、pay_time、pay_amount但order_no和remark不在索引中。如果查询必须返回这两个字段就还要回表。于是我希望再把order_no加入索引ALTER TABLE trade_record ADD INDEX idx_user_pay_time_amount_order (user_id, pay_time, pay_amount, order_no);但无论如何remark是长文本字段不适合放进索引。因此这里我做了个取舍让查询先通过覆盖索引拿到需要的主键再用主键回表取remark本质上还是回表但范围小了很多。第四步改写查询让回表只针对最终结果。改写后的SQL如下SELECT t.id, t.user_id, t.order_no, t.pay_amount, t.pay_time, t.remark FROM ( SELECT id FROM trade_record WHERE user_id 12345 AND pay_status 1 AND pay_time 2024-05-01 00:00:00 ORDER BY pay_amount DESC LIMIT 20 ) tmp JOIN trade_record t ON tmp.id t.id;这个改写看起来多包了一层子查询但实际执行时内层子查询直接走联合索引只把满足条件的主键id取出来并完成排序由于索引是有序的排序开销没了。最后再用20个主键去回表拿到完整行。回表次数从“几千行”变成“20行”量级完全不一样。经过这四步这条查询从5秒降到50毫秒左右。同样的SQL只因为让索引同时承担了“过滤”“排序”“取数”三项任务性能就产生了质变。4.2 要不要为了排序建索引这取决于数据量和更新频率索引不是免费的。每建一个索引写入时就要多维护一棵B树。对于一个每天几十万行写入的表额外加两个联合索引写入性能会受到明显影响。所以“为了排序建索引”一定要谨慎。我的判断标准大概有三条如果查询过滤后结果集只有几十行那排序本身很快不需要为了排序专门建索引。如果查询过滤后结果集有几千行甚至更多而且这条查询是高频热查询那么联合索引值得建。如果这张表的写入量极大而查询频率一般那么可以考虑在应用层做缓存或异步计算而不是拼命加索引。索引还有一个隐蔽的成本B树的更新会导致页分裂进而产生碎片。频繁写入的大表即使索引建对了时间久了性能也可能退化。所以索引不是建完就万事大吉需要用定期维护的手段来抑制碎片化。MySQL中查看表的碎片化程度可以用SELECT table_name, data_length, index_length, data_free FROM information_schema.tables WHERE table_schema 你的库名 AND table_name trade_record;如果data_free明显偏大说明碎片化严重可以执行OPTIMIZE TABLE trade_record;来重建表。当然这个操作会锁表生产环境需要挑低峰期执行或者用在线DDL工具处理。4.3 分组统计类SQL临时表是最大杀手分组统计是慢SQL的重灾区。看一个经典场景SELECT DATE(pay_time) AS pay_date, COUNT(*) AS order_cnt, SUM(pay_amount) AS total_amount FROM trade_record WHERE pay_time 2024-01-01 00:00:00 AND pay_time 2024-02-01 00:00:00 GROUP BY DATE(pay_time);这个SQL的问题是GROUP BY DATE(pay_time)对索引列做了函数处理索引大概率用不上。优化方式是增加一个冗余字段比如pay_date直接存储日期类型并在此字段上建索引。一次写入时多存一个字段换来的却是一次查询性能的大幅提升。如果没有办法加冗余字段另一个思路是提前在应用层算好日期范围然后按整点或整天分桶写入临时表。比如CREATE TEMPORARY TABLE tmp_pay_stat ( pay_date DATE PRIMARY KEY, order_cnt INT, total_amount DECIMAL(12,2) );然后分区间批量查询把结果回填到临时表里。这种方法适合离线统计不适合在线查询场景。更常见的做法还是保证GROUP BY的字段能直接从索引里读出来尽量避免函数包裹。4.4 分页优化实战从深分页到游标分页继续用前面的trade_record表。如果业务方要求“按支付金额从高到低查看某个用户的订单”而且订单量很大传统的LIMIT 100000, 20会越来越慢。我建议改成游标模式前端每次传上一个位置的标记。假设上一页最后一条记录是(pay_amount 888.00, id 500123)下一页查询写成SELECT id, user_id, order_no, pay_amount, pay_time FROM trade_record WHERE user_id 12345 AND pay_status 1 AND pay_time 2024-05-01 00:00:00 AND (pay_amount 888.00 OR (pay_amount 888.00 AND id 500123)) ORDER BY pay_amount DESC, id ASC LIMIT 20;这里最后一行的排序条件带了两个字段先按pay_amount降序如果金额相同再按id升序。用id做第二排序键是为了保证唯一性避免分页过程中出现“漏行”或“重复行”。这种基于游标的查询方式无论翻到第几页扫描的数据量都只跟当前页有关响应时间非常平稳。4.5 多表关联优化驱动表的选择比SQL写法更关键先看一条关联查询SELECT u.user_name, o.order_no, o.pay_amount FROM users u JOIN trade_record o ON o.user_id u.id WHERE u.level 2 AND o.pay_status 1 AND o.pay_time 2024-05-01 00:00:00 ORDER BY o.pay_time DESC LIMIT 100;这里有两个表users用户表和trade_record交易表。关联字段是user_id。优化的核心在于哪张表作为驱动表外层表哪张表作为被驱动表内层表。经验法则是用小结果集驱动大结果集。在这个查询里u.level 2可能会过滤出一部分用户o.pay_status 1 AND o.pay_time ...可能会过滤出一部分交易。哪边过滤后数据量少就让哪边先查然后拿它的关联键去另一张表匹配。你可以在执行计划里看到驱动顺序如果顺序反了可以尝试改写SQL的结构或者在另一张表的关联字段上确认是否有索引。另外要特别注意被驱动表的关联字段必须建立索引。在上面的例子中trade_record.user_id已经建过索引所以用users做驱动表去探测trade_record时效率很高。如果被驱动表没有索引那就只能每一行都扫一次另一张表后果不堪设想。5. 工具选型与辅助手段用好可视化和自动化排查5.1 用EXPLAIN的可视化工具提升效率命令行里的EXPLAIN输出是表格字段多了之后并不直观。我建议把执行计划的输出贴到可视化工具里看。MySQL官方Workbench、phpMyAdmin、DBeaver都有执行计划可视化的功能Oracle的SQL Developer也有类似面板。可视化的好处是你能一眼看到表连接顺序、索引使用情况、排序和临时表的位置不用在心里拼图。5.2 慢查询日志与自动化巡检脚本如果一张表的数据量在千万级线上SQL又多靠人工一条条排查不现实。我习惯写一个简单的巡检脚本每天读取慢查询日志按“平均执行时间 × 执行次数”排序找出真正影响系统吞吐量的Top N条SQL。比如下面的思路伪代码级别的SQL查询可以结合日志定期执行SELECT digest_text, count_star, avg_timer_wait / 1000000000 AS avg_ms, sum_timer_wait / 1000000000 AS total_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY total_ms DESC LIMIT 20;performance_schema中的events_statements_summary_by_digest会自动按SQL模板聚合统计不需要你自己解析日志。每次巡检只要关注total_ms最大的几条就能迅速定位绝大多数性能问题的源头。5.3 用EXPLAIN ANALYZE验证真实的执行时间MySQL 8.0提供了一个特别有用的命令EXPLAIN ANALYZE。它不只是估算而是真的执行SQL并返回每一步的实际耗时和行数。比如EXPLAIN ANALYZE SELECT ...;输出里会显示每一步的实际循环次数、平均耗时和返回行数。这对判断“优化器预估行数是否准确”特别有帮助。我多次遇到rows预估1万行、实际跑出100万行的情况此时看到EXPLAIN ANALYZE里的实际行数就能立刻明白统计信息已经严重过期需要重新ANALYZE TABLE。Oracle对应的是DBMS_XPLAN.DISPLAY_CURSOR加上gather_plan_statistics提示可以拿到每一步的实际行数和内存排序等信息思路是一样的。注意EXPLAIN ANALYZE会真实执行语句所以对INSERT、UPDATE、DELETE这类写操作要格外小心。最好在测试环境执行或者只是在查询语句上使用。5.4 索引设计辅助工具MySQL 8.0有一个“索引分析”特性通过sys库的diagnostics和index_unused视图可以查看哪些索引长期没有被使用。定期清理未使用的索引能减少写入开销也算是一种预防性优化。另外对于特别复杂的查询可以用自带的优化器跟踪SET optimizer_trace enabledon; SELECT ...; SELECT * FROM information_schema.OPTIMIZER_TRACE; SET optimizer_trace enabledoff;优化器追踪会展示优化器在考虑哪些索引、怎么计算成本、为什么选择了当前执行计划。这个工具对于理解“为什么我建了索引它不用”非常有帮助。6. 一个被忽视的维度应用层与SQL的交互方式6.1 N1查询问题ORM框架带来的隐性杀手很多慢SQL并不是单条SQL慢而是被调用了成千上万次。最典型的是ORM框架的N1查询。比如查询一批用户然后遍历每个用户去查他的订单ListUser users userMapper.selectList(...); for (User user : users) { ListOrder orders orderMapper.selectByUserId(user.getId()); // 处理逻辑 }这里的selectByUserId如果执行了N次就算每次只要1毫秒1000个用户就是1秒1万个用户就是10秒。这种问题在单个SQL层面很难发现因为每条都很快但整体性能极差。优化方向是把N1合并成一次查询ListLong userIds users.stream().map(User::getId).collect(Collectors.toList()); ListOrder orders orderMapper.selectByUserIds(userIds);然后在应用层按userId分组装配。一次查询解决所有订单的获取网络往返从N次降到1次。这个优化往往比调整单条SQL的索引更立竿见影。6.2 不必要的大结果集传输我在文章开头提到的报表系统问题就是典型的“应用层拿到所有结果再处理”。解决方案有两个方向一是把聚合操作尽量下推到数据库完成而不是在Java里循环累加二是确实需要在应用层做复杂计算时先在SQL里把数据量缩小到可接受的范围比如按天汇总后再传出来。来看一个曾经让我印象深刻的例子。某业务需要统计一个用户一年内每个月的消费金额。最初的做法是查询用户全年所有订单可能有几千条然后在代码里按月分组求和。几千条听起来不多但如果是几千个用户同时跑服务器内存就吃不消了。后来改成SELECT DATE_FORMAT(pay_time, %Y-%m) AS month, SUM(pay_amount) FROM trade_record WHERE user_id 12345 AND pay_time 2024-01-01 00:00:00 AND pay_time 2025-01-01 00:00:00 GROUP BY DATE_FORMAT(pay_time, %Y-%m);结果集从几千条变成12条网络和内存开销几乎可以忽略。同样的业务逻辑SQL写法不同资源消耗天差地别。6.3 事务内的查询尽量缩短还有一个和SQL优化不太相关但经常影响实际体验的问题长事务。如果一个事务里包含多个查询并且事务一直没有提交那么它持有的锁就会一直占用资源别人要改同一行数据就得等。这个问题看起来和SQL性能没关系但在并发环境下它会让所有SQL都变慢。所以我的原则是事务尽可能短只把必要的写操作放在事务里查询尽量不参与长事务。很多ORM框架默认会在一次Session里开启事务你在代码里要明确控制事务边界。6.4 查询并发与连接池设置数据库能同时处理的连接数是有限的。如果应用层连接池配置得太小请求会排队等待如果配置得太大数据库会被连接淹没CPU和内存消耗急剧上升SQL本身再快也没用。我见过很多案例数据库CPU飙升不是因为某条SQL多复杂而是连接数瞬间打满数百个连接同时在执行查询。连接池大小的设置有计算公式但通常我会结合数据库的核数和业务响应时间要求来设定一个范围。比如数据库8核日常并发大概几十到一百连接池可以设置在20~50之间具体值要靠压测验证。盲目调大连接池是新手常犯的错误。7. 常见问题与排查技巧实录7.1 为什么加了索引执行计划还是不用它这是最常被问的问题之一。原因通常有几种第一索引字段的基数太低优化器认为用索引还不如全表扫描第二查询返回的数据比例太高比如超过表的20%~30%优化器也会放弃索引第三隐式类型转换或函数包裹导致索引失效第四统计信息过期优化器误判。排查时先看字段类型是否匹配再看有没有函数包裹然后看返回数据量占比最后考虑ANALYZE TABLE更新统计信息。如果这些都排除了优化器还是不用索引可以尝试用FORCE INDEX或者USE INDEX来验证一下索引是否真的能提升性能。SELECT * FROM trade_record FORCE INDEX (idx_user_pay_time_amount) WHERE ...;如果你手动指定索引后性能明显提升而默认执行计划很慢大概率是优化器成本估算的问题需要通过更新统计信息或调整数据库参数来修正而不是长期依赖FORCE INDEX——毕竟SQL环境是会变化的。7.2 文件排序Using filesort一定能消除吗不一定。Using filesort的意思是MySQL需要额外执行一次排序操作它可能发生在内存中用快速排序也可能溢出到磁盘用归并排序。它并不一定代表性能差只有排序数据量很大或者频繁执行时才会成为瓶颈。如果排序字段和查询的过滤条件不在同一个索引里确实容易出现Using filesort。这时候要么调整索引让排序字段包含在索引中要么把排序字段加到查询条件里形成联合索引。但要注意联合索引对排序的支持有顺序要求比如索引是(a, b)查询条件里先等值过滤a再按b排序才能利用索引顺序。如果先过滤b再按a排序索引就帮不上忙了。7.3 MySQL和Oracle的优化差异如果你同时接触过MySQL和Oracle会发现两者的优化思路有不少差异。Oracle的优化器非常强大统计信息和代价估算机制更为成熟很多MySQL需要手动调整的地方Oracle会自动处理。比如Oracle支持基于函数的索引Function-Based Index你可以在表达式上建索引而MySQL直接不支持。Oracle中改写函数包裹字段的方案可以是CREATE INDEX idx_trade_pay_time ON trade_record(TO_CHAR(pay_time, YYYY-MM-DD));这样查询里写TO_CHAR(pay_time, YYYY-MM-DD) 2024-06-01时也能走索引。MySQL里没有这个特性只能另想办法。另外Oracle的分页用的是ROWNUM或FETCH FIRST没有LIMIT深分页优化思路类似但写法完全不同。跨数据库经验迁移时一定不能只背关键词要理解底层逻辑。7.4 临时表空间耗尽怎么排查当查询涉及大量GROUP BY或ORDER BY时MySQL会使用临时表。如果临时表过大超过了tmp_table_size和max_heap_table_size的限制就会从内存临时表转换为磁盘临时表性能断崖式下降。更严重的情况是临时表空间耗尽直接报错。遇到这种情况可以用EXPLAIN看Extra里有没有Using temporary然后尝试改写SQL比如提前缩小范围、预聚合数据或者调整临时表上限参数。不过我的经验是与其调大一倍临时表内存不如优先改写SQL因为临时表过大本身说明查询的数据处理方式不够高效。7.5 死锁和锁等待导致的“慢”SQL把锁问题放在最后是因为它太容易被忽略了。有一年我做压测发现同一个接口的响应时间从正常时的200毫秒涨到3秒一开始怀疑SQL性能问题把执行计划翻了个遍每个查询都能用上索引单条耗时也在毫秒级别。后来才发现某个批量更新任务未提交长时间持有行锁其他线程的更新只能在锁等待队列里排队。那段时间所有和该表相关的写操作都变慢但读操作正常。排查锁等待的方法在MySQL里可以查SELECT waiting_pid, waiting_query, blocking_pid, blocking_query FROM sys.innodb_lock_waits;看到blocking_pid后可以进一步查它的事务和执行的SQL再决定是等待还是终止。8. 优化之外如何防止新的慢SQL再次出现SQL优化不是一次性的工作。项目上线后数据量会增长统计信息会变化查询模式也会改变。我自己的做法是把优化过程沉淀成常规机制定期巡检慢查询日志每周至少一次。关注Top SQL的执行计划和行数变化尤其是那些执行次数多、平均耗时在增长的语句。新SQL上线前强制经过一次EXPLAIN检查。我见过太多事故都是因为开发者没看执行计划就上线等到数据量涨起来了才发现某个字段没有索引。对于高频查询定期观察索引的使用情况。如果有索引长期没有命中就考虑是否需要删除减少写入负担。另外尽量规范ORM层的SQL生成规则。很多人用ORM框架时不关注底层生成的SQL等出现问题再回头看往往发现SQL结构已经复杂到难以优化。我的建议是复杂查询不要迷信ORM直接用自定义SQL但要在代码评审里明确这些SQL的执行计划是人工确认过的。提示SQL性能优化的核心其实不是背技巧而是建立一套“发现问题—量化现状—阅读执行计划—设计索引—验证效果—防止回退”的闭环。每次优化完把耗时、执行计划、索引设计记录下来形成一个Case库下次遇到类似问题可以直接参考。9. 一点个人经验收尾文章写到这里技术内容基本说完了。最后分享一个我在多次踩坑之后形成的习惯优化SQL之前一定要先问自己一句“这条SQL真的要这样写吗”很多时候慢SQL的根源是业务逻辑设计的问题比如把多条查询硬拼成一条超级复杂的SQL或者在应用层可以做好的聚合放到了数据库里反复跑。SQL优化不只是调索引、改写法而是调整“数据在哪里处理”的效率方案。还有一个小技巧值得单独提一下修改完SQL和索引之后不要只看单次执行时间要在并发环境下多测几次观察数据库的CPU、IO、锁等待等指标变化。有些优化单线程跑很漂亮一旦并发上来问题就暴露了。我自己就经历过一次某条SQL单次5毫秒看似优秀但并发200时数据库CPU飙升到90%原因是这个查询每秒被调用上千次加起来的消耗远大于那条偶尔跑一跑但耗时2秒的报表SQL。性能优化要关注的不是单点峰值而是整体吞吐量。如果你遇到了SQL性能问题不用慌按着“慢查询日志定位、执行计划分析、索引设计调整、应用层优化配合”这套顺序走绝大多数问题都能找到答案。最后再强调一句优化完记得做回归测试确保业务结果没有变化。性能再好结果错了一切白搭。