MySQL连接(join)速度慢，有什么方法能加快吗？

Question

我有两个表，一个是音乐表，另一个是听歌记录表。听歌记录表跟踪每首歌曲的唯一播放次数。我正在尝试获取本月热门歌曲的结果。虽然我得到了结果，但是查询时间太长了。下面是我的表格和查询：

共有430,000行数据。

CREATE TABLE `listentrack` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `sessionId` varchar(50) NOT NULL,
    `url` varchar(50) NOT NULL,
    `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `ip` varchar(150) NOT NULL,
    `user_id` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=731306 DEFAULT CHARSET=utf8

12500行

CREATE TABLE `music` (
   `music_id` int(11) NOT NULL AUTO_INCREMENT,
   `user_id` int(11) NOT NULL,
   `title` varchar(50) DEFAULT NULL,
   `artist` varchar(50) DEFAULT NULL,
   `description` varchar(255) DEFAULT NULL,
   `genre` int(4) DEFAULT NULL,
   `file` varchar(255) NOT NULL,
   `url` varchar(50) NOT NULL,
   `allow_download` int(2) NOT NULL DEFAULT '1',
   `plays` bigint(20) NOT NULL,
   `downloads` bigint(20) NOT NULL,
   `faved` bigint(20) NOT NULL,
   `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`music_id`)
) ENGINE=MyISAM AUTO_INCREMENT=15146 DEFAULT CHARSET=utf8


SELECT COUNT(listenTrack.url) AS total, listenTrack.url 
FROM listenTrack
LEFT JOIN music ON music.url = listenTrack.url
WHERE DATEDIFF(DATE(date_created),'2009-08-15') = 0
GROUP BY listenTrack.url
ORDER BY total DESC
LIMIT 0,10

这个查询并不是很复杂，行数也不是太多。

有没有什么方法可以加快速度？或者您能提出更好的解决方案吗？这将成为每个月初的定时任务，但我也希望能够每天得到结果。

顺便说一句，我正在本地运行此查询，需要超过4分钟才能完成，但在生产环境中只需约45秒。

- Khary

Answer 1

我更擅长于SQL Server，但这些概念适用于其他数据库。

我建议添加一些索引：

1. 在ListenTrack表中，添加一个包含url和date_created的索引。
2. 在Music表中，添加一个包含url的索引。

这些索引应该会极大地提高查询速度（我最初混淆了表名 - 在最新的编辑中进行了修正）。

- Jeff Siver

---

我相信索引表的名称被颠倒了。第一次我也做到了，但在发布之前及时发现了它。 - TheJacobTaylor

TheJacobTaylor 是正确的，我把表名搞反了。我已经修复了这个问题。 - Jeff Siver

我有一个问题，为什么我们两个都搞错了？我再次查看了帖子，但没有发现我跟错的触发器。 - TheJacobTaylor

我的猜测是：在问题介绍中，它们被介绍为音乐，然后是listentrack，但是创建查询首先显示listentrack。 - Rob Drimmie

Answer 2

大多数情况下，您还应该索引在JOIN中使用的任何列。在您的情况下，您应该同时索引listentrack.url和music.url。

@jeff s-索引music.date_created无济于事，因为您首先要通过函数运行它，所以MySQL无法在该列上使用索引。通常，您可以重写查询，使索引引用的列静态使用，例如：

DATEDIFF(DATE(date_created),'2009-08-15') = 0

变成

date_created >= '2009-08-15' and date_created < '2009-08-15'

这将筛选出2009-08-15之后的记录，并允许该列上的任何索引成为候选项。请注意，MySQL可能不会使用该索引，这取决于其他因素。

您最好在listentrack（url，date_created）上创建双重索引，然后再在music.url上创建另一个索引。

这两个索引将涵盖此特定查询。

请注意，如果在此查询上运行EXPLAIN，则仍将获得using filesort，因为它必须将记录写入临时磁盘表以执行ORDER BY。

通常，您应始终在EXPLAIN下运行查询，以了解MySQL将如何执行查询，然后从那里开始。请参阅EXPLAIN文档：

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

- Cody Caughlan

---

时间真是太巧了。 :) 回答非常好。比我快了几秒钟。 - TheJacobTaylor

