08. Doris 与 StarRocks：MPP 实时分析

-- Aggregate Key 模型（预聚合）
CREATE TABLE user_stat (
    user_id BIGINT,
    dt DATE,
    city VARCHAR(50),
    
    -- 聚合列：必须声明聚合函数
    visit_count SUM(INT),
    stay_time SUM(INT),
    last_visit REPLACE(DATETIME)
)
AGGREGATE KEY(user_id, dt, city)
DISTRIBUTED BY HASH(user_id) BUCKETS 16
PROPERTIES ("replication_num" = "3");

-- 查询时自动聚合
dt = '2024-01-01' 的三条记录:
(user1, 2024-01-01, Beijing, 1, 60, '09:00')
(user1, 2024-01-01, Beijing, 1, 45, '10:00')
→ 自动合并为 (user1, 2024-01-01, Beijing, 2, 105, '10:00')

-- Unique Key 模型（去重 + 全列更新）
CREATE TABLE users (
    user_id BIGINT,
    user_name VARCHAR(100),
    city VARCHAR(50),
    update_time DATETIME
)
UNIQUE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 16
PROPERTIES ("replication_num" = "3");

-- 重复 user_id 时，后写入覆盖全部列

-- Duplicate Key 模型（保留所有明细）
CREATE TABLE events (
    event_id BIGINT,
    user_id BIGINT,
    event_type VARCHAR(50),
    event_time DATETIME,
    properties JSON
)
DUPLICATE KEY(event_time, event_type)
DISTRIBUTED BY HASH(event_id) BUCKETS 64
PROPERTIES ("replication_num" = "3");

-- 适用于日志、事件流等无需预聚合的场景

-- StarRocks Primary Key + MOW
CREATE TABLE orders (
    order_id BIGINT,
    user_id BIGINT,
    amount DECIMAL(18,2),
    status INT,
    update_time DATETIME
)
PRIMARY KEY(order_id)
DISTRIBUTED BY HASH(order_id) BUCKETS 16
PROPERTIES (
    "replication_num" = "3",
    "enable_persistent_index" = "true",
    -- MOW: 写入时直接 apply delete bitmap，无需读时合并
    "enable_merge_on_write" = "true"     
);

-- SQL 查询
SELECT region, SUM(amount) 
FROM orders 
WHERE dt >= '2024-01-01' 
GROUP BY region;

-- 执行计划（MPP 分布式）
-- ┌──────────────────────────────────┐
-- │  Coordinator (FE)                 │
-- │  Fragment 0: 最终聚合 + 返回        │
-- └──────────────┬───────────────────┘
--                │ Exchange (Gather)
-- ┌──────────────▼───────────────────┐
-- │  Fragment 1: 各节点聚合 (HashAgg)  │  × N BE
-- │  └─ Fragment 2: 本地过滤 + 扫描    │
-- └──────────────────────────────────┘
-- FE 将查询拆分为多个 Fragment，每个 Fragment 在 BE 并行执行

-- StarRocks 向量化执行开关（默认开启）
SET enable_pipeline_engine = true;
SET pipeline_dop = 0;   -- 0 = 自动推导并行度

-- 查看执行计划是否走 pipeline / 向量化
EXPLAIN ANALYZE 
SELECT region, SUM(amount) 
FROM orders 
GROUP BY region;

-- 输出中的 PIPELINE 标识表示使用了向量化引擎

-- Doris/StarRocks 同步物化视图（自动路由）
CREATE TABLE orders (
    order_id BIGINT,
    user_id BIGINT,
    amount DECIMAL(18,2),
    dt DATE,
    region VARCHAR(50)
)
DUPLICATE KEY(order_id)
DISTRIBUTED BY HASH(order_id) BUCKETS 16;

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_orders_region_daily AS
SELECT 
    dt, 
    region, 
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders
GROUP BY dt, region;

