Нужен совет по организации таблицы (MySQL, MyISAM)
В таблицу добавляется примерно 50 тысяч записей в день (сейчас около 10 миллионов записей). Каждая запись содержит 15 INT полей и одно VARCHAR(32) поле.
Встала задача добавить новые поля, содержащие составной атрибут. Есть 7 типов атрибутов, каждый может принимать значение от 1 до 65535 (2 байта). Но каждая запись может содержать одновременно не более 3 (а точнее или 0, или 1, или 3).
Только около 5% записей будет иметь атрибут. Большая часть запросов будет выбирать записи по принципу наличия (хоть какого-то) или полного отсутствия атрибутов. Разбор атрибутов будет происходить за пределами базы (только вывод информации по атрибутам записей, отсутствие поиска записей по конкретному атрибуту).
Какие варианты приходят в голову:
1. В лоб. 7 полей UNSIGNED SMALLINT (по полю на каждый тип атрибута). Большой объем, сложность выборки (AT_1 > 0 OR AT_2 > 0 OR AT_3 > 0 ...), но простота использования.
2. Небольшая экономия места. 3 поля TINYINT и 3 поля UNSIGNED SMALLINT (пары id атрибута и значение). Почти такой же объем, немного более простая выборка (AT_VAL_1 > 0 OR AT_VAL_2 > 0 OR AT_VAL_3 > 0), но необходимость присвоения каждому атрибуту определенного ID, плюс более сложный разбор данных после выборки.
3. Больше экономии, больше путаницы. 3 поля INT (первый байт — тип, остальные байты — значение). Сложно найти какие-либо плюсы по сравнению с предыдущим вариантом (только если меньшее число полей).
4. Проще некуда. Одно поле BLOB на 14 байт. Только одно поле, максимально простая выборка (ATTR IS NULL), отсутствие дополнительной путаницы (каждый атрибут имеет свое постоянное смещение в поле, не надо разбирать id).
Забыл добавить, что периодически происходит обновление выборочных записей (добавленных за последние сутки), а обновить проще одно BLOB-поле, чем 7 отдельных полей. Хотя очевидно, что длина текста SQL-запроса не является критичным параметром.
Во первых имхо лучше использовать char вместо varchar, чтобы ROW_FORMAT таблицы стала fixed, это убыстрит выборку и запись. На счет ваших вариантов я думаю нужно использовать первый.
Вам шашечки или ехать? Денормализация таблиц БД (и вообще структур данных) вполне нормальный этап оптимизации. А уже если говорить о нормализации, то у вас в любом варианте избыточность выходит. Согласно канонам свои 7 атрибутов вы должны положить или в 7 связанных таблиц (аналогично вашему первому варианту), или в одну связанную таблицу с парой полей — ид атрибута и его значение (аналогично остальным).
Кстати, если у вас и с остальными атрибутами дело похоже обстоит и собственно букву Р в РСУБД не используете (грубо говоря в БД только одна таблица), то посмотрите в сторону NoSQL решений (MongoDB или CouchDB в частности), профит должен быть ощутимый
Вы же сами пишете что последний вариант самый простой, и практически не имеет недостатков — его и используйте. Кстати ставить индексы скорее всего нет смысла — в вашей ситуации они будут только замедлять выполнение запроса.
Еще вариант: одно поле — совокупный тип атрибутов (например, битовая маска), и 3 SMALLINT. Экономия места, простая выборка (ATTR_TYPES = 0), но более сложный разбор атрибутов. Хотя способ с BLOB все-таки решает!
Простой
Средний
