一、90%的程序员，都踩过数据库优化的坑

做开发的都懂一个痛：上线初期跑得飞起的系统，随着数据量暴涨，突然就变得卡顿不堪——查询耗时从几十毫秒飙升到几秒，高峰期甚至直接超时，用户投诉不断，老板催着整改，自己对着数据库日志熬到深夜，却越改越乱。

有人上来就加索引，结果索引加了一堆，数据库反而更慢；有人盲目用缓存，却陷入数据一致性的泥潭；还有人跟风做分区，折腾半天却没解决核心问题。其实，数据库优化从来不是“一刀切”的操作，就像医生治病，得先找准病因，再对症下药。

今天，我们就拆解一篇国外技术大牛的实战指南，搞懂数据库变慢的4大核心病因，以及索引、反规范化、缓存、分区这4种“急救方案”的正确用法——学会这些，再也不用为数据库卡顿熬夜，还能轻松搞定面试中的优化难题。

关键技术补充：4种急救方案，开源免费可直接落地

本文核心涉及的4种数据库优化技术，均为开源免费，无需额外付费，是后端开发、DBA必备的基础技能，在GitHub上相关实践项目星标均突破10万+，广泛应用于互联网、电商、金融等各类场景：

1. 索引（Index）：所有关系型数据库（MySQL、PostgreSQL、Oracle）均原生支持，无需额外配置，核心作用是加速查询，减少数据扫描范围；

2. 反规范化（Denormalize）：无需依赖第三方工具，通过调整数据表结构实现，核心是牺牲部分规范化，换取查询性能提升；

3. 缓存（Cache）：常用Redis、Memcached等开源工具，均为免费开源，核心是将高频查询结果缓存起来，减少数据库访问压力；

4. 分区（Partition）：主流关系型数据库原生支持，无需额外插件，核心是将大表拆分，降低单表数据量，提升读写性能。

二、核心拆解：先找病因，再治慢病（附实战操作）

数据库变慢，本质不是“数据库本身不行”，而是它正承受着某种特定的“痛苦”——要么是CPU过载，要么是IO读写太多，要么是锁竞争激烈，要么是查询方式本身就错了。只有先找准这4种病因，才能精准选择优化方案。

我们先搭建一个实战场景（以PostgreSQL为例，MySQL用法类似），用一个常见的“订单系统”演示，所有代码可直接复制运行，新手也能轻松上手。

第一步：搭建实战场景，模拟数据 skew

我们创建3张核心表：用户表（customers）、订单表（orders）、订单项表（order_items），并插入大量测试数据，模拟“普通用户”和“大用户”的数据差异（实际场景中，这种数据 skew 是导致数据库变慢的常见原因）。

-- 创建用户表CREATE TABLE customers (  id         BIGSERIAL PRIMARY KEY,  account_id BIGINT NOT NULL,  email      TEXT NOT NULL);-- 创建订单表CREATE TABLE orders (  id          BIGSERIAL PRIMARY KEY,  account_id  BIGINT NOT NULL,  customer_id BIGINT NOT NULL REFERENCES customers(id),  status      TEXT NOT NULL,  total_cents INTEGER NOT NULL, -- 金额（单位：分，对应人民币）  created_at  TIMESTAMPTZ NOT NULL DEFAULT now());-- 创建订单项表CREATE TABLE order_items (  id            BIGSERIAL PRIMARY KEY,  order_id      BIGINT NOT NULL REFERENCES orders(id),  sku           TEXT NOT NULL,  qty           INTEGER NOT NULL,  price_cents   INTEGER NOT NULL -- 单价（单位：分，对应人民币）);-- 给订单表添加account_id索引（初始索引）CREATE INDEX idx_orders_account ON orders(account_id);-- 加快数据插入速度（临时设置）SET synchronous_commit = off;-- 插入测试数据：200个普通账户，1个大账户（999）-- 1. 普通账户：200个账户 × 每个账户200个用户 = 40000个用户INSERT INTO customers(account_id, email)SELECT a.account_id, 'user_' || a.account_id || '_' || g || '@example.com'FROM generate_series(1, 200) AS a(account_id),     generate_series(1, 200) AS g;-- 2. 大账户（999）：20000个用户INSERT INTO customers(account_id, email)SELECT 999, 'big_' || g || '@example.com'FROM generate_series(1, 20000) AS g;-- 3. 普通账户订单：每个用户约50个订单 → 40000 × 50 = 200万个订单INSERT INTO orders(account_id, customer_id, status, total_cents, created_at)SELECT  c.account_id,  c.id,  CASE WHEN random() < 0.7 THEN 'paid' ELSE 'refund' END, -- 70%已支付，30%退款  (random()*50000)::int, -- 订单金额：0-500元  now() - (random() * interval '365 days') -- 订单时间：近一年FROM customers cCROSS JOIN generate_series(1, 50) AS g(n)WHERE c.account_id BETWEEN 1 AND 200;-- 4. 大账户（999）订单：每个用户约200个订单 → 20000 × 200 = 400万个订单INSERT INTO orders(account_id, customer_id, status, total_cents, created_at)SELECT  999,  c.id,  CASE WHEN random() < 0.7 THEN 'paid' ELSE 'refund' END,  (random()*50000)::int,  now() - (random() * interval '365 days')FROM customers cCROSS JOIN generate_series(1, 200) AS g(n)WHERE c.account_id = 999;-- 更新数据库统计信息，让查询优化器更精准ANALYZE;

