一、为什么 PostgreSQL 权限容易混乱 很多人刚接触 PostgreSQL 权限时，会下意识拿 MySQL 来类比： 1 2 MySQL：用户 + 数据库 + 表权限 PostgreSQL：角色 + 数据库 + schema + 对象权限 + 默认权限 + RLS PostgreSQL 的权限体系更细，灵活性也更高，但如果没有先建立层级概念，就很容易出现下面这些问题： 用户明明有表的 SELECT 权限，却仍然提示 permission denied for schema 创建了只读用户，但用户还能在 public schema 里建表 给了 GRANT SELECT ON ALL TABLES，新建表却无法查询 不知道 CREATE USER 和 CREATE ROLE 到底有什么区别 不知道 public schema 和 PUBLIC 角色是不是同一个东西 删除用户时提示仍然有对象依赖，无法 DROP ROLE 要理解这些问题，必须先把 PostgreSQL 的权限模型分层看清楚。 二、PostgreSQL 的对象层级 PostgreSQL 常见层级如下： 1 2 3 4 5 6 7 8 PostgreSQL 实例 └── database └── schema ├── table ├── view ├── sequence ├── function └── type 一个 PostgreSQL 实例里可以有多个数据库。每个数据库内部又可以有多个 schema。表、视图、序列、函数等对象通常位于某个 schema 下面。 ...

一、为什么 PostgreSQL 有这么多索引类型 很多人刚接触 PostgreSQL 索引时，会自然地用 MySQL B+Tree 的思路去理解所有索引：索引就是按字段值排序，然后通过树结构快速定位行。 这个理解对 B-tree 索引基本成立，但不能覆盖 PostgreSQL 的全部索引体系。PostgreSQL 支持多种索引访问方法，常见的有： B-tree：默认索引，适合等值、范围、排序。 Hash：适合等值查询，实际使用频率低于 B-tree。 GIN：通用倒排索引，适合数组、JSONB、全文搜索。 GiST：通用搜索树，适合范围、空间、相似、最近邻。 SP-GiST：空间分区搜索树，适合某些非平衡分布的数据结构。 BRIN：块区域索引，适合超大表且数据物理顺序相关的场景。 本文重点解释最常被问到、也最容易混淆的四类： B-tree GIN GiST BRIN 尤其会重点讲清楚： GIN 的 posting list 和 posting tree 是什么。 GIN 找到 TID 后如何回表。 多个高频 key 的 posting tree 是否会占用很大空间。 GiST 和 BRIN 都像“摘要索引”，但区别在哪里。 千万级、亿级表上应该如何选择索引。 二、先理解 PostgreSQL 的行位置：TID / CTID 在理解 GIN、GiST、BRIN 之前，要先理解 PostgreSQL 索引里常见的一个概念：TID。 TID 可以理解为一行数据在 heap 表里的物理位置，形式类似： 1 (block number, offset number) 也就是： ...

1. 本文范围与参考 本文基于 MySQL 8.0（以 8.0.35 为讨论基线）InnoDB 的 MVCC（多版本并发控制） 与 一致性读 语义，聚焦： ReadView 各字段含义及与 全局事务 id 的关系； 聚簇索引行 与 undo 版本链； 本事务修改 与 他事务版本 在可见性判断上的不同路径； RR（REPEATABLE READ） 与 RC（READ COMMITTED） 下 ReadView 生命周期 的差异； 事务 id 尚未分配（常为 0） 时，不会以 0 进入活跃事务 id 列表；分配后才以正式 id 参与活跃事务管理。 官方文档入口： InnoDB Multi-Versioning Consistent Nonlocking Reads Transaction Isolation Levels 以下对源码字段的命名以教学常用归纳为主，与 storage/innodb 中结构体字段名可能略有出入，语义与手册一致。 2. 为什么需要 MVCC InnoDB 在不加锁的 SELECT（一致性非锁定读）下，需要回答： 当前会话应该看到该行哪一个已提交/未提交版本？ 若直接读最新聚簇记录，会与并发事务的未提交写冲突；若全部加锁，并发度下降。MVCC 通过保留历史版本（undo）与可见性判断（ReadView），使读操作在多数场景下不阻塞写，写不阻塞读（在可接受的隔离级别语义下）。 3. 一行数据与 undo 版本链 3.1 聚簇索引记录上的关键信息（概念） 对聚簇索引上一行（简化）： ...

