#sql #ruby-on-rails #postgresql
#sql #ruby-on-rails #postgresql
Вопрос:
Я пытаюсь оптимизировать SQL-запрос. Вы можете мне помочь?
По сути, у каждого пользователя есть друзья через таблицу friendship, и у каждого пользователя есть много feed_events через таблицу user_feed_events. Я пытаюсь перечислить feed_events друзей данного пользователя. Это не должно быть невозможно, верно? 🙂
Как вы можете видеть, производительность запроса зависит от того, сколько друзей у пользователя. Прямо сейчас выполнение пользователя со 150 друзьями занимает почти 7 секунд.
ОБНОВЛЕНИЕ: вот как построена моя таблица дружбы:
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"
Сначала я прошу rails предоставить мне список идентификаторов userids друзей пользователя, затем я использую эту строку в реальном запросе.
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:
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>
ОБНОВЛЕНИЕ # 3: Проблема в том, что для моего приложения rails я использую плагин has_many_friends (https://github.com/swemoney/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"
Но на данный момент это не работает, и я также не уверен, что это правильный способ сделать это!
Спасибо, Аугусто
Комментарии:
1. Пожалуйста, отформатируйте свои SQL-инструкции так, чтобы их можно было читать без прокрутки.
2. Хорошо, я отформатировал его. Теперь должно быть лучше 🙂
3. Можете ли вы показать определения таблиц? Есть ли у вас какие-либо индексы?
4. Не могли бы вы показать, как выглядит таблица дружбы? Как сопоставляются друзья — friend_id и?
5. Я обновил свой вопрос подробностями определения таблицы дружбы.
Ответ №1:
Почему вы используете список 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
Это удаляет ненужное ЛЕВОЕ соединение.
Кроме того, пожалуйста, убедитесь, что вы создали индексы для столбцов, которые вы используете для внешних ключей.
Комментарии:
1. Спасибо, Дэниел, как я объясняю в обновленном вопросе, проблема заключается в том, как построена таблица дружбы. Чтобы найти всех моих друзей, мне нужно искать столбцы user_id и friend_id! Как я могу справиться с этим внутри объединения?
2. @Augusto: Вы можете использовать вторую инструкцию в качестве прямой замены вашей инструкции. Я обновлю первое утверждение, чтобы отразить ваше требование о таблице дружбы.
3. @Daniel, большое тебе спасибо. Сейчас я использую ваше отредактированное первое предложение, и оно кажется более элегантным. Тем не менее, обработка запроса занимает 2 секунды : ( Теперь я проверяю, правильно ли проиндексированы все внешние ключи.
4. @Augusto: Пожалуйста, попробуйте также второй запрос.
5. Я тоже пробовал это, это работает, но, похоже, для обработки требуется то же время, что и для первого : (
Ответ №2:
Хорошо, итак, запрос здесь не ваша проблема, ваша база данных должна быть настроена так, чтобы это не занимало больше нескольких микросекунд. Однако сначала, запрос. Это должно выглядеть так:
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). Кстати, я только что придумал эти названия, я пытался угадать, как вы называете стол, в котором храните дружеские отношения.
Ответ №3:
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
Не уверен, действительно ли distinct здесь нужен. Запрос возвращает события ленты для друзей указанного пользователя .. я надеюсь, что так и должно быть 😉
Редактировать. Оказывается, что решение практически такое же, как предложил Дэниел Хилгарт.
Ответ №4:
Используйте подраздел SELECT в WHERE предложении, чтобы создать список событий канала для 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 выглядит это.