Оптимизация производительности MySQL с помощью битовых масок (bitmask)

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

Проблема

Приведу следующий пример:

Table shirts
+----+----------+
| id | motive   |
+----+----------+
|  1 | Homer    |
|  2 | Marge    |
|  3 | Bart     |
+----+----------+

Table colors
+----+----------+
| id | color    |
+----+----------+
|  1 | Red      |
|  2 | Blue     |
|  4 | Green    |
|  8 | Black    |
+----+----------+

У нас есть 2 таблицы - футболки и цвета.

Table: shirts_x_colors
+----------+------------+
| shirt_id | color_id   |
+----------+------------+
|        1 |          1 |
|        1 |          4 |
|        2 |          2 |
|        2 |          8 |
|        3 |          2 |
+----------+------------+

В этой таблице хранится информация о том, какая футболка в каком цвете доступна. Например, футолка "Homer" доступна в красном и зелёном цвете. Для того, чтобы получить список всем футболок с дуступными цветами, необходимо задействовать 3 таблицы и, возможно, использовать функцию GROUP_CONCAT, для получения строки с цветами, разделёнными запятой.

SELECT shirts.motive, GROUP_CONCAT(colors.color)
FROM shirts
JOIN shirts_x_colors ON shirts.id = shirts_x_colors.shirt_id
JOIN colors ON shirts_x_colors.color_id = colors.id
GROUP BY shirts.id

Если у футболок, помимо цветом, будут дополнительные параметры, например размеры, материалы, то и без того тяжёлый запрос будет ещё больше.

Использование bitmask

Битовые маски (далее bitmask) помогут оптимизировать такие запросы. Как вы уже наверно заметили, поле id в таблице colors не просто autoincrement integer. Это числа в степени 2 (1, 2, 4, 8, 16, 32 ....). Изменим структуру таблицы shirts следующим образом:

Table shirts
+----+----------+--------+
| id | motive   | colors |
+----+----------+--------+
|  1 | Homer    |      5 |
|  2 | Marge    |     10 |
|  3 | Bart     |      2 |
+----+----------+--------+

Теперь добавлена новая колонки colors, которая содержит сумму всех идентификаторов цветов для футболки. Например, для футболки "Homer" дустапны цвета 1 и 4, что в сумме даёт 5. Особенность bitmask в том, что сумма всегда уникальна. Т.е. цифра 5 означает только красный и зелёный и никакие другие.

Вот пример кода, преобразующий сумму наших идентификаторов обратно в одиночные значения:

function reverseBitmask($bitmask)
{
    $bin = decbin($bitmask);
    $total = strlen($bin);
    $stock = [];
    for ($i = 0; $i < $total; $i++) {
        if ($bin{$i} != 0) {
            $bin_2 = str_pad($bin{$i}, $total - $i, 0);
            array_push($stock, bindec($bin_2));
        }
    }
    return $stock;
}

print_r(reverseBitmask(5));

Вывод:

Array
(
    [0] => 4
    [1] => 1
)

Что, если нам нужно получить все футболки в красном или зелёном цвете? Мы можем сделать это с помощью одного простого запроса. Сумма красных и зеленых идентификаторов будет равна 5, поэтому наш запрос будет выглядеть так:

SELECT * FROM shirts WHERE colors & 5 > 0;

Но будьте осторожны: указанный выше запрос не может использовать индекс MySQL. С точки зрения производительности иногда бывает лучше использовать простой запрос IN (x, y, z).

Вывод

Использование bitmask может помочь улучшить производительность ваших SQL-запросов. Конечно, это специфичекий метод и вы должны понимать, будет ли он полезен в вашем случае или наоборот, только навредит. Я бы не советол использовать bitmask, если список атрибутов (например цветов в нашем случаем) может быть большим. Кроме того, вам необходимо четко указать, что вы сохраните избыточные данные в своей базе данных, если вы измените структуру своих таблиц в соответствии с приведенным выше примером.

Использование bitmask не является "волшебной пулей" для решения проблем с производительностью базы данных, но в некоторых случаях это может помочь. Я надеюсь, что теперь у вас есть базовое представление о том, как работают bitmask, и, возможно, они пригодятся в вашем следующем проекте.

Ссылки по теме: