深入MySQL架构：分层设计与核心模块源码解析

graph TB
    subgraph "MySQL Server 架构全景"
        subgraph "应用层 - Application Layer"
            Client1[MySQL Client]
            Client2[PHP/Java/Python]
            Client3[Connector/J]
            Client4[其他客户端]
        end
        
        subgraph "连接层 - Connection Layer"
            CM[连接管理器]
            Auth[权限验证]
            TM[线程管理]
            CC[连接缓存]
        end
        
        subgraph "SQL层 - SQL Layer"
            subgraph "解析器模块"
                LP[词法分析器 Lexer]
                YP[语法分析器 Yacc]
                AST[抽象语法树 AST]
            end
            
            subgraph "优化器模块" 
                RBO[规则优化器 RBO]
                CBO[代价优化器 CBO]
                Stats[统计信息]
                IndexOpt[索引优化]
            end
            
            subgraph "执行器模块"
                ExecPlan[执行计划]
                Executor[SQL执行器]
                ResultSet[结果集处理]
            end
            
            subgraph "缓存模块"
                QC[查询缓存]
                PC[预处理缓存]
                TC[表缓存]
                SC[模式缓存]
            end
        end
        
        subgraph "存储引擎接口层 - Storage Engine Interface"
            Handler[Handler接口]
            HALayer[存储引擎抽象层]
            Plugin[插件管理]
        end
        
        subgraph "存储引擎层 - Storage Engines"
            subgraph "InnoDB引擎"
                Buffer[缓冲池管理]
                BTree[B+树索引]
                MVCC[多版本控制]
                Lock[行锁机制]
                Redo[Redo日志]
                Undo[Undo日志]
                Pages[页面管理]
            end
            
            subgraph "MyISAM引擎"
                KeyCache[键缓存]
                TableLock[表锁]
                MYIIndex[.MYI索引文件]
                MYDData[.MYD数据文件]
            end
            
            subgraph "其他引擎"
                Memory[Memory引擎]
                Archive[Archive引擎]
                CSV[CSV引擎]
                NDB[NDB Cluster]
            end
        end
        
        subgraph "文件系统层 - File System"
            DataFiles[数据文件 .ibd]
            LogFiles[日志文件 ib_logfile]
            BinLog[二进制日志]
            ErrorLog[错误日志]
            SlowLog[慢查询日志]
            RelayLog[中继日志]
        end
        
        subgraph "系统监控 - System Monitoring"
            PFS[Performance Schema]
            InfoSchema[Information Schema]
            SysSchema[Sys Schema]
            Metrics[性能指标]
        end
        
        subgraph "高可用 - High Availability"
            MasterSlave[主从复制]
            GroupRepl[组复制]
            Clustering[集群方案]
            Backup[备份恢复]
        end
    end
    
    %% 连接关系
    Client1 --> CM
    Client2 --> CM
    Client3 --> CM
    Client4 --> CM
    
    CM --> Auth
    Auth --> TM
    TM --> LP
    
    LP --> YP
    YP --> AST
    AST --> RBO
    RBO --> CBO
    CBO --> Stats
    Stats --> IndexOpt
    IndexOpt --> ExecPlan
    ExecPlan --> Executor
    Executor --> ResultSet
    
    QC --> Executor
    PC --> Executor
    TC --> Executor
    SC --> Executor
    
    Executor --> Handler
    Handler --> HALayer
    HALayer --> Plugin
    
    Plugin --> Buffer
    Plugin --> KeyCache
    Plugin --> Memory
    
    Buffer --> BTree
    BTree --> MVCC
    MVCC --> Lock
    Lock --> Redo
    Redo --> Undo
    Undo --> Pages
    
    Pages --> DataFiles
    Redo --> LogFiles
    Executor --> BinLog
    
    PFS -.-> CM
    PFS -.-> Executor  
    PFS -.-> Buffer
    
    MasterSlave --> BinLog
    MasterSlave --> RelayLog
    
    style CM fill:#e1f5fe
    style Executor fill:#f3e5f5
    style Buffer fill:#e8f5e8
    style Handler fill:#fff3e0
    style BinLog fill:#fce4ec

