数据库学习路径：从关系模型与 SQL 到复制、备份、连接池与选型

使用方式

这条路径解决“会写 SQL，但不敢碰生产库”的问题：先建立关系模型和查询反馈的直觉，再练慢查询、锁、复制、备份、连接池和迁移。适合后端开发、SRE 和需要独立维护数据库的运维工程师。

前置知识：会用 Linux 命令行，写过基本 SQL，能在本机跑 Docker。四个阶段按依赖排序，每阶段建议 3–4 小时；没有现成数据库的，用单容器 MySQL 8 和 PostgreSQL 16 起步。

先读：按顺序读，先弄清视图和日志能回答什么问题，再记参数。
再做：每个阶段都要留下一条 SQL、一次恢复或一次切换验证。
后测：合上页面，能说出“现象 → 看哪个视图 → 下一步动作”。

所有破坏性实验都在一次性容器或测试实例里做。生产库上禁止直接演练 KILL、pg_terminate_backend、主从切换和全量恢复；这些操作必须先有审批、备份和回滚路径。

阶段一：关系模型与 SQL

目标：能设计三张有关联的表，写出可解释执行计划的查询，并说清 MySQL 与 PostgreSQL 的差异边界。

阅读顺序：

mysql — MySQL 客户端连接、库表操作和常用管理命令。
psql — PostgreSQL 交互式客户端、元命令和事务控制。
mysql-cheatsheet — 建表、索引、事务和权限的常用语句。
postgres-cheatsheet — 数据类型、索引、EXPLAIN 和扩展。
psql-vs-mysql — 两种客户端和 SQL 方言的差异。
mysql-vs-mariadb — 分支差异，避免把 MariaDB 文档直接套到 MySQL。
postgres-vs-mongodb — 关系模型与文档模型的适用边界。
pg_stat_statements — 按 SQL 归一化统计执行次数和耗时。
initdb — 初始化实例、locale 和认证配置，理解数据库不是单个进程。

动手任务：在本机 Docker 起 MySQL 8 和 PostgreSQL 16，各建一张订单表，插入数据并对比 EXPLAIN。环境是本机 Docker，端口只绑定 127.0.0.1；密码是实验用的临时密码。风险是容器会占内存和磁盘，结束后删除容器和卷。

mkdir -p /tmp/db-lab
docker run -d --name lab-mysql -e MYSQL_ROOT_PASSWORD=labpass -p 127.0.0.1:3307:3306 mysql:8.0
docker run -d --name lab-pg -e POSTGRES_PASSWORD=labpass -p 127.0.0.1:5433:5432 postgres:16
sleep 15
docker exec -i lab-mysql mysql -uroot -plabpass <<'SQL'
CREATE DATABASE shop;
USE shop;
CREATE TABLE orders (id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, amount DECIMAL(10,2), created_at DATETIME, KEY idx_user_created (user_id, created_at));
INSERT INTO orders VALUES (1, 10, 99.00, NOW());
EXPLAIN SELECT * FROM orders WHERE user_id = 10 ORDER BY created_at DESC;
SQL
docker exec -i lab-pg psql -U postgres -c 'CREATE DATABASE shop;'
docker exec -i lab-pg psql -U postgres -d shop -c 'CREATE TABLE orders (id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, amount NUMERIC(10,2), created_at TIMESTAMPTZ);'
docker exec -i lab-pg psql -U postgres -d shop -c 'EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 10;'

过关标准：

能解释主键、唯一索引、普通索引和联合索引最左前缀的差别。
能看懂 EXPLAIN 中的扫描方式、行数估算和是否命中索引。
能说出 MySQL 与 PostgreSQL 在自增、大小写、事务隔离上的至少两处差异。
能在两个客户端里用事务回滚验证一条写操作没有留下脏数据。

阶段二：慢查询、锁与连接

目标：看到慢、卡、连接满时，能从执行计划、锁等待和连接状态定位到具体 SQL 或事务。

阅读顺序：

