graph TD
    A[解析树] --> B[优化器]
    B --> C{核心决策}
    C --> D[索引选择<br/>用哪个索引?]
    C --> E[连接顺序<br/>多表怎么连?]
    C --> F[执行方式<br/>全表扫还是索引?]
    C --> G[子查询优化<br/>怎么改写?]
    D --> H[执行计划]
    E --> H
    F --> H
    G --> H
    H --> I[执行器执行]

-- 表结构
CREATE TABLE t (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    KEY idx_age(age),
    KEY idx_name(name)
);

-- 优化器需要决定用哪个索引
SELECT * FROM t WHERE age > 18 AND name = '张三';

SELECT * FROM a, b, c WHERE a.id = b.a_id AND b.id = c.b_id;

graph LR
    subgraph 方案A[成本100]
        A1[a表] --> A2[b表]
        A2 --> A3[c表]
    end

    subgraph 方案B[成本50]
        B1[b表] --> B2[a表]
        B2 --> B3[c表]
    end

    subgraph 方案C[成本200]
        C1[c表] --> C2[b表]
        C2 --> C3[a表]
    end

-- 原SQL
SELECT * FROM t WHERE id IN (SELECT id FROM t2);

-- 优化器可能改写为
SELECT * FROM t WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t.id);

-- 或者改写为半连接（semi-join）
SELECT * FROM t SEMI JOIN t2 ON t.id = t2.id;

-- 查看执行计划
EXPLAIN SELECT * FROM t WHERE age > 18 AND name = '张三';

-- 输出示例
+----+-------------+-------+------------+------+-----------------+----------+
| id | select_type | table | type       | key  | rows            | Extra    |
+----+-------------+-------+------------+------+-----------------+----------+
|  1 | SIMPLE      | t     | ref        | name | 1               | Using where |
+----+-------------+-------+------------+------+-----------------+----------+

-- 优化器不一定总能做出正确选择！
SELECT * FROM t WHERE id = 1;  -- 数据分布不均匀时可能选错索引

-- 强制使用索引
SELECT * FROM t FORCE INDEX(idx_age) WHERE age > 18;

-- 忽略索引
SELECT * FROM t IGNORE INDEX(idx_age) WHERE age > 18;

-- 指定连接顺序（STRAIGHT_JOIN）
SELECT * FROM a STRAIGHT_JOIN b ON a.id = b.a_id;