第二步：模拟核心接口，复现慢查询问题

我们模拟一个实际开发中最常见的接口：“根据账户ID，筛选订单状态，分页查询订单列表”，这也是很多系统中最容易卡顿的接口。

-- 核心查询接口：分页查询某个账户的特定状态订单SELECT o.id, o.status, o.total_cents, o.created_at, c.emailFROM orders oJOIN customers c ON c.id = o.customer_idWHERE o.account_id = $1 -- 账户ID（参数） AND o.status = $2 -- 订单状态（参数）ORDER BY o.created_at DESCLIMIT 50 OFFSET $3; -- 分页：每页50条，偏移量$3

第三步：0成本排查：先确认“慢”的根源在数据库

很多时候，我们误以为是数据库变慢，其实是应用程序、网络或请求量的问题。在动手优化数据库前，先做这1步排查，避免做无用功：

核心判断：查看接口的p95响应时间，是否与数据库查询时间高度相关（p95响应时间：95%的请求所花费的时间，能反映接口的真实性能）。

用如下伪代码，在应用程序中打印“数据库查询时间”和“接口总耗时”，对比两者即可：

// 伪代码：记录接口总耗时和数据库查询耗时const start = performance.now(); // 接口开始时间const dbStart = performance.now(); // 数据库查询开始时间// 执行数据库查询const rows = await db.query(sql, params);const dbMs = performance.now() - dbStart; // 数据库查询耗时const totalMs = performance.now() - start; // 接口总耗时// 打印日志，用于分析logger.info({ dbMs, totalMs, route: "/orders" }, "request timing");

如果数据库查询耗时（dbMs）占接口总耗时（totalMs）的80%以上，说明慢的根源在数据库，继续往下优化；否则，先优化应用程序、网络或请求频率。

第四步：找准4大病因，对应4种急救方案

排除了应用和网络问题后，我们重点分析数据库的4大核心病因，每种病因对应具体的症状、排查方法和优化方案，全部附实战代码，可直接落地。

病因1：IO-bound（IO过载，读写太多）

症状：磁盘延迟升高，缓存命中率下降，查询速度随数据量增长而变慢，执行EXPLAIN时，会显示“ sequential scan（全表扫描）”，且有大量的磁盘读取。

排查方法：执行如下查询，查看执行计划（以大账户999为例）：

EXPLAIN (ANALYZE, BUFFERS)SELECT id, status, total_cents, created_atFROM ordersWHERE account_id = 999 AND status = 'paid'ORDER BY created_at DESCLIMIT 50 OFFSET 50000;

执行结果会显示3个关键问题：① 全表扫描（Parallel Seq Scan on orders）；② 排序溢出到磁盘（Sort Method: external merge Disk）；③ 大量磁盘读取（shared read 数值巨大）。

