索引失效的场景分析

-- 索引
CREATE INDEX idx_a_b_c ON table(a, b, c);

-- ✅ 使用索引
WHERE a = 1;
WHERE a = 1 AND b = 2;
WHERE a = 1 AND b = 2 AND c = 3;

-- ❌ 不使用索引
WHERE b = 2;  -- 缺少a
WHERE c = 3;  -- 缺少a和b
WHERE b = 2 AND c = 3;  -- 缺少a

-- 索引
CREATE INDEX idx_a_b_c ON table(a, b, c);

-- ⚠️ 部分使用索引
WHERE a = 1 AND c = 3;  -- 只使用a，c失效

-- ❌ 失效：YEAR函数
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- ✅ 改进：使用范围查询
SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31 23:59:59';

-- ❌ 失效
WHERE DATE(created_at) = '2024-11-21';
WHERE LEFT(name, 3) = 'abc';
WHERE UPPER(email) = 'USER@EXAMPLE.COM';
WHERE price * 0.8 > 100;

-- ✅ 改进
WHERE created_at >= '2024-11-21' AND created_at < '2024-11-22';
WHERE name LIKE 'abc%';
WHERE email = 'user@example.com';  -- 应用层转小写
WHERE price > 125;  -- 100 / 0.8

-- 表结构
CREATE TABLE users (
    id INT,
    phone VARCHAR(20)  -- 字符串类型
);
CREATE INDEX idx_phone ON users(phone);

-- ❌ 失效：数字查询字符串列
SELECT * FROM users WHERE phone = 13800138000;  -- 数字
-- MySQL转换为：WHERE CAST(phone AS SIGNED) = 13800138000
-- 索引失效

-- ✅ 使用索引
SELECT * FROM users WHERE phone = '13800138000';  -- 字符串

-- 表结构
CREATE TABLE users (
    id INT,
    age INT  -- 数字类型
);
CREATE INDEX idx_age ON users(age);

-- ✅ 使用索引：字符串查询数字列
SELECT * FROM users WHERE age = '20';
-- MySQL转换为：WHERE age = CAST('20' AS SIGNED)
-- 索引仍然有效

-- ❌ 失效
WHERE name LIKE '%张三';
WHERE name LIKE '%abc%';

-- ✅ 使用索引
WHERE name LIKE '张三%';  -- 右模糊
WHERE name LIKE 'abc%';

-- 方案1：全文索引
CREATE FULLTEXT INDEX ft_name ON users(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张三');

-- 方案2：反转字符串（如果需要后缀搜索）
ALTER TABLE users ADD COLUMN name_reverse VARCHAR(50);
UPDATE users SET name_reverse = REVERSE(name);
CREATE INDEX idx_name_reverse ON users(name_reverse);
SELECT * FROM users WHERE name_reverse LIKE REVERSE('张三%');

-- ❌ 可能失效
SELECT * FROM users WHERE name = '张三' OR age = 20;
-- 如果name有索引，age没有索引 → 全表扫描

-- 方案1：都有索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);
-- OR两边都有索引，会使用index_merge

-- 方案2：改用UNION
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 20;

-- 方案3：改用IN（如果是同一列）
SELECT * FROM users WHERE age IN (20, 30, 40);

-- ❌ 可能失效
WHERE status != 'deleted';
WHERE status <> 'deleted';
WHERE NOT status = 'deleted';
WHERE age NOT IN (20, 30);
WHERE name NOT LIKE '%abc%';

-- 如果状态只有几种，用IN
WHERE status IN ('active', 'pending', 'completed');  -- 排除deleted

-- 或者反向思考
WHERE status = 'active';  -- 直接查询需要的状态

-- ⚠️ 可能失效，取决于NULL比例
WHERE column IS NULL;
WHERE column IS NOT NULL;

-- 尽量避免NULL
-- 方案1：设置DEFAULT
ALTER TABLE users ALTER COLUMN phone SET DEFAULT '';

-- 方案2：使用NOT NULL
ALTER TABLE users MODIFY phone VARCHAR(20) NOT NULL DEFAULT '';

-- 索引
CREATE INDEX idx_a_b_c ON table(a, b, c);

-- ⚠️ 只使用(a, b)
WHERE a = 1 AND b > 10 AND c = 3;
-- b是范围查询，c失效

-- 方案1：范围列放最后
CREATE INDEX idx_a_c_b ON table(a, c, b);
WHERE a = 1 AND c = 3 AND b > 10;  -- 使用(a, c, b)

-- 方案2：改为等值
WHERE a = 1 AND b IN (10, 11, 12, ..., 20) AND c = 3;

-- ❌ 失效
WHERE id + 1 = 100;
WHERE price * 0.8 > 100;
WHERE CONCAT(first_name, last_name) = '张三';

-- ✅ 改进
WHERE id = 99;  -- 100 - 1
WHERE price > 125;  -- 100 / 0.8
WHERE first_name = '张' AND last_name = '三';  -- 或使用生成列

-- 如果筛选出50%以上数据，优化器可能选择全表扫描
SELECT * FROM users WHERE gender = '男';  -- 50%数据

-- 强制使用索引（不推荐）
SELECT * FROM users FORCE INDEX (idx_gender) WHERE gender = '男';

-- 表只有几百行，全表扫描更快
SELECT * FROM small_table WHERE id = 10;
-- 即使id有索引，也可能全表扫描

EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- type: ALL（全表扫描）
-- key: NULL（未使用索引）

EXPLAIN SELECT * FROM users
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
-- type: range
-- key: idx_created_at

-- 1. 检查WHERE条件
-- 2. 检查是否使用函数
-- 3. 检查类型转换
-- 4. 检查LIKE模式
-- 5. 检查OR条件
-- 6. 检查联合索引顺序