本文由《MySQL 内部两阶段提交机制深度解析》与《MySQL 内部两阶段提交（2PC）深度解析》合并整理，去重后按「背景 → 流程 → 恢复 → 性能 → 参数 → 扩展 → 实战」组织。 一、背景：为什么需要内部两阶段提交 MySQL 的存储架构分为两层： Server 层：负责 SQL 解析、优化、执行，以及 binlog 的写入。 引擎层（InnoDB）：负责数据页的持久化与 redo log 的写入。 两层各自维护一套日志。事务提交时必须让两套日志在语义上一致；若简单「先写一个、再写另一个」而不协调，崩溃后极易出现主从不一致。MySQL 在引擎内部采用内部两阶段提交（Internal 2PC）（常以 XID 关联 redo 与 binlog），而不是分布式事务里的外部 2PC 协调者模型。 1.1 两个日志的分工 日志 层级 作用 常见格式/形态 redo log InnoDB 崩溃恢复，保证已提交变更可重做 物理日志（页修改） binlog MySQL Server 主从复制、备份与按时间点恢复（PITR） 逻辑日志（语句）或 ROW 为什么不能只保留一种日志？ redo log 与 InnoDB 紧耦合，其它存储引擎未必具备。 binlog 属于 Server 层，与引擎解耦，复制与备份都依赖它。 历史演进上 binlog 更早出现，redo 后补；二者长期并存。 1.2 若无 2PC：两种顺序都会导致主从不一致 先 redo、后 binlog，中间崩溃： ...

Orchestrator：MySQL 高可用复制拓扑管理与故障切换实战指南 一、引言 在 GTID 时代，MHA 显得有些落伍了，因此大家会优先选择了由 GitHub 团队开源的 MySQL 高可用组件 Orchestrator。 Orchestrator 是 MySQL 复制拓扑 HA，管理和可视化工具，目前 Booking、Github、Google/Vitess 等公司在使用。 项目地址：https://github.com/openark/orchestrator 二、Orchestrator 的特点 组件自身高可用：Orchestrator 节点之间通过 Raft 协议实现数据一致性即高可用性。 拓扑发现：主动搜寻拓扑并进行映射，读取基本的 MySQL 信息，例如复制状态和配置。 拓扑重组：可以通过 binlog file:position, GTID, Pseudo GTID, Binlog Servers 复制规则，对 MySQL 拓扑进行重组。 故障恢复：根据从拓扑本身获得的信息，它可以识别各种故障情况。可以手动或自动进行故障恢复，故障恢复前后都有对应的 Hook，比较灵活。 多种操作方式：提供了三种操作方式，API 接口调用、命令行操作、Web 页面调用。 其具有如下主要功能： 可以自动发现 MySQL 集群，通过 MySQL 节点信息获取相关 slave 的信息，进而获取整个集群拓扑结构并将信息存储在后端数据库中。 基于复制集管理规则，提供自由重构复制集拓扑结构的功能，如将某个 slave 节点移动到其他 master 下。 自动探测故障并采取相应恢复措施，实现 MySQL 服务高可用的目的，减少人工介入。 提供 Web UI，API 以及命令行等入口操作，方便用户监控和操作集群。 三、三大核心特性 Orchestrator 还有三个重要特性： 3.1 Discover（发现） Orchestrator 主动搜寻 MySQL 拓扑并进行映射。它能读取基本的 MySQL 信息，例如复制状态和配置。即使遇到故障，也可以为 MySQL 环境的拓扑提供流畅的可视化效果，包括复制问题。 ...

1. 为什么需要闪回 在生产环境中，误操作是不可避免的。常见场景包括： 运维人员执行 DELETE 时忘记加 WHERE 条件 业务代码 Bug 导致批量 UPDATE 覆盖错误数据 开发在生产库手工测试后未回滚 传统恢复方式是全量备份 + binlog PITR（Point-In-Time Recovery），但当数据库体量达到数百 GB、误操作仅涉及几十行时，这种方式的时间成本极高。 闪回（Flashback） 是针对这类场景的轻量级解决方案：直接从 binlog 中解析出误操作涉及的行变更，生成逆向 SQL，精准还原被影响的数据，整个过程可在秒级到分钟级内完成。 2. 闪回的前提：ROW 格式 binlog 闪回工具的核心依赖是 binlog 的 ROW 格式。MySQL binlog 有三种格式： 格式 记录内容 闪回可用性 STATEMENT 记录原始 SQL 语句 ❌ 不可用（无行级前后镜像） ROW 记录每行的变更前后值 ✅ 可用 MIXED 自动选择 STATEMENT 或 ROW ⚠️ 部分可用 ROW 格式下，每个 DML 操作都会记录： DELETE：记录被删除行的完整数据（before image） INSERT：记录新插入行的完整数据（after image） UPDATE：同时记录修改前（before image）和修改后（after image） 闪回的原理正是利用这些镜像信息： DELETE → 反转为 INSERT（用 before image 构造） INSERT → 反转为 DELETE（用 after image 构造） UPDATE → 反转为 UPDATE SET 旧值 WHERE 新值 2.1 确认并设置 binlog 格式 1 2 3 4 5 -- 查看当前格式 SHOW VARIABLES LIKE 'binlog_format'; SHOW VARIABLES LIKE 'binlog_row_image'; -- 确保为 ROW + FULL（my.cnf 中配置） 1 2 3 4 # /etc/my.cnf [mysqld] binlog_format = ROW binlog_row_image = FULL # 必须为 FULL，否则 UPDATE/DELETE 缺少前镜像 注意：binlog_row_image 默认值在 MySQL 5.6+ 即为 FULL，但仍需显式确认，部分精简配置可能设为 MINIMAL，会导致闪回工具无法获取完整的前镜像数据。 ...

