EP02. “数据操作、索引与效能优化”

首页 › PC 工具 › Calculating · SQL · EP02

约 16 分钟· #EP02#SQL

这篇笔记接着 EP01，讲怎么新增/修改/删除数据（INSERT、UPDATE、DELETE），以及子查询跟 CTE 该怎么选。接着讲索引（Index）什么时候该加、什么时候不该加，还有几个常见的效能反面教材（Anti-Patterns）。最后讲事务（Transaction）怎么保证多个操作要嘛全部成功、要嘛全部不生效。前置知识：先看过 EP01，熟悉 SELECT/WHERE/JOIN/聚合分组的基本写法。

重点内容

INSERT、UPDATE、DELETE

-- 插入单行
INSERT INTO users (email, name, role) VALUES ('test@example.com', 'Test User', 'user');

-- 插入多行（比一条条插入快）
INSERT INTO products (name, price, category_id) VALUES
  ('Widget A', 29.99, 1),
  ('Widget B', 49.99, 1),
  ('Gadget C', 99.99, 2);

-- Upsert（存在就更新，不存在就插入）
INSERT INTO user_settings (user_id, theme, notifications)
VALUES (123, 'dark', true)
ON CONFLICT (user_id) DO UPDATE SET
  theme = EXCLUDED.theme,
  notifications = EXCLUDED.notifications;

-- 更新
UPDATE products SET price = 34.99 WHERE id = 42;

-- 条件式更新
UPDATE users SET
  login_count = login_count + 1,
  last_login = NOW()
WHERE id = 123;

-- 删除（小心使用！）
DELETE FROM sessions WHERE expires_at < NOW();

-- 软删除（正式环境更推荐）
UPDATE users SET deleted_at = NOW(), active = false WHERE id = 123;

📌 软删除 vs 硬删除：正式环境常用「软删除」（加一个 deleted_at 时间戳栏位标记，而不是真的执行 DELETE）——这样数据还在，方便日后追溯或复原，也不会因为误删而丢失数据。

子查询（Subquery）vs CTE

-- 子查询（比较难读）
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- CTE（Common Table Expression）——同样的逻辑，清楚很多
WITH avg_price AS (
  SELECT AVG(price) AS value FROM products
)
SELECT * FROM products, avg_price
WHERE products.price > avg_price.value;

复杂案例——用 CTE 算月营收成长率：

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total_amount) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY month
),
monthly_target AS (
  SELECT
    month,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) AS growth
  FROM monthly_revenue
)
SELECT
  month::text,
  revenue,
  prev_month_revenue,
  ROUND((growth / prev_month_revenue * 100), 1) || '%' AS growth_pct
FROM monthly_target
ORDER BY month;

💡 逻辑一样复杂时，CTE 几乎总是比嵌套子查询更好读、更好维护——遇到需要嵌套好几层的子查询，优先考虑改写成 CTE。

索引（Index）要点

-- 建索引（基本）
CREATE INDEX idx_users_email ON users(email);

-- 复合索引（栏位顺序会影响效果！）
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- 唯一索引（同时强制唯一性）
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 部分索引（只对部分行建索引）
CREATE INDEX idx_active_users ON users(id) WHERE active = true;

-- 查看现有索引
\di table_name   -- psql 里的写法
-- 或者：
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

什么时候该加索引：

WHERE 子句里经常用到的栏位
JOIN 用到的栏位（外键）
搭配 WHERE 一起用的 ORDER BY 栏位
高基数（cardinality）栏位（唯一值很多的栏位）

什么时候不该加索引：

小表（少于 100 行）
很少被查询的栏位
低基数栏位（比如布尔值这种只有两三种可能值的栏位）
写入远比读取频繁的表（索引会拖慢写入速度）

效能反面教材（Performance Anti-Patterns）

