【AI大数据工程师特训笔记】第13讲：数据库性能手术刀
目录
5.3 查询重写——将标量子查询改为 JOIN + GROUP BY
第一章：为什么需要数据库性能优化？
在企业级应用中，数据库往往是最容易成为系统瓶颈的环节。一个未经优化的SQL语句，可能从毫秒级响应变成分钟级超时，直接影响用户体验和业务收入。尤其是当数据量达到千万、亿级时，不合理的查询设计会导致数据库服务器CPU飙高、内存耗尽、磁盘I/O拥堵。性能优化的目标就是用最少的资源、最短的时间，返回正确的结果。
本章将以一个真实电商平台为背景，带你掌握从执行计划分析到索引、分区、查询重写等全套优化手段，最终实现 20倍以上 的性能提升。
第二章：执行计划 — SQL的“导航地图”
2.1 什么是执行计划？
执行计划是数据库执行SQL语句时，所采用的操作步骤和算法的详细描述。就像你开车去一个陌生地点，导航会规划出路线（高速、国道、小路），数据库也会为你的查询选择一条“路径”。理解执行计划，是优化的第一步。
2.2 如何查看执行计划？
-- 最基本：只显示计划，不实际执行
EXPLAIN SELECT * FROM users WHERE age > 30;
-- 实际执行并显示真实耗时、缓存命中情况（推荐）
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE age > 30;
-- 更详细的输出（包含输出列信息）
EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM orders WHERE order_date > '2023-01-01';2.3 执行计划中的关键术语（零基础版）
|
术语 |
含义 |
性能启示 |
|---|---|---|
|
Seq Scan |
全表顺序扫描，一行一行读取 |
小表可以接受，大表非常慢 |
|
Index Scan |
使用B-tree索引快速定位 |
效率高，适合过滤条件选择性好 |
|
Index Only Scan |
索引中已包含所需所有列，无需回表 |
最优，速度最快 |
|
Bitmap Heap Scan |
先通过索引找到数据位置，再批量读取 |
适合返回较多行时 |
|
Nested Loop |
嵌套循环连接，外表每行都要匹配内表 |
适用于小表驱动大表且有索引 |
|
Hash Join |
先构建哈希表，再匹配 |
适合两张大表等值连接 |
|
Merge Join |
先排序，再合并 |
适合已排序的数据 |
|
Sort |
显式排序操作 |
消耗内存，可能溢出到磁盘 |
企业口诀：Seq Scan是大忌，Index Scan是利器，Hash Join大表欢，Nested Loop小表迷。
第三章：企业案例背景——某电商平台
3.1 业务场景
该电商平台每天产生数千万订单，需要分析用户购买行为、区域销售趋势等。核心数据表如下：
|
表名 |
记录数 |
说明 |
|---|---|---|
|
users |
3000万 |
用户基本信息 |
|
orders |
5000万 |
订单主表 |
|
order_items |
2亿 |
订单商品明细 |
|
products |
500万 |
商品信息 |
3.2 典型业务查询
查询一：统计每个城市VIP用户的订单总额、订单次数、平均订单金额，按总额降序取前100名。
查询二：分析一线城市（北上广深）的活跃用户（注册超2年），统计其总消费、购买商品种类数、最后一次购买时间等，并筛选出消费≥1000元且订单数≥3的用户。
这两个查询在生产环境执行时间分别为 45秒 和 68秒，用户无法接受。下面我们一步步优化。
第四章：优化前问题诊断
4.1 原查询一（标量子查询方式）
SELECT u.city, u.vip_level,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id) AS order_count,
(SELECT SUM(total_amount) FROM orders o WHERE o.user_id = u.user_id) AS total_amount,
(SELECT AVG(total_amount) FROM orders o WHERE o.user_id = u.user_id) AS avg_amount
FROM users u
WHERE u.vip_level >= 2 AND u.registration_date >= '2022-01-01'
ORDER BY total_amount DESC LIMIT 100;执行计划分析：
-
每个用户会执行 3次 标量子查询（COUNT、SUM、AVG），导致子查询被重复执行数百万次。
-
orders表上没有user_id索引，导致每次子查询都全表扫描。 -
总执行时间：45.2秒。
4.2 原查询二（多表关联方式）
SELECT u.user_id, u.username, u.city, u.vip_level,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(o.total_amount) AS total_spent,
COUNT(oi.item_id) AS item_count,
COUNT(DISTINCT oi.product_id) AS unique_products,
AVG(oi.price * oi.quantity) AS avg_item_value,
MAX(o.order_date) AS last_order_date
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id
AND o.status = 'completed'
AND o.order_date >= '2023-01-01'
LEFT JOIN order_items oi
ON o.order_id = oi.order_id
LEFT JOIN products p
ON oi.product_id = p.product_id
WHERE u.registration_date >= '2020-01-01'
AND u.city IN ('北京','上海','广州','深圳')
AND (p.category_id BETWEEN 1 AND 10 OR p.category_id IS NULL)
GROUP BY u.user_id, u.username, u.city, u.vip_level
HAVING COUNT(DISTINCT o.order_id) >= 3 AND SUM(o.total_amount) >= 1000
ORDER BY total_spent DESC LIMIT 50;执行计划暴露的问题：
-
所有表（users、orders、order_items、products）都使用全表扫描（Seq Scan）。
-
orders表与order_items关联时，缺乏索引导致 Nested Loop 灾难（2亿行×5000万行，天文数字）。 -
GROUP BY之前已产生巨大的中间结果集，内存不足以排序，大量使用临时磁盘文件。 -
总执行时间：68.7秒。
第五章：优化方案实施
5.1 索引优化（最立竿见影）
-- 用户表：复合索引覆盖过滤和排序
CREATE INDEX CONCURRENTLY idx_users_vip_city ON users(vip_level, city, registration_date);
-- 订单表：关键连接字段
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_orders_user_status_date ON orders(user_id, status, order_date);
CREATE INDEX CONCURRENTLY idx_orders_date_status ON orders(order_date, status);
-- 订单详情表
CREATE INDEX CONCURRENTLY idx_order_items_order_id ON order_items(order_id);
CREATE INDEX CONCURRENTLY idx_order_items_product_id ON order_items(product_id);
CREATE INDEX CONCURRENTLY idx_order_items_composite ON order_items(order_id, product_id, price, quantity);
-- 商品表
CREATE INDEX CONCURRENTLY idx_products_category ON products(category_id);CONCURRENTLY 选项允许在不阻塞读写的情况下创建索引，适合生产环境。
5.2 分区表优化（针对时间维度的巨表）
将orders表按order_date进行范围分区，每年一个分区。这样查询特定年份的数据时，只扫描对应分区，避免全表。
-- 创建分区主表
CREATE TABLE orders_partitioned (LIKE orders INCLUDING DEFAULTS) PARTITION BY RANGE (order_date);
-- 创建年度分区
CREATE TABLE orders_2020 PARTITION OF orders_partitioned FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
CREATE TABLE orders_2021 PARTITION OF orders_partitioned FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
CREATE TABLE orders_2022 PARTITION OF orders_partitioned FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE orders_2023 PARTITION OF orders_partitioned FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
-- 迁移数据并建立本地索引
INSERT INTO orders_partitioned SELECT * FROM orders WHERE order_date >= '2020-01-01';
CREATE INDEX idx_orders_part_user_status ON orders_partitioned(user_id, status);5.3 查询重写——将标量子查询改为 JOIN + GROUP BY
优化后查询一：
SELECT u.city, u.vip_level,
COUNT(o.order_id) AS order_count,
COALESCE(SUM(o.total_amount), 0) AS total_amount,
COALESCE(AVG(o.total_amount), 0) AS avg_amount
FROM users u
LEFT JOIN orders_partitioned o ON u.user_id = o.user_id AND o.order_date >= '2022-01-01'
WHERE u.vip_level >= 2 AND u.registration_date >= '2022-01-01'
GROUP BY u.user_id, u.city, u.vip_level
ORDER BY total_amount DESC LIMIT 100;改进点：
-
一次LEFT JOIN完成所有聚合，避免重复子查询。
-
利用分区裁剪（只扫描2022年后的订单分区）。
-
执行时间从45秒降至 1.8秒。
5.4 复杂关联查询——CTE分阶段聚合（优化后查询二）
WITH user_orders AS (
SELECT u.user_id, u.username, u.city, u.vip_level,
COUNT(o.order_id) AS order_count,
SUM(o.total_amount) AS total_spent,
MAX(o.order_date) AS last_order_date
FROM users u
INNER JOIN orders_partitioned o ON u.user_id = o.user_id
WHERE u.registration_date >= '2020-01-01'
AND u.city IN ('北京','上海','广州','深圳')
AND o.status = 'completed'
AND o.order_date >= '2023-01-01'
GROUP BY u.user_id, u.username, u.city, u.vip_level
HAVING COUNT(o.order_id) >= 3 AND SUM(o.total_amount) >= 1000
),
order_items_agg AS (
SELECT oi.order_id,
COUNT(oi.item_id) AS item_count,
COUNT(DISTINCT oi.product_id) AS unique_products,
AVG(oi.price * oi.quantity) AS avg_item_value
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
WHERE p.category_id BETWEEN 1 AND 10
GROUP BY oi.order_id
)
SELECT uo.*,
COALESCE(oia.item_count, 0) AS total_items,
COALESCE(oia.unique_products, 0) AS total_unique_products,
COALESCE(oia.avg_item_value, 0) AS overall_avg_item_value
FROM user_orders uo
LEFT JOIN orders_partitioned o ON uo.user_id = o.user_id
LEFT JOIN order_items_agg oia ON o.order_id = oia.order_id
GROUP BY uo.user_id, uo.username, uo.city, uo.vip_level,
uo.order_count, uo.total_spent, uo.last_order_date
ORDER BY uo.total_spent DESC LIMIT 50;改进点：
-
将多表大连接拆分为两个CTE，分别聚合后再关联，大幅减少中间结果集。
-
利用分区表索引，快速过滤。
-
执行时间从68.7秒降至 3.2秒。
第六章：优化效果对比
|
指标 |
优化前 |
优化后 |
提升倍数 |
|---|---|---|---|
|
查询一执行时间 |
45.2秒 |
1.8秒 |
25倍 |
|
查询二执行时间 |
68.7秒 |
3.2秒 |
21倍 |
|
内存使用 |
8GB+ |
1.2GB |
85%↓ |
|
磁盘I/O |
极高 |
显著降低 |
大量减少 |
|
执行计划操作 |
Seq Scan + 多次子查询 |
Index Scan + Hash Join |
质的飞跃 |
第七章：高级优化技巧
7.1 物化视图——预计算结果集
对于统计报表类查询，可以将聚合结果预先存储为物化视图，并定期刷新。
CREATE MATERIALIZED VIEW mv_user_order_stats AS
SELECT u.user_id, u.city, u.vip_level,
COUNT(o.order_id) AS total_orders,
SUM(o.total_amount) AS total_spent,
AVG(o.total_amount) AS avg_order_value
FROM users u
LEFT JOIN orders_partitioned o ON u.user_id = o.user_id AND o.status = 'completed'
GROUP BY u.user_id, u.city, u.vip_level;
-- 创建索引加速查询
CREATE INDEX idx_mv_city_spent ON mv_user_order_stats(city, total_spent DESC);
-- 并发刷新（不阻塞查询）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_order_stats;7.2 参数化查询（预编译语句）
在应用层多次执行相同结构的查询时，使用预编译语句可避免重复解析SQL，降低CPU开销。
PREPARE user_order_analysis (date, text) AS
SELECT city, vip_level, COUNT(*) AS user_count, SUM(total_amount) AS revenue
FROM users u
JOIN orders_partitioned o ON u.user_id = o.user_id
WHERE u.registration_date >= $1 AND u.city = $2 AND o.status = 'completed'
GROUP BY city, vip_level;
EXECUTE user_order_analysis('2022-01-01', '北京');7.3 监控与持续调优
-- 查找慢查询（需要pg_stat_statements扩展）
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 1000 -- 超过1秒
ORDER BY mean_exec_time DESC LIMIT 10;
-- 检查索引使用率
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan;第八章：知识补充 — 列存表与预编译详解
8.1 PostgreSQL中的列式存储
PostgreSQL原生是行存储，但可以通过扩展实现列存，适用于分析型查询（大量聚合、少选列）。
使用cstore_fdw扩展（开源）：
-- 安装后创建外部服务器
CREATE EXTENSION cstore_fdw;
CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;
-- 创建列存表
CREATE FOREIGN TABLE sales_columnar (
sale_date date,
product_id int,
amount numeric
) SERVER cstore_server OPTIONS (compression 'lz4');注意：列存表不支持更新/删除，也不支持索引，适合只读归档或ETL中间层。
8.2 预编译语句原理
预编译语句（Prepared Statement）将SQL语句的解析、规划与参数执行分离。首次执行时数据库生成执行计划并缓存，后续执行只替换参数，跳过解析阶段。在高并发、重复SQL场景下可减少10%~30%的CPU消耗。
第九章：内存对齐与行存储优化（PostgreSQL存储层调优）
9.1 什么是内存对齐？
内存对齐（Memory Alignment）是计算机系统存储数据的一种方式，它要求数据的起始地址必须是其自身大小的整数倍。例如，4字节的int类型通常需要存储在地址为4的倍数的位置。这样做可以让CPU单次指令读取完整数据，避免跨缓存行访问，从而提升性能。
在数据库领域，内存对齐会影响行存储的紧凑性和CPU缓存效率。PostgreSQL虽然会自动处理基本对齐，但列的声明顺序会影响每行记录的实际磁盘占用和内存占用。一个设计不良的表结构可能因填充字节（padding）浪费20%~30%的空间，进而导致：
-
更多的磁盘I/O（读取更多数据页）
-
更低的缓存命中率
-
更慢的索引扫描和顺序扫描
9.2 PostgreSQL中的行存储结构
PostgreSQL每行数据包含：固定长度的头信息（23字节，对齐到8字节），然后依次存储各列的值。每个列的对齐规则由其数据类型决定：
|
数据类型 |
典型长度 |
对齐要求 |
|---|---|---|
|
|
1字节 |
1字节 |
|
|
2字节 |
2字节 |
|
|
4字节 |
4字节 |
|
|
8字节 |
8字节 |
|
|
4字节 |
4字节 |
|
|
8字节 |
8字节 |
|
|
8字节 |
8字节 |
|
|
变长 |
通常1字节对齐（但指针引用） |
当不同对齐要求的列混合时，PostgreSQL会在列之间自动插入填充字节以满足下一个列的对齐要求，这些填充字节不存储任何业务数据，但占用磁盘和内存。
9.3 一个具体的浪费示例
假设有下表（未优化列顺序）：
CREATE TABLE bad_alignment (
flag char(1), -- 1字节
id bigint, -- 8字节，需要8字节对齐
score int, -- 4字节
note text,
status smallint -- 2字节
);存储布局（简化）：
-
行头（23字节 + 填充到8字节边界 = 24字节）
-
flag：占用1字节，但下一个id需要8字节对齐，因此后面插入7字节填充 → 实际占8字节 -
id：8字节 -
score：4字节，下一个status需要2字节对齐，仅需填充0字节？注意后续text是变长，通常不要求强对齐，但分数后仍可能产生2字节填充（若text起始需要4字节边界？实际变长字段引用指针为8字节，但存储位置较复杂）。 -
实际通过
pg_class查询表大小会发现每一行浪费约15~20%的空间。
优化后列顺序（按对齐大小降序）：
CREATE TABLE good_alignment (
id bigint, -- 8字节
score int, -- 4字节
status smallint, -- 2字节
flag char(1), -- 1字节，后面填充1字节到2字节边界？但总行大小已被优化
note text
);原则：将对齐要求大的列放在前面，小对齐的列放在后面，可以最小化填充字节，让行更紧凑。
9.4 如何检查表的对齐浪费
PostgreSQL没有内置函数直接显示填充浪费，但可以通过比较表实际大小与理论最小大小来估算。
-- 计算理论最小行大小（根据列类型）
SELECT pg_column_size(ROW(
-- 填入各列的典型值，注意变长字段需填入实际长度
1::bigint, 1::int, 1::smallint, 'a'::char(1), ''
)) AS min_row_size;
-- 查看实际平均行大小
SELECT avg(pg_column_size(t)) FROM (SELECT * FROM good_alignment LIMIT 1000) t;此外，使用pageinspect扩展可以更直观地查看页内行布局。
9.5 企业实战案例：订单表列顺序优化
某电商平台的订单表orders原有列顺序（简化）：
order_id (bigint), user_id (bigint), amount (numeric), status (varchar),
created_at (timestamp), is_deleted (smallint), flag (char(1))该表有20亿行，数据量约3.5TB。通过调整列顺序，将is_deleted和flag移动到所有定长数值列之后，并重新建表（CREATE TABLE new AS ... ORDER BY ...），新表占用量减少22%，全表扫描速度提升18%，缓存效率提高。
9.6 优化原则总结
（1）优先将固定长度（定长）且对齐要求大的列放在前面：如bigint、double、timestamp。
（2）将对齐要求小的列（如char(1)、boolean）放在后面。
（3）变长列（varchar、text、bytea）通常放在最后，因为它们不参与对齐填充链。
（4）避免大量char(n)定长字符串，尽量使用varchar（虽然varchar在PG中存储开销类似text，但有长度限制会额外占用1~4字节）。
（5）注意NULL值：每个NULL在行头中占用1个位（bit），但过多NULL不会引起对齐填充问题。
（6）在生产环境修改列顺序：需要CREATE TABLE new + INSERT + RENAME，会影响业务，应安排在维护窗口并配合pg_repack等工具。
9.7 内存对齐与CPU缓存优化延伸
-
数据库的Shared Buffers（共享缓冲区）中存储的是数据页（通常8KB），页内每行的紧凑程度决定了相同内存能容纳的行数。更紧凑的行 = 更高的缓存命中率 = 更少的磁盘I/O。
-
对于频繁更新的表，行因
UPDATE会存储旧版本（MVCC），过度填充可能导致页内死元组更多，加速VACUUM压力。因此初始对齐优化也能间接延长VACUUM周期。
一句话记：大靠前，小靠后，变长末尾放，对齐少浪费，查询快几倍。
第十章：企业最佳实践总结
10.1 亿级数据优化的“黄金法则”
（1）先看执行计划，再写SQL。任何优化都要基于真实执行计划。
（2）索引是首选武器，但不宜过多（影响写入）。常用过滤、关联字段建索引。
（3）避免标量子查询，能用JOIN/聚合代替的就用。
（4）大表按时间分区，查询时自动裁剪分区。
（5）复杂查询拆解为多个CTE或临时表，分阶段聚合。
（6）物化视图适用于实时性要求不高的报表。
（7）定期维护：ANALYZE更新统计信息、REINDEX重建膨胀索引、VACUUM清理死元组。
（8）监控慢查询，建立性能基线。
10.2 从45秒到1.8秒的启示
本案例证明了：90%的性能问题源于糟糕的SQL写法。通过系统性的优化（索引、分区、查询重写），即使亿级数据也能实现亚秒级响应。性能优化不是一蹴而就的，而是一个持续迭代、不断改进的过程。
记住：数据库慢，往往不是数据库慢，而是你的SQL慢。
第十一章 数据开发优化面试指南（企业级高频考点）
本部分以面试问答形式，梳理数据库性能优化中最常见的考点和答题要点，帮助你在面试中自信应对。
11.1 基础概念类
Q1：请解释一下执行计划，如何查看？
执行计划是数据库为SQL语句选择的“操作路线图”。使用
EXPLAIN查看预设计划，EXPLAIN (ANALYZE, BUFFERS)查看真实执行情况。关键术语：Seq Scan（全表扫描）、Index Scan（索引扫描）、Nested Loop（小表循环）、Hash Join（大表哈希连接）。优化目标：将Seq Scan改为Index Scan，将低效连接类型改为高效类型。
Q2：什么是回表？怎么避免？
回表是指通过索引找到行指针后，再去数据页获取完整行的过程。避免方法：
①建立覆盖索引（
CREATE INDEX ... INCLUDE），让索引包含查询所需全部列；②只查询索引中的列（
Index Only Scan）。
Q3：PostgreSQL中的CONCURRENTLY选项有什么用？
在创建索引或刷新物化视图时，加
CONCURRENTLY可以避免锁表，允许并发的DML操作。代价是执行时间更长、资源消耗更高。生产环境必用。
11.2 索引与存储优化
Q4：复合索引的列顺序怎么定？
把等值过滤（=） 的列放前面，范围过滤（>、<、BETWEEN） 的列放后面。同时考虑索引覆盖——如果
WHERE条件后还有ORDER BY，尽量让排序字段也在索引中。
Q5：什么情况下索引会失效？
常见场景：
-
对索引列使用函数（
WHERE upper(name) = 'ABC'）→ 改表达式索引或函数索引。 -
隐式类型转换（如
WHERE phone = 123，phone是varchar）→ 统一类型。 -
使用
!=或<>→ 大部分情况不走索引。 -
LIKE '%abc'（前模糊） → 只有后模糊'abc%'走索引。 -
数据分布倾斜，优化器认为全表扫描更快 → 更新统计信息或强制索引。
Q6：内存对齐优化是什么？能举例吗？
数据库行存储时，CPU要求数据地址是其大小的倍数。不同数据类型对齐要求不同。将对齐要求大的列（
bigint、timestamp）放在前面，小的列（char(1)、smallint）放在后面，变长列（text）放在最后，可以减少行内填充字节，提升缓存命中率。实测可减少15%~25%表体积。
11.3 查询重写优化
Q7：标量子查询有什么问题？怎么改写？
标量子查询会对每一行执行一次子查询，导致复杂度O(N×M)。改写为
LEFT JOIN + GROUP BY，把多行聚合为一行再关联，可提升几十倍性能。
Q8：IN和EXISTS哪个性能好？
子查询结果集小用
IN，子查询结果集大用EXISTS（因为EXISTS可短路，找到即停）。
NOT IN要注意NULL陷阱：子查询包含NULL会导致结果集为空，一般用NOT EXISTS代替。
Q9：大表关联如何优化？
① 分区裁剪：对大表按时间/业务键分区；② 使用
WITH（CTE）分阶段聚合，减少中间结果集；③ 确保关联字段有索引；④ 评估改用Hash JoinvsMerge Join；⑤ 考虑物化视图预计算。
11.4 分区与分片
Q10：分区表能带来哪些好处？
分区裁剪：查询只扫描相关分区，I/O减少。
批量管理：快速
DROP整个分区（如删除历史数据）。并行扫描：可每个分区并行扫描。
Q11：分区键选什么？有哪些注意事项？
首选时间、区域等范围字段。注意：分区键必须是主键的一部分（PostgreSQL限制）；避免分区数过多（建议每分区千万级，总分区<1000）；定期创建新分区。
11.5 数据库参数调优
Q12：work_mem、shared_buffers分别影响什么？怎么设置？
shared_buffers：共享缓存大小，通常设为内存的15%~25%。过大会增加维护开销。
work_mem：单个查询排序、哈希表可用内存。设太小会导致磁盘spill，设太大可能耗尽内存（多个并发同时消耗）。建议从64MB开始测试。
Q13：ANALYZE和VACUUM的作用？
ANALYZE：更新表统计信息，让优化器选择正确计划。大量数据变更后执行。
VACUUM：清理死元组（MVCC留下的过期行），回收磁盘空间，更新可见性映射。VACUUM FULL会锁表并收缩空间，慎用。
11.6 实战场景题
Q14：一张订单表有5亿行，每天新增3000万，查询最近7天订单需要3秒，如何优化到500ms内？
① 分区：按日分区，查询只扫描7个分区。
② 索引：在分区上建立(order_date, user_id)复合索引。
③ 聚类：如果查询主要以时间为范围，可CLUSTER表按order_date排序。
④ 物化视图：预聚合格子粒度（如小时级别）。
⑤ 考虑列存（如果分析型为主）或归档历史分区到外部表。
Q15：线上突然CPU飙高到90%，如何定位？
① 先查pg_stat_activity看活跃查询；
② 启用pg_stat_statements找出耗时最长的SQL；
③ 对该SQL执行EXPLAIN (ANALYZE, BUFFERS)分析执行计划；
④ 常见原因：索引失效、全表扫描、Nested Loop笛卡尔积、统计信息陈旧；
⑤ 紧急时可kill该查询（pg_terminate_backend）。
Q16：你优化过最慢的一个SQL是什么？怎么做的？
参考答案模板：
-
背景：订单明细表2亿行，查询用户近3个月消费排行榜，原SQL使用标量子查询，耗时68秒。
-
分析执行计划发现：全表扫描+重复子查询。
-
优化：建立
(user_id, order_date)索引，将标量子查询改为JOIN + GROUP BY，再用CTE分阶段聚合。优化后耗时1.2秒，提升56倍。 -
收获：永远先看执行计划，相信数据，不猜测。
11.7 面试避坑指南
|
错误回答 |
正确思路 |
|---|---|
|
“加索引就能加速” |
索引要建在过滤/关联列，且要考虑写放大和存储成本。 |
|
“分区表越多越好” |
分区过多会增加规划时间，通常几百个分区为宜。 |
|
“ |
|
|
“调大 |
并发连接多时可能耗尽内存，导致OOM。 |
|
“ |
|
一句话总结面试核心：先看执行计划，索引覆盖再分区，改写子查询，统计信息要勤，参数调优控内存，生产操作加并发。
AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。
更多推荐
2
0- 0
已为社区贡献23条内容
所有评论(0)