Datenbank-Collations steuern, wie MySQL Zeichenketten vergleicht und sortiert – und sie beeinflussen direkt CPU-Last, Index-Nutzung und I/O. Wähle ich eine langsame Collation oder mische Einstellungen, verlängern sich Abfragen, entstehen Konvertierungen und es drohen „Illegal mix“-Fehler.
Zentrale Punkte
- Charset/Collation: Falsche Kombis erzwingen Konvertierungen und bremsen.
- Indizes: Case-Insensitive verringert Selektivität, Case-Sensitive beschleunigt Matches.
- Unicode: utf8mb4 ist genauer, kostet aber mehr CPU.
- Konsistenz: Einheitliche Einstellungen verhindern Filesort und Full Scans.
- Tuning: Collation-Wahl mit Memory, Pooling und Query-Design kombinieren.
Was Collations sind – und warum sie Performance kosten oder sparen
Ich nutze Collations, um Vergleichs- und Sortierregeln für Strings festzulegen. Sie hängen mit dem database charset zusammen, das die Zeichenkodierung bestimmt, etwa utf8mb4 oder latin1. Wähle ich eine genauere Unicode-Collation wie utf8mb4_unicode_ci, steigen die Rechenkosten pro Vergleich. In Messungen mit MySQL 8.0 liefen OLTP-Workloads mit neueren Unicode-Collations teils 10–16 % langsamer, dafür stimmen Vergleiche für Sprachen und Emojis besser (Quelle [2]). Für reine Speed-Workloads greifen einfache Regeln wie utf8_general_ci, doch sie liefern weniger genaue Ergebnisse (Quelle [2]).
Charset vs. Collation: kleine Unterschiede, große Wirkung
Der Charset legt fest, wie MySQL Bytes speichert, die Collation entscheidet, wie MySQL diese Bytes vergleicht. Mische ich Collations in JOINs oder WHERE-Bedingungen, konvertiert MySQL on-the-fly – spürbar teurer bei großen Tabellen (Quelle [2]). Das kostet CPU, erzeugt temporäre Tabellen und kann zu Filesort auf Disk führen. Ich halte deshalb App-Ebene, Datenbank, Tabellen und Spalten strikt einheitlich. Für breitere Optimierung schließe ich das Thema Collation in meine Maßnahmen zur SQL-Datenbank optimieren ein.
Versionen und Defaults: was sich zwischen 5.7 und 8.0 geändert hat
Beim Upgrade achte ich auf Defaults: MySQL 8.0 setzt standardmäßig auf utf8mb4 und in vielen Builds auf utf8mb4_0900_ai_ci. Ältere Installationen nutzen oft latin1_swedish_ci oder utf8_general_ci. Der Wechsel verändert nicht nur die Kodierung, sondern auch die Sortierreihenfolge und Gleichheitsregeln. Das führt dazu, dass ORDER BY-Ergebnisse anders aussehen, UNIQUE-Indizes neu kollidieren oder plötzlich Duplikate entstehen, die vorher „gleich“ waren (oder umgekehrt). Ich plane deshalb Upgrades so, dass ich vorab prüfe: SELECT @@character_set_server, @@collation_server, @@collation_database; und lege im Zielsystem die Defaults bewusst fest. Ich teste gleichzeitig, wie sich utf8mb4_0900_ai_ci gegenüber utf8mb4_unicode_ci in meinen realen Queries verhält, weil die 0900-Varianten (ICU-basiert) oft präzisere, aber teurere Regeln mitbringen (Quelle [2]).
Indizes und Query-Pläne: wo Collations bremsen
Collations steuern die Index-Nutzung mit. Case-insensitive (_ci) weitet die Suche, senkt aber die Selektivität – der Optimizer greift dann seltener zum Index. Case-sensitive (_cs) beschleunigt exakte Matches, passt aber nicht zu allen Anforderungen. Wechselt eine Spalte die Collation, ändern sich Vergleichsregeln und damit der Plan – Filesort taucht häufiger auf, teils mit temporären Tabellen (Quelle [1], [3]). Mehr Hintergründe zur Indexwirkung decke ich in „Indexe: Nutzen und Risiken“ ab.
Häufige Fehlerbilder und direkte Lösungen
Die Meldung Illegal mix weist fast immer auf gemischte Collations hin. Ich löse das kurzfristig mit COLLATE in der Abfrage, langfristig vereinheitliche ich die Spalten. Treten Filesort und hohe Latenzen auf, prüfe ich die ORDER-BY-Spalten und passe die Collation an die Indexdefinition an (Quelle [3]). Bei JOINs mit TEXT/VARCHAR-Spalten achte ich auf identische Collations, sonst zwingen Konvertierungen den Optimizer zu schlechten Plänen. Konsistenz bringt oft sofort messbare Millisekunden-Gewinne.
Die MySQL-Hierarchie: von Server bis Ausdruck
MySQL kennt Collations auf fünf Ebenen: Server, Datenbank, Tabelle, Spalte, Ausdruck. Die unterste Ebene gewinnt, daher führen Abweichungen zu Überraschungen. Ich prüfe Einstellungen mit `SELECT SCHEMA_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA`, `SHOW TABLE STATUS` und `SHOW FULL COLUMNS`. Trifft eine Query `col1 COLLATE utf8mb4_unicode_ci = col2`, werten unterschiedliche Spalten-Collations den Vergleich auf – das kostet Zeit (Quelle [1]). Vor Änderungen setze ich Backups und teste die Umkodierung in Staging, um Datenverdrehungen zu vermeiden.
Verbindungs- und Session-Settings: wo Bugs entstehen
Viele Probleme passieren nicht im Schema, sondern in der Session. Ich prüfe die Variablen character_set_client, character_set_connection, character_set_results sowie collation_connection. ORMs setzen teils SET NAMES und überschreiben damit Server-Defaults; gemischte Deployments führen zu „unsichtbaren“ Konvertierungen. Ich halte mich an klare Regeln: Die App sendet UTF-8 (utf8mb4), die Verbindung setzt SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci; oder lässt es von der Treiber-Option setzen. Zum Debuggen nutze ich SHOW VARIABLES LIKE 'collation%'; und SELECT COLLATION(column), COERCIBILITY(column) bzw. COLLATION('literal'). Weichen hier Werte ab, finde ich die Ursache für temporäre Tabellen und Mismatch-Fehler meist schnell (Quelle [1]).
Case-Insensitive vs. Case-Sensitive: wann welche Wahl passt
Mit _ci ignoriere ich Groß-/Kleinschreibung, was Nutzerfreundlichkeit erhöht. Dafür sinkt die Selektivität, und LIKE-Suchen greifen seltener sauber auf Indizes zu. Mit _cs treffe ich exakte Vergleiche, erhalte schnellere Punktabfragen, verliere aber Bequemlichkeit. Für Logins, Token oder IDs setze ich _cs, für Suchfelder oft _ci. Ich halte beides sauber getrennt, um Missbrauch und Konvertierungen zu verhindern.
Feinheiten: Akzent-, Breiten- und Binary-Regeln (_ai, _as, _bin)
Ich unterscheide mehr als nur Case-Sensitivity. _ai (accent-insensitive) behandelt „é“ und „e“ als gleich; _as (accent-sensitive) unterscheidet sie. In Ostasiatischen Sprachen spielt zudem die Breite eine Rolle (Voll-/Halbbreite), während _bin reine Bytevergleiche durchführt – am schnellsten, aber ohne sprachliche Logik. Für Logs, Hashes und IDs nehme ich _bin oder _cs, für Benutzersuchen häufig _ai, damit Tippfehler und Akzente nicht ins Gewicht fallen. Ich teste bewusst Beispiele: SELECT 'straße' = 'strasse' COLLATE utf8mb4_0900_ai_ci; liefert TRUE, während ... COLLATE utf8mb4_0900_as_cs; FALSE ist. Solche Regeln entscheiden, wie viele Zeilen ein Index-Range-Scan umfasst – und damit über Latenz und I/O.
Benchmarks richtig lesen: Genauigkeit kostet CPU
Unicode-Collations wie utf8mb4_unicode_ci und utf8mb4_0900_ai_ci decken Sprachen, diakritische Zeichen und Emojis korrekt ab. Die Vergleichslogik ist aufwendiger, was pro Vergleich mehr CPU kostet. In OLTP-Szenarien mit vielen String-Vergleichen zeigen Messungen 10–16 % längere Laufzeiten, je nach Workload und Datensatzgröße (Quelle [2]). Kleine Tabellen spüren das weniger, breite Suchen und Sortierungen stärker. Ich entscheide je Anwendungsfall und beziehe Nutzeranforderungen ein.
Indexgröße, Prefix-Limits und Speicherbedarf
Mit utf8mb4 plane ich Indexbreite bewusst, denn ein Zeichen kann bis zu 4 Bytes belegen. InnoDB begrenzt die Länge von Index-Schlüsseln (historisch 767 Bytes, in neueren Versionen und Row-Formaten effektiv bis 3072 Bytes). Das wirkt sich auf VARCHAR-Spalten, Composite-Indizes und Covering-Indizes aus. Ich prüfe daher: Reichen 191 Zeichen (191×4≈764 Bytes) für E-Mail oder URL? In 5.7-Setups war das häufig die sichere Wahl, in 8.0 kann ich oft auf 255 hochgehen – solange Composite-Indizes nicht aus dem Rahmen fallen. Wo nötig, setze ich Prefix-Indizes: CREATE INDEX idx_email ON users(email(191)); Das spart Platz, mindert aber Selektivität; ich messe den Effekt mit EXPLAIN ANALYZE und dem Slow-Query-Log (Quelle [3]). Größere Schlüssel blähen außerdem den Buffer Pool: pro zusätzlichem Byte steigen Cache-Druck und I/O – Collation-Entscheidungen wirken somit bis auf Speicherkosten durch.
Hosting-Tuning: Collation, Buffer und Pooling zusammen denken
Ich erhöhe die innodb_buffer_pool_size, damit Indexe und Hot-Daten im Speicher bleiben. Mit Connection-Pooling senke ich Overhead pro Request, und Proxy-Layer verringern Spikes. Für Dateiformate, Redo-Log-Größe und Page-Size passe ich die Ziel-Workload an. Zusätzlich wähle ich die Storage-Engine bewusst; ein Blick auf InnoDB vs. MyISAM zeigt typische Unterschiede bei Transaktionen, Locks und Crash-Sicherheit. Ohne konsistente Collations verpufft ein Teil dieses Tunings.
Best Practices: Auswahl nach Einsatzszenario
Für moderne Web-Apps setze ich utf8mb4 als Charset, weil es Emojis und volle Unicode-Abdeckung bringt. Benötige ich maximale Genauigkeit für Sortierung in mehreren Sprachen, greife ich zu utf8mb4_unicode_ci oder utf8mb4_0900_ai_ci. Für reine Geschwindigkeit bei einfachen Vergleichen ist utf8_general_ci oft schneller, akzeptiert aber Ungenauigkeiten (Quelle [2]). Ich halte die Collation-Strategie konsistent auf Server-, Schema-, Tabellen- und Spaltenebene. Tests mit EXPLAIN ANALYZE und Slow-Query-Log sichern die Entscheidung ab (Quelle [3]).
| Collation | Genauigkeit | Geschwindigkeit | Emoji-Support | Geeignet für |
|---|---|---|---|---|
| utf8_general_ci | Niedrig | Hoch | Nein | Schnelle Suchen |
| utf8_unicode_ci | Hoch | Mittel | Nein | Unicode-Apps |
| utf8mb4_unicode_ci | Sehr hoch | Niedrig | Ja | Moderne Web |
| utf8mb4_0900_ai_ci | Höchste | Mittel | Ja | Multilingual |
Schritt-für-Schritt: Umstellung ohne Ausfall
Ich starte mit Inventur: Welche Schemas, Tabellen und Spalten nutzen welche Collations? Danach sichere ich Daten, exportiere kritische Tabellen und lege Rehearsals in Staging an. Die Umstellung erfolgt mit `ALTER TABLE … CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;`, beginnend bei wenig genutzten Tabellen. Für große Tabellen plane ich Wartungsfenster oder nutze Online-Migrationstools wie Percona Toolkit (Quelle [1], [2]). Nach der Umstellung prüfe ich EXPLAIN, das Slow-Query-Log und vergleiche Latenzen.
Diagnose: die richtigen Fragen an die Datenbank
Ich prüfe SCHEMATA und `SHOW FULL COLUMNS`, um Abweichungen sichtbar zu machen. Treten Filesort und temporäre Tabellen auf, steigere ich sort_buffer_size nicht blind, sondern beseitige das Collation-Mismatch. Mit EXPLAIN sehe ich, ob ein Index greift oder Full Scans stattfinden. Mit Performance Schema messe ich tmp_disk_tables und sort_merge_passes, um sortierbedingte I/O zu erkennen. So finde ich bottlenecks, die direkt aus String-Vergleichen stammen (Quelle [3]).
GROUP BY, DISTINCT und UNIQUE: semantische Folgen der Collation
Collations definieren, wann Werte als „gleich“ gelten. Das wirkt auf Deduplikation und Eindeutigkeitsregeln. Wechsel ich von _cs auf _ci oder von _as auf _ai, kann ein UNIQUE-Index plötzlich Kollisionen melden. Vor Migrationen suche ich potenzielle Konflikte: SELECT col, COUNT(*) FROM t GROUP BY col COLLATE utf8mb4_0900_ai_ci HAVING COUNT(*) > 1;. So sehe ich, welche Zeilen in der Zielcollation zusammenfallen. Ich beachte das auch bei GROUP BY und DISTINCT: Die Anzahl der Gruppen hängt vom Regelwerk ab, und damit auch der Plan (mehr oder weniger Sort/Hash-Aufwand). Für Report-Tabellen kann eine bewusst „grobe“ Collation sinnvoll sein, die weniger Gruppen erzeugt; bei Kassen-IDs und Logins ist sie riskant.
Design-Patterns: Binary, generierte Spalten und funktionale Indizes
Ich trenne Darstellung und Suche: Die sichtbare Spalte bleibt in einer „schönen“ Collation (z. B. utf8mb4_0900_ai_ci), dazu lege ich eine generierte Spalte an, die für performante Vergleiche normalisiert ist – etwa kleingeschrieben und binär. Beispiel: ALTER TABLE user ADD name_search VARCHAR(255) GENERATED ALWAYS AS (LOWER(name)) STORED, ADD INDEX idx_name_search (name_search); Mit einer _bin– oder _cs-Collation auf name_search erhalte ich exakte, schnelle Matches bei WHERE name_search = LOWER(?). In MySQL 8.0 kann ich zudem die Collation im Index angeben: CREATE INDEX idx_name_ai ON user (name COLLATE utf8mb4_0900_ai_ci); So bleibt die Spalte z. B. _cs, während der Index bewusst _ai nutzt – praktisch für „fuzzy“ Suche ohne Full Scan. Ich dokumentiere diese Muster im Schema, damit der Query-Generator der App die richtige Spalte bzw. den richtigen Index verwendet.
LIKE, Präfixe und Volltext: was wirklich beschleunigt
Bei LIKE-Suchen gelten die normalen Collation-Regeln. Ein führendes Wildcard (LIKE '%abc') verhindert Indexnutzung – egal, wie gut die Collation gewählt ist. Ich forme deshalb Suchmuster so um, dass Präfixe genutzt werden (LIKE 'abc%') und achte auf die Collation-Kompatibilität, damit MySQL nicht zwischendrin konvertiert. Für große freie Texte nehme ich FULLTEXT-Indizes; die Tokenisierung ist weitgehend unabhängig von Collation, aber Zeichenkodierung und Normalisierung beeinflussen Treffer. In CJK-Umgebungen helfen NGRAM-Parser; in westlichen Sprachen vermeide ich zu „grobe“ Collations, damit Stemming/Stopwords nicht zu viel zusammenwerfen. Auch hier gilt: Konsistenz vom Feld bis zur Verbindung verhindert temporäre Tabellen und Filesort (Quelle [3]).
Praxis: WordPress, Shops und APIs schnell halten
Content- und Shop-Systeme profitieren von utf8mb4_unicode_ci, weil Slugs, Kategorien und Benutzerinhalte sauber sortieren. Ich achte darauf, dass Plugins keine abweichenden Collations anlegen. In APIs und Auth-Pfaden setze ich für Token _cs, um exakte Matches via Index sicherzustellen. Bei Reports mit ORDER BY auf großen Textfeldern kombiniere ich Collation-Konsistenz und passende Covering-Indizes. Ergänzend schaue ich mir für mehr Durchsatz die Tipps aus SQL-Datenbank optimieren an.
Kompakte Zusammenfassung
Ich wähle Collations bewusst: Geschwindigkeit, Genauigkeit und Nutzererwartung bestimmen die Entscheidung. Einheitliche Einstellungen verhindern Konvertierungen, Filesort und ineffiziente Pläne. Unicode-Varianten liefern bessere Ergebnisse, kosten jedoch mehr CPU; Messungen mit MySQL 8.0 zeigen Einbußen von 10–16 % bei intensiven String-Workloads (Quelle [2]). Mit sauberem Schema-Design, Indizes, Buffer-Pool und Pooling skaliert die MySQL-Instanz verlässlich. Wer systematisch prüft, testet und konsolidiert, senkt Latenz und steigert die MySQL collation performance spürbar.
