DBA工作常见问题整理

发布于：2025-04-18 ⋅ 阅读:(26) ⋅ 点赞:(0)

MVCC机制:

PostgreSQL的多版本并发控制(MVCC)是其核心特性之一，它允许数据库在高并发环境下保持高性能的同时提供事务隔离。

MVCC通过维护数据的多个版本实现：

  • 读操作不阻塞写操作
  • 写操作不阻塞读操作
  • 避免使用锁实现并发控制

PostgreSQL的MVCC特点

  • 写时复制(Copy-on-Write)：修改数据时创建新版本
  • 无回滚段：与Oracle不同，PG将旧版本数据直接存放在表中
  • 通过事务ID和可见性规则判断数据版本可见性

MVCC工作流程:

  • INSERT操作:创建新行版本;设置xmin=当前事务ID;设置xmax=0;
  • UPDATE操作:
    创建新行版本(新版本包含修改后的数据):新版本xmin=当前事务ID;新版本xmax=0
    标记旧版本xmax=当前事务ID
  • DELETE操作:标记行的xmax=当前事务ID;不立即删除数据;
  • SELECT操作:根据当前事务的快照和可见性规则;返回满足条件的可见行版本;

MVCC的存储影响

  • 表膨胀问题:更新/删除操作不会立即释放空间,旧版本数据会累积导致表膨胀 ;
  • 清理机制:常规VACUUM：标记死元组空间为可重用,VACUUM FULL：重写整个表文件(锁表);
  • AUTOVACUUM:自动执行的清理进程,根据参数autovacuum_vacuum_scale_factor和autovacuum_vacuum_threshold触发;

关键参数调优

# postgresql.conf中的相关参数
# 自动清理
autovacuum = on
autovacuum_vacuum_scale_factor = 0.2  # 表大小20%变化时触发
autovacuum_vacuum_threshold = 50      # 最少50条死元组
autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 50
# 冻结相关
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 150000000
# 事务ID相关
vacuum_defer_cleanup_age = 0

事务隔离级别：

  • 读未提交：脏读，不可重复读，幻读
  • 读已提交：可能不可重复读，幻读（postgres默认隔离级别）
  • 可重复读：幻读
  • 串行化：效率低
 --postgres查询事务隔离级别：
SHOW default_transaction_isolation;  --read committed（读已提交）

ACID:

  • 原子性：事务被视为不可分割的最小工作单元，要么全部成功，要么全部失败。
  • 一致性: 事务执行前后，从一个状态到另一个状态保持一致。
  • 隔离性：多个事务并发执行时，一个事务的执行不能影响其他事务。
  • 持久性：事务一旦提交结果永久生效，系统故障也不会丢失。

悲观锁（Pessimistic Lock）

原理：假设并发冲突会发生，先加锁再操作。
实现：

BEGIN;
SELECT * FROM orders WHERE id = 100 FOR UPDATE;  -- 加X锁
UPDATE orders SET amount = 200 WHERE id = 100;
COMMIT;

适用场景：写多读少，高竞争环境（如库存扣减）。

乐观锁（Optimistic Lock）

  • 原理：假设冲突较少，通过版本号或时间戳检测冲突。
-- 1. 查询时获取版本号
SELECT id, amount, version FROM orders WHERE id = 100;
-- 2. 更新时检查版本号
UPDATE orders SET amount = 200, version = version + 1 
WHERE id = 100 AND version = 1;  -- 如果version被其他事务修改，更新失败

适用场景：读多写少，低竞争环境（如评论更新）。

锁类型 MySQL (InnoDB) Oracle SQL Server PostgreSQL
行锁 支持 支持 支持 支持
表锁 支持（MyISAM） 支持 支持 支持
意向锁 支持 不支持 支持 不支持
间隙锁 支持 不支持 支持 不支持
乐观锁 需手动实现 支持 支持 支持（MVCC）

特殊锁类型

记录锁（Record Lock）

  • 作用：锁定索引记录（InnoDB基于索引加锁）。
  • 示例：
SELECT * FROM users WHERE id = 5 FOR UPDATE;  -- 锁定id=5的行

间隙锁（Gap Lock）

  • 作用：锁定索引记录之间的间隙，防止幻读。
  • 示例：
SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE;  -- 锁定10~20的间隙

支持数据库：InnoDB（在REPEATABLE READ隔离级别下生效）。

临键锁（Next-Key Lock）

  • 作用：组合记录锁 + 间隙锁，锁定记录及前面的间隙。
  • 示例：
SELECT * FROM users WHERE id > 15 FOR UPDATE;  -- 锁定15及之后的记录+间隙

应用：InnoDB默认使用临键锁解决幻读。

自增锁（AUTO-INC Lock）

  • 作用：确保自增主键（AUTO_INCREMENT）的唯一性。
  • 特点：表级锁，在插入完成后立即释放。

