联合索引这点事儿
我们从头开始
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将会使联合索引失效
总结
- 多条件查询时,单列索引只能用到一个,此时应该选择联合索引
- 联合索引遵循最左前缀匹配原则,只有左侧先走了索引,之后的字段才有可能走索引。所以建立联合索引的时候,一定要注意顺序,字段使用越频繁越要靠左。这个顺序指的是创建索引时的顺序,至于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字段的地方需要修改成你运行脚本时插入的时间。
抱歉:建了那么多表,文中只用了一个,我原来以为都会用到了,捂脸。给自己开脱一下,万一以后文章用到了嘞。哈哈