【数据库】核心概念深入——索引、事务、锁、范式，从入门到进阶

1
有索引：查 id=500 → 读 3~4 个索引页 → 定位 1 个数据页 → 完成
2
无索引：查 id=500 → 扫描全部 10000 页 → 找到 → 完成

1
-- 场景：用户表，经常按 email 登录查询
2
-- 没有索引：全表扫描 100 万行
3
SELECT * FROM users WHERE email = 'sheng@example.com';
4

5
-- 建索引
6
CREATE INDEX idx_users_email ON users(email);
7

8
-- 现在走索引，查询从 800ms → 1ms

1
-- 场景：订单表，经常按 user_id + status 查询
2
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
3

4
-- ✅ 能走索引（最左前缀匹配）
5
SELECT * FROM orders WHERE user_id = 1;
6
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
7

8
-- ❌ 不能走索引（跳过了最左列 user_id）
9
SELECT * FROM orders WHERE status = 'paid';

1
-- ❌ 错误：给 gender 字段建索引
2
-- gender 只有 'male' / 'female' 两个值，索引区分度极低
3
-- 数据库往往直接走全表扫描，索引形同虚设
4
CREATE INDEX idx_users_gender ON users(gender);
5

6
-- ✅ 正确：给高基数字段建索引（email、phone、order_no）
7
-- 这些字段值几乎唯一，索引区分度高，效果显著

1
-- ❌ 错误：对 created_at 做函数运算，索引失效
2
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
3

4
-- ✅ 正确：改成范围查询，索引生效
5
SELECT * FROM orders
6
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

1
索引是"空间换时间"的权衡：
2
查询快 ←→ 写入慢
3
索引少 ←→ 索引多

1
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';

1
BEGIN;
2
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- A 扣 100
3
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- B 加 100
4
COMMIT;

1
-- 场景：创建订单，同时扣减库存
2
BEGIN;
3
  INSERT INTO orders (user_id, product_id, amount) VALUES (1, 101, 299);
4
  UPDATE products SET stock = stock - 1 WHERE id = 101;
5
COMMIT;
6
-- 如果任何一步失败，整个事务回滚，不会出现"订单建了但库存没扣"

1
-- ❌ 错误：把发邮件、调第三方 API 也包进事务
2
BEGIN;
3
  INSERT INTO orders ...;
4
  UPDATE products SET stock = stock - 1 ...;
5
  -- 调用第三方支付 API（网络请求，可能超时 30 秒！）
6
  -- 发送邮件通知...
7
COMMIT;
8
-- 事务持有锁的时间太长，严重影响并发性能

1
-- 悲观锁（MySQL / PG）
2
BEGIN;
3
SELECT * FROM products WHERE id = 101 FOR UPDATE;  -- 锁住这一行
4
UPDATE products SET stock = stock - 1 WHERE id = 101;
5
COMMIT;  -- 提交后自动释放锁

1
-- 乐观锁（应用层实现版本号）
2
-- 读取时记录 version
3
SELECT id, stock, version FROM products WHERE id = 101;
4
-- → stock=10, version=5
5

6
-- 更新时带上 version 条件
7
UPDATE products
8
SET stock = stock - 1, version = version + 1
9
WHERE id = 101 AND version = 5;
10
-- 如果 version 已经被别人改成 6，这条 UPDATE 影响行数为 0
11
-- 应用层检测到影响行数=0，重试整个流程

1
-- 场景：秒杀最后 1 件商品，高并发冲突概率极高
2
BEGIN;
3
SELECT stock FROM products WHERE id = 101 FOR UPDATE;
4
-- 其他事务此时无法修改这行，排队等待
5

6
-- 应用层判断库存
7
-- IF stock > 0:
8
UPDATE products SET stock = stock - 1 WHERE id = 101;
9
INSERT INTO orders (user_id, product_id) VALUES (1, 101);
10
COMMIT;
11
-- ELSE: ROLLBACK（库存不足）

1
-- 场景：多人协作编辑，冲突概率低
2
-- 用户 A 打开文档时，记录 version=10
3
-- 用户 A 保存时：
4
UPDATE documents
5
SET content = '新内容', version = version + 1
6
WHERE id = 1 AND version = 10;
7

8
-- 如果影响行数 = 0，说明有人在 A 编辑期间修改了文档
9
-- 提示用户"文档已被他人修改，请刷新后重试"