如何选择锁？

  • 高并发写入：优先使用 行级排他锁（FOR UPDATE）或 乐观锁。
  • 避免死锁： 按固定顺序访问多行（如先锁id=1，再锁id=2）。 减少事务持有锁的时间。
  • 读多写少：使用 MVCC（多版本并发控制） 或 乐观锁。
  • 防止幻读：在REPEATABLE READ隔离级别下，InnoDB默认使用 临键锁。

锁常见问题

Q1：什么是死锁？如何解决？

  • 死锁：两个事务互相等待对方释放锁。
  • 解决方案： 数据库自动检测并回滚一个事务（如InnoDB）。 设置锁超时（innodb_lock_wait_timeout）。

Q2：MySQL的InnoDB为什么默认用行锁？

  • 行锁并发度高，配合MVCC支持高吞吐量。

Q3：什么情况下会升级为表锁？

  • 无索引条件更新（如UPDATE users WHERE name = ‘Alice’，若name无索引）。 执行ALTER TABLE等DDL操作。

数据库驱动（Database Driver）

  • 数据库驱动是应用程序与数据库之间通信的桥梁，负责将SQL语句发送给数据库并接收返回结果。不同的数据库（MySQL、PostgreSQL、Oracle等）有不同的驱动实现方式。

数据库连接池（Connection Pool）

  • 连接池是一种管理数据库连接的技术，避免频繁创建和销毁连接，提高性能。

为什么需要连接池？

  • 问题：每次执行SQL都创建新连接（TCP三次握手、认证）开销大。

  • 解决方案：连接池预先创建一批连接，应用程序使用时直接从池中获取，用完归还。

#连接池核心参数

参数 说明
maximumPoolSize 最大连接数（默认10）
minimumIdle 最小空闲连接（建议与maximumPoolSize相同）
idleTimeout 空闲连接超时时间（默认60000ms）
connectionTimeout 获取连接超时时间（默认30000ms）
maxLifetime 连接最大存活时间（默认1800000ms，30分钟）

常用协议

(1)IP（网际协议）

IP协议是互联网的核心协议之一，它负责数据的封装、寻址和路由。
简单来说，当你想要发送一封电子邮件或者访问一个网站时，你的数据首先会被封装成IP数据包。每个数据包都会包含目标地址（即接收方的IP地址），这样路由器就知道如何将数据包从发送方传输到接收方。

(2)TCP（传输控制协议）

TCP是一个可靠的、面向连接的协议，它确保数据能够可靠地从发送方传输到接收方。它通过以下几个步骤来实现这一目标：
#建立连接： 在数据传输开始前，TCP会先建立一条连接，这涉及到三次握手过程，确保双方都准备好进行数据传输。
#数据传输： 一旦连接建立，数据就可以通过TCP以流的形式发送。TCP会确保数据的顺序、完整性，并且在传输过程中丢失或损坏的数据会被重新发送。
#关闭连接： 数据传输完成后，TCP会进行四次挥手过程来安全地关闭连接。

(3)UDP（用户数据报协议）

UDP是一个简单的、不可靠的、无连接的协议。与TCP相比，UDP不保证数据的可靠传输，也不保证数据的顺序。因此，UDP适用于那些对实时性要求高、可以容忍少量数据丢失的场景，如视频流、在线游戏等。

(4)ICMP（互联网控制消息协议）

ICMP用于网络故障诊断，比如当数据包因为某些原因无法送达目标时，它会返回一个错误消息给发送方。
例如，如果你尝试访问一个不存在的网站，你的计算机可能会收到一个“目标主机不可达”的ICMP消息。

(5)两阶段提交协议(2PC)

两阶段提交协议(2PC)是分布式系统中保证事务原子性的经典协议，确保所有参与节点要么全部提交事务，要么全部中止事务。
原子性：所有参与者要么全部提交，要么全部回滚 一致性：保证系统从一个一致状态转换到另一个一致状态 隔离性：事务执行过程中不会被其他事务干扰

阶段一：准备阶段

  • 协调者向所有参与者发送准备请求,每个参与者执行事务操作但不提交，记录Undo/Redo日志
  • 参与者回复准备结果： 同意(Yes)：事务执行成功，已准备好提交 中止(No)：事务执行失败或出现异常

阶段二：提交/中止阶段

  • 情况1：所有参与者都返回Yes
    协调者发送提交命令(Commit) 参与者完成提交，释放锁资源,参与者返回确认(Ack),协调者完成事务
  • 情况2：任一参与者返回No或超时 协调者发送回滚命令,参与者执行回滚操作，使用Undo日志恢复,参与者返回确认,协调者中止事务

(6)Paxos协议:

Paxos由Leslie Lamport提出，被认为是分布式一致性算法的理论基础。
核心角色 ：
Proposer：提案发起者
Acceptor：提案接收/批准者
Learner：学习已确定的提案

  • 阶段一：Prepare阶段 Proposer 选择一个全局唯一的提案编号n，向多数派 Acceptor 发送Prepare(n)请求
    Acceptor 收到Prepare(n)后：如果n大于已承诺的任何编号，承诺不再接受编号小于n的提案,返回已接受的最高编号提案(如果有)

  • 阶段二：Accept阶段 Proposer收到多数派响应后： 如果发现有已接受的提案，使用该提案值,否则使用自己的提案值 向
    Acceptor 发送 Accept(n, value)请求,Acceptor
    接受该提案除非它已经承诺了更高编号,提案被多数派接受后，值被确定

  • 优点： 理论严谨，被证明是正确的,可应对各种异常情况,是许多分布式系统的基础

  • 缺点： 难以理解和实现,活锁问题(需通过leader选举解决),工程实现复杂

(7)Raft协议:

Raft是Paxos的简化版本,更易于理解和实现。
核心角色：
Leader：唯一处理客户端请求的节点
Follower：被动接收Leader的更新
Candidate：选举中的临时状态

核心机制

  • Leader选举 ：
    Follower在选举超时(150-300ms随机)后成为Candidate
    Candidate发起选举：自增任期号,向其他节点发送RequestVote RPC
    节点投票规则：每个任期只能投一次票,Candidate日志至少和自己一样新,获得多数票则成为Leader,Leader定期发送心跳维持权威
  • 日志复制
    客户端请求发送到 Leader,Leader 将操作追加到本地日志 Leader 通过 AppendEntries RPC
    复制日志到 Followers,当多数派确认后，提交日志并应用到状态机, Leader 通知 Followers 提交日志

2PC vs 其他协议:

特性 2PC 3PC Paxos Raft
消息轮次 2 3 多轮 多轮
阻塞情况 可能长时间阻塞 减少阻塞 无阻塞 无阻塞
一致性 强一致 强一致 强一致 强一致
单点问题 严重 有所改善 无 无
实现复杂度 简单 中等 复杂 中等

GAUSSDB优化：

可以通过巡检平台（Prometheus + Grafana）监测数据库服务器各项指标,或打印诊断报告（WDR）进行分析数据库存在的潜在风险；
CPU核数大小影响系统的稳定性，进程大小影响数据库的并发能力；I/O大小影响数据传输；可以使用固态硬盘提升I/O或扩大内存降低I/O压力；提升缓存命中率；
数据库侧的话可以通过监控平台巡检或诊断报告等方式定位慢SQL、topsql和等待事件；然后通过执行计划（expian或explain
analyze）进行SQL分析判断如何进行优化; 比如新增索引,收集统计信息，对大表进行分区或者分表分库；
定期使用vacuum回收表空间，或vacuum full减少表碎片(oracle可以通过重建表或移动表空间减少表碎片);
定期对无效索引和备份表进行清理；脚本上多使用commit及时提交任务释放资源，设置任务超时减少资源占用。

逻辑复制和物理复制：

  • 逻辑复制：它通过解析事务日志（WAL）的逻辑变化实现数据复制，
  • 物理复制：它通过在磁盘块级别复制数据来实现主从数据库的同步。
特性 逻辑复制 物理复制
复制粒度 行级变更 磁盘块变更
跨版本支持 支持 不支持
目标库可写 支持 只读
网络带宽消耗 较低（仅传变更数据） 较高（传输完整页）
适用场景 数据分发、异构同步 高可用、灾备

GaussDB 副本机制：

GaussDB 采用多副本机制来保证数据的高可用性和可靠性，主要包含以下几种副本类型：

  • 主副本：负责处理所有读写请求，将数据变更同步到备副本，每个表或分区有且只有一个主副本

  • 备副本：只读副本，不处理写请求，通过日志同步保持与主副本数据一致，主副本故障时可自动或手动提升为主

  • 同步副本： 特殊备副本，要求事务必须在同步副本确认后才返回成功，保证数据强一致性，通常用于金融等高可靠性要求的场景

  • 异步副本 ：普通备副本，数据同步有延迟，不影响主副本性能，适用于跨地域容灾等场景

副本存储粒度：

节点级存储：

  1. 数据节点 (DN)：实际存储数据的节点，每个DN存储部分数据(分片)，支持多副本分布在不同的DN上；
  2. 协调节点 (CN)：不存储实际数据，负责SQL解析、生成执行计划，将请求路由到相应DN；

表空间 (Tablespace)：
分区表 (Partitioning)：
分片 (Sharding)：

一致性级别：

-- 设置事务一致性级别
SET LOCAL synchronous_commit = 'remote_apply';  -- 强同步
SET LOCAL synchronous_commit = 'on';            -- 本地持久化
SET LOCAL synchronous_commit = 'off';           -- 异步复制

副本配置建议

  • 生产环境：至少3副本(1主2备) 跨地域容灾：主副本+本地同步备+异地异步备 读写分离：配置多个只读备副本分担查询负载
--查看副本状态
SELECT * FROM pg_stat_replication;
--查看数据分布
SELECT * FROM pgxc_node;
--检查数据同步延迟
SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