原因：大账户999有400万个订单，查询时需要全表扫描筛选数据，还要对大量数据排序，排序数据太大，无法放入内存，只能溢出到磁盘，导致IO过载。

急救方案：添加复合索引（最优先、最有效），精准匹配查询模式。

-- 方案1：添加复合索引，匹配“筛选+排序”模式-- 筛选条件：account_id、status；排序条件：created_at DESCCREATE INDEX idx_orders_account_status_created_at  ON orders (account_id, status, created_at DESC);-- 方案2：添加覆盖索引（如果只需要查询特定字段，进一步提升性能）-- INCLUDE 包含查询需要的字段，无需回表查询CREATE INDEX idx_orders_list_covering  ON orders (account_id, status, created_at DESC)  INCLUDE (total_cents, customer_id);

优化效果：添加索引后，查询会从“全表扫描”变为“索引扫描”，排序直接在索引中完成，无需磁盘溢出，查询耗时从2秒以上降至50毫秒以内。

病因2：CPU-bound（CPU过载，计算太多）

症状：数据库CPU占用率居高不下，即使磁盘不忙，查询也很慢；执行EXPLAIN时，会显示大量的排序、哈希、聚合操作，或频繁调用函数、JSON解析、正则表达式。

排查方法：我们先给订单表添加一个JSONB字段（模拟实际场景中的复杂数据），再执行一个统计查询，查看执行计划：

-- 1. 给订单表添加JSONB字段（存储额外 metadata）ALTER TABLE ordersADD COLUMN IF NOT EXISTS metadata JSONB NOT NULL DEFAULT '{}'::jsonb;-- 2. 给metadata字段插入测试数据（渠道、优惠券、风险值）UPDATE ordersSET metadata = jsonb_build_object( 'channel', (ARRAY['web','mobile','partner'])[1 + floor(random()*3)], 'coupon', (ARRAY['none','sale10','vip'])[1 + floor(random()*3)], 'risk', floor(random()*100));-- 3. 更新统计信息ANALYZE orders;-- 4. 执行统计查询（模拟dashboard面板统计）EXPLAIN (ANALYZE, BUFFERS)SELECT  COUNT(*) FILTER (WHERE status = 'paid')   AS paid_orders,  COUNT(*) FILTER (WHERE status = 'refund') AS refund_orders,  SUM(total_cents) FILTER (WHERE status = 'paid') AS revenue_cents, -- 营收（分）  COUNT(*) FILTER (WHERE (metadata->>'channel') = 'mobile') AS mobile_ordersFROM ordersWHERE account_id = 999  AND created_at >= now() - interval '30 days';

执行结果会显示：聚合操作（Aggregate）占用大量时间，即使缓存命中率很高（shared hit 数值大），查询依然很慢——核心原因是CPU在反复计算大量数据的聚合统计，负担过重。

急救方案：反规范化（Denormalize），创建汇总表（rollup table），提前计算聚合结果，避免重复计算。

-- 方案：创建每日汇总表，提前存储聚合结果-- 1. 创建汇总表（主键：账户ID+日期，确保唯一）CREATE TABLE order_daily_rollups (  account_id    BIGINT NOT NULL,  day           DATE NOT NULL,  paid_orders   INTEGER NOT NULL, -- 每日已支付订单数  refund_orders INTEGER NOT NULL, -- 每日退款订单数  revenue_cents BIGINT NOT NULL, -- 每日营收（分）  mobile_orders INTEGER NOT NULL, -- 每日移动端订单数  PRIMARY KEY (account_id, day));-- 2. 一次性回填历史汇总数据INSERT INTO order_daily_rollups(account_id, day, paid_orders, refund_orders, revenue_cents, mobile_orders)SELECT  account_id,  created_at::date AS day,  COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders,  COUNT(*) FILTER (WHERE status = 'refund') AS refund_orders,  COALESCE(SUM(total_cents) FILTER (WHERE status = 'paid'), 0) AS revenue_cents,  COUNT(*) FILTER (WHERE (metadata->>'channel') = 'mobile') AS mobile_ordersFROM ordersGROUP BY account_id, created_at::date;-- 3. 增量更新汇总表（定时任务，每分钟执行一次）-- 重新计算昨天和今天的数据，避免遗漏，确保数据准确WITH rollup_src AS (  SELECT    account_id,    created_at::date AS day,    COUNT(*) FILTER (WHERE status = 'paid')   AS paid_orders,    COUNT(*) FILTER (WHERE status = 'refund') AS refund_orders,    COALESCE(SUM(total_cents) FILTER (WHERE status = 'paid'), 0) AS revenue_cents,    COUNT(*) FILTER (WHERE (metadata->>'channel') = 'mobile') AS mobile_orders  FROM orders  WHERE created_at >= current_date - 1  GROUP BY account_id, created_at::date)INSERT INTO order_daily_rollups(account_id, day, paid_orders, refund_orders, revenue_cents, mobile_orders)SELECT  account_id, day, paid_orders, refund_orders, revenue_cents, mobile_ordersFROM rollup_srcON CONFLICT (account_id, day)DO UPDATE SET  paid_orders   = EXCLUDED.paid_orders,  refund_orders = EXCLUDED.refund_orders,  revenue_cents = EXCLUDED.revenue_cents,  mobile_orders = EXCLUDED.mobile_orders;

