在23m行非常慢的表上的MySQL查询
我正在处理一个PHP Web服务,它需要在一个23万条记录的表上执行查询。我创建的查询似乎需要30多秒才能完成,并且从我可以告诉它是导致问题的查询部分的顺序,因为如果没有它,查询会快速响应。在23m行非常慢的表上的MySQL查询
这是查询;
SELECT artist_feeds.*, artists.name, artists.picture AS profile_picture
FROM artist_feeds
INNER JOIN user_artists ON user_artists.artist_id = artist_feeds.artist_id
INNER JOIN artists ON artists.id = artist_feeds.artist_id
WHERE artist_feeds.feed_date >= '2015-10-01'
AND user_artists.user_id = 486
AND NOT EXISTS (
SELECT id FROM user_artist_disabled_networks AS uadn
WHERE uadn.user_id = 486
AND uadn.artist_id = artist_feeds.artist_id
AND uadn.socialnetwork_id = artist_feeds.socialnetwork_id
LIMIT 1
)
ORDER BY artist_feeds.feed_date DESC
LIMIT 0, 20
该查询的解释如下所示;
任何人都可以提供任何指针?
根据要求,SHOW CREATE TABLE输出;
CREATE TABLE `artist_feeds` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`feed_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`feed_date` datetime DEFAULT NULL,
`message` text COLLATE utf8mb4_unicode_ci,
`hash` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`type` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`source` mediumtext COLLATE utf8mb4_unicode_ci,
`picture` mediumtext COLLATE utf8mb4_unicode_ci,
`link` mediumtext COLLATE utf8mb4_unicode_ci,
`artist_id` int(11) DEFAULT '0',
`socialnetwork_id` int(11) DEFAULT '0',
`direct_link` mediumtext COLLATE utf8mb4_unicode_ci,
`is_master_feed` tinyint(4) DEFAULT '0',
`active` tinyint(4) DEFAULT '0',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`rss_feed_id` int(11) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `artist_id` (`artist_id`),
KEY `socialnetwork_id` (`socialnetwork_id`),
KEY `feedidnetwork` (`feed_id`(191),`socialnetwork_id`),
KEY `feeddatenetworkid` (`feed_date`,`socialnetwork_id`),
KEY `feeddatenetworkidartistid` (`artist_id`,`socialnetwork_id`,`feed_date`),
KEY `type` (`type`),
KEY `feed_date` (`feed_date`)
) ENGINE=InnoDB AUTO_INCREMENT=26991713 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
解决:感谢比尔指针,我研究能够改变的表中的表的访问顺序,使artist_feed表是第一台访问,这反过来将消除需要一个文件上的数据,这导致了速度的增加。
我结束了使用STRAIGHT_JOIN而不是INNER JOIN。我的工作查询是;
SELECT af.*, a.name, a.picture AS profile_picture
FROM artist_feeds AS af
STRAIGHT_JOIN user_artists AS ua ON ua.artist_id = af.artist_id
STRAIGHT_JOIN artists AS a ON a.id = af.artist_id
LEFT OUTER JOIN user_artist_disabled_networks AS uadn
ON uadn.user_id = ua.user_id AND uadn.socialnetwork_id = af.socialnetwork_id
WHERE af.feed_date >= '2015-10-01'
AND uadn.user_id IS NULL
AND ua.user_id = 498
ORDER BY af.feed_date DESC
LIMIT 0, 20
说明现在看起来像这样;
我会写有排斥联接的查询,而不是一个NOT EXISTS子查询:
SELECT af.*, a.name, a.picture AS profile_picture
FROM artist_feeds AS af
INNER JOIN user_artists AS ua ON ua.artist_id = af.artist_id
INNER JOIN artists AS a ON a.id = af.artist_id
LEFT OUTER JOIN user_artist_disabled_networks AS uadn
ON uadn.user_id = ua.user_id AND uadn.socialnetwork_id = af.socialnetwork_id
WHERE af.feed_date >= '2015-10-01'
AND ua.user_id = 486
AND uadn.user_id IS NULL
ORDER BY af.feed_date DESC
LIMIT 0, 20
根据该解释的访问表的顺序是:
-
ua
lookup by user_id -
a
通过PRIMARY KEY查找 -
af
查找由artist_id和范围条件由feed_date -
uadn
查找由USER_ID和socialnetwork_id
所以,你应该有指标:
- user_artists(USER_ID,artist_id)
- 艺术家需求只有它的主键
- artist_feeds(artist_id,feed_date) 个
- user_artist_disabled_networks(USER_ID,socialnetwork_id)
查询性能问题的很大一部分无疑是TEMP表,文件排序。这是不可避免的,因为您的查询不会首先访问artist_feeds表。
重新在你的问题你的更新:
这是不覆盖的表访问优化的顺序是一个好主意。你可以看到,迫使它首先读取af
表,现在它必须检查该表中的1119万个条目。至少它可以避免手动对结果进行排序 - 它可以依赖af
表的自然顺序。但我不确定在这种情况下这是一个很好的折衷。
谢谢,这提供了一些指针。我会尝试重写查询来修改表访问的顺序。 – SheppardDigital
请注意,表访问的顺序是优化器的决定 - 它与您在查询中引用表的顺序无关。例外情况是当您使用'STRAIGHT_JOIN'覆盖优化器时。 –
你可以发布SHOW INDEX FROM artist_feeds,以便我们可以看到细节,基数等? –
我不是这方面的专家,但是如果您希望花费不到一秒,请添加3秒的超时时间并重试代码,然后开始调查。 –
我不希望查询花费少于一秒的时间。这是查询似乎需要多长时间才能完成的时间,但有时查询会挂起,然后导致查询队列落后。 – SheppardDigital
请求查询优化帮助时,请在查询中为每个表发布“SHOW CREATE TABLE”。它有助于我们理解您定义的数据类型,索引和约束条件,这是制定优化策略所必需的。 –