0-数据库 · 咖啡猫的工作空间

关系数据库设计理论：函数依赖、范式、ER图
SQL分类：DDL（定义修改表结构）、DML（操作表数据）、DQL（select查询）、DCL（用户权限控制）、TCL（事务控制）
事务与并发控制

事务特性：A（原子性，通过undo_log日志实现）C（一致性）I（隔离性，即并发事务互补干扰，通过锁+MVCC实现）D（持久性，通过redo_log实现）
并发一致性问题：脏读（其他事务未提交的数据且回滚导致的现象）、不可重复读（同一个事务内，两次读取同一行数据，结果不一致）、幻读（同一个事务内，两次执行相同条件的范围查询，结果集的记录数量/内容发生变化。其他事务提交，数据发生了新增或删除操作）、丢失更新（一个事务内，数据被其他事务更新，导致数据不一致。不靠隔离级别解决，通过乐观锁/悲观锁/分布式锁解决）
隔离级别和实现机制：读未提交READ UNCOMMITTED（允许一个事务读取其他事务尚未提交的数据。实现机制：MVCC策略（不生成 ReadView，直接读最新页）+ 锁（仅行写锁））、读已提交READ COMMITTED（一个事务只能读取其他事务已经提交的数据。实现机制：MVCC策略（每次select重新生成ReadView）+ 锁（仅Read Lock））、可重复读REPEATABLE READ（在同一个事务内，多次读取同一行数据，结果始终一致，即使其他事务已修改并提交。这是MySQL默认隔离级别，保证事务内读不变的，多数场景中使用。实现机制：MVCC策略（事务内首次select生成ReadView并全程复用）+ 锁（next-key lock））、串行化SERIALIZABLE（强制事务完全串行执行，彻底隔离。物理上高度并发，逻辑上保证串行效果。能不用就不用）。实现机制: MVCC策略（禁用 MVCC 一致读，全走当前读）+ 锁（所有读加共享锁，写加排他锁），属于“用锁换一致”的极端策略。）
总结：脏读 → 读未提交（RC 防）、不可重复读 → 同行值变（RR 防）、幻读 → 范围行数变（RR+Next-Key 锁防）、丢失更新 → 并发写覆盖（靠锁/版本号，不靠隔离级别）

锁机制

按粒度：全局锁（全库只读）、表级锁（表锁、元数据锁：访问表时自动加，防止DDL与DML冲突、意向锁：表级标记，快速判断表中是否有行被加锁，避免全表扫描检查、自增锁：控制自增主键分配）、行锁（记录锁：锁单条索引记录、间隙锁：锁索引间隙，防幻读、临键锁：Record + Gap，记录锁和间隙锁的组合，RR级别默认）
按模式：共享锁（允许多个事务同时读，阻塞写）、排他锁（独占资源，阻止其他读写）
意向锁：表级锁，标记表中是否有行被加锁，避免全表扫描检查。事务对行加 X锁（独占锁）前，必须先对表加 IX锁（只记录“意图”，本身不阻塞任何读写操作）；事务对行加 S锁（共享锁）前，必须先对表加 IS锁（只记录“意图”，本身不阻塞任何读写操作）。
InnoDB行级锁算法：Record Lock（记录锁）、Gap Lock（间隙锁）、Next-Key Lock（临建锁，同时锁住记录 + 左侧间隙，形成左开右闭区间 (gap, record]）

MVCC多版本并发控制

核心原理与作用：读写不互斥，写操作创建新版本，读操作读取历史快照，实现读已提交 (RC) 和可重复读 (RR) 隔离级别。
InnoDB的实现机制：隐藏字段（每行数据自动添加：DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID）、ReadView（InnoDB 内部的数据结构，包含：活跃事务 ID 列表、最小/最大事务 ID、创建者事务 ID 等。RC：每次 SELECT 前生成，RR：事务首次 SELECT 时生成）
undo_log：记录每条SQL语句的修改数据，实现事务回滚（作用：事务回滚——将数据恢复到修改前状态、MVCC——提供历史版本快照供读操作使用）
MVCC 只管 SELECT 快照。UPDATE/DELETE 的并发覆盖（丢失更新）必须靠锁或版本号

