索引不是建了就一定能用上。优化器会在背后评估各种执行计划的成本，如果它认为使用索引的代价比全表扫描还高，或者 SQL 的写法导致索引无法被利用，那么索引就会“失效”。了解这些失效场景，比死记硬背索引创建规则更有用，因为大多数慢查询都出在这些点上。
12.3.1 对索引列进行函数运算或表达式操作
这是最经典也最常见的索引失效原因。当你在 WHERE 子句中对索引列做了函数调用、数学运算或类型转换等操作时，优化器无法直接利用索引中存储的原始值进行查找，只能逐行计算出结果再比对，从而导致索引失效。
典型错误示例：
-- 对索引列使用函数
SELECT * FROM orders WHERE DATE(create_time) = '2025-01-01';
-- 对索引列进行运算
SELECT * FROM products WHERE price * 0.9 > 100;
-- 隐式的字符集转换函数（有时开发者也无意中触发）
SELECT * FROM users WHERE CONVERT(name USING utf8mb4) = '张三';第一个查询中，create_time 列上建有索引，但被 DATE() 函数包裹后，优化器必须把所有行的 create_time 都取出来，调用 DATE() 得到一个日期再与 '2025-01-01' 比较。索引本身存储的是完整的日期时间值，无法直接用于按“纯日期”查找。
正确写法： 把函数操作移到索引列的对立面——也就是把值进行同样的转换，使索引列保持不变。
SELECT * FROM orders
WHERE create_time >= '2025-01-01 00:00:00'
AND create_time < '2025-01-02 00:00:00';对于数学运算，也应当反向操作：
-- 错误：price * 0.9 > 100
-- 正确：将不等式化为 price > 100 / 0.9
SELECT * FROM products WHERE price > 111.1111;根本原因： 索引中存储的是列的原始值，查询需要依据这个原始值进行快速定位。任何对列本身的变动都会破坏这种直接对应关系，优化器只能选择全索引扫描或全表扫描。
12.3.2 隐式类型转换
MySQL 在比较不同类型的值时，会自动进行类型转换。当索引列是字符串类型，但查询时传入了数字，或者反过来，就会发生隐式转换。这种转换相当于在索引列上应用了函数，自然导致索引失效。
经典案例： 一个通过手机号查询用户的语句。
-- phone 列定义为 VARCHAR(20)，且建有索引
SELECT * FROM users WHERE phone = 13800138000;这里 13800138000 是一个整数，而 phone 是字符串。MySQL 会将字符串列的每个值都转换成数字再进行比较，等同于 CAST(phone AS UNSIGNED) = 13800138000，索引失效。
验证方法： 执行 EXPLAIN 后，key 列为 NULL，并且 Extra 中可能显示 Using where，同时扫描行数很大。
解决方式： 保持查询值的类型与列定义一致。
SELECT * FROM users WHERE phone = '13800138000'; -- 加引号反过来，如果列是数值类型，传入字符串则通常不会导致失效，因为字符串会被转换为数字，转换发生在常量一侧，不影响索引列。但为了规范，依然建议保持类型一致。
根源： 类型转换规则导致索引列被函数化。牢记一条原则：让索引列保持“干净”，把转换操作放在常量或绑定变量一侧。
12.3.3 模糊查询以通配符开头
对于字符串类型的索引列，LIKE 查询能否使用索引，取决于通配符的位置：
LIKE 'abc%'：前缀匹配，B+ 树索引可以利用前缀顺序快速定位到以abc开头的区间，索引有效。LIKE '%abc'：后缀匹配，无法确定起点，索引失效。LIKE '%abc%'：中间匹配，同样无法缩小扫描范围，索引失效。
示例：
-- 索引可用
SELECT * FROM articles WHERE title LIKE 'MySQL优化%';
-- 索引失效
SELECT * FROM articles WHERE title LIKE '%优化实战';当必须要做前后都有的模糊搜索时，可以考虑使用 MySQL 内置的全文索引（Full-Text Index），或者借助 Elasticsearch 等搜索引擎。
如果业务场景中频繁需要按后缀匹配（如邮箱后缀 @example.com），可以在插入时额外存储一个反转字段，并为其建立索引，然后通过反转查询实现前缀匹配：
-- 新增一个 reversed_email 列，存放反转的邮箱字符串
SELECT * FROM users WHERE reversed_email LIKE REVERSE('@example.com') || '%';12.3.4 联合索引不满足最左前缀
联合索引（复合索引）遵循“最左前缀”原则：只有当查询条件中包含了索引定义时从左到右的连续列时，该索引才能被有效利用。
假设有一个联合索引 INDEX idx_a_b_c (a, b, c)，那么：
WHERE a = 1—— 可用索引（使用a列）。WHERE a = 1 AND b = 2—— 可用索引（使用a, b两列）。WHERE a = 1 AND b = 2 AND c = 3—— 完整使用索引。WHERE b = 2—— 索引失效，因为没有从最左边的a开始。WHERE a = 1 AND c = 3—— 只能用到a列，c因为跳过了b，无法利用索引排序和过滤，但可能通过索引下推对c进行筛选（仅减少回表，不能缩小扫描范围）。WHERE a = 1 AND b > 2 AND c = 3—— 能用到a, b，c列因为在范围条件b > 2之后，无法继续使用索引进行等值匹配（范围查询会截断后续列）。
最左前缀的核心原因： B+ 树索引是先按第一列排序，第一列相同再按第二列排序，以此类推。因此，跳过第一列直接查第二列时，索引中的顺序无法帮助快速定位，只能扫描整个索引。
实际建议： 在设计联合索引时，把等值查询条件中区分度最高的列放在前面，把 = 条件涉及的列尽可能前置，把 >、<、BETWEEN 这类范围条件涉及的列放在后面。
12.3.5 范围查询对右侧列的截断
当联合索引中某一列使用了范围条件（>、<、>=、<=、BETWEEN、LIKE 'abc%'），那么从这列开始，其右侧的所有列都无法继续使用该索引进行精确匹配和排序了。
比如索引 (age, city, salary)，查询：
SELECT * FROM employees
WHERE age > 25 AND city = 'Beijing' AND salary > 10000;此查询能用到的索引部分只有 age，因为 age > 25 是范围，之后不管是 city 还是 salary 都无法继续利用索引形成精确限定，索引对于后续列只能起到过滤作用（依靠索引下推可以减少回表，但扫描的索引范围由 age > 25 决定）。
若想利用更多列，可以调整列顺序，将范围列后移，或者把 city 这种高选择性等值列放在范围列之前，范围列尽量靠后。比如创建 INDEX idx_city_age (city, age)，再查询 city = 'Beijing' AND age > 25，这样索引就能使用 (city, age) 两列。
12.3.6 OR 连接非索引列
OR 条件两边只要有一个列没有索引，或者两个列在不同的索引中无法合并，就可能导致索引失效，整体退化为全表扫描。
情形一： 一列有索引，另一列无索引。
-- id 有索引，status 无索引
SELECT * FROM orders WHERE id = 100 OR status = 'pending';MySQL 无法用 id 上的索引来过滤出 status = 'pending' 的行，只能全表扫描，再用 OR 条件逐一判断。
解决方式： 将查询改写为 UNION 或者 UNION ALL（如果确认不会重复）：
SELECT * FROM orders WHERE id = 100
UNION
SELECT * FROM orders WHERE status = 'pending';两次查询分别利用各自的索引（status 若无索引仍会全表扫描，但至少 id = 100 能走索引），最后合并结果。
情形二： 两列分别有索引，但优化器不能将它们合并成一个范围扫描。这时有时优化器仍会选择性使用其中一个索引，再回表后用另一个条件过滤，或者再次退化。针对这种情况，用 UNION 改写通常是行之有效的手段。
12.3.7 索引列参与比较的字段类型不匹配
不只是隐式类型转换，当两个列进行比较时，如果它们的字符集或排序规则（collation）不一致，也会因为隐式转换导致索引失效。
-- t1.name 和 t2.name 都建了索引，但字符集不同
SELECT * FROM t1 JOIN t2 ON t1.name = t2.name;若 t1.name 是 utf8mb4_general_ci，t2.name 是 utf8mb3_general_ci，MySQL 可能会对其中一列应用字符集转换，导致该列的索引失效。这在大表联接中会引发性能问题。保持数据库内统一的字符集设置是规避此类问题的最好方法。
12.3.8 使用 !=、<>、NOT IN、NOT EXISTS 等否定式条件
否定式条件通常会导致索引失效，因为索引擅长“找到某个值”，而不擅长“找所有不是该值的行”。大多数情况下，MySQL 会放弃索引而选择全表扫描。
SELECT * FROM users WHERE status != 'active';
SELECT * FROM products WHERE category_id NOT IN (10, 20, 30);即使 status 或 category_id 上有索引，优化器通常也会判断全表扫描成本更低，因为不等于或不在某个集合内的行往往占了数据的绝大部分，扫描索引再回表反而不划算。
如果 category_id 的可选择性很高，上述查询也可以用全索引扫描完成（虽然用到了索引，但只是遍历整个索引叶子节点），这仍然比全表扫描快。但从实际效果看，开发者无法强制索引，可以考虑改写为等值条件的组合，或者从业务层面避免此类频繁查询。
12.3.9 不恰当的 JOIN 顺序和 ORDER BY 使用
在多表联接时，如果驱动表上缺少适合的过滤条件，或者 ORDER BY 引用了不同表的列，可能导致索引利用不充分甚至排序使用文件排序（Using filesort）。虽然不完全是索引“失效”，但效果上索引的排序优势丧失。
典型场景：查出某个用户的所有订单，按订单创建时间倒序。
SELECT o.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.name = '张三'
ORDER BY o.create_time DESC;如果 orders 表上的索引只有 (user_id)，没有 (user_id, create_time)，那么尽管能通过索引找到指定用户的订单，但排序需要额外的临时表或文件排序。给 orders 加上联合索引 (user_id, create_time)，就能利用索引有序性消除排序开销。
12.3.10 表数据量过小或统计信息偏差
有时候索引并没有写错，查询也没犯上述错误，但优化器依然选择了全表扫描。这通常是因为：
- 表数据量很小：几行或几十行的表，全表扫描成本极低，使用索引还需要回表，效率可能不如直接扫描。这是优化器的理智选择。
- 统计信息不准：优化器依赖
innodb_table_stats和innodb_index_stats中的统计信息，如果数据发生大量增删改后没有及时更新统计信息，优化器可能错误估算扫描行数，做出不合理的执行计划。
可以用 ANALYZE TABLE table_name; 手动更新统计信息，MySQL 8.0 也支持直方图统计用以辅助优化器。
排查手段： 看到 EXPLAIN 中 rows 估计值远远偏离实际行数时，就应考虑统计信息问题。利用 SHOW INDEX FROM table_name; 查看索引基数（Cardinality），基数接近 0 或明显失真时需要更新。
这些失效场景归纳起来，本质都是优化器无法基于给定条件在 B+ 树中完成高效定位。开发者在写 SQL 时，多在心里模拟一遍索引树的查找过程，就能自然避开绝大多数陷阱。更重要的是，任何优化结论都必须以 EXPLAIN 的输出为准，千万不要靠猜测。