C архитектурой/выборкой с таблицы?

Question

[t0os]

Всем привет!


Помогите, пожалуйста, решить ситуацию: есть некая таблица, назовем ее timeline, куда пишутся все события, которые происходят с пользователем. Например, кто-то его добавил в друзья, у его друзей что-то произошло и все такого рода. В таблице есть первичный ключ, автоинкремент — все классически. Есть поле date_created — дата создания записи.


Теперь о сложностях. Хочу сделать подгрузку сообщений при скроллинге. Например, вывел 10 сообщений, долистал до низа — подгружаются еще 10. Все логично и просто до тех пор, пока не происходят события, попадающие на одну и ту же дату с точностью до секунды. Т.е. выборку

select * from timeline where date_created<дата_последнего_сообщения_в_ленте LIMIT 10

уже нормально не сделать — пропадет часть сообщений. Сортировку по первичному ключу тоже не получится сделать — в будущем в таблицу timeline могут писаться события за прошлые периоды и ID, выступающий в роли order_id, потеряет свою актуальность.


По идее это можно было бы решить сквозным order_id для каждого пользователя, но не очень хочется заводить еще одно поле и при некоторых действиях пересчитывать все order_id.


Можно решить вопрос следующим образом — dba.stackexchange.com/questions/23981/mysql-select...

Но тут не нравятся двойная сортировка и переменные.


Может быть у кого-то был опыт подобного и можете предложить другое решение?

Comments

[t0os]

UPD:

Классические запросы вида

select * from timeline where ORDER BY date_created DESC LIMIT start, 10

нормально настроить не получилось потому, что во время пролистывания ленты могут добавиться новые записи в таблицу, и они дадут неправильное смещение при следующей выборке для текущей страницы.

Эту проблему можно решить, сделав ограничение сверху по первой выбранной записи в самый первый раз. Но как строить последующие запросы? Чтобы выбирать все отсортированные по date_created записи меньше начального ID?

Answer 1

[Назар Мокринский]

А почему не использовать?:

select * from timeline where ORDER BY date_created DESC LIMIT start, 10

Вместо start подставляете 10 * (порция№ — 1)

Comments

[Назар Мокринский]

При частых событиях может иметь смысл для фильтра отправлять id уже показанных событий, чтобы не повторять их, либо первый показанный id, отображая только id меньше его

[t0os]

Так было с самого начала, конечно же.

Была проблема с обновлением ленты — пока пролистывалась лента, добавлялись новые сообщения и в ленте получался разрыв. Но это можно решить, ограничивая верхний диапазон выборки.

Спасибо, натолкнули на еще одну мысль.

[t0os]

Вспомнил, где была проблема — рано радовался.

Смысл в том, что сверху ограничить просто и быстро у меня не получилось — можно либо по ID, либо по date_created. Но как ограничить по ID записи, отсортированные по date_created — я не очень представляю.

[Назар Мокринский]

А зачем по id?

select * from timeline where ORDER BY date_created DESC LIMIT start, 10

Не требует id, а чтобы убрать повторяющиеся записи можно отправлять id уже отображенных записей и фильтровать по ним (даже при сотне-двух id запрос на сервер не будет слишком большим).
И добавить условие:

WHERE date_created < время_открытия_страницы AND id NOT IN(показанные_id_через_запятую)

[t0os]

Эти отображенные записи надо где-то хранить и как-то их передавать. А таскать за собой хвост в post-запросе из кучи ID отображенных записей не очень хочется, потому что записей на странице могут быть тысячи.

[leutsky]

при малом количестве записей этот запрос прокатит, но для лучшей производительности лучше делать условие так, чтобы однозначно попадать на нужную позицию в таблице и затем выбирать всегда только первые 10 записей.

ps в MySQL:
LIMIT 0, 10 — выберет первые 10 записей — гуд
LIMIT 10, 10 — будет выбрано 20 записей и в прогу будет передано только последние 10
…
LIMIT 1000, 10 — будет выбрано 1010 записей и в прогу будет передано только последние 10

[Назар Мокринский]

Хранить можно в data-id атрибуте контейнера записи, оверхед минимальный.
А даже 1000 записей по 5 цифер — это 6000 символов, для такого массива данных это по-моему не критичный размер POST запроса, если пользователь уже загрузил 4000 записей)

[t0os]

Размер POST'а как такового не большой, согласен. Но трафик неслабый будет гоняться на бэкэнд, а это уже получается дорого. В идеале нужно решение, которое позволило бы по одному ID выбирать записи для следующего шага.

Answer 2

[leutsky]

