SQL 查询性能优化从执行时间到 IO、执行计划与数据访问路径SQL 查询性能优化不能只看“这条 SQL 执行了多少秒”。一条 SQL 当前执行只需要 200ms并不代表它是一条性能良好的 SQL。如果它为了返回 100 行数据却读取了 100 万行、产生几十万次逻辑读那么当数据量、并发量和缓存压力增加后它很可能迅速退化。因此SQL 性能优化的核心不是单纯追求“当前执行得快”而是减少数据库为得到最终结果所付出的资源成本。可以把 SQL 查询性能抽象为[Performance f(ExecutionPlan, IO, CPU, Rows, Loops, Memory, Wait)]也就是说SQL 性能主要由以下几个因素决定执行计划逻辑 IO物理 IOCPU 消耗实际处理行数算子执行次数内存使用锁和等待一套好的 SQL 优化方法应该围绕这些指标展开而不是只看执行时间。一、SQL 性能优化首先看什么建议固定观察以下 7 个核心指标Execution Time执行时间CPU TimeCPU 时间Logical Reads逻辑读取Physical Reads物理读取Rows Read读取行数Rows Returned最终返回行数Operator Loops算子执行次数其中最容易被忽略、但又极其重要的是Logical Reads Rows Read Loops因为这三个指标更能反映 SQL 的真实成本。例如返回数据100 行 扫描数据1,000,000 行那么数据访问效率为[Efficiency \frac{RowsReturned}{RowsRead}]即[Efficiency \frac{100}{1000000} 0.01%]这意味着数据库做了大量无效工作。即使当前查询只运行 300ms这条 SQL 依然值得优化。二、不要只看执行时间这是 SQL 性能分析中最重要的原则之一。例如一条 SQLElapsed Time: 200ms Logical Reads: 900000 Physical Reads: 0看起来只执行了 200ms。但是Physical Reads 0很可能只是因为数据已经存在 Buffer Cache 中。数据库并没有真的从磁盘读取而是直接从内存中读取了大量数据。此时 SQL 的真实问题仍然存在Logical Reads 900000随着以下条件发生变化数据量增加 并发增加 Buffer Cache 竞争增加 数据库内存压力增加查询性能可能变成200ms ↓ 1s ↓ 5s ↓ 10s所以应该区分两个概念执行时间 当前环境下 SQL 跑得快不快 IO / Rows / Loops SQL 本身设计得好不好SQL 优化更应该关注第二个问题。三、SQL Server 查询性能分析SQL Server 提供了一套非常成熟的性能分析工具。最常用的是setstatisticsioon;setstatisticstimeon;select...from...where...;setstatisticsiooff;setstatisticstimeoff;其中setstatisticsioon;用于观察 IO。例如Table LSXHDMX. Scan count 9, logical reads 928764, physical reads 0这里最值得关注的是logical reads 928764表示 SQL Server 从 Buffer Pool 中读取了大约 92 万个数据页。SQL Server 一个 Page 默认 8KB。所以大致访问的数据量为[928764 \times 8KB]约等于[7.1GB]这并不代表真的从磁盘读取了 7GB而是表示 SQL Server 在内存页层面进行了如此大量的数据访问。这通常说明存在大范围扫描 索引不合适 Nested Loop 被大量重复执行 过滤条件没有有效下推 关联顺序不合理 统计信息估算错误四、SQL Server 的 Statistics IO 怎么看典型输出Table OrderItem. Scan count 20, logical reads 100000, physical reads 0, read-ahead reads 200几个重要指标分别表示1. Logical Readslogical reads表示数据库从 Buffer Pool 中读取的数据页数量。这是 SQL Server 优化中最应该关注的指标之一。通常来说在返回结果基本一致的情况下 Logical Reads 越少越好例如优化前logical reads 500000优化后logical reads 5000那么即使执行时间暂时变化不大这通常也是一次非常成功的优化。2. Physical Reads表示数据库真正从存储设备读取的数据页数量。例如physical reads 0并不意味着查询成本很低。它往往只意味着数据已经在内存中所以 SQL 优化不能只看 Physical Reads。3. Scan Count表示相关访问路径执行的次数。例如Scan count 1通常问题不大。如果出现Scan count 10000那么应该重点检查执行计划。很多情况下是Nested Loop导致内部表被重复访问。五、一定要看实际执行计划SQL 优化不能只看 SQL 文本。真正决定数据库如何执行的是Execution Plan也就是执行计划。SQL Server 中建议直接查看Actual Execution Plan重点关注几个节点Table Scan Index Scan Index Seek Nested Loops Hash Match Merge Join Sort Key Lookup RID Lookup Spool六、Scan 不一定差Seek 不一定好很多开发人员会形成一种简单认知Index Seek 好 Index Scan 差 Table Scan 很差实际上并不完全正确。例如查询select*fromsaleswheresale_date2026-01-01;如果这条 SQL 最终需要返回表中 70% 的数据那么Index Scan可能比Index Seek 大量 Key Lookup更高效。所以判断执行计划不能只看算子名字而应该同时看Rows Cost IO Loops Lookup 次数优化的目标不是强制所有查询都变成 Index Seek而是让数据库用最低成本获取目标数据七、重点检查 Actual Rows 和 Estimated Rows执行计划中一个非常关键的指标是Estimated Rows Actual Rows例如Estimated Rows 100 Actual Rows 1,000,000说明优化器估算严重错误。数据库原本认为只有 100 行于是可能选择Nested Loop但实际数据是100 万行最终导致 Nested Loop 内层执行几十万甚至几百万次。这种情况通常需要检查统计信息 参数嗅探 数据倾斜 临时表 表变量 表达式 关联条件因此执行计划分析中一个非常重要的问题是优化器以为有多少行实际上有多少行八、重点观察 Rows × Loops很多慢 SQL 的真正问题不是单次查询慢而是某个操作执行了太多次。例如Index Seek Actual Rows 100 Actual Number of Executions 10000那么实际处理量大约是[100 \times 100001,000,000]也就是说虽然执行计划上看只是一个普通的 Index Seek但它被执行了一万次。这是典型的Nested Loop 放大效应因此分析 SQL 时建议建立一个简单意识[TotalWork\approxRows\timesLoops]很多性能问题都可以通过这个公式发现。九、索引优化的核心不是“加索引”SQL 性能问题出现后最常见的处理方式就是加索引但这是一种非常危险的习惯。正确的索引设计应该从查询模式出发。例如selectproduct_code,quantity,amountfromsale_detailwhereshop_code001andsale_date2026-09-01andsale_date2026-10-01;可以考虑索引createindexix_sale_detail_shop_dateonsale_detail(shop_code,sale_date)include(product_code,quantity,amount);这里shop_code sale_date用于定位数据。而product_code quantity amount通过 INCLUDE 覆盖查询。这样可以减少Key Lookup十、索引字段顺序非常重要联合索引(a,b,c)并不等价于(c,b,a)通常可以按照下面的思路设计等值条件 ↓ 范围条件 ↓ 排序 / 分组 ↓ Include 字段例如whereshop_code?andproduct_code?andsale_datebetween?and?那么可能考虑(shop_code, product_code, sale_date)而不是简单按照数据库字段原来的顺序创建索引。十一、避免对索引字段进行函数计算典型错误whereconvert(varchar,rq,23)2026-09-28或者whereyear(rq)2026这会导致数据库很难直接使用rq上的索引进行 Seek。更推荐whererq2026-09-28andrq2026-09-29同样whereyear(rq)2026应该考虑改成whererq2026-01-01andrq2027-01-01优化原则是尽量不要加工索引列而应该加工查询参数。十二、日期范围推荐使用左闭右开日期查询非常容易写出问题。例如whererqbetween2026-09-01and2026-09-30 23:59:59这种写法容易受到datetime datetime2 毫秒精度影响。推荐统一使用whererq2026-09-01andrq2026-10-01即[[startDate,endDate)]这种方式更准确更容易使用索引更容易生成动态 SQL更容易统一报表日期逻辑十三、减少无效数据读取SQL 优化中最有效的方法之一不是“让数据库执行得更聪明”而是让数据库少读数据例如select*fromsale_detail;如果实际上只需要selectspdm,sl,jefromsale_detail;那么应该避免select *。尤其是在大宽表 LOB text json varchar(max)场景下读取不需要的字段会明显增加IO 内存 网络传输 序列化成本十四、尽早过滤数据例如select...from(select*fromsale_detail)awherea.shop_code001;逻辑上可以工作但复杂 SQL 中应该尽量让过滤条件尽早进入数据源。理想结构select...fromsale_detailwhereshop_code001;在复杂报表中尤其要关注Predicate Pushdown即过滤条件是否真正下推到最底层的数据访问节点。优化目标应该是先减少数据 再关联 再聚合 再排序而不是先把所有数据关联起来 ↓ 最后再过滤十五、JOIN 是性能优化重点大型业务 SQL 中JOIN 往往是主要性能瓶颈。例如ajoinbjoincjoindjoine最终性能取决于各表数据量 过滤条件 JOIN 字段索引 JOIN 顺序 统计信息 选择性 执行计划尤其要关注大表 JOIN 大表例如销售明细5 亿行 库存流水3 亿行 采购明细1 亿行如果直接 Joinsalejoinstockjoinpurchase很容易出现数据量爆炸。更好的办法通常是先按业务粒度分别聚合 ↓ 再 JOIN例如sale 先 group by shop product stock 先 group by shop product purchase 先 group by shop product 最后三份结果 JOIN这样可以大幅降低 Join 数据量。十六、GROUP BY 前尽量减少数据例如selectshop_code,product_code,sum(quantity)fromsale_detailgroupbyshop_code,product_code;如果 sale_detail 有 5 亿条数据那么 Group By 本身成本可能很高。如果实际只统计最近 7 天whererqdateadd(day,-6,endDate)andrqdateadd(day,1,endDate)一定要在 Group By 之前过滤。即5亿 ↓ 先过滤 ↓ 500万 ↓ 再 GROUP BY而不是5亿 ↓ GROUP BY ↓ 最后再过滤十七、临时表不是坏东西很多人认为一条 SQL 写完比临时表拆分更高级。实际上对于复杂业务查询这并不一定成立。例如一个 SQL 同时存在销售 退货 库存 采购 价格 会员 促销 门店 商品全部写成一个巨大 CTEwithaas(...),bas(...),cas(...),das(...)select...优化器可能很难得到稳定执行计划。此时可以合理使用select...into#salefrom...createindexix_sale...on#sale(...);然后select...from#salejoin...临时表有几个重要价值中间结果物化 降低查询复杂度 重新生成统计信息 建立临时索引 限制数据规模 提高执行计划稳定性所以临时表应该被视为一种查询执行策略而不是性能优化失败的标志。十八、CTE 并不等于缓存例如withsaleas(select...fromsale_detail)select...fromsale;很多开发人员误以为sale 已经执行一次并保存起来实际上 CTE 更多只是查询表达式数据库完全可能把它重新展开到最终执行计划中。所以复杂查询中如果同一个中间结果被反复使用应该观察执行计划。必要时使用临时表 物化视图 中间汇总表十九、ORDER BY 和 SORT 成本不能忽略排序orderbyamountdesc看起来很简单。但如果需要排序 1000 万行成本可能非常高。执行计划可能出现Sort并且发生Memory Spill即排序所需内存不足将数据写入 TempDB 或磁盘。因此应该关注排序行数 排序字段是否有索引 Memory Grant TempDB Spill很多报表 SQL 的真正瓶颈并不是 Join而是大规模 Sort二十、分页查询需要特别设计常见分页orderbyidoffset1000000rowsfetchnext20rowsonly;页数越深性能越差。因为数据库仍然可能需要处理前面的100 万行大型系统可以考虑Seek Pagination也叫Keyset Pagination例如whereidlastIdorderbyidfetchnext20rowsonly;这样性能更容易保持稳定。二十一、避免 N1 查询SQL 优化不仅发生在数据库端。ORM 系统经常出现查询订单 100 条 ↓ 每个订单查询一次明细 ↓ 101 次数据库访问也就是N 1即使每条 SQL 只执行 5ms[101 \times 5ms]也已经超过 500ms。所以应用侧同样应该观察一次请求执行了多少 SQL而不仅仅是某一条 SQL 执行了多久二十二、MySQL 怎么分析 SQLMySQL 8 推荐explainanalyzeselect...重点看actual time rows loops Table scan Index lookup Nested loop例如actual rows10000 loops1000说明实际处理规模约为[10000\times10001000万]因此 MySQL 同样应该重点观察Rows Loops而不是只看执行时间。二十三、PostgreSQL 怎么分析 SQLPostgreSQL 推荐explain(analyze,buffers)select...其中Buffers: shared hit shared read非常重要。可以近似理解shared hit ≈ SQL Server logical reads shared read ≈ 实际读取例如shared hit900000 shared read0和 SQL Serverlogical reads900000 physical reads0反映的是类似的问题。二十四、Oracle 怎么分析 SQLOracle 可以通过setautotraceonstatistics;查看consistent gets physical reads rows processed其中consistent gets与 SQL Serverlogical reads非常类似。更加专业的分析可以结合DBMS_XPLAN查看E-Rows A-Rows即Estimated Rows Actual Rows其核心思想与 SQL Server 完全一致。二十五、建立统一的跨数据库性能模型实际上不同数据库虽然命令不同但底层问题高度一致。可以统一成下面的分析模型SQL │ ▼ Execution Plan │ ┌──────────┼──────────┐ ▼ ▼ ▼ IO CPU Memory │ ▼ Rows × Loops │ ▼ Actual Work │ ▼ Execution Time其中Execution Time更像最终表现。而IO Rows Loops Execution Plan才是根因。因此可以把 SQL 优化过程抽象成[SQL\rightarrowPlan\rightarrowIO\rightarrowRows\rightarrowLoops\rightarrowTime]二十六、推荐的 SQL 性能优化流程实际工作中可以固定按照下面的顺序分析。第一步确认 SQL首先保存原始 SQL。不要一开始就修改 SQL否则后面无法比较优化效果。第二步记录基准数据记录执行时间 CPU 时间 Logical Reads Physical Reads 返回行数例如Elapsed Time 3200ms CPU Time 2800ms Logical Reads 928764 Rows Returned 2300第三步查看实际执行计划检查Table Scan Index Scan Index Seek Nested Loop Hash Match Sort Key Lookup Spool第四步寻找最大成本节点不要平均用力。通常应该寻找读取数据最多的节点 执行次数最多的节点 行数估算偏差最大的节点 IO 最大的节点也就是寻找[MaxCostOperator]第五步检查 Rows × Loops例如Rows 5000 Loops 10000那么这个节点很可能就是问题所在。第六步检查索引确认过滤字段 Join 字段 排序字段 Group By 字段是否存在适合的索引。第七步检查数据是否可以提前减少考虑提前 WHERE 提前 GROUP BY 拆临时表 减少 JOIN 减少字段第八步重新执行 SQL再次运行setstatisticsioon;setstatisticstimeon;获得新的指标。第九步对比优化前后例如指标优化前优化后Logical Reads928,76421,430CPU2,800ms320msElapsed3,200ms410msRows2,3002,300这样的优化才是可以量化和验证的。二十七、一个非常重要的优化原则SQL 性能优化的核心目标可以浓缩成一句话用尽可能少的数据访问得到相同的业务结果。因此[Performance\propto\frac{UsefulData}{TotalWork}]如果读取 100 万行 返回 100 行通常意味着查询效率较低。如果读取 120 行 返回 100 行则查询的数据访问路径通常非常理想。二十八、SQL 性能优化的三个层次从架构角度可以把 SQL 优化分成三个层次。第一层SQL 语句优化例如WHERE JOIN GROUP BY ORDER BY 子查询 CTE 窗口函数第二层数据库访问结构优化例如索引 分区 统计信息 物化视图 临时表 汇总表第三层系统架构优化例如缓存 读写分离 报表库 OLTP / OLAP 分离 预计算 数据仓库 ClickHouse 异步统计如果一条报表每次查询都需要扫描 10 亿销售明细 扫描 5 亿库存流水 扫描 3 亿采购记录那么继续优化 SQL 本身的收益已经有限。此时真正需要考虑的是数据模型和系统架构二十九、最后的判断标准判断 SQL 是否优秀不应该只问执行了多少秒而应该连续问几个问题为了返回这些数据 到底读取了多少数据 实际处理了多少行 某个算子执行了多少次 执行计划是否稳定 数据增长 10 倍后还能不能运行真正优秀的 SQL 应该同时具备低 IO 低 CPU 低 Rows Read 低 Loops 执行计划稳定 数据量增长后性能可预测最终可以把 SQL 性能优化浓缩成一个模型[SQLPerformancef(DataAccess,ExecutionPlan,IO,Rows,Loops,CPU)]其中最值得关注的是[DataAccess]因为绝大多数 SQL 性能问题本质上都可以归结为一句话数据库读取和处理了太多本来不应该处理的数据。因此SQL 查询优化真正的目标不是让数据库“跑得更快”而是让数据库少做无效工作。 SQL 查询性能优化：从执行时间到 IO、执行计划与数据访问路径
SQL 查询性能优化从执行时间到 IO、执行计划与数据访问路径SQL 查询性能优化不能只看“这条 SQL 执行了多少秒”。一条 SQL 当前执行只需要 200ms并不代表它是一条性能良好的 SQL。如果它为了返回 100 行数据却读取了 100 万行、产生几十万次逻辑读那么当数据量、并发量和缓存压力增加后它很可能迅速退化。因此SQL 性能优化的核心不是单纯追求“当前执行得快”而是减少数据库为得到最终结果所付出的资源成本。可以把 SQL 查询性能抽象为[Performance f(ExecutionPlan, IO, CPU, Rows, Loops, Memory, Wait)]也就是说SQL 性能主要由以下几个因素决定执行计划逻辑 IO物理 IOCPU 消耗实际处理行数算子执行次数内存使用锁和等待一套好的 SQL 优化方法应该围绕这些指标展开而不是只看执行时间。一、SQL 性能优化首先看什么建议固定观察以下 7 个核心指标Execution Time执行时间CPU TimeCPU 时间Logical Reads逻辑读取Physical Reads物理读取Rows Read读取行数Rows Returned最终返回行数Operator Loops算子执行次数其中最容易被忽略、但又极其重要的是Logical Reads Rows Read Loops因为这三个指标更能反映 SQL 的真实成本。例如返回数据100 行 扫描数据1,000,000 行那么数据访问效率为[Efficiency \frac{RowsReturned}{RowsRead}]即[Efficiency \frac{100}{1000000} 0.01%]这意味着数据库做了大量无效工作。即使当前查询只运行 300ms这条 SQL 依然值得优化。二、不要只看执行时间这是 SQL 性能分析中最重要的原则之一。例如一条 SQLElapsed Time: 200ms Logical Reads: 900000 Physical Reads: 0看起来只执行了 200ms。但是Physical Reads 0很可能只是因为数据已经存在 Buffer Cache 中。数据库并没有真的从磁盘读取而是直接从内存中读取了大量数据。此时 SQL 的真实问题仍然存在Logical Reads 900000随着以下条件发生变化数据量增加 并发增加 Buffer Cache 竞争增加 数据库内存压力增加查询性能可能变成200ms ↓ 1s ↓ 5s ↓ 10s所以应该区分两个概念执行时间 当前环境下 SQL 跑得快不快 IO / Rows / Loops SQL 本身设计得好不好SQL 优化更应该关注第二个问题。三、SQL Server 查询性能分析SQL Server 提供了一套非常成熟的性能分析工具。最常用的是setstatisticsioon;setstatisticstimeon;select...from...where...;setstatisticsiooff;setstatisticstimeoff;其中setstatisticsioon;用于观察 IO。例如Table LSXHDMX. Scan count 9, logical reads 928764, physical reads 0这里最值得关注的是logical reads 928764表示 SQL Server 从 Buffer Pool 中读取了大约 92 万个数据页。SQL Server 一个 Page 默认 8KB。所以大致访问的数据量为[928764 \times 8KB]约等于[7.1GB]这并不代表真的从磁盘读取了 7GB而是表示 SQL Server 在内存页层面进行了如此大量的数据访问。这通常说明存在大范围扫描 索引不合适 Nested Loop 被大量重复执行 过滤条件没有有效下推 关联顺序不合理 统计信息估算错误四、SQL Server 的 Statistics IO 怎么看典型输出Table OrderItem. Scan count 20, logical reads 100000, physical reads 0, read-ahead reads 200几个重要指标分别表示1. Logical Readslogical reads表示数据库从 Buffer Pool 中读取的数据页数量。这是 SQL Server 优化中最应该关注的指标之一。通常来说在返回结果基本一致的情况下 Logical Reads 越少越好例如优化前logical reads 500000优化后logical reads 5000那么即使执行时间暂时变化不大这通常也是一次非常成功的优化。2. Physical Reads表示数据库真正从存储设备读取的数据页数量。例如physical reads 0并不意味着查询成本很低。它往往只意味着数据已经在内存中所以 SQL 优化不能只看 Physical Reads。3. Scan Count表示相关访问路径执行的次数。例如Scan count 1通常问题不大。如果出现Scan count 10000那么应该重点检查执行计划。很多情况下是Nested Loop导致内部表被重复访问。五、一定要看实际执行计划SQL 优化不能只看 SQL 文本。真正决定数据库如何执行的是Execution Plan也就是执行计划。SQL Server 中建议直接查看Actual Execution Plan重点关注几个节点Table Scan Index Scan Index Seek Nested Loops Hash Match Merge Join Sort Key Lookup RID Lookup Spool六、Scan 不一定差Seek 不一定好很多开发人员会形成一种简单认知Index Seek 好 Index Scan 差 Table Scan 很差实际上并不完全正确。例如查询select*fromsaleswheresale_date2026-01-01;如果这条 SQL 最终需要返回表中 70% 的数据那么Index Scan可能比Index Seek 大量 Key Lookup更高效。所以判断执行计划不能只看算子名字而应该同时看Rows Cost IO Loops Lookup 次数优化的目标不是强制所有查询都变成 Index Seek而是让数据库用最低成本获取目标数据七、重点检查 Actual Rows 和 Estimated Rows执行计划中一个非常关键的指标是Estimated Rows Actual Rows例如Estimated Rows 100 Actual Rows 1,000,000说明优化器估算严重错误。数据库原本认为只有 100 行于是可能选择Nested Loop但实际数据是100 万行最终导致 Nested Loop 内层执行几十万甚至几百万次。这种情况通常需要检查统计信息 参数嗅探 数据倾斜 临时表 表变量 表达式 关联条件因此执行计划分析中一个非常重要的问题是优化器以为有多少行实际上有多少行八、重点观察 Rows × Loops很多慢 SQL 的真正问题不是单次查询慢而是某个操作执行了太多次。例如Index Seek Actual Rows 100 Actual Number of Executions 10000那么实际处理量大约是[100 \times 100001,000,000]也就是说虽然执行计划上看只是一个普通的 Index Seek但它被执行了一万次。这是典型的Nested Loop 放大效应因此分析 SQL 时建议建立一个简单意识[TotalWork\approxRows\timesLoops]很多性能问题都可以通过这个公式发现。九、索引优化的核心不是“加索引”SQL 性能问题出现后最常见的处理方式就是加索引但这是一种非常危险的习惯。正确的索引设计应该从查询模式出发。例如selectproduct_code,quantity,amountfromsale_detailwhereshop_code001andsale_date2026-09-01andsale_date2026-10-01;可以考虑索引createindexix_sale_detail_shop_dateonsale_detail(shop_code,sale_date)include(product_code,quantity,amount);这里shop_code sale_date用于定位数据。而product_code quantity amount通过 INCLUDE 覆盖查询。这样可以减少Key Lookup十、索引字段顺序非常重要联合索引(a,b,c)并不等价于(c,b,a)通常可以按照下面的思路设计等值条件 ↓ 范围条件 ↓ 排序 / 分组 ↓ Include 字段例如whereshop_code?andproduct_code?andsale_datebetween?and?那么可能考虑(shop_code, product_code, sale_date)而不是简单按照数据库字段原来的顺序创建索引。十一、避免对索引字段进行函数计算典型错误whereconvert(varchar,rq,23)2026-09-28或者whereyear(rq)2026这会导致数据库很难直接使用rq上的索引进行 Seek。更推荐whererq2026-09-28andrq2026-09-29同样whereyear(rq)2026应该考虑改成whererq2026-01-01andrq2027-01-01优化原则是尽量不要加工索引列而应该加工查询参数。十二、日期范围推荐使用左闭右开日期查询非常容易写出问题。例如whererqbetween2026-09-01and2026-09-30 23:59:59这种写法容易受到datetime datetime2 毫秒精度影响。推荐统一使用whererq2026-09-01andrq2026-10-01即[[startDate,endDate)]这种方式更准确更容易使用索引更容易生成动态 SQL更容易统一报表日期逻辑十三、减少无效数据读取SQL 优化中最有效的方法之一不是“让数据库执行得更聪明”而是让数据库少读数据例如select*fromsale_detail;如果实际上只需要selectspdm,sl,jefromsale_detail;那么应该避免select *。尤其是在大宽表 LOB text json varchar(max)场景下读取不需要的字段会明显增加IO 内存 网络传输 序列化成本十四、尽早过滤数据例如select...from(select*fromsale_detail)awherea.shop_code001;逻辑上可以工作但复杂 SQL 中应该尽量让过滤条件尽早进入数据源。理想结构select...fromsale_detailwhereshop_code001;在复杂报表中尤其要关注Predicate Pushdown即过滤条件是否真正下推到最底层的数据访问节点。优化目标应该是先减少数据 再关联 再聚合 再排序而不是先把所有数据关联起来 ↓ 最后再过滤十五、JOIN 是性能优化重点大型业务 SQL 中JOIN 往往是主要性能瓶颈。例如ajoinbjoincjoindjoine最终性能取决于各表数据量 过滤条件 JOIN 字段索引 JOIN 顺序 统计信息 选择性 执行计划尤其要关注大表 JOIN 大表例如销售明细5 亿行 库存流水3 亿行 采购明细1 亿行如果直接 Joinsalejoinstockjoinpurchase很容易出现数据量爆炸。更好的办法通常是先按业务粒度分别聚合 ↓ 再 JOIN例如sale 先 group by shop product stock 先 group by shop product purchase 先 group by shop product 最后三份结果 JOIN这样可以大幅降低 Join 数据量。十六、GROUP BY 前尽量减少数据例如selectshop_code,product_code,sum(quantity)fromsale_detailgroupbyshop_code,product_code;如果 sale_detail 有 5 亿条数据那么 Group By 本身成本可能很高。如果实际只统计最近 7 天whererqdateadd(day,-6,endDate)andrqdateadd(day,1,endDate)一定要在 Group By 之前过滤。即5亿 ↓ 先过滤 ↓ 500万 ↓ 再 GROUP BY而不是5亿 ↓ GROUP BY ↓ 最后再过滤十七、临时表不是坏东西很多人认为一条 SQL 写完比临时表拆分更高级。实际上对于复杂业务查询这并不一定成立。例如一个 SQL 同时存在销售 退货 库存 采购 价格 会员 促销 门店 商品全部写成一个巨大 CTEwithaas(...),bas(...),cas(...),das(...)select...优化器可能很难得到稳定执行计划。此时可以合理使用select...into#salefrom...createindexix_sale...on#sale(...);然后select...from#salejoin...临时表有几个重要价值中间结果物化 降低查询复杂度 重新生成统计信息 建立临时索引 限制数据规模 提高执行计划稳定性所以临时表应该被视为一种查询执行策略而不是性能优化失败的标志。十八、CTE 并不等于缓存例如withsaleas(select...fromsale_detail)select...fromsale;很多开发人员误以为sale 已经执行一次并保存起来实际上 CTE 更多只是查询表达式数据库完全可能把它重新展开到最终执行计划中。所以复杂查询中如果同一个中间结果被反复使用应该观察执行计划。必要时使用临时表 物化视图 中间汇总表十九、ORDER BY 和 SORT 成本不能忽略排序orderbyamountdesc看起来很简单。但如果需要排序 1000 万行成本可能非常高。执行计划可能出现Sort并且发生Memory Spill即排序所需内存不足将数据写入 TempDB 或磁盘。因此应该关注排序行数 排序字段是否有索引 Memory Grant TempDB Spill很多报表 SQL 的真正瓶颈并不是 Join而是大规模 Sort二十、分页查询需要特别设计常见分页orderbyidoffset1000000rowsfetchnext20rowsonly;页数越深性能越差。因为数据库仍然可能需要处理前面的100 万行大型系统可以考虑Seek Pagination也叫Keyset Pagination例如whereidlastIdorderbyidfetchnext20rowsonly;这样性能更容易保持稳定。二十一、避免 N1 查询SQL 优化不仅发生在数据库端。ORM 系统经常出现查询订单 100 条 ↓ 每个订单查询一次明细 ↓ 101 次数据库访问也就是N 1即使每条 SQL 只执行 5ms[101 \times 5ms]也已经超过 500ms。所以应用侧同样应该观察一次请求执行了多少 SQL而不仅仅是某一条 SQL 执行了多久二十二、MySQL 怎么分析 SQLMySQL 8 推荐explainanalyzeselect...重点看actual time rows loops Table scan Index lookup Nested loop例如actual rows10000 loops1000说明实际处理规模约为[10000\times10001000万]因此 MySQL 同样应该重点观察Rows Loops而不是只看执行时间。二十三、PostgreSQL 怎么分析 SQLPostgreSQL 推荐explain(analyze,buffers)select...其中Buffers: shared hit shared read非常重要。可以近似理解shared hit ≈ SQL Server logical reads shared read ≈ 实际读取例如shared hit900000 shared read0和 SQL Serverlogical reads900000 physical reads0反映的是类似的问题。二十四、Oracle 怎么分析 SQLOracle 可以通过setautotraceonstatistics;查看consistent gets physical reads rows processed其中consistent gets与 SQL Serverlogical reads非常类似。更加专业的分析可以结合DBMS_XPLAN查看E-Rows A-Rows即Estimated Rows Actual Rows其核心思想与 SQL Server 完全一致。二十五、建立统一的跨数据库性能模型实际上不同数据库虽然命令不同但底层问题高度一致。可以统一成下面的分析模型SQL │ ▼ Execution Plan │ ┌──────────┼──────────┐ ▼ ▼ ▼ IO CPU Memory │ ▼ Rows × Loops │ ▼ Actual Work │ ▼ Execution Time其中Execution Time更像最终表现。而IO Rows Loops Execution Plan才是根因。因此可以把 SQL 优化过程抽象成[SQL\rightarrowPlan\rightarrowIO\rightarrowRows\rightarrowLoops\rightarrowTime]二十六、推荐的 SQL 性能优化流程实际工作中可以固定按照下面的顺序分析。第一步确认 SQL首先保存原始 SQL。不要一开始就修改 SQL否则后面无法比较优化效果。第二步记录基准数据记录执行时间 CPU 时间 Logical Reads Physical Reads 返回行数例如Elapsed Time 3200ms CPU Time 2800ms Logical Reads 928764 Rows Returned 2300第三步查看实际执行计划检查Table Scan Index Scan Index Seek Nested Loop Hash Match Sort Key Lookup Spool第四步寻找最大成本节点不要平均用力。通常应该寻找读取数据最多的节点 执行次数最多的节点 行数估算偏差最大的节点 IO 最大的节点也就是寻找[MaxCostOperator]第五步检查 Rows × Loops例如Rows 5000 Loops 10000那么这个节点很可能就是问题所在。第六步检查索引确认过滤字段 Join 字段 排序字段 Group By 字段是否存在适合的索引。第七步检查数据是否可以提前减少考虑提前 WHERE 提前 GROUP BY 拆临时表 减少 JOIN 减少字段第八步重新执行 SQL再次运行setstatisticsioon;setstatisticstimeon;获得新的指标。第九步对比优化前后例如指标优化前优化后Logical Reads928,76421,430CPU2,800ms320msElapsed3,200ms410msRows2,3002,300这样的优化才是可以量化和验证的。二十七、一个非常重要的优化原则SQL 性能优化的核心目标可以浓缩成一句话用尽可能少的数据访问得到相同的业务结果。因此[Performance\propto\frac{UsefulData}{TotalWork}]如果读取 100 万行 返回 100 行通常意味着查询效率较低。如果读取 120 行 返回 100 行则查询的数据访问路径通常非常理想。二十八、SQL 性能优化的三个层次从架构角度可以把 SQL 优化分成三个层次。第一层SQL 语句优化例如WHERE JOIN GROUP BY ORDER BY 子查询 CTE 窗口函数第二层数据库访问结构优化例如索引 分区 统计信息 物化视图 临时表 汇总表第三层系统架构优化例如缓存 读写分离 报表库 OLTP / OLAP 分离 预计算 数据仓库 ClickHouse 异步统计如果一条报表每次查询都需要扫描 10 亿销售明细 扫描 5 亿库存流水 扫描 3 亿采购记录那么继续优化 SQL 本身的收益已经有限。此时真正需要考虑的是数据模型和系统架构二十九、最后的判断标准判断 SQL 是否优秀不应该只问执行了多少秒而应该连续问几个问题为了返回这些数据 到底读取了多少数据 实际处理了多少行 某个算子执行了多少次 执行计划是否稳定 数据增长 10 倍后还能不能运行真正优秀的 SQL 应该同时具备低 IO 低 CPU 低 Rows Read 低 Loops 执行计划稳定 数据量增长后性能可预测最终可以把 SQL 性能优化浓缩成一个模型[SQLPerformancef(DataAccess,ExecutionPlan,IO,Rows,Loops,CPU)]其中最值得关注的是[DataAccess]因为绝大多数 SQL 性能问题本质上都可以归结为一句话数据库读取和处理了太多本来不应该处理的数据。因此SQL 查询优化真正的目标不是让数据库“跑得更快”而是让数据库少做无效工作。