Effizientes Speichern von Ja/Nein-Attributen

Bei der Entwicklung von Software mit Datenhaltung in einer relationalen Datenbank (RDBMS) stößt man oft auf die Situation, viele verschiedene Ja/Nein-Attribute speichern zu müssen und effizient auffindbar zu machen.

Für das folgende Szenario stelle ich mir vor, dass 1.000.000 Datensätze gespeichert sind, die jeweils 30 verschiedene Ja/Nein-Attribute haben können.

Dabei sehe ich oft diese Lösung: Jedes Attribut wird in einer eigenen Tabellenspalte untergebracht. Für jedes Attribut wird der INT-Datentyp verwendet, selbst wenn es sinnvollere Datentypen gibt. In der Postgres-Welt wäre der Speicherplatzbedarf

30 Attribute*4 Bytes*1.000.000 Datensätze

und das sind 120 MB. Möglicherweise kommen noch dazu:

• 30 Indexe
• 30 Getter und 30 Setter im Entity-Model

Betrachtet man nur die 120 MB Speicherplatz fällt zunächst kein Problem auf — finanziell spielt diese Datenmenge heute keine große Rolle. Ein ineffizientes Datenmodell wie dieses kann aber durchaus darüber entscheiden, ob man 10, 100 oder 500 solcher Anwendungen parallel auf einem Host laufen lassen kann.

Genießt man den Luxus einer grünen Wiese (oder wenigstens einer freien Hand beim Refactoring), könnte man durchaus mit einem Bruchteil dieser Ressourcen auskommen und so — in kleinem Rahmen — aktiv Umweltschutz betreiben: Mit einer Bitmaske, dargestellt als Integer.

Das würde für das oben genannte Beispiel bedeuten, dass man mit einer 4-Byte-Integer-Spalte alle 30 Attribute abbildet und statt 120 MB nur 4 MB benötigt.

Das ist insbesondere dann interessant, wenn der Datenbankserver keinen echten Boolean-Datentyp anbietet, wie z.B. MySQL, MariaDB oder Oracle. Im Folgenden verwende ich allerdings Postgres als Beispiel, weil es zur Zeit das System ist, mit dem ich am häufigsten arbeite.

Ein konkretes Beispiel

Wir erinnern uns nun an die ersten Informatik-Vorlesungen und stellen uns einen vereinfachten Fall vor, bei dem wir sechs verschiedene Ja/Nein-Attribute haben und weisen jedem Attribut eine Zweierpotenz zu. Die Zweierpotenzen ermöglichen uns die bitweise Differenzierung der Attribute, denn wir wollen später in der Lage sein, per SQL nach diesen Attributen suchen zu können:

Weiterhin stellen wir uns vor, dass wir Christian Drosten in der Tabelle speichern wollen. Wie speichern wir verschiedene Attribute in einer einzigen Tabellenspalte?

Herr Drosten ist augenscheinlich ein europäischer, männlicher Wissenschaftler, also addieren wir bitweise wie folgt:

Das bedeutet, dass wir Christian Drosten’s Attribute auf die Dezimalzahl 14 summieren können — diesen Wert speichern wir. Für einige andere Personen sehen die Werte wie folgt aus:

Suchen per Bitmaske

Wie man per SQL in der Tabelle nach bestimmten Attributen suchen kann mag nicht sofort offensichtlich sein — es ist aber eigentlich recht simpel:

Wir suchen nach den als Integer repräsentierten Bits der Attribute. Einige Beispiele:

-- Alle Wissenschaftler finden:
SELECT * FROM tabelle WHERE attributwert & 4 = 4;

-- Alle männlichen Amerikaner finden: 
SELECT * FROM tabelle WHERE attributwert & 16 = 16 AND attributwert & 2 = 2;

In der Praxis möchte man auf Applikationsseite sicher eher mit benannten Konstanten als mit Zahlen arbeiten, denn “IST_AMERIKANISCH” sagt einem womöglich auf Anhieb mehr als “16”.

Jetzt ausprobieren als SQL-Fiddle!

Pro-Tipp: Solche Abfragen kann man auch prima als View speichern und später wiederverwenden.

Attribute setzen und entziehen

Attribute können mit Bit-Operationen idempotent gesetzt und wieder entzogen werden. Beispielsweise kann man Rick Sanchez so zum Aktivisten machen:

-- Rick Sanchez wird zum Aktivisten.
-- Erklärung: "setze dort 1, wo in 'attributwert' ODER '32' das Bit 1 steht":
UPDATE tabelle SET attributwert = attributwert | 32 
  WHERE character_name = 'Rick Sanchez';

-- Aber eigentlich ist er keiner, also wieder rückgängig machen.
-- Erklärung: "setze dort 1, wo in 'attributwert' UND NICHT in '32' das Bit 1 steht":
UPDATE tabelle SET attributwert = attributwert & ~32 
  WHERE character_name = 'Rick Sanchez';
-- Hinweis: Der Operator vor der 32 ist kein Minus, sondern steht für 'NICHT'.

Schlusswort

Ich sehe es als aktiven Umweltschutz an, skalierbare Software energieeffizient zu schreiben.