GaussDB中explain和explain analyze的区别

explain和explain analyze都是用于查看SQL的执行计划，区别在于explain不会实际执行SQL语句主要用于查看SQL语句的执行步骤和预计的开销，而
explain analyze 不仅会生成SQL语句的执行计划，还会实际执行SQL语句，并显示执行的概要信息。

GaussDB集中式与分布式数据库的区别

  1. ‌数据存储方式‌： 集中式数据库：将所有数据存储在单一服务器中，数据管理和维护相对简单，但存在单点故障的风险‌
    分布式数据库：将数据分散存储在多台服务器上，通过分片或分区的方式实现，提高了数据的可用性和冗余度，减少了单点故障的风险。 ‌
  2. 数据处理方式‌： 集中式数据库：所有的数据处理都在同一台服务器上进行，处理速度受限于单台服务器的性能。
    分布式数据库：充分利用多台服务器的计算能力，可以并行处理大量数据，提高数据处理的效率和速度。 ‌
  3. 可扩展性‌：
    集中式数据库：扩展性有限，一旦服务器性能达到瓶颈，就需要进行垂直扩展，这往往是昂贵且复杂的。
    分布式数据库：在面对大规模数据增长时表现出色，可以通过添加更多的服务器节点进行水平扩展，从而保持高性能和低延迟。 ‌
  4. 高可用性‌：
    集中式数据库：在服务器故障时容易导致系统中断，可用性相对较低。
    分布式数据库：由于数据在多个服务器上复制，当某台服务器故障时，系统仍然可以从其他服务器中获取数据，具有高可用性。
  5. 安全性与管理‌： 集中式数据库：可以在单一服务器上实施统一的安全策略，便于管理和监控。
    分布式数据库：由于涉及多台服务器，需要更复杂的安全措施来保护数据的完整性和隐私，管理相对复杂。
  6. 成本‌：
    集中式数据库：通常具有较低的初始成本，因为只需要购买和维护单一服务器。然而，随着数据量的增长和性能的需求提升，额外的硬件和资源投入可能会显著增加。
    分布式数据库：虽然初始投入较高，但随着需求的增长，其成本增长相对较稳定。

存储过程中的游标用法：

游标主要用于处理需要批量执行的SQL，它类似于循环，定义游标将其于select语句进行绑定，开启游标后可以进行遍历出select里的值来进行批处理。批处理结束后需及时结束游标将资源释放。

分布式数据库如何规避单点故障：

  • 心跳监测，定期巡检，监控系统状态，及时发现并处理异常‌
  • 容灾部署：通过同城和异地的数据中心部署，确保数据的完整性和业务的连续性。例如，同城一个数据中心和异地一个数据中心可以采用3副本部署
  • 定期检查和维护服务器硬件，预防硬件故障导致的DN节点失效。
  • 设置会话超时，及时提交任务释放资源。

GaussDB vacuum和vacuum full的区别：

VACUUM命令主要用于回收表的空间，使得已删除或更新的数据所占用的空间可以被重新使用，但它不会将额外的空间返回给操作系统。 VACUUM
FULL 命令则更为彻底，它会将表的全部内容重写到一个没有垃圾数据的新文件中，并释放旧表占用的空间给操作系统。
但会消耗较长时间和资源‌会对业务造成一定的中断‌。

数据迁移

在进行数据迁移时，确保方案设计的全面性至关重要，必须涵盖操作前、操作中及操作后的各项准备工作。
首先，在数据迁移前，需对工作量进行详细评估，包括数据库、用户、表、视图、存储过程、包及相关应用的改造等。此外，还需对数据库及网络配置进行审查，确保两端之间的网络互通顺畅。同时，针对可能出现的故障，制定相应的解决方案，并明确迁移失败后的回退措施。为了降低风险，建议准备测试库进行演练，以确保迁移流程的可行性。
其次，提前进行相关宣贯，确保所有参与人员明确各自角色与责任，并做好后勤保障，以支持迁移过程的顺利进行。
最后，迁移完成后，安排全面的测试工作，及时处理测试中发现的问题，及时进行反馈并按紧急程度进行分类处理。测试完成后，整理相应的技术文档，以便总结经验教训，优化未来的迁移流程。

DRS数据同步

出现高延迟原因：

  • ‌全量阶段刚结束会导致的增量迁移延迟‌：
    在全量迁移过程中，源库增量数据还在不断地写入。DRS需要在全量结束之后，将这部分增量数据同步到目标端去，因此时延较高‌。
    ‌源库存在大批量数据变更‌：大事务写入在源库执行时间很长，而DRS把数据同步到目标库也需要一段时间，所以此时延迟会慢慢增加‌。
  • ‌网络条件不佳‌：网络质量直接影响数据传输效率，特别是跨地域迁移时，网络延迟会显著增加‌。
  • ‌库性能差异‌：源库与目标库之间的性能不匹配可能导致数据处理延时‌。
  • ‌特定数据库特性或配置‌： 例如，在MongoDB链路中，如果集合的索引中有unique
    key或集合属性的capped为true时，DRS可能只支持单线程写入，不支持并发回放，从而导致时延增高‌