优化效果：原本需要反复聚合400万条数据的查询，现在只需查询汇总表的30条数据（近30天），查询耗时从几百毫秒降至10毫秒以内，CPU占用率大幅下降。

病因3：Lock contention（锁竞争，数据库等待）

症状：CPU和IO都正常，但查询延迟极高，经常出现超时、死锁；高峰期卡顿明显，大量请求排队等待，执行查询时，会显示大量“阻塞会话”。

最常见的原因：热点行更新——多个请求同时更新同一条或少量几条数据，导致锁竞争，所有请求都在等待锁释放。

排查方法：我们先给用户表添加一个“未读消息数”字段（模拟热点行），再查询阻塞会话：

-- 1. 给用户表添加未读消息数字段（热点行，频繁更新）ALTER TABLE customersADD COLUMN IF NOT EXISTS unread_count INTEGER NOT NULL DEFAULT 0;-- 2. 查询阻塞会话，确认锁竞争SELECT  blocked.pid     AS blocked_pid,  blocked.query   AS blocked_query,  blocking.pid    AS blocking_pid,  blocking.query  AS blocking_query,  blocked.wait_event_type,  blocked.wait_eventFROM pg_stat_activity blockedJOIN pg_stat_activity blocking  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))WHERE blocked.wait_event IS NOT NULL;

执行结果会显示：大量会话被阻塞（blocked_pid 数量多），wait_event_type = Lock（锁等待）——说明确实是锁竞争导致的卡顿。

急救方案：3种方案，从简单到复杂，按需选择：

-- 方案1：缩短事务时间（最简单，优先尝试）-- 坏示例：事务中包含外部接口调用，锁被长时间持有/*await db.query("BEGIN");await db.query("UPDATE customers SET unread_count = unread_count + 1 WHERE id=$1", [id]);await callExternalApi(); // 外部接口调用，耗时不确定，锁一直持有await db.query("COMMIT");*/-- 好示例：先执行外部接口，再执行数据库更新，缩短事务时间/*await callExternalApi(); // 先做耗时操作，不持有锁await db.query("BEGIN");await db.query("UPDATE customers SET unread_count = unread_count + 1 WHERE id=$1", [id]);await db.query("COMMIT"); // 快速提交，释放锁*/-- 方案2：重写写操作（推荐，彻底解决热点行）-- 1. 创建事件表，用“插入”替代“更新”CREATE TABLE customer_events (  id          BIGSERIAL PRIMARY KEY,  customer_id BIGINT NOT NULL REFERENCES customers(id),  kind        TEXT NOT NULL, -- 事件类型：unread_increment（未读递增）  created_at  TIMESTAMPTZ NOT NULL DEFAULT now());-- 2. 给事件表添加索引，加速查询CREATE INDEX idx_customer_events_customer_timeON customer_events (customer_id, created_at DESC);-- 3. 用插入事件替代更新热点行INSERT INTO customer_events(customer_id, kind)VALUES (123, 'unread_increment'); -- 123是用户ID-- 4. 定时汇总事件，更新用户未读消息数（异步执行，不影响主流程）UPDATE customers cSET unread_count = sub.cntFROM (  SELECT customer_id, COUNT(*) AS cnt  FROM customer_events  WHERE kind = 'unread_increment'  GROUP BY customer_id) subWHERE c.id = sub.customer_id;-- 方案3：分区（适用于大表、高频删除旧数据的场景）-- 创建按时间分区的订单表（简化版）CREATE TABLE orders_partitioned (LIKE orders INCLUDING ALL)PARTITION BY RANGE (created_at);-- 创建2026年1月的分区CREATE TABLE orders_2026_01 PARTITION OF orders_partitionedFOR VALUES FROM ('2026-01-01') TO ('2026-02-01');-- 后续按月创建分区，删除旧数据时，直接删除分区即可（快速无锁）

