Several situations of mysql index failure

Common situations: 1. Use functions or operations; 2. Implicit type conversion; 3. Use not equal to (!= or ); 4. Use the LIKE operator and start with a wildcard; 5. OR condition; 6. NULL value; 7. Low index selectivity; 8. Leftmost prefix principle of compound index; 9. Optimizer decision-making; 10. FORCE INDEX and IGNORE INDEX.

Indexes in MySQL are an important tool to help optimize query performance, but in some cases, indexes may not work as expected, i.e. index " Failure".

The following are some common situations that cause MySQL index failure:

1. : When using functions or performing operations on indexed columns, the index usually will not take effect. For example:

SELECT * FROM users WHERE YEAR(date_column) = 2023;

Here, YEAR(date_column) invalidates the index.
2. Implicit type conversion: When implicit type conversion is involved in the query conditions, the index may not be used. For example, if a column is of type string but is queried using numbers, or vice versa.

SELECT * FROM users WHERE id = '123';  -- 假设id是整数类型

3. Use inequality (!= or ): Using the inequality operator usually causes the index to fail because it requires scanning multiple values ​​of the index.

SELECT * FROM users WHERE age != 25;

4. Use the LIKE operator and start with a wildcard character: When the LIKE operator is used and the pattern starts with the wildcard character %, the index will usually not take effect.

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

5. OR conditions: When using OR conditions, if all the columns involved are not indexed, or one of the conditions causes index failure, then the entire query may be Indexes will not be used.

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

6. NULL值：如果索引列包含NULL值，并且查询条件涉及到NULL，索引可能不会生效。

SELECT * FROM users WHERE age IS NULL;

7. 索引选择性低：如果索引列中的值重复度很高（例如性别列只有“男”和“女”两个值），则索引可能不会被使用，因为全表扫描可能更为高效。
8. 复合索引的最左前缀原则：对于复合索引，查询条件必须满足最左前缀原则，否则索引可能不会生效。例如，如果有一个(a, b, c)的复合索引，那么只有a、(a, b)和(a, b, c)的组合才能充分利用索引。
9. 优化器决策：MySQL的查询优化器可能会基于统计信息和其他因素决定不使用索引，即使索引是存在的。这通常发生在它认为全表扫描比使用索引更快时。
10. FORCE INDEX和IGNORE INDEX：使用FORCE INDEX可以强制查询使用某个索引，而IGNORE INDEX则告诉优化器忽略某个索引。如果误用这些提示，可能会导致索引失效。

为了避免索引失效，建议：

• 仔细设计和选择索引列。
• 定期检查查询的性能，并考虑对查询进行优化。
• 使用EXPLAIN命令来查看查询的执行计划，并确定是否使用了索引。
• 监控数据库的性能，并定期更新统计信息。
• 考虑使用覆盖索引（Covering Index）来提高查询性能。

The above is the detailed content of Several situations of mysql index failure. For more information, please follow other related articles on the PHP Chinese website!