Каков наиболее эффективный способ хранения порядка сортировки по группе записей в базе данных?

Предположим, PHP/MYSQL, но мне не обязательно нужен реальный код, меня интересует только теория.

Хорошим вариантом использования будет страница фотогалереи Facebook. Вы можете перетащить фотографию на страницу, которая запускает событие Ajax, чтобы сохранить новый порядок сортировки. Я реализую нечто очень похожее.

Например, у меня есть таблица базы данных "фотографии" с примерно миллионом записей:

фотографии id: int, userid: int, albumid: int, sortorder: int, filename: varchar, название: varchar

Скажем, у меня есть альбом со 100 фотографиями. Я перетаскиваю фото в новое место, и событие Ajax срабатывает для сохранения на сервере.

Должен ли я передавать весь массив идентификаторов фотографий на сервер и обновлять каждую запись? Предположим, что проверка на ввод "WHERE userid= loggedin_id", поэтому злонамеренные пользователи могут испортить только порядок сортировки своих собственных фотографий

Должен ли я передавать идентификатор фотографии, свой предыдущий индекс порядка сортировки и новый индекс сортировки, извлекать все записи между этими двумя индексами, сортировать их, а затем обновлять их заказы?

Что произойдет, если в одной галерее будут тысячи фотографий и порядок сортировки будет изменен?

Ответ 1

Как насчет использования столбца integer, который определяет порядок? По умолчанию вы назначаете номера * 1000, например 1000, 2000, 3000.... и если вы перемещаете 3000 между 1000 и 2000, вы меняете его на 1500. Таким образом, в большинстве случаев вам не нужно обновлять другие номера вообще. Я использую этот подход, и он работает хорошо. Вы также можете использовать double, но тогда у вас нет контроля над ошибками точности и округления, поэтому скорее не используйте его.

Таким образом, алгоритм будет выглядеть как: скажем, вы переместите B в позицию после A. Сначала выполните выбор, чтобы увидеть порядок записи рядом с A. Если он не менее +2 выше, чем порядке A, тогда вы просто установите порядок B, чтобы соответствовать между ними. Но если он только +1 выше (нет пробела после A), вы выбираете граничные записи B, чтобы узнать, сколько места на этой стороне, разделите на 2, а затем добавьте это значение в порядок всех записей между A и Б. Это!

(Обратите внимание, что вы должны использовать транзакцию/блокировку для любого алгоритма, который содержит более одного запроса, поэтому это относится и к этому случаю. Самый простой способ - использовать транзакцию InnoDB.)

Ответ 2

Сохранить как связанный список, порядок сортировки - это ссылка внешнего ключа на следующую команду photo_id в наборе.

Ответ 3

это, вероятно, будет конструкцией "связанного списка".

Ответ 4

Для меня второй способ обновления - это путь (обновляйте только диапазон, который изменяется). Вы упоминаете "Что происходит, если в одной галерее тысячи фотографий", и для меня это никогда не произойдет. Давайте рассмотрим ваш пример в facebook. Facebook не показывает тысячи фотографий на одной странице, они разбивают ее примерно на 10-20 на страницу.

Ответ 5

То, как я делал это в нереляционной базе данных, - это сохранить список идентификаторов фотографий в сущности/записи альбома "альбом" в желаемом порядке. Переупорядочение фотографий приводит к переупорядочению списка, и записывается только одна база данных.

Некоторые базы данных SQL (например, PostgreSQL) имеют собственные типы данных списка, но MySQL этого не делает. Вы можете сериализовать список как строку или двоичный код в MySQL.

Обученные гуру базы данных 3-й нормальной формы будут кричать на вас, что это ужасный подход, но RDBMSes оптимизированы для запросов типа OLAP, где гибкость запросов важнее производительности чтения. Webapps лучше всего писать с учетом стратегии "писать тяжелый, читаемый свет", и такая денормализация точно соответствует этому.