非常感谢，听起来非常有用。我会跟随您的链接，阅读并尝试您的示例。 - Khary

Answer 3

尝试创建一个可帮助连接的索引：

CREATE INDEX idx_url ON music (url);

- VoteyDisciple

Answer 4

使用MySQL/MyISAM进行预分组，然后再加入可以大大提高速度。（我怀疑在其他数据库中不需要这么做）

这样做的性能应该与未连接版本相当快：

SELECT
   total, a.url, title
FROM
(
  SELECT COUNT(*) as total, url
  from listenTrack
  WHERE DATEDIFF(DATE(date_created),'2009-08-15') = 0
  GROUP BY url
  ORDER BY total DESC
  LIMIT 0,10
) as a
LEFT JOIN music ON music.url = a.url
;

附言 - 使用ID而不是URL将两个表格进行映射是一个明智的建议。

- user645280

Answer 5

我之前可能忽略了一个显而易见的问题。你为什么要加入音乐表呢？看起来你根本没有使用那个表中的数据，而且你还进行了一个不必要的左连接。我认为这个查询中包含这个表会使它变得更慢，并且不会增加任何价值。除非需要包含url，否则请将所有与音乐相关的内容删除，在这种情况下，你需要进行右连接以强制它不包括没有匹配值的行。

---

像其他人提到的那样，我会添加新索引。具体来说，我会添加： 音乐url listentrack date_created,url

这将大大改善你的连接。

然后我会查看查询，因为你正在迫使系统对每一行执行工作。最好将日期限制重新表述为范围。

我不确定语法是什么： where '2009-08-15 00:00:00' <= date_created < 2009-08-16 00:00:00

这应该可以快速使用索引定位适当的记录。基于日期和URL的音乐组合双键索引应该能够找到记录。你应该进行实验，它们可能更适合在索引上按相反的顺序排列url、date_created。

此查询的解释计划应该在右侧列上说“使用索引”。这意味着它不必访问表中的数据来计算你的总和。

我还会检查你为MySQL配置的内存设置。听起来你没有分配足够的内存。非常小心服务器设置和线程设置之间的差异。一个具有10MB缓存的线程可以很快使用大量内存。

- TheJacobTaylor

---

我正在使用该表中的数据，但我在调试哪里出现了缓慢。未进行连接的查询需1秒。添加音乐URL索引后进行连接，查询需要7秒；再加上音乐播放次数索引，查询时间增长到10秒。 - Khary

你能发布“真实”查询或带索引的查询计划吗？一天有多少条记录（数量级）？你在盒子上使用了多少内存？索引和数据缓存，还是仅在InnoDB上缓存，这些都是关键且易于修复的。 - TheJacobTaylor

Answer 6

为什么在两个表格中都重复URL？

让listentrack持有music_id，并在其上进行连接。这会消除文本搜索以及额外索引。

此外，这更正确。您正在跟踪播放特定曲目的时间，而不是URL。如果URL更改会怎样呢？

- kyoryu

Answer 7

以下内容可能有助于加速查询。

CREATE INDEX music_url_index ON music (url) USING BTREE; CREATE INDEX listenTrack_url_index ON listenTrack (url) USING BTREE;

您真的需要知道正在发生的比较和行扫描的总数。要获取该答案，请查看此处的代码，了解如何使用explain http://www.siteconsortium.com/h/p1.php?id=mysql002。

- JTHouseCat

Answer 8

在添加索引之后，您可能希望探索添加一个新列来将date_created转换为unix_timestamp，这将使数学运算更快。

我不确定您为什么需要diff函数，因为它似乎是要查找特定日期更新的所有行。

您可能需要检查查询，因为它似乎存在错误。

如果您使用单元测试，则可以比较查询结果和使用unix时间戳的查询结果。

- James Black

Answer 9

你可能想要在两个表的url字段上添加索引。

话虽如此，当我从mysql转换到sql server 2008时，使用相同的查询和相同的数据库结构，查询运行速度提高了1-3个数量级。

我认为其中一部分原因是rdbms（mysql优化器不太好...），另一部分原因可能与rdbms如何保留系统资源有关。尽管如此，这些比较是在只运行数据库的生产系统上进行的。

- mson