This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new a2d66911cd6 [chore](docs) Fix partition cache design principles #28110
a2d66911cd6 is described below
commit a2d66911cd6898807ca307f1060ae9cec651d079
Author: Xinyi Zou <[email protected]>
AuthorDate: Thu Dec 7 22:23:46 2023 +0800
[chore](docs) Fix partition cache design principles #28110
---
.../docs/advanced/cache/partition-cache-manual.md | 24 ++++++++++++++++------
docs/en/docs/advanced/cache/query-cache.md | 7 -------
docs/en/docs/advanced/cache/sql-cache-manual.md | 6 ++++++
.../docs/advanced/cache/partition-cache-manual.md | 24 ++++++++++++++++------
docs/zh-CN/docs/advanced/cache/query-cache.md | 7 -------
docs/zh-CN/docs/advanced/cache/sql-cache-manual.md | 6 ++++++
6 files changed, 48 insertions(+), 26 deletions(-)
diff --git a/docs/en/docs/advanced/cache/partition-cache-manual.md
b/docs/en/docs/advanced/cache/partition-cache-manual.md
index 854b5629ea6..4b81b3b5221 100644
--- a/docs/en/docs/advanced/cache/partition-cache-manual.md
+++ b/docs/en/docs/advanced/cache/partition-cache-manual.md
@@ -40,17 +40,21 @@ See query-cache.md.
1. SQL can be split in parallel, Q = Q1 ∪ Q2 ... ∪ Qn, R= R1 ∪ R2 ... ∪ Rn, Q
is the query statement, R is the result set
-2. Split into read-only partitions and updateable partitions, read-only
partitions are cached, update partitions are not cached
+2. SQL only uses DATE, INT, and BIGINT types of partition field aggregation,
and only scans one partition. Therefore, it does not support partitioning by
day, but only supports partitioning by year and month.
-As above, query the number of daily users in the last 7 days. For example, if
partitioned by date, the data will only be written to the partition of the
current day. The data of other partitions other than the current day are fixed.
Under the same query SQL, query a certain area that is not updated. The
partition indicators are all fixed. As follows, the number of users in the
previous 7 days is queried on 2020-03-09. The data from 2020-03-03 to
2020-03-07 comes from the cache. The first [...]
-
-Therefore, to query N days of data, the data is updated for the most recent D
days. Similar queries with different date ranges every day only need to query D
partitions. The other parts come from the cache, which can effectively reduce
the cluster load and query time.
+3. Cache the results of some dates in the query result set, and then reduce
the date range scanned in SQL. In essence, PartitionCache does not reduce the
number of partitions scanned, but also reduces the date range scanned, thereby
reducing the amount of scanned data.
In addition some restrictions:
- Only supports grouping by partition fields, not by other fields. Grouping by
other fields may cause the group data to be updated, which will cause the cache
to become invalid.
-- Only the first half, second half and all hits of the result set are
supported. The result set is not supported to be divided into several parts by
cached data.
+- Only the first half, the second half and all hits of the result set are
supported in the cache. The result set is not supported to be divided into
several parts by cached data, and the dates of the result set must be
continuous. If there is no data in the result set on a certain day, then only
this Dates one day older will be cached.
+
+- If the predicate has columns outside the partition, you must add brackets to
the partition predicate `where k1 = 1 and (key >= "2023-10-18" and key <=
"2021-12-01")`
+
+- The number of days in the query must be greater than 1 and less than
cache_result_max_row_count, otherwise the partition cache cannot be used.
+
+- The predicate of the partition field can only be key >= a and key <= b or
key = a or key = b or key in (a,b,c).
## Usage
@@ -77,7 +81,15 @@ If the interval between the latest version of the partition
and the present is g
For detailed parameter introduction and unfinished matters, see query-cache.md.
-## Implementation principle example
+## Unfinished business
+
+Split into read-only partitions and updateable partitions, read-only
partitions are cached, update partitions are not cached
+
+As above, query the number of daily users in the last 7 days. For example, if
partitioned by date, the data will only be written to the partition of the
current day. The data of other partitions other than the current day are fixed.
Under the same query SQL, query a certain area that is not updated. The
partition indicators are all fixed. As follows, the number of users in the
previous 7 days is queried on 2020-03-09. The data from 2020-03-03 to
2020-03-07 comes from the cache. The first [...]
+
+Therefore, to query N days of data, the data is updated for the most recent D
days. Similar queries with different date ranges every day only need to query D
partitions. The other parts come from the cache, which can effectively reduce
the cluster load and query time.
+
+Implementation principle example:
```sql
MySQL [(none)]> SELECT eventdate,count(userid) FROM testdb.appevent WHERE
eventdate>="2020-03-03" AND eventdate<="2020-03-09" GROUP BY eventdate ORDER BY
eventdate;
diff --git a/docs/en/docs/advanced/cache/query-cache.md
b/docs/en/docs/advanced/cache/query-cache.md
index 39811667a90..6517dbb2886 100644
--- a/docs/en/docs/advanced/cache/query-cache.md
+++ b/docs/en/docs/advanced/cache/query-cache.md
@@ -138,10 +138,3 @@ Parameters unique to Partition Cache. The maximum number
of BE partitions refers
vim be/conf/be.conf
cache_max_partition_count=1024
```
-
-## Unfinished business
-
-- SQL contains functions that generate random values, such as random(). Using
QueryCache will cause the query results to lose their randomness, and the same
results will be obtained every time they are executed.
-- Can T+1 data also be cached using Partition? Currently it is not supported.
-- Similar SQL, 2 indicators were queried before, and now 3 indicators are
queried. Can the cache of 2 indicators be used? Not currently supported
-- Partition by date, but the data needs to be summarized by week dimension.
Can PartitionCache be used? Not currently supported
diff --git a/docs/en/docs/advanced/cache/sql-cache-manual.md
b/docs/en/docs/advanced/cache/sql-cache-manual.md
index cc33620145e..c7b82787c7e 100644
--- a/docs/en/docs/advanced/cache/sql-cache-manual.md
+++ b/docs/en/docs/advanced/cache/sql-cache-manual.md
@@ -70,3 +70,9 @@ After the first query, if the following three conditions are
met, the query resu
3. The query result bytes is less than cache_result_max_data_size in fe.conf.
For detailed parameter introduction and unfinished matters, see query-cache.md.
+
+## Unfinished business
+
+- SQL contains functions that generate random values, such as random(). Using
QueryCache will cause the query results to lose their randomness, and the same
results will be obtained every time they are executed.
+
+- Similar SQL, 2 indicators were queried before, and now 3 indicators are
queried. Can the cache of 2 indicators be used? Not currently supported
diff --git a/docs/zh-CN/docs/advanced/cache/partition-cache-manual.md
b/docs/zh-CN/docs/advanced/cache/partition-cache-manual.md
index 69796066527..a131343ce0e 100644
--- a/docs/zh-CN/docs/advanced/cache/partition-cache-manual.md
+++ b/docs/zh-CN/docs/advanced/cache/partition-cache-manual.md
@@ -40,17 +40,21 @@ under the License.
1. SQL可以并行拆分，Q = Q1 ∪ Q2 ... ∪ Qn，R= R1 ∪ R2 ... ∪ Rn，Q为查询语句，R为结果集
-2. 拆分为只读分区和可更新分区，只读分区缓存，更新分区不缓存
+2. SQL 只使用DATE、INT、BIGINT类型的分区字段聚合，且只扫描一个分区，因此不支持按天分区，只支持按年、月分区。
-如上，查询最近7天的每天用户数，如按日期分区，数据只写当天分区，当天之外的其他分区的数据，都是固定不变的，在相同的查询SQL下，查询某个不更新分区的指标都是固定的。如下，在2020-03-09当天查询前7天的用户数，2020-03-03至2020-03-07的数据来自缓存，2020-03-08第一次查询来自分区，后续的查询来自缓存，2020-03-09因为当天在不停写入，所以来自分区。
-
-因此，查询N天的数据，数据更新最近的D天，每天只是日期范围不一样相似的查询，只需要查询D个分区即可，其他部分都来自缓存，可以有效降低集群负载，减少查询时间。
+3. 将查询结果集中部分日期的结果缓存，然后缩减 SQL 中扫描的日期范围，本质 PartitionCache
并没有减少扫描的分区数量，而且缩减扫描的日期范围，从而减少扫描数据量。
此外一些限制：
- 只支持按分区字段分组，不支持按其他字段分组，按其他字段分组，该分组数据都有可能被更新，会导致缓存都失效
-- 只支持结果集的前半部分、后半部分以及全部命中缓存，不支持结果集被缓存数据分割成几个部分
+-
只支持结果集的前半部分、后半部分以及全部命中缓存，不支持结果集被缓存数据分割成几个部分，且结果集的日期必须连续，如果某一天在结果集中没有数据，那只有这一天之前的日期会被缓存。
+
+- 如果 predicate 有分区之外的列，那么必须给分区 predicate 加上括号 `where k1 = 1 and (key >=
"2023-10-18" and key <= "2021-12-01")`
+
+- 查询的天数必须大于1，小于cache_result_max_row_count，否则无法使用partition cache。
+
+- 分区字段的 predicate 只能是 key >= a and key <= b 或者 key = a or key = b 或者 key
in(a,b,c)。
## 使用方式
@@ -77,7 +81,15 @@ cache_last_version_interval_second=30
具体参数介绍和未尽事项见 query-cache.md。
-## 实现原理示例
+## 未尽事项
+
+拆分为只读分区和可更新分区，只读分区缓存，更新分区不缓存
+
+如查询最近7天的每天用户数，如按日期分区，数据只写当天分区，当天之外的其他分区的数据，都是固定不变的，在相同的查询SQL下，查询某个不更新分区的指标都是固定的。如下，在2020-03-09当天查询前7天的用户数，2020-03-03至2020-03-07的数据来自缓存，2020-03-08第一次查询来自分区，后续的查询来自缓存，2020-03-09因为当天在不停写入，所以来自分区。
+
+因此，查询N天的数据，数据更新最近的D天，每天只是日期范围不一样相似的查询，只需要查询D个分区即可，其他部分都来自缓存，可以有效降低集群负载，减少查询时间。
+
+实现原理示例:
```sql
MySQL [(none)]> SELECT eventdate,count(userid) FROM testdb.appevent WHERE
eventdate>="2020-03-03" AND eventdate<="2020-03-09" GROUP BY eventdate ORDER BY
eventdate;
diff --git a/docs/zh-CN/docs/advanced/cache/query-cache.md
b/docs/zh-CN/docs/advanced/cache/query-cache.md
index daaf22800a0..9636942b8fa 100644
--- a/docs/zh-CN/docs/advanced/cache/query-cache.md
+++ b/docs/zh-CN/docs/advanced/cache/query-cache.md
@@ -138,10 +138,3 @@ Partition Cache 独有的参数。BE最大分区数量，指每个SQL对应的
vim be/conf/be.conf
cache_max_partition_count=1024
```
-
-## 未尽事项
-
-- SQL中包含产生随机值的函数，比如 random()，使用 QueryCache 会导致查询结果失去随机性，每次执行将得到相同的结果。
-- T+1的数据，是否也可以用 PartitionCache? 目前不支持
-- 类似的SQL，之前查询了2个指标，现在查询3个指标，是否可以利用2个指标的缓存？ 目前不支持
-- 按日期分区，但是需要按周维度汇总数据，是否可用PartitionCache？ 目前不支持
diff --git a/docs/zh-CN/docs/advanced/cache/sql-cache-manual.md
b/docs/zh-CN/docs/advanced/cache/sql-cache-manual.md
index 812329b4cbb..82e6b41662e 100644
--- a/docs/zh-CN/docs/advanced/cache/sql-cache-manual.md
+++ b/docs/zh-CN/docs/advanced/cache/sql-cache-manual.md
@@ -70,3 +70,9 @@ MySQL [(none)]> set [global] enable_sql_cache=true;
3. 查询结果bytes 小于 fe.conf 中的 cache_result_max_data_size。
具体参数介绍和未尽事项见 query-cache.md。
+
+## 未尽事项
+
+- SQL中包含产生随机值的函数，比如 random()，使用 QueryCache 会导致查询结果失去随机性，每次执行将得到相同的结果。
+
+- 类似的SQL，之前查询了2个指标，现在查询3个指标，是否可以利用2个指标的缓存？ 目前不支持
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
