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 SQL：
SELECT 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 FORMAT=JSON 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变成ALL，rows暴增。解决方式很简单：查询参数的类型要和字段类型一致，或者强制写成字符串字面量：
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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;业务需求是：查询某个用户在最近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_id，rows估算可能是几千行，但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 = "enabled=on"; SELECT ...; SELECT * FROM information_schema.OPTIMIZER_TRACE; SET optimizer_trace = "enabled=off";优化器追踪会展示优化器在考虑哪些索引、怎么计算成本、为什么选择了当前执行计划。这个工具对于理解“为什么我建了索引它不用”非常有帮助。
6. 一个被忽视的维度：应用层与SQL的交互方式
6.1 N+1查询问题：ORM框架带来的隐性杀手
很多慢SQL并不是单条SQL慢，而是被调用了成千上万次。最典型的是ORM框架的N+1查询。比如查询一批用户，然后遍历每个用户去查他的订单：
List<User> users = userMapper.selectList(...); for (User user : users) { List<Order> orders = orderMapper.selectByUserId(user.getId()); // 处理逻辑 }这里的selectByUserId如果执行了N次，就算每次只要1毫秒，1000个用户就是1秒，1万个用户就是10秒。这种问题在单个SQL层面很难发现，因为每条都很快，但整体性能极差。
优化方向是把N+1合并成一次查询：
List<Long> userIds = users.stream().map(User::getId).collect(Collectors.toList()); List<Order> 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性能问题，不用慌，按着“慢查询日志定位、执行计划分析、索引设计调整、应用层优化配合”这套顺序走，绝大多数问题都能找到答案。最后再强调一句：优化完记得做回归测试，确保业务结果没有变化。性能再好，结果错了，一切白搭。