-- 查询自动命中物化视图
SELECT dt, region, SUM(amount) FROM orders GROUP BY dt, region;
-- FE 自动路由到 mv_orders_region_daily

-- 异步物化视图：定时刷新，适合复杂聚合
CREATE MATERIALIZED VIEW mv_user_monthly
REFRESH ASYNC START("2024-01-01 00:00:00") EVERY (INTERVAL 1 HOUR)
AS
SELECT 
    user_id,
    DATE_TRUNC('MONTH', dt) as month,
    COUNT(DISTINCT order_id) as order_count,
    SUM(amount) as total_amount,
    MAX(amount) as max_amount
FROM orders
GROUP BY user_id, month;

-- 手动刷新
REFRESH MATERIALIZED VIEW mv_user_monthly;

-- 查询（透明重写）
SELECT user_id, SUM(amount) FROM orders WHERE dt >= '2024-01-01' GROUP BY user_id;
-- 若满足条件，自动改写为查物化视图

-- 创建 Iceberg Catalog
CREATE EXTERNAL CATALOG iceberg_catalog
PROPERTIES (
    "type" = "iceberg",
    "iceberg.catalog.type" = "REST",
    "iceberg.catalog.uri" = "http://iceberg-rest:8181",
    "iceberg.catalog.warehouse" = "s3://bucket/warehouse"
);

-- 查询数据湖表（无需导入）
SELECT * FROM iceberg_catalog.db.orders WHERE dt >= '2024-01-01';

-- Join 数据湖 + 本地表
SELECT 
    o.*, u.user_name
FROM iceberg_catalog.db.orders o
JOIN local_db.users u ON o.user_id = u.user_id;

-- 谓词下推（自动）
-- 查询条件自动下推到数据湖/外部存储
SELECT * FROM iceberg_catalog.db.orders WHERE dt = '2024-01-01';
-- → Iceberg 只做 202401 分区文件扫描

-- 使用本地缓存加速湖查询
SET enable_local_disk_cache = true;

-- CBO 统计信息收集
ANALYZE TABLE iceberg_catalog.db.orders;

-- FE 配置（fe.conf）
-- 元数据保留
meta_dir = /data/fe/meta
edit_log_roll_num = 50000
-- 查询
max_running_txn_num_per_db = 100

-- BE 配置（be.conf）
-- 存储
storage_root_path = /data1/storage;/data2/storage
-- CPU/内存
mem_limit = 80%
-- Compaction
max_compaction_concurrency = 10
cumulative_compaction_num_threads_per_disk = 4
base_compaction_num_threads_per_disk = 2

-- 会话级调优
SET query_timeout = 300;
SET enable_pipeline_engine = true;
SET runtime_filter_mode = "GLOBAL";    -- 全局 Runtime Filter
SET enable_runtime_adaptive_dop = true; -- 自适应并行度

-- 1. 查看查询执行计划
EXPLAIN SELECT ...;

-- 2. 统计信息收集（CBO 依赖）
ANALYZE TABLE orders WITH SYNC MODE;

-- 3. 索引与分桶
-- 分桶键 = 高频过滤/Join 键
-- 分桶数 = BE 数量 × 单 BE 核数

-- 4. Colocation Join（避免 Shuffle）
ALTER TABLE orders SET ("colocate_with" = "group_user");
ALTER TABLE users SET ("colocate_with" = "group_user");
-- 两张表按 user_id 分桶在同一组 → Join 无需 Shuffle

-- 5. Runtime Filter（动态过滤）
-- 大表 Join 小表时，自动广播小表过滤条件
SET runtime_filter_mode = "GLOBAL";

# fe.conf — FE 高可用核心参数
meta_dir = /data/fe/meta
edit_log_roll_num = 50000
max_bdbje_clock_delta_ms = 5000
# 元数据副本数（默认与 Follower 数量一致）
metadata_failure_recovery = false
# 缓存策略
# query_cache_size_mb = 512

