今天我们来聊一聊，mysql数据库索引失效的情况

原因：如果在索引列上使用了函数或表达式，MySQL 无法利用索引。

SELECT * FROM users WHERE LEFT(name, 3) = 'Tom';

解释：LEFT(name, 3) 需要先计算结果，索引无法作用。

优化建议：避免在索引列上使用函数，改为等效的条件：

SELECT * FROM users WHERE name LIKE 'Tom%';

原因：如果索引列的数据类型与查询条件的类型不一致，MySQL 可能会进行隐式转换，从而导致索引失效。

SELECT * FROM users WHERE phone = 1234567890;

解释：假设 phone 列是字符串类型，1234567890 是数字，MySQL 会对 phone 列进行类型转换。

优化建议：确保查询条件的类型与索引列的类型一致：

SELECT * FROM users WHERE phone = '1234567890';

原因：不等号查询通常无法利用 B-Tree 索引。

SELECT * FROM users WHERE age != 30;

解释：索引无法快速定位不等号的结果范围。

优化建议：尽量减少使用不等号，或者结合其他可以利用索引的条件。

原因：模糊查询中 % 开头会使 MySQL 无法通过索引快速匹配前缀。

SELECT * FROM users WHERE name LIKE '%Tom';

解释：% 表示任意字符开头，需要全表扫描。

优化建议：尽量避免 % 开头的模糊查询：

SELECT * FROM users WHERE name LIKE 'Tom%';

原因：如果 OR 的两边条件中，只有部分列有索引，索引可能会失效。

SELECT * FROM users WHERE age = 25 OR name = 'Tom';

解释：如果只有 age 列有索引，而 name 没有，则索引可能不被使用。

优化建议：对所有参与 OR 的列建立索引，或者改为 UNION：

SELECT * FROM users WHERE age = 25UNIONSELECT * FROM users WHERE name = 'Tom';

原因：在某些情况下，IS NULL 或 IS NOT NULL 查询会导致索引失效。

SELECT * FROM users WHERE age IS NOT NULL;

解释：索引通常不存储 NULL 值，因此需要扫描更多数据。

优化建议：如果业务逻辑允许，避免存储 NULL 值，使用默认值代替。

原因：对索引列进行排序或计算（如 +、-）可能导致索引失效。

SELECT * FROM users WHERE age + 1 = 30;

优化建议：改为不涉及计算的等效条件：

SELECT * FROM users WHERE age = 29;

原因：MySQL 的联合索引按照“最左前缀原则”匹配，如果跳过了某些前缀列，索引会失效。

CREATE INDEX idx_name_age ON users(name, age);SELECT * FROM users WHERE age = 25;

解释：name 是联合索引的第一列，查询中未使用，索引失效。

优化建议：查询条件中包含索引的最左列：

SELECT * FROM users WHERE name = 'Tom' AND age = 25;

原因：如果查询条件的过滤效果很差（如查询结果接近全表），优化器可能放弃使用索引。

SELECT * FROM users WHERE gender = 'M';

解释：如果表中 90% 的数据性别为 'M'，全表扫描可能比索引更快。

优化建议：对高选择性字段建立索引，避免对低选择性字段使用索引。

原因：范围查询（如 <、>）会导致 MySQL 使用索引扫描，而不是精确匹配。

SELECT * FROM users WHERE age > 10;

优化建议：结合其他索引列精确过滤范围。

原因：对于频繁更新的表，索引的维护成本较高，MySQL 优化器可能会选择放弃索引。

UPDATE users SET age = age + 1 WHERE name = 'Tom';

解释：在更新操作中，如果 name 有索引，则每次更新需要维护索引结构，可能导致性能降低。

优化建议：

原因：虽然模糊查询（LIKE）中非 % 开头的模式能利用索引，但复杂模式可能仍会失效。

SELECT * FROM users WHERE name LIKE 'T_m%';

解释：_ 表示匹配单个字符，MySQL 会对每个字符进行扫描，可能导致索引部分失效。

优化建议：如果查询模式复杂且频繁，可以考虑使用全文索引。

原因：在多表关联查询中，如果关联字段的数据类型不一致，索引可能无法生效。

SELECT * FROM orders o JOIN users u ON o.user_id = u.id;

解释：假设 orders.user_id 是字符串类型，而 users.id 是整数类型，索引失效。

优化建议：确保 JOIN 的关联字段类型一致。

原因：如果 ORDER BY 的字段没有索引，或 LIMIT 的条件超出索引能优化的范围，可能导致索引失效。

SELECT * FROM users ORDER BY age DESC LIMIT 100;

解释：如果 age 没有索引，则需要全表排序后截取前 100 条记录。

优化建议：对 ORDER BY 字段建立索引，并尽量避免大范围扫描。

原因：GROUP BY 操作需要对数据进行分组，如果没有合适的索引，可能会导致全表扫描。

SELECT COUNT(*), age FROM users GROUP BY age;

解释：如果 age 没有索引，则分组操作需要扫描整个表。

优化建议：对分组字段建立索引：

CREATE INDEX idx_age ON users(age);

原因：组合索引（联合索引）需要遵循最左前缀原则，如果查询中跳过了索引中间的列，索引会失效。

CREATE INDEX idx_name_age_gender ON users(name, age, gender);SELECT * FROM users WHERE name = 'Tom' AND gender = 'M';

解释：age 列被跳过，gender 列索引失效。

优化建议：确保查询条件包含联合索引的前缀列。

原因：对于分区表，某些操作可能导致索引失效。

SELECT * FROM orders WHERE partition_key = '2023' AND user_id = 123;

解释：如果 partition_key 是分区字段，分区范围之外的索引可能无效。

优化建议：合理设计分区字段，尽量减少分区字段以外的查询条件。

原因：当查询结果的数据量过大时，MySQL 优化器可能认为全表扫描比使用索引更高效。

SELECT * FROM users WHERE age > 10;

解释：如果 age > 10 的记录占表中绝大部分，索引的筛选效果不明显，索引可能失效。

优化建议：将大范围条件优化为多个小范围条件。

原因：统计函数如 AVG()、SUM() 等可能会导致索引无法完全发挥作用。

SELECT AVG(age) FROM users WHERE name = 'Tom';

解释：统计函数需要读取所有符合条件的数据，而索引通常用来定位特定行。

优化建议：尽量避免在频繁使用统计函数的列上建立索引。

原因：当表中删除了大量数据但未优化表时，索引中可能会残留无效数据，影响性能。

解决办法：执行以下命令：

OPTIMIZE TABLE table_name;

今天我们内容就分享到这儿，如果你觉得学到了知识，就给个关注。你的关注和点赞是我前进的动力！

扫码关注

一起积累后端知识

不积跬步，无以至千里

不积小流，无以成江海