1. GaussDB基础命令速查手册作为华为开源的分布式关系型数据库GaussDB在金融、电信等行业的核心系统中广泛应用。掌握其基础命令是DBA和开发人员的必备技能。以下是我在银行系统迁移项目中总结的高频操作清单1.1 连接与退出# 使用gsql客户端连接端口默认5432 gsql -d 数据库名 -U 用户名 -W 密码 -h 主机IP -p 端口 # 连接后查看版本 SELECT version(); # 退出交互界面 \q注意生产环境建议通过SSL连接需额外添加-sslcompression 0参数1.2 数据库对象管理-- 查看所有数据库 \l 或 SELECT datname FROM pg_database; -- 切换数据库 \c 数据库名 -- 查看当前库所有表 \dt -- 查看表结构 \d 表名2. 运维监控关键命令2.1 系统状态检查-- 查看活跃连接数 SELECT count(*) FROM pg_stat_activity; -- 检查锁等待 SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid;2.2 性能分析-- 查看耗时TOP10 SQL SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10; -- 重置统计信息需先加载pg_stat_statements扩展 SELECT pg_stat_statements_reset();3. 数据操作实战命令3.1 批量导入导出# 导出单表数据-a仅数据 -s仅结构 gs_dump -t 表名 -f 导出文件.sql 数据库名 # 并行导入-j指定线程数 gs_restore -j 4 -d 目标库 备份文件.dump3.2 事务控制-- 开启事务 BEGIN; -- 或 START TRANSACTION; -- 设置隔离级别GaussDB特有 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 提交/回滚 COMMIT; ROLLBACK;4. 高级功能命令4.1 列存表操作-- 创建列存表GaussDB特色 CREATE TABLE sales ( id INT, date DATE, amount DECIMAL(10,2) ) WITH (ORIENTATION COLUMN); -- 查看表存储类型 SELECT relname, reloptions FROM pg_class WHERE relname sales;4.2 分布式特性-- 查看数据分布适用于分布式版本 SELECT gp_segment_id, count(*) FROM 表名 GROUP BY gp_segment_id; -- 手动重分布 VACUUM FULL 表名;5. 常见问题排查5.1 连接失败处理# 检查服务状态 gs_ctl status -D 数据目录 # 查看日志默认在数据目录/pg_log tail -100f postgresql-2023-08-01_000000.log5.2 性能问题定位-- 查看索引使用情况 SELECT indexrelid::regclass AS index_name, relid::regclass AS table_name, idx_scan FROM pg_stat_user_indexes; -- 重建索引 REINDEX INDEX 索引名;实战经验在电信级系统中我们曾通过pg_stat_statements发现一个未使用索引的查询添加索引后使查询耗时从1200ms降至15ms