【实用指南】十招防范MySQL索引失效

MySQL索引失效情况概述：

尽管在字段上创建了索引，但使用时可能不会利用索引来查询，这种情况被称为索引失效。以下是一些常见的索引失效场景：

1. 不满足最左前缀原则：在使用联合索引时，查询条件必须符合最左前缀原则，即从索引的第一列开始匹配。

• 错误示例：违反最左前缀原则（有联合索引idx_username_age）

select * from user where age = 30;

• 解决方案：

select * from user where username = 'Alice' AND age = 30;

2. 使用SELECT *：查询时使用SELECT *而非指定所需列，会影响索引的使用效率，尽管这不会导致索引失效。

• 错误示例：

SELECT * FROM users WHERE sex = '男';

• 解决方案：

1、使用 SELECT column1, column2, ... 明确指定需要的列。

2、确保查询条件能够有效利用索引，即使使用了 SELECT *。

SELECT * 本身并不直接导致索引失效，但可能会导致性能问题，尤其是在处理大型表时。当使用 SELECT * 时，数据库需要返回表中所有的列，包括很多不需要的数据，并且会阻止数据库使用某些索引，特别是当返回的数据量较大时，错误示例就会返回大量的数据，用户数据要么是男性，要么是女性。

3. 索引列参与运算:在索引列上执行函数或运算会使得索引无法使用。

• 错误示例：

SELECT * FROM users WHERE YEAR(birth_date) = 1990;

• 解决方案：

1、使用表达式索引：如果 YEAR(birth_date) 是一个常见的查询条件，可以考虑为 birth_date 列创建一个表达式索引，也称为“功能性索引”。

ALTER TABLE users ADD INDEX idx_birth_date_year (YEAR(birth_date));

2、避免使用函数：如果能重构查询以避免在 WHERE 子句中使用函数。例如，如果你正在处理一个历史数据集，并且知道 birth_date 列中的所有日期都是格式良好的，可以直接查询特定的日期范围：

SELECT * FROM users WHERE birth_date BETWEEN '1989-01-01' AND '1990-12-31';

4. 类型不匹配:查询条件与索引列类型不匹配时，索引不会使用。

• 错误示例：

SELECT * FROM users WHERE username = 123;（username为字符串类型）

• 解决方案：

SELECT * FROM users WHERE username = ‘123’;（username为字符串类型）

5. 使用OR连接条件:使用OR连接多个条件，如果这些条件不共用同一个索引，会导致索引失效。有两个索引：一个在username 列上，另一个在 age 列上

• 错误示例：

SELECT * FROM users WHERE username = 'Alice' OR age = 30;

• 解决方案：

1、创建复合索引：如果 OR 子句中使用的列经常一起被查询，可以考虑创建一个包含这些列的复合索引。

例如，查询经常根据 username 和 age 进行搜索，可以创建一个 (username, age) 的复合索引。

2、分离查询：将查询分离成两个独立的查询，每个查询使用一个索引，然后在应用程序层面合并结果。

3、评估查询需求：如果 OR 子句的使用不是很频繁，或者其对性能的影响不大，可能不需要采取特别的优化措施。

4、使用 UNION：在某些情况下，可以分别对每个条件执行查询，并将结果使用 UNION 或 UNION ALL 合并，这样可以利用到索引。

5、查询重写：考虑重写查询逻辑，以使用能够利用索引的等价条件。

6. 使用LIKE查询:LIKE查询中如果使用了前缀通配符%，则无法使用索引。

• 错误示例：

SELECT * FROM users WHERE username LIKE '%Alice';

• 解决方案：如果需要全模糊，可以使用全文索引full text

select * from sys_menu where MATCH(username) AGAINST('%Alice');

7. 使用IS NULL或IS NOT NULL:对可能为NULL的列使用IS NULL或IS NOT NULL可能会使索引失效。

• 错误示例：

SELECT * FROM users WHERE email IS NOT NULL

• 解决方案：

1、确保索引存在：首先，确保在 email 列上确实存在索引。

2、使用 EXPLAIN 分析查询：使用 EXPLAIN 关键字来查看查询的执行计划，确认是否使用了索引。

