MySQL优化再续:依靠子查询充分利用索引

前面一篇日志中,我记录了自己优化关联表的过程,当时提到一点: 把数据按取出的需要进行物理聚集

本篇我想强调的另一个的问题是:

任何一个查询,都必须充分利用索引。没有可利用的索引就建索引。

筛选条件太多,要考虑拆分成子查询来利用索引。

开始之前,我再次罗列几个表:

Album表: 专辑数据, 98万条记录,包含一个叫rate的字段

Tag表:    标签数据,20万条记录

Album_tags表:   关联表,300万条记录,保存了每个专辑都关联了哪些标签

我的需求是:

从Album表中取出和某个Tag关联的所有专辑,按照rate字段排序,再取rate最高的top 1000。

在上一篇日志里,我已经完成了取专辑ID的工作。

现在可以直接拿查询结果去和Album做inner join了。

但是Album表的大小超过1GB,这个inner join也不是可以随便写的。

 

1. 创建根据rate预排序的专辑表Tag_Albums

如果不预先排好序,而让MySQL每次都对得到的临时表做内存排序,将浪费不少时间。

我们首先来省掉这些时间,将Album中的数据按照rate降序导出到一个新的表:

create table Tag_Albums select rate,id,vote_count,album_name, pub_date from Album order by rate DESC;

这里我并不需要导出所有的列,需要哪些列就选择哪几列。

经过上面的精简,我得到的tag_albums表同样是98万条记录,却只有66MB。

2.  为Tag_Albums表增加主键和索引

建立这个表,是要让它预先按照rate排好序,所以选择主键是很关键的。

这里我选择复合的(rate, id)作为主键:

alter table tag_albums add primary key (`rate`,`id`)

如果错误地选择id作为主键,那我们导出的数据最后又是按照id排序和聚集了。

另外,我们还需要对id字段增加一个索引,至于何时用到,看了后文就知道了:

alter table tag_albums add key idx_id(id)

3. 预览最终的SQL

select T0.rate, T0.id, T0.album_name from tag_albums T0 inner join 
  (select T3.rate,T3.id from 
  (select album_id from album_tags T1 where tag_id = 8) T2, 
    tag_albums T3 where T3.id = T2.album_id order by T3.rate DESC,T3.id DESC limit 1000) T4 
on T0.rate = T4.rate and T0.id = T4.id
查询1

上面是我最终使用的版本,下面解释为什么需要这么写。

4. MySQL在一个查询中只能使用一个索引

MySQL在一个查询中,只能使用一个索引,明确这一点非常重要。

你可能建立了若干索引,但MySQL要么全都不用,要么,只能选择其中一个。

然而,如果分成子查询,则可以在不同的子查询中使用不同的索引。

5. 分析上述SQL语句

从最里层的子查询开始:

(select album_id from album_tags T1 where tag_id = 8) T2

这是取出与某个标签关联的所有专辑ID,在上一篇文章日志已经说明了,

因为利用了(tag_id, id)这个主键,它耗时不超过0.1秒。

我们将得到的结果作为临时表T2。

select T3.rate,T3.id from 
  (select album_id from album_tags T1 where tag_id = 8) T2, 
    tag_albums T3 where T3.id = T2.album_id order by T3.rate DESC,T3.id DESC limit 1000
查询2

实际上,这里出现了一个inner join,只是我没有直接写成inner join罢了。

为什么我要多此一举,先取出rate和id,却不直接把专辑数据就给取出来呢?

答案是,一次查询只能使用到一个索引。

貌似是有更好的写法哦?比如说这个:

select T3.rate,T3.id, T3.vote_count, T3.album_name, T3.pub_date from 
  (select album_id from album_tags T1 where tag_id = 5) T2, 
  tag_albums T3 where T3.id = T2.album_id order by T3.rate DESC,T3.id DESC limit 1000
查询3

但这个查询耗时将是我提供的查询1的10倍左右。

临时表T2得到的是一系列专辑ID,T2和T3进行inner join的时候,使用的是哪一个索引?

理想的情况,是不是应该用idx_id这个索引?就是我们刚才建的那个。(在上文我提到过后面会用到)

但查询3会用哪个索引呢? 因为where语句中使用了rate DESC和id DESC,MySQL可能会主动选择主键(rate,id)

这带来的问题是:

利用(rate,id)主键去找和album_id满足的专辑,无可避免就要扫整个表。

因为记录跟id没太大关系,是按rate排列的,只能从头扫到尾。

回过头来,我们再看查询2,它的重要价值就是,利用了索引idx_id去和临时表T2做inner join,从而避免了扫表。

那如果MySQL选择了idx_id索引呢? 后果是,它不可避免需要要对几千条记录进行rate DESC和id DESC的内存排序,最严重的问题是,它还不可以利用主键来排序。

 

查询2的排序是按照rate DESC, id DESC,也是非常重要的。

实际上,查询2利用了idx_id这个索引后,就不能再利用主键了(rate,id)排序了,这里,MySQL会对临时表做一个内存排序。

但是,在临时表中排序完毕再取top 1000,已经是比较好的一种选择。对比之下,肯定是还有别的写法的,比如:

select T0.rate, T0.id, T0.album_name from tag_albums T0 inner join 
  (select T3.rate,T3.id from 
    (select album_id from album_tags T1 where tag_id = 18) T2, 
     tag_albums T3 where T3.id = T2.album_id) T4 
on T0.rate = T4.rate and T0.id = T4.id order by T0.rate DESC, T0.id DESC limit 1000
查询4

查询4避免了内存排序取top 1000,在这方面的效率可能比查询1还高。但它依旧是要根据主键排序的

内存排序最多几万条,主键排序呢? 98万条

取了top 1000去inner join,还是先inner join再取1000,这个问题值得仔细考量。

查询4和查询1,各有优劣。我不敢断言查询1就要比查询4优秀很多,各有各的好处。

分析完上面的子查询,得到T4,再去跟T0做inner join,就没有什么好分析的了,直接上主键,这个是很迅速的。

 

这个分析过程可能比较麻烦,但经过优化,取出任何一个标签的top 1000专辑,都能够保证在1秒之内完成

耗时包含了:

1) 关联表album_tags查询,0.1秒以内

2) 利用idx_id索引进行的子查询,这也是一个inner join,此处涉及临时表在内存排序,最费时

3) 利用上述结果再次inner join,耗时很少,因为充分利用了tag_albums表已经排序的事实,以及主键(rate,id)

4) 取出1000条记录的时间

 

上述分析可能显得比较凌乱,我们可以得到的一点经验是:

如果经常需要获取按照某一列排序后的数据,那么可以对数据进行预排序,保存到一个新的表格。

一个MySQL查询只能利用一个索引,为了利用不同索引,则需要拆分成子查询。

 

发表评论

您的电子邮箱地址不会被公开。