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