复杂的SQL查询优化
问题描述：
我想优化一个SQL查询。你可以帮我吗？复杂的SQL查询优化
基本上每个用户都有朋友通过友情表，每个用户有很多feed_event通过user_feed_events表。 我想列出给定用户的朋友的feed_events。不应该是不可能的，对吧？ :)
正如您所见，查询的性能取决于用户拥有多少个朋友。现在，拥有150位朋友的用户需要将近7秒才能执行。
UPDATE：这里是我的朋友表是如何构建的：
create_table "friendships", :force => true do |t|
t.integer "user_id", :null => false
t.integer "friend_id", :null => false
t.datetime "created_at"
t.datetime "accepted_at"
end
add_index "friendships", ["friend_id"], :name => "index_friendships_on_friend_id"
add_index "friendships", ["user_id"], :name => "index_friendships_on_user_id"
首先我问导轨给我的用户的朋友的用户ID的ID列表，然后我用这真正的查询字符串。
friends_id = current_user.friends.collect {|f| f.id}.join(",")
sql = "
SELECT
DISTINCT feed_events.id,
feed_events.event_type,
feed_events.type_id,
feed_events.data,
feed_events.created_at,
feed_events.updated_at,
user_feed_events.user_id
FROM feed_events
LEFT JOIN user_feed_events
ON feed_events.id = user_feed_events.feed_event_id
WHERE user_feed_events.user_id IN (#{friends_id})
ORDER BY feed_events.created_at DESC"
然后我实际上可以执行查询（拼版它并限制到30个结果）：
@events = FeedEvent.paginate_by_sql(sql, :page => params[:page], :per_page => 30)
更新＃2：这里是EXPLAIN ANALYZE OUTPUT：
SQL> EXPLAIN ANALYZE (SELECT DISTINCT feed_events.id, feed_events.event_type, feed_events.type_id, feed_events.data, feed_events.created_at, feed_events.updated_at, user_feed_events.user_id FROM user_feed_events INNER JOIN feed_events ON feed_events.id = user_feed_events.feed_event_id WHERE user_feed_events.user_id IN (1,7,9,8,14,15,20,35,40,39,41,42,57,84,98,109,121,74,129,64,137,77,172,182,206,201,284,31,94,232,311,168,30,114,50,174,419,403,438,464,423,513,351,349,385,622,751,359,809,838,844,962,831,786,896,1001,992,998,990,256,67,623,957,1226,1060,1009,1490,132,1467,1672,619,1459,1466,993,1599,1365,607,1381,1714,1154,2032,2230,2240,2354,598,2345,1804,634,1900,2652,1975,2164,1759,3288,1004,3487,3507,3542,3566,514,3787,3137,3803,3090,4012,855,17,2026,1463,335,1000,935,5,12,10,13,19,18,16,22,34,27,29,59,126,90,46,23,63,291,134,229,107,439,521) ORDER BY feed_events.created_at DESC)
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Unique (cost=6090.87..6162.93 rows=18014 width=389) (actual time=1641.210..1733.010 rows=29691 loops=1) |
| -> Sort (cost=6090.87..6099.88 rows=18014 width=389) (actual time=1641.206..1670.882 rows=29694 loops=1) |
| Sort Key: feed_events.created_at, feed_events.id, feed_events.event_type, feed_events.type_id, feed_events.data, feed_events.updated_at, user_feed_events.user_id |
| Sort Method: quicksort Memory: 17755kB |
| -> Hash Join (cost=3931.63..5836.21 rows=18014 width=389) (actual time=258.541..361.345 rows=29694 loops=1) |
| Hash Cond: (user_feed_events.feed_event_id = feed_events.id) |
| -> Bitmap Heap Scan on user_feed_events (cost=926.64..2745.66 rows=18014 width=8) (actual time=6.930..42.367 rows=29694 loops=1) |
| Recheck Cond: (user_id = ANY ('{1,7,9,8,14,15,20,35,40,39,41,42,57,84,98,109,121,74,129,64,137,77,172,182,206,201,284,31,94,232,311,168,30,114,50,174,419,403,438,464,423,513,351,349,385,622,751,359,809,838,844,962,831,786,896,1001,992,998,990,256,67,623,957,1226,1060,1009,1490,132,1467,1672,619,1459,1466,993,1599,1365,607,1381,1714,1154,2032,2230,2240,2354,598,2345,1804,634,1900,2652,1975,2164,1759,3288,1004,3487,3507,3542,3566,514,3787,3137,3803,3090,4012,855,17,2026,1463,335,1000,935,5,12,10,13,19,18,16,22,34,27,29,59,126,90,46,23,63,291,134,229,107,439,521}'::integer[])) |
| -> Bitmap Index Scan on index_user_feed_events_on_user_id (cost=0.00..925.74 rows=18014 width=0) (actual time=6.836..6.836 rows=29694 loops=1) |
| Index Cond: (user_id = ANY ('{1,7,9,8,14,15,20,35,40,39,41,42,57,84,98,109,121,74,129,64,137,77,172,182,206,201,284,31,94,232,311,168,30,114,50,174,419,403,438,464,423,513,351,349,385,622,751,359,809,838,844,962,831,786,896,1001,992,998,990,256,67,623,957,1226,1060,1009,1490,132,1467,1672,619,1459,1466,993,1599,1365,607,1381,1714,1154,2032,2230,2240,2354,598,2345,1804,634,1900,2652,1975,2164,1759,3288,1004,3487,3507,3542,3566,514,3787,3137,3803,3090,4012,855,17,2026,1463,335,1000,935,5,12,10,13,19,18,16,22,34,27,29,59,126,90,46,23,63,291,134,229,107,439,521}'::integer[])) |
| -> Hash (cost=2848.84..2848.84 rows=44614 width=385) (actual time=251.490..251.490 rows=44663 loops=1) |
| -> Seq Scan on feed_events (cost=0.00..2848.84 rows=44614 width=385) (actual time=0.035..77.044 rows=44663 loops=1) |
| Total runtime: 1780.200 ms |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQL>
UPDATE＃3：问题是，我的rails应用程序使用了has_many_friends插件（https://github.com/s wemoney/has_many_friends），这是照顾我的友谊。它是这样工作的。我是user_id＃6，并且正在向user_id＃10寻求友谊。当用户＃10接受我的友谊时，将新行添加到user_id = 6和friend_id = 10的表中。如果用户＃10问我友谊，行是：user_id = 10和friend_id = 6.
这意味着，为了找到friends_by_me，我需要搜索“user_id = 6”，以便找到friends_for_me我需要“friend_id = 6”。为了找到我所有的朋友，我需要搜索这两列。这使得创建联接非常复杂！ 你会如何处理？
我能想到的唯一的选择是：
"(SELECT
DISTINCT feed_events.id,
feed_events.event_type,
feed_events.type_id,
feed_events.data,
feed_events.created_at,
feed_events.updated_at,
user_feed_events.user_id
FROM feed_events
INNER JOIN user_feed_events
ON feed_events.id = user_feed_events.feed_event_id
INNER JOIN friendships
ON user_feed_events.user_id = friendships.user_id
WHERE friendships.user_id = 6
AND friendships.accepted_at IS NOT NULL)
UNION DISTINCT
(SELECT
DISTINCT additional_feed_events.id,
additional_feed_events.event_type,
additional_feed_events.type_id,
additional_feed_events.data,
additional_feed_events.created_at,
additional_feed_events.updated_at,
user_feed_events.user_id
FROM feed_events AS additional_feed_events
INNER JOIN user_feed_events
ON additional_feed_events.id = user_feed_events.feed_event_id
INNER JOIN friendships
ON user_feed_events.user_id = friendships.friend_id
WHERE friendships.friend_id = 6
AND friendships.accepted_at IS NOT NULL)
ORDER BY feed_events.created_at DESC"
但目前没有工作，我也不能肯定是做正确的方式！
感谢， 奥古斯托
答
你为什么使用IN列表？你为什么不从所选用户开始？另外，我觉得联接，不需要你的左外：
SELECT
DISTINCT feed_events.id,
feed_events.event_type,
feed_events.type_id,
feed_events.data,
feed_events.created_at,
feed_events.updated_at,
user_feed_events.user_id
FROM
(
select friend_id from friendship where user_id = YOURUSER
UNION
select user_id as friend_id from friendship where friend_id = YOURUSER
) friendship
inner join user_feed_events
on friendship.friend_id = user_feed_events.user_id
inner join feed_events
on user_feed_events.feed_event_id = feed_events.id
ORDER BY feed_events.created_at DESC
如果您希望继续使用原来的说法，只是优化它，然后使用此：
SELECT
DISTINCT feed_events.id,
feed_events.event_type,
feed_events.type_id,
feed_events.data,
feed_events.created_at,
feed_events.updated_at,
user_feed_events.user_id
FROM user_feed_events
INNER JOIN feed_events
ON feed_events.id = user_feed_events.feed_event_id
WHERE user_feed_events.user_id IN (#{friends_id})
ORDER BY feed_events.created_at DESC
这消除了不必要的LEFT JOIN 。
此外，请确保您在用于外键的列上创建索引。
+0
谢谢Daniel，正如我在更新的问题上解释的那样，问题在于如何建立友情表。为了找到我所有的朋友，我需要查找user_id和friend_id列！我如何在连接内部处理这个问题？ – Augusto 2011-05-26 14:18:39
+0
@Augusto：您可以使用第二个陈述作为您的陈述的直接替代。我会更新第一个陈述以反映您对友谊表的要求。 – 2011-05-26 14:25:10
+0
@Daniel，非常感谢。我现在正在使用您编辑的第一个提案，它似乎更优雅。尽管如此，查询需要2秒的时间来处理:(现在我检查一下，以确保我的所有外键正确索引。 – Augusto 2011-05-26 14:40:31
答
好的，所以这里的查询不是你的问题，你的数据库必须设置好，这样不会超过几微秒。首先，查询。它应该是这样的：
SELECT feed_events.id,
feed_events.event_type,
feed_events.type_id,
feed_events.data,
feed_events.created_at,
feed_events.updated_at,
user_feed_events.user_id
FROM feed_events
INNER JOIN
user_feed_events ON feed_events.id = user_feed_events.feed_event_id
INNER JOIN
user_friends ON user_friends.friend_id = user_feed_events.user_id
WHERE user_friends.user_id = ** The Id of the User in Question **
ORDER BY feed_events.created_at DESC
接下来，你需要确保你的ID列是主键上有东西像user_friends表（friend_id，USER_ID）唯一索引。顺便说一句，我只是做了这些名字，我试图猜测你是在叫你存储的友谊表。
答
select distinct fe.id, fe.event_type,
fe.type_id, fe.data, fe.created_at,
fe.updated_at, ufe.user_id
from friendships as f
inner join user_feed_events as ufe on f.friend_id = ufe.user_id
inner join feed_events as fe on ufe.user_id = fe.id
where f.user_id = 6 and f.accepted_at is not null
order by fe.created_at desc
不知道是否明显确实需要在这里。查询返回指定用户的朋友的馈送事件..它应该我希望;）
编辑。 发生这种问题的方法与Daniel Hilgarth提出的方法非常相似。
答
用户WHERE子句中的子SELECT为IN()呼叫建立馈送事件的列表。东西（未经测试）是这样的：
SELECT fe.id,
fe.event_type,
fe.type_id,
fe.data,
fe.created_at,
fe.updated_at,
ufe.user_id
FROM feed_events AS fe, user_feed_events AS ufe
WHERE TRUE = TRUE
AND fe.id = ufe.feed_event_id
AND ufe.user_id = :user_id
AND fe.id IN((
SELECT ufe.feed_event_id
FROM user_feed_events AS ufe, user_friends AS uf
WHERE uf.friend_id = :user_id
))
ORDER BY feed_events.created_at DESC;
我很好奇，想看看EXPLAIN ANALYZE样子来源于此。
请格式化您的SQL语句，以便在不滚动的情况下读取它们。 – 2011-05-26 13:19:57
好的，我现在要做:) – Augusto 2011-05-26 13:20:49
好的，我格式化了它。现在应该会更好:) – Augusto 2011-05-26 13:26:10