索引失效指MySQL未使用预期索引，导致查询效率下降甚至全表扫描。通过EXPLAIN分析执行计划，观察type=ALL或key为NULL可判断索引未命中。常见原因包括：WHERE子句中数据类型不匹配、索引列使用函数或表达式、OR连接无索引条件、NOT负向查询、范围查询过大、联合索引不满足最左前缀原则。此外，统计信息不准、索引选择性低、MySQL版本或配置问题也会导致失效。避免方式包括遵循最左前缀原则、避免在索引列上使用函数、确保数据类型一致。模糊查询以%开头、低区分度字段如性别建索引效果差。可使用FORCE INDEX强制走索引，但不推荐作为常规手段。当频繁增删改导致索引碎片、数据量大或查询变慢时，应考虑重建索引，通过ALTER TABLE或OPTIMIZE TABLE整理碎片，建议在低峰期执行。
索引失效，简单来说，就是MySQL在执行查询时，本应该用上的索引没用上，导致查询效率直线下降，甚至全表扫描。
排查MySQL索引失效，需要从以下几个方面入手：
-
你的查询：这是最基础也是最重要的步骤。在你的SQL查询语句前加上，执行后MySQL会返回查询的执行计划。关注列，表示全表扫描，基本可以确定索引失效了。列会告诉你可能用到的索引，列会告诉你实际用到的索引，如果为，那也说明索引失效了。列显示了索引中被使用字节的数量，通过这个值可以计算具体使用了索引中的哪些列。
-
检查WHERE子句：WHERE子句是索引发挥作用的关键区域。
- 数据类型不匹配：如果列类型是字符串，而你在WHERE子句中使用了数字，MySQL可能会进行隐式类型转换，导致索引失效。例如，，如果是VARCHAR类型，就可能导致索引失效。
- 使用了函数或表达式：在索引列上使用函数（如、、）或表达式，会导致索引失效。例如，。
- OR条件：如果OR连接的多个条件中，有一个条件没有索引，那么整个查询都可能导致索引失效。尽量避免在WHERE子句中使用OR。
- NOT条件：、、、等负向查询，通常会导致索引失效。
- 范围查询：范围查询（如、、、、）在某些情况下可能导致索引失效，特别是当范围过大时。MySQL会评估使用索引的成本，如果全表扫描更快，它会选择全表扫描。
- 最左前缀原则：对于联合索引，必须从索引的最左边的列开始使用，否则索引会失效。例如，如果有一个联合索引，那么是无法使用该索引的。
-
索引统计信息：MySQL的查询优化器依赖于索引统计信息来做出最佳的执行计划。如果统计信息不准确，可能会导致优化器错误地选择不使用索引。可以使用命令来更新表的统计信息。
-
索引选择性：索引的选择性是指索引列中不同值的数量与表总行数的比率。选择性越高，索引的效果越好。如果索引的选择性很低，MySQL可能认为使用索引的成本高于全表扫描，从而放弃使用索引。
-
MySQL版本和配置：不同版本的MySQL在索引优化方面可能有所不同。同时，一些配置参数（如）也会影响索引的使用。
索引失效的场景很多，但归根结底都是因为MySQL的优化器认为使用索引的成本高于全表扫描。
- 模糊查询以开头：，这种情况下索引无法使用。尽量避免使用前缀模糊查询，或者考虑使用全文索引。
- 联合索引不满足最左前缀原则：确保查询条件包含联合索引的最左边的列。
- WHERE子句中使用了计算或函数：避免在索引列上使用计算或函数。可以将计算或函数应用在WHERE子句的右侧。
- 数据量小：当表的数据量很小时，MySQL可能认为全表扫描更快。
- 区分度低的字段：例如，性别字段，只有男女两种值，建立索引的意义不大。
虽然通常情况下MySQL的优化器会做出正确的选择，但在某些特殊情况下，我们可能需要强制MySQL使用特定的索引。可以使用提示。
但是，强制使用索引通常不是一个好的做法。它可能会掩盖更深层次的问题，例如索引设计不合理或统计信息不准确。建议先排查问题，优化SQL语句和索引设计，而不是简单地强制使用索引。
索引在长期使用过程中可能会产生碎片，影响查询性能。重建索引可以整理索引碎片，提高查询效率。以下情况可以考虑重建索引：
- 频繁的INSERT、UPDATE、DELETE操作：这些操作会导致索引碎片增多。
- 表数据量很大：大表的索引更容易产生碎片。
- 查询性能下降：如果发现查询性能明显下降，可以考虑重建索引。
重建索引可以使用命令：
或者使用命令，它会重建表并更新统计信息：
注意：重建索引是一个耗时操作，特别是对于大表，应该在业务低峰期进行。
以上就是mysql如何排查索引失效的详细内容，更多请关注php中文网其它相关文章！