如何在MySQL中通过连接提高性能的顺序

无言的风

我正在开发一个社交网络跟踪应用程序。即使连接正确，也可以正常工作。但是，当我添加order by子句时，总查询执行时间要长100倍。我用于获取不带order
by子句的twitter_users的以下查询。
SELECT DISTINCT  `tracked_twitter`.id
FROM tracked_twitter
INNER JOIN  `twitter_content` ON  `tracked_twitter`.`id` = `twitter_content`.`tracked_twitter_id`
INNER JOIN  `tracker_twitter_content` ON  `twitter_content`.`id` = `tracker_twitter_content`.`twitter_content_id`
AND  `tracker_twitter_content`.`tracker_id` =  '88'
LIMIT 20
显示第0-19行（共20行，查询耗时0.0714秒）
但是当我添加order by子句（在索引列上）时
SELECT DISTINCT  `tracked_twitter`.id
FROM tracked_twitter
INNER JOIN  `twitter_content` ON  `tracked_twitter`.`id` =  `twitter_content`.`tracked_twitter_id`
INNER JOIN  `tracker_twitter_content` ON  `twitter_content`.`id` =  `tracker_twitter_content`.`twitter_content_id`
AND  `tracker_twitter_content`.`tracker_id` =  '88'
ORDER BY tracked_twitter.followers_count DESC
LIMIT 20
显示第0-19行（共20行，查询耗时13.4636秒）
解释

当我仅在其表中实现order by子句时，不会花费太多时间
SELECT * FROM `tracked_twitter` WHERE 1 order by `followers_count` desc limit 20
显示第0-19行（共20行，查询耗时0.0711秒）[followers_count：68236387-10525612]
表创建查询如下
CREATE TABLE IF NOT EXISTS `tracked_twitter` (
`id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`handle` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`location` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`description` text COLLATE utf8_unicode_ci,
`profile_image` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`followers_count` int(11) NOT NULL,
`is_influencer` tinyint(1) NOT NULL DEFAULT '0',
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`gender` enum('Male','Female','Other') COLLATE utf8_unicode_ci
   DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `followers_count` (`followers_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
因此，当我在其表上执行它时，join并不会因工作良好而减慢查询和排序的速度。那么如何提高性能呢？
更新1
@GordonLinoff方法解决是否只需要父表的结果集。我想知道每人tweets的数量（与tracked_twitter表匹配的twitter_content的数量）。我该如何修改？如果我想在推文内容上使用数学函数，该怎么做？
SELECT  `tracked_twitter` . * , COUNT( * ) AS twitterContentCount, retweet_count + favourite_count + reply_count AS engagement
FROM  `tracked_twitter`
INNER JOIN  `twitter_content` ON  `tracked_twitter`.`id` =  `twitter_content`.`tracked_twitter_id`
INNER JOIN  `tracker_twitter_content` ON  `twitter_content`.`id` =  `tracker_twitter_content`.`twitter_content_id`
WHERE  `is_influencer` !=  '1'
AND  `tracker_twitter_content`.`tracker_id` =  '88'
AND  `tracked_twitter_id` !=  '0'
GROUP BY  `tracked_twitter`.`id`
ORDER BY twitterContentCount DESC
LIMIT 20
OFFSET 0

解决方案:


            尝试摆脱distinct。那是性能杀手。我不确定为什么您的第一个查询会很快工作；也许MySQL足够聪明，可以对其进行优化。
我会尝试：
SELECT tt.id
FROM tracked_twitter tt
WHERE EXISTS (SELECT 1
            FROM twitter_content tc INNER JOIN
               tracker_twitter_content ttc
               ON  tc.id =  ttc.twitter_content_id
            WHERE  ttc.tracker_id =  88 AND
                  tt.id =  tc.tracked_twitter_id
         )
ORDER BY tt.followers_count DESC ;
对于此版本，您想对指数： tracked_twitter(followers_count,
id)，twitter_content(tracked_twitter_id, id)，和
tracker_twitter_content(twitter_content_id, tracker_id)。

如何在MySQL中通过连接提高性能的顺序

本帖子中包含更多资源

无言的风 LV1