源端库需开启归档日志，开启读写权限

  • 相关数据库参数配置：
[mysqld]
server_id = 123456      # 必须唯一
log_bin = ON            # 开启binlog
binlog_format = ROW     # 必须为ROW模式
binlog_row_image = FULL # 全镜像模式
expire_logs_days = 7    # 日志保留周期
gtid_mode = ON          # 建议开启GTID
enforce_gtid_consistency = ON


--Oracle关键参数
-- 检查归档模式
SELECT log_mode FROM v$database;
-- 若未开启归档需执行
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
-- 参数调整
ALTER SYSTEM SET supplemental_log_data_all=ON;
ALTER SYSTEM SET enable_goldengate_replication=TRUE;

postgresql.conf
wal_level = logical     # 必须为logical或replica
max_wal_senders = 10    # 大于订阅数
max_replication_slots = 10
hot_standby = on

oracle内存

SGA：SystemGlobal

Area是OracleInstance的基本组成部分，在实例启动时分配;系统全局域SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。

  • 共享池：Shared
    Pool用于缓存最近被执行的SQL语句和最近被使用的数据定义，主要包括：Librarycache（共享SQL区）和Datadictionarycache（数据字典缓冲区）。
    共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息。

  • 缓冲区高速缓存：DatabaseBufferCache用于缓存从数据文件中检索出来的数据块，可以大大提高查询和更新数据的性能。

  • 大型池：Large Pool是SGA中一个可选的内存区域，它只用于shared server环境。

  • Java池：Java Pool为Java命令的语法分析提供服务。

  • PGA：ProcessGlobal Area是为每个连接到Oracledatabase的用户进程保留的内存。

mysql日志类型介绍：

  • 错误日志‌：记录MySQL服务器在启动、运行和停止时出现的问题，包括错误、警告和诊断消息。默认情况下，错误日志是开启的，且无法被禁止。可以通过SQL命令SHOW
    VARIABLES LIKE ‘log_error’;查看错误日志文件的位置‌
  • 日志生成顺序： 事务提交前:undo日志 redo日志 事务提交后：binlog
  • Undo：是事务的“后悔药”，保证原子性和隔离性。
  • Redo：是数据的“安全带”，确保持久性。
  • Binlog：是数据库的“时光机”，支持复制和恢复。
  • ‌通用查询日志‌：记录所有客户端连接和客户端提交的SQL语句。默认情况下，通用查询日志没有启用，但可以通过系统变量general_log查看和控制其设置‌
  • ‌慢查询日志‌：记录执行时间超过指定时间（默认为10秒）的查询。启用慢查询日志可以帮助监控需要优化的查询语句‌
  • ‌二进制日志‌（Binlog）：记录所有修改数据的SQL语句，用于数据恢复和复制。二进制日志记录了所有DDL和DML语句，但不包括纯查询语句‌
  • ‌重做日志‌（Redo log）：确保事务的持久性，记录事务执行后的状态，用于在故障发生时恢复未写入磁盘的数据页‌
  • ‌回滚日志‌（Undo log）：用于实现事务的原子性和一致性，在事务失败或需要回滚时，使用回滚日志将数据恢复到原始状态‌
  • ‌中继日志‌（Relay log）：仅存在于主从复制结构中的从节点，用于保存主节点传输过来的数据变更事件，并将这些事件应用于从节点‌
  • ‌DDL日志‌：在MySQL
    8.0中，DDL日志存储在mysql.innodb_ddl_log数据字典表中，用于记录DDL语句执行的元数据操作‌

存储引擎（InnoDB、MyISAM常用）

  • MySQL支持多种存储引擎，每种存储引擎都有其特定的优势和适用场景。以下是MySQL中一些常见的存储引擎及其特点：

InnoDB‌：

  • ‌特点‌：支持事务（ACID特性）、行级锁定、外键约束、自动崩溃恢复、多版本并发控制（MVCC）、数据缓存（使用缓冲池）。
  • ‌优势‌：遵循ACID模型，事务具有提交、回滚和崩溃的恢复功能；支持行级锁，提高了多用户的读取并发性和性能；维护了自己的缓冲池，访问数据时可以在内存缓存表和索引数据，提高效率；支持外键约束，保证数据的完整性。
  • ‌应用场景‌：适用于大多数在线事务处理（OLTP）系统，如电子商务网站、银行系统等。

MyISAM‌：

  • ‌特点‌：支持B-tree索引、全文索引、空间数据支持、压缩数据；不支持事务处理和外键约束；采用表级锁定。
  • ‌优势‌：插入和查询速度较快，占用空间小。
  • ‌应用场景‌：适用于读取操作远多于写入操作的场景，如数据仓库、日志系统等。

