MySQL导致索引失效的几种情况

MySQL导致索引失效的几种情况

在使用MySQL数据库时，我们经常需要利用索引提高查询效率，但是有时候我们发现索引并没有起到预期的作用，这可能是索引被失效了，下面列举了几种常见的MySQL导致索引失效的情况：

1. 对索引列进行函数操作

如果查询条件中对索引列进行了函数操作，那么MySQL就无法使用这个索引了。

例如下面的查询语句：

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

如果create_time上有索引，那么这个索引将无法被使用，因为YEAR(create_time)的操作将会导致索引失效，此时可以考虑将2021也转为日期类型与create_time比较，或者将YEAR(create_time)的结果计算好后作为查询参数传递。

1. 在联合索引中未按照顺序使用

如果一个表上有多个索引，我们可以考虑使用联合索引提高查询效率。但是，如果我们在联合索引中未按照顺序使用，则索引也会失效。

例如下面的查询语句：

SELECT * FROM table WHERE name = 'John' AND age > 18;

如果表上有一个联合索引(name, age)，那么这个索引将无法被使用，因为查询时未按照索引中列的顺序使用。此时可以考虑创建一个(age, name)的索引，或者将查询改为WHERE age > 18 AND name = 'John'。

示例说明：

首先，我们建立一个名为student的表，其结构如下：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `age` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

接下来，我们向表中插入50000条模拟数据：

SET autocommit=0;
START TRANSACTION;
INSERT INTO student (name, age, create_time) VALUES
('Alex',20,'2021-01-01'),('Bob',23,'2021-01-02'),('Carl',26,'2021-01-03'),
('David',29,'2021-01-04'),('Eric',32,'2021-01-05'),('Frank',35,'2021-01-06'),
('Grace',38,'2021-01-07'),('Helen',41,'2021-01-08'),('Ivy',44,'2021-01-09'),
('Jack',47,'2021-01-10'),('Kelly',50,'2021-01-11'),('Lily',53,'2021-01-12'),
('Mike',56,'2021-01-13'),('Nancy',59,'2021-01-14'),('Oliver',62,'2021-01-15'),
('Paul',65,'2021-01-16'),('Queen',68,'2021-01-17'),('Robert',71,'2021-01-18'),
('Sam',74,'2021-01-19'),('Tracy',77,'2021-01-20'),('Ubaid',80,'2021-01-21'),
('Vivian',83,'2021-01-22'),('Wendy',86,'2021-01-23'),('Xander',89,'2021-01-24'),
('York',92,'2021-01-25'),('Zara',95,'2021-01-26');
INSERT INTO student (name, age, create_time)
SELECT `name`, `age`, DATE_ADD('2021-01-01', INTERVAL `id` DAY) FROM `student`;
COMMIT;

接下来，我们分别演示如何导致索引失效：

1. 对索引列进行函数操作：

我们使用下面的查询语句查询2021年4月份创建的学生记录：

SELECT * FROM student WHERE YEAR(create_time) = 2021 AND MONTH(create_time) = 4;

可以看到，虽然create_time上有一个索引，但是这个索引无法被使用，因为我们对create_time进行了YEAR和MONTH函数操作：

id  name    age create_time
--------------------------------------------------
909 Alex    20  2021-04-09 00:00:00
911 Carl    26  2021-04-11 00:00:00
913 Eric    32  2021-04-13 00:00:00
915 Grace   38  2021-04-15 00:00:00
917 Ivy 44  2021-04-17 00:00:00
919 Kelly   50  2021-04-19 00:00:00
921 Lily    53  2021-04-21 00:00:00
923 Mike    56  2021-04-23 00:00:00
925 Oliver  62  2021-04-25 00:00:00
927 Paul    65  2021-04-27 00:00:00
929 Robert  71  2021-04-29 00:00:00

如果我们将查询改为下面的形式，就可以使用索引了：

SELECT * FROM student WHERE create_time >= '2021-04-01' AND create_time < '2021-05-01';

1. 在联合索引中未按照顺序使用：

我们使用下面的查询语句查询年龄大于18岁且名字为Alex的学生记录：

SELECT * FROM student WHERE name = 'Alex' AND age > 18;

可以看到，虽然(name, age)上有一个联合索引，但是这个索引无法被使用，因为查询时未按照索引中列的顺序使用，如果我们将查询改为下面的形式，就可以使用索引了：

SELECT * FROM student WHERE age > 18 AND name = 'Alex';