MySQL查询优化器源码解析：从SQL到执行计划的底层实现

MySQL查询优化器源码解析：从SQL到执行计划的底层实现

重点介绍：本文与之前已发布的《InnoDB底层架构》《索引实现原理》《锁机制》形成完整知识链，聚焦sql/optimizer模块实现细节

一、优化器架构全景图

源码定位（MySQL 8.0.32）：

• 主入口：sql/sql_optimizer.cc::JOIN::optimize()
• 关键类：JOIN、AccessPath、Cost_model_server
• 结构体：TABLE、KEY、SQL_SELECT

二、统计信息管理的源码实现

1. InnoDB统计信息收集

// storage/innobase/optimizer/opt_stats.cc
void dict_stats_update(/*...*/) {
  // 采用随机采样算法
  for (i = 0; i < N_SAMPLE_PAGES; i++) {
    page_no_t page_id = rand() % total_pages;
    buf_block_t* block = buf_page_get(/*...*/);
    // 扫描页面统计不同键值
    while (!page_rec_is_supremum(rec)) {
      dfield_t* field = rec_get_nth_field(/*...*/);
      update_cardinality_count(field);
    }
  }
  // 持久化到mysql.innodb_table_stats
  row_update_statistics_table(/*...*/);
}

关键参数：

• innodb_stats_persistent_sample_pages (默认值=20)
• innodb_stats_transient_sample_pages (默认值=8)

2. 代价模型中的统计信息应用

// sql/opt_costmodel.cc
double Cost_model_table::page_read_cost(double pages) const {
  const double in_mem = table->file->table_in_memory_estimate();
  const double on_disk = 1.0 - in_mem;
  // 计算混合I/O成本
  return pages * (in_mem * m_server->page_read_cost_cache() + 
                 on_disk * m_server->page_read_cost_disk());
}

三、逻辑优化的核心转换策略

1. 条件化简（WHERE优化）

// sql/sql_optimizer.cc
void Item_cond_and::fix_after_pullout(/*...*/) {
  // 应用谓词传递规则：a>5 AND a>10 → a>10
  if (const_item() && !with_subquery) {
    Item *item = fold_condition(/*...*/);
  }
}

2. 子查询优化实战

案例：IN转EXISTS的源码路径

// sql/sql_resolver.cc
SELECT_LEX::resolve_subquery(/*...*/) {
  if (substype == Item_subselect::IN_SUBS) {
    // 尝试转换为EXISTS
    if (transform_in_to_exists()) { 
      Item_subselect *subq = new Item_exists_subselect(/*...*/);
      // 重写语法树节点
    }
  }
}

四、物理优化：访问路径选择源码剖析

1. 索引选择的代价计算

// sql/opt_range.cc
double test_quick_select(/*...*/) {
  // 遍历所有可能索引
  for (KEY *key = table->key_info; key != end; key++) {
    // 计算范围扫描成本
    Cost_estimate cost = check_quick_select(/*...*/);
    
    // 对比全表扫描成本
    if (cost < table->file->table_scan_cost()) {
      best_key = key;
    }
  }
}

2. 连接算法实现对比

Hash Join核心逻辑（MySQL 8.0+）

// sql/hash_join.cc
bool HashJoinIterator::Init() {
  // 构建阶段：填充哈希表
  while (m_build_input->Read()) {
    hash_table->put(m_build_key, row_data);
  }
}

bool HashJoinIterator::Read() {
  // 探测阶段
  while (m_probe_input->Read()) {
    HashTable::Iterator it = hash_table->find(probe_key);
    while (it != end) {
      // 返回匹配行
    }
  }
}

对比BNL实现差异

// sql/bnl_join.cc
bool BNLJoinIterator::Read() {
  // 使用join buffer批量加载
  while (load_buffer()) {
    for (probe_row in probe_table) {
      // 内存中循环匹配
    }
  }
}

五、临时表与排序的工程实现

1. 临时表存储引擎选择

// sql/sql_tmp_table.cc
TABLE *create_tmp_table(/*...*/) {
  // 根据字段类型选择存储引擎
  if (blob_count > 0 || total_length > max_heap_table_size) {
    table->s->db_type = TMP_TABLE_ENGINE; // 默认InnoDB
  } else {
    table->s->db_type = MEMORY_ENGINE;
  }
}

2. Filesort 多路归并排序

// sql/filesort.cc
bool filesort(/*...*/) {
  // 内存排序阶段
  while (get_next_sort_key()) {
    if (free_space < sort_key_size) {
      std::sort(/*...*/);  // 内存排序
      write_chunk_to_file(); // 写入临时文件
    }
  }
  
  // 多路归并
  Merge_chunk_array chunks(/*...*/);
  merge_buffers(/*...*/); // 使用优先队列合并
}

关键参数：

• sort_buffer_size (默认256KB)
• max_sort_length (默认1024字节)

六、优化器跟踪实现原理

optimizer_trace 工作流程

// sql/opt_trace.cc
void Opt_trace_context::start_query(/*...*/) {
  if (optimizer_trace) {
    // 创建JSON跟踪器
    opt_trace = new Opt_trace_object();
    opt_trace->add_utf8_table(/*...*/);
  }
}

// 在优化决策点埋入跟踪
void Cost_model_server::read_cost(/*...*/) {
  if (current_trace) {
    current_trace->add("io_cost", calculated_cost);
  }
}

七、执行计划生成关键路径

// sql/sql_planner.cc
void JOIN::make_join_plan() {
  // 1. 初始化单表访问路径
  for (table in join_tables) {
    table->best_access_path = find_best_access_path();
  }
  
  // 2. 多表连接优化
  while (tables_to_join = get_remaining_tables()) {
    // 动态规划搜索最优连接顺序
    for (pos in join_position) {
      current_cost = calculate_join_cost();
      if (current_cost < best_cost) {
        best_pos = pos;
      }
    }
    set_join_position(best_pos);
  }
  
  // 3. 生成AccessPath树
  root_path = CreateAccessPath(/*...*/);
}

八、性能优化实战建议

1. 统计信息陷阱：

-- 强制刷新某表统计信息
ANALYZE TABLE orders PERSISTENT FOR ALL;

2. 连接顺序调整：

/*+ JOIN_ORDER(customer, orders, payments) */
SELECT * FROM orders 
JOIN customers USING (cust_id)
JOIN payments USING (order_id);

3. 索引选择干预：

SELECT /*+ INDEX(orders, idx_order_date) */ *
FROM orders 
WHERE order_date > '2023-01-01';

附录：核心源码文件地图

模块	源码路径
查询解析	sql/sql_parse.y
逻辑优化	sql/sql_optimizer.cc
代价模型	sql/opt_costmodel.cc
访问路径优化	sql/opt_range.cc
连接优化	sql/sql_planner.cc
执行器	sql/sql_executor.cc
统计信息	storage/innobase/opt_stats