MySQL单值索引失效的几种场景

最新推荐文章于 2024-07-18 19:36:34 发布

Amy_Queen

最新推荐文章于 2024-07-18 19:36:34 发布

阅读量637

点赞数

分类专栏： mysql 文章标签： mysql 数据库 database

本文链接：https://blog.youkuaiyun.com/Amy_Queen/article/details/122326200

版权

mysql 专栏收录该内容

3 篇文章

订阅专栏

本文通过创建MySQL表并插入数据，测试了不同查询条件下的索引使用情况。当对tel字段进行正常查询、%通配符后跟查询、单个值的IN查询时，索引有效；但当出现隐性类型转换、%通配符前跟查询、多个值的IN查询、OR条件、使用函数等场景时，索引会失效。这有助于理解如何优化SQL查询以充分利用索引。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、测试准备
MySQL5.7 简单的创建一个表并准备几条测试数据

CREATE TABLE `t_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  `tel` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `t_user`(`id`, `name`, `tel`, `age`) VALUES (1, 'Alice', '13302546589', 30);
INSERT INTO `t_user`(`id`, `name`, `tel`, `age`) VALUES (2, 'Rock', '15203256984', 20);
INSERT INTO `t_user`(`id`, `name`, `tel`, `age`) VALUES (3, 'Amy', '15863257894', 22);

二、测试一下几种情况，索引是否失效

给tel字段添加索引

ALTER TABLE `t_user` ADD INDEX idx_tel (`tel`);

正常情况

EXPLAIN SELECT * FROM t_user WHERE tel = '13302546589';

在这里插入图片描述
显示使用索引

隐性类型转换，即where条件tel字段不加单引号

EXPLAIN SELECT * FROM t_user WHERE tel = 13302546589;

在这里插入图片描述
key为null，索引失效

使用 like 模糊查询
1）%通配符在后面

EXPLAIN SELECT * FROM t_user WHERE tel like  '13302546%';

在这里插入图片描述
索引没有失效

2）%通配符在前面

EXPLAIN SELECT * FROM t_user WHERE tel like  '%02546589';

在这里插入图片描述
索引失效

使用 in 查询
1）in 里面只有一个

EXPLAIN SELECT * FROM t_user WHERE tel in  ('13302546589')

在这里插入图片描述
索引没有失效

2） in 里面有多个

EXPLAIN SELECT * FROM t_user WHERE tel in  ('13302546589','15203256984');

索引失效

使用 or

EXPLAIN SELECT * FROM t_user WHERE tel = '13302546589' OR age = 30;

在这里插入图片描述
索引失效

使用函数

EXPLAIN SELECT * FROM t_user WHERE MD5(tel) = '3546b734c57ba7f77f4fae80bf3d9069';

索引失效