什么情况下会导致索引失效？

索引失效主因包括对索引列使用函数、隐式类型转换、LIKE开头通配符、低基数列、复合索引未遵循最左前缀原则及统计信息过期等，导致优化器放弃索引而选全表扫描。

索引失效通常发生在数据库优化器无法有效利用索引来加速查询时，或者当索引本身因数据变更或结构问题而变得不再准确或效率低下时。这并非指索引被物理删除，而是它在特定查询场景下无法提供预期的性能优势，甚至可能因为维护成本而成为负担。

解决方案

索引失效的原因是多方面的，它既可能源于我们编写查询的方式，也可能与数据本身的特性或数据库的内部机制有关。

一个常见的情况是对索引列进行函数操作。当我们在

WHERE

YEAR(create_date) = 2023

LOWER(username) = 'admin'

隐式类型转换也是一个容易被忽视的陷阱。如果一个数字类型的列被索引，但在查询中我们用字符串字面量去比较（例如

WHERE user_id = '123'

通配符在

LIKE

WHERE description LIKE '%关键字%'

'关键字%'

当查询需要返回的数据量占总数据量的比例过大时，数据库优化器可能会判断进行全表扫描反而比走索引更高效。因为走索引需要先查找索引，再回表获取数据，如果回表次数过多，其IO开销可能远超一次性的全表扫描。

索引列的基数（Cardinality）过低，即该列的重复值太多，例如一个性别列只有“男”、“女”两个值，那么对这种列建立索引的意义就不大。当查询条件是

WHERE gender = '男'

复合索引（联合索引）的使用不当也是一个常见问题。如果查询条件没有包含复合索引的最左前缀列，那么这个复合索引就无法被有效利用。例如，索引是

(col1, col2, col3)

WHERE col2 = 'X'

数据库统计信息不准确或过时，是导致优化器做出错误判断的“幕后黑手”。优化器依赖这些统计信息来估算不同查询路径的成本，如果统计信息不准确，它可能会错误地选择全表扫描而不是索引扫描，或者反之。

此外，索引本身可能存在碎片化。随着大量的数据插入、更新和删除，索引页可能会变得不连续，导致物理存储上的碎片，从而增加IO操作，降低索引的效率。虽然这不直接导致“失效”，但会显著降低其性能。

索引失效与查询性能的深层关联：为何优化器会“放弃”你的索引？

我们常常疑惑，明明已经为某个列创建了索引，为什么查询还是那么慢？这背后其实是数据库成本优化器（Cost-Based Optimizer, CBO）在“作祟”。CBO的核心任务是为SQL查询找到一个执行成本最低的执行计划。它会综合考虑多种因素，如表的行数、索引的基数、数据块的大小、CPU和IO的成本模型，甚至还有内存使用情况。

当优化器决定“放弃”一个索引时，它通常是基于一个冷酷的成本计算：它认为使用索引的开销（包括读取索引页、回表获取数据等）比直接扫描整个表还要高。这在几种情况下尤为明显：

首先，数据选择性（Selectivity）是关键。如果一个索引列的选择性很低，比如一个布尔类型的列，其值只有

TRUE

FALSE

WHERE status = TRUE

其次，表的规模和查询的数据量。对于非常小的表，全表扫描几乎总是最快的，因为读取索引的额外开销（即使很小）也显得不必要。而对于大表，如果查询条件会返回表中很大比例的数据（例如超过20%到30%，具体阈值因数据库和配置而异），CBO会倾向于全表扫描。因为它计算出，通过索引定位少量数据块然后回表，不如一次性顺序读取所有数据块来得高效。顺序读取通常比随机读取更快，因为磁盘预读机制可以发挥作用。

再者，查询条件的复杂性也会影响优化器的决策。复杂的

OR

NOT IN

NOT EXISTS

纳米搜索

纳米搜索：360推出的新一代AI搜索引擎

30

查看详情

理解优化器的工作原理，我们才能更好地设计索引和编写SQL，避免那些让优化器“误解”我们意图的情况。它不是在“搞破坏”，而是在尽力寻找它认为的“最优解”，只是这个“最优解”不一定是我们期望的索引加速。

