cyxCoder

在MySQL中使用B+Tree索引时，以下查询场景可能导致无法使用索引而触发全表扫描（Full Table Scan）。理解这些场景有助于优化索引设计和查询语句。

一、导致索引失效的常见场景

1.1、不符合最左前缀原则

  联合索引（如INDEX(a, b, c)）要求查询条件必须从最左列开始，且连续。

-- 有效使用索引的条件：
WHERE a = 1;                    -- ✅使用索引a
WHERE a = 1 AND b = 2;          -- ✅使用索引a,b
WHERE a = 1 AND b = 2 AND c = 3;-- ✅使用索引a,b,c

-- 索引失效的条件：
WHERE b = 2;                    -- ❌未指定a，无法使用索引
WHERE a = 1 AND c = 3;          --  ❌跳过b，只能用到a的索引
WHERE b = 2 AND c = 3;          --  ❌未指定a，无法使用索引

解决方法：调整查询条件顺序或重建联合索引。

1.2、对索引列使用函数或表达式

对索引列进行运算或函数处理后，索引无法匹配原始值。

   -- 索引失效：
   WHERE YEAR(create_time) = 2023;      -- ❌ 函数操作
   WHERE amount * 2 > 100;              -- ❌ 表达式计算
   WHERE UPPER(name) = 'JOHN';          -- ❌ 函数操作
   -- 有效写法：
   WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'; -- ✅
   WHERE amount > 100 / 2;              -- ✅ 将计算移到右侧

1.3、隐式类型转换

查询条件与索引列类型不匹配，触发隐式转换。

   -- 假设user_id是字符串类型（VARCHAR）：
   WHERE user_id = 10086;               -- ❌ 数字转字符串，索引失效
   -- 有效写法：
   WHERE user_id = '10086';              -- ✅ 类型一致

1.4、使用OR连接非索引列

若OR的一侧字段无索引，优化器可能选择全表扫描。

   -- 假设age字段无索引：
   WHERE name = 'Alice' OR age = 25;     -- ❌ 若age无索引，触发全表扫描
   -- 解决方法：为age单独建索引或使用UNION优化：
   SELECT * FROM users WHERE name = 'Alice'
   UNION
   SELECT * FROM users WHERE age = 25;

1.5、范围查询后的列无法使用索引

联合索引中，若某一列使用范围查询（>、<、BETWEEN），其后的列无法继续使用索引。

   -- 索引(a, b, c)：
   WHERE a = 1 AND b > 10 AND c = 3;    -- ❌ 只能用到a和b的索引，c无法使用
   -- 解决方法：调整索引顺序（如`INDEX(a, c, b)`）或拆分查询。

1.6、使用!=或<>操作符

不等于操作符通常无法有效利用索引。

   WHERE status != 'deleted';            -- ❌ 可能触发全表扫描

1.7、LIKE以通配符开头

前缀模糊查询（如LIKE '%abc'）无法利用索引。

   WHERE title LIKE '%system';           -- ❌ 全表扫描
   WHERE title LIKE 'system%';           -- ✅ 使用索引（后缀匹配）

1.8、数据量过少时优化器放弃索引

表中数据量极少（如几十行），优化器可能认为全表扫描更快。

解决方法：强制使用索引（慎用）：

SELECT * FROM users FORCE INDEX(index_name) WHERE ...;

二、如何验证索引是否生效

使用EXPLAIN分析查询计划，关注以下字段：

•    type：若为ALL，表示全表扫描；ref或range表示使用索引。
•    key：显示实际使用的索引名称。
•    rows：预估扫描的行数（越小越好）。

示例：

EXPLAIN SELECT * FROM users WHERE name = 'John';

三、总结

• 不符合最左前缀：调整联合索引顺序或拆分索引。
• 索引列参与计算/函数：重写查询条件，避免对索引列操作。
• 隐式类型转换：确保查询条件与索引列类型一致。
• OR连接非索引列：改用UNION或为相关字段添加索引。
• 范围查询后的列失效：将等值查询列放在联合索引的前部。
• 不使用!=和<>
• like不以通配符开头
• 定期使用EXPLAIN分析慢查询，调整索引策略。

慢查询相关内容可以查看我的另一篇文章：《MySQL慢查询与查询优化器》