MySQL !=条件优化

在 MySQL 中使用 != 或 <> 过滤时，确实可能导致索引失效（尤其是当过滤条件的“排除范围”过大时，优化器可能认为全表扫描比索引更高效）。以下是针对此问题的优化方案，逐步分析解决：

1. 确认索引是否真正“失效”

首先通过 EXPLAIN 命令验证索引使用情况：

EXPLAIN SELECT * FROM orders WHERE user_id != 123;

观察结果中的 type 和 key 字段：

• 若 type 为 range 或 ref，且 key 显示使用了索引，则索引未完全失效。
• 若 type 为 ALL，则索引未生效，优化器选择全表扫描。

2. 索引失效的常见原因

a. 数据分布倾斜

• 场景：如果 user_id = 123 的订单占全表 90%，优化器认为“排除 10% 数据”不如全表扫描快。
• 解决方案：强制使用索引（需谨慎，可能适得其反）：

  SELECT * FROM orders FORCE INDEX(index_user_id) WHERE user_id != 123;
  

b. 索引选择性差

• 场景：user_id 基数（不同值数量）低（如只有 10 个用户），索引无法有效缩小范围。
• 解决方案：结合其他高选择性字段创建联合索引：

  ALTER TABLE orders ADD INDEX idx_user_status(user_id, status);
  

3. 优化方案

方案 1：改写为等值查询（最佳实践）

将 != 转换为 IN 或 NOT IN，利用索引范围扫描：

-- 假设已知所有可能的 user_id 列表（如 1,2,3,4...）
SELECT * FROM orders 
WHERE user_id NOT IN (123);

适用场景：排除的 user_id 是少数离散值。

方案 2：覆盖索引（减少回表）

创建覆盖索引，包含查询中所有需要返回的字段：

ALTER TABLE orders ADD INDEX idx_cover(user_id, order_no, amount, ...);

优势：

• 即使使用 !=，索引扫描后无需回表查数据页。
• EXPLAIN 的 Extra 字段会显示 Using index。

方案 3：分阶段查询（空间换时间）

1. 先查询正向匹配的数据（利用索引）：

   SELECT id FROM orders WHERE user_id = 123; -- 快速通过索引定位
   

2. 再排除这些 ID：

   SELECT * FROM orders 
   WHERE id NOT IN (SELECT id FROM orders WHERE user_id = 123);
   

适用场景：需要排除的 user_id 数据量极少。

方案 4：使用范围查询（谨慎使用）

若 user_id 是连续值，改写为范围查询：

SELECT * FROM orders 
WHERE user_id < 123 OR user_id > 123; -- 仅当 user_id 严格递增时有效

风险：若 user_id 不连续或存在小数，会导致数据遗漏。

4. 终极优化：调整数据模型

a. 增加状态字段

若业务允许，添加一个 is_self 标记字段：

ALTER TABLE orders ADD COLUMN is_self TINYINT(1) DEFAULT 0;
-- 更新数据
UPDATE orders SET is_self = 1 WHERE user_id = 123;
-- 查询时使用等值查询
SELECT * FROM orders WHERE is_self = 0;

优势：is_self 作为低基数字段，可结合其他条件创建高效索引。

b. 数据分区

按 user_id 分区，直接排除特定分区：

ALTER TABLE orders PARTITION BY HASH(user_id) PARTITIONS 10;

适用场景：数据量极大且查询通常集中在少数分区。

5. 性能对比总结

6. 最终建议

1. 优先尝试覆盖索引：

   ALTER TABLE orders ADD INDEX idx_cover(user_id, ...其他查询字段);
   

2. 若数据分布允许，改写为 NOT IN：

   SELECT * FROM orders WHERE user_id NOT IN (123);
   

3. 对高频查询，添加 is_self 标记字段，从根本上优化查询模式。

示例代码（Java + MyBatis）：

<select id="selectExcludingSelf" resultType="Order">
    SELECT * FROM orders 
    WHERE user_id NOT IN (#{userId})
    <!-- 或使用覆盖索引 -->
    <!-- WHERE user_id != #{userId} -->
</select>

通过合理选择索引策略、改写查询条件或调整数据模型，即使在使用 != 的场景下，仍能实现高性能查询。核心原则是减少扫描数据量和避免回表操作。