mysql-slow-query — slow log、EXPLAIN 和索引选择。
postgres-lock-wait — 锁等待视图和阻塞链。
mysql-deadlock — InnoDB 死锁日志和加锁顺序。
postgres-deadlock — 死锁检测、事务重试和隔离级别。
postgres-autovacuum — 表膨胀、冻结和 vacuum 参数。
postgres-wal-full — WAL 堆积、复制槽和归档失败。
mysql-connection-failed — 先分清网络、认证和连接数。
postgres-connection-failed — pg_hba、监听地址和连接数。
mysql-too-many-connections — 连接泄漏与池大小。
postgres-too-many-connections — 保留连接与空闲事务。

动手任务：先按 lab-mysql-deadlock 在一次性 MySQL 容器里复现死锁，再在 PostgreSQL 容器里制造一次锁等待并读阻塞链。环境是隔离容器；风险是锁会阻塞其他会话，绝不能在共享库执行，事后回滚事务并确认没有残留锁。

docker exec lab-mysql mysql -uroot -plabpass -e 'SHOW ENGINE INNODB STATUS\G' | sed -n '/LATEST DETECTED DEADLOCK/,/TRANSACTIONS/p'
docker exec -i lab-pg psql -U postgres -d shop <<'SQL'
CREATE TABLE accounts (id INT PRIMARY KEY, balance INT NOT NULL);
INSERT INTO accounts VALUES (1,100),(2,100);
SQL
docker exec -i lab-pg psql -U postgres -d shop -c "BEGIN; UPDATE accounts SET balance=balance-10 WHERE id=1; SELECT pg_sleep(15); COMMIT;" &
sleep 3
docker exec -i lab-pg psql -U postgres -d shop -c "SELECT pid, state, wait_event_type, wait_event, left(query,60) FROM pg_stat_activity WHERE state <> 'idle';"
docker exec -i lab-pg psql -U postgres -d shop -c "SELECT * FROM pg_locks WHERE NOT granted;"
wait

过关标准：

能根据 pg_stat_activity 和 pg_locks 指出阻塞链的源头会话。
能区分慢查询、锁等待和连接池耗尽三种不同的“慢”。
能读懂 MySQL 死锁日志中的两个事务、持有锁和等待锁。
能说出 autovacuum 跟不上时表和索引会发生什么，以及先看哪些指标。

阶段三：复制、高可用与备份

目标：能搭一主一从，判断复制延迟原因，并完成一次经过验证的恢复。

阅读顺序：

mysql-replication-lag — 位点差、SQL 线程和大事务。
postgres-replication-lag — replay_lag、复制槽和从库回放。
mysql-replication-broken — 复制中断后的错误定位与重建。
postgres-replication-broken — WAL 缺失、时间线和重新同步。
patroni — PostgreSQL 高可用、选主和自动切换。
orchestrator — MySQL 拓扑发现和主从切换。
pgbackrest — 全量/增量备份、WAL 归档和恢复。
wal-g — 对象存储备份和时间点恢复。
build-mysql-ha — 从零搭一套 MySQL 高可用。
build-postgresql-pitr — 按时间点恢复的完整演练。

动手任务：在 PostgreSQL 容器里做一次逻辑备份，恢复到新库并核对行数；如果已按 PITR 页面搭好归档，再验证 pg_stat_replication 和备份清单。环境是一次性容器，备份文件只写到 /tmp。风险是备份包含业务数据，必须限制文件权限；恢复只能写到新库，不能覆盖原库。

docker exec lab-pg pg_dump -U postgres -Fc shop > /tmp/db-lab/shop.dump
docker exec -i lab-pg createdb -U postgres shop_restore
docker exec -i lab-pg pg_restore -U postgres -d shop_restore < /tmp/db-lab/shop.dump
docker exec -i lab-pg psql -U postgres -d shop_restore -c 'SELECT count(*) FROM orders;'
docker exec -i lab-pg psql -U postgres -c 'SELECT client_addr, state, sent_lsn, replay_lsn, replay_lag FROM pg_stat_replication;'

过关标准：

