StarRocks命令速查
MySQL 协议连接、FE/BE/CN 节点运维、库表与分区设计、Stream/Routine Load 导入、查询函数、物化视图与索引、备份恢复与报错排查等 195 条命令
共 195 条命令 · 13 类场景 · 点击右侧图标可复制
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
mysql -h 10.0.0.11 -P 9030 -uroot -p | StarRocks 兼容 MySQL 协议,用 mysql 客户端连接 FE 的 9030 端口 | |
mysql -h 10.0.0.11 -P 9030 -uroot -p -D db | 连接时指定默认库 | |
mysql -h fe_host -P 9030 -uroot -p -e "SHOW FRONTENDS;" | 执行单条 SQL 后退出,脚本巡检常用 | |
mysql --default-character-set=utf8mb4 -h fe -P 9030 -uapp -p db < init.sql | 执行 SQL 文件并指定字符集,避免中文乱码 | |
SHOW FRONTENDS; | 查看 FE 列表、是否 Master 与存活状态 | |
SHOW BACKENDS; | 查看 BE 节点、磁盘使用率与 tablet 数量 | |
SHOW COMPUTE NODES; | 存算分离架构下查看 CN 节点(3.0+) | |
SHOW BROKER; | 查看 Broker 进程,Broker Load 与备份依赖它 | |
SELECT VERSION(); | 查看 StarRocks 版本 | |
SELECT DATABASE(), CURRENT_USER(), NOW(); | 确认当前库、用户与时间 | |
访问 http://fe_host:8030 打开 FE Web UI | 查看 Query Profile、导入任务与会话 | |
curl http://be_host:8040/api/health | 检查 BE 健康状态,负载均衡探活常用 | |
SET CATALOG hive_catalog; | 切换 Catalog,之后可直接查询外部数据源 | |
SHOW CATALOGS; | 查看已注册的 Catalog 列表 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
ALTER SYSTEM ADD BACKEND "10.0.0.12:9050"; | 扩容 BE,9050 为 BE 心跳端口 | |
ALTER SYSTEM ADD COMPUTE NODE "10.0.0.13:9050"; | 存算分离架构下新增 CN 节点 | |
ALTER SYSTEM ADD FOLLOWER "10.0.0.14:9010"; | 增加 FE Follower,提升元数据高可用 | |
ALTER SYSTEM ADD OBSERVER "10.0.0.15:9010"; | 增加只读 FE,分担元数据读压力 | |
ALTER SYSTEM DECOMMISSION BACKEND "10.0.0.12:9050"; | 优雅下线 BE,先把 tablet 迁移走 | |
ALTER SYSTEM DROP BACKEND "10.0.0.12:9050"; | 强制下线 BE,可能丢副本(谨慎) | |
ALTER SYSTEM DROP FOLLOWER "10.0.0.14:9010"; | 移除 FE 节点 | |
SHOW PROC '/backends'; | BE 明细:磁盘、tablet 数、是否 decommission | |
SHOW PROC '/frontends'; | FE 明细:角色、版本与元数据同步状态 | |
ADMIN SHOW FRONTEND CONFIG LIKE "%max_running_txn%"; | 查看 FE 配置项当前值 | |
ADMIN SET FRONTEND CONFIG ("max_running_txn_num_per_db" = "2000"); | 动态修改 FE 参数,无需重启 | |
ADMIN SET BACKEND CONFIG ("max_compaction_concurrency" = "8"); | 动态调整 BE 参数,需评估机型与负载 | |
SHOW BACKENDS; | 确认节点 Alive、LastStartTime 与 DiskState | |
SELECT * FROM information_schema.be_tablets LIMIT 20; | tablet 级明细:版本数、行数与大小 | |
SELECT * FROM information_schema.be_compactions LIMIT 20; | 查看 BE 正在进行的 compaction 任务 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
CREATE DATABASE IF NOT EXISTS db; | 建库,库级默认副本数可通过 PROPERTIES 指定 | |
SHOW DATABASES; | 列出所有库 | |
USE db; | 切换当前库 | |
CREATE TABLE db.orders (id BIGINT, uid BIGINT, amount DECIMAL(18,2), ts DATETIME) PRIMARY KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 16 PROPERTIES ("replication_num" = "3", "enable_persistent_index" = "true"); | 主键模型建表模板:支持实时更新、持久化索引 | |
CREATE TABLE db.logs (id BIGINT, msg VARCHAR(500), ts DATETIME) DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 8; | 明细模型,不聚合不去重,适合日志与埋点 | |
CREATE TABLE db.stats (k1 VARCHAR(32), v BIGINT SUM) AGGREGATE KEY(k1) DISTRIBUTED BY HASH(k1) BUCKETS 4; | 聚合模型,导入时按 Key 预聚合 | |
CREATE TABLE db.users (id BIGINT, name VARCHAR(64), ts DATETIME) UNIQUE KEY(id) DISTRIBUTED BY HASH(id); | 唯一模型(老版本写法,新表建议用主键模型) | |
SHOW TABLES FROM db; | 列出库内所有表 | |
SHOW CREATE TABLE db.orders; | 查看建表语句,迁移与比对必用 | |
DESC db.orders; | 查看字段、类型与 Key 类型 | |
DESC db.orders ALL; | 连同同步物化视图(rollup)一起展示 | |
ALTER TABLE db.orders ADD COLUMN channel VARCHAR(64) DEFAULT ""; | 加列,主键模型支持轻量级 Schema Change | |
ALTER TABLE db.orders DROP COLUMN channel; | 删除列 | |
ALTER TABLE db.orders MODIFY COLUMN channel VARCHAR(128); | 修改列类型或长度 | |
ALTER TABLE db.orders SET ("replication_num" = "2"); | 调整表级副本数 | |
ALTER TABLE db.orders SET ("bloom_filter_columns" = "uid,channel"); | 为高基数列开启 Bloom Filter 索引 | |
ALTER TABLE db.orders RENAME orders_new; | 重命名表 | |
TRUNCATE TABLE db.orders; | 清空表数据,保留表结构 | |
DROP TABLE IF EXISTS db.orders; | 删除表 | |
CREATE EXTERNAL CATALOG hive_catalog PROPERTIES ('type' = 'hive', 'hive.metastore.uris' = 'thrift://10.0.0.20:9083'); | 接入 Hive Metastore,做数据湖联邦查询 | |
REFRESH EXTERNAL TABLE hive_catalog.db.tbl; | 刷新外部表元数据缓存 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
PARTITION BY date_trunc('day', ts) | 表达式分区:按天自动建分区(3.x 推荐写法) | |
PARTITION BY RANGE(ts) (PARTITION p202601 VALUES [("2026-01-01"), ("2026-02-01"))) | 范围分区,需自行维护分区边界 | |
"dynamic_partition.enable" = "true", "dynamic_partition.time_unit" = "DAY", "dynamic_partition.start" = "-30", "dynamic_partition.end" = "3", "dynamic_partition.prefix" = "p" | 动态分区属性:自动建未来分区、删过期分区 | |
ALTER TABLE db.orders ADD PARTITION p202612 VALUES [("2026-12-01"), ("2027-01-01")); | 手动补建分区,导入前分区必须存在 | |
ALTER TABLE db.orders DROP PARTITION p202501; | 删除分区,比 DELETE 高效且不产生大量版本 | |
ALTER TABLE db.orders SET ("dynamic_partition.enable" = "false"); | 关闭动态分区,改为手工管理 | |
SHOW PARTITIONS FROM db.orders; | 查看分区列表、数据量与副本状态 | |
SHOW DATA FROM db.orders; | 查看表数据量、副本数与占用空间 | |
SHOW TABLETS FROM db.orders; | 查看 tablet 分布、版本数与行数 | |
SHOW TABLET 10001; | 查看单个 tablet 的副本与版本明细 | |
SHOW CREATE TABLE db.orders; | 确认分区分桶与动态分区配置是否生效 | |
分桶数建议为 BE 数量的整数倍,单桶 1~10GB | 桶过少并行不足,桶过多增加元数据与 compaction 压力 | |
3.x 可省略 BUCKETS,由系统按数据量自动分桶 | 新表建议交给自动分桶,避免拍脑袋定桶数 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
curl --location-trusted -u root: -H "label:orders_20261102_01" -H "column_separator:," -H "Expect:100-continue" -T orders.csv http://be_host:8040/api/db/orders/_stream_load | Stream Load 导入 CSV,直接请求 BE 的 8040 端口 | |
curl --location-trusted -u root: -H "format:json" -H "strip_outer_array:true" -T data.json http://be_host:8040/api/db/orders/_stream_load | 导入 JSON 数组,日志采集常用 | |
curl --location-trusted -u root: -H "format:json" -H "read_json_by_line:true" -T data.json http://be_host:8040/api/db/orders/_stream_load | 逐行 JSON 导入,兼容 Kafka 落地文件 | |
curl --location-trusted -u root: -H "partial_update:true" -H "columns:id,amount" -T d.json http://be_host:8040/api/db/orders/_stream_load | 主键模型部分列更新,只传变化的字段 | |
curl --location-trusted -u root: -H "strict_mode:true" -H "max_filter_ratio:0.01" -T d.csv http://be_host:8040/api/db/orders/_stream_load | 严格模式 + 允许 1% 脏数据,避免整批失败 | |
INSERT INTO db.orders VALUES (1, 1001, 99.50, "2026-11-02 10:00:00"); | 少量写入,仅适合调试 | |
INSERT INTO db.orders SELECT * FROM db.orders_stage WHERE ts >= "2026-11-01"; | 表间导入,内表之间效率最高 | |
INSERT OVERWRITE TABLE db.orders PARTITION(p202611) SELECT * FROM db.orders_tmp; | 分区级覆盖写,原子替换数据 | |
LOAD LABEL db.label_001 (DATA INFILE("hdfs://ns/data/orders/*") INTO TABLE db.orders COLUMNS TERMINATED BY ",") WITH BROKER "hdfs_broker" PROPERTIES ("timeout" = "3600"); | Broker Load 从 HDFS/对象存储批量导入 | |
CREATE ROUTINE LOAD db.job_orders ON orders COLUMNS(id, uid, amount, ts) PROPERTIES ("desired_concurrent_number" = "3", "max_batch_interval" = "10", "format" = "json") FROM KAFKA ("kafka_broker_list" = "10.0.0.30:9092", "kafka_topic" = "orders", "property.group.id" = "sr_orders"); | Routine Load 持续消费 Kafka,实时接入首选 | |
SHOW ROUTINE LOAD FOR db.job_orders; | 查看消费进度、延迟与暂停原因 | |
PAUSE ROUTINE LOAD FOR db.job_orders; | 暂停消费,维护窗口使用 | |
RESUME ROUTINE LOAD FOR db.job_orders; | 恢复消费 | |
STOP ROUTINE LOAD FOR db.job_orders; | 停止并删除作业 | |
SHOW LOAD FROM db ORDER BY CreateTime DESC LIMIT 20; | 查看导入任务历史与失败原因 | |
SHOW LOAD WHERE LABEL = "orders_20261102_01"; | 按 label 精确定位导入任务 | |
CANCEL LOAD FROM db WHERE LABEL = "orders_20261102_01"; | 取消正在运行的导入 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
DELETE FROM db.orders WHERE id = 1; | 主键模型按条件删除 | |
DELETE FROM db.orders WHERE ts < "2026-01-01"; | 按时间清理历史数据,优先考虑 DROP PARTITION | |
UPDATE db.orders SET amount = 88.00 WHERE id = 1; | 主键模型支持 UPDATE | |
INSERT INTO db.orders VALUES (1, 1001, 88.00, "2026-11-02 10:00:00"); | 同主键写入即 upsert,最高效的更新方式 | |
BEGIN; INSERT INTO db.orders VALUES (...); COMMIT; | 显式事务,保证多条写入的原子性(单表) | |
ROLLBACK; | 回滚当前显式事务 | |
SHOW LOAD WHERE LABEL = "orders_20261102_01"; | 确认写入事务状态与是否可见 | |
label 相同视为同一事务,天然幂等 | 重试导入复用 label 可避免重复数据 | |
主键模型按主键去重,聚合模型按 Key 聚合,明细模型全部保留 | 写入行为由模型决定,选型前先确认业务语义 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
SELECT * FROM db.orders WHERE ts >= "2026-11-01" AND ts < "2026-11-02" LIMIT 100; | 分区列用常量区间,保证分区裁剪生效 | |
SELECT uid, COUNT(*) AS cnt, SUM(amount) AS total FROM db.orders GROUP BY uid ORDER BY total DESC LIMIT 10; | 常规分组聚合 | |
SELECT date_trunc("hour", ts) AS h, COUNT(*) FROM db.orders GROUP BY h ORDER BY h; | 按小时聚合趋势 | |
SELECT uid, amount, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY amount DESC) AS rn FROM db.orders; | 窗口函数排名,取每组 TopN | |
SELECT uid, SUM(amount) OVER (PARTITION BY uid ORDER BY ts) AS acc FROM db.orders; | 窗口累计指标 | |
SELECT COUNT(DISTINCT uid), approx_count_distinct(uid) FROM db.orders; | 精确去重与 HyperLogLog 近似去重 | |
SELECT bitmap_union_count(to_bitmap(uid)) FROM db.orders; | Bitmap 精确去重,UV 统计首选 | |
SELECT hll_union(hll_hash(uid)) FROM db.orders; | HLL 近似去重,亿级 UV 更快 | |
SELECT percentile_approx(amount, 0.95) FROM db.orders; | 近似分位数,耗时与金额分析常用 | |
SELECT uid, array_agg(amount) FROM db.orders GROUP BY uid; | 聚合为数组 | |
SELECT uid, group_concat(DISTINCT channel) FROM db.orders GROUP BY uid; | 分组拼接字符串 | |
SELECT * FROM db.orders, UNNEST(split(tags, ",")) AS tag; | 数组/字符串展开为多行 | |
SELECT * FROM db.orders o JOIN [broadcast] db.users u ON o.uid = u.id; | Broadcast Hint 让小表广播,避免大表 Shuffle | |
SELECT * FROM db.orders o JOIN [shuffle] db.users u ON o.uid = u.id; | Shuffle Hint 强制按连接键重分布 | |
WITH t AS (SELECT uid, SUM(amount) s FROM db.orders GROUP BY uid) SELECT * FROM t WHERE s > 1000; | CTE 拆分复杂查询 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
CREATE MATERIALIZED VIEW mv_k1 AS SELECT k1, SUM(v) AS sv FROM db.stats GROUP BY k1; | 同步物化视图(rollup),查询自动改写命中 | |
CREATE MATERIALIZED VIEW mv_daily REFRESH ASYNC EVERY(INTERVAL 1 HOUR) DISTRIBUTED BY HASH(k1) AS SELECT date_trunc('day', ts) d, k1, SUM(amount) s FROM db.orders GROUP BY d, k1; | 异步物化视图:定时刷新,支持多表与复杂查询 | |
SHOW MATERIALIZED VIEWS FROM db; | 查看异步物化视图及刷新状态 | |
SHOW MATERIALIZED VIEWS FROM db LIKE 'mv_%'; | 按名称筛选物化视图 | |
REFRESH MATERIALIZED VIEW mv_daily; | 手动触发异步物化视图刷新 | |
ALTER MATERIALIZED VIEW mv_daily ACTIVE; | 启用/禁用物化视图(ACTIVE / INACTIVE) | |
DROP MATERIALIZED VIEW mv_daily; | 删除异步物化视图 | |
SHOW ALTER MATERIALIZED VIEW FROM db; | 查看同步物化视图的构建任务状态 | |
DESC db.stats ALL; | 查看表上已建的同步物化视图 | |
CREATE INDEX idx_city ON db.orders (channel) USING BITMAP; | Bitmap 索引,适合低基数列过滤 | |
CREATE INDEX idx_msg ON db.logs (msg) USING INVERTED; | 倒排索引,加速等值与全文检索(3.x) | |
CREATE INDEX idx_msg_ngram ON db.logs (msg) USING INVERTED PROPERTIES("parser" = "english"); | 带分词的倒排索引 | |
SHOW INDEX FROM db.orders; | 查看表上的索引 | |
DROP INDEX idx_city ON db.orders; | 删除索引 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
SHOW DATA; | 查看全库数据量、副本数与占用空间 | |
SHOW PROC '/backends'; | BE 巡检:磁盘使用率、tablet 数与 compaction 分数 | |
SELECT * FROM information_schema.tables WHERE table_schema = "db"; | 以系统表方式查看表信息 | |
SELECT * FROM information_schema.be_tablets LIMIT 20; | tablet 版本数巡检,版本过多要查 compaction | |
SELECT * FROM information_schema.be_compactions LIMIT 20; | 查看正在执行的 compaction 任务 | |
SHOW TABLETS FROM db.orders; | 按表查看 tablet 与版本情况 | |
SHOW PARTITIONS FROM db.orders; | 分区级数据量与副本健康 | |
SHOW LOAD FROM db ORDER BY CreateTime DESC LIMIT 20; | 导入任务巡检,失败看 ErrorMsg 与 ErrorURL | |
SHOW ROUTINE LOAD; | 查看所有例行导入作业状态 | |
SHOW PROCESSLIST; | 查看当前连接与正在执行的语句 | |
KILL 12345; | 终止连接(SHOW PROCESSLIST 中 Id) | |
SELECT * FROM starrocks_audit_db__.starrocks_audit_tbl__ ORDER BY queryTime DESC LIMIT 20; | 审计日志(需开启 audit 插件) | |
curl http://be_host:8040/metrics | grep starrocks_be_query | BE 指标导出,对接 Prometheus 监控 | |
SHOW GRANTS; | 查看当前用户权限 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
EXPLAIN SELECT * FROM db.orders WHERE uid = 1001; | 查看执行计划,确认分区裁剪与扫描方式 | |
EXPLAIN COSTS SELECT * FROM db.orders WHERE uid = 1001; | 输出代价估算,对比不同写法的选择 | |
EXPLAIN VERBOSE SELECT o.uid, u.name FROM db.orders o JOIN db.users u ON o.uid = u.id; | 详细计划:Join 顺序、分布方式与数据量估算 | |
SET enable_profile = true; | 为后续查询开启 Profile,FE Web UI 中查看 | |
SET exec_mem_limit = 8589934592; | 提高单查询内存上限(字节) | |
SET query_timeout = 300; | 延长查询超时,避免大查询被中断 | |
SET pipeline_dop = 8; | 设置查询并行度,匹配 BE 核数(2.5+) | |
SET broadcast_row_limit = 15000000; | 调整广播 Join 的行数阈值 | |
SET enable_spill = true; | 开启中间结果落盘,缓解内存不足(3.0+) | |
ANALYZE TABLE db.orders; | 收集统计信息,CBO 选错计划时先做这一步 | |
SHOW TABLETS FROM db.orders; | 观察各 tablet 的 DataSize 分布,桶间差异大时最慢的桶会拖住查询 | |
分区裁剪 + 分桶列 Join | StarRocks 最优路径:先裁分区,再让 Join 走 colocate/bucket shuffle | |
避免 SELECT *,只取必要列 | 列存下减少列读取能显著降低 IO 与内存 | |
控制导入频率,避免 tablet 版本堆积 | 版本数上涨会拖慢查询,必要时调大 BE compaction 并发 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
CREATE REPOSITORY repo_s3 WITH BROKER "broker" ON LOCATION "s3://bucket/backup" PROPERTIES ("aws.s3.access_key" = "AK", "aws.s3.secret_key" = "SK"); | 创建备份仓库(S3/HDFS 均通过 Broker 访问) | |
SHOW REPOSITORY; | 查看已创建的备份仓库 | |
BACKUP SNAPSHOT db.snap_20261102 TO repo_s3 PROPERTIES ("type" = "full"); | 发起全量备份快照 | |
SHOW BACKUP FROM db ORDER BY SnapshotName DESC LIMIT 10; | 查看备份进度与状态 | |
CANCEL BACKUP FROM db; | 取消正在执行的备份 | |
SHOW SNAPSHOT ON repo_s3 WHERE SNAPSHOT = "snap_20261102"; | 查看仓库中已有快照与时间戳 | |
RESTORE SNAPSHOT db.snap_20261102 FROM repo_s3 PROPERTIES ("backup_timestamp" = "2026-11-02-10-00-00", "replication_num" = "3"); | 从快照恢复,时间戳必须与 SHOW SNAPSHOT 一致 | |
SHOW RESTORE FROM db; | 查看恢复任务状态与失败原因 | |
CANCEL RESTORE FROM db; | 取消恢复任务 | |
DROP REPOSITORY repo_s3; | 删除备份仓库(不影响已有备份文件) | |
SELECT * FROM db.orders INTO OUTFILE "hdfs://ns/export/orders_" FORMAT AS CSV; | 查询结果导出到外部存储(3.0+) | |
INSERT INTO db.orders SELECT * FROM remote_catalog.db.orders; | 通过 Catalog 做跨集群数据迁移 | |
迁移前后用 COUNT(*) 与 SHOW DATA 校验 | 行数与数据量双重校验,避免漏表漏分区 | |
备份前避免大批量导入 | 导入期间备份可能失败或产生不一致快照,安排在低峰期 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
CREATE USER 'app' IDENTIFIED BY 'Str0ng!Pass'; | 创建用户,默认无权限 | |
CREATE USER 'reader' IDENTIFIED BY 'Pass1234' DEFAULT ROLE 'analyst'; | 创建用户并绑定默认角色 | |
GRANT SELECT ON ALL TABLES IN DATABASE db TO 'app'@'%'; | 授予库内全部表的只读权限 | |
GRANT SELECT ON TABLE db.orders TO 'app'@'%'; | 按表授予查询权限 | |
GRANT INSERT, UPDATE, DELETE ON TABLE db.orders TO 'etl'@'%'; | 授予写入与变更权限 | |
REVOKE DELETE ON TABLE db.orders FROM 'etl'@'%'; | 回收删除权限 | |
SHOW GRANTS FOR 'app'@'%'; | 查看指定用户权限 | |
CREATE ROLE analyst; | 创建角色统一管理权限 | |
GRANT SELECT ON ALL TABLES IN DATABASE db TO ROLE analyst; | 给角色授权 | |
GRANT analyst TO 'reader'@'%'; | 把角色授予用户 | |
SET ROLE analyst; | 切换当前会话角色 | |
SHOW ROLES; | 查看角色列表 | |
ALTER USER 'app' IDENTIFIED BY 'N3w!Pass'; | 修改用户密码 | |
DROP USER 'app'; | 删除用户 | |
CREATE RESOURCE GROUP rg_etl TO (user='etl') WITH ("cpu_weight" = "20", "mem_limit" = "40%", "concurrency_limit" = "10"); | 资源组限流:隔离 ETL 与在线查询的 CPU、内存与并发 | |
SHOW RESOURCE GROUPS ALL; | 查看所有资源组与绑定用户 | |
ALTER RESOURCE GROUP rg_etl WITH ("concurrency_limit" = "20"); | 动态调整资源组限制 | |
DROP RESOURCE GROUP rg_etl; | 删除资源组 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
Label [xxx] has already been used | label 重复:换新 label 或复用以实现幂等 | |
too many versions / version count 持续增长 | compaction 落后:降低导入频率、提高 BE compaction 并发或扩容 | |
Failed to commit transaction / publish version failed | 副本写入失败:查 SHOW TABLETS 与 BE 日志,确认磁盘与副本状态 | |
Memory limit exceeded | 内存超限:调大 exec_mem_limit、降低并行度或开启 enable_spill | |
no queryable replicas / replica is not healthy | 副本不可用:SHOW TABLETS 定位异常 tablet,必要时等待副本修复 | |
BE 磁盘使用率超过 high water mark(默认 85%) | 写入被拒绝:清理历史分区、扩容或迁移 tablet | |
Stream Load 返回 307 Temporary Redirect | 请求打到了 FE:改为直接请求 BE 的 8040 端口 | |
Access denied for user | 账号密码或来源 IP 不匹配,检查用户与授权 | |
Unknown database / Unknown table | 库表名大小写、当前 Catalog 或库上下文不对 | |
导入报 partition does not exist | 分区未创建:补 ADD PARTITION 或开启动态分区/表达式分区 | |
ROUTINE LOAD 状态变为 PAUSED | 消费出错或被限流:看 ReasonOfStateChanged 与 ErrorLogUrls | |
SHOW FRONTENDS 中 Alive 为 false | 检查 9010 端口、时钟同步与 fe.log | |
SHOW BACKENDS 中节点 Alive=false | BE 进程或心跳异常,查 be.out 与 9050 连通性 | |
查询突然变慢但语句未变 | 多为统计信息过期或数据分布变化:ANALYZE TABLE 后重新 EXPLAIN | |
SHOW PROCESSLIST 发现大量相同查询 | 应用重试或缓存击穿,先限流再优化查询 | |
物化视图不命中 | 查 SHOW MATERIALIZED VIEWS 状态,确认刷新成功且查询可改写 | |
建表报 replication_num 超过可用节点数 | 副本数不能大于 BE 数量,先扩容或降低副本 |
运维常用组合
SHOW FRONTENDS → SHOW BACKENDS → SHOW PROC "/backends" 看磁盘 → SHOW TABLETS 看版本数CREATE DATABASE → CREATE TABLE(主键/明细/聚合 + 分区 + 分桶) → SHOW CREATE TABLE → SHOW PARTITIONSStream Load 到 BE:8040 → SHOW LOAD 看状态 → 失败看 ErrorURL → SELECT COUNT(*) 校验CREATE ROUTINE LOAD → SHOW ROUTINE LOAD 看延迟 → PAUSE/RESUME 维护 → STOP 下线SET enable_profile=true → 执行查询 → FE Web UI 看 Profile → EXPLAIN 调整分区分桶与 JoinCREATE REPOSITORY → BACKUP SNAPSHOT → SHOW SNAPSHOT → RESTORE SNAPSHOT → COUNT(*) 校验常见问题排查
1. 模型先选对
实时更新用主键模型,明细日志用明细模型,固定报表指标用聚合模型;模型选错后续优化代价很高
2. 分区 + 分桶是地基
按时间做表达式分区、按高基数列分桶;新表可交给自动分桶,单桶控制在 1~10GB
3. 导入别用 INSERT
批量导入走 Stream Load / Broker Load / Routine Load,INSERT 只适合调试,量大会拖垮事务与 compaction
4. label 就是幂等键
重试导入复用同一 label,StarRocks 会视为同一事务,避免重复写入
5. 主键模型注意写放大
频繁全列更新会产生大量删除标记,尽量用部分列更新并控制导入批次
6. 统计信息要新鲜
CBO 依赖统计信息,大批量导入或数据倾斜变化后执行 ANALYZE TABLE 再看执行计划
相关工具
Linux命令速查
文件、文本、权限、进程、网络、磁盘、systemd与防火墙等 Linux 常用命令速查手册
Docker命令速查
镜像、容器、网络、数据卷、Compose、Swarm、清理与排障等 Docker 常用命令速查手册
Nginx命令速查
服务管理、配置指令、反向代理、HTTPS、限流缓存、日志排查等 Nginx 常用命令速查手册
K8S命令速查
集群上下文、Pod调试、工作负载、Service与Ingress、存储、权限、排障等 kubectl 常用命令速查手册
Windows CMD命令速查
文件目录、网络诊断、进程服务、系统信息、用户权限、注册表、批处理等 Windows CMD 常用命令速查手册
systemctl服务管理速查
服务启停、开机自启、unit文件编写、日志查看、定时器、依赖分析与排障等 systemctl 命令速查手册
关于StarRocks命令速查
在线 StarRocks 命令速查手册,按连接与客户端、集群节点与运维命令、库表 DDL 与数据模型、分区分桶与数据生命周期、数据导入、数据变更与事务、查询与常用函数、物化视图与索引、系统表监控与巡检、性能优化与执行分析、备份恢复与迁移、用户权限与资源组、常见报错与排查等 13 类场景整理 180+ 条常用命令与 SQL,每条附中文说明与可直接复制的写法,支持关键词搜索、分类折叠和一键复制,打开网页即可查阅。涵盖 mysql 客户端连接 FE 9030 与 -e/-D 参数、FE Web UI 8030 与 BE 8040 /api/health 与 /metrics、SHOW FRONTENDS/BACKENDS/COMPUTE NODES/BROKER、ALTER SYSTEM ADD BACKEND/COMPUTE NODE/FOLLOWER/OBSERVER 与 DECOMMISSION/DROP、SHOW PROC 的 /backends 与 /frontends、ADMIN SHOW FRONTEND CONFIG 与 ADMIN SET FRONTEND CONFIG 与 ADMIN SET BACKEND CONFIG、information_schema 的 be_tablets 与 be_compactions、CREATE DATABASE 与 CREATE TABLE 的 PRIMARY KEY/DUPLICATE KEY/AGGREGATE KEY/UNIQUE KEY 与 DISTRIBUTED BY HASH BUCKETS 与 PROPERTIES 的 replication_num/enable_persistent_index/bloom_filter_columns、DESC ALL 与 SHOW CREATE TABLE 与 CREATE EXTERNAL CATALOG 与 REFRESH EXTERNAL TABLE、date_trunc 表达式分区与 RANGE 分区与动态分区 dynamic_partition 与 ADD/DROP PARTITION、SHOW PARTITIONS/SHOW TABLETS/SHOW TABLET/SHOW DATA/SHOW DATA FROM、Stream Load 的 label/column_separator/format:json/read_json_by_line/partial_update/strict_mode/max_filter_ratio 与 Broker Load 与 Routine Load 的 PAUSE/RESUME/STOP 与 SHOW LOAD/CANCEL LOAD、INSERT INTO/INSERT OVERWRITE 与 DELETE/UPDATE/BEGIN-COMMIT-ROLLBACK、date_trunc/approx_count_distinct/bitmap_union_count/hll_union/percentile_approx/array_agg/group_concat/UNNEST 与窗口函数与 [broadcast]/[shuffle] 提示、CREATE MATERIALIZED VIEW 的同步与 REFRESH ASYNC EVERY(INTERVAL 1 HOUR) 异步物化视图与 SHOW/REFRESH/ALTER ACTIVE/DROP MATERIALIZED VIEW、BITMAP 与 INVERTED 索引与 bloom_filter_columns、SHOW PROCESSLIST/KILL 与审计表 starrocks_audit_db__.starrocks_audit_tbl__、EXPLAIN/EXPLAIN COSTS/EXPLAIN VERBOSE 与 SET enable_profile/exec_mem_limit/query_timeout/pipeline_dop/broadcast_row_limit/enable_spill 与 ANALYZE TABLE、CREATE REPOSITORY 与 BACKUP SNAPSHOT 与 RESTORE SNAPSHOT 与 SHOW SNAPSHOT/SHOW BACKUP/SHOW RESTORE 与 SELECT INTO OUTFILE、CREATE USER 与 GRANT SELECT ON ALL TABLES IN DATABASE 与角色 ROLE 与 SET ROLE 与 CREATE RESOURCE GROUP 的 cpu_weight/mem_limit/concurrency_limit、以及 Label has already been used、too many versions、Failed to commit transaction、Memory limit exceeded、no queryable replicas、high water mark、Stream Load 307、partition does not exist、ROUTINE LOAD PAUSED 等报错排查。适用于 StarRocks 集群日常运维、实时数仓与极速报表、主键模型实时更新、分区分桶规划、Stream/Routine Load 数据接入、物化视图与索引优化、资源组隔离、备份恢复与迁移,以及大数据与 DBA 面试复习。
核心能力
- 涵盖 mysql 客户端连接 FE 9030 与 -e/-D 参数、FE Web UI 8030 与 BE 8040 /api/health 与 /metrics、SHOW FRONTENDS/BACKENDS/COMPUTE NODES/BROKER、ALTER SYSTEM ADD BACKEND/COMPUTE NODE/FOLLOWER/OBSERVER 与 DECOMMISSION/DROP、SHOW PROC 的 /backends 与 /frontends、ADMIN SHOW FRONTEND CONFIG 与 ADMIN SET FRONTEND CONFIG 与 ADMIN SET BACKEND CONFIG、information_schema 的 be_tablets 与 be_compactions、CREATE DATABASE 与 CREATE TABLE 的 PRIMARY KEY/DUPLICATE KEY/AGGREGATE KEY/UNIQUE KEY 与 DISTRIBUTED BY HASH BUCKETS 与 PROPERTIES 的 replication_num/enable_persistent_index/bloom_filter_columns、DESC ALL 与 SHOW CREATE TABLE 与 CREATE EXTERNAL CATALOG 与 REFRESH EXTERNAL TABLE、date_trunc 表达式分区与 RANGE 分区与动态分区 dynamic_partition 与 ADD/DROP PARTITION、SHOW PARTITIONS/SHOW TABLETS/SHOW TABLET/SHOW DATA/SHOW DATA FROM、Stream Load 的 label/column_separator/format:json/read_json_by_line/partial_update/strict_mode/max_filter_ratio 与 Broker Load 与 Routine Load 的 PAUSE/RESUME/STOP 与 SHOW LOAD/CANCEL LOAD、INSERT INTO/INSERT OVERWRITE 与 DELETE/UPDATE/BEGIN-COMMIT-ROLLBACK、date_trunc/approx_count_distinct/bitmap_union_count/hll_union/percentile_approx/array_agg/group_concat/UNNEST 与窗口函数与 [broadcast]/[shuffle] 提示、CREATE MATERIALIZED VIEW 的同步与 REFRESH ASYNC EVERY(INTERVAL 1 HOUR) 异步物化视图与 SHOW/REFRESH/ALTER ACTIVE/DROP MATERIALIZED VIEW、BITMAP 与 INVERTED 索引与 bloom_filter_columns、SHOW PROCESSLIST/KILL 与审计表 starrocks_audit_db__.starrocks_audit_tbl__、EXPLAIN/EXPLAIN COSTS/EXPLAIN VERBOSE 与 SET enable_profile/exec_mem_limit/query_timeout/pipeline_dop/broadcast_row_limit/enable_spill 与 ANALYZE TABLE、CREATE REPOSITORY 与 BACKUP SNAPSHOT 与 RESTORE SNAPSHOT 与 SHOW SNAPSHOT/SHOW BACKUP/SHOW RESTORE 与 SELECT INTO OUTFILE、CREATE USER 与 GRANT SELECT ON ALL TABLES IN DATABASE 与角色 ROLE 与 SET ROLE 与 CREATE RESOURCE GROUP 的 cpu_weight/mem_limit/concurrency_limit、以及 Label has already been used、too many versions、Failed to commit transaction、Memory limit exceeded、no queryable replicas、high water mark、Stream Load 307、partition does not exist、ROUTINE LOAD PAUSED 等报错排查
适用场景
适用于 StarRocks 集群日常运维、实时数仓与极速报表、主键模型实时更新、分区分桶规划、Stream/Routine Load 数据接入、物化视图与索引优化、资源组隔离、备份恢复与迁移,以及大数据与 DBA 面试复习。StarRocks命令速查全程在浏览器中完成,无需安装插件,可放心用于日常办公与排障。
常见问题
- StarRocks命令速查是免费的吗?需要注册或安装吗?
- StarRocks命令速查完全免费,无需注册、无需下载安装任何软件,用浏览器打开网页即可直接使用,也不限制使用次数。
- StarRocks命令速查支持哪些功能?
- 涵盖 mysql 客户端连接 FE 9030 与 -e/-D 参数、FE Web UI 8030 与 BE 8040 /api/health 与 /metrics、SHOW FRONTENDS/BACKENDS/COMPUTE NODES/BROKER、ALTER SYSTEM ADD BACKEND/COMPUTE NODE/FOLLOWER/OBSERVER 与 DECOMMISSION/DROP、SHOW PROC 的 /backends 与 /frontends、ADMIN SHOW FRONTEND CONFIG 与 ADMIN SET FRONTEND CONFIG 与 ADMIN SET BACKEND CONFIG、information_schema 的 be_tablets 与 be_compactions、CREATE DATABASE 与 CREATE TABLE 的 PRIMARY KEY/DUPLICATE KEY/AGGREGATE KEY/UNIQUE KEY 与 DISTRIBUTED BY HASH BUCKETS 与 PROPERTIES 的 replication_num/enable_persistent_index/bloom_filter_columns、DESC ALL 与 SHOW CREATE TABLE 与 CREATE EXTERNAL CATALOG 与 REFRESH EXTERNAL TABLE、date_trunc 表达式分区与 RANGE 分区与动态分区 dynamic_partition 与 ADD/DROP PARTITION、SHOW PARTITIONS/SHOW TABLETS/SHOW TABLET/SHOW DATA/SHOW DATA FROM、Stream Load 的 label/column_separator/format:json/read_json_by_line/partial_update/strict_mode/max_filter_ratio 与 Broker Load 与 Routine Load 的 PAUSE/RESUME/STOP 与 SHOW LOAD/CANCEL LOAD、INSERT INTO/INSERT OVERWRITE 与 DELETE/UPDATE/BEGIN-COMMIT-ROLLBACK、date_trunc/approx_count_distinct/bitmap_union_count/hll_union/percentile_approx/array_agg/group_concat/UNNEST 与窗口函数与 [broadcast]/[shuffle] 提示、CREATE MATERIALIZED VIEW 的同步与 REFRESH ASYNC EVERY(INTERVAL 1 HOUR) 异步物化视图与 SHOW/REFRESH/ALTER ACTIVE/DROP MATERIALIZED VIEW、BITMAP 与 INVERTED 索引与 bloom_filter_columns、SHOW PROCESSLIST/KILL 与审计表 starrocks_audit_db__.starrocks_audit_tbl__、EXPLAIN/EXPLAIN COSTS/EXPLAIN VERBOSE 与 SET enable_profile/exec_mem_limit/query_timeout/pipeline_dop/broadcast_row_limit/enable_spill 与 ANALYZE TABLE、CREATE REPOSITORY 与 BACKUP SNAPSHOT 与 RESTORE SNAPSHOT 与 SHOW SNAPSHOT/SHOW BACKUP/SHOW RESTORE 与 SELECT INTO OUTFILE、CREATE USER 与 GRANT SELECT ON ALL TABLES IN DATABASE 与角色 ROLE 与 SET ROLE 与 CREATE RESOURCE GROUP 的 cpu_weight/mem_limit/concurrency_limit、以及 Label has already been used、too many versions、Failed to commit transaction、Memory limit exceeded、no queryable replicas、high water mark、Stream Load 307、partition does not exist、ROUTINE LOAD PAUSED 等报错排查。
- StarRocks命令速查适合哪些场景使用?
- 适用于 StarRocks 集群日常运维、实时数仓与极速报表、主键模型实时更新、分区分桶规划、Stream/Routine Load 数据接入、物化视图与索引优化、资源组隔离、备份恢复与迁移,以及大数据与 DBA 面试复习。