DBA 参考手册
使用规则：以下命令是稳定且广为人知的格式。不同版本和版本（许可证！）的行为有所不同——当某行提示“请根据您的版本验证”时，请查阅相应厂商文档以获取确切版本信息后再使用。切勿在生产环境中运行未经变更记录的诊断性数据修改语句。
1. 引擎快速对比
| 主题 | Oracle | PostgreSQL | SQL Server | Db2 LUW | MySQL / MariaDB (InnoDB) |
|---|
| 并发模型 | MVCC (undo)；读取从不阻塞写入 | MVCC（表内的行版本；VACUUM） | 默认使用锁机制；如果启用了 RCSI/快照隔离，则使用 MVCC | 锁机制；"当前提交"读取 (CUR_COMMIT) | MVCC (undo) + 可重复读（默认）中的 next-key/gap 锁 |
| 事务性 DDL | 否（DDL 前后隐式提交） | 是（但例外如 CREATE INDEX CONCURRENTLY, CREATE DATABASE, VACUUM） | 大部分 DDL 支持 | 大部分 DDL 支持（请按语句验证） | 否（隐式提交；MySQL 8 的 DDL 仅在单语句级别保证原子性） |
| 锁升级 | 从不 | 从不 | 是（行/页 -> 表，每个对象每语句约 5000 个锁） | 是（LOCKLIST/MAXLOCKS） | 否（但有 gap 锁） |
| 查询统计 | AWR/ASH (诊断包), Statspack | pg_stat_statements | 查询存储, DMVs | MON_GET_* 表函数 | performance_schema, sys 模式, 慢查询日志 |
| 会话锁超时 | ALTER SESSION SET DDL_LOCK_TIMEOUT = 30; (DDL, 秒) | SET lock_timeout = '5s'; | SET LOCK_TIMEOUT 5000; (毫秒) | SET CURRENT LOCK TIMEOUT 30; (秒) | SET SESSION lock_wait_timeout = 30; (元数据锁) + innodb_lock_wait_timeout (行锁) |
2. 性能诊断
方法：(1) 定义症状（单条查询、单个时间段、整个实例）；(2) 在调优 SQL 之前，先查看等待/时间模型；(3) 获取实际执行计划（不仅仅是估算的）；(4) 比较估算行数与实际行数——基数估算错误通常是根本原因（过时的统计信息、数据倾斜、关联谓词、参数嗅探/绑定窥探）；(5) 一次只改一个变量，并测量效果。
Oracle
- 估算计划：
EXPLAIN PLAN FOR <stmt>; 然后 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
- 实际计划：使用
/*+ GATHER_PLAN_STATISTICS */ 提示运行，然后 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<SQL_ID>', NULL, 'ALLSTATS LAST'));
- AWR/ASH/ADDM（
@?/rdbms/admin/awrrpt.sql, ashrpt.sql, addmrpt.sql, V$ACTIVE_SESSION_HISTORY, DBA_HIST_*）需要诊断包（企业版）；SQL Monitor 和 SQL Tuning Advisor 需要调优包。请检查 CONTROL_MANAGEMENT_PACK_ACCESS。无需许可证：Statspack。
- 实时等待：
V$SESSION (event, wait_class, sql_id, blocking_session), V$SYSTEM_EVENT, V$SQL / V$SQLSTATS。
- 常见等待事件：
db file sequential read（单块读，索引访问），db file scattered read / direct path read（全表扫描），log file sync（提交频率/重做 I/O），enq: TX - row lock contention（应用程序锁争用），enq: TM - contention（通常由未索引的外键引起），latch: cache buffers chains（热块竞争），gc *（RAC 互联）。
- 计划稳定性：SQL 计划管理（基线）；SQL Profile 需要调优包。
PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) <stmt>; - ANALYZE 会实际执行语句：请将 DML 包裹在 BEGIN; ... ROLLBACK; 中。pg_stat_statements（需要设置 shared_preload_libraries）：按 total_exec_time 降序排列的 Top SQL（PG13 中列名已更改：total_exec_time, mean_exec_time）。
SELECT queryid, calls, round(total_exec_time) total_ms, round(mean_exec_time::numeric,1) mean_ms, rows, left(query,80) FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;- 实时视图：
pg_stat_activity (state, wait_event_type, wait_event, xact_start, query)；idle in transaction 状态的会话会持有锁并阻塞 VACUUM。
auto_explain 模块用于在日志中记录慢查询计划；启用 track_io_timing = on 以获取 I/O 时间。- 健康检查：
pg_stat_user_tables (seq_scan, n_dead_tup, last_autovacuum, last_autoanalyze)；表膨胀；事务 ID 回卷：SELECT datname, age(datfrozenxid) FROM pg_database;。
- 终止会话：首先
pg_cancel_backend(pid)（取消查询），然后 pg_terminate_backend(pid)（终止会话）。
SQL Server
- 实际计划：SSMS 中的“包含实际执行计划”或
SET STATISTICS XML ON;；使用 SET STATISTICS IO, TIME ON; 获取读取次数和 CPU 时间。
- 查询存储（2016+；2022 年新数据库默认开启）：
sys.query_store_query, sys.query_store_query_text, sys.query_store_plan, sys.query_store_runtime_stats, sys.query_store_wait_stats (2017+)；回归问题 -> 强制计划 EXEC sp_query_store_force_plan @query_id = <q>, @plan_id = <p>;。
- DMVs：
sys.dm_exec_requests (wait_type, blocking_session_id), sys.dm_os_wait_stats, sys.dm_exec_query_stats + CROSS APPLY sys.dm_exec_sql_text(sql_handle) + sys.dm_exec_query_plan(plan_handle)。
- 常见等待事件：
PAGEIOLATCH_*（数据读取），LCK_M_*（阻塞），WRITELOG（日志 I/O / 提交率），CXPACKET/CXCONSUMER（并行度——检查 MAXDOP/成本阈值），SOS_SCHEDULER_YIELD（CPU），ASYNC_NETWORK_IO（客户端未消费结果），RESOURCE_SEMAPHORE（内存授予）。
- 参数嗅探：按参数比较计划；选项包括
OPTION (RECOMPILE), OPTIMIZE FOR, 查询存储提示 (2022)——根据情况选择。
- 扩展事件；
system_health 会话会保留最近的死锁图。
Db2 LUW
- 解释计划：创建解释表（
SYSPROC.SYSINSTALLOBJECTS 或 EXPLAIN.DDL），执行 EXPLAIN PLAN FOR <stmt>，使用 db2exfmt -d <DB> -1 -o plan.txt 格式化；快速查看使用 db2expln。
- 监控表函数：
MON_GET_PKG_CACHE_STMT（Top SQL：STMT_EXEC_TIME, NUM_EXECUTIONS, ROWS_READ），MON_GET_CONNECTION, MON_GET_ACTIVITY, MON_GET_TABLE, MON_GET_BUFFERPOOL。
- 锁：
SYSIBMADM.MON_LOCKWAITS, db2pd -db <DB> -wlocks, db2pd -db <DB> -locks。
- 统计信息：
RUNSTATS ON TABLE <SCHEMA>.<TABLE> WITH DISTRIBUTION AND DETAILED INDEXES ALL; - 使用 REORGCHK 评估重组需求；使用 db2advis 获取索引建议。
MySQL / MariaDB
EXPLAIN <stmt>；实际计划：MySQL 8.0.18+ 使用 EXPLAIN ANALYZE <stmt>；MariaDB 使用 ANALYZE <stmt>（两者都会实际执行语句）。- Top SQL：
sys.statement_analysis, performance_schema.events_statements_summary_by_digest；慢查询日志（slow_query_log, long_query_time）+ pt-query-digest。
- InnoDB：
SHOW ENGINE INNODB STATUS\G（最近的死锁，历史列表长度），information_schema.INNODB_TRX, sys.innodb_lock_waits；MySQL 8：performance_schema.data_locks / data_lock_waits；MariaDB：information_schema.INNODB_LOCKS / INNODB_LOCK_WAITS 以及 METADATA_LOCK_INFO 插件；MySQL 元数据锁：performance_schema.metadata_locks。
3. 锁与并发——陷阱
| 引擎 | 陷阱 | 缓解措施 |
|---|
| 所有 | 长事务 + DDL：DDL 等待，其后的所有查询都排队等待 | 在 DDL 上设置较短的锁超时，使用重试循环，在没有长事务时执行；执行前检查活跃会话 |
| Oracle | 未索引的外键 -> 当父表行被删除或主键更新时，子表会产生 TM 锁 | 为外键列创建索引 |
| Oracle | 在繁忙对象上执行 DDL 失败，报 ORA-00054 | DDL_LOCK_TIMEOUT；在线变体（CREATE INDEX ... ONLINE, ALTER TABLE ... MOVE ONLINE 12.2+, DBMS_REDEFINITION）——所有功能仅限企业版（SE2 中不可用） |
| Oracle | 在遍历打开游标的循环中提交 -> ORA-01555 snapshot too old | 按键值/ROWID 范围分块处理（DBMS_PARALLEL_EXECUTE），合理设置撤销保留大小 |
| PostgreSQL | 大多数 ALTER TABLE 需要 ACCESS EXCLUSIVE 锁；等待该锁会阻塞所有读取 | 设置 SET lock_timeout；使用 CREATE INDEX CONCURRENTLY；创建 FK/CHECK 约束时使用 NOT VALID，然后使用 VALIDATE CONSTRAINT |
| PostgreSQL | 更改列类型会重写整个表（除了安全的情况，如增加 varchar 长度） | 使用新列 + 回填的扩缩容方法 |
| PostgreSQL | idle in transaction 状态的会话 | 设置 idle_in_transaction_session_timeout，修复应用程序 |
| SQL Server | 在默认的 READ COMMITTED 隔离级别下，读取会阻塞写入 | 评估是否启用 READ_COMMITTED_SNAPSHOT ON（注意 tempdb 版本存储的影响；需要测试） |
| SQL Server | 大量 DML 导致锁升级到表级别 | 将批次控制在约 5000 行以下；小心使用 ALTER TABLE ... SET (LOCK_ESCALATION = ...) |
| SQL Server | 使用 NOLOCK 作为“修复”手段 | 脏读、丢失/重复行——在代码审查中拒绝使用 |
| Db2 | 默认 LOCKTIMEOUT = -1（无限等待） | 在发布会话中设置 CURRENT LOCK TIMEOUT |
| Db2 | ALTER TABLE ... DROP COLUMN，大多数 ALTER COLUMN SET DATA TYPE, SET NOT NULL/DROP NOT NULL 会使行组织表进入待重组状态（SQLSTATE 57007）：直到执行 REORG TABLE 前，只能进行扫描操作，DROP/RENAME/TRUNCATE 和大多数其他 ALTER 操作允许；此类操作最多 31 个工作单元后强制要求 REORG | 计划在执行窗口内执行 REORG TABLE；在 SYSIBMADM.ADMINTABINFO 中检查 REORG_PENDING / NUM_REORG_REC_ALTERS |
| MySQL | 元数据锁（MDL）等待：DDL 被一个打开的事务阻塞，然后会阻塞所有后续请求 | 在发布会话中设置较低的 lock_wait_timeout；杀掉长时间空闲的事务 |
| MySQL | 在线 DDL 静默地回退到表复制模式 | 始终指定 ALGORITHM=INSTANT（或 INPLACE）和 LOCK=NONE，以便在无法执行时立即报错；或者使用 gh-ost / pt-online-schema-change |
| MySQL | 在 REPEATABLE READ 级别下，范围更新会导致 Gap/next-key 锁 | 为谓词列创建索引；在允许的情况下考虑使用 READ COMMITTED |
死锁：始终是设计问题（访问顺序不一致、缺少索引）。捕获方法：Oracle 跟踪文件/警报日志（ORA-00060），PostgreSQL 日志（启用了 log_lock_waits = on 且等待时间超过 deadlock_timeout），SQL Server system_health XE 会话，Db2 锁事件监视器，MySQL innodb_print_all_deadlocks。
4. 高可用 / 灾难恢复
| 引擎 | 本地高可用 | 灾难恢复 / 副本 | 关键操作与检查 |
|---|
| Oracle | RAC（共享存储，主-主，额外许可） | Data Guard 物理备库（重做应用）；Active Data Guard = 以只读方式打开并应用（额外许可） | 保护模式：最大性能（ASYNC，默认），最大可用性（SYNC，故障时回退），最大保护（SYNC，如果无备库，主库停止）。Broker dgmgrl：SHOW CONFIGURATION;, VALIDATE DATABASE <stby>;, SWITCHOVER TO <stby>;, FAILOVER TO <stby>;。延迟：V$DATAGUARD_STATS。主库启用 FORCE LOGGING。快速启动故障转移需要 observer（理想部署在第三个站点）。 |
| PostgreSQL | Patroni (DCS: etcd/Consul/ZooKeeper) + HAProxy/VIP | 流式复制（物理，WAL）；逻辑复制（发布/订阅，PG10+） | pg_stat_replication (sent_lsn, write_lsn, flush_lsn, replay_lsn)，pg_is_in_recovery()，pg_promote() (PG12+)。同步：synchronous_standby_names + synchronous_commit。复制槽在副本宕机时会保留 WAL 直至磁盘满：需监控，使用 max_slot_wal_keep_size (PG13+) 限制大小。Patroni：patronictl -c <cfg> list / switchover / failover；为负载均衡器提供 REST 健康检查端点（/primary, /replica）。逻辑复制不复制 DDL 和序列值（截至 PG 18）：在订阅者上先应用增量的模式更改，并在故障转移至订阅者前重置序列。 |
| SQL Server | 故障转移群集实例（共享存储，WSFC） | Always On 可用性组（WSFC，或 Linux 上的 Pacemaker）；日志传送 | 仅在同步提交副本之间支持自动故障转移。为客户端提供侦听器（MultiSubnetFailover=True）。登录名、Agent 作业、链接服务器、凭据不会被复制（包含的可用性组，SQL Server 2022 企业版，有助于解决此问题）。完整 AG 需要企业版；标准版提供基本 AG（1 个数据库，2 个副本）。检查 sys.dm_hadr_database_replica_states。计划内故障转移：在目标同步辅助副本上执行 ALTER AVAILABILITY GROUP [<AG>] FAILOVER;。可读的辅助副本有许可影响。 |
| Db2 LUW | pureScale（主-主）或 HADR + Pacemaker/TSA 自动化 | HADR（最多 3 个备库，HADR_TARGET_LIST） | HADR_SYNCMODE：SYNC, NEARSYNC, ASYNC, SUPERASYNC。监控 db2pd -db <DB> -hadr 或 MON_GET_HADR。在备库上切换角色：TAKEOVER HADR ON DATABASE <DB>（BY FORCE 用于故障转移）。通过 DB2_HADR_ROS 在备库上进行读取（有限制）。 |
| MySQL / MariaDB | InnoDB Cluster（组复制 + MySQL Router）；Galera (MariaDB) | 异步 / 半同步复制，基于 GTID | SHOW REPLICA STATUS（MySQL 8.0.22+；MariaDB 10.5.1+ 接受 REPLICA 作为 SLAVE 的同义词；旧版本：SHOW SLAVE STATUS）。关注复制延迟和错误的 GTID；Galera 需要主组件法定人数（3 个节点或仲裁者）。 |
设计规则：在选择同步/异步复制之前，为每个应用定义 RPO/RTO；至少每年进行一次 DR 测试并记录结果（DORA 要求金融机构进行 ICT 连续性测试）；客户端必须能够透明地重新连接（使用包含多个主机/侦听器/SCAN 的连接字符串，并实现重试逻辑）；监控延迟并发出告警；防止脑裂保护（在第三个站点部署法定人数、隔离、observer/witness）。
5. 备份、恢复与时间点恢复 (PITR)
| 引擎 | 全量 / 增量 | PITR | 验证 |
|---|
| Oracle (RMAN) | BACKUP DATABASE PLUS ARCHIVELOG; 增量级别 0/1；CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS; | 需要 ARCHIVELOG 模式。RUN { SET UNTIL TIME "TO_DATE('2026-10-01 12:00','YYYY-MM-DD HH24:MI')"; RESTORE DATABASE; RECOVER DATABASE; } 然后 ALTER DATABASE OPEN RESETLOGS; | RESTORE DATABASE VALIDATE;, CROSSCHECK BACKUP;, LIST BACKUP SUMMARY;, REPORT NEED BACKUP;, DELETE OBSOLETE; |
| PostgreSQL | pg_basebackup -D <dir> -Ft -z -X stream -P；pgBackRest --type=full / diff / incr backup；Barman。pg_dump 仅为逻辑备份（不支持 PITR） | WAL 归档（archive_mode, archive_command）；恢复基础备份 + restore_command + recovery_target_time + recovery.signal (PG12+)。pgBackRest：pgbackrest --stanza=<s> --type=time "--target=2026-10-01 12:00:00+02" restore | pgbackrest --stanza=<s> check, pgbackrest --stanza=<s> info；定期进行实际的恢复测试 |
| SQL Server | 恢复模型 FULL；BACKUP DATABASE [<db>] TO DISK = N'<path>' WITH CHECKSUM, COMPRESSION; 差异备份；BACKUP LOG | 使用 WITH NORECOVERY 恢复完整备份，然后恢复日志备份直到目标时间点，最后使用 WITH RECOVERY。RESTORE DATABASE [<db>] FROM DISK = N'<path>' WITH FILE = 1, NORECOVERY; RESTORE LOG [<db>] FROM DISK = N'<path>' WITH FILE = 1, STOPAT = N'2026-10-01 12:00:00', RECOVERY; | RESTORE VERIFYONLY FROM DISK = N'<path>'; |
| Db2 LUW | BACKUP DATABASE <DB> TO <path> WITH 2 BUFFERS BUFFER 1024 PARALLELISM 3 COMPRESS；增量（INCREMENTAL DELTA/INCREMENTAL）需要启用 TRACKMOD | RESTORE DATABASE <DB> FROM <path> TAKEN AT <timestamp> INTO <new_db>，然后 ROLLFORWARD DATABASE <new_db> TO 2026-10-01-12.00.00.000000 USING LOCAL TIME AND STOP; | RESTORE DATABASE <DB> FROM <path> TAKEN AT <timestamp> VALIDATE; |
| MySQL / MariaDB | mysqldump --single-transaction --routines --triggers --events --flush-logs --master-data=2 > dump.sql（逻辑）；XtraBackup / MariaDB Backup（物理，支持 PITR：--backup --target-dir=<dir>, --apply-log, --copy-back） | 需要 binlog（log_bin）。使用二进制日志执行时间点恢复：`mysqlbinlog --start-datetime="..." --stop-datetime="..." binlog.000001 | mysql -u root -p` |
6. Oracle -> PostgreSQL 迁移陷阱
| # | 陷阱 | 详解 |
|---|
| 1 | PL/SQL 到 PL/pgSQL 语法差异 | Oracle 的包、重载、OUT SYS_REFCURSOR、CONNECT BY PRIOR（用递归 CTE 替代）、MERGE（PG 15 之前用 INSERT ... ON CONFLICT / 独立 UPDATE 和 INSERT）、NULLS FIRST/LAST、ROWNUM（用 LIMIT/OFFSET 或 ROW_NUMBER() 替代）、DECODE（用 CASE 替代）、(+) 连接（用显式 JOIN 替代）。 |
| 2 | 数据类型差异 | Oracle NUMBER -> NUMERIC 或 INTEGER（考虑序列或 IDENTITY 列）。VARCHAR2(n) -> VARCHAR(n)，但空字符串是 ''（PG 中是 ''，Oracle 中是 NULL）。DATE 包含时间（PG 中不包含；使用 TIMESTAMP）。CLOB -> TEXT。BLOB -> BYTEA。RAW -> BYTEA。XMLType -> XML。 |
| 3 | 事务和并发差异 | PostgreSQL 的 MVCC 会为更新创建新行版本，需要 VACUUM。Oracle 的 undo 无此需求。Oracle 中 SELECT ... FOR UPDATE 会等待，PG 中默认 NOWAIT；使用 SKIP LOCKED。Oracle 中 DDL 是隐式提交的，PG 中部分 DDL 是事务性的。 |
| 4 | 备份和恢复的差异 | RMAN 与 pg_basebackup / pgBackRest。Oracle 使用撤销表空间，PG 使用 VACUUM。Oracle 的闪回查询 -> PG 中无直接等价功能。 |
| 5 | 安全特性和函数差异 | Oracle 的用户/模式耦合（一个用户就是一个模式）与 PG 分离的登录角色和模式。DBMS_CRYPTO -> pgcrypto。DBMS_OUTPUT -> RAISE NOTICE。UTL_FILE -> COPY 或 PL 中的服务端文件函数（需小心使用）。审计：Oracle 的细粒度审计与 PG 的 audit 扩展（或在 postgresql.conf 中启用 log_statement = 'ddl'/'mod'/'all'）。DBMS_SCHEDULER -> pg_cron 或 PG 的调度扩展。 |
| 6 | 性能和调优差异 | 执行计划格式（EXPLAIN ANALYZE 与 DBMS_XPLAN）。统计信息收集：Oracle 的自动任务与 PG 的 autovacuum 和 autoanalyze。PG 中的并行查询默认是开启的；Oracle 需要特定提示。连接池：PG 中建议使用 PgBouncer 或连接池。 |
| 7 | 迁移工具和工作流 | Oracle SQL Developer, ora2pg, pgloader, AWS DMS/SCT。迁移前运行 ora2pg 的评估模式。规划停机时间；逻辑迁移可能较慢。在目标平台上重新测试所有性能敏感的查询。 |
| 8 | 功能和语法的其他差异 | 物化视图的差异（Oracle 可快速刷新；PG 需要 REFRESH MATERIALIZED VIEW）。全文检索（Oracle 的 CONTAINS 与 PG 的 @@ 和 to_tsvector/to_tsquery）。SEQUENCE：PG 的序列可以在没有间隙的情况下缓存；Oracle 的序列默认有间隙。 |
7. 发布脚本最佳实践
- 版本控制：所有变更脚本应在 Git 中进行版本控制。脚本文件名采用
<release>/<ticket-or-JIRA>/<seq>-<desc>.sql 格式。
- 环境分离：按相同顺序在开发、测试、预发布和生产环境中运行脚本。
- 幂等性：尽可能使脚本幂等——使用
CREATE OR REPLACE, IF NOT EXISTS, DROP IF EXISTS 或在执行前检查对象存在性。
- 错误处理：在每个脚本中启用
BEGIN TRY ... END TRY / BEGIN CATCH ... END CATCH 或等效的事务包装器。确保事务完整性：要么完全提交，要么完全回滚。
- 性能窗口：了解每个更改所需的时间。长时间运行的 DDL（重建索引、大表
ALTER）应在维护窗口内执行。
- 最小权限：发布用户应仅拥有执行脚本所需的最小权限。不要使用超级用户或 SA 账户进行日常发布。
- 监控与告警：设置对长时间运行的事务、死锁和锁等待的告警。
- 回滚计划：每个发布必须有一个文档化的回滚计划。包括撤消脚本。
特定于引擎的提示：
- Oracle：DDL 是隐式提交的。在执行 DDL 之前监控长时间运行的事务。对于大表，使用
TRUNCATE 和 DROP 而不是 DELETE。
- PostgreSQL：
ALTER TABLE ... ADD COLUMN 在 PG 11+ 中通常是即时操作（对于非默认值列）。但更改列数据类型或添加带有 NOT NULL 的列会重写表。使用 NOT VALID 约束，然后使用 VALIDATE CONSTRAINT 以避免长时间阻塞。
- SQL Server：小心锁升级。使用
ALTER TABLE ... SET (LOCK_ESCALATION = AUTO | TABLE | DISABLE)。对于大型 INSERT...SELECT，使用 TABLOCK 提示以启用最小日志记录。
- MySQL：对于 MySQL 8.0，始终为 DDL 指定
ALGORITHM=INSTANT 或 ALGORITHM=INPLACE, LOCK=NONE，以便在不支持的引擎上立即失败，而不是静默地使用表复制。对于大表，使用 gh-ost 或 pt-online-schema-change。
8. 审查清单
通用 SQL 审查
DDL 审查
性能审查
安全与合规审查
发布专项审查