联合索引这点事儿

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_33240946/article/details/83308629

我们从头开始

ER图

在这里插入图片描述

建表

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

sql优化——建索引

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

select * from article where title like "title666%"

结果

已找到记录: 1,111  警告: 0  持续时间 1 查询: 0.688 sec

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

alter table article add key titleindex(title);

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

已找到记录: 1,111  警告: 0  持续时间 1 查询: 0.000 sec.

但如果like条件这样写

select * from article where title like "%itle666%"

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

已找到记录: 1,111  警告: 0  持续时间 1 查询: 0.656 sec.

又使用了0.656秒,通过explain我们可以看到
在这里插入图片描述
这次查询并没有走索引。所以建立索引的字段在like条件下,如果以“%”开头,索引会失效。

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

select * from article where title like "title666%" 
and content like "content666%"

耗时

已找到记录: 1,111  警告: 0  持续时间 1 查询: 0.594 sec.

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

alter table article add key title_summary_index(title,summary)

再次执行select语句,耗时

已找到记录: 1,111  警告: 0  持续时间 1 查询: 0.031 sec

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

select * from article where summary like "summary666%"
已找到记录: 1,111  警告: 0  持续时间 1 查询: 0.672 sec.

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

select * from article where summary like "summary666%" 
and title like "title666%" 

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

alter table article add key 
time_title_summary_index(publish_time,title,summary)

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

select * from article where summary like "summary666%" 
and title like "title666%" and publish_time>'2018-10-20 21:42:20'

在这个sql中,publish_time走不了索引,于是联合索引失效,只能进行全表扫描。这里使之失效的查询条件是publish_time>‘2018-10-20 21:42:20’,并不是说使用“>”就会失效,mysql中使用了“!=”,“<>”,“not in”,“not exist”会使索引失效,但对于“>”,“<”,“>=”,“<=”的使用,优化器会根据查询的数据情况来决定走不走索引(走索引快就走索引,索引慢就全表扫描),比如这里将大于号改成小于号

select * from article where summary like "summary666%" 
and title like "title666%" and publish_time<'2018-10-20 21:42:20'

很愉快的走了索引
在这里插入图片描述
由此你也可以推断,库中pulish_time小于2018-10-20 21:42:20的一定比少,不错,只有七百多,大于它的有不到一百万条。在一个博客上曾看见这么一段话形如联合索引:联合索引就像是一路关斩将,对与联合索引(a,b,c),a就是第一关,b是第二关,c是第三关,关得一个一个按顺序来过(大致这么个意思)。感觉形如的还真是恰当。
如果当时我们在没有删除第一个联合索引的情况下,执行上边没有走索引的select语句,就是这个

select * from article where summary like "summary666%" 
and title like "title666%" and publish_time>'2018-10-20 21:42:20'

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

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

 explain select * from article where summary like 
 "summary666%" and title like "title666%"

我们发现
在这里插入图片描述
只走了一个索引。但单列索引也有他的好处,比如如果是条件是or的关系,两个索引就都可以走

 explain select * from article where summary like 
 "summary666%" or title like "title666%"

在这里插入图片描述
而如果我们使用是刚才的联合索引,or将会使联合索引失效
在这里插入图片描述

总结

  1. 多条件查询时,单列索引只能用到一个,此时应该选择联合索引
  2. 联合索引遵循最左前缀匹配原则,只有左侧先走了索引,之后的字段才有可能走索引。所以建立联合索引的时候,一定要注意顺序,字段使用越频繁越要靠左。这个顺序指的是创建索引时的顺序,至于sql查询语句中的顺序没有要求,因为mysql会对这个顺序进行优化调整以满足索引的要求。“%…”,“!=”,“<>”,“not in”,“not exist”,“or”,“is null”,“is not null”使索引失效,“>”,“<”,“>=”,“<=”有可能使索引失效。
  3. 联合索引的本质:当建立了(a,b,c)联合索引时,相当于创建了(a)单列索引,(a,b)联合索引,(a,b,c)联合索引。
  4. 什么情况下应该建立索引:选择性高的字段,经常where查询的字段,稳定的字段
  5. 什么情况下不要建立索引:频繁变化的字段,选择性低的字段

注:插入测试数据的脚本大约会运行10分钟,插入300多万条数据。文中测试sql涉及到publish_time字段的地方需要修改成你运行脚本时插入的时间。
抱歉:建了那么多表,文中只用了一个,我原来以为都会用到了,捂脸。给自己开脱一下,万一以后文章用到了嘞。哈哈

展开阅读全文

没有更多推荐了,返回首页