03.11 知识点全清单
所属:全栈开发面试题库 / 学习路径 / 03 MySQL 与 PostgreSQL
| 模块 | 知识点 | 必须掌握到什么程度 | 面试/实战常见问题 |
|---|---|---|---|
| SQL 基础 | SELECT、WHERE、ORDER BY、LIMIT 语法 | 能独立写出正确的单表查询并解释过滤顺序 | 分页查询为什么深翻页变慢 |
| SQL 基础 | INNER / LEFT / RIGHT JOIN 语义 | 能区分内连接与外连接对结果集的影响 | 两表关联时 LEFT JOIN 后条件写 ON 与 WHERE 的区别 |
| SQL 基础 | 聚合函数与 GROUP BY、HAVING | 能按维度聚合并对聚合结果过滤 | WHERE 与 HAVING 的执行顺序差异 |
| SQL 基础 | 子查询与派生表 | 能在相关子查询与非相关子查询间选择 | 子查询改写为 JOIN 后的性能变化 |
| SQL 基础 | CTE(WITH 子句) | 能写出递归与非递归 CTE 并读懂执行结构 | CTE 与子查询在优化上的差异 |
| SQL 基础 | 窗口函数 | 能使用排序、分区、累计聚合类窗口函数 | 分组取每组前 N 名的写法 |
| 建模与范式 | 第一至第三范式 | 能按范式判断表结构是否合理并指出问题 | 范式与反范式各自解决什么问题 |
| 建模与范式 | 反范式与冗余字段 | 能判断哪些读多写少场景适合冗余 | 冗余字段带来的一致性维护代价 |
| 建模与范式 | 主键选择 | 能对比自增整数、UUID、分布式 ID 的取舍 | UUID 做主键对页分裂与索引大小的影响 |
| 建模与范式 | 外键取舍 | 能说明应用层约束与数据库外键的权衡 | 高并发系统为什么常去掉外键约束 |
| 建模与范式 | 字段类型选择 | 能按值域与精度选择整数、字符串、日期、数值类型 | 金额字段为什么不能用浮点类型 |
| 建模与范式 | 大字段与冷热分离 | 能识别应拆分的大字段并设计冷热表 | 大字段放在主表为什么拖垮查询 |
| 建模与范式 | 字符集与排序规则 | 能说清字符集与 collation 对存储、排序、唯一约束的影响 | 大小写不敏感排序导致的重复值问题 |
| 索引与执行计划 | B+Tree 与页结构 | 能描述B+Tree 的层级、页与叶子链表结构 | 为什么数据库索引用 B+Tree 而不是二叉树 |
| 索引与执行计划 | 聚簇索引与二级索引 | 能区分 InnoDB 聚簇索引组织方式与二级索引结构 | 一张表没有主键时 InnoDB 怎么组织 |
| 索引与执行计划 | 回表与覆盖索引 | 能判断查询是否触发回表并设计覆盖索引 | 哪些索引能做到不回表 |
| 索引与执行计划 | 联合索引最左前缀 | 能判断给定索引列顺序下哪些条件命中 | 联合索引列顺序按什么原则排 |
| 索引与执行计划 | 索引选择性 | 能计算区分度并评估单列索引价值 | 低区分度列为什么不适合单独建索引 |
| 索引与执行计划 | 索引下推 ICP | 能说明 ICP 把过滤条件下推到存储引擎层的作用 | 没有 ICP 时二级索引查询多做哪些回表 |
| 索引与执行计划 | 统计信息与成本模型 | 能理解优化器基于行数与成本估算选计划 | 统计信息过期为什么会选错索引 |
| 索引与执行计划 | EXPLAIN / EXPLAIN ANALYZE 解读 | 能逐列解读访问类型、扫描行数、成本与实际耗时 | 执行计划里出现全表扫描的常见原因 |
| 索引与执行计划 | 索引失效场景 | 能列举函数包裹、隐式转换、前导通配等失效情形 | 对索引列做函数运算为什么走不到索引 |
| 事务与并发 | ACID 四项保证 | 能分别解释原子性、一致性、隔离性、持久性的落地机制 | 一致性由哪些机制共同保证 |
| 事务与并发 | 四种隔离级别 | 能说明各级别解决的脏读、不可重复读、幻读问题 | 当前业务应选哪个隔离级别 |
| 事务与并发 | MySQL MVCC 实现 | 能说清 undo log、版本链与 Read View 的工作方式 | 可重复读级别下 Read View 何时生成 |
| 事务与并发 | PostgreSQL MVCC 实现 | 能说清 xmin、xmax 与事务快照的版本判断方式 | PostgreSQL 更新一行为什么产生新版本元组 |
| 事务与并发 | 快照读与当前读 | 能区分普通 SELECT 与加锁读的行为 | 哪些语句属于当前读 |
| 事务与并发 | 幻读处理 | 能描述幻读现象及两个库的处理手段 | 快照读与当前读对幻读的表现差异 |
| 事务与并发 | 死锁检测与排查 | 能定位死锁会话并解读死锁日志 | 两个事务加锁顺序相反为什么死锁 |
| 锁 | MySQL 表锁与行锁 | 能区分表级锁与行级锁的粒度与场景 | 什么操作会升级到表级锁 |
| 锁 | 意向锁 | 能说明意向锁存在的目的与兼容性判断 | 意向锁与行锁为什么不冲突 |
| 锁 | 元数据锁 MDL | 能理解 DDL 与 DML 之间的 MDL 互斥 | 线上改表为什么可能被长事务堵住 |
| 锁 | PostgreSQL 表级与行级锁 | 能区分锁模式与适用场景 | SELECT FOR UPDATE 与普通读的锁行为差异 |
| 锁 | 咨询锁 advisory lock | 能理解应用层命名锁的用途 | 定时任务互斥为什么用咨询锁 |
| 锁 | 乐观锁与悲观锁 | 能在应用层写出版本号 CAS 与加锁两种实现 | 冲突频繁时乐观锁为什么代价变高 |
| 日志与持久化 | MySQL redo log | 能说明 redo log 保证崩溃后已提交事务不丢的原理 | redo log 与数据页写入的先后关系 |
| 日志与持久化 | MySQL undo log | 能说明 undo log 支持回滚与版本链的双重作用 | 长事务为什么阻碍 undo 回收 |
| 日志与持久化 | MySQL binlog 三种格式 | 能对比 statement、row、mixed 的记录粒度与风险 | 主从复制为什么默认用 row 格式 |
| 日志与持久化 | 两阶段提交与组提交 | 能描述 redo 与 binlog 写入的协调过程 | 两阶段提交解决的一致性问题 |
| 日志与持久化 | PostgreSQL WAL | 能说明 WAL 先写日志后写数据页的持久化机制 | 崩溃后如何通过 WAL 做 REDO 恢复 |
| 日志与持久化 | checkpoint 与 full_page_writes | 能理解 checkpoint 的刷新节奏与页写满的作用 | 频繁 checkpoint 为什么影响写入性能 |
| 备份与恢复 | 逻辑备份 | 能使用 mysqldump 与 pg_dump 导出并理解限制 | 大库逻辑备份为什么慢且锁风险高 |
| 备份与恢复 | 物理备份 | 能理解 XtraBackup 与 pg_basebackup 的文件级备份方式 | 物理备份恢复对版本与目录结构的要求 |
| 备份与恢复 | PITR 时间点恢复 | 能基于 binlog 或 WAL 恢复到指定时刻 | 误删数据后恢复流程包含哪些步骤 |
| 备份与恢复 | 备份策略与演练 | 能制定全量加增量加日志归档的策略并定期演练 | 从未做过恢复演练的备份有什么风险 |
| 复制与高可用 | MySQL 主从复制原理 | 能描述 dump 线程、IO 线程、SQL 线程的协作 | 主从复制默认是同步还是异步 |
| 复制与高可用 | GTID 与半同步复制 | 能说明 GTID 定位事务与半同步确认机制 | 半同步复制牺牲什么换取什么 |
| 复制与高可用 | 并行复制与延迟治理 | 能列举延迟来源(大事务、网络、IO 瓶颈)与治理手段 | 从库延迟高时先排查哪几类原因 |
| 复制与高可用 | 组复制 MGR | 能理解多主一致性与故障成员剔除的基本概念 | MGR 与传统主从复制的定位差异 |
| 复制与高可用 | PostgreSQL 流复制 | 能理解 WAL 传输与 standby 回放流程 | 流复制同步级别对主库性能的影响 |
| 复制与高可用 | PostgreSQL 逻辑复制 | 能配置 publication / subscription 并理解粒度 | 逻辑复制适合跨版本、跨平台与部分表订阅 |
| 复制与高可用 | Patroni / Repmgr / Pgpool-II | 能描述各组件职责:自动选主、配置分发、连接代理 | 高可用切换由谁发起、谁决定新主 |
| 复制与高可用 | 脑裂与 fencing | 能理解双主风险与隔离旧主的措施 | 为什么必须在切换时阻断旧主写入 |
| 读写分离与分片 | 代理式与客户端式 | 能对比两种路由方式的部署成本与语言绑定 | Proxy 与 JDBC 客户端各自的适用场景 |
| 读写分离与分片 | ShardingSphere 与 Vitess | 能说清两个分片中间件的定位与接入方式 | 分片中间件能解决哪些跨分片问题 |
| 读写分离与分片 | 分片键选择 | 能按查询模式、数据分布与扩容方向选分片键 | 分片键选择不当会造成哪些热点 |
| 读写分离与分片 | 跨分片 JOIN 与聚合 | 能理解应用层组装与中间层聚合的代价 | 跨分片排序分页为什么复杂 |
| 读写分离与分片 | 分布式 ID | 能对比雪花、号段、数据库序列方案 | 时钟回拨对雪花 ID 的影响 |
| 读写分离与分片 | 扩容与双写迁移 | 能描述双写、追平、校验、切流的迁移步骤 | 迁移期间如何校验新旧分片数据一致 |
| 性能诊断 | 慢查询日志与统计视图 | 能开启慢日志并从 pg_stat_statements 定位高频慢 SQL | 排查慢查询先看耗时还是先看执行计划 |
| 性能诊断 | 锁等待与长事务排查 | 能通过活动视图找到阻塞链与长事务源头 | 长事务为什么是性能与膨胀问题的共同源头 |
| 性能诊断 | 连接数与内存参数 | 能根据规格评估最大连接数与内存结构占用 | 连接数设置过大为什么反而降低吞吐 |
| 容量规划与运维 | 核心内存参数 | 能配置 buffer pool、shared_buffers、work_mem、effective_cache_size 的经验值 | work_mem 过大为什么可能导致内存溢出 |
| 容量规划与运维 | 在线 DDL | 能理解在线变更的锁风险与工具辅助手段 | 大表加索引在高峰期有什么风险 |
| 容量规划与运维 | 表膨胀与 VACUUM | 能理解 PostgreSQL 死元组积累与 VACUUM、autovacuum 的作用 | 膨胀会让哪些查询与索引变慢 |
| 容量规划与运维 | 监控指标与升级迁移 | 能列出核心指标清单并制定版本升级步骤 | 升级前需要做哪些兼容性检查 |
| 应用层搭配 | 连接池与 ORM | 能说明池化参数与 ORM 生成 SQL 的审查要点 | N+1 查询在 ORM 中如何产生与规避 |
| 应用层搭配 | 迁移工具与事务边界 | 能把 schema 变更纳入迁移流程,说清事务边界与跨库事务 | 跨库操作为什么不能依赖本地事务保证一致 |