MySQL高级优化与性能工程

一、InnoDB存储引擎核心机制

1.1 物理存储结构

InnoDB以Page为单位管理数据，默认16KB/页。理解Page结构是分析锁冲突和索引失效的基础：

-- InnoDB Page结构（16KB）
-- Page Header(38B) + System Records + User Records + Free Space + Page Trailer(8B)
-- User Records：记录通过记录头部的 next_record 指针组成单向链表

-- 查看表占用空间
SELECT NAME, SPACE, PAGE_NO, PAGE_TYPE
FROM information_schema.INNODB_BUFFER_PAGE
WHERE TABLE_NAME = '`test`.`orders`'
ORDER BY POSITION;

-- 查看索引树高度（>3层说明数据量很大）
SELECT b.TABLE_NAME, b.INDEX_NAME, b.N_LEVELS, b.N_PAGES
FROM information_schema.INNODB_INDEX_STATS b
WHERE b.TABLE_NAME = 'orders'
  AND b.INDEX_NAME = 'PRIMARY';

-- N_LEVELS含义：
-- 1 = 根页面（数据在根页面）
-- 2 = 根+叶子
-- 3 = 根+中间+叶子（100万~1000万行）
-- 4 = 超大数据（>1亿行）

1.2 MVCC与Read View机制

InnoDB通过MVCC（Multi-Version Concurrency Control）实现非锁定读，避免读写冲突。这是RR（可重复读）隔离级别的基础：

// MVCC核心：每行数据有两个隐藏列
// DB_TRX_ID: 最近修改的事务ID（插入/更新时赋值，删除是更新为新事务ID）
// DB_ROLL_PTR: 回滚指针，指向undo log中的历史版本

// Read View结构（在事务启动时生成）
// ReadView {
//     creator_trx_id: 当前事务ID
//     low_limit_id: 尚未分配的最大事务ID
//     up_limit_id: 最小活跃事务ID
//     m_ids: 活跃事务ID列表
// }

// 可重复读（RR）下的SELECT：
// 读取ReadView时使用的是事务启动时创建的ReadView
// 如果一行数据的trx_id > up_limit_id 且不在m_ids中 → 可见
// 否则 → 通过DB_ROLL_PTR读取undo log中的历史版本

// 当前读 vs 快照读
// 快照读（普通SELECT）：无锁，通过MVCC读取历史版本
// 当前读（SELECT...FOR UPDATE, INSERT, UPDATE, DELETE）：读取最新数据 + 行锁

// ⚠️ RR级别下MVCC的幻读问题
// T1: SELECT * FROM orders WHERE status='pending' — 5行（MVCC快照读）
// T2: INSERT INTO orders ... (新行，trx_id=T2) — 提交
// T1: UPDATE orders SET status='processed' WHERE status='pending' — 当前读，锁定所有行！
//     实际上UPDATE会锁住T2新插入的行（因为status='pending'的WHERE条件）
//     即：快照读无幻读，当前读有幻读（通过间隙锁实现）

// 读已提交（RC）vs 可重复读（RR）的区别
// RC：每次SELECT都重新生成ReadView，所以能看到其他事务已提交的修改
// RR：ReadView在事务开始时创建，整个事务期间使用同一个ReadView

二、InnoDB锁机制与行锁优化

2.1 锁的类型与兼容性矩阵

-- InnoDB 锁类型
-- 1. 共享锁(S)：允许读取行
-- 2. 排他锁(X)：允许读取/修改行
-- 3. 意向共享锁(IS)：事务打算在行上设置S锁
-- 4. 意向排他锁(IX)：事务打算在行上设置X锁
-- 5. 记录锁(Record Lock)：锁定索引记录
-- 6. 间隙锁(Gap Lock)：锁定索引之间的间隙
-- 7. Next-Key Lock：记录锁 + 间隙锁（RR级别默认）

-- 兼容性矩阵：
--         X    IX    S    IS
-- X       ❌   ❌   ❌   ❌
-- IX      ❌   ✅   ❌   ✅
-- S       ❌   ❌   ✅   ✅
-- IS      ❌   ✅   ✅   ✅

-- 死锁检测
SHOW ENGINE INNODB STATUS;
-- LATEST DETECTED DEADLOCK: 显示最近一次死锁详情