MEMORY（Heap）‌：

  • ‌特点‌：所有数据存储在内存中，读写速度极快；不支持事务和外键约束；数据在MySQL重启时会丢失。
  • ‌优势‌：极高的读写速度，适合临时表和缓存。
  • ‌应用场景‌：适用于需要快速读写的小型数据集，如临时表、缓存表等。

MYSQL慢日志

MySQL慢日志（Slow Query
Log）是一种用于记录执行时间超过指定阈值的SQL语句的日志。通过分析慢查询日志，可以找出数据库性能瓶颈，优化SQL语句，从而提高数据库性能。以下是关于MySQL慢日志的一些关键信息和配置步骤：

  • ‌定义‌：慢日志全称为慢查询日志（Slow Query Log），主要用来记录在MySQL中执行时间超过指定时间的SQL语句。
  • ‌作用‌：通过慢查询日志，可以找出哪些语句的执行效率低，以便进行优化。 慢日志相关参数
  • ‌slow_query_log‌：是否启用慢查询日志，默认为0（关闭），可设置为1（开启）。
  • ‌slow_query_log_file‌：指定慢查询日志的位置及名称，默认值为host_name-slow.log，可指定绝对路径。
  • ‌long_query_time‌：慢查询执行时间阈值，超过此时间会记录，默认为10秒，单位为秒。
  • ‌log_output‌：慢查询日志输出目标，默认为FILE（即输出到文件），也可以设置为TABLE（即输出到数据库表）。
  • ‌log_timestamps‌：控制错误日志、慢日志、通用日志文件中的显示时区，默认使用UTC时区，建议改为SYSTEM系统时区。
  • ‌log_queries_not_using_indexes‌：是否记录所有未使用索引的查询语句，默认为OFF。
  • ‌min_examined_row_limit‌：对于查询扫描行数小于此参数的SQL，将不会记录到慢查询日志中，默认为0。
  • ‌log_slow_admin_statements‌：慢速管理语句是否写入慢日志中，管理语句包括ALTER TABLE、CREATE
    INDEX等，默认为OFF。

慢日志配置步骤
开启慢日志‌： 在MySQL配置文件（如my.cnf或my.ini）中，设置slow_query_log=1。
设置慢查询阈值‌： 根据需要调整long_query_time的值，例如设置为2秒long_query_time=2。
指定日志文件位置‌： 设置slow_query_log_file参数，指定慢查询日志文件的保存位置，例如slow_query_log_file=/data/mysql/logs/slow.log。
重启MySQL服务‌： 修改配置文件后，需要重启MySQL服务使配置生效。
慢日志分析 查看慢日志‌： 可以使用文本编辑器打开慢查询日志文件，查看记录的慢查询语句。
分析工具‌： 可以使用mysqldumpslow、pt-query-digest等工具来分析慢日志，提取出执行时间最长的SQL语句，以便进行优化。
注意事项： ‌性能影响‌：开启慢查询日志可能会对数据库性能产生一定影响，因为需要记录额外的日志信息。因此，在生产环境中开启慢查询日志时，需要权衡其对性能的影响。
‌定期归档‌：慢查询日志文件可能会变得非常大，因此需要定期进行归档处理，例如将旧文件重命名并开启新的日志文件。
‌灵活调整‌：慢查询阈值（long_query_time）可以根据实际情况灵活调整，以便捕捉到更多的慢查询语句。

Linux常用命令

1. 数据库服务管理命令

通用服务管理

#查看数据库服务状态
systemctl status <数据库服务名>
# 启动数据库服务
systemctl start <数据库服务名>
# 停止数据库服务
systemctl stop <数据库服务名>
# 重启数据库服务
systemctl restart <数据库服务名>
# 设置开机自启
systemctl enable <数据库服务名>

2. 数据库文件管理

查找数据库文件

# 查找Oracle数据文件
find / -name "*.dbf" 2>/dev/null
# 查找MySQL数据文件
find / -name "ibdata*" 2>/dev/null
# 查找PostgreSQL数据文件
find / -name "PG_VERSION" 2>/dev/null

检查磁盘空间使用

df -h  # 查看文件系统使用情况
du -sh /var/lib/mysql/  # 查看MySQL数据目录大小
du -sh /oracle/oradata/  # 查看Oracle数据目录大小

3. 日志文件查看

通用日志查看

# 实时查看日志
tail -f /path/to/logfile.log
# 查看最后100行日志
tail -100 /path/to/logfile.log
# 按时间筛选日志
grep "2023-10-01" /path/to/logfile.log
#数据库特定日志
# Oracle告警日志
tail -f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/trace/alert_$ORACLE_SID.log
# MySQL错误日志
tail -f /var/log/mysql/error.log
# PostgreSQL日志
tail -f /var/lib/pgsql/data/pg_log/postgresql-*.log

4. 性能监控命令

系统资源监控

