整合StarRocks主键表全部知识点

已于 2024-07-24 19:10:07 修改
阅读量380 收藏 19
点赞数 22
分类专栏： starrocks 文章标签： 数据库
于 2024-07-24 18:54:00 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_18218071/article/details/140670745
版权

总结StarRocks主键表的全部内容的集合（V3.2版本）

一、基本功能

  • 主键非空约束，任何一个字段都不可以为空
  • 支持导入数据时删除数据操作(CDC)
  • 支持部分更新
  • 支持独立的排序键（只有主键表支持独立排序键，而且可以不为key键）
  • Delete+Insert策略只需要读取具有相同主键值的数据中的最新数据，并且支持谓词和索引下推
  • 可以把全部主键索引加载到内存（用于加速查询/delete/update标记要删除的数据）

创建主键表ddl

CREATE TABLE pri_orders (
    order_id bigint NOT NULL,
    create_time datetime NOT NULL,
    merchant_id int NOT NULL,
    user_id int NOT NULL,
    good_name string NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id, create_time, merchant_id)
PARTITION BY date_trunc('day', create_time)
DISTRIBUTED BY HASH (merchant_id)
ORDER BY (create_time, merchant_id)
PROPERTIES (
    "enable_persistent_index" = "true"
);

INSERT INTO test.pri_orders
VALUES
    (1001, "2024-07-01 12:00:00", 2001, 3001, "tv", 5, 500, 1),
    (1002, "2024-07-01 13:00:00", 2001, 3002, "tv", 9, 900, 1),
    (1003, "2024-07-02 11:00:00", 2002, 3001, "tv", 8, 800, 1),
    (1004, "2024-07-03 10:00:00", 2004, 3003, "tv", 7, 700, 1),
    (1005, "2024-07-04 17:00:00", 2006, 3001, "tv", 6, 600, 1);

注意事项

  • 主键列必须定义在其他列之前
  • 主键必须包括分区列和分桶列
  • 主键表分桶必须使用DISTRIBUTED BY HASH，而且不能省略（也就是必须使用hash分桶）
  • 主键列支持数值（包括整型和布尔）、日期和字符串
  • 可以单独定义排序列，排序列可以修改，不支持删除，不支持修改数据类型
  • 定义排序列则前缀索引按照排序列建立，否则按照主键列建立前缀索引

重要参数
enable_persistent_index：默认值true，把主键索引持久化到磁盘，false表示把全部主键索引加载到内存。

二、分区（分区键必须为key键）

starrocks支持分区+分桶的数据分布，分区可以是单个分区，也可以是多个分区，分区是左闭右开的范围。
分区的方式：

  • 表达式分区：自动创建分区，适用于时间范围或者枚举值分区，数据导入自动创建分区（很强大）
  • Range分区：对于简单有序的数据分区（连续1天，连续6天，1-3等），动态、批量或者手动创建
  • List分区：适用于枚举值分区（比如按照国家，城市分区），手动创建

List分区不支持动态和批量创建
异步物化视图暂不支持基于使用List分区的基表创建
Range分区只支持时间类型/整数类型的字段
List分区支持字符串/时间/整数/布尔值类型字段

