数据存储选型:关系库、NoSQL 与检索
本篇要点:拿到一份查询需求和一份数据量估算,能判断该用哪种存储、要不要引入第二种存储,并说清两份数据之间怎么同步。前置文章:《高并发:缓存、异步与容量测算》用缓存、读写分离和分库分表撑住了读流量,这一章处理“数据本身落在哪里”。
一、为什么需要它
2026 年 3 月,一个做母婴电商的团队给我看了一条 SQL。订单表 2000 万行,客服要在后台按“收件人手机号后四位 + 最近 30 天下单”筛出订单,开发写成了这样:
SELECT id, order_no, phone, amount, created_at
FROM orders
WHERE phone LIKE '%8842'
AND created_at > NOW() - INTERVAL 30 DAY
LIMIT 50;这条语句跑了 8.2 秒,把只读库的 CPU 压到 90%。它慢的原因要看执行计划,EXPLAIN 的输出如下:
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 19842130| Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
1 row in set, 1 warning (0.00 sec)type=ALL、key=NULL、rows 接近两千万,三个字段连起来读就是一句话:把整张表扫了一遍,而且没有任何索引可走。原因在 LIKE '%8842'——前导通配符让 B+ 树无法定位起点,只能逐行比较。
他们按《高并发:缓存、异步与容量测算》的办法加了两个只读实例、给列表页加了 Redis 缓存,撑住了一周,然后又被打回来——因为客服每次输的后四位都不一样,缓存命中率不到 3%。
这里有个容易混淆的点:《高并发:缓存、异步与容量测算》那套手段(缓存、读写分离、分库分表)解决的是“同一条查询被重复问很多遍”。而这条查询是“每次问法都不同”,缓存帮不上;LIKE '%8842' 让 B+ 树索引失效,只读实例也帮不上,因为每个实例都要扫全表。
三种能力对应三种存储:缓存解决重复读,B+ 树索引解决“我知道大概在哪一页”,倒排索引(inverted index)解决“我不知道在哪一行,但我知道它含这个词”。选型的第一个动作不是挑产品,是先看查询长什么样。
截至 2026 年 11 月的版本坐标先钉一下,这一块变动快:MySQL 现在同时跑两条线,长期支持版(LTS)是 8.4.x(8.4.13)和 9.7.x(9.7.4,2026-04-21 发布),创新版按年月命名,最新是 26.10.0(2026-10-20,不承诺长期支持)。PostgreSQL 最新大版本是 18,小版本 18.7(2026-11-12),18 带来的 uuidv7()、B-tree skip scan、默认虚拟生成列都会影响你怎么建模。分析侧 ClickHouse 的 LTS 是 26.8,检索侧 Elasticsearch 9.5.6、OpenSearch 3.9.0,文档库 MongoDB 9.0(2026-09-30)。以上以各自官网发布说明为准。
二、核心概念
2.1 存储模型:行、列、文档、倒排
存储模型(storage model)指的是数据在磁盘上的组织单位,它决定了“一次查询要读多少无关数据”。
- 关系模型(relational model):以行(row)为单位连续存放,一行里的所有字段挨在一起。类比:Excel 的一行就是一条记录,翻到那一行,所有列都拿到了。
- 列式模型(columnar model):同一列的值连续存放。类比:把 Excel 按列切开,每列单独装订成册,
SUM(amount)时只需要翻金额那一册。 - 文档模型(document model):整个对象存成一个 JSON/BSON 文档,天然支持嵌套数组。类比:把一个订单连同它的商品明细装进一个信封,取信封就取全了。
- 倒排索引(inverted index):以“词 → 文档编号列表”的方式建表。类比:书末的索引页,从“缓存”这个词能翻到所有提到它的页码。
反例很典型:把订单明细拆成 orders + order_items 两张表,查一个订单要 join,关系库没问题;但把同样的结构放进宽列库(wide-column store,如 HBase、Cassandra)就难受了,因为没有 join,只能靠冗余。反过来,把 200 个字段的埋点事件塞进 MySQL,单行超过 8KB 的行溢出(row overflow)会让每次读都多跳一次页。
五种模型放一起对照,差别集中在“一次写入搬动多少字节”和“一次查询能跳过多少字节”:
| 模型 | 写入单位 | 擅长 | 典型产品 | 别用它做 |
|---|---|---|---|---|
| 关系型 | 行 + 事务 | 多条件组合查询、事务、join | MySQL 9.7、PostgreSQL 18 | 单列全表聚合、模糊检索 |
| 文档型 | 整个文档 | 结构不固定、一次取全 | MongoDB 9.0 | 跨文档事务多的账务 |
| 宽列 | 行键 + 列族 | 海量写入、按行键点查 | HBase、Cassandra 5 | 临时组合条件查询 |
| 列式 | 列批次 | 大范围聚合、压缩比高 | ClickHouse、DuckDB、Parquet | 单行更新、点查 |
| 倒排索引 | 词条 | 全文检索、相关性排序 | Elasticsearch、OpenSearch | 当主库、当账本 |
表 4-1 五种存储模型的写入单位、擅长查询与典型误用
这张表要横着读最后两列。“擅长”说的是写入单位与查询形状的匹配,“别用它做”说的是同一份数据放进去以后会在哪一步难受。选错存储的代价不在安装那一步,而在半年后某个查询无法优化。
2.2 索引与查询模式:最左前缀和覆盖索引
索引(index)是为某几种查询预先排好的顺序,它的价值只在“查询形状(access pattern)”匹配时才兑现。
联合索引 (user_id, created_at) 的排序规则是先按 user_id 排,user_id 相同的再按 created_at 排,这就是最左前缀(leftmost prefix):只有从最左列开始连续用,才能顺着这棵树走。WHERE created_at > '2025-02-01' 单独出现时,树是按 user_id 排的,用不上。
覆盖索引(covering index)指查询要的列全在索引里,不用回表(回表 = 拿索引里的主键再去聚簇索引取整行)。反例:SELECT * 一定回表,索引再合适也要多读一次。
2.3 一致性级别:从 AFTER_SYNC 到 w:majority
一致性级别(consistency level)是“写成功”这句话的定义,各家叫法不同,语义可以对齐到三个档位:主库落盘即返回、多数派确认后返回、异步复制。
MySQL 的半同步复制(semi-synchronous replication)用 rpl_semi_sync_source_wait_point 决定等在哪一步。默认值是 AFTER_SYNC:主库先写 binlog、等从库确认收到、再提交并返回客户端,所以客户端拿到成功时从库至少已经有了这份 binlog。另一个取值 AFTER_POINT 是主库先提交再等确认,从库可能落后。超时由 rpl_semi_sync_source_timeout 控制,默认 10000 毫秒,超时后自动退回异步复制——这一点最容易被忽略:半同步退化成异步时,日志里通常只有一行 warning。
MongoDB 的写法是 w: "majority",即多数派数据节点写进 oplog 才确认。截至 2026 年 11 月的官方文档写明,多数部署的隐式默认写关注就是 majority;但带仲裁节点(arbiter)的副本集有例外,当非仲裁节点数不大于投票多数时默认退成 w: 1。
2.4 多存储组合与同步链路
多存储组合(polyglot persistence)指同一份业务数据落在两种以上存储里,各承担一种查询。它的问题从来不是“写两遍”,而是两份数据在时间里会分叉。
同步有两条路:应用双写(dual write)和变更数据捕获(change data capture,CDC)。CDC 从数据库的日志(MySQL 的 binlog、PostgreSQL 的逻辑复制槽)里读变更,不侵入业务代码。
图 4-1 存储选型的判定顺序:先问事务与查询形状,再问读写比例,最后才落到产品名
如图 4-1 所示,产品名是最后一步。同一份数据可以同时命中多个分支,这时主库只能有一个——能承载事务的那一个,其余都是派生副本。
三、动手实践
我用的环境是 macOS 上 Homebrew 装的 MySQL 8.4.6(独立端口 3406、独立 datadir,不动本机已有实例;8.4 线截至 2026 年 11 月已到 8.4.13),Python 侧是 DuckDB 2.0.0。三个步骤都能照着重跑。
3.1 造 20 万行,先看没有索引时的代价
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
status TINYINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME NOT NULL
) ENGINE=InnoDB;
SET SESSION cte_max_recursion_depth = 300000;
INSERT INTO orders (user_id, status, amount, created_at)
WITH RECURSIVE s(n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 200000
)
SELECT n % 5000 + 1, n % 4, ROUND(10 + (n % 900), 2),
TIMESTAMPADD(SECOND, n * 37, '2025-01-01 00:00:00')
FROM s;
EXPLAIN ANALYZE
SELECT id, amount, created_at FROM orders
WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;运行结果(只留关键行):
-> Limit: 20 row(s) (cost=20000 rows=20) (actual time=42.5..42.5 rows=20 loops=1)
-> Sort: orders.created_at DESC, limit input to 20 row(s) per chunk (actual time=42.5..42.5)
-> Filter: (orders.user_id = 42) (actual time=0.61..42.4 rows=40 loops=1)
-> Table scan on orders (actual time=0.605..36.9 rows=200000 loops=1)要 40 行,扫了 200000 行,表扫描本身 36.9ms,整体 42.5ms。这就是“查询形状没有对应结构”的代价。
3.2 加一条联合索引,同一个查询
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);
ANALYZE TABLE orders;
EXPLAIN ANALYZE
SELECT id, amount, created_at FROM orders
WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;运行结果:
-> Limit: 20 row(s) (cost=14 rows=20) (actual time=0.0855..0.0882 rows=20 loops=1)
-> Index lookup on orders using idx_user_created (user_id=42) (reverse)
(actual time=0.0851..0.087 rows=20 loops=1)42.5ms 变成 0.09ms,扫描行数从 200000 降到 20。注意 (reverse):ORDER BY created_at DESC 能直接用索引反向走,不再额外排序——索引的第二列就是为了这个 ORDER BY 加的,只加 (user_id) 的话 Sort 那一步还会回来。
代价也在同一张表里能看到:information_schema.tables 里 index_length 从 0 涨到 6.52MB,而 data_length 是 9.52MB。索引差不多是数据量的七成。
再补一个更贴近真实取舍的数字。同一台机器上建两张 5 万行、字段完全相同的表,w1 只有主键,w4 额外加 4 个二级索引,各灌 20000 行:
| 无二级索引 w1 | 4 个二级索引 w4 | |
|---|---|---|
| 20000 行 INSERT 用时(三轮) | 0.18s / 0.20s / 0.18s | 0.28s / 0.36s / 0.29s |
| data_length | 1.52MB | 1.52MB |
| index_length | 0MB | 1.66MB |
表 4-2 二级索引对写入耗时与空间占用的实测影响
写入慢了 55% 到 80%,索引空间比数据本身还大。每一条索引都是给写入加的一笔税,加之前要能说出它服务哪条 SQL。
3.3 同一份数据换列式存储
把同样的 20 万行导成 Parquet(列式文件格式,按列压缩),用 DuckDB 查聚合:
import duckdb, os, time
con = duckdb.connect()
con.execute("""
CREATE TABLE orders AS
SELECT (i % 5000 + 1)::BIGINT AS user_id,
(i % 4)::TINYINT AS status,
round(10 + (i % 900), 2)::DECIMAL(10,2) AS amount,
TIMESTAMP '2025-01-01 00:00:00' + to_seconds(i * 37) AS created_at
FROM range(200000) t(i);
""")
con.execute("COPY orders TO '/tmp/orders.parquet' (FORMAT PARQUET, COMPRESSION ZSTD)")
print("parquet 大小 = %.2f MB" % (os.path.getsize('/tmp/orders.parquet') / 1048576))
con.execute("CREATE VIEW p AS SELECT * FROM read_parquet('/tmp/orders.parquet')")
def bench(sql, n=3):
con.execute(sql).fetchall() # 预热
best = float("inf")
for _ in range(n):
t0 = time.perf_counter()
con.execute(sql).fetchall()
best = min(best, time.perf_counter() - t0)
return best * 1000
print("sum(amount) 单列聚合 : %.1f ms" % bench("SELECT sum(amount) FROM p"))
print("5 列全聚合 : %.1f ms" % bench(
"SELECT count(*), sum(user_id), sum(amount), min(created_at), max(created_at) FROM p"))运行结果(DuckDB 2.0.0;毫秒数与机器相关,重跑会在 0.7–0.9ms 附近浮动,文件大小是稳定的):
parquet 大小 = 0.66 MB
sum(amount) 单列聚合 : 0.8 ms
5 列全聚合 : 3.0 ms同一份数据,InnoDB 占 9.52MB,Parquet + ZSTD 占 0.66MB,差 14 倍——列式只压相邻的同类值,status 只有 0 到 3 四个值,压缩率自然高。单列聚合不到 1ms 是因为它只读了 amount 一列的数据页,其余四列的字节一个都没碰。
但要看清代价:这点延迟的前提是“整列顺序读”。改成 WHERE id = 12345 去取单行,列式存储要读很多个 row group 的元数据,反而不如 B+ 树一次三层查找。没有更快的存储,只有更匹配查询形状的存储。
四、常见误解
误解一:NoSQL 一定比 MySQL 快。 上面那张 20 万行的表,加了联合索引是 0.09ms,同数据量的 MongoDB 走索引点查通常在毫秒级同一档,谁快取决于查询形状而不是产品名。NoSQL 的真实优势在“可水平扩展的写入吞吐”和“结构自由”,不在单条查询延迟。真按延迟选型,你会得到一个更慢的系统加上一套没人会运维的集群。
误解二:加了索引查询就会快。 索引生效需要三个条件同时成立:查询条件从最左列开始连续用、类型能对上(VARCHAR 列传数字会隐式转换,索引失效)、选择性够高(在 4 个取值上建索引,优化器会主动放弃它)。我在同一张表上实测到过一个更麻烦的版本。status 只有 4 个取值,先建了单列 idx_status,再加 (status, created_at) 联合索引并 ANALYZE TABLE。两条索引都在时,优化器估算单列索引代价 3624、联合索引代价 17758,于是选了单列——实际耗时 42.1ms 对 7.9ms,估错的量级刚好反过来。用 FORCE INDEX(idx_status_created) 强制后是 7.9ms,因为联合索引能做覆盖索引范围扫描,status 和 created_at 都不用回表。这种时候要么改写成优化器能算准的形态,要么承认统计信息已经不可靠——ANALYZE TABLE 只更新统计,救不回一个本身有偏的代价模型。
误解三:多存储组合就是同步多写一份。 应用双写(dual write)看着简单,但它把“两个存储要么都成功”变成了业务代码要处理的问题:MySQL 提交成功、ES 写入超时,你重试还是丢弃?重试要保证幂等,丢弃要保证能补。上面那个团队最后用的是 CDC——Debezium 3.7.1.Final 从 binlog 读变更写进 ES,业务代码一行没改。代价是链路多了一个组件,延迟从毫秒变成百毫秒级,运维要盯消费者延迟(consumer lag)。这条链路的细节是《数据一致性:缓存双写与最终一致》的主题。
五、小结
《高并发:缓存、异步与容量测算》用缓存、读写分离和分库分表撑住了读流量,这一章回答的是更基本的一问:这份数据本来就该落在哪种存储里。
- 选型第一步看查询形状(access pattern),不是看产品榜单。缓存解决重复读,B+ 树解决定位,倒排索引解决“不确定在哪一行”。
- 20 万行实测:没有索引的表扫描 42.5ms,加
(user_id, created_at)联合索引后 0.09ms;索引占 6.52MB,是数据量 9.52MB 的七成。 - 索引有成本:4 个二级索引让 20000 行批量写入从 0.18s 左右涨到 0.28 至 0.36s,索引空间 1.66MB 比数据 1.52MB 还大。加索引要能说清它服务哪条 SQL。
- 一致性级别要落到具体配置:MySQL 半同步默认
AFTER_SYNC+ 10000ms 超时后退回异步;MongoDB 默认w: "majority",带仲裁节点时例外。 - 引入第二种存储就走 CDC,别走应用双写。主库只能有一个,其余都是派生副本。
六、相关文章
这一章把数据放到了合适的存储里,代价是“主库只有一个、其余都是派生副本”这句话从此要天天兑现。上面“常见误解”第三条里那个 CDC 链路只是被提到,没有展开;两份数据一旦并存,真正难的问题是“谁先写、写漏了怎么发现”。相关文章就进这个正面战场:缓存与数据库双写之后,那段几百毫秒的窗口里到底会发生什么,45% 到 51% 的不一致概率是怎么测出来的,以及怎么把不一致的数据精确找出来。
继续阅读:[《数据一致性:缓存双写与最终一致》 数据一致性:缓存双写与最终一致](/guide/architecture/cache-db-consistency.html)