一个反直觉的事实
刚开始用 DuckDB 的时候，我按照 PostgreSQL 的习惯，给每个 WHERE 条件涉及的列都建了索引。结果查询没变快，插入速度反而慢了一倍多。
当时我以为是哪里配置错了，反复检查了好几遍。后来才意识到，DuckDB 是 OLAP 数据库，索引策略和传统 OLTP 数据库完全不同。
这篇文章用实测数据告诉你：DuckDB 什么时候需要索引，什么时候不需要，以及比索引更有效的优化方案。
两种索引类型：免费的和收费的
DuckDB 有两种索引机制，理解它们的区别是做出正确决策的前提。
Min-Max Index（Zonemap）— 免费性能
这是 DuckDB 自动创建的索引，所有通用数据类型列都有。原理很简单：每个 row group（默认 122,880 行）记录该列的最小值和最大值。
执行查询时，DuckDB 先检查 row group 的 min/max 值，如果不满足 WHERE 条件，直接跳过整个 row group。这种机制在数据有序时效果特别好，比如时间序列数据。
关键点是：zonemap 零维护成本，不需要手动创建，也不会拖慢写入速度。
ART Index（Adaptive Radix Tree）— 有代价的优化
ART 索引需要手动创建，语法和传统数据库一样：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
| CREATE INDEX idx_user_id ON orders(user_id);
```sql
ART 索引是一棵自适应基数树，支持快速点查询。但它有几个限制：
1. 创建时必须完全放入内存
2. 每次 INSERT/UPDATE/DELETE 都要维护索引树
3. 对 JOIN、聚合、排序操作没有帮助
4. 索引内存不受 buffer manager 控制，可能挤占查询可用内存
简单说，ART 索引是用写入性能换读取性能。
## 实测性能对比
为了搞清楚 ART 索引到底有多大效果，我在腾讯云上做了详细测试。测试环境是 100 万行电商订单数据，DuckDB 1.5.5。
### 查询性能测试
| 查询类型 | 无索引 | 有 ART 索引 | 提升倍数 | 说明 |
|---------|--------|----------|---------|------|
| 点查询 (WHERE order_id = 500000) | 2.73ms | 2.91ms | 0.94x | 几乎无差异，zonemap 已经够用 |
| 范围查询 (WHERE order_id BETWEEN 100000 AND 100100) | 1.08ms | 1.15ms | 0.94x | 无差异，zonemap 处理 |
| 高选择性查询 (WHERE user_id = 12345) | 1.59ms | 0.56ms | **2.84x** | ART 索引有效，返回约 10 行 |
| 低选择性查询 (WHERE status = 'delivered') | 400.39ms | 400.84ms | 1.00x | 索引无效，返回 25% 数据 |
测试结果很清晰：ART 索引只在高选择性点查询时有效，其他场景基本没用。
### 写入性能测试
| 场景 | 耗时 | 说明 |
|------|------|------|
| 无索引插入 50 万行 | 230.57ms | 基准 |
| 有索引插入 50 万行 | 587.64ms | **慢了 155%** |
这个数据很扎心。建了索引后，插入速度直接慢了一倍半。如果是批量导入场景，这个代价太大了。
### 索引创建耗时
| 索引 | 创建耗时 | 说明 |
|------|---------|------|
| order_id 索引（100 万整数） | 144.05ms | 高基数列 |
| user_id 索引（100 万整数） | 572.19ms | 约 10 万唯一值 |
| 数据库文件大小 | 28.76MB | 含 2 个 ART 索引 |
索引创建本身也需要时间和空间。
## 什么时候需要索引
基于测试结果和官方文档，我总结了一个决策流程：
```text
需要索引吗？
├── 需要约束检查（PRIMARY KEY / UNIQUE / FOREIGN KEY）？
│ └── YES → 自动创建 ART 索引（必须接受性能代价）
├── 查询返回 < 0.1% 数据（点查询）？
│ ├── YES → 创建 ART 索引
│ └── NO → 不需要索引
├── 数据是否有序？
│ ├── YES → zonemap 已经够用，不需要索引
│ └── NO → 先排序数据，而不是建索引
└── 写入密集型应用？
└── YES → 绝对不要建索引
```text
核心原则：DuckDB 的 ART 索引只适用于返回数据量小于表总行数 0.1% 的点查询场景。超过这个阈值，DuckDB 会智能地选择全表扫描，索引白建了。
## 比索引更好的方案：数据排序
DuckDB 官方博客明确指出：
> "Sorting data when loading can speed up selective read queries by an order of magnitude, thanks to DuckDB's automatic min-max indexes (also known as zone maps)."
翻译过来就是：导入数据时排序，可以让选择性查询快 10 倍，而且零维护成本。
### 实操代码
```python
import duckdb
con = duckdb.connect("optimized.db")
# 方法1：导入时排序（推荐）
con.execute("""
CREATE TABLE orders_optimized AS
SELECT * FROM read_csv('orders.csv')
ORDER BY order_date -- 按查询最频繁的列排序
""")
# 方法2：对已有表重排
con.execute("""
CREATE TABLE orders_sorted AS
SELECT * FROM orders
ORDER BY order_date
""")
con.execute("DROP TABLE orders")
con.execute("ALTER TABLE orders_sorted RENAME TO orders")
```text
### 排序的效果
在我的测试中，有序数据和无序数据的查询性能差异不大（211ms vs 216ms）。但这只是因为数据量还不够大。在 1000 万行以上或远程存储（S3/Parquet）场景下，排序带来的差异会放大到 10 倍。
几个关键技巧：
1. 不需要完全排序，近似排序也有帮助
2. VARCHAR 列只需排序前 8 个字节（zonemap 只存前 8 字节）
3. 可以用空间填充曲线做多列近似排序
## 踩坑记录
### 坑1：ART 索引创建时内存不足
现象：`OutOfMemoryException: failed to create ART index`
原因：ART 索引创建时必须完全放入内存。
解决：增大 `memory_limit` 配置，或分批创建索引。
### 坑2：索引对大范围查询无效
现象：建了索引但查询没变快。
原因：查询返回数据超过 0.1%，DuckDB 选择全表扫描。
解决：用 `EXPLAIN` 查看执行计划，确认是否走了 INDEX_SCAN。
```python
explain = con.execute("""
EXPLAIN SELECT * FROM orders WHERE user_id = 12345
""").fetchall()
for row in explain:
print(f"{row[0]}: {row[1]}")
# 如果看到 INDEX_SCAN，说明索引生效
# 如果看到 SEQ_SCAN + FILTER，说明没走索引
```sql
### 坑3：索引导致 INSERT 变慢 155%
现象：建索引后批量导入速度大幅下降。
原因：每行插入都要更新索引树。
解决：先导入数据，再创建索引。官方最佳实践明确建议这样做。
### 坑4：UPDATE 在索引列上的约束检查问题
现象：UPDATE 索引列时报约束冲突。
原因：UPDATE 被重写为 DELETE + INSERT，按 chunk（2048 行）处理。
解决：避免在索引列上做递增 UPDATE，或分批处理。
### 坑5：索引内存不受 buffer manager 管理
现象：有大量索引时，查询可用内存减少。
原因：索引缓冲区不会被 evict。
解决：用 `DETACH` + `ATTACH` 重新加载数据库（懒加载索引）。
## 创建和查看索引的代码示例
```python
import duckdb
con = duckdb.connect("test.db")
# 创建表
con.execute("""
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY, -- 自动创建 ART 索引
user_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR
)
""")
# 手动创建 ART 索引
con.execute("CREATE INDEX idx_user_id ON orders(user_id)")
# 查看索引信息
print(con.execute("""
SELECT index_name, table_name, is_unique, is_primary, sql
FROM duckdb_indexes()
""").fetchdf())
# 删除索引
con.execute("DROP INDEX idx_user_id")
```text
## 总结
DuckDB 的索引策略和传统 OLTP 数据库完全不同。核心要点：
1. **大多数情况不需要手动建索引** — zonemap 自动创建，已经足够快
2. **只在点查询（< 0.1% 数据）时才需要 ART 索引** — 比如通过 ID 查单条记录
3. **索引会让插入变慢 155%** — 必须权衡读写性能
4. **数据排序比索引更重要** — 预排序数据可以让 zonemap 效率提升 10 倍
5. **先导入数据，再创建索引** — 避免导入时的性能惩罚
记住这个原则：OLAP 索引 ≠ OLTP 索引。在 DuckDB 里，少建索引，多排序数据，才是正确的优化方向。
## 参考资源
- [DuckDB 官方文档 - Indexing Guide](https://duckdb.org/docs/current/guides/performance/indexing.html)
- [DuckDB 官方文档 - SQL Indexes](https://duckdb.org/docs/current/sql/indexes.html)
- [DuckDB 官方博客 - Persistent ART Storage](https://duckdb.org/2022/07/27/art-storage.html)
- [DuckDB 官方博客 - Sorting for Fast Selective Queries](https://duckdb.org/2025/05/14/sorting-for-fast-selective-queries.html)
- [MotherDuck 文档 - CREATE INDEX](https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/create-index)
|
