-- 索引：idx_name_age(name, age)

-- 提问：下面两个查询，哪个索引生效？
SELECT * FROM user WHERE name = '张三' AND age = 18;
SELECT * FROM user WHERE age = 18 AND name = '张三';

sequenceDiagram
    participant Q as SQL
    participant P as 分析器
    participant O as 优化器
    participant E as 执行器

    Q->>P: WHERE age=18 AND name='张三'
    P->>P: 解析为语法树（保留原始顺序）

    P->>O: 传递解析树
    O->>O: 分析可用索引: idx_name_age(name, age)
    O->>O: 发现 name 在索引最左边
    O->>O: 自动调整条件顺序: name='张三' AND age=18
    Note over O: ⭐ 优化器会做条件重排

    O->>E: 执行计划: 使用 idx_name_age
    E->>E: 按 name='张三' 定位<br/>再按 age=18 过滤

-- 验证两个查询的执行计划是否相同

CREATE TABLE t (
    id INT PRIMARY KEY,
    a INT,
    b INT,
    INDEX idx_a_b(a, b)
);

-- 插入测试数据
INSERT INTO t VALUES (1, 1, 2), (2, 3, 4), (3, 1, 3);

EXPLAIN SELECT * FROM t WHERE b = 4 AND a = 3;

+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t     | ref  | idx_a_b       | idx  | 5       | const |    1 | Using where |
+----+-------------+-------++------+---------------+------+---------+-------+------+-------------+

EXPLAIN SELECT * FROM t WHERE a = 3 AND b = 4;

-- 再看一个例子
EXPLAIN SELECT * FROM t WHERE a = 1 OR b = 4;
-- OR 条件优化器没法重排
-- a 和 b 是"或"关系，不是"与"关系
-- 可能不会使用联合索引

-- ✅ AND 连接的等值条件，可以任意顺序
WHERE a = 1 AND b = 2       → (a, b) 或 (b, a) 都行
WHERE b = 2 AND a = 1       → 自动重排为 (a, b)

-- ✅ 等值 + IN 也可以重排
WHERE a IN (1,2) AND b = 3  → 自动重排

-- ✅ 等值 + 范围
WHERE b > 5 AND a = 1       → 自动重排为 a=1 AND b>5

-- ❌ OR 连接的条件
WHERE a = 1 OR b = 2
-- 优化器无法将 OR 重排为 AND

-- ❌ 子查询
WHERE a = 1 AND id IN (SELECT ...)
-- 子查询的执行顺序受优化器复杂决策影响

-- ❌ 函数/表达式
WHERE a = 1 AND FUNC(b) = 2
-- 函数包裹的列优化器无法深入分析

-- 索引 idx_a_b_c(a, b, c)

-- ✅ 都满足最左前缀：包含 a
WHERE a = 1 AND c = 3 AND b = 2  -- 重排后 a=1, b=2, c=3
WHERE c = 3 AND b = 2 AND a = 1  -- 同上
WHERE b = 2 AND a = 1            -- a=1 存在，重排后

-- ❌ 不满足最左前缀：没有 a
WHERE b = 2 AND c = 3            -- 没有 a，无法重排
WHERE c = 3                      -- 没有 a

-- 好：逻辑清晰
SELECT * FROM orders
WHERE user_id = ? AND status = 'paid' AND created_at > '2024-01-01';

-- 差：逻辑混乱
SELECT * FROM orders
WHERE created_at > '2024-01-01' AND status = 'paid' AND user_id = ?;

-- 简单查询优化器能正确处理
-- 但复杂查询（多表 JOIN、子查询）中
-- 优化器的选择可能不是最优的

-- ✅ 推荐写法：索引列顺序 = WHERE 条件顺序
CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);

-- 查询时按索引列顺序写
SELECT * FROM orders
WHERE user_id = ?
  AND status = 'paid'
  AND created_at > '2024-01-01';