病因4：Query-shape（查询方式错误，从根源上错了）

症状：只有部分用户（如大用户）查询慢，小用户查询正常；查询时使用了大量OFFSET、巨型IN列表、OR条件，导致索引失效，查询性能随数据量增长急剧下降。

最常见的问题：OFFSET分页——当OFFSET很大时，数据库需要先扫描并丢弃前N条数据，再返回结果，即使有索引，性能也会很差。

排查方法：执行如下查询（大账户999，OFFSET=50000），查看执行计划：

EXPLAIN (ANALYZE, BUFFERS)SELECT id, status, total_cents, created_atFROM ordersWHERE account_id = 999 AND status = 'paid'ORDER BY created_at DESCLIMIT 50 OFFSET 50000;

执行结果会显示：即使有索引，依然需要扫描大量数据，排序耗时久——核心原因是OFFSET=50000，数据库需要先丢弃前50000条数据，才能返回后面的50条。

急救方案：改用Keyset分页（游标分页），配合复合索引，彻底解决OFFSET分页的痛点。

-- 方案：Keyset分页 + 复合索引-- 1. 创建匹配的复合索引（包含排序字段和唯一ID，避免排序歧义）CREATE INDEX idx_orders_keyset   ON orders (account_id, status, created_at DESC, id DESC);-- 2. 第一页查询（无偏移量）SELECT id, status, total_cents, created_atFROM ordersWHERE account_id = 999 AND status = 'paid'ORDER BY created_at DESC, id DESCLIMIT 50;-- 3. 后续分页查询（用前一页最后一条数据的created_at和id作为游标）-- 假设前一页最后一条数据：created_at='2026-01-01 12:00:00'，id=100000SELECT id, status, total_cents, created_atFROM ordersWHERE account_id = 999   AND status = 'paid'  AND (created_at < '2026-01-01 12:00:00'        OR (created_at = '2026-01-01 12:00:00' AND id < 100000))ORDER BY created_at DESC, id DESCLIMIT 50;

优化效果：Keyset分页无需扫描和丢弃前N条数据，直接通过索引“跳”到目标位置，即使OFFSET很大，查询性能也能保持稳定，耗时始终在50毫秒以内。

补充：缓存（Cache）的正确用法，别用错了！

缓存不是“万能药”，只有在查询方式正确、结果重复率高、允许少量数据 stale 的场景下，才能用缓存——否则会陷入数据一致性的坑，反而增加复杂度。

实战示例（用Redis缓存高频查询结果）：

// 伪代码：用Redis缓存订单列表第一页（高频查询，允许30秒stale）const key = `orders:${accountId}:${status}:page1`; // 缓存key，包含账户ID、状态、页码const cached = await redis.get(key); // 从缓存获取数据// 如果缓存存在，直接返回if (cached) return JSON.parse(cached);// 如果缓存不存在，查询数据库const rows = await db.query(SQL_PAGE1, [accountId, status]);// 存入缓存，设置30秒过期时间（TTL）await redis.set(key, JSON.stringify(rows), "EX", 30);return rows;

核心规则：① 只缓存读多写少、重复率高的查询；② 优先设置TTL（过期时间），避免缓存雪崩；③ 只有必须保证数据实时性时，才添加缓存失效机制（否则尽量简化）。

三、辩证分析：没有最优方案，只有最适合的方案

