索引设计与优化

分析查询模式

查询 1：等值查询

SELECT * FROM orders WHERE user_id = 1 AND status = 1;

-- 索引方案 1：联合索引
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 分析：
-- 1. user_id 区分度高（假设 100万用户）
-- 2. status 区分度低（只有 5 个状态）
-- 3. 联合索引 (user_id, status) 效率高

-- 索引方案 2：两个独立索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);

-- 分析：
-- 1. MySQL 可能只使用一个索引（user_id）
-- 2. status 需要通过 Using where 过滤
-- 3. 不如联合索引高效

最优方案：

CREATE INDEX idx_user_status ON orders(user_id, status);

查询 2：排序查询

SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC;

-- 索引方案
CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);

-- 分析：
-- 1. user_id 用于过滤
-- 2. created_at 用于排序（避免 filesort）
-- 3. Extra: Using where, Using index (如果是覆盖索引)

最优方案：

CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);

查询 3：范围查询

SELECT * FROM orders WHERE status = 1 AND created_at >= '2024-01-01';

-- 索引方案
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 分析：
-- 1. status 用于过滤
-- 2. created_at 用于范围查询
-- 3. 注意：created_at >= 之后的部分不能使用索引

最优方案：

CREATE INDEX idx_status_created ON orders(status, created_at);

查询 4：聚合查询

SELECT user_id, COUNT(*) FROM orders WHERE status = 1 GROUP BY user_id;

-- 索引方案
CREATE INDEX idx_status_user ON orders(status, user_id);

-- 分析：
-- 1. status 用于过滤
-- 2. user_id 用于 GROUP BY（避免临时表）
-- 3. Extra: Using index (如果是覆盖索引)

最优方案：

-- 如果只需要 user_id
CREATE INDEX idx_status_user ON orders(status, user_id);

-- 如果需要其他字段，考虑覆盖索引
CREATE INDEX idx_status_user_amount ON orders(status, user_id, amount);

查询 5：IN 查询

SELECT * FROM orders WHERE user_id IN (1, 2, 3) AND status IN (1, 2);

-- 索引方案
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 分析：
-- 1. user_id IN (1, 2, 3) 可以使用索引（多次等值查询）
-- 2. status IN (1, 2) 可以使用索引
-- 3. 联合索引 (user_id, status) 有效

最优方案：

CREATE INDEX idx_user_status ON orders(user_id, status);

综合索引设计

-- 方案 1：多个独立索引（不够优化）
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created_at ON orders(created_at);

-- 方案 2：联合索引（推荐）
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 分析：
-- 查询 1: ✓ 使用 (user_id, status)
-- 查询 2: ✓ 使用 (user_id, created_at) - 部分使用
-- 查询 3: ✗ 不使用（user_id 在最左）
-- 查询 4: ✓ 使用 (user_id)
-- 查询 5: ✓ 使用 (user_id, status)

-- 方案 3：多个联合索引（最优）
CREATE INDEX idx_user_status ON orders(user_id, status);
CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 分析：
-- 查询 1: ✓ 使用 idx_user_status
-- 查询 2: ✓ 使用 idx_user_created
-- 查询 3: ✓ 使用 idx_status_created
-- 查询 4: ✓ 使用 idx_user_status
-- 查询 5: ✓ 使用 idx_user_status

最终推荐方案

-- 核心索引
CREATE INDEX idx_user_status ON orders(user_id, status);
CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 覆盖索引（如果查询频繁）
CREATE INDEX idx_user_status_created_amount ON 
    orders(user_id, status, created_at DESC, amount);

-- 删除冗余索引
-- DROP INDEX idx_user_id;  -- 被 idx_user_status 覆盖
-- DROP INDEX idx_status;   -- 被 idx_status_created 覆盖