1
-- ❌ 错误：事务开始后做了大量计算，长时间持有锁
2
BEGIN;
3
SELECT * FROM orders WHERE user_id = 1 FOR UPDATE;
4
-- ... 在应用层做了复杂的计算，花了 10 秒 ...
5
UPDATE orders SET status = 'processed' WHERE user_id = 1;
6
COMMIT;
7
-- 这 10 秒内，其他所有要修改这些行的事务都被阻塞

1
范式化 → 数据一致性好，但查询要做多个 JOIN
2
反范式化 → 查询快，但数据冗余，更新要同步多处

1
CREATE TABLE orders (
2
  id            BIGINT PRIMARY KEY,
3
  user_id       BIGINT NOT NULL,
4
  product_id    BIGINT NOT NULL,
5
  product_name  VARCHAR(200),   -- 冗余字段
6
  product_price DECIMAL(10,2),  -- 冗余字段
7
  created_at    TIMESTAMP
8
);

1
-- ❌ 错误：把登录记录冗余到用户表
2
ALTER TABLE users ADD COLUMN last_login_ip     VARCHAR(50);
3
ALTER TABLE users ADD COLUMN last_login_city   VARCHAR(100);
4
ALTER TABLE users ADD COLUMN last_login_device VARCHAR(200);
5
-- 每次登录都要 UPDATE users，高并发下锁竞争严重
6
-- 这些字段和"用户"本身没有强关联，应该放独立的登录日志表

1
-- ✅ 正确：独立的登录日志表
2
CREATE TABLE login_logs (
3
  id         BIGINT PRIMARY KEY,
4
  user_id    BIGINT NOT NULL,
5
  ip         VARCHAR(50),
6
  city       VARCHAR(100),
7
  device     VARCHAR(200),
8
  created_at TIMESTAMP
9
);
10
-- 查最近登录：
11
SELECT * FROM login_logs WHERE user_id = ? ORDER BY created_at DESC LIMIT 1;

1
-- MySQL：开启慢查询日志
2
SET GLOBAL slow_query_log = 'ON';
3
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录
4

5
-- PostgreSQL：配置文件 postgresql.conf
6
log_min_duration_statement = 1000  -- 超过 1000ms 记录

1
-- ❌ 慢查询：没有索引，全表扫描 200 万行，耗时 4.2s
2
SELECT * FROM user_actions
3
WHERE user_id = 1001 AND action_type = 'purchase'
4
ORDER BY created_at DESC
5
LIMIT 20;
6

7
-- 分析：WHERE 条件是 user_id + action_type，ORDER BY 是 created_at
8
-- ✅ 建联合索引，把 ORDER BY 的字段也带进去
9
CREATE INDEX idx_user_actions ON user_actions(user_id, action_type, created_at DESC);
10

11
-- 优化后：走索引，耗时 0.8ms，提升约 5000 倍

1
应用 ← 连接池（10-20 个连接）← 数据库

1
// ✅ 正确：lib/prisma.ts，全局单例，复用连接池
2
import { PrismaClient } from '@prisma/client'
3
const prisma = new PrismaClient()
4
export default prisma

1
// ❌ 错误：每次请求都建新连接，极慢
2
async function getUser(id: number) {
3
  const prisma = new PrismaClient()  // 每次都建新连接！
4
  const user = await prisma.user.findUnique({ where: { id } })
5
  await prisma.$disconnect()
6
  return user
7
}

1
用户刚注册 → 写入主库 → 立刻查询（走从库）→ 查不到！
2
原因：主从同步有延迟（通常 10ms-1s，网络差时更长）

1
-- 场景：用户的扩展属性（不同用户有不同的字段）
2
CREATE TABLE user_profiles (
3
  user_id    BIGINT PRIMARY KEY,
4
  extra_info JSONB,
5
  created_at TIMESTAMP
6
);
7

8
-- 对 JSONB 内部字段建 GIN 索引
9
CREATE INDEX idx_vip_level ON user_profiles ((extra_info->>'vip_level'));
10

11
-- 查询 JSONB 内部字段，走索引
12
SELECT * FROM user_profiles WHERE extra_info->>'vip_level' = '3';
13
-- 包含查询
14
SELECT * FROM user_profiles WHERE extra_info @> '{"tags": ["active"]}';