Skip to content

MySQL

事务的ACID特性

原子性 (Atomicity)

  • 事务是最小执行单元，全部成功或全部失败
  • 通过 Undo Log 实现回滚

一致性 (Consistency)

  • 事务前后数据库保持合法状态
  • 由应用层和数据库约束共同保证

隔离性 (Isolation)

  • 事务间操作相互隔离
  • 通过锁和 MVCC 机制实现

持久性 (Durability)

  • 事务提交后数据永久保存
  • 通过 Redo Log 和刷盘策略保证

隔离级别

读未提交 (RU)

  • 可能脏读、不可重复读、幻读

读已提交 (RC)

  • 解决脏读，但可能出现不可重复读和幻读（默认隔离级别）

可重复读 (RR)

  • 解决脏读、不可重复读（MySQL默认级别，通过 MVCC + Next-Key Locks 避免幻读）

串行化 (Serializable)

  • 完全隔离，性能最低

MVCC（多版本并发控制）

核心概念

  • 通过快照读实现非阻塞读，解决读写冲突

实现机制

  • 每行数据包含隐藏字段 DB_TRX_ID（事务ID）和 DB_ROLL_PTR（回滚指针）
  • 通过 Undo Log 构建版本链，结合 ReadView 判断数据可见性
  • 只在 RC 和 RR 隔离级别下生效

InnoDB的索引结构

B+树索引

  • 非叶子节点只存键值，叶子节点存储数据（聚集索引存整行，二级索引存主键）

B+树相比B树的优势

  • 叶子节点形成有序链表，范围查询高效
  • 非叶子节点存储更多键，树高更低，减少IO次数
  • 数据只存在叶子节点，查询稳定性更高

死锁

产生条件

  • 互斥、请求与保持、不可剥夺、循环等待

检测

  • 通过 等待图 (Wait-for Graph) 检测循环依赖，触发死锁回滚

避免方案

  • 事务按固定顺序访问资源
  • 降低事务粒度，减少锁竞争
  • 设置合理的事务超时时间（innodb_lock_wait_timeout）
  • 使用 SELECT ... FOR UPDATE NOWAIT（MySQL 8.0+）

慢查询优化

1. 定位慢SQL

开启 slow_query_log，分析慢日志

2. Explain分析

关注 type（扫描方式）、key（使用索引）、rows（扫描行数）、Extra（是否 Using filesort / Using temporary）

3. 优化方向

  • 添加缺失索引，避免全表扫描
  • 优化SQL写法（如拆分复杂查询、避免 SELECT *）
  • 使用覆盖索引，减少回表操作
  • 调整表结构（如范式化/反范式化）

主从复制

原理

  • 主库通过 Binlog 记录变更，从库通过 I/O线程 拉取日志，SQL线程 重放

延迟解决

  • 并行复制（MTS，基于库/组提交/事务粒度）
  • 调整 sync_binlog 和 innodb_flush_log_at_trx_commit 参数平衡安全与性能
  • 使用半同步复制（semisync）确保主从数据一致性

分库分表

垂直拆分

  • 按业务拆分（如订单库、用户库）
  • 优点：解耦业务，降低单库压力
  • 缺点：无法解决单表数据量过大的问题

水平拆分

  • 按规则分散数据（如按ID取模、按时间范围）
  • 优点：解决单表数据膨胀问题
  • 缺点：跨分片查询复杂，事务管理困难

代理层 vs 客户端分片

  • ShardingSphere、MyCAT等工具实现

Explain关键字

Using index

  • 仅用索引即可返回所需数据（覆盖索引）

Using index condition

  • 使用索引条件下推（ICP），在存储引擎层过滤数据，减少回表次数

覆盖索引

定义

  • 查询的字段全部被索引覆盖，无需回表

示例

-- 创建索引 (age, name)
CREATE INDEX idx_age_name ON user(age, name);

-- 查询命中覆盖索引
SELECT age, name FROM user WHERE age > 20;

聚集索引 vs 非聚集索引

聚集索引 (Clustered Index)

  • 数据即索引：索引的叶子节点直接存储完整行数据
  • 物理有序：表中的数据行按聚集索引的键值顺序存储
  • 唯一性：一张表有且仅有一个聚集索引（InnoDB强制要求）

非聚集索引 (Non-Clustered Index)

  • 索引与数据分离：叶子节点存储主键值（InnoDB中）或行指针（MyISAM中）
  • 逻辑有序：索引本身有序，但数据行物理存储无序
  • 可多个共存：一张表可以创建多个非聚集索引（二级索引）

InnoDB引擎实现

特征 聚集索引 非聚集索引
叶子节点内容 存储完整数据行 存储主键值
B+树结构 主键构建的B+树即数据文件 独立B+树结构
查找流程 直接返回数据 先查主键，再通过主键查聚集索引（回表）

关键差异

对比维度 聚集索引 非聚集索引
数据存储方式 数据按索引顺序物理存储 索引与数据物理分离
查询速度 主键查询极快（直接定位数据） 需要二次查找（回表）
插入效率 顺序插入高效，随机插入可能产生页分裂 插入只需更新索引树
空间占用 无额外存储空间 需要额外存储索引结构
更新代价 主键修改代价高（需移动数据行） 只需更新索引树

常见误区

误区1："主键一定是聚集索引"

  • 事实：在InnoDB中主键自动成为聚集索引，但若未定义主键：
  • 选择第一个UNIQUE且非空的字段
  • 否则隐式创建6字节的DB_ROW_ID作为聚集索引

误区2："非聚集索引查询性能差"

  • 优化方案：
  • 通过覆盖索引避免回表
  • 使用索引条件下推（ICP，Index Condition Pushdown）