top        # 实时系统监控
htop       # 增强版top
vmstat 1   # 虚拟内存统计
iostat 1   # I/O统计

数据库连接监控

# MySQL连接数
mysqladmin -u root -p processlist
# Oracle会话数
sqlplus / as sysdba
SQL> SELECT count(*) FROM v$session;
# PostgreSQL连接数
psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"

5. 网络连接检查

# 检查数据库端口是否监听
netstat -tulnp | grep 1521  # Oracle
netstat -tulnp | grep 3306  # MySQL
netstat -tulnp | grep 5432  # PostgreSQL
# 测试远程连接
telnet 主机名 端口号
nc -zv 主机名 端口号

快速定位问题的技巧

① 按时间筛选

# 查看最近 10 条日志
tail -n 10 /var/log/syslog

# 查看某个时间段的日志（如 2023-10-01 10:00 到 11:00）
sed -n '/Oct 1 10:00/,/Oct 1 11:00/p' /var/log/syslog

# 实时追踪最新日志（调试时常用）
tail -f /var/log/nginx/error.log

② 按关键词过滤

# 筛选错误和警告（忽略大小写）
grep -i -E "error|warning|fail|critical" /var/log/syslog

# 筛选特定服务或进程（如 sshd）
grep "sshd" /var/log/auth.log

# 结合时间戳和关键词（如 5 分钟内的错误）
awk '/Oct 1 12:00/,/Oct 1 12:05/' /var/log/syslog | grep -i "error"

③ 按日志级别过滤

  • 许多服务日志标注了级别（如 ERROR、WARN、DEBUG），可通过正则匹配：
grep -E "\[ERROR\]|\[CRITICAL\]" /var/log/your_app.log

使用专用工具分析日志
① journalctl（Systemd 系统日志）

# 查看某个服务的日志（如 nginx）
journalctl -u nginx.service

# 按时间范围查看
journalctl --since "2023-10-01 09:00:00" --until "2023-10-01 10:00:00"

# 按优先级过滤（0: emerg, 3: error, 4: warning）
journalctl -p err -b  # 本次启动后的错误

② logrotate 管理日志轮转
检查是否因日志轮转导致历史日志被压缩：