能解释复制延迟是主库大事务、网络、从库回放还是资源不足导致。
能完成一次备份并在新库恢复，用行数或校验和证明数据一致。
能说出主从切换后应用连接、读写路由和旧主分别要怎么处理。
能指出“备份任务成功”和“数据可恢复”是两件不同的事。

阶段四：连接池、迁移与选型

目标：能选择连接池位置和大小，设计一次可回滚的迁移，并按访问模式而不是流行度选型。

阅读顺序：

pgbouncer — PostgreSQL 连接池模式、认证和池大小。
proxysql — MySQL 协议代理、读写分离和查询规则。
mysql-router — InnoDB Cluster 的路由与故障转移。
migrate-mysql-to-postgresql — 数据类型、SQL 方言和增量同步。
migrate-mysql57-to-mysql8 — 升级路径、认证插件和兼容性。
migrate-postgresql-major-version — pg_upgrade 与逻辑复制。
database-selection-roadmap — 按访问模式选择数据库。
pgvector-vs-qdrant — 向量检索选型，理解扩展与专用库的边界。

动手任务：对订单表做一次“只导结构”的迁移预检，列出 MySQL 专有语法和类型，再在 PostgreSQL 中核对版本与兼容项。环境是一次性容器；风险是结构导出本身安全，但不要把预检脚本直接当迁移脚本执行，更不能在生产库上跑。

docker exec -i lab-mysql mysqldump -uroot -plabpass --no-data --skip-comments shop > /tmp/db-lab/shop-schema.sql
grep -nE 'AUTO_INCREMENT|ENGINE=|`' /tmp/db-lab/shop-schema.sql | head -20
docker exec lab-mysql mysql -uroot -plabpass -e "SHOW VARIABLES LIKE 'character_set_server';"
docker exec -i lab-pg psql -U postgres -d shop -c 'SELECT version();'
docker exec -i lab-pg psql -U postgres -d shop -c '\d+ orders'

过关标准：

能回答连接池放在应用侧、Sidecar 还是数据库前，各自的失败模式是什么。
能列出迁移前必须核对的五类兼容项：类型、字符集、自增、事务语义和 SQL 方言。
能设计一次带回滚点的迁移，并说明如何验证数据一致。
能根据读写比例、事务要求和数据规模选择关系型、文档型或向量库。

常见误区

索引越多越快：写放大、锁范围和优化器选择都会变差，索引要按查询建。
慢查询就是缺索引：先看执行计划、数据分布、锁等待和连接池，不要盲加索引。
复制延迟都是网络问题：大事务、从库回放、复制槽和磁盘 IO 更常见。
有备份就等于能恢复：没有演练过的备份不可信，恢复时间和数据一致性都要验证。
连接池越大越好：数据库并发能力有限，池过大会把压力转移成排队和超时。
迁移就是把数据导过去：类型、字符集、自增、事务语义和应用 SQL 都要改。
选型看排行榜：先看访问模式、一致性要求和团队能否运维。

读完自测

合上页面，先回答，再展开对照。

一条 SQL 在测试环境很快，生产很慢，先查哪三件事？
复制延迟持续升高，如何判断是大事务、网络还是从库资源？
备份任务显示成功，为什么还不能说“有备份”？

答案

先对比执行计划和统计信息是否准确，再看数据量、索引选择性与表膨胀，最后查锁等待、连接池和实例资源，同时核对版本与参数差异。
看主库写入速率与位点差、replay_lag 和复制线程状态，结合大事务、从库 CPU/IO、复制槽和网络 RTT 判断；只看 Seconds_Behind_Master 容易误判。
成功只证明备份文件已生成，未证明可恢复；必须在隔离环境实际恢复，核对行数、校验和、约束和 PITR 目标时间。

数据库学习路径：从关系模型与 SQL 到复制、备份、连接池与选型

使用方式

阶段一：关系模型与 SQL

阶段二：慢查询、锁与连接

阶段三：复制、高可用与备份

阶段四：连接池、迁移与选型

常见误区

读完自测

相关条目