各个分区的创建的标识

  • range分区：PARTITION BY RANGE(dt) （支持日期/整型）
  • list分区：PARTITION BY LIST (city) （支持多种类型）
  • 时间表达式分区：PARTITION BY date_trunc/time_slice(‘day’, create_time) （支持时间）(特殊的Range分区功能）
  • 列表达式分区：PARTITION BY (dt, merchant_id) （支持多种类型）（特殊的List分区功能）
2.1 手动创建Range分区（仅支持分区键的数据类型为日期和整数类型）
-- 日期
CREATE TABLE pri_orders (
    order_id bigint NOT NULL,
    dt date NOT NULL,
    merchant_name int NOT NULL,
    user_id int NOT NULL,
    good_id int NOT NULL,
    good_name string NOT NULL,
    price int NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id, dt, merchant_name)
PARTITION BY RANGE(dt)(
    PARTITION p1 VALUES LESS THAN ("2020-01-31"),
    PARTITION p2 VALUES LESS THAN ("2020-02-29"),
    PARTITION p3 VALUES LESS THAN ("2020-03-31")
)
DISTRIBUTED BY HASH (merchant_name)
ORDER BY (dt,merchant_name)
PROPERTIES (
    "enable_persistent_index" = "true"
);

--整数
CREATE TABLE pri_orders (
    order_id bigint NOT NULL,
    dt date NOT NULL,
    merchant_id int NOT NULL,
    user_id int NOT NULL,
    good_id int NOT NULL,
    good_name string NOT NULL,
    price int NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id, dt, merchant_id)
PARTITION BY RANGE(merchant_id)(
    PARTITION p1 VALUES LESS THAN ("10"),
    PARTITION p2 VALUES LESS THAN ("20"),
    PARTITION p3 VALUES LESS THAN ("30")
)
DISTRIBUTED BY HASH (merchant_id)
ORDER BY (dt,merchant_id)
PROPERTIES (
    "enable_persistent_index" = "true"
);

--联合分区
CREATE TABLE pri_orders (
    order_id bigint NOT NULL,
    dt date NOT NULL,
    merchant_id int NOT NULL,
    user_id int NOT NULL,
    good_id int NOT NULL,
    good_name string NOT NULL,
    price int NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id, dt, merchant_id)
PARTITION BY RANGE(dt, order_id)(
    PARTITION p1 VALUES LESS THAN ("2020-01-31", "1"),
    PARTITION p2 VALUES LESS THAN ("2020-02-29", "2"),
    PARTITION p3 VALUES LESS THAN ("2020-03-31", "3")
)
DISTRIBUTED BY HASH (merchant_id)
ORDER BY (dt,merchant_id)
PROPERTIES (
    "enable_persistent_index" = "true"
);
2.2 批量创建Range分区（仅支持分区键的数据类型为日期和整数类型）

方法：START、END 指定批量分区的开始和结束，EVERY子句指定分区增量值，时间指定单位HOUR（3.0V）、DAY、WEEK、MONTH、YEAR

--日期
CREATE TABLE pri_orders (
    order_id bigint NOT NULL,
    dt date NOT NULL,
    merchant_name String NOT NULL,
    user_id int NOT NULL,
    good_id int NOT NULL,
    good_name string NOT NULL,
    price int NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id, dt, merchant_name)
PARTITION BY RANGE(dt)(
    START ("2021-01-01") END ("2021-01-04") EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH (merchant_name)
ORDER BY (dt,merchant_name)
PROPERTIES (
    "enable_persistent_index" = "true"
);


--整数
CREATE TABLE pri_orders (
    order_id bigint NOT NULL,
    dt date NOT NULL,
    merchant_id int NOT NULL,
    user_id int NOT NULL,
    good_id int NOT NULL,
    good_name string NOT NULL,
    price int NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id, dt, merchant_id)
PARTITION BY RANGE(merchant_id)(
     START ("1") END ("5") EVERY (1)
)
DISTRIBUTED BY HASH (merchant_id)
ORDER BY (dt,merchant_id)
PROPERTIES (
    "enable_persistent_index" = "true"
);
2.3 动态创建Range分区（仅支持分区键的数据类型为日期类型）

动态分区通过PROPERTIES进行配置

表达式分区与动态分区创建分区的区别：表达式分区会根据导入数据自动创建分区，而动态分区是根据动态分区属性，定期提前创建一些分区，如果导入数据不属于任何存在的分区，则会报错。
动态分区：⾃动提前创建新的分区，删除过期分区（可以设置TTL）

--日期
CREATE TABLE pri_orders (
    order_id bigint NOT NULL,
    dt date NOT NULL,
    merchant_id int NOT NULL,
    user_id int NOT NULL,
    good_id int NOT NULL,
    good_name string NOT NULL,
    price int NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id, dt, merchant_id)
PARTITION BY RANGE(dt)(
    PARTITION p20200321 VALUES LESS THAN ("2020-03-22"),
    PARTITION p20200322 VALUES LESS THAN ("2020-03-23")
)
DISTRIBUTED BY HASH (merchant_id)
ORDER BY (dt,merchant_id)
PROPERTIES (
    "enable_persistent_index" = "true",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-3",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.history_partition_num" = "0"
);

表示依照当天时间为基准，删除3天前的分区，同时创建3天后的分区
dynamic_partition_check_interval_seconds：FE 配置项，动态分区检查的时间周期，默认为 600, 周期检查是否删除分区与创建分区
其他配置项见官网（动态分区）
动态分区只能创建时间类型的分区

2.4 手动创建List分区（V3.1支持）

分区键支持字符串/时间/整数/布尔值类型字段
分区键不可以为null

--列的多个值放在一个分区
CREATE TABLE pri_orders (
    id bigint,
    city varchar(20) not null,
    user_id bigint,
    recharge_money decimal(32,2), 
    dt varchar(20) not null
)
PRIMARY KEY (id, city)
PARTITION BY LIST (city) (
   PARTITION pCalifornia VALUES IN ("Los Angeles","San Francisco","San Diego"),
   PARTITION pTexas VALUES IN ("Houston","Dallas","Austin")
)
DISTRIBUTED BY HASH(`id`);

--联合分区（列多个值放在一个分区）
CREATE TABLE pri_orders (
    order_id bigint NOT NULL,
    dt date NOT NULL,
    city varchar(20) not null,
    merchant_id int NOT NULL,
    user_id int NOT NULL,
    good_id int NOT NULL,
    good_name string NOT NULL,
    price int NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id, dt, city)
PARTITION BY LIST (dt,city) (
   PARTITION p202204_California VALUES IN (
       ("2022-04-01", "Los Angeles"),
       ("2022-04-01", "San Francisco"),
       ("2022-04-02", "Los Angeles"),
       ("2022-04-02", "San Francisco")
    ),
   PARTITION p202204_Texas VALUES IN (
       ("2022-04-01", "Houston"),
       ("2022-04-01", "Dallas"),
       ("2022-04-02", "Houston"),
       ("2022-04-02", "Dallas")
   )
)
DISTRIBUTED BY HASH (city)
ORDER BY (dt,city)
PROPERTIES (
    "enable_persistent_index" = "true"
);
2.5 表达式分区
  • 根据表达式的值可以自动创建对应分区，不需要提前创建分区
  • 基本适用于多数场景（覆盖大部分range分区与list分区功能，但是部分场景可能需要独立使用Range或者List分区）
  • 自动创建分区数量上限默认为 4096
2.5.1 时间函数表达式分区
  • 分区列只能有一个，而且数据类型必须是时间类型
  • 分区粒度：hour、day、month 或 year
  • 仅支持date_trunc和time_slice函数，应用到分区列上
  • 分区列值可以为null
-- 天级别分区
CREATE TABLE pri_orders (
    order_id bigint NOT NULL,
    create_time datetime NOT NULL,
    merchant_id int NOT NULL,
    user_id int NOT NULL,
    good_name string NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id, create_time, merchant_id)
PARTITION BY date_trunc('day', create_time)
DISTRIBUTED BY HASH (merchant_id)
ORDER BY (create_time, merchant_id)
PROPERTIES (
    "enable_persistent_index" = "true"
);

--时间分片，7天内的放在一个分区
CREATE TABLE pri_orders (
    order_id bigint NOT NULL,
    create_time datetime NOT NULL,
    merchant_id int NOT NULL,
    user_id int NOT NULL,
    good_name string NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id, create_time, merchant_id)
PARTITION BY time_slice(create_time, INTERVAL 7 day)
DISTRIBUTED BY HASH (merchant_id)
ORDER BY (user_id, merchant_id)
PROPERTIES (
    "enable_persistent_index" = "true"
);
2.5.2 列表达式分区（V3.1）
  • 分区列可以多个，数据类型包括字符串、日期、整数和布尔值
  • 分区值不支持null
  • 每个列值都会创建一个分区
CREATE TABLE pri_orders (
    order_id bigint NOT NULL,
    dt date NOT NULL,
    merchant_id int NOT NULL,
    user_id int NOT NULL,
    good_id int NOT NULL,
    good_name string NOT NULL,
    price int NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id, dt, merchant_id)
PARTITION BY (dt, merchant_id)
DISTRIBUTED BY HASH (merchant_id)
ORDER BY (dt,merchant_id)
PROPERTIES (
    "enable_persistent_index" = "true"
);
2.6 建表后创建分区
--Range分区批量日期
ALTER TABLE pri_orders ADD PARTITIONS START ("2021-01-10") END ("2021-01-20") EVERY (INTERVAL 1 DAY);

--Range分区批量整数
ALTER TABLE pri_orders ADD PARTITIONS START ("100") END ("200") EVERY (50);

--List分区不支持批量创建，只能手动单个分区
ALTER TABLE pri_orders ADD PARTITION p202205_California VALUES IN (
       ("2022-05-03", "Los Angeles"),
       ("2022-05-03", "San Francisco"),
       ("2022-05-04", "Los Angeles"),
       ("2022-05-05", "San Francisco")
);

--Range分区创建单个日期分区
ALTER TABLE pri_orders 
ADD PARTITION p20200130 VALUES LESS THAN ("2021-01-22")

--Range分区创建单个整数分区
ALTER TABLE pri_orders ADD PARTITION p300 VALUES LESS THAN ('300');
2.7 管理分区
2.7.1 删除分区(默认会保留一天，误删可以使用恢复分区恢复）
测试发现LIST分区无法删除
Range分区可以删除
ALTER TABLE pri_orders DROP PARTITION p1;
2.7.2 恢复分区
RECOVER PARTITION p1 FROM pri_orders;
2.7.3 查看分区
SHOW PARTITIONS FROM pri_orders;
三、分桶
3.1 哈希分桶
  • 分区内部的数据按照分桶键存放数据
  • 支持多个分桶键，主键表分桶键必须为key键
  • 分桶键值尽量多基数，避免数据倾斜
  • 分桶键支持的数据类型：整型、字符串、日期时间
  • 建表后可以修改分桶键（V3.2）
CREATE TABLE pri_orders (
    order_id bigint NOT NULL,
    dt date NOT NULL,
    merchant_name String NOT NULL,
    user_id int NOT NULL,
    good_id int NOT NULL,
    good_name string NOT NULL,
    price int NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id, dt, merchant_name)
PARTITION BY RANGE(dt)(
    START ("2021-01-01") END ("2021-01-04") EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH (dt, merchant_name)
ORDER BY (dt,merchant_name)
PROPERTIES (
    "enable_persistent_index" = "true"
);
3.2.1 哈希分桶数
  • 每个分桶的数据规模最好不要超过10G
  • 单个分区数据超过100G，建议手动设置分区，其他建议系统自动分桶数
--显示指定分桶数
--一个分区的原始数据量为 300 GB，则按照每 10 GB 原始数据一个 Tablet，则分区中分桶数量可以设置为 30
CREATE TABLE pri_orders (
    order_id bigint NOT NULL,
    dt date NOT NULL,
    merchant_name String NOT NULL,
    user_id int NOT NULL,
    good_id int NOT NULL,
    good_name string NOT NULL,
    price int NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id, dt, merchant_name)
PARTITION BY RANGE(dt)(
    START ("2021-01-01") END ("2021-01-04") EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH (dt, merchant_name) BUCKETS 30
ORDER BY (dt,merchant_name)
PROPERTIES (
    "enable_persistent_index" = "true"
);

建表后设置分桶数

-- 手动指定所有分区中分桶数量
ALTER TABLE pri_orders DISTRIBUTED BY HASH(dt, merchant_name) BUCKETS 20;

-- 手动指定部分分区中分桶数量
ALTER TABLE pri_orders partitions p20210102 DISTRIBUTED BY HASH(dt,merchant_name) BUCKETS 30;

查看分桶数

SHOW PARTITIONS from pri_orders

三、索引

starrocks内部自动创建的索引（内部索引）：前缀索引、Ordinal索引、ZoneMap 索引
需要用户手动创建的索引：Bitmap 索引和 Bloom filter 索引

3.1 Ordinal索引（列级索引）

starrocks采用列式存储，每一列数据以Date Page（64*1024 个字节，64KB）为单位分块存储，每个Date Page的起始行号作为Ordinal索引项。所以其他所有的索引都要通过Ordinal索引找到数据的物理地址。
在这里插入图片描述

3.2 ZoneMap索引

ZoneMap索引存储了每块数据统计信息，统计信息包括 Min 最大值、Max 最小值、HasNull 空值、HasNotNull 不全为空的信息。
在查询时，StarRocks 可以根据这些统计信息，快速判断这些数据块是否可以过滤掉，从而减少扫描数据量，提升查询速度。
ZoneMap索引有两种：一种是存每个 Segment 的统计信息，另一种是存每个 Data Page 的统计信息

3.3 前缀索引

主键的如果定义排序键（order by），则前缀索引使用排序键建立前缀索引，否则使用PRIMARY KEY建立索引
前缀索引每1024行数据创建一个索引项，所以前缀索引一般都可以全部加载到内存，加速查询速度。
一个表只能有一个前缀索引
主键表可以在建表后修改排序键
注意事项：

  • 排序列不宜过多，一般为3个，建议不超过4个
  • 前缀索引项的最大长度为36字节，超过部分会被截断
  • 前缀字段中CHAR、VARCHAR、STRING类型的列只能出现一次，并且处在末尾位置，而且超过部分会被截断
3.4 Bitmap索引

原理：bitmap即为一个bit数组，一个bit的取值有两种：0或1。每一个bit对应数据表中的一行，并根据该行的取值情况来决定bit的取值是0还是1。
适用场景：

  • 于较高基数列的查询
  • 多个低基数列的组合查询
  • 等值条件过滤

劣势：

  • 查询时加载Bitmap索引的开销
  • 写入数据可能会影响损耗性能

查询有Bitmap索引的自适应选择机制
原因：由于查询bitmap可能有性能损耗，所以建立bitmap索引是否使用，由starrocks自动控制（也可以强制使用）
原则：查询条件涉及的列值数量/基数 是否小于 1/1000

SELECT * FROM employees WHERE gender = 'male'
由于 gender 基数 只用两个，查询只查询其中一个， 1/2 > 1/1000 所以不使用对应的bitmap索引

SELECT * FROM employees WHERE gender = 'male' AND city IN ('北京', '上海');
假设city列的基数为10000，查询条件涉及其中2个值，结合gender，则(1*2)/(2*10000) < 1/1000 所以使用对应的bitmap索引
3.4.1 创建bitmap索引
  • 主键表所有的列都可以创建bitmap索引
  • 每个索引只能一个列
  • 常见的数据类型都支持
CREATE TABLE pri_orders (
    order_id bigint NOT NULL,
    create_time datetime NOT NULL,
    merchant_id int NOT NULL,
    user_id int NOT NULL,
    good_name string NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL,
    INDEX i_merchant_id (merchant_id) USING BITMAP,
    INDEX i_good_name (good_name) USING BITMAP
)
PRIMARY KEY (order_id, create_time, merchant_id)
PARTITION BY date_trunc('day', create_time)
DISTRIBUTED BY HASH (merchant_id)
ORDER BY (create_time, merchant_id)
PROPERTIES (
    "enable_persistent_index" = "true"
);
3.4.2 管理bitmap索引
--创建索引
CREATE INDEX i_user_id ON pri_orders (user_id) USING BITMAP;

--查看创建进度
SHOW ALTER TABLE COLUMN FROM db;

--查看索引
SHOW INDEX FROM pri_orders;

--删除索引
DROP INDEX i_user_id ON pri_orders;
3.5 Bloom filter索引

原理：快速判断表的数据文件中是否可能包含要查询的数据，如果不包含就跳过。

3.5.1 创建Bloom filter索引
  • 主键表所有的列都可以创建Bloom filter索引
  • 每个索引只能一个列，可以同时创建多个索引，用,分隔
  • 常见的数据类型都支持
CREATE TABLE pri_orders (
    order_id bigint NOT NULL,
    create_time datetime NOT NULL,
    merchant_id int NOT NULL,
    user_id int NOT NULL,
    good_name string NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id, create_time, merchant_id)
PARTITION BY date_trunc('day', create_time)
DISTRIBUTED BY HASH (merchant_id)
ORDER BY (create_time, merchant_id)
PROPERTIES (
    "enable_persistent_index" = "true",
    "bloom_filter_columns" = "merchant_id,good_name"
);
3.5.2 管理Bloom filter索引
--创建索引
ALTER TABLE pri_orders SET ("bloom_filter_columns" = "merchant_id,good_name,user_id");

--查看索引
SHOW CREATE TABLE pri_orders;

--删除索引
ALTER TABLE pri_orders SET ("bloom_filter_columns" = "");

四、同步物化视图

主键表不支持同步物化视图

五、异步物化视图

异步物化视图优势：

  • 支持外部表，多表关联查询
  • 支持部分分区刷新，定时刷新与手动刷新 （异步机制）
  • 支持查询改写：直接查询基表，可以自动从对应物化视图获取数据
  • 支持直接查询异步物化视图
  • 异步物化视图可以与其基表设置不同的分区和分桶策略，并且可以为分区指定TTL
  • 异步物化视图支持分区上卷
  • 支持单批次最大刷新分区数与只刷新最近N个分区

异步物化视图局限性：

  • 基表发生变化，物化视图可能还没有发生变化
  • 只能对有分区的基表创建异步物化视图
  • 不支持List分区的基表创建物化视图，同时不能创建List分区的异步物化视图
5.1 创建异步物化视图
CREATE MATERIALIZED VIEW merchant_mv
PARTITION BY create_date
DISTRIBUTED BY HASH(merchant_id)
REFRESH IMMEDIATE ASYNC START('2024-07-01 00:00:00') EVERY (interval 1 minute)
PROPERTIES(
  "partition_ttl_number" = "5",
  "auto_refresh_partitions_limit" = "2",
  "partition_refresh_number" = "2"
)
AS 
SELECT
    date_trunc('day', create_time) as create_date,
    merchant_id,
    sum(revenue) as total_revenue,
    count(*) as cnt
FROM pri_orders 
GROUP BY create_date, merchant_id;

基表的查询尽量不要使用order by 可能导致查询改写失败

5.2 设置分区

由于starrocks的range分区与表达式分区，基本都是针对时间类型的字段，而且基本都是单字段分区，所以异步物化视图的分区基本只能是时间类型的字段单分区。

  • 物化视图分区键必须是基表的分区键

  • 物化视图的分区字段可以是基表的String类型的时间格式字段，创建分区的时候转换成时间类型即可（str2date）

PARTITION BY str2date(datekey, '%Y-%m-%d')

  • 增量更新：如果物化视图分区内的基表数据没有发生变化，则对应的物化视图分区数据不会刷新

  • partition_ttl_number: 需要保留的最近的物化视图分区数量，也就是ttl，比如上例子中"partition_ttl_number" = "5"，表示保留最近5个分区，其他分区会定时删除。

  • partition_refresh_number：表示单批次最大刷新分区数，当多个分区数据都发生变化，则会分批次刷新数据，每批次都刷新对应的分区数，避免资源过多消耗。

  • auto_refresh_partitions_limit: 表示需要刷新的最近的物化视图分区数量，比如上例子中"auto_refresh_partitions_limit" = "2"，表示只刷新最近2个分区，其他的分区数据当发生变化也不再刷新，但是在数据查询改写中，对于不刷新的分区，查询会直接查询基表的数据（有数据变化）

5.3 设置刷新模式
5.3.1 自动刷新

REFRESH ASYNC : 每当基表数据发生变化时，物化视图会自动刷新

5.3.2 定时刷新

ASYNC [START (<start_time>)] EVERY(INTERVAL <interval>)

day/hour/minute/second

--从当前时间开始
REFRESH ASYNC EVERY (interval 1 minute)

--指定开始时间
REFRESH ASYNC START('2024-07-01 00:00:00') EVERY (interval 1 minute)
5.3.3 设置成手动刷新

REFRESH MANUAL: 设置成手动刷新

-- 异步调用刷新任务
REFRESH MATERIALIZED VIEW order_mv;

-- 同步调用刷新任务
REFRESH MATERIALIZED VIEW order_mv WITH SYNC MODE;
5.3.4 创建异步物化视图是否立即执行刷新

IMMEDIATE：异步物化视图创建成功后立即刷新
DEFERRED：异步物化视图创建成功后不进行刷新，您可以通过手动调用或创建定时任务触发刷新

5.3.4 查询异步物化视图

可以直接查询异步物化视图，同时也支持查询基表的查询改写

5.4 管理异步物化视图
5.4.1 修改异步物化视图
-- 启用被禁用的异步物化视图
ALTER MATERIALIZED VIEW order_mv ACTIVE;

-- 重命名
ALTER MATERIALIZED VIEW order_mv RENAME order_total;

-- 修改刷新机制
ALTER MATERIALIZED VIEW order_mv REFRESH ASYNC EVERY(INTERVAL 2 DAY);
5.4.2 查看异步物化视图
-- 查看所有的物化视图
SHOW MATERIALIZED VIEWS;

-- 按条件查询
SHOW MATERIALIZED VIEWS WHERE NAME LIKE "order%";

-- 查看物化视图的创建语句
SHOW CREATE MATERIALIZED VIEW merchant_mv_3;
5.4.3 查询物化视图执行状态与异步刷新情况
-- 找到 物化视图的任务 task_name
select * from information_schema.tasks  order by CREATE_TIME desc

-- 通过task_name 获取 物化视图执行与刷新情况
select * from information_schema.task_runs where task_name='mv-11576' order by CREATE_TIME

在这里插入图片描述

5.4.4 删除物化视图
DROP MATERIALIZED VIEW order_mv;

六、数据去重

starrocks是mpp架构的引擎，所以在数据去重方面，就需要把各个BE节点上的数据进行shuffle，所以如果大规模的数据会导致计算资源紧张，所以对于数据去重，starrcoks提供两种特殊的额外的方式，但是都是有局限性的。

6.1 常规去重

对于数据集基数在百万、千万量级，并拥有几十台机器，那么您可以直接使用count distinct方式

select date_trunc('day', create_time) as create_date, count(distinct user_id) as cnt
from pri_orders
group by create_date
6.2 Bitmap精确去重

主键表不支持：PRIMARY_KEYS table should not specify aggregate type for non-key column[user_id].

6.3 HyperLogLog近似去重

不键表不支持

七、Colocate Join

功能：Colocate Join 功能是分布式系统实现 Join 数据分布的策略之一，能够减少数据多节点分布时 Join 操作引起的数据移动和网络传输，从而提高查询性能。

原理：同一CG内，所有表的数据分布在相同一组 BE 节点上。当 Join 列为分桶键时，计算节点只需做本地 Join，从而减少数据在节点间的传输耗时，提高查询性能（把相同数据放在同一个BE上，数据本地join）， 所以当数据迁移都是一起均衡操作，在数据迁移过程中，查询退回普通的join（shfflue）

条件：

  • 表具有相同数据类型的分桶键（名字可以不一样），而且顺序要保持一致，桶数一致
  • 表具有相同的副本数
  • 查询只支持等值查询（其他比较都失效，退化成普通的join）

使用方式：在表PROPERTIES中指定属性"colocate_with" = “group_name”

如果指定的 CG 不存在，StarRocks 会自动创建一个只包含当前表的 CG，并指定当前表为该 CG 的 Parent Table。如果 CG 已存在，StarRocks 会检查当前表是否满足条件。如果满足，StarRocks 会创建该表，并将该表加入 Group。同时，StarRocks 会根据已存在的 Group 中的数据分布规则为当前表创建分片和副本。

7.1 创建Colocate Join表
CREATE TABLE pri_orders_colocate (
    order_id bigint NOT NULL,
    create_time datetime NOT NULL,
    merchant_id int NOT NULL,
    user_id int NOT NULL,
    good_name string NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id, create_time, merchant_id)
PARTITION BY date_trunc('day', create_time)
DISTRIBUTED BY HASH (merchant_id) BUCKETS 8
ORDER BY (create_time, merchant_id)
PROPERTIES (
    "colocate_with" = "order_merchant_group",
    "enable_persistent_index" = "true"
);

CREATE TABLE IF NOT EXISTS test.aggregate_tbl_colocate (
    site_id int NOT NULL COMMENT "id of site",
    date DATETIME NOT NULL COMMENT "time of event",
    city_code VARCHAR(20) COMMENT "city_code of user",
    pv BIGINT SUM DEFAULT "0" COMMENT "total page views"
)
AGGREGATE KEY(site_id, date, city_code)
PARTITION BY date_trunc('day', date)
DISTRIBUTED BY HASH(site_id) BUCKETS 8
PROPERTIES (
    "colocate_with" = "order_merchant_group",
    "replication_num" = "1"
);
7.2 修改表Colocate Join组
ALTER TABLE tbl SET ("colocate_with" = "group_name");
7.2 删除Colocate Join
-- 删除表的Colocate Join属性
ALTER TABLE tbl SET ("colocate_with" = "");
  • 删除特定的Colocate Join组
    当所有的设置colocate_with的表都删除，并且真正后台清理掉后（默认保留一天），colocate_with设置的组才会真实的删掉
7.3 是否真实使用Colocate Join
-- 查看 colocation_group
SHOW PROC '/colocation_group';

-- 查看特定group bucket分布的BE
SHOW PROC '/colocation_group/11912.11916';

在这里插入图片描述

使用Colocate Join和使用普通的join是一样的，通过expain命令查看是否真实使用Colocate Join，如果Colocate Join失效或者不稳定，则会退化成普通的join查询。

explain 
select * 
from test.pri_orders_colocate as pri 
INNER JOIN 
test.aggregate_tbl_colocate as agg 
on (pri.merchant_id = agg.site_id)
and pri.create_time > "2024-07-01 12:00:00"

在这里插入图片描述

八、Query Cache

8.1、Query Cache使用场景

query cache默认是关闭的。

  • 查询多为宽表模型下的单表聚合查询或星型模型下简单多表 JOIN 的聚合查询。
  • 聚合查询以非 GROUP BY 聚合和低基数 GROUP BY 聚合为主。
  • 查询的数据以按时间分区追加的形式导入，并且在不同时间分区上的访问表现出冷热性。
8.2、Query Cache命中高效性

会对query判断是否语义等价

  • 聚合语句的第一次聚合是语义等价就判定语义等价
q1:
SELECT
   sum(murmur_hash3_32(hour)), 0)) + sumvi AS fingerprint
FROM
(
    SELECT
        date_trunc('hour', ts) AS hour,
        sum(v1) AS sumvi
    FROM
          t0
    WHERE
        ts BETWEEN '2022-01-03 00:00:00'
        AND '2022-01-03 23:59:59'
    GROUP BY
        date_trunc('hour', ts)
) AS t;

q2:

SELECT
    date_trunc('hour', ts) AS hour,
    sum(v1) AS sumvi
FROM
      t0
WHERE
    ts BETWEEN '2022-01-03 00:00:00'
    AND '2022-01-03 23:59:59'
GROUP BY
    date_trunc('hour', ts)
  • 是否含有 ORDER BY 子句和 LIMIT 子句，不影响两个查询的语义等价
  • where 条件的先后顺序， group by 先后顺序不影响语义等价

九、CBO 统计信息

CBO优化器（cost-based Optimizer)是查询优化的关键，CBO会统计starrcoks中表的信息，当一条Sql查询到达Starrocks后，会基于统计信息，选择一个最优的执行路径作为最终的物理查询计划。

9.1、CBO统计信息的类别（列的粒度）

CBO统计表的信息主要分为两种：
第一种是统计表的列的基础统计信息，包括：

  • row_count: 表的总行数
  • data_size: 列的数据大小
  • ndv: 列基数，即 distinct value 的个数
  • null_count: 列中 NULL 值的个数
  • min: 列的最小值
  • max: 列的最大值

上面的信息存储在_statistics_.column_statistics表中，是按照表的分区内各个列进行统计的。
在这里插入图片描述

第二种是把表的列信息统计到直方图中（用直方图表达数据的情况）
直方图能方便的展示数据列的倾斜分布不均的情况。支持列的数据类型是数值类型、DATE、DATETIME 或字符串类型。
直方图目前只支持手动采样采集，采集的数据存储zai _statistics_.histogram_statistics表中，其中mcv表示列的值出现的次数
在这里插入图片描述

9.2、自动（全量/抽样）采集基础信息

自动全量采集基础信息可能消耗大量的系统资源，默认5分钟一次，默认开启自动全量采集基础信息。可以配置参数statistic_auto_analyze_start_time, statistic_auto_analyze_end_time设置每天的开始结束时间。

在自动全量采集基础信息中，如果表过大或者设置的健康度达到阈值，将把自动全量采集基础信息变更为自动抽样采集基础信息。具体信息见官网。

9.3、自定义自动（全量/抽样）采集基础信息

创建自动意自动采集任务，需要先关闭自动全量采集基础信息enable_collect_full_statistic=false，采集相关参数见官网。

语法：
CREATE ANALYZE [FULL|SAMPLE] TABLE tbl_name (col_name [,col_name]) [PROPERTIES (property [,property])]

例子：
-- 定期全量采集所有数据库的统计信息。
CREATE ANALYZE ALL;

-- 定期全量采集指定数据库下所有表的统计信息。
CREATE ANALYZE FULL DATABASE db_name;

-- 定期全量采集指定表、列的统计信息。
CREATE ANALYZE TABLE tbl_name(c1, c2, c3); 

-- 定期抽样采集指定数据库下所有表的统计信息。
CREATE ANALYZE SAMPLE DATABASE db_name;

-- 自动采集所有数据库的统计信息，不收集`db_name.tbl_name`表。
CREATE ANALYZE SAMPLE DATABASE db_name PROPERTIES (
   "statistic_exclude_pattern" = "db_name.tbl_name"
);
9.4、手动（全量/抽样）采集基础信息

手动任务创建后仅会执行一次，无需手动删除。

语法：
ANALYZE [FULL|SAMPLE] TABLE tbl_name (col_name [,col_name])
[WITH SYNC | ASYNC MODE]
[PROPERTIES (property [,property])]

例子：
-- 手动全量采集指定表的统计信息，使用默认配置。
ANALYZE FULL TABLE tbl_name;

-- 手动全量采集指定表指定列的统计信息，使用默认配置。
ANALYZE TABLE tbl_name(c1, c2, c3);

-- 手动抽样采集指定表的统计信息，使用默认配置。
ANALYZE SAMPLE TABLE tbl_name;

-- 手动抽样采集指定表指定列的统计信息，设置抽样行数。
ANALYZE SAMPLE TABLE tbl_name (v1, v2, v3) PROPERTIES(
    "statistic_sample_collect_rows" = "1000000"
);
9.5、手动采集直方图信息

手动任务创建后仅会执行一次，无需手动删除。

-- 语法
ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name]
[WITH SYNC | ASYNC MODE]
[WITH N BUCKETS]
[PROPERTIES (property [,property])]

例子：
-- 手动采集v1列的直方图信息，使用默认配置。
ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON v1;

-- 手动采集v1列的直方图信息，指定32个分桶，mcv指定为32个，采样比例为50%。
ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON v1,v2 WITH 32 BUCKETS 
PROPERTIES(
   "histogram_mcv_size" = "32",
   "histogram_sample_ratio" = "0.5"
);

十、性能优化

explain命令
是否命中索引
trace命令 （查询改写失败的原因）
Query Plan

陪你一起捡蛋壳
关注
  • 22
    点赞
  • 踩
  • 19
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
国产开源优秀新一代MPP数据库StarRocks入门之旅-数仓新利器（上）
weixin_45566993的博客
05-13 928
🚀 优质资源分享 🚀 学习路线指引（点击解锁） 知识定位 人群定位 🧡 Python实战微信订餐小程序 🧡 进阶级 本课程是python flask+微信小程序的完美结合，从项目搭建到腾讯云部署上线，打造一个全栈订餐系统。 💛Python量化交易实战💛 入门级 手把手带你打造一个易扩展、更安全、效率更高的量化交易系统 概述 背景 Apache Doris官方地址 https://doris.apache.org/ Apache Doris GitHub源码地址 https://gi
数据同步技术分析与介绍
liyonglin27的博客
02-26 1526
通过分析以及认证对比当下主流数据同步技术进行分析介绍内容包含CDC、OGG、DG、ADG、sql server发布订阅、mysql主从库技术、ETL定时同步技术、XTL离线同步技术等内容。
StarRocks实战——松果出行实时数仓实践
爱吃辣条的博客
03-03 1225
StarRocks实战——松果出行实时数仓实践
StarRocks实战——首汽约车实时数仓实践
爱吃辣条的博客
03-01 1296
StarRocks实战——首汽约车实时数仓实践
Doris【基础篇】
xiayuhaisong的博客
02-29 2133
Doris（原百度Palo）是一款基于大规模并行处理技术的分布式SQL数据库。基于MPP的交互式SQL数据库，可以用于OLAP。MPP是将任务并行的分散到多个服务器和节点上，在每个节点上结算完后，将各个部分的结果汇总在一起得到最终的结果。
大数据开发治理平台 DataWorks
难得糊涂
03-06 3342
结合业务过程的规划，梳理分析各业务过程中可能产生的实际数据，将这些实际数据字段通过明细表的方式存储下来。当企业业务比较复杂，不同类型业务彼此间需要共享数据域，但是又希望能在模型设计和应用过程中快速定位本业务的数据时，您可结合真实业务情况，规划不同的业务分类，在后续建模的维度表和明细表中，将其关联到对应的业务分类中。----常用的类似于数据分层。这里的数仓规格通俗点讲是，给数据贴标签，根据数据的处理流程，业务分类，业务过程，数据域等分类方式来区分不同阶段，场景，业务等类型的数据，方便将来根据类型来查询数据。
SQL代码开发规范
woaini886353的博客
04-24 1533
SQL编码规范 文 档 信 息 文档名称： SQL编码规范 电子文档： 版本号： 密级： 文档编号： 编写人： 日期： ...
干货 | 分布式数据库TiDB在携程的实践
携程技术
12-09 555
作者简介Army，携程数据库专家，主要负责分布式数据库运维及研究。Keira，资深数据库工程师，主要负责MySQL和TiDB运维。Rongjun，携程大数据架构开发，专注离线和实时大数据...
2023年8月国产数据库大事记-墨天轮
Era666的博客
09-08 1110
本文为墨天轮社区整理的2023年8月国产数据库大事件和重要产品发布消息。
2023年Access数据表知识点.doc
11-05
Access 2023是Microsoft Office套件中...总的来说，Access 2023的数据表管理涉及表的定义、字段设置、数据输入、主键和外键的使用、以及数据交换等多个方面，熟练掌握这些知识点将有助于构建和管理高效、规范的数据库。
获取缺失主键表信息的MYSQL语句
09-10
在MySQL数据库管理中，主键是非常重要的组成部分，它用于唯一标识表中的每一行记录，确保数据的完整性和一致性。然而，有时候我们可能会遇到没有设置主键的表，这对于数据库的管理和优化是不利的。本篇文章将详细...
MySQL必备的常见知识点汇总整理
09-08
MySQL是世界上最流行的关系型数据库管理系统之一，掌握其基本知识点对于任何IT从业者来说都是至关重要的。以下是一些关于MySQL的关键概念和操作的详细说明： 1. **登录与退出**： - 使用`mysql -u <username> -p...
Sql Server的一些知识点定义总结
09-09
每个表应有一个主键，确保每一行都有唯一的标识，并且不允许主键字段为空或重复。主键的定义是确保实体完整性的关键。 2. 域完整性（Domain Integrity）：这关注的是表内列的约束。例如，列必须符合特定的数据类型...
数据库技术及应用知识点总结.doc
11-29
数据库技术及应用知识点总结 本文总结了数据库技术及应用的知识点，涵盖了数据库的基本概念、数据模型、关系模型、E-R 模型、数据库设计、Access 数据库操作等方面的内容。 一、数据库基本概念 * 数据：对客观...
OAuth2.0 or Spring Session or 单点登录流程
qq_53374893的博客
07-20 388
一句话精辟解释： 在过滤器放行的时候，偷偷的把原生的 request 和 response 对象换成了它的实现，也就是 调用getSession 的时候拿到的 其实是对redis 操作的Session。但其他的操作还是使用原生的，只不过重写的方法，调用的是子类的，也就是往 redis 里放入 Session，把原生的操作给替换了！分布式Session原理，使用子域名，的时候放入父域名的 JsessionId 然后将这个ID 的所存的内容放入Redis ，这样实现不同域名共享同一sessionID.
基于JSP的高校信息资源共享平台
heye0910032的博客
07-22 579
高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。通过使用JSP技术和MySQL数据库，本系统实现了一个稳定、安全、易用的高校信息资源管理平台。虽然在开发过程中遇到了一些技术挑战，但通过不断学习和实践，最终完成了一个能够满足用户需求的系统。未来，我们将继续优化系统功能，提升用户体验，以期达到更高的应用价值和社会效益。高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。
秒杀业务: 简单梳理
最新发布
m0_59925573的博客
07-23 900
（1）在一人一单优惠券秒杀中，先是解决了因线程安全导致的超卖问题，为了满足一人一单的业务需求需要给查询订单表查询库存表上锁，但是synchronized只能满足单机环境下线程安全，在分布式环境对多个JVM需要使用分布式锁，于是手写分布式锁,但是在极端情况下，例如业务超时，会出现“误删”问题，手写锁无法解决，于是引入redisson,借助看门狗机制可以有效解决这个问题。
MySQL（事务、索引）&&MyBatis
hycccccch的博客
07-19 760
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
show keys获取starrocks主键
06-09
要获取StarRocks的主键，可以使用SHOW KEYS语句。具体的语法如下： SHOW KEYS FROM <database_name>.<table_name> 其中，<database_name>是要查询的数据库名称，<table_name>是要查询的表名称。执行该语句后，将会返回该表的主键信息。例如： SHOW KEYS FROM db1.table1; 可以获取db1数据库中table1表的主键信息。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值