Gefällt dir dieser Artikel?

MariaDB: Case-Insensitive Keys und der BINARY Operator

erschienen in der Kategorie Webdesign, am 02.02.2018
Schnatterente
Ich wurde heute mit einem interessanten Problem konfrontiert. Ein Freund von mir hat eine recht frequentierte, selbst programmierte Webapplikation am Laufen. In den letzten Jahren ist die Datenbank immer voller und dementsprechend auch so manches langsamer geworden.

Er bat mich um Hilfe, da gegenzusteuern. Wir haben uns also hingesetzt, hier und da ein paar Queries vereinfacht, zusätzliche Indizes angelegt und die Ausführungszeiten der relevanten Skripte erfasst.

Nach einer Weile blieben wir dann an einer Stelle hängen, bei der wir uns erst nicht so recht erklären konnten, warum der folgende Query so lang braucht:
SELECT * FROM `table` WHERE BINARY `abc` = 'bla';

Der eine oder andere wird sich jetzt fragen, was der ominöse BINARY Operator zu bedeuten hat. Den kann man benutzen, um quasi case-sensitive (also unter Beachtung der Groß- und Kleinschreibung) in Daten zu suchen, die case-insensitive gespeichert wurden.

Das ist zugegebenermaßen ein eher seltener Anwendungsfall. Bei der Software meines Freundes ist es aber so, dass die besagte Spalte ("abc"), auf der auch ein Index angelegt ist, aus historischen Gründen im utf8_general_ci-Format vorliegt. Wie das "ci" am Ende verrät, wird bei dieser Kollation also nicht zwischen Groß- und Kleinschreibung unterschieden.
Irgendwann wurde die Software erweitert und dann war gewünscht, dass man auch case-sensitive suchen kann. So zog der BINARY Operator in den Code ein.

Die Folge dessen ist, dass MariaDB bei dem gezeigten Query nun nicht mehr nur die Zeichenkette abgleicht, sondern überprüft, ob der Binärcode der zu vergleichenden Objekte auch gleich ist. Unsere Tests haben gezeigt, dass dies nicht allzu performant umgesetzt wurde. MariaDB vergleicht binär und muss dafür einen Scan über alle Indexeinträge durchführen, welche zum Vergleich aber erst einmal in die Binärdarstellung überführt werden müssen. Dies dauert viel länger, als ein herkömmlicher Datenzugriff über einen Index.

Da mein Freund die Kollation der Spalte nicht ändern wollte, haben wir dann versucht den Query schneller zu bekommen. Glücklicherweise ist uns das auch mit trivialen Mitteln gelungen:
SELECT * FROM `table` WHERE `abc` = 'bla' AND BINARY `abc` = 'bla';


Mit dieser doppelten WHERE-Bedingung sucht MariaDB erst jene Datensätze heraus, bei denen der Inhalt der Spalte "abc" in irgendeiner Groß-Kleinschreib-Variante dem String "abc" entspricht (was Dank Index äußerst schnell geht) und überprüft dann nur noch diese Varianten auf binäre Übereinstimmung.

Das Resultat kann sich sehen lassen. Aus 0,3 Sekunden Ausführungszeit für den Query wurden 0,00002 Sekunden.

Wer also, aus was für Gründen auch immer, den BINARY Operator zur Datensuche einsetzt, dem sei empfohlen, diesen aus Performanzgründen um die einfache, textbasierte Suche zu erweitern.

Eigentlich könnten hier auch die MariaDB-Entwickler ansetzen. Wann immer ein Index auf der jeweiligen Spalte vorliegt, könnte der Ausführungsplan automatisch so geändert werden, dass vorm Binärvergleich eine textbasierte Suche erfolgt.

Geschnatter

2 Kommentare, selbst mitschnattern << < Seite 1/1 > >>
Tobi, am 02.02.2018 um 20:41 Uhr
Danke für den informativen Beitrag. Ich ziehe daraus, dass es tatsächlich eine Lösung gibt in _ci Zeichensätzen nach unterschiedlichen Schreibvarianten zu suchen. Das war mir nicht bekannt und hätte ich schon mehrfach brauchen können.

Grüße
Tobi
Matthias, am 05.02.2018 um 17:44 Uhr
Auch von mir vielen Dank für den Tipp! Wäre es nicht auch denkbar gewesen die ganze Spalte (mit allen Datensätzen) anzupassen?

Noch eine kleine Korrektur: Statt incase-sensitive meintest du vermutlich case-insensitive, oder? ;)
Antwort: Ups, ja, vertippt. Hab's korrigiert. Wenigsten stimmte es in der Überschrift. :)