делаете индекс по date_created, лучше его вообще в виде unix_timestamp хранить (а лучше создаете сразу составной индекс id & date_created)

когда вам нужно будет догрузить еще 10 записей, отправляете параметры (id и date_created) последней записи (т.е самой нижней в списке событий), а запрос будет вида:

SELECT * FROM timeline WHERE id > %s AND date_created <= %s ORDER BY date_created DESC, id ASC LIMIT 10

т.е запись с переданным id будет пропущена и будут выбраны еще 10 записей, которые вам нужны, и которые идут прям за записью с номером id

Недостаток в следующем:
— если во время просмотра пользователем страницы будет создана запись с date_created большим, чем передано в запросе, то эта запись будет показана только при перезагрузке страницы с событиями.

Comments

[t0os]

Спасибо, попробую. Это, скорее, не недостаток, а фича.)

[t0os]

Условие

WHERE id > %s 

бывает срабатывает не так, как надо. Например, на таких данных:

INSERT INTO `tbl_timeline` (`id`, `data`, `date_created`)
VALUES
	(397, NULL, '2013-07-24 11:21:32'),
	(382, NULL, '2013-07-23 21:10:57'),
	(428, 51, '2013-07-23 17:59:13'),
	(425, 49, '2013-07-23 17:52:15'),
	(422, 48, '2013-07-23 17:45:14'),
	(418, 46, '2013-07-23 17:38:14'),
	(432, 45, '2013-07-23 17:26:25'),
	(375, NULL, '2013-07-23 16:15:51'),
	(368, NULL, '2013-07-22 15:16:28'),
	(412, 32, '2013-07-22 14:51:22'),
	(415, 36, '2013-07-22 14:51:22'),
	(358, NULL, '2013-07-22 00:16:25'),
	(353, NULL, '2013-07-21 15:14:16'),
	(349, NULL, '2013-07-20 14:12:49'),
	(346, NULL, '2013-07-19 12:17:28');

Проблема получается в том, что нижележащие ID могут быть как больше текущего, так и меньше.

[leutsky]

да, понял в чем проблема. тут нужно уже подумать.

Можно попробовать решить следующем образом.
Создаем уникальный идентификатор для каждого события:
uid = unix_datetime << 32 + event_id
+ создаем к нему индекс в бд.
event_id — должен быть уникален, и тем самым он обеспечит уникальность этому идентификатору. А конструкция unix_datetime << 32 «размажет» события по временной шкале. 32 — это количество бит (=integer). Т.е получится своеобразный идентификатор, привязанный к времени, но при этом уникальный.
Если вы ожидаете, что количество событий перевалит за пределы uinteger, то нужно подумать над тем, как хранить uid. Можно для него использовать тип Decimal или varchar. А так можно обойтись типом bigint.

далее работаем с этим индексом, как с обычным числом. Т.е запрос будет следующим:

SELECT * FROM timeline WHERE uid < %s ORDER BY uid DESC LIMIT 10

Также почитайте это Разработка простого аналога Twitter'а с использованием Redis в качестве БД, т.к по сути то, что вы хотите сделать подобно твиттеру))

[leutsky]

с другой стороны, можно использовать и первоначальный запрос:

SELECT * FROM timeline WHERE id > %s AND date_created <= %s ORDER BY date_created DESC, id ASC LIMIT 10

но нужно обеспечить условие, чтобы каждое новое событие имело дату больше или равную дате предыдущего события и идентификатор, больший чем у предыдущего

[t0os]

Про uid интересно, спасибо. Но тогда может быть имеет смысл заводить order_id сквозной для каждого пользователя. Если когда-нибудь этот order_id понадобится пересчитать, то можно будет отсортировать по date_created все записи пользователя и выставить им order_id по порядку.

Answer 3

[rakot]

У вас PK это автоинкримент? Если да, то вот так

select * from timeline where id>id_последнего_сообщения_в_ленте LIMIT 10

Comments

[t0os]

Было и так, ничего хорошего не вышло. По причине:

Сортировку по первичному ключу тоже не получится сделать — в будущем в таблицу timeline могут писаться события за прошлые периоды и ID, выступающий в роли order_id, потеряет свою актуальность.

Answer 4

[Sayonji]

Передавайте назад только айди записей, которые имеют дату ту же, что дата_последнего_сообщения, раз вас смущают большие запросы.
А потом как выше предлагали WHERE date_created <= $last AND id not in $ids. Получать эти айди на клиенте можно, если жалко серверного времени.

Comments

[t0os]

Так тоже делали, остановились пока что на варианте сковозного order_id для каждого пользователя.