База данных: передовой опыт - старые данные?

У меня есть база данных автомобильных объявлений.

По истечении 90 дней классифицированный листинг больше не может быть отображен (листинг истекает); однако я хочу сохранить список для архивных целей.

Вопрос: с точки зрения оптимальной практики проектирования баз данных, а также с точки зрения производительности запросов, что лучше сохранить старый листинг A) в той же таблице, что и текущий листинг, или B), переместить листинг с истекшим сроком действия в таблицу с истекшим сроком действия и удалить этот листинг из текущей таблицы листинга?

Другими словами,

Вариант А):

table_classified_listing:
car_id
expired = true | false
...

Вариант Б):

// only current listing in this table (expired = false)
table_classified_listing:
car_id
...

// only expired listing in this table (expired = true)
expired_table_classified_listing:
car_id
...

ОБНОВЛЕНИЕ:

Меня беспокоит вариант A в моей базе данных MySQL - когда я запускаю EXPLAIN, он говорит, что использует expired в качестве первичного ключа для индексации. Однако для производительности моего поиска по запросу более важным является использование поля price, поскольку я выполняю поиск на основе price > X. Поэтому я подумываю о выборе варианта Б.


database database-design rdbms
person Timk    schedule 22.12.2009    source источник
comment
Тимк, здесь мы используем A, но я предлагаю не делать is_enabled, если это поле действительно не имеет значения, вместо этого записывать time_entered в БД и либо вычислять истекший интервал в коде, либо использовать представление. Я вижу случай использования, когда люди / хотят / просматривать секретные списки старше 30 дней.   -  person Evan Carroll    schedule 23.12.2009

Ответы (9)


arrow_upward
6
arrow_downward

Вариант A) таким образом у вас будут все данные в одном месте, и вам будет проще создавать запросы для отчетов, перечислять исторические записи пользователей и т. Д. Любые проблемы со скоростью должны быть смягчены индексом базы данных в этом столбце. Вариант B) - это преждевременная оптимизация.

person Matthew Lock    schedule 22.12.2009

arrow_upward
4
arrow_downward

Общий совет (вы должны заполнить пробелы ;-)):

  • Производительность будет значительной только в некоторых случаях (более миллиона записей, огромный размер строки ...).

  • Будете ли вы запрашивать две таблицы с помощью "объединений" или идентичных запросов? Если вы не будете запрашивать таблицы, используя одни и те же запросы, я предлагаю разные таблицы (возможный прирост производительности по мере увеличения количества записей, но в основном прирост значения).

Проблема дублирования в том, что это может увеличить объем работы (написание запросов, их тестирование ...). Но все технологии (особенно современные) позволяют уменьшить или отменить дублирование.

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

ОБНОВЛЕНИЕ после обновления в вопросе:

Вы можете создавать нужные вам индексы, не волнуйтесь. Если производительность, которую вы ищете, для запроса данных, срок действия которых не истек, по цене выше X, создайте индекс (с истекшим сроком, цена), и все в порядке :-)

person KLE    schedule 22.12.2009
comment
Я не планирую и не предвижу необходимости запрашивать обе таблицы (к сведению) - person Timk; 22.12.2009
comment
Итак, вы говорите - выберите вариант А. Верно? - person Timk; 22.12.2009
comment
@Timk Итак, это ясно. У вас есть какой-нибудь размер строки или рекордное количество цифр? - person KLE; 22.12.2009
comment
@Timk На самом деле я еще не выбирал между вариантами :-). Но я обновил свой ответ после вашего обновления ... - person KLE; 22.12.2009

arrow_upward
3
arrow_downward

Не используйте B, это просто разделение атрибутов.

Я бы предпочел вместо этого использовать два столбца даты. ValidFromDate и ValidToDate.

person no_one    schedule 22.12.2009

arrow_upward
2
arrow_downward

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

Не делайте ничего сложного, пока не будете уверены, что оно вам нужно, а простое не сработает. Храните в одной таблице. См. Вопрос о пессимизациях - это один.

person dkretz    schedule 22.12.2009
comment
+1 за то, что вы заметили, что производительность оборудования и программного обеспечения часто увеличивается быстрее, чем вы можете заполнить машину данными - person Matthew Lock; 22.01.2010

arrow_upward
1
arrow_downward

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

Из-за всех старых записей размер таблицы будет постоянно увеличиваться, а это означает более медленные запросы, даже с учетом оптимизации запросов и тому подобного.

РЕДАКТИРОВАТЬ: как кто-то еще упомянул, один большой недостаток этого подхода заключается в том, что вы планируете часто комбинировать живые данные и архивные данные. Если вы всегда будете ссылаться на них по отдельности, то это прекрасно, но если нет, у вас будет много объединений и объединений, необходимых для объединения данных, что не идеально.

person Gavin H    schedule 22.12.2009
comment
как происходит потеря производительности при индексировании столбца с просроченными / действительными записями? Разве это не должно быть быстро? - person kender; 22.12.2009

arrow_upward
1
arrow_downward

