mysql: используйте SET или много столбцов?

Похоже, вы в основном озабочены работой.

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

С индексами вполне нормализованный подход, подобный этому (который добавляет два JOIN), будет по-прежнему иметь «очень хорошую» производительность чтения. Одна из проблем заключается в том, что INSERT или UPDATE для события теперь могут также включать один или несколько INSERT / UPDATE / DELETE в таблицу перекрестных ссылок, которая на MyISAM означает, что таблица перекрестных ссылок заблокирована, а на InnoDB означает, что строки заблокированы, поэтому, если ваша база данных занята значительным количеством записей, у вас будут проблемы с большими конфликтами, чем если бы были заблокированы строки событий.

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

SET против "большого количества столбцов"

Итак, что касается вашего фактического вопроса «SET против большого количества столбцов», я могу сказать, что я работал с двумя компаниями с умными инженерами (продуктами которых были веб-приложения CRM … на самом деле это было управление событиями), и они оба использовал подход «много столбцов» для такого типа статических данных набора.

Моим советом было бы подумать обо всех запросах, которые вы будете делать на этой таблице (взвешенных по их частоте) и о том, как будут работать индексы.

Во-первых, при подходе «много столбцов» вам понадобятся индексы в каждом из этих столбцов, чтобы вы могли делать SELECT FROM events WHERE CategoryX = TRUE . С индексами это супер-быстрый запрос.

В отличие от SET, для выполнения этого запроса вы должны использовать побитовое И (&), LIKE или FIND_IN_SET (). Это означает, что запрос не может использовать индекс и должен выполнять линейный поиск всех строк (вы можете использовать EXPLAIN для проверки этого). Медленный запрос!

Это основная причина, по которой SET – плохая идея – ее индекс полезен, если вы выбираете точные группы категорий. SET отлично работает, если вы выбираете категории по событию, но не наоборот.

Основная проблема с менее нормированным подходом «много столбцов» (по сравнению с полностью нормализованным) заключается в том, что он не масштабируется. Если у вас 5 категорий, и они никогда не меняются, хорошо, но если у вас есть 500 и меняют их, это большая проблема. В вашем сценарии, где около 30 никогда не меняются, основная проблема заключается в том, что в каждом столбце есть индекс, поэтому, если вы делаете частые записи, эти запросы становятся медленнее из-за количества индексов, которые необходимо обновить. Если вы выберете этот подход, вы можете проверить журнал медленных запросов MySQL, чтобы убедиться, что из-за разногласий в трудные времена суток нет слишком медленных запросов.

В вашем случае, если у вас типичное веб-приложение для чтения, я думаю, что использование подхода «много столбцов» (как это делали два продукта CRM по той же причине), вероятно, является разумным. Это определенно быстрее, чем SET для этого запроса SELECT.

TL; DR Не используйте SET, потому что запрос «select events by category» будет медленным.

Связь между событиями и типами событий / категориями – это много-много отношений, как говорится в echo , но простая таблица xref оставит вас с проблемой: если вы хотите запросить всех потомков любого данного узла, вы должны сделать несколько рекурсивных запросов. На глубоком дереве это будет очень неэффективно.

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

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Модель Nested Set делает записи немного медленнее, но очень легко извлекает поддеревья:

• Чтобы получить подэлемент Televisions , вы запрашиваете для всех категорий left >= 2 и right <= 9 .
• У узлов листа всегда есть left = right - 1
• Вы можете найти количество потомков, не вытягивая эти строки: (right - left - 1)/2
• Поиск путей наследования и глубины также очень прост (одно вещество запроса). Подробности см. В статье.

Вы можете попробовать использовать таблицу перекрестных ссылок (Xref), чтобы создать взаимосвязь между вашими событиями и их типами во многих отношениях.

 create table event_category_event_xref ( event_id int, event_category_id int, foreign key(event_id) references event(id), foreign key (event_category_id) references event_category(id) ); 

Членство в событии / категории определяется записями в этой таблице. Поэтому, если у вас есть запись с {event_id = 3, event_category_id = 52} , это означает, что событие №3 находится в категории № 52. Аналогично, вы можете иметь записи для {event_id = 3, event_category_id = 27} и т. Д.

Хорошо, что количество категорий фиксировано. Если бы это было не так, вы не могли использовать ни один подход.

Проверьте, почему вы не должны использовать SET на странице, с которой вы связаны. Я думаю, это должно дать вам исчерпывающий справочник.

Я думаю, что самый важный из них – об индексах. Кроме того, изменение SET немного сложнее.