sequenceDiagram
    participant Client as 客户端
    participant ConnMgr as 连接管理器
    participant SQLParser as SQL解析器
    participant Optimizer as 查询优化器
    participant Executor as 执行器
    participant Handler as 存储引擎接口
    participant InnoDB as InnoDB引擎
    participant FileSystem as 文件系统

    Note over Client,FileSystem: MySQL SQL执行完整流程
    
    Client->>ConnMgr: 1. 发送SQL请求
    ConnMgr->>ConnMgr: 2. 验证连接和权限
    ConnMgr->>SQLParser: 3. 将SQL传递给解析器
    
    Note over SQLParser: 词法和语法分析阶段
    SQLParser->>SQLParser: 4. 词法分析(Lexer)
    SQLParser->>SQLParser: 5. 语法分析(Yacc)
    SQLParser->>SQLParser: 6. 语义分析
    SQLParser->>Optimizer: 7. 传递解析树
    
    Note over Optimizer: 查询优化阶段
    Optimizer->>Optimizer: 8. 规则优化(RBO)
    Optimizer->>InnoDB: 9. 获取统计信息
    InnoDB-->>Optimizer: 10. 返回表和索引统计
    Optimizer->>Optimizer: 11. 代价优化(CBO)
    Optimizer->>Executor: 12. 生成执行计划
    
    Note over Executor: SQL执行阶段
    loop 执行计划中的每个操作
        Executor->>Handler: 13. 调用存储引擎接口
        Handler->>InnoDB: 14. 转发到具体存储引擎
        
        alt 读取操作
            InnoDB->>InnoDB: 15. 检查缓冲池
            alt 缓冲池未命中
                InnoDB->>FileSystem: 16. 从磁盘读取页面
                FileSystem-->>InnoDB: 17. 返回页面数据
                InnoDB->>InnoDB: 18. 加载到缓冲池
            end
            InnoDB-->>Handler: 19. 返回记录数据
        else 写入操作
            InnoDB->>InnoDB: 20. 获取行锁
            InnoDB->>InnoDB: 21. 写入Redo日志
            InnoDB->>InnoDB: 22. 修改缓冲池页面
            InnoDB->>InnoDB: 23. 标记页面为脏页
            InnoDB-->>Handler: 24. 返回操作结果
        end
        
        Handler-->>Executor: 25. 返回操作结果
    end
    
    Executor->>Executor: 26. 处理结果集
    Executor->>ConnMgr: 27. 返回执行结果
    ConnMgr->>Client: 28. 发送结果到客户端
    
    Note over InnoDB,FileSystem: 后台异步操作
    par 并行后台任务
        InnoDB->>FileSystem: 29. 刷新脏页到磁盘
    and
        InnoDB->>FileSystem: 30. 刷新Redo日志
    and
        InnoDB->>InnoDB: 31. 清理Undo页面
    end

stateDiagram-v2
    [*] --> 空闲状态
    
    空闲状态 --> 事务开始 : BEGIN/START TRANSACTION
    事务开始 --> 活跃事务 : 第一个DML语句
    
    活跃事务 --> 活跃事务 : DML操作
    活跃事务 --> 准备提交 : COMMIT
    活跃事务 --> 回滚 : ROLLBACK
    活跃事务 --> 回滚 : 错误发生
    
    准备提交 --> 提交阶段1 : 写入Redo日志
    提交阶段1 --> 提交阶段2 : 释放行锁
    提交阶段2 --> 事务完成 : 清理事务信息
    
    回滚 --> 回滚阶段1 : 应用Undo日志
    回滚阶段1 --> 回滚阶段2 : 释放行锁  
    回滚阶段2 --> 事务完成 : 清理事务信息
    
    事务完成 --> 空闲状态
    
    note right of 活跃事务
        在此状态下：
        - 持有行锁
        - 生成Undo日志
        - 修改缓冲池页面
    end note
    
    note right of 提交阶段1
        两阶段提交：
        1. 准备阶段(Prepare)
        2. 提交阶段(Commit)
    end note

