MySQL索引失效

表定义

CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_phone` (`phone`) USING BTREE,
  KEY `idx_name_age_gender` (`name`,`age`,`gender`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入数据

INSERT INTO `t_user` (`id`, `name`, `age`, `gender`, `email`, `address`, `phone`) VALUES (1, 'Lee', 23, '男', 'lee@qq.com', '深圳', '18888888888');
INSERT INTO `t_user` (`id`, `name`, `age`, `gender`, `email`, `address`, `phone`) VALUES (2, 'Mike', 22, '男', 'mike@qq.com', '北京', '19999999999');
INSERT INTO `t_user` (`id`, `name`, `age`, `gender`, `email`, `address`, `phone`) VALUES (3, 'Marry', 18, '女', 'marry@qq.com', '上海', '10086');
INSERT INTO `t_user` (`id`, `name`, `age`, `gender`, `email`, `address`, `phone`) VALUES (4, 'Neo', 25, '男', 'neo@qq.com', '深圳', '10010');
INSERT INTO `t_user` (`id`, `name`, `age`, `gender`, `email`, `address`, `phone`) VALUES (5, 'Sara', 24, '女', 'sara@qq.com', '广州', '10000');

数据库版本：

8.0.28

索引：

1. 主键索引：id
2. 联合索引：idx_name_age_gender
3. 唯一索引：idx_phone

1 联合索引不符合最左匹配原则

联合索引需要遵守最左匹配原则，在本文的示例中，联合索引idx_name_age_gender的最左字段是name，则在查询时想要走索引，就必须保证name出现在where条件中。

示例一：

EXPLAIN SELECT * FROM t_user WHERE `name` = 'lee' AND `age` = 23 AND `gender` = '男'

结果：上述查询走了idx_name_age_gender索引。

示例二：

EXPLAIN SELECT * FROM t_user WHERE `name` = 'lee' AND `age` = 23

结果：因为where条件中只有name和age，所以联合索引只走了name和age两列。

示例三：

EXPLAIN SELECT * FROM t_user WHERE `name` = 'lee' AND `gender` = '男'

结果：在where条件中，使用了name和gender，走了联合索引的name列，由于没有使用age，所以联合索引从中间断开了，就没有走到gender列的索引。

示例四：

EXPLAIN SELECT * FROM t_user WHERE `age` = 23 AND `name` = 'lee'

结果：在where条件中，列的先后顺序不会影响到索引，上面虽然name在age之后，但是同样会走联合索引的name和age列。

示例五：

EXPLAIN SELECT * FROM t_user WHERE `age` = 23

结果：但是如果在where条件中没有使用name字段，则不会走联合索引，因为不符合最左匹配原则。同样的，下面这些语句都不会走idx_name_age_gender索引。

EXPLAIN SELECT * FROM t_user WHERE `gender` = '男'
EXPLAIN SELECT * FROM t_user WHERE `age` = 23 AND `gender` = '男'

2 模糊查询时占位符条件位于首部

当使用模糊查询关键字like时，有以下三种情况：

1. like ‘%lee%’
2. like ‘%lee’
3. like ‘lee%’

由于1和2中的占位符都在条件的首部，所以1和2是不会走索引的，3的占位符不在首部，所以会走索引。

示例一：

EXPLAIN SELECT * FROM t_user WHERE `name` LIKE 'Lee%'

结果：当占位符不在首部时，可走索引。

示例二：

EXPLAIN SELECT * FROM t_user WHERE `name` LIKE '%Lee%'
EXPLAIN SELECT * FROM t_user WHERE `name` LIKE '%Lee'

结果：当占位符在首部时，不走索引。

3 类型隐式转换导致索引失效

当参数类型为int类型，但是实际索引字段为varchar类型时，MySQL会发生隐式类型转换从而导致索引失效。

示例一：

EXPLAIN SELECT * FROM t_user WHERE `phone` = 10086

结果：在示例中的phone字段为varchar类型，但是SQL语句中的参数为int类型，发生了隐式类型转换，所以导致当前SQL可能走的索引是idx_phone，但是实际没有走这个索引。

只需要将参数加上单引号就可以正常走索引。

示例二：

EXPLAIN SELECT * FROM t_user WHERE `phone` = '10086'

结果：参数和字段类型一致，正常走索引。

4 索引列参与计算

示例一：

EXPLAIN SELECT * FROM t_user WHERE `id` + 1 = 3

结果：由于在SQL语句中id字段参与了计算，所以导致索引失效。

示例二：

EXPLAIN SELECT * FROM t_user WHERE `id` = 3 - 1

结果：参数列参与计算不影响，可正常走索引。

5 索引列使用函数

示例一：

EXPLAIN SELECT * FROM t_user WHERE SUBSTR(`phone`, 1, 3) = '100'

结果：虽然phone列有索引，但是由于索引列使用了函数所以导致索引失效。

6 OR的错误使用

当使用OR关键字时，必须保证OR关键字两边的字段都有索引，才会走索引，如果只有一边的字段有索引则不会走索引。

示例一：

EXPLAIN SELECT * FROM t_user WHERE `id` = 3 OR `address` = '深圳'

结果：由于OR左边的id字段有索引但是右边的address没有索引，所以索引失效。

示例二：

EXPLAIN SELECT * FROM t_user WHERE `id` = 3 OR `phone` = '10086'

结果：OR左边的id和右边的phone字段均有索引，所以可正常走索引。

7 使用select *

示例一：

EXPLAIN SELECT `name`,`age`,`gender` FROM t_user WHERE `age` = 23

结果：当select * 时使用联合索引并且不遵循最左匹配原则时，索引失效。

示例二：

EXPLAIN SELECT `name`,`age`,`gender` FROM t_user WHERE `age` = 23

结果：当select后面的字段均为索引列时，那么where后的条件虽然不遵循最左匹配原则，也可走索引。

这里使用到的是覆盖索引的知识，当select后面的字段都是索引列时，那么这些索引称为覆盖索引，查询这些字段就能够走索引，而使用select * 时，查询的是所有字段，这样就存在查询非索引列的数据，就不会走索引。

8 列对比

示例一：

EXPLAIN SELECT * FROM t_user WHERE `id` = `phone`

结果：当两个列进行比较时，即便两列都有索引，也会导致索引失效。（本示例中id和phone比较仅仅是作为演示，而且两个字段的类型不一致，即使类型一致也不会走索引，可自行测试）

9 not in和not exists

在范围查询中常用的有in、exists、not in、not exists和between and，在这些范围查询中，in、exists和between and都会走索引，not in如果是主键索引则会走索引，如果是普通索引则会失效，not exists同样也会索引失效。

示例一：

explain select * from t_user where `id` in (2,3)
explain select * from t_user u1 where exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id)
explain select * from t_user where `id` between 1 and 2

以上所用的in、exists和between and都会正常走索引，可自行测试。

示例二：

EXPLAIN SELECT * FROM t_user WHERE `phone` NOT IN ('2','3')

结果：普通索引列使用not in时索引会失效

示例三：

EXPLAIN SELECT * FROM t_user WHERE `id` NOT IN ('2','3')

结果：主键索引列使用not in时正常走索引。

示例四：

EXPLAIN SELECT * FROM t_user u1 WHERE NOT EXISTS (SELECT 1 FROM t_user u2 WHERE `phone` = '10086' AND u1.id = u2.id)

结果：使用not exists时索引会失效，即使是主键索引同样会失效。

10 order by

示例一：

EXPLAIN SELECT * FROM t_user ORDER BY `id`

结果：order by后面接主键列可走主键索引。

示例二：

EXPLAIN SELECT * FROM t_user ORDER BY `phone`
EXPLAIN SELECT * FROM t_user ORDER BY `phone` LIMIT 10

结果：order by后面接普通索引列时，索引失效。

示例三：

EXPLAIN SELECT * FROM t_user ORDER BY `name`
EXPLAIN SELECT * FROM t_user ORDER BY `name`, `age`
EXPLAIN SELECT * FROM t_user ORDER BY `name`, `age`, `gender`

结果：order by后面接联合索引列，并遵循最左匹配原则，同样索引失效，包括在后面加上limit关键字同样索引失效。

示例四：

EXPLAIN SELECT * FROM t_user WHERE `name` = 'Lee' ORDER BY `age`,`gender`

结果：配合where一起使用，并遵循最左匹配原则时，where后面的索引有效，order by后面的索引照样失效，加limit关键字同样失效。

之前在网上有看到，如果在使用order by时，加上where关键字和limit关键字并且遵守最左匹配原则就会正常走索引，但是本文在MySQL8.0.28版本下这些都不会走索引，推断可能是由于数据库版本的原因，如果有使用其他版本的可以自行尝试一下order by在这些情况下是否会走索引。