MysSQL索引会失效的几种情况分析

CBeann

发布于 2023-12-25 19:10:35

740

发布于 2023-12-25 19:10:35

索引失效情况（MySQL5.7.24）

如果条件中有or，索引失效

DROP TABLE IF EXISTS `indexdemo`;
CREATE TABLE `indexdemo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of indexdemo
-- ----------------------------
INSERT INTO `indexdemo` VALUES ('1', '1', '1');
INSERT INTO `indexdemo` VALUES ('2', '2', '2');
INSERT INTO `indexdemo` VALUES ('3', '3', '3');

有的说or字段里没有索引的会索引失效，我在id和name都建立了索引，还是失效的，这个地方可以注意一下。

对于多列索引，不是使用的第一部分(第一个)，则不会使用索引

这个大家都是这么学的，最左前缀原则，就是对应这一条，但是我没有复现，所以不贴图了。

like “%开头” 会使得索引失效

DROP TABLE IF EXISTS `indexdemo`;
CREATE TABLE `indexdemo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `parkid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of indexdemo
-- ----------------------------
INSERT INTO `indexdemo` VALUES ('1', '1', '1', '1');
INSERT INTO `indexdemo` VALUES ('2', '2', '2', '2');
INSERT INTO `indexdemo` VALUES ('3', '3', '3', '3');

数据库中id,name都有索引，查询条件为 name like “%1”

查询条件中带（计算，函数，（自动或者手动）类型装换）会使索引失效

失效原因：索引的一个特性就是有序性，如果你（计算，函数，（自动或者手动）类型装换）了该字段，其实就是抹去了有序性，比如你做hash,那结果就是无序的，所以没办法使用索引。

DROP TABLE IF EXISTS `indexdemo`;
CREATE TABLE `indexdemo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `parkid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of indexdemo
-- ----------------------------
INSERT INTO `indexdemo` VALUES ('1', '1', '1', '1');
INSERT INTO `indexdemo` VALUES ('2', '2', '2', '2');
INSERT INTO `indexdemo` VALUES ('3', '3', '3', '3');

如果你写 where name =“1”,索引不会失效，但是你写name = 1，索引就会失效。因为发生了类型转换（数字1变为字符串1）