(后续)记录一次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)分区优化

了解到索引没问题,我产生了第一个思路:分表,

把这个300万记录的表,根据tag_id分成几个不同的表。

因为MySQL自身就支持分区,我直接根据 hash(tag_id)把表分成了10个区。

这个基本的思路是正确的,经过分区之后,所有的查询都可以在1秒之内完成了。

但是,结果依旧不够理想。

 

2. 为什么查询这么慢

当时看到MySQL查询利用了索引,就以为没什么问题了。

但问题正出在这里,为什么用了索引,查询还那么慢?

答案是我的数据物理分布太零散了。

MySQL可以利用索引快速定位数据,但从磁盘上取出大量数据,依旧花费了过多的时间。

来看看我的表当时是怎么安排的吧,3个字段的顺序依次是:

id    album_id    tag_id

这就意味着,记录主要是按照album_id来聚集的,而和某个tag_id对应的记录,可能在磁盘上从头到尾都有分布。

如果我们能把数据按照tag_id来进行物理组织,就可以提升查询效率了。

知道了这一点,就好办了:

1. 去掉两个外键约束

2. 去掉id上的主键

3. alter table把tag_id安排到最前,变成:  tag_id,  id,  album_id

4. 为该表建立复合的主键:  primary key(tag_id, id)

经过上面的操作,我们就可以不需要任何索引,让查询在0.1秒内完成。

比之前分区的还快几倍。

 

经历这次优化,我得到的一个教训就是:

当记录数较多时, 列的顺序十分重要的。 尽量把where条件的列放前面。

MySQL是根据聚类索引来组织物理数据的,有必要的时候,可以建立复合主键,让某些记录彼此靠近。

发表评论

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