MySQL 索引

索引结构：B+树（InnoDB引擎默认，适用场景：主键、普通索引、唯一索引、联合索引、排序/分页）、Hash索引（适用于等值查询）、全文索引（底层结构基于倒排索引，将文本分词 → 词项映射到文档 ID 列表）、空间索引（R-Tree 索引）、前缀/函数/联合索引（底层结构基于B+树）
B+树特点与优势：更少的磁盘IO（非叶子节点只存key，单页可存更多索引项，树高更低，3-4层可以存千万级数据）、范围查询高效（叶子节点双向链表，顺序遍历无需回溯）、查询稳定（仅叶子节点存数据，所有查询都需要走到叶子节点，时间复杂度一致）
扫描优化：当预估扫描行数 > 全表行数的 20%~30%（经验值），会放弃 B+ 树链表，改走全表顺序扫描。
索引分类（按功能）：主键索引、唯一索引（防重复，强一致性）、普通索引/联合索引（纯加速查询，无约束）、前缀索引（低频使用，节省空间、牺牲部分区分度）、空间索引/全文索引（基本不使用，一般会使用其他数据库）
索引优化：最左前缀原则（联合索引中）、索引下推（预先过滤索引中不满足条件的记录）、覆盖索引（即索引字段要包含查询字段）
索引失效场景：违反最左前缀（查询条件不使用联合索引的最左边字段）、函数/运算操作、类型隐式转换（根据字段类型，如字符类型查询值加引号保持类型一致）、模糊查询左匹配、OR连接条件、数据分布倾斜、使用 != 或 IS NOT NULL
索引优化建议：选择区分度高的列（区分度 = 不同值数量/总行数，>0.1，建议建索引）、联合索引字段顺序（高频查询字段在前，区分度高的字段在前）、避免过度索引、前缀索引长度、定期分析（ANALYZE TABLE 更新统计信息，OPTIMIZE TABLE 整理碎片）

SQL执行与优化

SQL执行流程：客户端请求 -> 连接器（权限校验+连接管理）-> 查询缓存（8.0已移除,缓存命中则返回）-> 分析器（词法分析：识别关键字、表名、列明、语法分析：构建语法树，检查语法正确性） -> 优化器（选择索引，确定连接顺序，生成最优执行器） -> 执行器（权限校验、调用存储引擎接口、获取数据与条件过滤、返回客户端） -> 存储引擎（缓冲池、日志系统：redo log + undo log、持久化存储：.ibd数据文件）
explain：查看SQL执行计划，关键字段（id，select_type，table，partitions，type访问类型: system/const、eq_ref联合索引前缀/主键关联查询、ref非唯一索引等值查询，、range可接受、index全索引扫描，考虑优化、all全表扫描，必须优化，possible_keys，key，key_len，ref，rows，filtered，Extra）、extra(using index、)
explain：查看SQL执行计划，关键字段（id，select_type，table，partitions，type访问类型: system/const主键/唯一索引等值查询，常量优化、eq_ref联合索引前缀/主键关联查询、ref非唯一索引等值查询、range索引范围扫描，可接受、index全索引扫描，考虑优化、all全表扫描，必须优化，possible_keys，key，key_len，ref，rows，filtered，Extra）、extra(using index、using where、Using index condition、Using filesort、Using join buffer、Impossible WHERE) 7，日志系统
三大核心日志：redo log（重做日志。保证持久性，崩溃恢复）、undo log（回滚日志。保证原子性，事务回滚 + MVCC）、binlog(归档日志。用于主从复制 + 数据恢复 + 审计)

性能调优

慢查询优化：定位慢查询（开启慢查询日志、使用 performance_schema/sys库、使用监控工具）、分析执行计划、优化策略（索引优化、sql改写、表结构优化、架构优化）、验证效果
表结构优化：字段设计、大表拆分
架构级优化：读写分离、分库分表、引入缓存
配置参数调优（my.conf）：内存、连接、IO（刷盘策略）、日志