问题描述
我正在开发一个社交网络跟踪应用程序.即使连接正确，也可以正常工作.但是，当我添加order by子句时，总查询执行时间要长100倍.我曾经使用以下查询来获取不带order by子句的twitter_users.
I am working on a social network tracking application. Even joins works fine with proper indexing. But when I add the order by clause the total query takes 100 times longer time to execute. The following query I used to get the twitter_users without order by clause.
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秒)
Showing rows 0 - 19 (20 total, Query took 0.0714 sec)
但是当我添加order by子句(在索引列上)
But when I add order by clause ( on indexed column )
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秒)
Showing rows 0 - 19 (20 total, Query took 13.4636 sec)
说明
当我仅在其表中实现order by子句时，就不会花费很多时间
When I implement the order by clause in its table alone it doesn't take much time
SELECT * FROM `tracked_twitter` WHERE 1 order by `followers_count` desc limit 20
显示第0-19行(共20行，查询耗时0.0711秒)[followers_count:68236387-10525612]
Showing rows 0 - 19 (20 total, Query took 0.0711 sec) [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并不会因工作良好而减慢查询和排序的速度.那么如何提高性能呢?
So join didn't slow the query and order by working well when I execute it on its table. So how can I improve performance?
更新1
@GordonLinoff方法解决我是否只需要父表的结果集.我想知道每人tweets的数量(与tracked_twitter表匹配的twitter_content的数量).我该如何修改?而且，如果我想在推文内容上使用数学函数，该怎么做?
@GordonLinoff method solves if i only need the result set from parent table. What f I want to know the number tweets per person (count of twitter_content which match the tracked_twitter table). How can I modify it? And if I want to have math functions on tweet content how do I do it ??
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足够聪明，可以对其进行优化.
Try getting rid of the distinct. That is a performance killer. I'm not sure why your first query works quickly; perhaps MySQL is smart enough to optimize it away.
我会尝试:
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).
For this version, you want indexes on: tracked_twitter(followers_count, id), twitter_content(tracked_twitter_id, id), andtracker_twitter_content(twitter_content_id, tracker_id).
这篇关于如何在MySQL中通过连接提高性能的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！