看完上面的实战操作，很多人会问：索引、反规范化、缓存、分区，到底哪个最好？其实，这4种方案没有绝对的优劣，每种方案都有自己的适用场景和弊端，盲目使用只会适得其反。

1. 索引：好用但不能滥用

优势：实现简单、无需修改业务代码、优化效果立竿见影，是解决IO过载的首选方案；开源免费，原生支持，无额外成本。

弊端：索引会占用额外的存储空间，且写入操作（插入、更新、删除）会变慢——因为每次写入都要同步更新索引。如果盲目添加大量索引，反而会导致数据库整体性能下降。

思考：不是索引加得越多越好，而是要“精准匹配查询模式”——只给筛选、排序、关联的字段添加索引，避免冗余索引，定期清理无用索引。

2. 反规范化：性能提升但牺牲一致性

优势：能彻底解决CPU过载问题，将复杂的聚合查询转化为简单的查表操作，性能提升显著；无需依赖第三方工具，纯表结构调整即可实现。

弊端：牺牲了数据库的规范化，数据存在冗余，需要额外维护汇总表（如定时任务增量更新），增加了系统的复杂度；如果更新逻辑出错，会导致数据不一致。

思考：反规范化适合“读远大于写”的场景（如dashboard统计、报表查询），如果是写密集型场景（如订单创建），不建议使用——毕竟数据一致性，永远比查询速度更重要。

3. 缓存：高效但需应对stale问题

优势：能大幅减少数据库访问压力，查询速度极快（内存读取vs磁盘读取）；开源免费，Redis等工具生态完善，易于集成。

弊端：会出现数据 stale（过期）问题，需要设计缓存失效机制（如TTL、主动删除），增加了业务代码的复杂度；如果缓存雪崩、缓存穿透，会导致数据库瞬间被压垮。

思考：缓存是“锦上添花”，不是“雪中送炭”——只有在查询本身已经优化到位（如添加了正确的索引），且结果重复率高时，才适合用缓存；不要指望用缓存来解决查询方式错误导致的慢查询。

4. 分区：适合大表但操作复杂

优势：能降低单表数据量，提升读写性能，尤其是删除旧数据时，直接删除分区即可，快速无锁；原生支持，无需额外插件。

弊端：操作复杂，需要提前规划分区策略（如时间分区、范围分区），后续维护成本高；如果分区策略设计不合理，反而会导致查询性能下降（如跨分区查询）。

思考：分区只适合“大表”（如千万级、亿级数据量），如果单表数据量在百万级以内，完全不需要分区——简单的索引优化，就能满足性能需求。

四、现实意义：学会这些，搞定80%的数据库优化场景

在实际开发中，数据库卡顿是最常见的性能瓶颈，也是后端程序员、DBA的核心考核点——很多面试中，面试官都会问：“如果数据库变慢，你会怎么优化？”

这篇实战指南的核心价值，不在于教会你某一个具体的SQL语句，而在于传递一种“先诊断、后优化”的思维——很多程序员之所以踩坑，就是因为上来就盲目尝试各种优化方案，却没有找准问题的根源。

总结一下，数据库优化的核心流程的是：① 确认慢查询的根源在数据库；② 用EXPLAIN排查，找准是IO、CPU、锁竞争还是查询方式的问题；③ 选择最适合的优化方案，优先使用简单、低复杂度的方案（如索引），再考虑复杂方案（如分区、反规范化）；④ 优化后，持续监控性能，及时调整。

无论是互联网大厂的高并发系统，还是中小企业的普通业务系统，这套流程都完全适用——学会这些，你不仅能快速解决工作中的数据库卡顿问题，还能提升自己的技术竞争力，轻松应对面试中的各类优化难题。

五、互动话题：你踩过哪些数据库优化的坑？

数据库优化，从来都是“实践出真知”——哪怕是资深DBA，也难免会踩坑。

留言区聊聊：你在工作中，遇到过数据库卡顿的问题吗？你当时是怎么优化的？有没有踩过索引、缓存、分区的坑？

另外，如果你有具体的数据库慢查询场景，也可以在留言区贴出查询语句，我们一起分析优化方案～

关注我，每天分享后端实战技巧，带你避开开发中的那些坑，快速提升技术实力！