联合索引最左前缀原则深度解析#

-- 创建联合索引
CREATE INDEX idx_a_b_c ON users (a, b, c);

-- 索引 (a, b, c) 下，以下查询能使用索引：

-- 1. 精确查 a (最左前缀)
WHERE a = 1                          -- ✅ 使用索引

-- 2. 精确查 a + b (左前缀)
WHERE a = 1 AND b = 2                -- ✅ 使用索引

-- 3. 精确查 a + b + c (完整左前缀)
WHERE a = 1 AND b = 2 AND c = 3      -- ✅ 使用索引

-- 4. 范围查 a + 精确查 b (a 是前缀，b 精确)
WHERE a > 1 AND b = 2                -- ⚠️ a 范围查询，b 只能用一部分

-- 5. 跳过 a，查 b (没有最左前缀)
WHERE b = 2                          -- ❌ 无法使用索引

-- 6. 跳过 a 和 b，查 c (没有最左前缀)
WHERE c = 3                          -- ❌ 无法使用索引

-- 7. 精确查 a + 范围查 c (a 精确，c 范围，b 断开了)
WHERE a = 1 AND c > 3                -- ⚠️ 只能用到 a，c 用不到

-- 场景 1: 用户表，查询以登录为主
-- 查询: WHERE email = ? 和 WHERE phone = ? 和 WHERE name = ?
-- 结论: 这三个查询是独立的，无法用一个联合索引覆盖，应该建三个独立索引

-- 场景 2: 订单表，查询以用户和状态为主
-- 查询: WHERE user_id = ? 和 WHERE user_id = ? AND status = ?
-- 结论: 可以建联合索引 (user_id, status)，覆盖两个查询

-- 场景 3: 报表查询
-- 查询: WHERE DATE(create_time) = ? (对日期函数处理后的值查询)
-- 结论: 无法使用 create_time 的索引，因为对列做了函数处理

-- ❌ 差的设计
CREATE INDEX idx_status_user ON orders (status, user_id);
-- status 只有 3 种值 (pending, paid, shipped)，区分度极低
-- 99% 的查询会走 user_id 过滤，status 索引几乎没用

-- ✅ 好的设计
CREATE INDEX idx_user_status ON orders (user_id, status);
-- user_id 区分度极高，先用 user_id 过滤，数据量骤减

-- 验证区分度
SELECT status, COUNT(*) as cnt FROM orders GROUP BY status;
-- 结果: pending=1000, paid=50000, shipped=100000
-- status 区分度 = 3 / 总行数 ≈ 0.00001（极低）

-- ❌ 错误设计
CREATE INDEX idx_age_status ON users (age, status);
-- 查询: WHERE age BETWEEN 18 AND 30 AND status = 'active'

-- age 是范围查询，只能用到 age 一列
-- status 因为范围查询中断，用不到

-- ✅ 正确设计
CREATE INDEX idx_status_age ON users (status, age);
-- 查询: WHERE status = 'active' AND age BETWEEN 18 AND 30

-- status 是等值查询，可以完全匹配
-- age 是范围查询，status 之后可以用到

-- IN 在某些情况下可以等价于多个等值查询，不中断索引
CREATE INDEX idx_a_b ON t (a, b);

-- 查询: WHERE a = 1 AND b IN (2, 3)
-- 等价于 a=1 AND b=2 OR a=1 AND b=3
-- MySQL 5.6+ 可以使用索引

-- 但: WHERE a IN (1, 2) AND b = 3
-- 只能用到 a 列，b 用不到（因为 a 是范围）

-- LIKE 'abc%' 是前缀匹配，在 B+Tree 中可以按序遍历
CREATE INDEX idx_name ON users (name);

SELECT * FROM users WHERE name LIKE '张%';
-- ✅ 可以使用 idx_name 索引，按字典序扫描所有 '张' 开头的记录

SELECT * FROM users WHERE name LIKE '%张%';
-- ❌ 无法使用索引，必须全表扫描

-- 方案 A: 三个单列索引
INDEX idx_a ON t(a);
INDEX idx_b ON t(b);
INDEX idx_c ON t(c);

-- 方案 B: 一个联合索引
INDEX idx_a_b_c ON t(a, b, c);

-- 场景 1: 函数处理导致索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2024;
-- ❌ YEAR() 函数导致无法使用 create_time 索引

-- ✅ 正确写法
SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

-- 场景 2: 类型转换导致索引失效
CREATE TABLE users (user_id VARCHAR(20));  -- 字符串类型
SELECT * FROM users WHERE user_id = 123456;  -- 数值 vs 字符串
-- ❌ 隐式类型转换导致索引失效

-- 场景 3: OR 条件导致索引失效
SELECT * FROM users WHERE name = '张三' OR age = 25;
-- 如果 age 上没有索引，OR 会导致整个查询无法使用 name 的索引

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

-- 关键字段解读:
-- type: ref (使用了索引的等值匹配) / ALL (全表扫描)
-- key: idx_user_status (实际使用的索引)
-- key_len: 索引使用到的字节数
--    - user_id BIGINT(8字节): key_len = 8
--    - user_id + status VARCHAR(50)*3 + 2: key_len = 8 + 152 + 2 = 162
-- rows: 预计扫描的行数
-- Extra: Using index condition (索引下推) / Using index (覆盖索引) / Using filesort (需排序)

-- 常见订单查询场景
-- 查询1: WHERE user_id = ? AND status = ?
-- 查询2: WHERE user_id = ? AND status = ? AND create_time > ?
-- 查询3: WHERE user_id = ?

-- 索引设计: (user_id, status, create_time)
-- 完美覆盖三个查询