表字段设计的范式与反范式

1NF 第一范式：原子性

规则：列不可再拆分，一个单元格只能存一份原子数据

2NF 第二范式：消除「部分依赖」

规则：所有非主键字段，必须完全依赖【整个复合主键】，不能只依赖主键其中某一列
前置条件：必须先满足 1NF
适用场景：复合主键（多个字段联合做主键）

3NF 第三范式：消除「传递依赖」

规则：规则：非主键字段，不能依赖另一个非主键字段。 即非主键列之间不能存在推导关系。
前置条件：满足 1NF、2NF

BCNF 巴斯 - 科德范式

规则：任何依赖关系，左边必须是候选主键。
3NF 允许主键内部存在依赖；BCNF 堵上这个漏洞。
普通业务系统，绝大多数场景用到 3NF 就足够，一般不用追求 BCNF。

字段设计

索引设计

设计索引要从业务出发，尽可能覆盖业务本身，节省重复的索引空间。

普通索引(单列索引)

适用场景

1. 单字段独立查询：where phone = ?、where status = ?
2. 单列范围查询、排序：create_time > 'xxxx' order by create_time
3. 该字段查询频率高，无其他字段经常搭配一起查询

SQL

-- MySQL / PostgreSQL 通用
CREATE INDEX idx_user_phone ON user(phone);

-- 删除索引
DROP INDEX IF EXISTS idx_user_phone;

-- 修改索引：不能直接修改，只能先删再重建
DROP INDEX IF EXISTS idx_user_phone;
CREATE INDEX idx_user_phone ON user(phone);

优点

• 结构简单，维护成本低；支持单列等值、范围、排序查询
• 相比全表扫描，大幅减少 IO

关键注意

1. 索引占用空间与字段长度正相关；超长字符串建议使用前缀索引
2. 写入（insert/update/delete）会同步维护索引，索引越多写入越慢
3. InnoDB 二级索引叶子节点存储主键 ID，查询命中索引后需要回表

联合索引

适用场景

1. 经常多字段组合查询：where status=1 and type=2
2. 等值条件 + 范围条件组合：where status=1 and create_time > xxx
3. 查询条件 + 排序字段可以放进同一个索引，避免 filesort

-- MySQL / PostgreSQL 通用
-- 顺序非常重要！等值字段放前面，范围字段放后面
CREATE INDEX idx_order_status_ctime ON order(status, create_time);

-- 删除
DROP INDEX IF EXISTS idx_order_status_ctime;

优点

1. 满足最左前缀原则时，可以同时覆盖多个查询条件
2. 设计合理可以实现覆盖索引，直接从索引拿到全部查询字段，消除回表
3. 相比建立多个单列索引，减少索引总数，降低写入开销

核心规则 & 坑

1. 字段顺序铁律：等值条件在前，范围条件在后
2. 遵循最左前缀，跳过前置字段会导致索引失效
3. 不要把无关字段无脑塞进联合索引，索引变长，内存占用上升、区分度下降
4. 索引变更：同样不支持 ALTER 直接修改，删除重建；调整字段顺序等于新建索引

前缀索引

对字符串字段只取前 N 个字符建立索引，节省空间

CREATE INDEX idx_email on users(email(64));

GIN / GiST 索引(PostgresQL)

• GIN：数组、JSONB、全文检索，精准查找
• GiST：模糊查询、范围、地理位置、近似检索

条件索引(Filtered Index)

PostgreSQL 原生支持；SQL Server 支持；MySQL 5.7 不支持，MySQL8.0.13 之后有限支持
在建索引时指定 WHERE 条件，只有满足条件的行才会存入索引，不满足条件的数据完全不出现在索引内。

优势:

• 索引体积大幅缩小（只保留业务高频访问子集）
• 降低写入开销：新增 / 更新数据不满足条件时，不需要维护索引
• 实现条件唯一约束（普通唯一索引做不到）

CREATE INDEX idx_user_active ON user(id, name, status, mobile) WHERE status='active';

-- 命中索引, SQL 过滤条件中必须包含索引定义的条件字段
SELECT * from "user" WHERE status='active' AND mobile='1380000000';
-- 无法使用此索引
SELECT * from "user" WHERE mobile='1380000000';

关联查询

高频问题

• InnoDB 为什么默认使用是 B+Tree 而非 B 树、Hash?
• 联合索引最左前缀原理
• 回表、覆盖索引、索引下推 ICP
• 聚簇索引与非聚簇索引区别
• like 'abc%' 能走索引，'%abc' 不能（B+Tree 有序前缀）