/**
 * MySQL系统性能指标结构
 * 用于监控和调优MySQL服务器性能
 */
struct mysql_performance_metrics {
    // 连接相关指标
    struct {
        uint64_t total_connections;        ///< 总连接数
        uint64_t active_connections;       ///< 活跃连接数
        uint64_t max_used_connections;     ///< 最大使用连接数
        uint64_t connection_errors_total;  ///< 连接错误总数
        double   avg_connection_time;      ///< 平均连接时间(秒)
    } connection_metrics;
    
    // SQL执行指标
    struct {
        uint64_t queries_total;            ///< 总查询数
        uint64_t slow_queries;             ///< 慢查询数
        uint64_t queries_per_second;       ///< 每秒查询数(QPS)
        double   avg_query_time;           ///< 平均查询时间(秒)
        uint64_t select_count;             ///< SELECT语句数
        uint64_t insert_count;             ///< INSERT语句数
        uint64_t update_count;             ///< UPDATE语句数
        uint64_t delete_count;             ///< DELETE语句数
    } query_metrics;
    
    // InnoDB指标
    struct {
        uint64_t buffer_pool_reads;        ///< 缓冲池读取次数
        uint64_t buffer_pool_read_requests;///< 缓冲池读取请求数
        double   buffer_pool_hit_ratio;    ///< 缓冲池命中率
        uint64_t pages_flushed;            ///< 刷新页面数
        uint64_t log_writes;               ///< 日志写入次数
        uint64_t row_lock_waits;           ///< 行锁等待次数
        double   avg_row_lock_time;        ///< 平均行锁时间(秒)
    } innodb_metrics;
    
    // 网络I/O指标
    struct {
        uint64_t bytes_sent;               ///< 发送字节数
        uint64_t bytes_received;           ///< 接收字节数
        uint64_t network_roundtrips;       ///< 网络往返次数
    } network_metrics;
};

-- MySQL架构健康检查脚本
-- 1. 检查关键性能指标
SELECT 
  'Connections' as metric_name,
  VARIABLE_VALUE as current_value,
  'max_connections' as related_config
FROM PERFORMANCE_SCHEMA.GLOBAL_STATUS 
WHERE VARIABLE_NAME = 'Threads_connected'
UNION ALL
SELECT 
  'QPS',
  ROUND(VARIABLE_VALUE/Uptime_since_flush_status, 2),
  'query_cache_size'
