This is an automated email from the ASF dual-hosted git repository.
dataroaring pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new 5791beb9ca7 [docs] Improve POC guide with bucket sizing rules and
heading cleanup (#3445)
5791beb9ca7 is described below
commit 5791beb9ca79679f4f1d1522de86a63435bc5df0
Author: Yongqiang YANG <[email protected]>
AuthorDate: Tue Mar 10 07:09:03 2026 -0700
[docs] Improve POC guide with bucket sizing rules and heading cleanup
(#3445)
## Summary
- Replace bucket count guidance with clear **four-rule approach** (BE
multiple, minimize count, 20GB/10GB cap, 128 max per partition)
- Rename section headings for clarity: Key Columns → Sort Key, Typical
Use Cases → Example Templates, Common Performance Pitfalls → Performance
Pitfalls
- Merge sparse partition section into a single paragraph
- Remove unnecessary "Fixing Mistakes" section
- Fix broken CDC sync link and rule count reference
## Test plan
- [ ] Verify docs build successfully
- [ ] Check EN and ZH POC guide pages render correctly
- [ ] Confirm all internal links resolve
🤖 Generated with [Claude Code](https://claude.com/claude-code)
---------
Co-authored-by: Claude Opus 4.6 <[email protected]>
---
docs/gettingStarted/must-read-before-poc.md | 166 ++++-----------------
.../current/gettingStarted/must-read-before-poc.md | 166 ++++-----------------
2 files changed, 60 insertions(+), 272 deletions(-)
diff --git a/docs/gettingStarted/must-read-before-poc.md
b/docs/gettingStarted/must-read-before-poc.md
index 15bebbd1ad3..da53a1bf6ee 100644
--- a/docs/gettingStarted/must-read-before-poc.md
+++ b/docs/gettingStarted/must-read-before-poc.md
@@ -2,112 +2,52 @@
{
"title": "Must Read Before the POC",
"language": "en",
- "description": "Understand the four table design decisions in Apache Doris
— data model, key columns, partitioning, and bucketing — and why each one
matters for your POC.",
+ "description": "Common issues new users encounter with table design, data
loading, and query tuning in Apache Doris.",
"sidebar_label": "Must Read Before the POC"
}
---
# Must Read Before the POC
-Creating a table in Doris involves four decisions that affect load and query
performance. Some of them — like the data model — cannot be changed later.
Understanding **why** each decision exists helps you get it right the first
time.
+This document highlights common issues that new users may encounter, with the
goal of accelerating the POC process.
-:::tip The simplest valid CREATE TABLE
+## Table Design
-```sql
-CREATE TABLE my_table
-(
- id INT,
- name VARCHAR(100),
- created_at DATETIME,
- amount DECIMAL(10,2)
-);
-```
-
-This is the simplest syntax — Doris defaults to Duplicate Key, a single
partition, and Random bucketing. It works, but **it won't give you good
performance for most POC scenarios.** Read the four decisions below to
understand what to tune and why.
-
-:::
-
-## 1. Data Model
-
-**Why it matters:** The data model controls whether Doris keeps every row,
keeps only the latest row per key, or pre-aggregates rows at write time.
+Creating a table in Doris involves four decisions that affect load and query
performance.
-**How to choose:** Ask yourself one question — *do I need to update rows?*
+### Data Model
| If your data is... | Use | Why |
|---|---|---|
-| Append-only (logs, events, facts) | **[Duplicate
Key](../table-design/data-model/duplicate)** (default — just omit it) | Keeps
all rows. Best query performance. Safest default. |
-| Updated by primary key (CDC sync, user profiles) | **[Unique
Key](../table-design/data-model/unique)** | New rows replace old rows with the
same key. |
-| Pre-aggregated metrics (PV, UV, revenue sums) | **[Aggregate
Key](../table-design/data-model/aggregate)** | Rows are merged with SUM/MAX/MIN
during ingestion. |
-
-For a POC, **Duplicate Key works for most scenarios**. Switch only if you have
a clear need for upsert or pre-aggregation. For a detailed comparison, see
[Data Model Overview](../table-design/data-model/overview).
-
-## 2. Key Columns
-
-**Why it matters:** Key columns determine the **physical sort order** on disk.
Doris builds a [prefix index](../table-design/index/prefix-index) on the first
36 bytes of key columns, so queries that filter on these columns run
significantly faster. However, when a `VARCHAR` column is encountered, the
prefix index stops immediately — no subsequent columns are included. So place
fixed-size columns (INT, BIGINT, DATE) before VARCHAR to maximize index
coverage.
-
-**How to choose:** Put the column you filter on most frequently first, with
fixed-size types before VARCHAR types. You can add [inverted
indexes](../table-design/index/inverted-index) later for any column that needs
fast filtering.
-
-## 3. Partitioning
-
-**Why it matters:** Partitioning splits data into independent units. When a
query includes a partition column in its WHERE clause, Doris only scans the
relevant partitions — this is called **partition pruning** and it can skip the
vast majority of data.
+| Append-only (logs, events, facts) | **Duplicate Key** (default) | Keeps all
rows. Best query performance. |
+| Updated by primary key (CDC, upsert) | **Unique Key** | New rows replace old
rows with the same key. |
+| Pre-aggregated metrics (PV, UV, sums) | **Aggregate Key** | Rows are merged
with SUM/MAX/MIN at write time. |
-**How to choose:**
-
-- **Have a time column?** → Use `AUTO PARTITION BY RANGE(date_trunc(time_col,
'day'))`. Partitions are created automatically during import, no manual
management needed.
-
-For full syntax and advanced options, see [Auto
Partition](../table-design/data-partitioning/auto-partitioning).
-
-## 4. Bucketing
-
-**Why it matters:** Each bucket is stored as one or more **tablets** (one per
replica). A tablet lives on a single BE node, so scanning a tablet can only use
that one BE. For a single query, parallelism is determined by `partitions ×
buckets` — replicas are not used simultaneously. For concurrent queries,
different replicas can serve different queries, so the total tablet count
`partitions × buckets × replicas` determines cluster-wide throughput.
-
-**Partitions first, then buckets.** Both partitioning and bucketing increase
tablet count, but partitions also enable pruning and are easier to manage
(add/drop). When you need more parallelism, prefer adding partitions before
increasing bucket count.
-
-**How to choose bucket count:** Follow these four rules:
-
-1. **Make it a multiple of the number of BEs** — ensures even data
distribution across nodes.
-2. **Keep it as low as possible** — fewer buckets mean larger tablets, which
improves scan efficiency and reduces metadata overhead. In production, large
tables have many partitions and queries span multiple partitions, so
parallelism comes primarily from partitions — performance is not sensitive to
bucket count.
-3. **Compressed data per bucket should not exceed 20 GB** (under **10 GB** for
Unique Key tables) — check with `SHOW TABLETS FROM your_table`.
-4. **Bucket count per partition should not exceed 128** — if you need more,
consider partitioning the table first.
-
-**Default is Random bucketing** — you can omit the `DISTRIBUTED BY` clause
entirely. For Duplicate Key tables, Random bucketing is recommended because it
enables `load_to_single_tablet` for lower memory usage and higher load
throughput.
-
-**When to specify Hash bucketing:** If you frequently filter or join on a
specific column, `DISTRIBUTED BY HASH(that_column)` enables **bucket pruning**
— Doris skips irrelevant buckets, which is faster than scanning all of them.
-
-```sql
--- Default: random bucketing (omit the clause, or write explicitly)
-DISTRIBUTED BY RANDOM BUCKETS 10
-
--- Better for queries that filter on a specific column
-DISTRIBUTED BY HASH(user_id) BUCKETS 10
-```
+**Duplicate Key works for most scenarios.** See [Data Model
Overview](../table-design/data-model/overview).
-For details on choosing between Hash and Random bucketing, see [Data
Bucketing](../table-design/data-partitioning/data-bucketing).
+### Sort Key
-## Important Notes
+Put the column you filter on most frequently first, with fixed-size types
(INT, BIGINT, DATE) before VARCHAR. Doris builds a [prefix
index](../table-design/index/prefix-index) on the first 36 bytes of key columns
but stops at the first VARCHAR. Add [inverted
indexes](../table-design/index/inverted-index) for other columns that need fast
filtering.
-Things that surprise new users. Read these before you create your first table.
+### Partitioning
-:::caution
+If you have a time column, use `AUTO PARTITION BY RANGE(date_trunc(time_col,
'day'))` to enable [partition
pruning](../table-design/data-partitioning/auto-partitioning). Doris skips
irrelevant partitions automatically.
-**Data model is permanent.** You cannot change from Duplicate to Unique or
Aggregate after table creation. If you choose wrong, the only fix is to create
a new table and re-import data.
+### Bucketing
-:::
+Default is **Random bucketing** (recommended for Duplicate Key tables). Use
`DISTRIBUTED BY HASH(col)` if you frequently filter or join on a specific
column. See [Data Bucketing](../table-design/data-partitioning/data-bucketing).
-**STRING type cannot be a key or partition column.** Use `VARCHAR` instead.
`STRING` is only for value columns storing large text content. For key columns,
`VARCHAR(65533)` has no performance penalty compared to `VARCHAR(255)` — they
perform the same when storing identical data, so use a generous length. See
[Data Types](../table-design/data-type) for the full type reference.
+**How to choose bucket count:**
-**Aggregate Key tables don't support `count(*)` well.** Because values are
pre-aggregated, `count(*)` cannot simply count rows. The workaround is to add a
column like `row_count BIGINT SUM DEFAULT '1'` and query `SELECT
SUM(row_count)` instead.
+1. **Multiple of BE count** to ensure even data distribution. When BEs are
added later, queries typically scan multiple partitions, so performance holds
up.
+2. **As low as possible** to avoid small files.
+3. **Compressed data per bucket ≤ 20 GB** (≤ 10 GB for Unique Key). Check with
`SHOW TABLETS FROM your_table`.
+4. **No more than 128 per partition.** Consider partitioning first if you need
more.
-**Bucket count on existing partitions cannot be changed.** You can only adjust
bucket count for **new** partitions. Follow the three rules in the Bucketing
section above to choose the right count upfront.
-
-## Typical Use Cases
-
-Ready-to-use templates for the most common POC scenarios.
+## Example Templates
### Log / Event Analytics
-Append-only data, queried by time range and keyword.
-
```sql
CREATE TABLE app_logs
(
@@ -123,14 +63,8 @@ AUTO PARTITION BY RANGE(date_trunc(`log_time`, 'day'))
DISTRIBUTED BY RANDOM BUCKETS 10;
```
-- Default **Duplicate Key** — logs are never updated, Random bucketing for
best load throughput
-- **AUTO PARTITION by day** — time-range queries skip irrelevant days
-- **Inverted index on message** — enables full-text search
([details](../table-design/index/inverted-index))
-
### Real-Time Dashboard with Upsert (CDC)
-Sync from MySQL/PostgreSQL, keep latest state per primary key.
-
```sql
CREATE TABLE user_profiles
(
@@ -144,13 +78,8 @@ UNIQUE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 10;
```
-- **Unique Key** — new rows replace old by `user_id`, enabling [CDC
sync](../data-operate/import/data-source/migrate-data-from-other-oltp)
-- **No partition** — dimension table, small and not time-series
-
### Metrics Aggregation
-Pre-compute SUM/MAX at write time for fast dashboard queries.
-
```sql
CREATE TABLE site_metrics
(
@@ -165,55 +94,20 @@ AUTO PARTITION BY RANGE(date_trunc(`dt`, 'day'))
DISTRIBUTED BY HASH(site_id) BUCKETS 10;
```
-- **Aggregate Key** — PV is summed, UV takes max, automatically during
ingestion ([details](../table-design/data-model/aggregate))
-- **AUTO PARTITION by day** — daily rollup with automatic partition creation
-
-### Lakehouse Query (No Table Needed)
-
-Query external data (Hive, Iceberg, S3) without importing. No table design
decisions required.
-
-```sql
-CREATE CATALOG lakehouse PROPERTIES (
- 'type' = 'iceberg',
- 'iceberg.catalog.type' = 'rest',
- 'uri' = 'http://iceberg-rest:8181'
-);
-
-SELECT * FROM lakehouse.db.events WHERE dt = '2025-01-01';
-```
-
-This is the fastest way to validate Doris query performance on your existing
data. Create internal tables later if you need better performance. See
[Lakehouse Overview](../lakehouse/lakehouse-overview).
-
-## Common Performance Pitfalls
+## Performance Pitfalls
### Load
-- **Using `INSERT INTO VALUES` for large data.** This is the slowest import
method. For bulk loading, use [Stream
Load](../data-operate/import/import-way/stream-load-manual) (HTTP, synchronous,
best for files < 10 GB) or [Broker
Load](../data-operate/import/import-way/broker-load-manual) (async, for large
files on S3/HDFS). Reserve `INSERT INTO VALUES` for small tests only. See
[Loading Overview](../data-operate/import/load-manual) for choosing the right
method.
-
-- **Many small imports instead of batching.** Each import creates a new data
version that requires compaction later. High-frequency small imports cause
version accumulation, increasing memory and CPU pressure. Batch writes on the
client side first — this is the most effective approach. If client-side
batching is not feasible, [Group
Commit](../data-operate/import/group-commit-manual) can help by automatically
batching small writes on the server side.
-
-- **Too many small tablets.** Total tablets = `partitions × buckets ×
replicas`. Excessive small tablets cause memory pressure during import, slow
metadata operations, and generate too many small files. Avoid over-partitioning
or setting bucket count too high. Reducing tablets after the fact is very
costly — it's much easier to start small and add partitions or buckets later
when needed.
-
-- **Running a single long-running load statement.** If a large import fails
halfway through, you have to restart from scratch — failure recovery is very
costly. Break large imports into smaller batches, or use [INSERT INTO SELECT
with S3 TVF](../data-operate/import/import-way/insert-into-manual) to import
data incrementally with automatic resume.
-
-- **Not enabling `load_to_single_tablet` with Random bucketing.** For
Duplicate Key tables with Random bucketing, set `"load_to_single_tablet" =
"true"` during import. Each import batch writes to a single tablet, improving
throughput and reducing write amplification.
+- **Don't use `INSERT INTO VALUES` for bulk data.** Use [Stream
Load](../data-operate/import/import-way/stream-load-manual) or [Broker
Load](../data-operate/import/import-way/broker-load-manual) instead. See
[Loading Overview](../data-operate/import/load-manual).
+- **Batch writes on the client side.** High-frequency small imports cause
version accumulation. If not feasible, use [Group
Commit](../data-operate/import/group-commit-manual).
+- **Break large imports into smaller batches.** A failed long-running import
must restart from scratch. Use [INSERT INTO SELECT with S3
TVF](../data-operate/import/streaming-job/streaming-job-tvf) for incremental
import.
+- **Enable `load_to_single_tablet`** for Duplicate Key tables with Random
bucketing to reduce write amplification.
-For more loading optimization tips, see [Load Best
Practices](../data-operate/import/load-best-practices).
+See [Load Best Practices](../data-operate/import/load-best-practices).
### Query
-- **Data skew.** If the bucket column has low cardinality or uneven
distribution, some tablets hold far more data than others. The slowest tablet
determines overall query time. Check with `SHOW TABLETS FROM your_table` — if
tablet sizes vary significantly, choose a higher-cardinality bucket column or
switch to Random bucketing for even distribution.
-
-- **Wrong key column order.** If your most common filter column is not in the
[prefix index](../table-design/index/prefix-index) (first 36 bytes of key
columns), queries fall back to scanning all data blocks. Reorder key columns to
put the most frequently filtered column first, or add an [inverted
index](../table-design/index/inverted-index) on that column.
-
-- **Missing partition pruning.** If your query doesn't filter on the partition
column, Doris scans all partitions. Always include the partition column
(usually a time column) in your WHERE clause when possible.
-
-- **`SELECT *` on wide tables.** Doris is a columnar store — it only reads the
columns you request. `SELECT *` on a table with many columns forces reading all
of them, wasting I/O. Select only the columns you need.
-
-To diagnose slow queries, use [Query
Profile](../query-acceleration/query-profile) to see where time is spent.
-
-## What If I Choose Wrong?
-
-During a POC, most decisions can be fixed by creating a new table and running
`INSERT INTO new_table SELECT * FROM old_table` — this takes minutes, not days.
The exception is that bucket count on existing partitions cannot be changed in
place. Start with reasonable choices, measure, then optimize.
+- **Data skew.** Check tablet sizes with `SHOW TABLETS`. Switch to Random
bucketing or a higher-cardinality bucket column if sizes vary significantly.
+- **Wrong sort key order.** See [Sort Key](#sort-key).
-For production-level table design guidance, see [Best
Practices](../table-design/best-practice).
+See [Query Profile](../query-acceleration/query-profile) to diagnose slow
queries.
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/gettingStarted/must-read-before-poc.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/gettingStarted/must-read-before-poc.md
index 1f322470e3f..df9f2f6bb69 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/gettingStarted/must-read-before-poc.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/gettingStarted/must-read-before-poc.md
@@ -2,112 +2,52 @@
{
"title": "POC 前必读",
"language": "zh-CN",
- "description": "了解 Apache Doris 建表时的四个关键决策——数据模型、排序键、分区和分桶——以及它们为什么对 POC
性能至关重要。",
+ "description": "新用户在 Apache Doris 建表设计、数据导入和查询调优中常见的问题。",
"sidebar_label": "POC 前必读"
}
---
# POC 前必读
-在 Doris 中建表涉及四个影响导入和查询性能的决策。其中一些（如数据模型）建表后无法更改。理解每个决策**为什么**存在，可以帮助你一次做对。
+本文档汇总了新用户常见的问题，旨在加速 POC 进程。
-:::tip 最简建表语句
+## 建表设计
-```sql
-CREATE TABLE my_table
-(
- id INT,
- name VARCHAR(100),
- created_at DATETIME,
- amount DECIMAL(10,2)
-);
-```
-
-这是最简语法——Doris 默认使用 Duplicate Key 模型、单分区和 Random 分桶。它可以运行，但**在大多数 POC
场景中性能不佳。**请阅读以下四个决策，了解需要调整什么以及为什么。
-
-:::
-
-## 1. 数据模型
-
-**为什么重要：**数据模型决定 Doris 是保留每一行、按主键只保留最新行，还是在写入时预聚合数据。
+在 Doris 中建表涉及四个影响导入和查询性能的决策。
-**如何选择：**问自己一个问题——*我需要更新数据吗？*
+### 数据模型
| 数据特征 | 使用 | 原因 |
|---|---|---|
-| 仅追加（日志、事件、事实表） | **[Duplicate
Key](../table-design/data-model/duplicate)**（默认——直接省略） | 保留所有行。查询性能最好。最安全的默认选择。
|
-| 按主键更新（CDC 同步、用户画像） | **[Unique Key](../table-design/data-model/unique)** |
新行按相同 Key 替换旧行。 |
-| 预聚合指标（PV、UV、收入汇总） | **[Aggregate
Key](../table-design/data-model/aggregate)** | 写入时按 SUM/MAX/MIN 合并行。 |
-
-POC 阶段，**Duplicate Key
适用于大多数场景**。只有在明确需要更新或预聚合时才切换。详细对比见[数据模型概述](../table-design/data-model/overview)。
-
-## 2. 排序键
-
-**为什么重要：**排序键决定数据在磁盘上的**物理排列顺序**。Doris 会在排序键的前 36
字节上自动构建[前缀索引](../table-design/index/prefix-index)，使基于这些列的过滤查询显著加速。但当遇到
`VARCHAR` 列时，前缀索引会立即截断——后续列不会被包含。因此，请将定长列（INT、BIGINT、DATE）放在 VARCHAR
前面，以最大化索引覆盖范围。
-
-**如何选择：**将最常用于过滤的列放在最前面，定长类型在 VARCHAR
类型之前。之后可以为需要快速过滤的列添加[倒排索引](../table-design/index/inverted-index)。
-
-## 3. 分区
-
-**为什么重要：**分区将数据拆分为独立的管理单元。当查询的 WHERE 条件包含分区列时，Doris
只扫描相关分区——即**分区裁剪**，可以跳过绝大部分数据。
+| 仅追加（日志、事件、事实表） | **Duplicate Key**（默认） | 保留所有行。查询性能最好。 |
+| 按主键更新（CDC、Upsert） | **Unique Key** | 新行按相同 Key 替换旧行。 |
+| 预聚合指标（PV、UV、汇总） | **Aggregate Key** | 写入时按 SUM/MAX/MIN 合并行。 |
-**如何选择：**
-
-- **有时间列？** → 使用 `AUTO PARTITION BY RANGE(date_trunc(time_col,
'day'))`。分区在导入时自动创建，无需手动管理。
-
-完整语法和高级选项见 [Auto
Partition](../table-design/data-partitioning/auto-partitioning)。
-
-## 4. 分桶
-
-**为什么重要：**每个分桶存储为一个或多个 **tablet**（每个副本一个）。一个 tablet 位于单个 BE 节点上，因此扫描一个 tablet
只能使用那一个 BE。对于单个查询，并行度由 `分区数 × 分桶数` 决定——副本不会同时参与。对于并发查询，不同副本可以服务不同查询，因此总 tablet
数 `分区数 × 分桶数 × 副本数` 决定集群整体吞吐量。
-
-**优先增加分区，再增加分桶。**分区和分桶都会增加 tablet
数量，但分区还能启用裁剪且更易管理（添加/删除）。需要更多并行度时，优先增加分区，其次才增加分桶数。
-
-**如何选择分桶数：**遵循以下四条规则：
-
-1. **设为 BE 数量的整数倍**——确保数据均匀分布在各节点上。
-2. **尽可能少**——更少的分桶意味着更大的
tablet，可以提升扫描效率并减少元数据开销。在生产环境中，大表通常有很多分区，查询往往涉及多个分区，因此整体并行度主要来自分区——性能对分桶数并不敏感。
-3. **每个分桶的压缩后数据大小不应超过 20 GB**（Unique Key 表不超过 **10 GB**）——可通过 `SHOW TABLETS
FROM your_table` 查看。
-4. **每个分区的分桶数不应超过 128**——如果需要更多，应优先考虑对表进行分区。
-
-**默认是 Random 分桶** ——可以完全省略 `DISTRIBUTED BY` 子句。对于 Duplicate Key 表，推荐使用 Random
分桶，因为它支持 `load_to_single_tablet`，降低导入内存使用并提高导入吞吐。
-
-**何时使用 Hash 分桶：**如果频繁按某列过滤或 JOIN，`DISTRIBUTED BY HASH(该列)` 可以启用**分桶裁剪**——Doris
跳过无关的分桶，比扫描全部分桶更快。
-
-```sql
--- 默认：Random 分桶（省略该子句，或显式写出）
-DISTRIBUTED BY RANDOM BUCKETS 10
-
--- 适合频繁按特定列过滤的查询
-DISTRIBUTED BY HASH(user_id) BUCKETS 10
-```
+**Duplicate Key 适用于大多数场景。**详见[数据模型概述](../table-design/data-model/overview)。
-Hash 与 Random 分桶的详细对比见[数据分桶](../table-design/data-partitioning/data-bucketing)。
+### Sort Key（排序键）
-## 重要说明
+将最常用于过滤的列放在最前面，定长类型（INT、BIGINT、DATE）放在 VARCHAR 之前。Doris 在排序键的前 36
字节上构建[前缀索引](../table-design/index/prefix-index)，但遇到 VARCHAR
会立即截断。其他需要快速过滤的列可添加[倒排索引](../table-design/index/inverted-index)。
-新用户常踩的坑。建表前请务必阅读。
+### 分区
-:::caution
+如果有时间列，使用 `AUTO PARTITION BY RANGE(date_trunc(time_col, 'day'))`
启用[分区裁剪](../table-design/data-partitioning/auto-partitioning)。Doris 会自动跳过无关分区。
-**数据模型不可更改。**建表后无法从 Duplicate 改为 Unique 或 Aggregate。选错的唯一补救是新建表并重新导入数据。
+### 分桶
-:::
+默认是 **Random 分桶**（推荐用于 Duplicate Key 表）。如果频繁按某列过滤或 JOIN，使用 `DISTRIBUTED BY
HASH(该列)`。详见[数据分桶](../table-design/data-partitioning/data-bucketing)。
-**STRING 类型不能作为 Key 列或分区列。**请使用 `VARCHAR` 代替。`STRING` 仅适用于存储大文本的 Value 列。对于
Key 列，`VARCHAR(65533)` 与 `VARCHAR(255)`
在存储相同数据时性能完全一致，所以放心使用较大的长度。完整类型参考见[数据类型](../table-design/data-type)。
+**如何选择分桶数：**
-**Aggregate Key 表不能很好地支持 `count(*)`。**因为值已预聚合，`count(*)` 无法简单计数。解决方法是添加一个列如
`row_count BIGINT SUM DEFAULT '1'`，查询时使用 `SELECT SUM(row_count)` 代替。
+1. **设为 BE 数量的整数倍**，确保数据均匀分布。后续扩容 BE 时，查询通常涉及多个分区，性能不会受影响。
+2. **尽可能少**，避免小文件。
+3. **每个分桶的压缩后数据 ≤ 20 GB**（Unique Key 表 ≤ 10 GB）。可通过 `SHOW TABLETS FROM
your_table` 查看。
+4. **每个分区不超过 128 个分桶。**需要更多时优先考虑分区。
-**已有分区的分桶数不可更改。**只能调整**新分区**的分桶数。请参考上方分桶章节的三条规则来选择合适的分桶数。
-
-## 典型使用场景
-
-常见 POC 场景的建表模板，可直接使用。
+## 建表模板
### 日志 / 事件分析
-仅追加数据，按时间范围和关键词查询。
-
```sql
CREATE TABLE app_logs
(
@@ -123,14 +63,8 @@ AUTO PARTITION BY RANGE(date_trunc(`log_time`, 'day'))
DISTRIBUTED BY RANDOM BUCKETS 10;
```
-- 默认 **Duplicate Key** ——日志不更新，Random 分桶获得最佳导入吞吐
-- **按天自动分区** ——时间范围查询跳过无关天数
-- **message 倒排索引** ——支持全文检索（[详情](../table-design/index/inverted-index)）
-
### 实时看板与 Upsert（CDC）
-从 MySQL/PostgreSQL 同步数据，按主键保留最新状态。
-
```sql
CREATE TABLE user_profiles
(
@@ -144,13 +78,8 @@ UNIQUE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 10;
```
-- **Unique Key** ——按 `user_id` 新行替换旧行，支持 [CDC
同步](../data-operate/import/data-source/migrate-data-from-other-oltp)
-- **无分区** ——维度表，数据量小且非时序
-
### 指标聚合
-写入时预计算 SUM/MAX，加速看板查询。
-
```sql
CREATE TABLE site_metrics
(
@@ -165,55 +94,20 @@ AUTO PARTITION BY RANGE(date_trunc(`dt`, 'day'))
DISTRIBUTED BY HASH(site_id) BUCKETS 10;
```
-- **Aggregate Key** ——PV 自动求和，UV
取最大值（[详情](../table-design/data-model/aggregate)）
-- **按天自动分区** ——按日汇总，自动创建分区
-
-### 湖仓查询（无需建表）
-
-直接查询外部数据（Hive、Iceberg、S3），无需导入，无需建表设计。
-
-```sql
-CREATE CATALOG lakehouse PROPERTIES (
- 'type' = 'iceberg',
- 'iceberg.catalog.type' = 'rest',
- 'uri' = 'http://iceberg-rest:8181'
-);
-
-SELECT * FROM lakehouse.db.events WHERE dt = '2025-01-01';
-```
-
-这是验证 Doris
查询性能最快的方式——直接在现有数据上查询。如需更好性能，之后再创建内部表。详见[湖仓一体概述](../lakehouse/lakehouse-overview)。
-
-## 常见性能陷阱
+## 性能陷阱
### 导入
-- **大数据量使用 `INSERT INTO VALUES`。**这是最慢的导入方式。批量导入请使用 [Stream
Load](../data-operate/import/import-way/stream-load-manual)（HTTP，同步，适合 < 10 GB
文件）或 [Broker Load](../data-operate/import/import-way/broker-load-manual)（异步，适合
S3/HDFS 上的大文件）。`INSERT INTO VALUES`
仅用于小规模测试。选择导入方式详见[导入概述](../data-operate/import/load-manual)。
-
-- **大量小批次导入而不合并。**每次导入都会创建新的数据版本，需要后续 compaction。高频小批次导入导致版本堆积，增加内存和 CPU
压力。优先在客户端进行批量合并——这是最有效的方式。如果客户端合并不可行，可使用 [Group
Commit](../data-operate/import/group-commit-manual) 在服务端自动合并小批次写入。
-
-- **过多小 tablet。**总 tablet 数 = `分区数 × 分桶数 × 副本数`。过多小 tablet
导致导入内存压力大、元数据操作慢、产生过多小文件。避免过度分区或分桶数设置过高。事后减少 tablet 代价很大——不如一开始设少，后续按需增加。
-
-- **单次长时间运行的导入语句。**如果一个大型导入中途失败，必须从头重试——恢复代价非常高。将大型导入拆分为小批次，或使用 [INSERT INTO
SELECT 配合 S3 TVF](../data-operate/import/import-way/insert-into-manual)
实现增量导入与自动续传。
-
-- **Random 分桶未启用 `load_to_single_tablet`。**对于使用 Random 分桶的 Duplicate Key
表，在导入时设置 `"load_to_single_tablet" = "true"`。每批数据写入单个 tablet，提高吞吐并减少写放大。
+- **批量数据不要用 `INSERT INTO VALUES`。**请使用 [Stream
Load](../data-operate/import/import-way/stream-load-manual) 或 [Broker
Load](../data-operate/import/import-way/broker-load-manual)。详见[导入概述](../data-operate/import/load-manual)。
+- **优先在客户端合并写入。**高频小批次导入导致版本堆积。如不可行，使用 [Group
Commit](../data-operate/import/group-commit-manual)。
+- **将大型导入拆分为小批次。**长时间运行的导入失败后必须从头重试。使用 [INSERT INTO SELECT 配合 S3
TVF](../data-operate/import/streaming-job/streaming-job-tvf) 实现增量导入。
+- **Random 分桶的 Duplicate Key 表启用 `load_to_single_tablet`**，减少写放大。
-更多导入优化建议见[导入最佳实践](../data-operate/import/load-best-practices)。
+详见[导入最佳实践](../data-operate/import/load-best-practices)。
### 查询
-- **数据倾斜。**如果分桶列基数低或分布不均，部分 tablet 的数据量远大于其他。最慢的 tablet 决定整体查询耗时。通过 `SHOW
TABLETS FROM your_table` 检查——如果 tablet 大小差异明显，选择基数更高的分桶列或切换为 Random 分桶以均匀分布。
-
-- **排序键顺序不当。**如果最常用的过滤列不在[前缀索引](../table-design/index/prefix-index)中（排序键前 36
字节），查询会退化为扫描所有数据块。将最常过滤的列放在排序键最前面，或为该列添加[倒排索引](../table-design/index/inverted-index)。
-
-- **缺少分区裁剪。**如果查询未在分区列上过滤，Doris 会扫描所有分区。尽可能在 WHERE 条件中包含分区列（通常是时间列）。
-
-- **宽表使用 `SELECT *`。**Doris 是列式存储——只读取请求的列。对多列宽表使用 `SELECT *` 会读取所有列，浪费
I/O。请只查询需要的列。
-
-诊断慢查询请使用 [Query Profile](../query-acceleration/query-profile) 查看耗时分布。
-
-## 选错了怎么办？
-
-POC 阶段，大多数决策都可以通过新建表并执行 `INSERT INTO new_table SELECT * FROM old_table`
来修复——耗时几分钟而非几天。唯一的例外是已有分区的分桶数无法原地修改。从合理的选择开始，观察实际表现，再进行优化。
+- **数据倾斜。**通过 `SHOW TABLETS` 检查 tablet 大小。差异明显时切换为 Random 分桶或选择基数更高的分桶列。
+- **排序键顺序不当。**参见 [Sort Key（排序键）](#sort-key排序键)。
-生产级建表指导见[最佳实践](../table-design/best-practice)。
+诊断慢查询请使用 [Query Profile](../query-acceleration/query-profile)。
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
