sql优化方案

慎用!=、<>、OR、IS NULL、IS NOT NULL等,它们会导致索引失效,用以下方案

1. 使用范围查询替代不等于!=、<>

-- 原始低效查询
SELECT * FROM products WHERE status != 'out_of_stock';

-- 优化方案1：使用范围查询
SELECT * FROM products WHERE status < 'out_of_stock' OR status > 'out_of_stock';   -- 字符串
SELECT * FROM orders WHERE amount > 0 OR amount < 0;     -- 数值

3. 使用EXISTS/NOT EXISTS替代NOT IN
   在某些场景下，可以用EXISTS/NOT EXISTS替代不等于条件：

-- 查找没有订单的客户
SELECT * FROM customers c WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

为什么NOT EXISTS效率更高？
提前终止机制​：NOT EXISTS 子查询一旦找到第一条匹配记录就会停止扫描，而 NOT IN 需要遍历子查询的所有结果集，尤其在子查询数据量大时性能差异显著。
索引利用率​：NOT EXISTS 通常能利用子查询表的索引（如 customer_id 上的索引），而 NOT IN 可能导致全表扫描，尤其当子查询包含 NULL 值时性能更差。

2. 使用IS NULL/IS NOT NULL的优化
   对于允许NULL值的列，IS NOT NULL操作同样可能导致索引失效

-- 低效查询
SELECT * FROM customers WHERE email IS NOT NULL;

-- 优化方案：设置默认值替代NULL
SELECT * FROM customers WHERE email != '';  -- 前提是空字符串不是有效业务值

4. 使用UNION ALL替代OR
   OR操作符对索引列使用将造成全表扫描。UNION ALL是最高效的替代方案：

-- 原始低效查询
SELECT * FROM employees 
WHERE department = 'IT' OR salary > 10000;

-- 优化方案：使用UNION ALL
SELECT * FROM employees WHERE department = 'IT'
UNION ALL
SELECT * FROM employees WHERE salary > 10000;

5. 同一字段的OR条件优化
   当OR条件作用于同一字段时，可考虑以下优化

-- 原始查询
SELECT * FROM products 
WHERE category = 'electronics' OR category = 'books';

-- 优化方案1：使用IN
SELECT * FROM products WHERE category IN ('electronics', 'books');

-- 优化方案2：对连续值使用BETWEEN
SELECT * FROM orders 
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31';

6. 使用CASE WHEN重构复杂OR逻辑
   对于复杂的OR条件组合，可以使用CASE WHEN进行重构

-- 原始复杂OR查询
SELECT * FROM orders 
WHERE (status = 'paid' AND amount > 100) 
   OR (status = 'unpaid' AND amount > 200);

-- 优化方案：使用CASE WHEN
SELECT * FROM orders 
WHERE CASE 
    WHEN status = 'paid' AND amount > 100 THEN 1
    WHEN status = 'unpaid' AND amount > 200 THEN 1
    ELSE 0
END = 1;