FROM 
  (SELECT VARIABLE_VALUE FROM PERFORMANCE_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Questions') q,
  (SELECT VARIABLE_VALUE as Uptime_since_flush_status FROM PERFORMANCE_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Uptime_since_flush_status') u
UNION ALL
SELECT 
  'InnoDB Buffer Pool Hit Rate',
  ROUND(100 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests * 100), 2),
  'innodb_buffer_pool_size'
FROM 
  (SELECT VARIABLE_VALUE as Innodb_buffer_pool_reads FROM PERFORMANCE_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') r,
  (SELECT VARIABLE_VALUE as Innodb_buffer_pool_read_requests FROM PERFORMANCE_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') rr;

/**
 * MySQL架构瓶颈诊断器
 * 全面分析MySQL各层的性能瓶颈
 */
class MySQL_architecture_diagnostics {
private:
    struct architecture_metrics {
        // 连接层指标
        double connection_utilization;      ///< 连接使用率
        double auth_failure_rate;          ///< 认证失败率
        
        // SQL层指标  
        double parse_time_pct;             ///< 解析时间占比
        double optimize_time_pct;          ///< 优化时间占比
        double execute_time_pct;           ///< 执行时间占比
        
        // 存储层指标
        double buffer_pool_hit_rate;       ///< 缓冲池命中率
        double lock_wait_ratio;            ///< 锁等待比率
        double io_utilization;             ///< I/O使用率
        
        // 整体指标
        double cpu_utilization;            ///< CPU使用率
        double memory_utilization;         ///< 内存使用率
        double disk_utilization;           ///< 磁盘使用率
    };
    
public:
    /**
     * 执行全面的架构诊断
     * @return 诊断报告
     */
    architecture_diagnosis perform_full_diagnosis() {
        architecture_metrics metrics = collect_architecture_metrics();
        architecture_diagnosis diagnosis;
        
        // 1. 连接层诊断
        diagnose_connection_layer(metrics, diagnosis);
        
        // 2. SQL层诊断
        diagnose_sql_layer(metrics, diagnosis);
        
        // 3. 存储层诊断
        diagnose_storage_layer(metrics, diagnosis);
        
        // 4. 系统层诊断
        diagnose_system_layer(metrics, diagnosis);
        
        // 5. 生成综合建议
        generate_optimization_suggestions(diagnosis);
        
        return diagnosis;
    }
    
    /**
     * 识别架构瓶颈点
     */
    std::vector<bottleneck_info> identify_bottlenecks(const architecture_metrics &metrics) {
        std::vector<bottleneck_info> bottlenecks;
        
        // CPU瓶颈检测
        if (metrics.cpu_utilization > 80.0) {
            bottlenecks.push_back({
                .layer = "SYSTEM",
                .component = "CPU",
                .severity = metrics.cpu_utilization > 95.0 ? "CRITICAL" : "HIGH",
                .description = "CPU使用率过高: " + std::to_string(metrics.cpu_utilization) + "%",
                .impact = "可能导致查询响应延迟增加",
                .suggestions = {"优化慢查询", "增加CPU核心数", "使用读写分离"}
            });
        }
        
        // 内存瓶颈检测
        if (metrics.memory_utilization > 85.0) {
            bottlenecks.push_back({
                .layer = "SYSTEM", 
                .component = "MEMORY",
                .severity = "HIGH",
                .description = "内存使用率过高: " + std::to_string(metrics.memory_utilization) + "%",
                .impact = "可能触发swap，严重影响性能",
                .suggestions = {"调整innodb_buffer_pool_size", "优化内存使用", "增加物理内存"}
            });
        }
        
        // I/O瓶颈检测
        if (metrics.io_utilization > 80.0) {
            bottlenecks.push_back({
                .layer = "STORAGE",
                .component = "DISK_IO", 
                .severity = "HIGH",
                .description = "磁盘I/O使用率过高: " + std::to_string(metrics.io_utilization) + "%",
                .impact = "查询响应时间受磁盘I/O限制",
                .suggestions = {"使用SSD存储", "优化索引设计", "调整刷新策略"}
            });
        }
        
        // 锁等待瓶颈检测
        if (metrics.lock_wait_ratio > 5.0) {
            bottlenecks.push_back({
                .layer = "STORAGE_ENGINE",
                .component = "LOCK_SYSTEM",
                .severity = "MEDIUM",
                .description = "锁等待比例过高: " + std::to_string(metrics.lock_wait_ratio) + "%", 
                .impact = "并发性能受锁争用影响",
                .suggestions = {"优化事务逻辑", "调整隔离级别", "减少锁持有时间"}
            });
        }
        
        return bottlenecks;
    }
    
private:
    struct bottleneck_info {
        std::string layer;              ///< 架构层次
        std::string component;          ///< 组件名称
        std::string severity;           ///< 严重程度
        std::string description;        ///< 问题描述
        std::string impact;             ///< 影响分析
        std::vector<std::string> suggestions; ///< 优化建议
    };
    
    struct architecture_diagnosis {
        std::vector<bottleneck_info> bottlenecks;
        std::vector<std::string> optimization_suggestions;
        double overall_health_score;
    };
};

/**
 * MySQL架构设计指导原则
 * 基于生产环境的架构设计最佳实践
 */
class MySQL_architecture_guidelines {
public:
    /**
     * 评估架构设计的合理性
     * @param config 架构配置
     * @return 评估结果和建议
     */
    architecture_assessment evaluate_architecture(const mysql_config &config) {
        architecture_assessment assessment;
        
        // 1. 连接层设计评估
        evaluate_connection_layer_design(config, assessment);
        
        // 2. 存储引擎选择评估
        evaluate_storage_engine_choice(config, assessment);
        
        // 3. 事务设计评估
        evaluate_transaction_design(config, assessment);
        
        // 4. 复制架构评估
        evaluate_replication_architecture(config, assessment);
        
        return assessment;
    }
    
    /**
     * 生成架构优化建议
     */
    std::vector<std::string> generate_architecture_recommendations(
        const workload_characteristics &workload) {
        
        std::vector<std::string> recommendations;
        
        if (workload.read_write_ratio > 10.0) {
            recommendations.push_back(
                "读写比例过高，建议实施读写分离架构，"
                "将读查询分发到从库，减轻主库压力");
        }
        
        if (workload.concurrent_connections > 1000) {
            recommendations.push_back(
                "高并发连接场景，建议使用连接池中间件（如ProxySQL），"
                "实现连接复用和负载均衡");
        }
        
        if (workload.data_size > 100 * 1024 * 1024 * 1024ULL) { // 100GB
            recommendations.push_back(
                "大数据量场景，建议考虑分库分表策略，"
                "使用中间件实现数据分片");
        }
        
        if (workload.has_complex_queries) {
            recommendations.push_back(
                "复杂查询场景，建议启用查询缓存或引入Redis缓存层，"
                "减少复杂查询的重复计算");
        }
        
        return recommendations;
    }
};

// 连接处理主循环（核心路径精炼版）
void handle_connection(THD *thd) {
    // 1) 握手
    Protocol_classic *proto = thd->get_protocol_classic();
    if (!proto->send_handshake_packet()) return;             // 发送握手

    // 2) 认证
    if (!proto->handle_auth_response()) {                    // 解析认证响应并校验
        proto->send_error_packet(ER_ACCESS_DENIED_ERROR, "Access denied");
        return;
    }

    // 3) 会话就绪
    if (!proto->send_ok_packet()) return;                    // 登录成功

    // 4) 命令循环
    while (!thd->killed && thd->net.vio != nullptr) {
        if (do_command(thd)) break;                          // 读取并分派命令
    }

    end_connection(thd);                                     // 连接清理
}

// 从网络读取一个命令并分派
bool do_command(THD *thd) {
    ulong len = my_net_read(&thd->net);
    if (len == packet_error) return true;                    // 断开或网络错误

    enum enum_server_command cmd = static_cast<enum_server_command>(thd->net.read_pos[0]);
    COM_DATA data{};
    if (!parse_com_data(thd->net.read_pos, len, &data)) {    // 解析请求体
        my_error(ER_MALFORMED_PACKET, MYF(0));
        return true;
    }
    return dispatch_command(thd, &data, cmd);                // 按类型处理
}

// 命令分派（仅展示关键分支）
bool dispatch_command(THD *thd, const COM_DATA *com, enum enum_server_command cmd) {
    switch (cmd) {
        case COM_QUERY:
            return mysql_parse(thd, com->com_query.query, com->com_query.length);
        case COM_INIT_DB:
            return mysql_change_db(thd, com->com_init_db.db_name, false);
        case COM_PING:
            return send_ok_packet(thd);
        case COM_QUIT:
            return true;
        default:
            my_error(ER_UNKNOWN_COM_ERROR, MYF(0));
            return true;
    }
}

// SQL 解析主入口（骨架）
bool mysql_parse(THD *thd, const char *query, size_t length) {
    Parser parser(thd);
    Parse_tree ast;
    if (!parser.parse(query, length, &ast)) {
        my_error(ER_PARSE_ERROR, MYF(0));
        return true;
    }

    // 生成并优化执行计划
    Optimizer optimizer(thd);
    ExecPlan plan = optimizer.build_and_optimize(ast);

    // 执行
    Executor exec(thd);
    return exec.run(plan); // 返回true表示关闭连接
}

// 执行器访问存储引擎（典型读路径）
bool Executor::run(const ExecPlan &plan) {
    for (const Step &op : plan.steps) {
        handler *h = op.table->file;                     // 通过Handler访问引擎
        if (op.type == Step::IndexScan) {
            h->index_init(op.index);
            if (h->index_read(op.key, op.key_len, HA_READ_KEY_EXACT))
                continue;                                // 首条
            do {
                // 处理当前行...
            } while (!h->index_next());                 // 顺序推进
            h->index_end();
        } else if (op.type == Step::FullScan) {
            h->rnd_init(true);
            while (!h->rnd_next(op.record_buf)) {
                // 处理当前行...
            }
            h->rnd_end();
        }
    }
    return false;
}

sequenceDiagram
    participant Client
    participant Proto as Protocol_classic
    participant THD
    participant Exec as Executor

    Client->>Proto: 建立连接
    Proto-->>Client: 握手包
    Client->>Proto: 认证响应
    Proto->>THD: 校验凭据
    THD-->>Proto: 认证结果
    Proto-->>Client: OK（登录成功）
    loop 命令循环
        Client->>THD: 命令包
        THD->>THD: do_command/dispatch_command
        THD->>Exec: mysql_parse → 计划生成
        Exec-->>THD: 执行结果
        THD-->>Client: 结果/OK
    end

sequenceDiagram
    participant THD
    participant Parser
    participant Optimizer
    participant Executor
    participant Handler
    participant InnoDB

    THD->>Parser: mysql_parse(query)
    Parser-->>THD: AST
    THD->>Optimizer: build_and_optimize(AST)
    Optimizer-->>THD: ExecPlan
    THD->>Executor: run(plan)
    loop plan steps
        Executor->>Handler: index_read()/rnd_init()
        Handler->>InnoDB: 访问/修改页面
        InnoDB-->>Handler: 行或状态
        Handler-->>Executor: 结果
    end
    Executor-->>THD: 结果/OK

classDiagram
    class THD {
        +NET net
        +Security_context* security_context()
        +Protocol_classic* get_protocol_classic()
    }
    class Protocol_classic {
        +send_handshake_packet() bool
        +handle_auth_response() bool
        +send_ok_packet(...) bool
    }
    THD --> Protocol_classic : uses

    class Connection_handler {
        <<abstract>>
        +add_connection(Channel_info*) bool
    }
    class Per_thread_connection_handler
    Connection_handler <|-- Per_thread_connection_handler

    class Authentication_manager {
        +authenticate_user(... ) bool
    }
    THD ..> Authentication_manager : auth

    class Executor {
        +run(ExecPlan) bool
    }
    class Optimizer {
        +build_and_optimize(Parse_tree) ExecPlan
    }
    class Parser {
        +parse(sql,len, out Parse_tree) bool
    }
    THD ..> Parser
    THD ..> Optimizer
    THD ..> Executor

    class handler {
        +index_init(...)
        +index_read(...)
        +index_next()
        +rnd_init(...)
        +rnd_next(...)
    }
    class ha_innobase
    handler <|-- ha_innobase
    Executor ..> handler : drive

    class USER_CONN {
        +check_connection_limit(THD*) bool
        +increment_connections()
        +decrement_connections()
    }