MySQL索引失效的常见情况
MySQL索引的目的是为了提高查询效率，但并非所有查询都会使用到索引，或者有时索引会“失效”。索引失效意味着查询优化器决定不使用索引，而是进行全表扫描或其他效率较低的操作。以下是导致MySQL索引失效的常见情况：
对索引列进行函数操作 当你在查询条件中对索引列进行函数操作时（如LEFT()，RIGHT()，SUBSTRING()，DATE_FORMAT()，CONCAT()，数学运算等），MySQL优化器通常无法直接使用索引。 示例： SELECT * FROM users WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2023-01-01'; -- create_time是索引列
原因： 函数操作改变了列的原始值，导致索引无法直接匹配。 解决方案： 尽量避免在WHERE子句中对索引列使用函数。可以将函数作用于查询值，或创建函数索引（MySQL 8.0+）。 -- 更好的写法 SELECT * FROM users WHERE create_time >= '2023-01-01 00:00:00' AND create_time < '2023-01-02 00:00:00';
使用OR连接条件 当OR连接的条件中，某个条件列不是索引列，或者所有条件列都是索引列但优化器认为全表扫描更快时，索引可能会失效。 示例： SELECT * FROM products WHERE product_name = 'Laptop' OR description LIKE '%fast%'; -- product_name是索引，description不是索引
原因： 如果OR连接的条件中包含没有索引的列，为了满足所有条件，MySQL可能选择全表扫描。即使所有列都有索引，如果OR连接的条件过多或者返回的结果集过大，优化器也可能放弃使用索引。 解决方案：
- 确保OR连接的每个条件都涉及到索引列。
- 考虑使用UNION或UNION ALL来代替OR，将查询拆分为多个独立的查询。
- 如果条件复杂，可以考虑创建组合索引。
LIKE查询以%开头 当LIKE查询的模式以%开头时，例如%keyword，索引通常会失效。 示例： SELECT * FROM articles WHERE content LIKE '%MySQL%'; -- content是索引列
原因： B-tree索引是按照从左到右的顺序进行排序和查找的。当模式以%开头时，无法利用索引的有序性进行快速查找。 解决方案：
- 如果可能，避免以%开头。例如，使用keyword%可以利用索引。
- 如果确实需要模糊匹配，可以考虑使用全文索引（Full-Text Index）来提高效率。
数据类型不匹配或隐式转换 当查询条件中的数据类型与索引列的数据类型不一致时，MySQL可能会进行隐式类型转换，导致索引失效。 示例： SELECT * FROM products WHERE product_id = '123'; -- product_id是INT类型，但查询条件是字符串
原因： MySQL在进行比较前，需要将字符串'123'转换为整数，这个转换过程会使得索引无法被有效利用。 解决方案： 确保查询条件中的数据类型与索引列的数据类型严格一致。
使用IS NULL或IS NOT NULL 某些情况下，对允许为NULL的列使用IS NULL或IS NOT NULL时，索引可能失效。这取决于索引类型和数据分布。 示例： SELECT * FROM orders WHERE ship_date IS NULL;
原因： 如果列中包含大量NULL值，MySQL优化器可能会认为全表扫描更高效。 解决方案： 可以考虑为NULL值创建特殊的默认值，或者针对性地优化查询，但通常情况下，对NULL值的查询优化比较复杂。
负向查询 使用负向查询条件，如!=、NOT LIKE、NOT IN通常会导致索引失效。 示例： SELECT * FROM users WHERE status != 'active';
原因： 负向查询意味着要扫描所有不符合条件的数据，这通常不如正向查询能利用索引的范围查找优势。 解决方案： 考虑将负向查询转换为正向查询。例如，status != 'active'可以转换为status = 'inactive' OR status = 'pending'（如果状态是枚举值），前提是inactive和pending都有索引。
优化器认为全表扫描更快 在某些情况下，即使存在可用索引，MySQL优化器也可能决定进行全表扫描，因为：
- 数据量太小： 表中数据量非常小，使用索引的开销（如IO寻址）可能比全表扫描的开销更大。
- 返回结果集过大： 如果查询预计返回表中绝大部分数据（例如，查询结果占总行数的20%以上），优化器可能认为直接全表扫描更有效。
- 统计信息不准确： MySQL的统计信息不准确可能导致优化器做出错误的判断。 解决方案：
- 针对小表，无需过度优化索引。
- 对于返回大量数据的查询，全表扫描可能是正常的选择。
- 定期使用ANALYZE TABLE更新表的统计信息。
组合索引未遵循最左前缀原则 当使用组合索引（Composite Index）时，查询条件必须从索引的最左边列开始匹配，才能有效利用索引。 示例： 组合索引是(first_name, last_name, email) SELECT * FROM users WHERE last_name = 'Smith'; -- 索引失效，因为没有使用first_name
原因： 组合索引的构建是基于列的顺序的。如果查询条件没有从最左侧的列开始，或者中间跳过列，索引的有序性就被破坏了。 解决方案： 确保查询条件遵循组合索引的最左前缀原则。
使用IN但包含过多值 当IN子句中包含大量值时，即使IN的列有索引，也可能导致索引失效。 示例： SELECT * FROM products WHERE product_id IN (1, 2, ..., 10000); -- 包含大量ID
原因： 当IN列表过长时，MySQL优化器可能会认为遍历索引的成本高于全表扫描。 解决方案： 考虑分批查询，或者将值存入临时表并进行JOIN操作。 总结 了解这些索引失效的情况可以帮助你编写更高效的SQL查询，并更好地设计数据库索引。在实际开发中，可以使用EXPLAIN命令来分析SQL语句的执行计划，查看是否使用了索引以及如何使用，从而进行有针对性的优化。