graph TD
    A[表A] --> B[表A+表B]
    B --> C[表A+B+表C]
    C --> D[表A+B+C+表D]

graph TD
    A[表A] --> AB[表A+表B]
    B[表B] --> AB
    C[表C] --> CD[表C+表D]
    D[表D] --> CD
    AB --> ABCD[最终结果]
    CD --> ABCD

SELECT * FROM a
JOIN b ON a.id = b.a_id
JOIN c ON b.id = c.b_id;

// 超边结构：表示连接关系
struct Hyperedge {
  // 超边连接的表（用bitmask表示，每个bit对应一个表）
  table_map vertices;
  // 超边的选择性（selectivity）：连接后结果集缩小的比例
  double selectivity;
  // 连接条件（如a.id = b.a_id）
  Item *condition;
  // 估算的连接成本
  Cost cost;
};

// 超图结构
class Hypergraph {
 public:
  // 表数量
  int num_vertices;
  // 超边列表
  std::vector<Hyperedge> edges;
  // 每个表的基础成本（扫描成本）
  std::vector<Cost> vertex_costs;

  // 构建超图
  void Build(const JOIN *join);
  // 枚举所有可能的切分
  std::vector<Cut> EnumerateCuts();
  // 计算切分成本
  Cost ComputeCutCost(const Cut &cut);
};

void Hypergraph::Build(const JOIN *join) {
  num_vertices = join->tables; // 表数量
  vertex_costs.resize(num_vertices);

  // 1. 计算每个表的基础扫描成本
  for (int i = 0; i < num_vertices; ++i) {
    const JOIN_TAB *tab = join->join_tab + i;
    // 表的扫描成本（全表或索引扫描）
    vertex_costs[i] = EstimateScanCost(tab);
  }

  // 2. 处理连接条件，生成超边
  for (const auto &cond : join->join_conditions) {
    Hyperedge edge;
    // 找出条件涉及的所有表
    edge.vertices = FindTablesInCondition(cond);
    // 计算连接选择性（估算结果集缩小比例）
    edge.selectivity = EstimateSelectivity(cond);
    // 计算连接成本
    edge.cost = EstimateJoinCost(cond);
    edges.push_back(edge);
  }
}

std::vector<Cut> Hypergraph::EnumerateCuts() {
  std::vector<Cut> cuts;
  // 从单个表开始，逐步扩大分组
  for (table_map subset = 1; subset < (1 << num_vertices); ++subset) {
    Cut cut;
    cut.subset = subset; // 切分的S组（用bitmask表示）
    cut.complement = ((1 << num_vertices) - 1) ^ subset; // T组

    // 找出跨界超边（同时包含S和T的超边）
    for (const auto &edge : edges) {
      if ((edge.vertices & subset) != 0 &&
          (edge.vertices & cut.complement) != 0) {
        cut.cross_edges.push_back(&edge); // 加入跨界超边
      }
    }

    // 只保留有跨界超边的切分（否则S和T无法连接）
    if (!cut.cross_edges.empty()) {
      cuts.push_back(cut);
    }
  }
  return cuts;
}

Plan FindOptimalJoinOrder(const Hypergraph &hg) {
  // DP表：dp[subset]表示连接subset表组的最优计划
  std::unordered_map<table_map, Plan> dp;

  // 初始化：单个表的计划就是其扫描成本
  for (int i = 0; i < hg.num_vertices; ++i) {
    table_map subset = 1 << i;
    dp[subset] = Plan{subset, hg.vertex_costs[i]};
  }

  // 逐步扩大表组，计算最优计划
  for (table_map subset = 1; subset < (1 << hg.num_vertices); ++subset) {
    // 枚举subset的所有可能切分
    for (const auto &cut : hg.EnumerateCutsForSubset(subset)) {
      table_map s1 = cut.subset;
      table_map s2 = subset ^ s1; // 子分组

      if (dp.count(s1) && dp.count(s2)) {
        // 计算合并s1和s2的成本
        Cost total_cost = dp[s1].cost + dp[s2].cost +
                          EstimateCrossCost(cut.cross_edges);

        // 更新最优计划
        if (!dp.count(subset) || total_cost < dp[subset].cost) {
          dp[subset] = Plan{subset, total_cost, {&dp[s1], &dp[s2]}};
        }
      }
    }
  }

  // 返回包含所有表的最优计划
  return dp[(1 << hg.num_vertices) - 1];
}

SELECT u.id, u.name,
  (SELECT COUNT(*) FROM login_log l WHERE l.user_id = u.id) AS login_cnt,
  (SELECT COUNT(*) FROM posts p WHERE p.user_id = u.id) AS post_cnt,
  (SELECT COUNT(*) FROM comments c WHERE c.user_id = u.id) AS comment_cnt,
  (SELECT COUNT(*) FROM likes k WHERE k.user_id = u.id) AS like_cnt
FROM users u
WHERE u.reg_time >= '2023-01-01';

SELECT g.guild_id, g.name,
  SUM(CASE WHEN b.winner_guild_id = g.guild_id THEN 1 ELSE 0 END) / COUNT(*) AS win_rate
FROM guilds g
JOIN players p ON g.guild_id = p.guild_id
JOIN battles_2023 b ON p.player_id = b.attacker_id OR p.player_id = b.defender_id
WHERE b.battle_time >= '2023-10-01'
GROUP BY g.guild_id, g.name;

ALTER TABLE a ADD INDEX idx_id (id);
ALTER TABLE b ADD INDEX idx_a_id (a_id);

-- 查询需要category_id和name
ALTER TABLE categories ADD INDEX idx_covering (category_id, name);

-- 先status（过滤掉大部分无效订单），再create_time
ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);

-- 原始子查询
SELECT * FROM users u WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000
);

-- 改写为JOIN
SELECT DISTINCT u.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;

-- 拆分前：10表连接
SELECT ... FROM a JOIN b JOIN c JOIN d JOIN e JOIN f JOIN g JOIN h JOIN i JOIN j ...

-- 拆分后
CREATE TEMPORARY TABLE temp1 AS
SELECT ... FROM a JOIN b JOIN c ...;

CREATE TEMPORARY TABLE temp2 AS
SELECT ... FROM d JOIN e JOIN f ...;

SELECT ... FROM temp1 JOIN temp2 JOIN g JOIN h ...;

-- 原始查询：过滤条件模糊
SELECT ... FROM orders o JOIN users u ON o.user_id = u.id;

-- 优化后：加入明确过滤
SELECT ... FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.create_time >= '2023-01-01' -- 订单表过滤
  AND u.reg_time >= '2023-01-01'; -- 用户表过滤