前言 数据库监控是 DBA 日常工作的基石。一个成熟的监控体系不仅能在故障发生时快速定位根因，更重要的是能在问题恶化前发出预警，实现从被动响应到主动防御的转变。 MySQL 通过 SHOW GLOBAL STATUS、SHOW GLOBAL VARIABLES、information_schema、performance_schema 以及 sys 库暴露了数百个运行时指标。面对如此庞杂的信息，关键不在于"监控的越多越好"，而在于理解每个指标的物理含义、相互关联以及在不同业务场景下的合理阈值。 本文将围绕以下八个维度，对 MySQL 监控指标体系进行系统性梳理： 维度 核心关注点 服务器级指标 实例存活性、吞吐量、负载趋势 连接与线程 并发压力、连接池健康度、异常断连 临时对象与慢查询 SQL 质量、优化器行为 锁与并发 表锁/行锁竞争、死锁频率 网络流量 带宽消耗、数据传输规模 InnoDB Buffer Pool 内存命中率、I/O 压力 文件与表缓存 文件描述符、表缓存效率 复制拓扑 主从健康、延迟、数据一致性 约定：本文中所有 SHOW GLOBAL STATUS 返回的变量统一称为"状态变量"（Status Variable），SHOW GLOBAL VARIABLES 返回的称为"系统变量"（System Variable）。状态变量为累计值或瞬时值，系统变量为配置参数。 1. 服务器级指标 服务器级指标反映 MySQL 实例的整体运行状态和负载趋势，是监控体系的第一道防线。 1.1 Uptime —— 实例存活时间 1 SHOW GLOBAL STATUS LIKE 'Uptime'; 属性 说明 类型 状态变量（瞬时值） 单位 秒 含义 自 MySQL 服务启动以来经过的秒数 监控价值： ...

1. 基准测试的边界认知 在使用 sysbench 之前，必须先明确基准测试能回答什么、不能回答什么，否则很容易得出错误结论。 能回答的问题： 当前硬件和配置下，MySQL 的 TPS/QPS 上限是多少 两套配置方案之间的相对性能差异（如调整 innodb_buffer_pool_size 前后） 某次参数调整或版本升级后，性能是否有可量化的提升或退步 目标并发量下，响应延迟是否在可接受范围内 不能回答的问题： 生产环境的真实性能（基准负载是理想化的，生产 SQL 复杂得多） 长期运行稳定性（基准测试通常持续分钟级） 热点、锁竞争、主从延迟等业务相关的性能问题 sysbench 的结果是性能的参考基线，而非生产能力的直接映射。同一套硬件，sysbench 跑出 5 万 TPS，生产复杂查询下可能只有几千 QPS。 2. 安装 1 2 3 4 5 6 7 8 9 # 添加 Percona yum 源 curl -sSL https://repo.percona.com/yum/percona-release-latest.noarch.rpm -o percona-release.rpm rpm -ivh percona-release.rpm # 安装 sysbench yum install -y sysbench # 验证版本 sysbench --version 安装后路径：主程序 /usr/bin/sysbench，测试脚本 /usr/share/sysbench/。 3. 第一阶段：数据准备（prepare） 3.1 创建测试库与专用账号 1 2 3 4 5 CREATE DATABASE sbtest CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER 'sbtest'@'%' IDENTIFIED BY 'SbTest@2026'; GRANT ALL PRIVILEGES ON sbtest.* TO 'sbtest'@'%'; FLUSH PRIVILEGES; 不要用 root 账号跑压测，测试库与业务库严格隔离。 ...

