MySQL索引失效的几种情况详析

一、全值匹配被模糊查询所取代

在使用MySQL查询时，如果需要使用索引进行优化查询，应当尽可能的使用全值匹配的方式进行查询，而不是使用模糊查询。因为模糊查询无法利用索引，会导致索引失效。例如：

-- 使用全值匹配查询
SELECT * FROM users WHERE username='Tom';

-- 使用模糊查询
SELECT * FROM users WHERE username LIKE '%Tom%';

上述两个查询语句中，第一个查询使用了全值匹配查询，可以利用users表的username列索引进行优化查询，而第二个查询中，使用了模糊查询，无法利用索引进行优化，会导致索引失效。

二、联合索引中包含了不必要的列

联合索引是指在一个索引中，包含多个列。如果在联合索引中包含了不必要的列，那么查询时可能会出现索引失效的情况。例如，有一个用户表users，包含了id、username和password三个字段，建立了一个联合索引：

ALTER TABLE users ADD INDEX idx_username_password(username, password);

当执行以下查询语句时，联合索引idx_username_password将会失效，因为查询语句使用了不必要的列id：

SELECT * FROM users WHERE id=1 AND username='Tom';

因此，在设计联合索引时，需要避免包含不必要的列。

三、表之间的关联条件没有使用索引

如果在两个表进行JOIN操作时，关联条件没有使用索引，那么查询时可能会出现索引失效的情况。例如，有两个表users和orders，在orders表中建立了一个外键user_id，指向users表的主键id，并且在users表中建立了一个索引idx_id：

ALTER TABLE orders ADD CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(id);
CREATE INDEX idx_id ON users(id);

当执行以下查询语句时，因为ORDER BY子句使用了表orders的列，而不是使用users表的索引，会导致索引失效：

SELECT * FROM users
JOIN orders ON users.id = orders.user_id
ORDER BY orders.order_date;

为了避免这种情况的发生，应当尽可能的使用索引来连接表，例如：

SELECT * FROM users
JOIN orders ON users.id = orders.user_id AND orders.order_date > '2022-01-01'
ORDER BY users.username;

上述查询语句中，使用了联合索引idx_id_user_id和users表的索引idx_username，可以避免索引失效的情况。

总结

以上是MySQL索引失效的几种情况，分别是全值匹配被模糊查询所取代、联合索引中包含了不必要的列、表之间的关联条件没有使用索引。在进行MySQL查询时，应当尽可能的避免出现以上情况，以便能够使用索引进行优化查询，提高查询效率。