Хранение и выборка булевых значений. Mysql

Question

[Антон]

Есть 4 переключателя (вкл\выкл) по ним будет проводится выборка, пример, 1й включен 2й выключен 3й не важно 4й включен.
Как лучше организовать хранение данных, в голову сразу пришло решение 4 поля TINYINT, но как понимаю есть более грамотные решения? например, хранения в одном поле четырех значений (1101), вопрос как выбирать, через like? как эффективнее?
Какое лучше выбрать решение, ткните носом пожалуйста где почитать?

Answer 1

[Dzuba]

Я бы попробовал следующее: хранить все 4 бита совместно в одном индексированном поле (назовем его bits), имеющем тип TINYINT UNSIGNED (либо BIT(4), либо SET).
А запросы бы делал в виде:

SELECT * FROM table WHERE bits IN (...)

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

SELECT * FROM table WHERE bits IN (b'1001', b'1011')

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

Comments

[Антон]

Только как я понимаю, если допустим мне нужно что одно значение вкл., а остальные не важны перебирать в ограничениях все варианты? Хотя с индексом это должно быть быстро.

[Dzuba]

Да, перебирать все 8 оставшихся вариантов. Это не много.
И я тоже думаю, что это должно быть очень быстро, поскольку поле с индексом. Проверил на табличке с 3.5 млн записей — работает почти мгновенно.
Кстати, поскольку иногда оптимизатор MySQL чудит, имеет смысл явно указать индекс в запросе (в данном случае в качестве имени индекса взято имя поля):

SELECT * FROM table FORCE INDEX(bits) WHERE bits IN (...)

[kolesnikov]

А если переключателей 30 штук?

[Dzuba]

Если 30, то такой подход не годится. Но по условию задачи их всего 4, и, цитирую:

99.9% что не будет добавляться новых параметров.

Однако, к счастью, даже в случае 30 битов есть выход, правда существенно менее скорострельный, поскольку требует полного сканирования таблицы:

SELECT * FROM table WHERE bits & mask = mask

Это почти полностью эквивалентно использованию типа SET.

[Антон]

Как я понимаю это решение как SET только по месту экономия в 4 раза, в этом решении будет занимать запись 1 байт, а в SET 4 байта.

Answer 2

[KEKSOV]

Уверен, что для описанной задачи подойдет тип данных SET

Comments

[KEKSOV]

Забыл ссылку на первоисточник

[Антон]

Спасибо, думаю практически самое оно, нужно попробовать. Вопрос только интересный по поводу индексов, так же будет производителен как на несколько полей индекс навесить…

Answer 3

[kutelev]

Если строк будет не много, то я бы, возможно, сделал бы так:

CREATE TABLE `test`.`switches` (
`id` INTEGER NOT NULL AUTO_INCREMENT,
`switches` CHAR(4) NOT NULL,
PRIMARY KEY (`id`)
);

Тогда ваш пример (1 — включен, 2 — выключен, 3 — не важно, 4 — включен) превращается в запрос:

SELECT * FROM switches WHERE switches LIKE '10_1';

Comments

[Антон]

Я изначально примерно так же думал, но вопрос в производительности, записей планируется больше миллиона.

Answer 4

[Assorium]

На Вашем месте использовал бы тип BOOLEAN (это синоним TINYINT но со значениями 0 или 1 только).
По ним можно выстроить индекс и поиск будет проводится гораздо быстрее, чем по строке с LIKE.
Также можно будет легко расширить систему, удалив или добавив новый переключатели.

Comments

[Антон]

Как я понимаю в базе что BOOLEAN что TINYINT занимают одинаковое пространство.
На счет индекса и правда интересно. Только подумал выборка по ним будет идти в основном как дополнительные параметры к sphinx поиску, а он строит свои индексы.
99.9% что не будет добавляться новых параметров.

Answer 5

[eresik]

Важна производительность? BOOLEAN
Важна экономия места? SET

Comments

[Антон]

Больше важна производительность, разница с занимаемом месте между двумя вариантами не существенная.

Answer 6

[Dzuba]

Вы путаете. Никакой экономии по месту нет. И в моем решении, и в SET, все хранится в 1 байте. Компактнее некуда. А вот решение с 4 полями типа BOOLEAN занимает 4 байта.
Я убежден, что приведенное мной решение является также и самым производительным из предложенных.
Преимущество по скорости в нем достигается за счет использования индекса конструкцией WHERE IN (...). Точно такой же результат должен быть, если использовать эту конструкцию и с полем типа SET. Я даже изначально написал, что решение приемлемо для типов, цитирую:

TINYINT UNSIGNED (либо BIT(4), либо SET)

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

Comments

[Dzuba]

Блин. Промахнулся. Извините.
Следует мысленно присовокупить этот комментарий к обсуждению выше.

[Антон]

Про типы давайте обсудим тут.
Если верить: dev.mysql.com/doc/refman/5.5/en/storage-requirements.html
BIT(M) approximately (M+7)/8 bytes
SET('value1','value2',...) 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)
Получается занимаемое место будет если BIT то 2 байта, т.к. все равно до байта будет округлять, а если SET то 4 элемента будут занимать 4 байта.

[Dzuba]

BIT(M) approximately (M+7)/8 bytes

При M < 9 это будет 1 байт, поскольку речь идет о целочисленном делении (которое всегда с округлением вниз). В случае 4 бит: (4+7)/8=11/8 = 1 байт.

SET('value1','value2',...) 1, 2, 3, 4, or 8 bytes, depending on the number of set members

Один элемент SET'а — это тоже 1 бит. Поэтому 64 элемента занимают 8 байт, 32 элемента — 4 байта, а 4 элемента — 1 байт. Подробнее: тут и тут.