1. 什么是企业级数据库巡检 在很多团队的认知里，“巡检"等同于跑一段脚本检查备份文件是否存在、大小是否合理。这种做法只能证明备份文件存在，无法证明备份可用。 企业级巡检的本质定义是： 将备份真正还原到一套临时实例，执行读写验证查询，以可量化的结果证明该份备份在灾难发生时能够被成功恢复并投入使用。 这个定义带出了巡检体系的三个核心观念： 1.1 核心观念一：备份的价值在还原那一刻才被证明 备份文件完整存在 ≠ 备份可用。常见的备份失效场景包括： 物理备份工具版本与恢复工具版本不匹配，导致元数据无法解析 备份集内部页损坏，但文件大小正常，checksum 未被校验 加密备份的密钥已轮换，旧备份无法解密 全量备份完整，但增量链断裂，PITR 无法回放到目标时间点 备份文件被意外截断，gzip/压缩格式损坏，无报错但无法完整解压 只有实际恢复并验证业务查询，才能发现上述隐患。 1.2 核心观念二：巡检必须自动化且覆盖率可量化 人工巡检有两个根本性缺陷：频率不足和覆盖率无法保证。企业数据库实例少则几十、多则数百，人工无法做到每份备份都验证。 自动化巡检体系需要能回答： 过去 N 天内，哪些实例的备份从未被验证过？ 当前巡检覆盖率是多少（已验证备份数 / 总备份数）？ 哪些实例上次验证失败，失败原因是什么？ 1.3 核心观念三：巡检结果要形成闭环 巡检不是一次性的任务，而是一个持续运转的反馈循环： 备份产生 -> 纳入巡检队列 -> 按优先级调度 -> 恢复验证 -> 结果记录 ^ | +------- 失败告警 -> 修复备份策略 <-----------------------+ 失败的巡检必须触发告警并推动根因修复，而不是仅仅记录日志。 2. 企业级巡检架构的三层设计 一套完整的企业级巡检体系通常由三层组成：调度层、执行层、收尾层。 2.1 调度层：任务规划与优先级编排 调度层负责回答"本周应该巡检哪些备份、按什么顺序巡检”。 任务触发：通常采用定时触发（如每周一固定时间），扫描过去一个周期内产生的所有备份集，过滤掉已经验证过的，生成本轮待巡检列表。 优先级策略：并非所有备份都同等重要。常见的优先级维度： 优先级因子 说明 实例数据量 1T 级实例的备份失效影响远大于 96G 实例，优先验证大实例 上次巡检时间 长期未验证的备份优先调度 实例业务等级 核心业务库（如订单、支付）优先于边缘业务库 备份类型 全量备份优先于增量备份 历史失败记录 曾经验证失败的实例需要更高频次的巡检 资源规划：调度层需要根据待巡检备份的大小，预估所需的临时存储和计算资源，避免同时恢复多个 TB 级备份打爆存储或带宽。 ...

1. 概述：可传输表空间 在 InnoDB 且 innodb_file_per_table=ON（MySQL 5.6.6 起默认开启）时，每张表的数据独立存储于一个 .ibd 文件。MySQL 提供了一对 DDL 语句，允许在表定义（数据字典元数据）与磁盘上的 .ibd 文件之间建立或解除关联： ALTER TABLE tbl DISCARD TABLESPACE：解除当前表与 .ibd 的关联，并物理删除该文件。表结构仍保留在数据字典，但无任何数据文件。 ALTER TABLE tbl IMPORT TABLESPACE：在表定义已存在的前提下，将一份外部 .ibd 文件挂接到该表，InnoDB 完成内部一致性校验。 二者组合形成**可传输表空间（Transportable Tablespaces）**机制，典型用途：替换损坏的 .ibd 文件、跨实例搬运表数据、利用物理备份快速恢复单表。 版本说明：MySQL 5.6 起逐步完善，MySQL 8.0 引入 SDI（Serialized Dictionary Information）嵌入 .ibd，元数据校验方式有所演进。 2. 标准操作流程 2.1 源端：导出一致性快照 1 FLUSH TABLES tbl FOR EXPORT; 执行效果：将 buffer pool 中该表的脏页全部刷入 .ibd，使文件处于可安全拷贝的一致状态；MySQL 5.6/5.7 同时生成 .cfg 文件用于目标端校验；MySQL 8.0 中 .ibd 内已嵌入 SDI。 在持锁期间复制文件（保持当前会话不关闭）： 1 2 cp /var/lib/mysql/db_name/tbl.ibd /backup/ cp /var/lib/mysql/db_name/tbl.cfg /backup/ 复制完成后解锁：UNLOCK TABLES; ...