# 查看 BE 节点状态与负载
mysql -h fe_host -P 9030 -u root -e "SHOW PROC '/backends';"

# 输出关键字段：
# BackendId, Host, HeartbeatPort, BePort, HttpPort, BrpcPort, 
# LastStartTime, LastHeartbeat, Alive, SystemDecommissioned, TabletNum, 
# DataUsedCapacity, AvailCapacity, CpuCores, MemLimit, MemUsedPct

# StarRocks 存算分离模式部署（使用 Docker Compose 示例）
# docker-compose.yml 关键配置

cat << 'EOF' > docker-compose-cn.yml
version: "3"
services:
  fe:
    image: starrocks/fe-ubuntu:3.2-latest
    volumes:
      - ./fe/meta:/opt/starrocks/fe/meta
    environment:
      - AWS_ACCESS_KEY_ID=xxx
      - AWS_SECRET_ACCESS_KEY=yyy
    command: >
      /bin/bash -c "
        echo 'run_mode=shared_data' >> /opt/starrocks/fe/conf/fe.conf &&
        echo 'cloud_native_storage_type=S3' >> /opt/starrocks/fe/conf/fe.conf &&
        echo 'aws_s3_path=oss://bucket/starrocks' >> /opt/starrocks/fe/conf/fe.conf &&
        sh /opt/starrocks/fe/bin/start_fe.sh
      "
  cn:
    image: starrocks/cn-ubuntu:3.2-latest
    volumes:
      - ./cn/cache:/opt/starrocks/cn/storage
    environment:
      - FE_IP=fe
    command: sh /opt/starrocks/cn/bin/start_cn.sh
EOF
docker-compose -f docker-compose-cn.yml up -d

-- 验证向量化执行是否生效
EXPLAIN ANALYZE
SELECT 
    customer_id,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount,
    COUNT(DISTINCT order_id) AS unique_orders
FROM orders
WHERE dt BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY customer_id
HAVING SUM(amount) > 10000;

-- 执行计划中应出现 "PIPELINE" 字样
-- 例如：PIPELINE (colocate=false, pipeline_dop=8)

-- 全量收集统计信息（CBO 准确度的关键）
ANALYZE FULL TABLE orders;
ANALYZE FULL TABLE orders UPDATE HISTOGRAM ON amount WITH 128 BUCKETS;

-- 增量收集（大表推荐）
ANALYZE TABLE orders WITH SAMPLE PERCENT 10;

-- 查看统计信息
SHOW STATS META WHERE DatabaseName = 'db1';

-- 手动调整统计信息（特殊场景）
ALTER TABLE orders MODIFY COLUMN amount SET STATS ('row_count'='10000000', 'ndv'='500000');

-- 开启全局 Runtime Filter（默认开启，建议确认）
SET runtime_filter_mode = "GLOBAL";
SET runtime_filter_wait_time_ms = 1000;
SET runtime_filter_max_in_num = 1024;

-- 验证 Runtime Filter 是否生效
EXPLAIN 
SELECT o.* 
FROM orders o 
JOIN customers c ON o.customer_id = c.customer_id 
WHERE c.city = 'Shanghai';

-- 执行计划中应有 Runtime Filter 节点：
-- TABLE: orders
--     runtime filters: RF000[in] <- c.customer_id

-- Pipeline 自适应并行度（根据数据量自动调整）
SET enable_runtime_adaptive_dop = true;
SET pipeline_dop = 0;  -- 0 = 自动推导

-- 固定并行度（资源隔离场景）
SET pipeline_dop = 8;

-- 本地 Exchange 优化（避免不必要的 Shuffle）
SET enable_local_exchange = true;

