同事说，组合索引加上，但是没生效

发表于

这是我参与11月更文挑战的第20天，活动详情查看：2021最后一次更文挑战

写在前面

最近可能和Mysql系列杠上了，同事又来问我了。正好我也学习下，我一直坚信一句话，你教别人的同时 自己也能从中收获到以前没有认知到的东西。感谢同事 感谢努力，感谢现在正在看文章的你。

以前我一直有个认知，组合索引必须按照顺序，否则会让索引失效。

所以当同事问我的时候，我毫不犹豫的说，请检查你组合索引的顺序。哈哈 翻车了

组合索引匹配规则

直接进入正题，给下面该表添加上组合索引

1
2
3
4
5
6
7
8
9
10
11
12
java复制代码CREATE TABLE `t_book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cover` varchar(255) DEFAULT '',
  `title` varchar(255) NOT NULL DEFAULT '',
  `author` varchar(255) DEFAULT '',
  `date` varchar(20) DEFAULT '',
  `press` varchar(255) DEFAULT '',
  `abs` varchar(255) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_title_author_press` (`title`,`author`,`press`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8;

我们添加了一个index_title_author_press 组合索引，将title（书名），author（作者），press（出版社） 组合在一起。

1 、接着我们explain一下，全值匹配的情况

1
2
java复制代码explain select * from t_book where title = '且在人间' 
and author = '余秀华' and press= '湖南文艺出版社';

image.png

可以看到 正常走索引了

2、接着 我们换下顺序，验证下上面的我一直认为换顺序会影响索引的想法

1
2
java复制代码explain select * from t_book where title = '且在人间' and press= '湖南文艺出版社'
and author = '余秀华' ;

image.png

可以看到 无论怎么变换顺序，都是正常走索引的。where后的顺序会在Mysql优化器中进行重组，不会失效

3、那么我们考虑下如果缺失组合索引中其中一个字段呢 ？

1
java复制代码explain select * from t_book where title = '且在人间' and press= '湖南文艺出版社'

结果如下图。虽然passible_keys 和 key都有值 但是大家看下Extra

image.png

Using index condition 这个什么意思，只是表示：用到了索引作为查询条件。查找使用了索引，但是需要回表查询数据，后面我会针对这个详细讲下，

总之一句话 Using index condition需要回表查询数据，所以当你组合索引中缺少一个字段。效率会大大降低，这也是同事遇到的问题

最左匹配

上面我们说到组合索引中字段缺少一个便会影响索引，那么下面大家看这种情况，还记得上面我们的组合索引顺序依次是KEY index_title_author_press (title,author,press)

那么我们依照最左匹配法试下

1
2
3
java复制代码explain select * from t_book where title = '且在人间' and author = '余秀华' 

explain select * from t_book where title = '且在人间'

image.png

看到没，依照最左匹配法则，依然正常走索引，不会影响效率。

接下来我们看like匹配是什么情况

模糊匹配

1
2
3
java复制代码1）explain select * from t_book where title like '且在%'

2）explain select * from t_book where title like '%人间'

上面哪种会索引失效，毋庸置疑第二种会直接索引失效。但是第一种会走索引，但是和上面缺字段的那个情况是一样的，会出现Using index condition，需要回查表，所以总之建议，索引不要进行like匹配

OK 今天关于组合索引就说这么多，当然还有注意点和优化点。我这里就不一一赘述了，后面遇到我们再一起学习下

下面来进行几点重要的总结

总结

  • 不要在索引上进行任何计算
  • 不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），会导致索引失效而转向全表扫描（机制）
  • 尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询
  • 组合索引遵循最左匹配
  • 最后最重要的是，创建索引的列，不允许为null，可能会得到不符合预期的结果

OK 我们下期再见。一起努力

弦外之音

感谢你的阅读，如果你感觉学到了东西，您可以点赞，关注。也欢迎有问题我们下面评论交流

加油！ 我们下期再见！

给大家分享几个我前面写的几篇骚操作

copy对象，这个操作有点骚！

干货！SpringBoot利用监听事件，实现异步操作

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%