> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# 同步数据读取

> 新设置 `allow_asynchronous_read_from_io_pool_for_merge_tree` 允许读取线程（流）的数量超过查询执行流水线其余部分的线程数。

新设置 `allow_asynchronous_read_from_io_pool_for_merge_tree` 允许读取线程 (流) 的数量超过查询执行流水线其余部分的线程数。

通常，[max\_threads](/zh/reference/settings/session-settings#settings-max_threads) 设置会[控制](https://clickhouse.com/company/events/query-performance-introspection)并行读取线程和并行查询处理线程的数量：

<Image img={sync_read} size="md" alt="同步数据读取示意图" />

数据会按顺序从磁盘中逐列读取。

<div id="asynchronous-data-reading">
  ### 异步数据读取
</div>

新的设置 [allow\_asynchronous\_read\_from\_io\_pool\_for\_merge\_tree](https://github.com/ClickHouse/ClickHouse/pull/43260) 允许读取线程 (流) 的数量高于查询执行流水线其余部分中的线程数，从而**加快低 CPU 的 ClickHouse Cloud 服务上的冷查询**，并**提升受 I/O 限制的查询性能**。
启用该设置后，读取线程的数量由 [max\_streams\_for\_merge\_tree\_reading](https://github.com/ClickHouse/ClickHouse/pull/43260) 设置控制：

<Image img={async_read} size="md" alt="异步数据读取示意图" />

数据会以异步方式从不同列并行读取。

请注意，还有一个 [max\_streams\_to\_max\_threads\_ratio](https://github.com/ClickHouse/ClickHouse/pull/43260) 设置，用于配置读取线程 (流) 数量与查询执行流水线其余部分线程数之间的比率。不过，在基准测试中，它带来的提升不如 `max_streams_for_merge_tree_reading` 设置明显

<div id="what-about-optimize_read_in_order">
  ### optimize\_read\_in\_order 呢？
</div>

借助 [optimize\_read\_in\_order 优化](/zh/reference/statements/select/order-by#optimization-of-data-reading)，如果查询的排序顺序与磁盘上数据的物理顺序一致，ClickHouse 就可以[跳过](https://clickhouse.com/blog/clickhouse-faster-queries-with-projections-and-primary-indexes)在内存中对数据重新排序，**但这要求按顺序读取数据 (而不是异步读取) **：

<Image img={optimize_read} size="md" alt="按顺序读取优化示意图" />

<div id="optimize_read_in_order-has-precedence-over-asynchronous-reading">
  ### `optimize_read_in_order` 优化的优先级高于异步读取
</div>

当 ClickHouse 判断可应用 `optimize_read_in_order 优化` 时，`allow_asynchronous_read_from_io_pool_for_merge_tree` 设置会被忽略 / 禁用。

<div id="example-demonstrating-all-of-the-above">
  ### 演示上述所有内容的示例
</div>

* 创建并加载[英国 Property Price Paid 表](/zh/get-started/sample-datasets/uk-price-paid)

* 检查 max\_threads 的设置值 (默认值为 ClickHouse 在执行查询的节点上检测到的 CPU 核心数

```
SELECT getSetting('max_threads');

┌─getSetting('max_threads')─┐
│                        10 │
└───────────────────────────┘
```

* 查看在读取和处理数据时使用默认线程数的查询管道

```
EXPLAIN PIPELINE
SELECT *
FROM uk_price_paid;

┌─explain──────────────────────┐
│ (Expression)                 │
│ ExpressionTransform × 10     │
│   (ReadFromMergeTree)        │
│   MergeTreeThread × 10 0 → 1 │
└──────────────────────────────┘
```

* 检查查询管道是否使用 60 个异步读取线程，以及查询执行流水线其余部分是否使用默认线程数

```
EXPLAIN PIPELINE
SELECT *
FROM uk_price_paid
SETTINGS
    allow_asynchronous_read_from_io_pool_for_merge_tree = 1,
    max_streams_for_merge_tree_reading = 60;

┌─explain────────────────────────┐
│ (Expression)                   │
│ ExpressionTransform × 10       │
│   (ReadFromMergeTree)          │
│   Resize 60 → 10               │
│     MergeTreeThread × 60 0 → 1 │
└────────────────────────────────┘
```

* 检查查询管道，读取和处理数据均使用 20 个线程

```
EXPLAIN PIPELINE
SELECT *
FROM uk_price_paid
SETTINGS
    max_threads = 20;

┌─explain──────────────────────┐
│ (Expression)                 │
│ ExpressionTransform × 20     │
│   (ReadFromMergeTree)        │
│   MergeTreeThread × 20 0 → 1 │
└──────────────────────────────┘
```

* 检查查询管道：其中 60 个线程用于异步读取，20 个线程用于其余查询执行流水线

```
EXPLAIN PIPELINE
SELECT *
FROM uk_price_paid
SETTINGS
    max_threads = 20,
    allow_asynchronous_read_from_io_pool_for_merge_tree = 1,
    max_streams_for_merge_tree_reading = 60;

┌─explain────────────────────────┐
│ (Expression)                   │
│ ExpressionTransform × 20       │
│   (ReadFromMergeTree)          │
│   Resize 60 → 20               │
│     MergeTreeThread × 60 0 → 1 │
└────────────────────────────────┘
```

* 检查查询管道：在可应用 `optimize_read_in_order 优化` 时，使用 60 个异步读取线程，其余查询执行流水线使用 20 个线程

```
EXPLAIN PIPELINE
SELECT *
FROM uk_price_paid
ORDER BY postcode1, postcode2
SETTINGS
    max_threads = 20,
    allow_asynchronous_read_from_io_pool_for_merge_tree= 1,
    max_streams_for_merge_tree_reading= 60;

┌─explain───────────────────────────┐
│ (Expression)                      │
│ ExpressionTransform               │
│   (Sorting)                       │
│   MergingSortedTransform 20 → 1   │
│     (Expression)                  │
│     ExpressionTransform × 20      │
│       (ReadFromMergeTree)         │
│       MergeTreeInOrder × 20 0 → 1 │
└───────────────────────────────────┘

-- 注意：这等同于禁用 allow_asynchronous_read_from_io_pool_for_merge_tree

EXPLAIN PIPELINE
SELECT *
FROM uk_price_paid
ORDER BY postcode1, postcode2
SETTINGS
    max_threads = 20,
    allow_asynchronous_read_from_io_pool_for_merge_tree = 0,
    max_streams_for_merge_tree_reading = 0;

┌─explain───────────────────────────┐
│ (Expression)                      │
│ ExpressionTransform               │
│   (Sorting)                       │
│   MergingSortedTransform 20 → 1   │
│     (Expression)                  │
│     ExpressionTransform × 20      │
│       (ReadFromMergeTree)         │
│       MergeTreeInOrder × 20 0 → 1 │
└───────────────────────────────────┘

-- 注意：可以通过禁用 optimize_read_in_order 来强制启用 allow_asynchronous_read_from_io_pool_for_merge_tree

EXPLAIN PIPELINE
SELECT *
FROM uk_price_paid
ORDER BY
    postcode1 ASC,
    postcode2 ASC
SETTINGS
    max_threads = 20,
    allow_asynchronous_read_from_io_pool_for_merge_tree = 1,
    max_streams_for_merge_tree_reading = 60,
    optimize_read_in_order = 0;

┌─explain──────────────────────────────┐
│ (Expression)                         │
│ ExpressionTransform                  │
│   (Sorting)                          │
│   MergingSortedTransform 20 → 1      │
│     MergeSortingTransform × 20       │
│       (Expression)                   │
│       ExpressionTransform × 20       │
│         (ReadFromMergeTree)          │
│         Resize 60 → 20               │
│           MergeTreeThread × 60 0 → 1 │
└──────────────────────────────────────┘

```