当前位置：首页 > java >正文

主流数据库排查与优化速查手册

java 2025/8/25 10:54:41

主流数据库排查与优化速查手册（优化版）

一、连接失败

1.1 统一排查流程

1.2 各库特例处理

数据库	关键命令/配置	常见错误示例
MySQL	`SHOW VARIABLES LIKE 'bind_address';` `mysql -h HOST -P PORT -u USER -p`	ERROR 1045 (Access denied)
PgSQL	`SELECT * FROM pg_hba_file_rules;` `pg_isready -h HOST -p PORT`	FATAL: no pg_hba.conf entry
SQL Server	`EXEC xp_readerrorlog` `sqlcmd -S HOST,PORT -U USER -P PWD`	Login failed for user
Oracle	`lsnrctl status` `tnsping SERVICE_NAME`	ORA-12541: TNS no listener

1.3 诊断口诀

服-口-网-权（服务→端口→网络→权限）

二、慢查询

2.1 黄金诊断流程

2.2 各库优化技术对比

数据库	执行计划分析命令	优化工具/技术
MySQL	`EXPLAIN FORMAT=TREE`	索引下推、BKA优化
PgSQL	`EXPLAIN (ANALYZE, BUFFERS)`	JIT编译、并行查询
SQL Server	`SET SHOWPLAN_XML ON`	列存储索引、内存优化表
Oracle	`SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR)`	自适应执行计划、SQL Profile

2.3 优化口诀

析-索-导-离（分析计划→索引优化→执行引导→查询解耦）

三、锁冲突

3.1 锁类型与隔离级别

数据库	默认隔离级别	锁机制特性	死锁检测方式
MySQL	REPEATABLE READ	间隙锁、Next-Key Locks	`SHOW ENGINE INNODB STATUS`
PgSQL	READ COMMITTED	MVCC、行级锁	`pg_blocking_pids()`
SQL Server	READ COMMITTED	行版本控制（RCSI）、锁升级	死锁图捕获
Oracle	READ COMMITTED	行级TX锁、表级TM锁	`V$LOCK`视图

3.2 实战命令示例

-- MySQL 查看锁等待
SELECT * FROM performance_schema.data_locks 
WHERE LOCK_STATUS = 'WAITING';-- PostgreSQL 查阻塞关系
SELECT pid, query, pg_blocking_pids(pid) 
FROM pg_stat_activity;

3.3 破解口诀

源-级-拆-快（定位阻塞源→调整隔离级→事务拆分→快速提交）

四、主从延迟

4.1 延迟监控指标

数据库	主库监控指标	从库监控指标	延迟计算方式
MySQL	`SHOW MASTER STATUS`	`SHOW SLAVE STATUS`	`Seconds_Behind_Master`
PgSQL	`pg_current_wal_lsn()`	`pg_last_wal_replay_lsn()`	LSN差值转换
SQL Server	`sys.dm_hadr_database_replica_states`	`redo_queue_size`	事务日志堆积量
Oracle	`V$ARCHIVED_LOG`	`V$DATAGUARD_STATS`	Apply Lag (seconds)

4.2 优化策略

网络层：启用压缩（MySQL的slave_compressed_protocol）、专用通道（Oracle Data Guard FastStart Failover）
主库层：批量提交事务、减少DDL操作
从库层：并行回放（MySQL MTS）、硬件加速（PgSQL的FPW优化）

4.3 调优口诀

网-主-从-监（网络优化→主库减压→从库加速→监控预警）

五、存储瓶颈

5.1 存储优化四层模型

5.2 各层优化策略

层级	优化手段
硬件层	NVMe SSD RAID10、Optane持久内存
文件系统层	XFS调整预读（`/sys/block/sda/queue/read_ahead_kb`）、禁用atime
数据库层	MySQL双写缓冲关闭（`innodb_doublewrite=0`）、PgSQL WAL分段存储
应用层	分页查询优化（`WHERE id > last_id LIMIT N`）、避免SELECT *