ls /var/log/*.gz      # 查看压缩的旧日志
zcat /var/log/syslog.1.gz | grep "error"  # 解压并搜索

③ 高级工具（ELK/Splunk）
对于海量日志，可用 ELK（Elasticsearch+Logstash+Kibana）或 Splunk 进行可视化分析。

  1. 常见问题定位示例
    案例 1：服务器突然变慢
# 检查系统资源历史
sar -u -r 1 3        # CPU 和内存使用率
dmesg | grep "OOM"   # 是否因内存不足杀死进程

# 检查磁盘 I/O
iostat -x 1 3        # 查看磁盘负载
df -h                # 检查磁盘空间

案例 2：SSH 登录失败

# 查看认证日志中的失败记录
grep "Failed password" /var/log/auth.log

# 提取攻击者 IP
grep "Failed password" /var/log/auth.log | awk '{print $11}' | sort | uniq -c

案例 3：服务崩溃

# 查看服务状态和日志
systemctl status nginx
journalctl -xe -u nginx --no-pager

沟通相关面试：

一、技术与非技术人员沟通

  • 请描述一次你需要向非技术人员(如高管或业务部门)解释复杂数据库问题的经历。你是如何简化技术概念的？

回答建议：

  • 使用类比或比喻来解释技术概念。
  • 避免使用技术术语，专注于业务影响。

示例回答：
“有一次数据库出现性能瓶颈，导致业务部门的报表延迟。我向高管解释时，用高速公路和车流量作比喻：数据库就像一条高速公路，数据请求就像车辆，当车辆过多或道路设计不合理时，就会产生堵塞。我建议增加‘车道’（增加索引或优化查询）来缓解拥堵。通过这种类比，高管很快理解了问题的本质，并同意了我的优化方案。

  • 当开发团队提出一个可能对数据库性能产生负面影响的请求时，你会如何沟通你的顾虑？

回答建议：

  • 先表达理解开发需求，再提出具体的风险。
  • 提供替代方案，而不是直接否定请求。

示例回答：
“我会首先表示理解开发团队的需求和目标，然后指出具体的潜在风险，比如可能导致查询变慢或资源消耗增加。接着，我会提出更安全的替代方案，并与开发团队一起讨论这些方案的可行性，确保大家都能接受。”

  • 如何向业务部门解释为什么某些查询需要优化，即使它们“现在运行良好”？ 回答建议：

强调潜在风险和长期影响。
使用具体的例子或数据支撑你的观点。

示例回答：
“我会向业务部门说明，虽然现在查询运行良好，但随着数据量增长，性能可能迅速下降。我会用历史数据或趋势图来展示这种风险，并强调提前优化可以避免未来业务中断，确保业务稳定性。”

二、冲突解决与协作

  • 描述一次你与开发团队在数据库设计上存在分歧的经历。你是如何解决这种技术分歧的？

回答建议：

  • 强调尊重对方意见，采用客观数据和测试结果来决策。
  • 鼓励双方协作讨论而非对抗。

示例回答：
“开发团队想采用一种简化的数据库设计，但我认为这可能导致未来扩展困难。我们进行了技术讨论和小规模的性能测试。测试结果证明了我的顾虑，于是我们共同调整了设计，平衡了开发效率和长期可维护性。”

  • 当管理层要求在不合理的短时间内完成数据库迁移时，你会如何回应和沟通？ 回答建议：

明确表达对目标的理解。
提供风险分析和可行的替代方案。

示例回答：
“我会首先表示理解管理层的紧迫感，然后清晰地列出短时间迁移可能带来的风险，比如数据丢失或业务中断。接着，我会提出更安全的时间表或分阶段迁移方案，确保业务连续性。”

  • 如何向固执的同事解释他们的SQL查询需要重构？请分享你的沟通策略。 回答建议：
  • 避免直接批评，采用客观数据和事实。
  • 提供具体的优化建议和理由。

示例回答：
“我通常会先肯定同事的工作成果，然后展示查询的性能数据和具体瓶颈所在。接着，我会提供具体的优化建议，并邀请他们共同测试优化后的结果，让他们亲眼看到性能提升。”

三、文档与知识共享

  • 你如何确保数据库文档既对技术人员有用，又能被非技术利益相关者理解？

回答建议：

  • 分层次创建文档，不同受众提供不同版本。
  • 使用图表、类比和简单文字描述技术内容。

示例回答： “我通常会创建两种版本的文档：技术版本详细描述数据库架构和配置细节；业务版本则采用图表和业务语言，解释数据库如何支持业务流程。”

  • 描述你创建过的最有效的数据库相关报告或演示，以及你是如何针对受众调整内容的。

回答建议：

  • 强调根据受众需求调整内容的能力。
  • 使用具体案例说明。

示例回答：
“我曾为高管制作了一份数据库性能趋势报告，使用直观的图表和业务指标，避免技术术语。而在面向技术团队的报告中，我则详细列出具体的SQL查询和执行计划。”

  • 如何向团队传达数据库最佳实践？请分享你的方法。

回答建议：

  • 举办培训或分享会，提供清晰的示例和原因。
  • 创建易于访问的知识库或文档。

示例回答： “我定期组织团队内的技术分享会，介绍数据库最佳实践，并提供具体示例。同时我也建立了内部Wiki，方便团队成员随时查阅。”

四、紧急情况沟通

  • 在数据库严重故障期间，你如何同时管理技术修复和利益相关者的沟通？

回答建议：

  • 指定专人处理沟通，避免混乱。
  • 提供定期更新和清晰的恢复预期。

示例回答：
“我通常会快速组建应急小组，指定一名成员专门负责与利益相关者沟通，提供定期更新，说明当前状况、预计恢复时间及影响范围。同时，我自己专注于技术修复。”

  • 当需要向高层管理人员报告数据泄露或安全事件时，你会包含哪些关键信息？

回答建议：

  • 事件概要、影响范围、已采取措施、下一步计划。
  • 提供清晰、客观的信息，避免技术细节过多。

示例回答： “我会清晰说明事件发生的时间、影响的数据范围、可能的业务影响、我们已采取的应急措施，以及下一步的整改计划和预防措施。”

  • 如何向用户解释计划外的数据库维护及其影响？

回答建议：

  • 提供简单清晰的解释，说明原因、影响和预期恢复时间。
  • 表达理解用户的不便，提供积极态度。

示例回答：
“我会简单说明维护原因，比如‘数据库出现意外问题，我们需要紧急修复以避免数据损失’，并明确告知用户预计的恢复时间，同时表达歉意并感谢他们的理解。”

五、战略沟通

  • 你如何向管理层证明投资于新数据库技术或升级的合理性？

回答建议：

  • 使用清晰的数据和ROI分析。
  • 强调业务收益和风险规避。

示例回答： “我会提供具体的数据支持，比如新技术如何提高性能、降低维护成本或减少业务风险，给出清晰的ROI分析和对比数据。”

  • 当需要说服团队采用新的数据库工具或流程时，你的沟通方法是什么？

回答建议：

  • 展示新工具的具体优势和成功案例。
  • 鼓励团队参与试用和反馈。

示例回答： “我会先展示新工具的优势和其他团队的成功案例，然后邀请团队成员参与小范围试用，收集反馈并共同决定是否推广。”

  • 如何向不同部门解释数据库资源分配决策？

回答建议：

  • 强调客观标准和公平性。
  • 提供透明的数据和决策依据。

示例回答：
“我会清晰说明资源分配的标准，比如业务优先级、影响范围和紧迫性，提供具体的数据和决策依据，确保不同部门理解决策的公平性和透明性。”

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布