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

╰+攻爆jí腚メ 2022-09-01 09:30 223阅读 0赞

# 索引失效情况（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都建立了索引，还是失效的，这个地方可以注意一下。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTcxMzUz_size_16_color_FFFFFF_t_70]

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

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

## 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”  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTcxMzUz_size_16_color_FFFFFF_t_70 1]

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

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

如果你写 where name =“1”,索引不会失效，但是你写name = 1，索引就会失效。因为发生了类型转换（数字1变为字符串1）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTcxMzUz_size_16_color_FFFFFF_t_70 2]

# 如有错误，请批评指正 #

# 参考 #

https://blog.csdn.net/guoxingege/article/details/51034387

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTcxMzUz_size_16_color_FFFFFF_t_70]: /images/20220829/0adf7a0367694d629bd3b7a9c225ce61.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTcxMzUz_size_16_color_FFFFFF_t_70 1]: /images/20220829/2c3a3d6a707146aaa22d6afcf0e6e70b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTcxMzUz_size_16_color_FFFFFF_t_70 2]: /images/20220829/473efbe378fa4e498b0a894b2d3cf3a0.png