Есть как минимум два дополнительных варианта решения общей проблемы хранения старых данных:

  • Разделите данные по дате и либо откатите дату, либо отсоедините раздел. В качестве альтернативы можно реализовать каждую секцию как отдельную таблицу, а затем объединить их с помощью представления объединения всего. В последнем случае обычно лучше использовать разбиение с грубой степенью детализации (месяц вместо дня). MySQL должен поддерживать оба этих решения, а секционирование имеет дополнительное преимущество, заключающееся в повышении производительности запросов, связанных с запросами больших частей данных вашей таблицы.
  • Экспортируйте все данные, которые хотите сохранить, обрежьте таблицу и перезагрузите ее. Серьезно - перезагрузка может быть намного быстрее, чем удаление, когда вы удаляете много данных. Во многих базах данных не хватает данных для этого - по крайней мере, в течение нескольких лет, а затем их администраторы обнаруживают, что им нужно либо обновить оборудование, либо очистить данные за весь год. На этом этапе эта стратегия часто оказывается лучшей.

Вернемся к вашим двум предлагаемым решениям:

  • Храните данные в одной таблице. С вашими объемами данных это, вероятно, лучший подход. Но - в какой-то момент вы все еще можете захотеть отказаться от него (7 лет?), И в этот момент у вас может быть небольшая асинхронная работа, которая выполняет тонкое удаление, может сбрасывать разделы или может экспортировать / перезагружать.
  • Храните архивные данные в другой таблице. Это становится наиболее полезным, если вы можете использовать другое (меньшее) оборудование для менее часто используемых архивных данных, например отдельный сервер, меньшее количество процессоров, другой набор более дешевых / медленных дисков, меньший буфер памяти и т. Д. MySQL не имеет достаточных возможностей настройки, чтобы сделать что-то из этого. Другая причина заключается в том, что в ваших запросах часто выполняется сканирование таблиц, и если, перемещая большую часть данных, вы можете значительно повысить производительность. И это могло быть так. Вы используете MySQL, который имеет заведомо незрелый оптимизатор / планировщик, и вы не используете секционирование. Итак, всякий раз, когда индекс не может быть использован, вы собираетесь сканировать таблицу. Если вам нужны молниеносные запросы, небольшой сервер или много строк - я бы сохранил старые данные в отдельной таблице. Но вот, возможно, лучший способ сделать это:
  • храните данные в двух таблицах, но первая таблица содержит 100% ваших данных (новые и старые), а вторая таблица содержит только самые новые данные. Причина этого подхода в том, что может быть множество подмножеств или агрегатов, которые вы, возможно, захотите сгенерировать - и теперь таблица с новейшими данными является лишь одной из многих. Эти подмножества / агрегаты не совсем необходимы - вы всегда можете просто запросить свою основную таблицу. Однако аналитические запросы, как правило, довольно сильно загружают базу данных - и эти таблицы могут сделать их очень быстрыми. И, честно говоря, любой процесс, на который стоит потратить время, стоит проанализировать.
person KenFar    schedule 22.12.2009

arrow_upward
1
arrow_downward

Вот что я понимаю:

  • Поскольку это засекреченные списки, данные носят "недолговечный" характер, и срок их действия истекает.
  • В результате объем данных с истекшим сроком может превышать «текущие» или данные с истекшим сроком действия.

Если я правильно понял сказанное выше, следующий вопрос: как часто используются ваши просроченные данные? и для чего это используется? Как отметил @ghills, sql-unions может вас замедлить.

Если просроченные данные не обязательно должны быть в сети, возможно, имеет смысл заархивировать их в отдельной таблице. Особенно, если количество просроченных строк может превышать количество активных строк.

Если вы сохраните их в той же таблице, «where expired = false» может оказаться вашим постоянным спутником, а поскольку избирательность будет низкой (то есть много просроченных строк), индексация столбца «с истекшим сроком» не принесет вам многого. взрыва за доллар. (У Oracle есть индексы битовых карт, но это может вообще не применяться).

person blispr    schedule 22.12.2009

arrow_upward
1
arrow_downward

Я бы держал их в одной таблице. В противном случае: (а) у вас есть две таблицы с одинаковыми столбцами. ТОГДА каждый раз, когда вы вносите изменения в данные, вы должны не забывать изменять обе таблицы одинаково. Рано или поздно кто-то забудет - или поймет, что данные в одной таблице не нужны в другой - и теперь ваш дизайн усложняется. Довольно скоро вы напишете одну и ту же логику дважды: один раз для извлечения из «текущей» таблицы и еще раз для извлечения из «архивной» таблицы. Но затем кто-то вносит изменения в один фрагмент кода и забывает внести такие же изменения в другой фрагмент кода. Тогда следующий человек не может быть уверен, что они другие, потому что есть веская причина, почему они должны быть другими, или кто-то просто забыл. И т. Д. (Б) Похоже, что у вас будут запросы, которые захотят попасть в обе таблицы, например «Скажите мне все объявления с запрашиваемой ценой более 20 000 долларов США за последние 12 месяцев», где одни из объявлений могут быть текущими, а другие в архиве. Эти запросы теперь являются объединениями или сложными объединениями вместо того, чтобы просто не включать флаг «истек - истина» или «истек - ложь».

Что касается проблемы с производительностью, это просто: создайте многопольный ключ, который включает все, что вам нужно включить. просроченный + цена или просроченный + название модели кажутся вероятными ключами. Вы, вероятно, захотите сначала поставить просроченный, потому что большинству ваших запросов, вероятно, будут нужны записи с истекшим сроком, но я просто предполагаю. Выбор того, что стоит индексировать, - сложное решение, но когда есть очевидные общие запросы по нескольким полям, просто сделайте это.

person Jay    schedule 22.12.2009

arrow_upward
0
arrow_downward

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

person S M Kamran    schedule 22.12.2009