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

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

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

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

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

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查询只能利用一个索引，为了利用不同索引，则需要拆分成子查询。