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

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

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

(后续)记录一次MySQL关联表的优化

昨天写了篇日志,记录自己尝试去优化一个MySQL表。

最后因为对结果不满意,用了一种非常“高富帅”的方法解决问题,即把整个表都放到内存中提升查询性能。

现在回忆起来,性能问题确实是因为自己滥用MySQL造成的。

 

首先,再描述一下场景。

需要优化的是一个关联表,名为 album_tags ,300万条记录,只有3个字段:

id

album_id    外键关联到专辑的id, integer

tag_id           外键关联到标签的id, integer

应用中需要获取和某个tag关联的所有专辑,平均情形有几千个。

通过inner join两个表来取数据: album_tags 和album。

当时发现这个查询比较费时,有时多达四五秒。

排查从子查询开始,测试了类似语句:

select album_id from album_tags where tag_id = 8

当结果集数量较多时,这条语句会变慢,比如取出2万条的结果,可能就需要两三秒。

我尝试explain这个select查询,发现是有使用索引的。

实际上,最初为了提升查询性能,我还特别在tag_id这一列上建了个hash索引。

 

1. HASH(tag_id)分区优化

了解到索引没问题,我产生了第一个思路:分表, 继续阅读(后续)记录一次MySQL关联表的优化