-- 记录锁示例
SELECT * FROM orders WHERE id=100 FOR UPDATE; -- 对主键加X锁

-- 间隙锁示例（RR级别下）
SELECT * FROM orders WHERE id BETWEEN 10 AND 20 FOR UPDATE;
-- 锁住(10,20)之间的所有间隙，防止插入新记录

-- 唯一索引 + 等值查询 = Record Lock（无间隙锁！）
SELECT * FROM orders WHERE order_no='ON20260508' FOR UPDATE;
-- ✅ 不会锁间隙，只锁 order_no='ON20260508' 这一行

-- 普通索引 + 等值查询 = Next-Key Lock（锁记录+间隙）
SELECT * FROM orders WHERE seller_id=5 FOR UPDATE;
-- 锁住 seller_id=5 的所有记录 + 前后间隙

2.2 死锁预防与解决

// Java层：按固定顺序获取锁（防止死锁）
public void transfer(Long fromId, Long toId, BigDecimal amount) {
    // 始终按ID大小顺序加锁
    Long first = fromId.compareTo(toId) < 0 ? fromId : toId;
    Long second = fromId.compareTo(toId) < 0 ? toId : fromId;

    Account firstAcc = accountRepo.findById(first).orElseThrow();
    Account secondAcc = accountRepo.findById(second).orElseThrow();

    synchronized(firstAcc) {           // 先锁ID小的
        synchronized(secondAcc) {       // 再锁ID大的
            // 不可能发生：AB场景先A后B，BA场景先B后A
            // 避免A等B、B等A的死锁
        }
    }
}

// MySQL层：设置锁等待超时
SET innodb_lock_wait_timeout = 5;  -- 超过5秒自动回滚
SET innodb_rollback_on_timeout = ON; -- 超时时回滚整个事务

// 监控长事务（持有锁超过10秒的查询）
SELECT
    trx.THREAD_ID, trx.TRANSACTION_ID, trx.TRANSACTION_STATE,
    trx_started, trx_rows_locked, trx_rows_modified,
    sql_text
FROM information_schema.INNODB_TRX trx
LEFT JOIN performance_schema.events_statements_current stmt
    ON trx.THREAD_ID = stmt.THREAD_ID
WHERE trx_rows_locked > 0 OR trx_rows_modified > 0
ORDER BY trx_started;

三、SQL执行计划与索引覆盖扫描

-- 执行计划详解
EXPLAIN ANALYZE SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
  AND o.create_time > '2026-01-01'
ORDER BY o.create_time DESC
LIMIT 100;

-- 关键字段解读
-- type: ALL(全表扫描) < index < range < ref < eq_ref < const
-- key: 实际使用的索引（NULL表示全表扫描）
-- rows: 估算扫描行数（乘积越小越好）
-- Extra:
--   Using index: 覆盖索引，无需回表（最优）
--   Using index condition (ICP): 索引下推，在引擎层过滤
--   Using filesort: 需额外排序（慢）
--   Using temporary: 需临时表（慢）
--   Using where: 服务层过滤

-- 索引覆盖扫描（Covering Index）
-- 创建复合索引：覆盖查询的所有列
CREATE INDEX idx_orders_cover ON orders(status, create_time, id, amount);
-- 查询：SELECT id, amount FROM orders WHERE status='paid' AND create_time > '...'
-- 只需扫描索引，无需回表（Extra: Using index）

-- ICP（Index Condition Pushdown）优化
-- WHERE条件尽量能放到索引中
-- ❌ SELECT * FROM orders WHERE YEAR(create_time)=2026 AND status='paid'
--    — YEAR()导致无法用create_time索引
-- ✅ SELECT * FROM orders WHERE create_time >= '2026-01-01' AND status='paid'
--    — create_time范围索引 + status作为索引过滤条件

-- 排序优化
-- ORDER BY字段必须在索引中（最左前缀）
-- ❌ SELECT * FROM orders ORDER BY amount, create_time
--    — amount不是最左前缀，filesort
-- ✅ CREATE INDEX idx ON orders(user_id, create_time, status)
--    SELECT * FROM orders WHERE user_id=5 ORDER BY create_time DESC
--    — 完全覆盖排序（Using index）

四、主从复制与读写分离