❌ 别这样写	✅ 该这样写	为什么
`SELECT * FROM users WHERE email = 'x';`	`SELECT id, name, email FROM users WHERE email = 'x';`	`SELECT *` 浪费带宽，也没办法用"仅索引扫描"优化
先查 100 个 user，再逐个查各自的订单（N+1 问题）	用 `JOIN` 一次查完：`SELECT u.*, COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id;`	N+1 会变成 1 次查询 + N 次查询，数据量一大效能就崩了
`SELECT COUNT(*) FROM events;`（大表不带条件）	`SELECT COUNT(*) FROM events WHERE created_at >= NOW() - INTERVAL '30 days';`	不带条件会扫描几百万行，务必先过滤
`WHERE name LIKE '%widget'`（开头带通配符）	`WHERE name ILIKE 'widget%'`（通配符放结尾）	开头带 `%` 没办法用索引，改成全文搜索或 trigram 索引，或至少把字面量放前面
`ORDER BY RANDOM() LIMIT 5`（大表很慢）	用「先抓随机 ID 范围、再 LIMIT」之类的替代写法	`ORDER BY RANDOM()` 要先给全表排序，大表效能很差
`SELECT * FROM logs;`（不带 LIMIT）	`SELECT * FROM logs ORDER BY created_at DESC LIMIT 100 OFFSET 0;`	不加限制会一次返回全部数据，务必分页

事务（Transactions）

BEGIN;

-- 两个账户之间转账
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

INSERT INTO transactions (from_account, to_account, amount)
VALUES (1, 2, 100);

COMMIT;  -- 两个更新一起生效，要嘛都成功、要嘛都不生效

-- 出问题的话：
ROLLBACK;  -- 全部复原

📌 为什么需要事务：像转账这种"必须多个操作一起成功、否则一起失败"的场景，一定要用事务包起来——如果扣了 A 账户的钱、却在加 B 账户余额之前程序崩溃了，没有事务保护的话钱就凭空消失了。

在 Node.js 里的写法：

const client = await pool.connect();
try {
  await client.query('BEGIN');

  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, fromId]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, toId]);

  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release();
}

语法速查

任务	语法
插入一行	`INSERT INTO t (cols) VALUES (vals)`
更新	`UPDATE t SET col = val WHERE cond`
删除	`DELETE FROM t WHERE cond`
聚合函数	`COUNT()`、`SUM()`、`AVG()`、`MIN()`、`MAX()`
合并结果集	`UNION`（去重）／`UNION ALL`（保留全部）
检查是否存在	`EXISTS (subquery)` 或 `IN (...)`
Null 值替代	`COALESCE(col, default)`

适用版本

内容以 PostgreSQL 语法为主（ON CONFLICT、\di、DATE_TRUNC、窗口函数等属于 PostgreSQL 常见写法），MySQL/SQL Server 等其他数据库部分语法可能不同，实际使用前建议对照所用数据库的官方文档确认。

常见错误

❌ 正式环境直接用 DELETE 硬删除重要数据——考虑用软删除（加 deleted_at 栏位标记），保留复原和追溯的可能性
❌ 复合索引栏位顺序随便排——复合索引的栏位顺序会影响查询能不能用上索引，顺序不对等于白建
❌ 遇到"先查列表、再逐一查每条记录关联数据"的写法（N+1 问题）——用 JOIN 一次查完，数据量大的时候差异非常明显
❌ 大表不带 WHERE 条件就跑 COUNT(*) 或 SELECT *——务必先加时间范围或其他条件过滤，否则会扫描全表
❌ 复杂查询逻辑写成好几层嵌套子查询——改写成 CTE（WITH ... AS (...)）会更好读、更好维护，逻辑一样但可读性差很多
💡 转账、库存扣减这类"必须多个操作同时成功或同时失败"的场景，一定要用 BEGIN/COMMIT/ROLLBACK 包成事务，不要让操作各自独立执行

Sources

Blog / Website：

SQL Basics Every Developer Should Know (2026) — https://dev.to/armorbreak/sql-basics-every-developer-should-know-2026-2986