#索引失效场景全解

-- 索引 idx(a, b, c)
WHERE b = 1                               ❌ 跳过 a
WHERE c = 1                               ❌ 跳过 a、b
WHERE a = 1 AND c = 1                     ❌ 跳过 b

-- 索引 idx(name)
WHERE name LIKE '%Tom%'                   ❌ 前缀通配，无法二分
WHERE name LIKE '%Tom'                    ❌ 同上
WHERE name LIKE 'Tom%'                    ✅ 后缀通配，可以二分

-- 索引 idx(a, b, c)
WHERE a = 1 AND b > 2 AND c = 3            -- a、b 命中，c 不命中
WHERE a = 1 AND b BETWEEN 1 AND 10 AND c = 3  -- 同上
WHERE a > 1 AND b = 2                      -- 只有 a 命中

-- 索引 idx(created_at)
WHERE YEAR(created_at) = 2024             ❌ 对索引列使用函数
WHERE created_at + INTERVAL 1 DAY = '...'  ❌ 对索引列进行运算
WHERE LEFT(name, 4) = 'Tom'               ❌ 函数包裹索引列

-- ✅ 正确的写法
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

-- name 是 VARCHAR，phone 是 VARCHAR
WHERE phone = 13800138000                  ❌ 字符串列用数字比较

-- age 是 INT，score 是 DECIMAL
WHERE age = '25'                           ✅ INT 兼容字符串，可以命中
WHERE score = '95.5'                      ✅ DECIMAL 兼容字符串
WHERE name = 123                          ❌ 字符串列用数字比较

-- name 有索引，status 没有索引
WHERE name = 'Tom' OR status = 1          ❌ OR 涉及无索引列，全表扫描
WHERE name = 'Tom' OR id = 5              ✅ 两个条件都有索引

-- 索引 idx(status)
WHERE status != 1                          ❌ NOT 等值
WHERE status NOT IN (1, 2, 3)              ❌ NOT IN
WHERE NOT (status = 1)                    ❌ 等效于上一条
WHERE status IS NOT NULL                   ⚠️ 部分场景可命中索引

-- 表只有 1000 行，idx(a) 选择性极低（80% 的行 a=1）
SELECT * FROM orders WHERE a = 1;           -- MySQL 可能选择全表扫描而非索引

-- 索引 idx(status)
WHERE status <> 1                          ❌ 不等于
WHERE status != 1                          ❌ 同上

-- order_no 是 VARCHAR 类型存储数字
WHERE order_no = 12345                     ❌ 字符串列用数字比较
WHERE order_no LIKE '12345%'               ✅ 可以命中

-- 表字符集 utf8mb4，字段排序规则 utf8mb4_general_ci
-- 查询用 utf8_general_ci 排序规则比较
WHERE name = 'Tom' COLLATE utf8_general_ci  ❌ 排序规则不一致可能导致索引失效

-- 表数据变化后，统计信息未更新
ANALYZE TABLE orders;                       -- 刷新统计信息

-- phone 是 VARCHAR
WHERE phone = 13800138000

WHERE CAST(phone AS SIGNED INT) = 13800138000

-- age 是 INT，值为 25
WHERE age = '25'                           ✅ 数字兼容字符串，可以命中索引

EXPLAIN SELECT * FROM orders WHERE status = 1;
-- type: ALL 表示全表扫描，ref 或 range 表示用了索引
-- key: 显示实际使用的索引
-- rows: 预计扫描行数

-- 场景：订单查询按 order_no（VARCHAR）查
-- 实际数据中 order_no 全是数字字符串
WHERE order_no = 12345                     -- 隐式转换，索引失效
WHERE order_no = '12345'                   -- 正常命中

-- MySQL 优化器选错了索引时，强制指定索引
SELECT * FROM orders FORCE INDEX (idx_status)
WHERE status = 1 AND created_at > '2024-01-01';

-- ❌ 改写前
WHERE YEAR(created_at) = 2024 AND status = 1

-- ✅ 改写后（区间查询代替函数）
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' AND status = 1

-- ❌ 改写前
WHERE name LIKE '%Tom%'

-- ✅ 改写后（前置搜索引擎）
-- 改用 Elasticsearch 处理模糊匹配

-- ❌ 改写前
WHERE status != 1

-- ✅ 改写后
WHERE status = 2 OR status = 3 OR status = 4 ...
-- 或分开查询后合并