// MySQL主从复制原理
// Master: binlog dump thread → 读取binlog事件
// Slave: IO_thread → relay log
//        SQL_thread → 重放relay log中的事件

// 复制延迟监控
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 延迟秒数（>0说明有延迟）
-- Relay_Log_Pos: relay log位置
-- Exec_Master_Log_Pos: 已执行的主库binlog位置

// 延迟原因分析
// 1. 大事务：主库执行5分钟的事务，从库也需要5分钟重放
// 2. 网络延迟：binlog传输慢
// 3. 从库机器性能差：SQL_thread跟不上IO_thread

// 解决方案1：大事务拆分
-- ❌ INSERT INTO orders SELECT * FROM old_orders; (100万行)
-- ✅ 分批插入
INSERT INTO orders SELECT * FROM old_orders WHERE id BETWEEN 1 AND 5000;
INSERT INTO orders SELECT * FROM old_orders WHERE id BETWEEN 5001 AND 10000;
-- 或用存储过程分批提交

// 解决方案2：并行复制（MySQL 5.7+）
-- MySQL 5.7: 库级并行复制（log_slave_updates + slave_parallel_type=DATABASE）
-- MySQL 8.0: WRITESET并行（slave_parallel_type=LOGICAL_CLOCK）
SET GLOBAL slave_parallel_type='LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers=8;  -- 8个SQL线程并行重放

// Java读写分离路由
@Configuration
public class DataSourceConfig {
    @Bean
    public DataSource routingDataSource(
            @Qualifier("master") DataSource master,
            @Qualifier("slave1") DataSource slave1,
            @Qualifier("slave2") DataSource slave2) {
        Map targets = Map.of("master", master, "slave1", slave1, "slave2", slave2);
        RoutingDataSource ds = new RoutingDataSource();
        ds.setTargetDataSources(targets);
        ds.setDefaultTargetDataSource(master);
        return ds;
    }
}

// 路由策略：写→主库，读→从库（随机或轮询）
public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        boolean readOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
        return readOnly ? "slave" + (new Random().nextInt(2) + 1) : "master";
    }
}

// ⚠️ 主从延迟问题：读写分离后读到脏数据
// 解决方案：
// 1. 强制读主库（写后立即读）：@Transactional(readOnly=false) → 强制主库
// 2. 延迟感知：应用层记录主库写入时间，下次读判断延迟是否可接受
// 3. GTID复制：MySQL 5.7+，基于全局事务ID，延迟更易追踪

五、MySQL 8.0 新特性与生产配置

// MySQL 8.0 关键新特性
// 1. 窗口函数（Window Functions）：SQL 2003标准
SELECT
    user_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY create_time) AS running_total,
    RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS amount_rank
FROM orders;

// 2. CTE（Common Table Expression）
WITH monthly_sales AS (
    SELECT DATE_FORMAT(create_time,'%Y-%m') AS month,
           SUM(amount) AS total
    FROM orders
    GROUP BY month
)
SELECT * FROM monthly_sales ORDER BY total DESC;

// 3. 隐藏索引（Invisible Index）
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE; -- 索引对优化器不可见
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;    -- 恢复可见

// 4. 降序索引（Descending Index）
CREATE INDEX idx_create_desc ON orders(create_time DESC);
-- MySQL 8.0支持，扫描时无需filesort

// MySQL 8.0 生产推荐配置
[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
# 基础配置
server-id = 1
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
binlog_row_image = FULL

# InnoDB配置
innodb_buffer_pool_size = 12G        # 建议为机器内存的60-70%
innodb_log_file_size = 2G           # Redo日志文件大小
innodb_flush_log_at_trx_commit = 1  # 每次提交都刷盘（最安全）
innodb_flush_method = O_DIRECT      # 绕过OS Page Cache

# 连接配置
max_connections = 3000
max_connect_errors = 100000
wait_timeout = 600
interactive_timeout = 600

# 缓冲区配置
key_buffer_size = 256M               # MyISAM索引（少量MyISAM时）
sort_buffer_size = 4M
read_buffer_size = 2M
join_buffer_size = 4M

# 性能配置
innodb_io_capacity = 2000           # SSD建议2000+
innodb_io_capacity_max = 8000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_flush_neighbors = 0          # SSD关闭（机械盘开启）
innodb_page_cleaners = 4

# 复制配置
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
relay_log_recovery = ON