联合索引这点事儿

微信公众号:Vegout

如有问题或建议,请公众号留言

我们从头开始

ER图

建表

按照ER图,建立数据库和表,并且进行测试数据的填充。(建表sql和填充脚本的文件可公众号(Vegout)回复关键字“联合索引”获取)

sql优化——建索引

1、查询所有标题以title666开头的文章

结果

用了0.688秒,下面我们给title字段加上索引

再次执行select语句(记得清除缓存reset query cache),瞬间查出

但如果like条件这样写

因为我们测试数据article中的所有title都是以“title”开头的,所以这样写,查出的数据是一样的,但是

又使用了0.656秒,通过explain我们可以看到

这次查询并没有走索引。所以建立索引的字段在like条件下,如果以“%”开头,索引会失效。

2、查询title以“title666”开头并且summary以“summary666”开头的所有文章

我们执行

耗时

我们给title和summary建立一个联合索引

再次执行select语句,耗时

但此时如果我们只查询summary like"summary666"的记录,将不会走索引

对与联合索引title_summary_index来说,遵循最左前缀匹配原则,只有先走了title的索引,才会再走summaruy的索引。这里只有summary是查询条件,将不会走索引。也就是对与联合索引(a,b),查询a会走索引,查询a,b也会走索引,但查询b不会走索引。还有就是,对与联合索引中的字段出现位置,比如查询a,b,还是b,a,并没有严格的要求,也就是说我们这样写,也会走索引

为了进一步说明最左匹配原则,我们再建一个索引(并删除上一个联合索引)

这个索引加入了发表时间,并放在了首位,也就是说只有publish_time 先走了索引,后边的两个字段才有机会走索引。

在这个sql中,publish_time走不了索引,于是联合索引失效,只能进行全表扫描。这里使之失效的查询条件是publish_time>'2018-10-20 21:42:20',并不是说使用“>”就会失效,mysql中使用了“!=”,“”,“not in”,“not exist”会使索引失效,但对于“>”,“=”,“

很愉快的走了索引

由此你也可以推断,库中pulish_time小于2018-10-20 21:42:20的一定比少,不错,只有七百多,大于它的有不到一百万条。在一个博客上曾看见这么一段话形如联合索引:联合索引就像是一路关斩将,对与联合索引(a,b,c),a就是第一关,b是第二关,c是第三关,关得一个一个按顺序来过(大致这么个意思)。感觉形如的还真是恰当。

如果当时我们在没有删除第一个联合索引的情况下,执行上边没有走索引的select语句,就是这个

我们将会发现,它会走我们创建的第一个联合索引。也就是publish_time使得不能够走第二个联合索引,但publish_time之后的条件可以走第一个联合索引。

当然,我们也可以在title,summary上分别建立单列索引,但当多条件查询时,只能有一个索引生效。我们给这两个字段分别建立索引,然后执行这个sql

我们发现

只走了一个索引。但单列索引也有他的好处,比如如果是条件是or的关系,两个索引就都可以走

而如果我们使用是刚才的联合索引,or将会使联合索引失效

总结

多条件查询时,单列索引只能用到一个,此时应该选择联合索引

联合索引遵循最左前缀匹配原则,只有左侧先走了索引,之后的字段才有可能走索引。所以建立联合索引的时候,一定要注意顺序,字段使用越频繁越要靠左。这个顺序指的是创建索引时的顺序,至于sql查询语句中的顺序没有要求,因为mysql会对这个顺序进行优化调整以满足索引的要求。“%…”,“!=”,“”,“not in”,“not exist”,“or”,“is null”,“is not null”使索引失效,“>”,“=”,“有可能使索引失效。

联合索引的本质:当建立了(a,b,c)联合索引时,相当于创建了(a)单列索引,(a,b)联合索引,(a,b,c)联合索引。

什么情况下应该建立索引:选择性高的字段,经常where查询的字段,稳定的字段

什么情况下不要建立索引:频繁变化的字段,选择性低的字段

注:插入测试数据的脚本大约会运行10分钟,插入300多万条数据。文中测试sql涉及到publish_time字段的地方需要修改成你运行脚本时插入的时间。

抱歉:建了那么多表,文中只用了一个,我原来以为都会用到了,捂脸。给自己开脱一下,万一以后文章用到了嘞。哈哈

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181023G1V96P00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

同媒体快讯

扫码关注云+社区

领取腾讯云代金券