MySQL索引失效的几种情况小结

下面我来详细讲解MySQL索引失效的几种情况小结。

1. 索引列被函数操作

一般来说，对索引列的操作会使索引失效，例如：

SELECT * FROM table WHERE YEAR(create_time) = '2021';

上面这个语句中，对create_time列进行了YEAR()函数操作，这会导致该列上的索引失效。因此，在对索引列进行查询时尽量避免使用函数操作。

2. 索引列被隐式类型转换

在进行比较操作时，如果列类型和值类型不一致，MySQL会进行类型转换。如果索引列被隐式类型转换，也会导致该列上的索引失效，例如：

SELECT * FROM table WHERE id = '1';

在上面这个语句中，id列的类型为整数，但查询条件中的值为字符串类型，因此MySQL会进行类型转换，导致该列上的索引失效。因此，在进行比较操作时，应该保持列类型和值类型一致。

示例说明

假设我们有一张orders表，其中包含订单信息：

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  order_time DATETIME
);

INSERT INTO orders (user_id, order_time) VALUES (1, '2021-02-01 12:00:00');
INSERT INTO orders (user_id, order_time) VALUES (2, '2021-02-02 13:00:00');
INSERT INTO orders (user_id, order_time) VALUES (3, '2021-02-03 14:00:00');

现在我们要查询用户在2021年2月1日到2月3日之间的订单信息，可以使用以下两种查询语句：

第一种查询语句：

SELECT * FROM orders WHERE YEAR(order_time) = '2021' AND MONTH(order_time) = '2' AND DAY(order_time) BETWEEN '1' AND '3';

第二种查询语句：

SELECT * FROM orders WHERE order_time BETWEEN '2021-02-01' AND '2021-02-03 23:59:59';

这两个查询语句的结果相同，但是第一种查询语句对order_time列进行了函数操作，导致该列上的索引失效，效率较低；而第二种查询语句没有对order_time列进行函数操作，可以使用该列上的索引，效率较高。因此，在进行查询时尽量避免使用函数操作，以提高查询效率。