-- 明细模型：保留所有原始数据
CREATE TABLE user_events (
    event_id BIGINT,
    user_id BIGINT,
    event_time DATETIME,
    event_type VARCHAR(64),
    properties JSON
)
DUPLICATE KEY(event_time, event_type, user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 32
PROPERTIES ("replication_num" = "3");

-- 聚合模型：写入时自动按 Key 预聚合
CREATE TABLE traffic_stat (
    dt DATE,
    page_id BIGINT,
    visit_count SUM(BIGINT),
    stay_time_ms SUM(BIGINT),
    bounce_rate SUM(BIGINT)
)
AGGREGATE KEY(dt, page_id)
DISTRIBUTED BY HASH(page_id) BUCKETS 16
PROPERTIES ("replication_num" = "3");

-- 主键模型 + MOW（Merge-on-Write）— StarRocks 推荐
CREATE TABLE realtime_orders (
    order_id BIGINT,
    user_id BIGINT,
    amount DECIMAL(18,2),
    status INT COMMENT '0=pending, 1=paid, 2=shipped, 3=completed',
    create_time DATETIME,
    update_time DATETIME
)
PRIMARY KEY(order_id)
DISTRIBUTED BY HASH(order_id) BUCKETS 32
PROPERTIES (
    "replication_num" = "3",
    "enable_persistent_index" = "true",
    "enable_merge_on_write" = "true"
);

-- Sort Key 建表示例（前缀匹配原则）
CREATE TABLE access_log (
    dt DATE,
    hour INT,
    ip VARCHAR(32),
    path VARCHAR(512),
    latency_ms INT,
    status_code INT
)
DUPLICATE KEY(dt, hour, ip)  -- Sort Key：按 dt → hour → ip 排序
DISTRIBUTED BY HASH(ip) BUCKETS 64
PROPERTIES (
    "replication_num" = "3",
    "bloom_filter_columns" = "ip, path",  -- 对高频等值过滤列加 Bloom Filter
    "datacache.enable" = "true"           -- 启用数据缓存
);

-- 查询时利用 Sort Key 前缀剪枝：
-- WHERE dt = '2024-08-01'              → 最佳：命中前缀
-- WHERE dt = '2024-08-01' AND hour = 12 → 最佳：命中前缀
-- WHERE hour = 12                       → 差：未命中前缀，全表扫描
-- WHERE ip = '10.0.0.1'                 → 差：未命中前缀

-- 基表：订单明细
CREATE TABLE order_detail (
    order_id BIGINT,
    customer_id BIGINT,
    region VARCHAR(50),
    dt DATE,
    amount DECIMAL(18,2),
    discount DECIMAL(18,2)
)
DUPLICATE KEY(order_id)
DISTRIBUTED BY HASH(order_id) BUCKETS 32;

-- 同步物化视图：按区域 + 日期预聚合
CREATE MATERIALIZED VIEW mv_region_daily AS
SELECT 
    region,
    dt,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    SUM(discount) AS total_discount
FROM order_detail
GROUP BY region, dt;

-- 查询自动命中物化视图
SELECT region, dt, SUM(amount) 
FROM order_detail 
WHERE dt = '2024-08-01' 
GROUP BY region, dt;
-- FE 自动改写为查询 mv_region_daily，避免全表扫描

-- 异步物化视图：跨表 JOIN + 复杂聚合 + 定时刷新
CREATE MATERIALIZED VIEW mv_customer_monthly
REFRESH ASYNC START("2024-01-01 00:00:00") EVERY (INTERVAL 15 MINUTE)
PARTITION BY dt
AS
SELECT 
    c.customer_id,
    c.customer_name,
    c.segment,
    DATE_TRUNC('MONTH', o.dt) AS dt,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(o.amount) AS total_amount,
    AVG(o.amount) AS avg_amount,
    MAX(o.amount) AS max_amount,
    MIN(o.amount) AS min_amount
FROM customers c
JOIN order_detail o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.segment, DATE_TRUNC('MONTH', o.dt);

-- 查询透明改写示例
SELECT 
    customer_name,
    SUM(total_amount)
FROM mv_customer_monthly
WHERE dt >= '2024-01-01' AND segment = 'Enterprise'
GROUP BY customer_name;
-- 即使 SQL 中没有显式写物化视图，FE 也会自动判断并改写

-- 查看查询是否命中物化视图改写
EXPLAIN 
SELECT customer_name, SUM(total_amount) 
FROM mv_customer_monthly 
WHERE dt >= '2024-01-01' 
GROUP BY customer_name;

-- 执行计划中应出现类似：
-- MATERIALIZED VIEW: mv_customer_monthly
--     PREAGGREGATION: ON
--     PREDICATES: dt >= '2024-01-01'

# 物化视图设计原则
rules:
  - 冷热分离: "高频查询用同步 MV，离线报表用异步 MV"
  - 维度对齐: "异步 MV 的分区键与基表保持一致，避免全局刷新"
  - 刷新窗口: "设置合理的刷新周期，避免与 ETL 峰值冲突"
  - 监控命中: "定期分析物化视图查询命中率，淘汰低价值 MV"
  - 存储控制: "异步 MV 的数据量应远小于基表，否则失去意义"

-- 查看物化视图刷新状态与查询命中率
SELECT * FROM information_schema.materialized_views 
WHERE TABLE_NAME = 'mv_customer_monthly';

-- 手动强制刷新
REFRESH MATERIALIZED VIEW mv_customer_monthly FORCE;  -- 强制全量刷新
REFRESH MATERIALIZED VIEW mv_customer_monthly;        -- 增量刷新

-- 清理物化视图
DROP MATERIALIZED VIEW mv_customer_monthly;

# 1. Stream Load：通过 curl 直接推送本地 JSON/CSV
curl -X PUT \
  --location-trusted \
  -u root: \
  -H "label:stream_load_20240801_001" \
  -H "column_separator:," \
  -H "format:csv" \
  -H "columns:id,name,amount" \
  -T /data/orders.csv \
  http://fe_host:8030/api/db1/orders/_stream_load

# 响应示例（JSON）：
# {
#   "TxnId": 12345,
#   "Label": "stream_load_20240801_001",
#   "Status": "Success",
#   "NumberLoadedRows": 1000000,
#   "NumberFilteredRows": 0
# }

-- 2. Broker Load：从 OSS/S3 批量导入
LOAD LABEL db1.batch_orders_20240801 (
    DATA INFILE("oss://my-bucket/orders/dt=2024-08-01/*")
    INTO TABLE orders
    FORMAT AS "parquet"
    (order_id, user_id, amount, dt, status)
)
WITH BROKER 'oss_broker' (
    "fs.oss.accessKeyId" = "xxx",
    "fs.oss.accessKeySecret" = "yyy",
    "fs.oss.endpoint" = "oss-cn-hangzhou.aliyuncs.com"
)
PROPERTIES (
    "timeout" = "3600",
    "max_filter_ratio" = "0.1"
);

-- 查询导入任务状态
SHOW LOAD FROM db1 WHERE LABEL LIKE 'batch_orders_20240801%';

-- 3. Routine Load：实时消费 Kafka
CREATE ROUTINE LOAD db1.kafka_orders_routine ON orders
COLUMNS TERMINATED BY ',',
COLUMNS (order_id, user_id, amount, dt, status)
PROPERTIES (
    "desired_concurrent_number" = "3",
    "max_batch_interval" = "5",
    "max_batch_rows" = "200000",
    "max_batch_size" = "52428800"
)
FROM KAFKA (
    "kafka_broker_list" = "kafka-1:9092,kafka-2:9092",
    "kafka_topic" = "orders_topic",
    "kafka_partitions" = "0,1,2,3",
    "kafka_offsets" = "OFFSET_BEGINNING"
);

-- 查看消费进度
SHOW ROUTINE LOAD FOR db1.kafka_orders_routine;
SHOW ROUTINE LOAD TASK WHERE JobName = 'kafka_orders_routine';

# 4. Spark Load：Spark + StarRocks Connector 导入
# spark-submit 参数示例
spark-submit \
  --class com.starrocks.StarRocksSparkLoad \
  --conf spark.starrocks.fe.host=fe_host \
  --conf spark.starrocks.fe.port=8030 \
  --conf spark.starrocks.db=db1 \
  --conf spark.starrocks.table=orders \
  --conf spark.starrocks.columns="order_id,user_id,amount,dt,status" \
  --conf spark.starrocks.broker="hdfs_broker" \
  /path/to/spark-load.jar

-- 5. INSERT INTO：从本地表或外表导入
INSERT INTO target_orders 
SELECT * FROM staging_orders 
WHERE dt = '2024-08-01';

-- INSERT OVERWRITE 覆写分区
INSERT OVERWRITE target_orders PARTITION(dt='2024-08-01')
SELECT order_id, user_id, amount, status FROM staging_orders WHERE dt = '2024-08-01';

-- 创建 JDBC Resource（复用连接池）
CREATE EXTERNAL RESOURCE jdbc_mysql
PROPERTIES (
    "type" = "jdbc",
    "user" = "readonly",
    "password" = "******",
    "jdbc_url" = "jdbc:mysql://mysql-host:3306/production?useSSL=false",
    "driver_class" = "com.mysql.jdbc.Driver",
    "driver_url" = "http://fe_host:8080/mysql-connector-java-5.1.47.jar"
);

-- 创建外表映射 MySQL 表
CREATE EXTERNAL TABLE ext_users (
    user_id BIGINT,
    user_name VARCHAR(100),
    email VARCHAR(200),
    created_at DATETIME
)
ENGINE = JDBC
PROPERTIES (
    "resource" = "jdbc_mysql",
    "table" = "users",
    "database" = "production"
);

-- 直接查询（谓词下推到 MySQL）
SELECT user_id, user_name FROM ext_users WHERE created_at > '2024-01-01';
-- 实际执行：WHERE 条件推送到 MySQL，只返回匹配行

-- 创建 Hive Catalog
CREATE EXTERNAL CATALOG hive_catalog
PROPERTIES (
    "type" = "hive",
    "hive.metastore.uris" = "thrift://hive-metastore:9083"
);

-- 创建 Iceberg REST Catalog
CREATE EXTERNAL CATALOG iceberg_catalog
PROPERTIES (
    "type" = "iceberg",
    "iceberg.catalog.type" = "REST",
    "iceberg.catalog.uri" = "http://iceberg-rest:8181",
    "iceberg.catalog.warehouse" = "s3://data-lake/warehouse"
);

-- 切换 Catalog 查询（无需导入）
SET CATALOG iceberg_catalog;
USE sales_db;
SELECT 
    product_id,
    SUM(revenue) AS total_revenue,
    COUNT(*) AS transaction_count
FROM sales_transactions
WHERE dt >= '2024-01-01'
GROUP BY product_id
ORDER BY total_revenue DESC
LIMIT 100;

-- 查询 Iceberg 表（带快照时间旅行）
SELECT * FROM iceberg_catalog.sales.orders FOR VERSION AS OF 123456789;

-- 利用 Iceberg 的隐藏分区直接过滤
SELECT * FROM iceberg_catalog.sales.orders 
WHERE dt >= '2024-08-01' AND event_hour = 14;

-- 联合查询：StarRocks 本地表 JOIN Iceberg 数据湖表
SELECT 
    l.order_id,
    l.amount,
    c.customer_name,
    c.region
FROM local_db.orders l
JOIN iceberg_catalog.crm.customers c 
    ON l.customer_id = c.customer_id
WHERE l.dt = '2024-08-01';

# be.conf — 数据湖查询缓存配置
datacache_enable = true
datacache_mem_size = 2147483648        # 内存缓存 2GB
datacache_disk_size = 107374182400     # 磁盘缓存 100GB
datacache_disk_path = /data/cache;/data2/cache
datacache_block_size = 1048576         # 1MB 块大小

# FE 级缓存配置
# external_table_meta_cache_ttl_seconds = 3600
# enable_experimental_profile = true

-- 查看数据缓存命中率
SELECT 
    BE_ID,
    TABLET_ID,
    DISK_NAME,
    CACHE_HIT_COUNT,
    CACHE_MISS_COUNT,
    CACHE_HIT_COUNT / (CACHE_HIT_COUNT + CACHE_MISS_COUNT) AS hit_ratio
FROM information_schema.datacache_metrics;

-- 设置查询超时与并行度
SET query_timeout = 300;
SET enable_pipeline_engine = true;
SET pipeline_dop = 0;  -- 0 表示自适应

-- 资源组隔离（大查询限流，小查询优先）
CREATE RESOURCE GROUP bi_report 
TO (
    user='report_reader', 
    query_type in ('SELECT')
)
WITH (
    'cpu_core_limit' = '8',
    'mem_limit' = '40%',
    'concurrency_limit' = '20',
    'max_cpu_cores' = '4'
);

-- 查看当前查询的resource group
SELECT * FROM information_schema.current_queries;

# be.conf — 内存管理
mem_limit = 80%                         # BE 进程最大内存
query_mem_limit = 2147483648            # 单个查询 2GB 上限
enable_spill = true                     # 内存不足时 spill 到磁盘
spill_local_storage_dir = /data/spill
spill_max_write_buffer_size = 268435456 # 256MB spill buffer

# 大聚合操作启用磁盘 spill
enable_agg_spill = true
enable_sort_spill = true
enable_hash_join_spill = true

# be.conf — Compaction 调优
max_compaction_concurrency = 10
cumulative_compaction_num_threads_per_disk = 4
base_compaction_num_threads_per_disk = 2
cumulative_compaction_check_interval_seconds = 5
base_compaction_check_interval_seconds = 60
min_cumulative_compaction_num_singleton_deltas = 5
max_cumulative_compaction_num_singleton_deltas = 1000

# 3.1+ 支持自动 compaction 调度
enable_compaction_scheduler = true

-- 查看 Tablet 的 compaction 状态
SHOW TABLET STATUS FROM db1.orders;

-- 手动触发 compaction（数据导入后查询慢时）
ALTER TABLE orders COMPACT;

-- 查看 compaction 分数（越高越需要 compaction）
SELECT 
    TABLET_ID, 
    COMPACTION_SCORE, 
    CUMULATIVE_COMPACTION_SCORE, 
    BASE_COMPACTION_SCORE 
FROM information_schema.be_tablets 
WHERE TABLE_NAME = 'orders'
ORDER BY COMPACTION_SCORE DESC
LIMIT 20;

-- 开启审计日志与查询 profile
SET enable_profile = true;

-- 查询最近慢查询（FE 内置表）
SELECT 
    QueryId,
    StartTime,
    QueryTimeMs,
    Sql
FROM information_schema.fe_slow_queries
WHERE QueryTimeMs > 5000
ORDER BY QueryTimeMs DESC
LIMIT 20;

-- 分析具体查询的 profile
-- 在 Web UI: http://fe_host:8030/query 查看可视化 profile
-- 或通过 API 获取 JSON profile

# 扩容：在新节点部署 BE 后添加到集群
mysql -h fe_host -P 9030 -u root -e "ALTER SYSTEM ADD BACKEND 'new_be_ip:9050';"

# 缩容（优雅下线，数据迁移完成后再移除）
mysql -h fe_host -P 9030 -u root -e "ALTER SYSTEM DECOMMISSION BACKEND 'old_be_ip:9050';"

# 强制删除（风险高，仅用于故障节点）
mysql -h fe_host -P 9030 -u root -e "ALTER SYSTEM DROP BACKEND 'dead_be_ip:9050';"

# 查看 Tablet 迁移进度
SHOW PROC '/statistic';