MySQL架构设计

InnoDB架构

InnoDB采用Buffer Pool缓存数据页和索引页，通过Change Buffer优化非唯一索引写入，借助Redo Log保证崩溃恢复。

┌─────────────────────────────────┐
│         Buffer Pool             │
│  ┌───────┐ ┌────────┐ ┌──────┐ │
│  │ Data  │ │  Index │ │Change│ │
│  │ Pages │ │  Pages │ │Buffer│ │
│  └───────┘ └────────┘ └──────┘ │
└─────────────────────────────────┘
         ↓ flush        ↓ merge
┌──────────────┐  ┌──────────────┐
│   Redo Log   │  │   数据文件    │
└──────────────┘  └──────────────┘

Buffer Pool配置

Buffer Pool大小建议设置为物理内存的60%-80%，通过LRU算法管理缓存页。

# my.cnf
[mysqld]
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 12
innodb_old_blocks_pct = 37
innodb_old_blocks_time = 1000

-- 监控Buffer Pool命中率
SHOW ENGINE INNODB STATUS\G
SELECT 
  (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 AS hit_rate
FROM (
  SELECT 
    VARIABLE_VALUE AS Innodb_buffer_pool_reads 
  FROM performance_schema.global_status 
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) a, (
  SELECT 
    VARIABLE_VALUE AS Innodb_buffer_pool_read_requests 
  FROM performance_schema.global_status 
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) b;

WAL机制

Write-Ahead Logging确保数据修改先写入日志再刷盘，实现崩溃恢复和事务持久性。

@Transactional
public void transfer(String from, String to, BigDecimal amount) {
    // 1. 更新A账户余额（写入Buffer Pool + Redo Log）
    accountMapper.deduct(from, amount);
    // 2. 更新B账户余额
    accountMapper.add(to, amount);
    // 3. 事务提交时刷盘
}

事务隔离级别

InnoDB默认REPEATABLE READ，通过MVCC和间隙锁解决幻读问题。

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- MVCC实现：每行有隐藏的trx_id和roll_pointer
-- 通过Read View判断数据版本可见性

索引优化

B+树索引是InnoDB的核心数据结构，聚簇索引存储完整行数据，二级索引存储主键值。

-- 联合索引遵循最左前缀原则
CREATE INDEX idx_name_age ON users(name, age);

-- 覆盖索引避免回表
SELECT name, age FROM users WHERE name = '张三';

-- 索引下推（ICP）减少回表次数
SELECT * FROM users WHERE name LIKE '张%' AND age > 20;