MySQL Datenbank Performance verbessern mit Bitmasks

Wenn es um die Optimierung von (MySQL-)Datenbanken geht, ist es üblich, die Datenbankstruktur zu normalisieren. Im Allgemeinen ist das eine gute Idee, aber wenn es um die reine Performance geht, ist eine komplett normalisierte Datenbank nicht immer die beste Lösung.

Zur besseren Verständlichkeit werde ich in diesem Artikel die folgenden Beispieldaten bzw. Tabellen verwenden:

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

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

Wir haben eine Tabelle mit T-Shirts und eine weitere Tabelle mit den Farben. Jedes Shirt kann in verschiedenen Farben erhältlich sein, aber nicht jedes Shirt ist in jeder Farbe erhältlich. Daher müssen wir eine neue Tabelle erstellen, um zu speichern, welches Shirt in welcher Farbe erhältlich ist. Diese würde wahrscheinlich so aussehen:

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

Diese Tabelle gibt uns nun Auskunft darüber, welches Shirt in welcher Farbe erhältlich ist - zum Beispiel ist das Shirt "Marge" in Rot und Schwarz erhältlich. Wenn wir eine Liste aller Shirts und verfügbaren Farben haben möchten, müssten wir 3 Tabellen berücksichtigen z.B. GROUP_CONCAT verwenden, um eine kommaseparierte Liste der Farben zu erhalten. Etwas wie:

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

Solche Abfragen können schnell langsam werden, vor allem wenn wir berücksichtigen, dass Shirts wahrscheinlich nicht nur eine Farbe haben, sondern auch unterschiedliche Größen, Materialien usw.

Verwendung von Bitmasks

Bitmasken können dazu beitragen, diese Art von Abfragen zu verbessern. Die IDs in der Tabelle "colors" sind daher nicht einfach aufsteigende Ganzzahlen, sondern nur Zahlen, die Zweierpotenzen (1,2,4,8,16,32,...) sind. Wir können nun unsere Tabelle "shirts" wie folgt erweitern:

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

Ich habe einfach eine neue Spalte namens "colors" hinzugefügt, die die Summe aller verfügbaren Farb-IDs für jedes Shirt enthält - zum Beispiel ist das Shirt "Homer" in Rot (1) und Grün (4) erhältlich, also ist "colors" 1 + 4 = 5. Das Schöne an Bitmasken ist, dass diese Summe immer eindeutig ist - das bedeutet, dass wir nur aufgrund dieser Summe wissen, in welchen Farben ein Shirt erhältlich ist.

Hier ist ein Beispielcode, der die Summe unserer IDs zurück in die einzelnen Werte umwandelt:

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));

Es gibt aber noch andere Vorteile: Was ist, wenn wir z.B. alle Shirts suchen möchten, die in Rot oder Grün erhältlich sind? Das können wir mit einer einzigen Abfrage tun. Die Summe der Rot- und Grün-IDs wäre 5, also sieht unsere Abfrage so aus:

SELECT * FROM shirts WHERE colors & 5 > 0;

Dies würde eine Liste aller Shirts zurückgeben, die entweder in Rot oder Grün erhältlich sind.

Aber Vorsicht: Die obige Abfrage kann keinen MySQL-Index nutzen. In Bezug auf die Performance kann es daher besser sein, eine einfache IN(x,y,z)-Abfrage zu verwenden.

Fazit

Die Verwendung von Bitmasken kann dazu beitragen, die Performance von SQL-Abfragen in einigen Fällen zu verbessern. Natürlich muss sorgfältig geprüft werden, ob diese Technik im gegebenen Fall nützlich ist. Wenn z.B. die Liste der Attribute (z.B. Farben) sehr lang werden kann, würde ich die Verwendung von Bitmasken nicht empfehlen. Außerdem muss klar sein, dass redundante Daten in Ihrer Datenbank gespeichert werden, wenn die Struktur einer Datenbank wie im Beispiel oben angepasst wird.

Die Verwendung von Bitmasken ist kein Allheilmittel zur Lösung von Performance-Problemen in Datenbanken, kann jedoch in einigen Fällen helfen - und ich hoffe, mit diesem Artikel ein grundlegendes Verständnis dafür vermittelt zu haben, wie Bitmasken funktionieren.

Happy coding!

Hinweis: Diesen Artikel hatte ich (auf Englisch) bereits 2016 auf einer anderen Website veröffentlicht.