EXPLAIN SELECT * FROM users WHERE email IS NOT NULL;

3、考虑索引的选择性：如果 email 列包含大量 NULL 值，MySQL 优化器可能会认为全表扫描比使用索引更高效。在这种情况下，考虑是否需要索引 NULL 值。

4、避免全表扫描：如果查询优化器认为全表扫描更优，考虑是否可以通过增加合适的索引来改善性能。

5、使用覆盖索引：如果查询只返回 email 列，可以考虑使用覆盖索引（如果 email 列的值不重复，或者重复值的数量在可接受范围内）。

6、避免复杂的 NULL 值处理：尽量避免在查询中使用多个与 NULL 值相关的条件，因为它们可能会使查询优化变得更加复杂。

7、考虑查询重写：如果可能重写查询以避免使用 IS NULL 或 IS NOT NULL，例如，使用 LEFT JOIN 和检查 JOIN 的结果来代替。

8、优化数据模型：如果 NULL 值不是必需的，考虑将列定义为 NOT NULL，并提供一个合理的默认值。

9、数据库版本：确认你的数据库版本是否支持在 IS NULL 或 IS NOT NULL 条件下使用索引。不同版本的MySQL可能有不同的优化策略。

说明：MySQL的查询优化器会根据统计信息和成本估算来决定是否使用索引。如果优化器认为使用索引的成本高于全表扫描，可能会选择不使用索引。因此，使用

EXPLAIN 分析查询并根据实际情况调整索引策略是非常重要的

8. 强制类型转换:隐式类型转换会导致索可能引失效。

• 错误示例：

SELECT * FROM users WHERE id = '123';（假设id为整数类型）

• 解决方案：

SELECT * FROM users WHERE id =123;

9. 使用NOT:使用NOT操作符，尤其是与IN或EXISTS子句结合使用时，可能会导致索引失效。

• 错误示例：

SELECT * FROM users WHERE NOT EXISTS (SELECT * FROM orders WHERE users.id = orders.user_id);

• 解决方案：

1、使用 JOIN 替代：如果可能，使用 LEFT JOIN 和 IS NULL 来替代 NOT EXISTS。

SELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.user_id IS NULL;

2、确保相关列上有索引：确保 users 表的 id 列和 orders 表的 user_id 列上有索引。

CREATE INDEX idx_users_id ON users(id); CREATE INDEX idx_orders_user_id ON orders(user_id);

3、分析执行计划：使用 EXPLAIN 关键字分析查询，查看是否使用了索引。

EXPLAIN SELECT * FROM users WHERE NOT EXISTS (SELECT * FROM orders WHERE users.id = orders.user_id);

4、优化查询条件：如果子查询中的表 (orders) 有过滤条件，确保这些条件能够利用索引。

10. 使用BETWEEN: 使用BETWEEN且范围的开始值不确定时，可能不会使用索引。

• 错误示例：

SELECT * FROM users WHERE id BETWEEN ? AND 1000;（如果?的值未知）

• 解决方案：

留一道给大家思考

11. 排序规则不一致:如果列的排序规则与查询条件中的值不一致，索引可能不会使用。

• 错误示例：

SELECT * FROM users WHERE full_name = 'alice';（如果full_name列有特定的排序规则）

• 解决方案：

1、确保列值匹配：查询条件中的值应该与列的排序规则和大小写规则相匹配。

-- 假设 full_name 列是按照某种排序规则（如二进制排序）存储的 SELECT * FROM users WHERE full_name = 'alice' COLLATE utf8_bin;

2、使用 COLLATE 关键字：在查询条件中使用 COLLATE 关键字指定排序规则，以匹配列的排序规则。

3、统一数据输入：在数据输入时，确保使用一致的排序规则，避免数据存储时的不一致性。

4、考虑数据清洗：如果数据中存在不一致性，考虑对现有数据进行清洗，统一排序规则。

在使用索引时，应考虑是否有上面的情况，以避免索引失效。当怀疑索引未被使用时，可以使用EXPLAIN关键字分析查询的执行计划，以确定是否真的存在性能问题，并据此进行优化。