> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# 查询性能 - 时间序列

> 提升时间序列查询性能

完成存储优化后，下一步就是提升查询性能。
本节将介绍两项关键技术：优化 `ORDER BY` 键，以及使用 materialized view。
我们将看到，这些方法如何将查询时间从秒级缩短到毫秒级。

<div id="time-series-optimize-order-by">
  ## 优化 `ORDER BY` 键
</div>

在尝试其他优化之前，应先优化排序键，以确保 ClickHouse 能获得尽可能快的查询结果。
选择合适的键很大程度上取决于你要运行的查询。假设我们的大多数查询都会根据 `project` 和 `subproject` 列进行过滤。
在这种情况下，最好将它们加入排序键中——还要加上 `time` 列，因为我们也会按时间查询。

下面我们创建该表的另一个版本，它与 `wikistat` 具有相同的列类型，但按 `(project, subproject, time)` 排序。

```sql theme={null}
CREATE TABLE wikistat_project_subproject
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = MergeTree
ORDER BY (project, subproject, time);
```

现在我们来比较多个查询，看看排序键表达式对性能到底有多关键。请注意，我们尚未应用前面介绍的数据类型和编解码器优化，因此这里的任何查询性能差异都仅取决于排序顺序。

<table>
  <thead>
    <tr>
      <th style={{ width: '36%' }}>查询</th>
      <th style={{ textAlign: 'right', width: '32%' }}>`(time)`</th>
      <th style={{ textAlign: 'right', width: '32%' }}>`(project, subproject, time)`</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>
        ```sql theme={null}
        SELECT project, sum(hits) AS h
        FROM wikistat
        GROUP BY project
        ORDER BY h DESC
        LIMIT 10;
        ```
      </td>

      <td style={{ textAlign: 'right' }}>2.381 秒</td>
      <td style={{ textAlign: 'right' }}>1.660 秒</td>
    </tr>

    <tr>
      <td>
        ```sql theme={null}
        SELECT subproject, sum(hits) AS h
        FROM wikistat
        WHERE project = 'it'
        GROUP BY subproject
        ORDER BY h DESC
        LIMIT 10;
        ```
      </td>

      <td style={{ textAlign: 'right' }}>2.148 秒</td>
      <td style={{ textAlign: 'right' }}>0.058 秒</td>
    </tr>

    <tr>
      <td>
        ```sql theme={null}
        SELECT toStartOfMonth(time) AS m, sum(hits) AS h
        FROM wikistat
        WHERE (project = 'it') AND (subproject = 'zero')
        GROUP BY m
        ORDER BY m DESC
        LIMIT 10;
        ```
      </td>

      <td style={{ textAlign: 'right' }}>2.192 秒</td>
      <td style={{ textAlign: 'right' }}>0.012 秒</td>
    </tr>

    <tr>
      <td>
        ```sql theme={null}
        SELECT path, sum(hits) AS h
        FROM wikistat
        WHERE (project = 'it') AND (subproject = 'zero')
        GROUP BY path
        ORDER BY h DESC
        LIMIT 10;
        ```
      </td>

      <td style={{ textAlign: 'right' }}>2.968 秒</td>
      <td style={{ textAlign: 'right' }}>0.010 秒</td>
    </tr>
  </tbody>
</table>

<div id="time-series-materialized-views">
  ## Materialized views
</div>

另一种方法是使用 materialized view 来聚合并存储常用查询的结果。随后即可查询这些结果，而无需查询原始表。假设在我们的场景中，以下查询会被频繁执行：

```sql theme={null}
SELECT path, SUM(hits) AS v
FROM wikistat
WHERE toStartOfMonth(time) = '2015-05-01'
GROUP BY path
ORDER BY v DESC
LIMIT 10
```

```text theme={null}
┌─path──────────────────┬────────v─┐
│ -                     │ 89650862 │
│ Angelsberg            │ 19165753 │
│ Ana_Sayfa             │  6368793 │
│ Academy_Awards        │  4901276 │
│ Accueil_(homonymie)   │  3805097 │
│ Adolf_Hitler          │  2549835 │
│ 2015_in_spaceflight   │  2077164 │
│ Albert_Einstein       │  1619320 │
│ 19_Kids_and_Counting  │  1430968 │
│ 2015_Nepal_earthquake │  1406422 │
└───────────────────────┴──────────┘

10 rows in set. Elapsed: 2.285 sec. Processed 231.41 million rows, 9.22 GB (101.26 million rows/s., 4.03 GB/s.)
Peak memory usage: 1.50 GiB.
```