避免索引“隐性失效”的策略：常见误区与最佳实践

索引的“隐性失效”比物理删除更具迷惑性，因为它存在却不被使用。避免这种情况需要我们在SQL编写和数据库设计上多加注意。

一个最常见的误区就是在索引列上使用函数。例如，

CREATE_TIME

WHERE DATE_FORMAT(CREATE_TIME, '%Y-%m-%d') = '2023-01-01'

WHERE CREATE_TIME >= '2023-01-01 00:00:00' AND CREATE_TIME < '2023-01-02 00:00:00'

隐式类型转换同样是“隐性杀手”。假设

user_id

INT

SELECT * FROM users WHERE user_id = '100'

user_id

SELECT * FROM users WHERE user_id = 100

对于

LIKE

%

WHERE name LIKE '张%'

复合索引的“最左前缀原则”是必须牢记的。如果你有一个

(col1, col2, col3)

col1

WHERE col1 = 'A' AND col2 = 'B'

WHERE col2 = 'B' AND col3 = 'C'

col2

col3

此外，避免使用

OR

WHERE col1 = 'A' OR col2 = 'B'

UNION ALL

OR

IN

-- 错误示例：索引可能失效
SELECT * FROM products WHERE YEAR(created_at) = 2023;

-- 优化示例：利用索引
SELECT * FROM products WHERE created_at >= '2023-01-01 00:00:00' AND created_at < '2024-01-01 00:00:00';

-- 错误示例：隐式类型转换
SELECT * FROM users WHERE user_id = '123'; -- user_id 是 INT 类型

-- 优化示例：类型匹配
SELECT * FROM users WHERE user_id = 123;

-- 错误示例：LIKE开头通配符
SELECT * FROM articles WHERE content LIKE '%数据库%';

-- 优化示例：考虑全文索引或业务调整
-- 如果是前缀匹配，B-tree索引有效：
SELECT * FROM articles WHERE title LIKE '数据库%';

这些细节看似微不足道，却能决定索引是成为查询的加速器，还是仅仅占用磁盘空间的“摆设”。

索引维护与监控：确保索引持续有效性的关键步骤

索引并非一劳永逸的解决方案，它需要持续的关注和维护，才能确保其长期的有效性和性能。就像汽车需要定期保养一样，数据库索引也需要“体检”。

定期更新统计信息是重中之重。数据库优化器依赖准确的统计信息来估算数据分布和查询成本。随着数据的不断插入、更新和删除，表的行数、列的基数、数据分布等都会发生变化。如果统计信息没有及时更新，优化器就可能基于过时的数据做出错误的执行计划，导致索引失效。大多数数据库都提供了自动更新统计信息的机制，但对于高并发、数据变化频繁的核心表，可能需要手动或通过定时任务更频繁地进行更新。例如，在MySQL中可以使用

ANALYZE TABLE

UPDATE STATISTICS

索引碎片化管理是另一个重要的维护方面。当数据在表中频繁地进行插入、删除和更新操作时，索引的物理存储结构可能会变得不连续，产生碎片。这种碎片会导致索引扫描时需要读取更多的物理页面，从而增加IO开销，降低查询性能。解决索引碎片化通常有两种方法：重建（Rebuild）和重组（Reorganize）。重建索引会完全删除并重新创建索引，通常能更有效地消除碎片，但会占用更多资源并可能导致短暂的锁表。重组索引则是一个更轻量级的操作，它会整理现有索引页的顺序，通常可以在线进行，对系统影响较小。选择哪种方式取决于碎片的程度和业务对可用性的要求。

监控索引使用情况也至关重要。有些索引可能创建后从未被使用过，它们不仅浪费了存储空间，还增加了DML操作的维护成本。通过数据库提供的性能视图（如MySQL的

information_schema.STATISTICS

sys.dm_db_index_usage_stats

最后，性能基线和定期性能测试是发现索引问题的有效手段。通过建立常态化的性能监控体系，我们可以及时发现查询性能的下降，并结合执行计划分析工具（如

EXPLAIN

以上就是什么情况下会导致索引失效？的详细内容，更多请关注php中文网其它相关文章！