昨天写了篇日志，记录自己尝试去优化一个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是根据聚类索引来组织物理数据的，有必要的时候，可以建立复合主键，让某些记录彼此靠近。