<div id="time-series-create-materialized-view">
  ### 创建 materialized view
</div>

我们可以创建以下 materialized view：

```sql theme={null}
CREATE TABLE wikistat_top
(
    `path` String,
    `month` Date,
    hits UInt64
)
ENGINE = SummingMergeTree
ORDER BY (month, hits);
```

```sql theme={null}
CREATE MATERIALIZED VIEW wikistat_top_mv 
TO wikistat_top
AS
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM wikistat
GROUP BY path, month;
```

<div id="time-series-backfill-destination-table">
  ### 回填目标表
</div>

这个目标表只有在向 `wikistat` 表插入新记录时才会填充，因此我们需要进行一些[回填](/docs/zh/guides/clickhouse/data-modelling/backfilling)。

最简单的方法是使用 [`INSERT INTO SELECT`](/docs/zh/reference/statements/insert-into#inserting-the-results-of-select) 语句，[利用](https://github.com/ClickHouse/examples/tree/main/ClickHouse_vs_ElasticSearch/DataAnalytics#variant-1---directly-inserting-into-the-target-table-by-using-the-materialized-views-transformation-query)该视图的 `SELECT` 查询 (转换逻辑) ，直接向 materialized view 的目标表插入数据：

```sql theme={null}
INSERT INTO wikistat_top
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM wikistat
GROUP BY path, month;
```

根据原始数据集的基数 (我们有 10 亿行！) ，这可能是一种非常占用内存的方法。或者，你也可以采用一种几乎不需要内存的变体：

* 创建一个使用 Null 表引擎的临时表
* 将平时使用的 materialized view 的一个副本连接到这个临时表
* 使用 `INSERT INTO SELECT` 查询，将原始数据集中的所有数据复制到该临时表
* 删除该临时表和临时 materialized view。

采用这种方法时，原始数据集中的行会按块复制到临时表中 (该表不会存储这些行) ，并且对于每个数据块，都会计算一个部分状态并将其写入目标表，这些状态随后会在后台逐步合并。

```sql theme={null}
CREATE TABLE wikistat_backfill
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = Null;
```

接下来，我们将创建一个 materialized view，将从 `wikistat_backfill` 读取的数据写入 `wikistat_top`

```sql theme={null}
CREATE MATERIALIZED VIEW wikistat_backfill_top_mv 
TO wikistat_top
AS
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM wikistat_backfill
GROUP BY path, month;
```

最后，我们将使用初始 `wikistat` 表中的数据填充 `wikistat_backfill`：

```sql theme={null}
INSERT INTO wikistat_backfill
SELECT * 
FROM wikistat;
```

该查询完成后，我们可以删除回填用表和 materialized view：

```sql theme={null}
DROP VIEW wikistat_backfill_top_mv;
DROP TABLE wikistat_backfill;
```

现在我们可以查询 materialized view，而不必查询原始表：

```sql theme={null}
SELECT path, sum(hits) AS hits
FROM wikistat_top
WHERE month = '2015-05-01'
GROUP BY ALL
ORDER BY hits DESC
LIMIT 10;
```

```text theme={null}
┌─path──────────────────┬─────hits─┐
│ -                     │ 89543168 │
│ Angelsberg            │  7047863 │
│ Ana_Sayfa             │  5923985 │
│ Academy_Awards        │  4497264 │
│ Accueil_(homonymie)   │  2522074 │
│ 2015_in_spaceflight   │  2050098 │
│ Adolf_Hitler          │  1559520 │
│ 19_Kids_and_Counting  │   813275 │
│ Andrzej_Duda          │   796156 │
│ 2015_Nepal_earthquake │   726327 │
└───────────────────────┴──────────┘

10 rows in set. Elapsed: 0.004 sec.
```

这里的性能提升非常明显。
此前，这个查询得出结果需要略多于 2 秒，而现在只需 4 毫秒。