Zeichensatzvorgaben für MySQL und PHP – SET NAMES – und leere Strings durch htmlentities()

Gestern ist mir ein dummer Fehler passiert, dessen Analyse mich Zeit gekostet hat. Dabei erwies sich die Sache am Ende als trivial. Es ging letztlich um konsistente Zeichensatzeinstellungen für PHP und MySQL - allerdings mit einem mir bislang unbekannten Nebeneffekt.

Zeichensätze im Kontext von PHP und MySQL sind ein Thema vieler Foren- und Q&A-Artikel im Internet - und nicht immer trifft man auf zufriedenstellende Antworten. Ich hoffe, dieser Artikel trägt anhand eines Beispiels zu etwas mehr Klarheit bei.

Voraussetzungen und aufgetretener Fehler

Unsere hausinternen Apache- und Datenbank-Server laufen normalerweise vollständig unter UTF-8. Inklusive der eigenen Datenbank- und Tabellen-Kollationen unter MySQL- oder MariaDB-Systemen. Aber für Tests müssen wir immer mal wieder gezielt eine Kompatibilität zu den festgelegten Kollationen für MySQL-Banken/Tabellen auf Kundenservern herstellen. Meist kommt dann Latin1 (iso-8859-1) ins Spiel.

Gestern mussten wir für einen solchen Test Datensätze eines von uns entwickelten, php-basierten CMS von einem gehosteten MySQL-Kundenserver in unsere lokale Test-Datenbank übernehmen. Diese Datensätze beinhalteten viele Text-Strings mit deutschen Umlauten. Da es sich nur um wenige Records handelte, haben wir die Daten in diesem Fall mit Copy/Paste und mit Hilfe von Eingabefeldern unserer CMS-Verwaltungsoberfläche übernommen. Das CMS lief dabei unter einer lokalen UTF8-Standarddomäne. Unsere aktuellen CMS-Programme zeigten die fraglichen Textstrings denn auch korrekt in der Web-Oberfläche des CMS an.

Es gab aber andere zu untersuchende Punkte im Layout. Um die Unstimmigkeiten zu testen, haben wir zusätzlich eine separate Testdomäne angelegt und diverse PHP-Klassen vom Kundenserver (auf dem dortigen Versionsstand) in bestimmte Verzeichnisse des zugehörigen Web-Spaces auf unserem lokalen Server geladen. Ergebnis: 2 Domainen mit z.T. unterschiedlichen Versionsständen von PHP-Klassen.

Und dann passierte es:

Bei einem Aufruf der Webseiten in der Testdomäne verschwanden plötzlich fast alle Text-Strings aus der Web-Oberfläche.

Bilder und grundsätzlicher Aufbau der Webseiten bleiben jedoch erhalten. Ich war zunächst völlig verblüfft über diesen Effekt. Zumal der Einsatz unserer lokalen Versionen der gleichen PHP-Klassen kein Verschwinden der Textstrings zeitigte.

Die Analyse war nicht ohne, da ich zunächst nicht wusste, wonach ich zu suchen hatte. Man denkt da zunächst natürlich an Unterschiede im Programmcode selbst. Am Schluss entpuppten sich aber eine im wesentlichen unmodifizierte Klasse, die die Datenbankverbindung steuert, sowie eine Klasse, die die Strings aus Sicherheitsgründen nach unerlaubten Sequenzen filtert, als Kerne des Übels. Ausschlaggebend waren allerdings nicht Programmunterschiede sondern bestimmte Parameter-Setzungen sowie Server- und Zeichensatzeinstellungen. Aber der Reihe nach.

Zeichensatzeinstellungen in der Kommunikationskette zwischen einem PHP-Server und einer MySQL-Datenbank

In der Datenaustausch-Kette zwischen einer MySQL-Datenbank und PHP-Modulen auf einem Web-Server (und natürlich auch bei der Übersendung eines HTML-/XML- oder JSON-Outputs an Web-Clients) spielen verschiedene Zeichensatzeinstellungen eine Rolle. Einige davon können vom Entwickler beeinflusst werden. Andere wiederum nicht immer.

Für unseren Fall waren vor allem die nachfolgenden Punkte relevant; sie betreffen den Web-Server (mit PHP-Modul) und die MySQL-Datenbank:

  1. Zeichensätze ("Kollationen") der MySQL-Datenbank und zugehöriger Datenbanktabellen.
  2. Zeichensatz-Vorgaben zur MySQL-Verbindung und zum Datentransfer von und zu (PHP-)Programmen, die mittels der SQL-Direktive "SET NAMES" vorgenommen wurden.
  3. Einstellungen für den Default-Character-Set des PHP-Apache-Moduls.
  4. Zeichensatzeinstellungen für die PHP-Funktion "htmlentities()".

Zeichensätze in der Datenbank und die Direktive "SET NAMES"

Für die Kollation der relevanten MySQL-Datenbank und ihrer Tabellen war auf dem Kundenserver "latin1_german2_ci" gewählt worden. Unsere Einstellungen im lokalen Testsystem waren dazu auf Tabellenebene kompatibel. Die Kollation einer Datenbanktabelle bestimmt letztlich aber nur die interne Ablage der Daten in der Tabelle und nicht den Zeichensatz, unter dem z.B. per SQL ermittelte Resultsets an weiterverarbeitende Programme übermittelt werden.

Für letzteres sind andere Parameter verantwortlich, die man als Entwickler für eine spezifische Verbindung zur MySQL-Datenbank einstellen kann. Unter MySQL und der MariaDB nutzt man dafür etwa die SQL-Direktive "SET NAMES" (oder aber die Funktion mysqli_set_charset(); s.u.).

"SET NAMES" führt zur gleichzeitigen Festlegung dreier RDBMS-Parameter für die Behandlung einer spezifischen Datenbankverbindung. Diese Parameter sind: character_set_client, character_set_connection, character_set_results.

Siehe hierzu etwa
https://dev.mysql.com/doc/refman/5.7/en/set-names.html
und
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_character_set_results.

Die Datenbankverbindung wird unter dem PHP/mysqli-Interface dabei über das Connection-Objekt

$dbi = mysqli_connect(....)

identifiziert. Im Wesentlichen legen die genannten Parameter Folgendes fest:

  • character_set_client: Zeichensatz, unter dem Daten, die vom Datenbank-Client zum RDBMS-Server transferiert werden, interpretiert werden.
  • character_set_connection: Handhabung bestimmterErsetzungen und Konversionen, u.a. von Numbers zu Strings.
  • character_set_results: Zeichensatz, unter dem Daten, die vom RDBMS zu Client-Programmen übermittelt werden interpretiert werden.

Der "Client" ist in unserem Fall natürlich ein PHP-Programm auf einem Apache-Server. Die Anwendung des "SET NAMES"-Statements durch ein PHP-Programm, z.B.

$sql_unames = "SET NAMES 'utf8'";
$this->dbi->query($sql_unames);
//dbi->Datenbank-Connection Object
//$this->dbi = mysqli_connect(....)

ist somit von zentraler Bedeutung für die Kommunikation zwischen einem PHP-Programm und einer MySQL/MariaDB! Solange eine hinreichende Konvertierung verwendeter Zeichen gewährleistet ist, muss der Zeichensatz, unter dem Resultsets zum PHP-Programm übermittelt werden, nicht zwingend mit dem der Datenbanktabellen selbst identisch sein.

Es sei darauf hingewiesen, dass es zu "SET NAMES" auch Alternativen gibt, die man im Rahmen des mysqli-Interfaces einsetzen kann und sollte (s.u.). Dass wir in einigen Programmen noch SET NAMES verwenden, hat lediglich historische Gründe. Die hier beschriebene Problematik gilt aber unabhängig vom genauen Werkzeug zur Einstellung der Zeichensatzparameter.

Konsistenz zu Zeichensatzvorgaben für PHP - Einstellungen in der php.ini

Das PHP-Programm muss in jedem Fall mit dem gewählten Zeichensatz für Resultsets adäquat umgehen können; s.u.. Der rechte Bereich der folgenden Skizze, die ich aus einem anderen Artikel dieses Blogs entliehen habe, verdeutlicht das für den Fall "SET NAMES 'latin1'" :

Nun könnte man in seinen PHP-Programmen natürlich spezifische Umwandlungsfunktionen (u.a. iconv(), mb_convert_encoding(), utf8-encode(), utf8-decode) für die Zeichensatzkonvertierung aus- und eingehender Strings bemühen. Die linke Seite der Skizze liefert hierfür Beispiele (s. den dazu gehörigen Abschnitt weiter unten).

Wenn möglich, kann man aber auch einen Standardzeichensatz für die PHP-Verarbeitung von Strings vorgeben und sich darauf bzgl. der Datenbankinteraktion verlassen.

Entsprechende Einstellungen nimmt man in der Konfigurationsdatei
/etc/php7/apache2/php.ini
vor. Die relevanten Parameter sind dort:

; PHP's default character set is set to UTF-8.
; http://php.net/default-charset
default_charset = "UTF-8"

; PHP internal character encoding is set to empty.
; If empty, default_charset is used.
; http://php.net/internal-encoding
;internal_encoding =

; PHP input character encoding is set to empty.
; If empty, default_charset is used.
; http://php.net/input-encoding
;input_encoding =

; PHP output character encoding is set to empty.
; If empty, default_charset is used.
; mbstring or iconv output handler is used.
; See also output_buffer.
; http://php.net/output-encoding
;output_encoding =

Die hierfür gesetzten Werten sollte natürlich mit den Einstellungen für die Datenbankverbindung zusammenpassen.

Kundenvorgaben

Wir parametrieren in Kundenprojekten die PHP-Methoden für den Verbindungsaufbau zum Datenbankserver meist gemäß expliziter Kundenvorgaben. In unserem Fall hatten wir "SET NAMES 'latin1'" gewählt. (Hinweis: Die Zeichensatz-Namen auf einem MySQL-Server enthalten grundsätzlich keine Trennzeichen; daher ist statt iso8859-1 "latin1" zu verwenden).

Der Grund dafür war, dass das Apache-PHP-Modul des (gehosteten) Kunden-Web-Servers auf "iso-8859-1" eingestellt war. Das bestätigte eine Überprüfung mit phpinfo(). Diese Einstellungen sollten wir gem. Kundenvorgabe nicht ändern, da auf dem Web-Server auch andere PHP-Programme als unsere eigenen laufen müssen.

Auf dem Kundenserver waren die Zeichensatzeinstellungen also konsistent.

Ursache unseres Problems und die Rolle von htmlentities()

Man ahnt es bereits: Durch das Kopieren der PHP-Klasse für die Steuerung von Datenbankverbindungen vom Kundenserver auf die Testdomäne unseres lokalen Web-Servers entstand dort u.a. eine Inkonsistenz zwischen der Zeichensatzbehandlung unter PHP (utf8!) und dem Zeichensatz für den Transfer von Resultsets aus der MySQL-Datenbank (latin1).

Diese Inkonsistenz kam jedoch noch nicht zum Tragen, als wir die Daten per Copy/Paste über lokale Web-Interfaces einer UTF8-Standard-Domäne in die Bank einbrachten.

Für unsere Testdomäne dagegen muss man jedoch u.a. eine fehlerhafte Konvertierung von deutschen Umlauten erwarten! Warum aber verschwanden die Text-Strings in Gänze aus den Web-Oberflächen?

Die Antwort lieferte schließlich eine von mir bislang zu wenig beachtete Eigenschaft der PHP-Funktion "htmlentities()".

Unsere Web-Generatoren jagen Strings vor einer Web-Darstellung durch eine Reihe von Prüfroutinen, Transformatoren für erlaubte Zeichenfolgen und durch Filter (u.a. HTMLPurifier, aber auch eigene Filter). Dabei wird in einem Zwischenschritt (nach einer vorhergehenden Konvertierung erlaubter HTML-Zeichenfolgen) auch "htmlentities()" eingesetzt.

htmlentities() erlaubt selbst eine Vorgabe des "Character Sets" über einen Parameter. Ein Check zeigte: Dieser Parameter stand in der lokalen Testdomäne explizit auf "UTF-8". Diese Einstellung betrifft jedoch eine Konvertierung in den gewünschten Ziel-Zeichensatz für den HTML-Output. Hier hatten wir kein Problem, da die HTML-Header der Webseiten bzw. die vom Apache-Server generierten HTTP-Header tatsächlich auf UTF-8 ausgerichtet waren.

Allerdings sorgten schon die vorhergehenden Widersprüche zwischen dem Zeichensatz der Datenbank-Resultsets und dem Zeichensatz für die anschließende Behandlung von Strings durch PHP. Das hatte gravierende Folgen. Unter http://php.net/manual/de/function.htmlentities.php findet man nämlich folgenden Hinweis:

Rückgabewerte
Gibt die kodierte Zeichenkette zurück. Enthält der string eine in dem übergebenen encoding ungültige Code Unit Sequenz, wird eine leere Zeichenkette zurückgegeben, sofern weder das ENT_IGNORE noch das ENT_SUBSITUTE Flag gesetzt sind.

(Hervorhebung durch mich).

Das war des Rätsels Lösung:

Eine Inkonsistenz in der Zeichensatzbehandlung im Datenaustausch zwischen unserer MySQL-Datenbank und PHP führte zu nicht behandelbaren Zeichen bei der Anwendung von htmlentities() auf Strings - und diese Funktion produzierte dann gemäß ihrer Default-Einstellungen leere Strings.

Trivial - man muss es halt nur wissen! Ein Test mit

"SET NAMES 'utf8'"

ließ denn alle verschwundenen Strings auch in unserer Testdomäne prompt wieder erscheinen!

Notwendige Checks vor der Anwendung von SET NAMES (oder von mysqli_set_charset())

Hat man die Kette der Zeichensatzsetzungen bzw. Zeichensatzbehandlung im Austausch zwischen PHP und einer MySQL-Datenbank erst einmal verstanden, so ist auch klar, wo man mit vorbeugenden Maßnahmen ansetzen kann. Solche Vorkehrungen sind - wie das Beispiel zeigt - vor allem dann notwendig, wenn man die Zeichensatz-Einstellungen für PHP nicht auf allen involvierten Web-Servern beeinflussen kann oder darf.

Bevor man "SET NAMES" (oder mysqli_set_charset(); s.u.) in einem PHP-Programm tatsächlich anwendet, sollte man die Setzung des "Default Character Sets" in der php.ini für den aktuellen Server explizit abfragen - mittels

ini_get('default_charset');

- und dann mit der ebenfalls abfragbaren Kollation der Datenbanktabellen vergleichen. Das Ergebnis dieses Vergleichs kann man dann nach bestimmten Regeln behandeln:

Z.B. Warnhinweise bei (ernsthafter) Inkompatibilität ausgeben. Oder wenn man wirklich sicher ist, dass nur deutsche Umlaute zu potentiellen Problemen führen können: Wahl des zu den PHP-Einstellungen konsistenten Zeichensatzes für den Transfer von Resultsets aus der Datenbank. In unserem Fall also "utf8".

Alternative: Ändern der php.ini-Vorgaben für den Zeichensatz durch und für das laufende PHP-Programm

Auf festgestellte Inkonsistenzen in den Zeichensatzeinstellungen kann man u.U. - nämlich wenn man die dafür nötigen Rechte besitzt - auch mit einer Modifikation der php.ini-Vorgaben für das laufende Programm reagieren. Dazu muss man natürlich die Funktion ini_set() bemühen; Bsp.:

ini_set( string 'default_charset', 'ISO-8859-1')

bemühen.

Empfohlener Weg zum Setzen des "Character Sets" für eine MySQL-Verbindung

Ich möchte explizit darauf hinweisen, dass es andere Möglichkeiten als die SQL-Direktive "SET NAMES" gibt, Character Sets für die Datenbankverbindung zu setzen. Das PHP-Manual empfiehlt explizit, die Funktion

mysqli_set_charset(mysqli $link , string $charset)

anstelle von SQL und "Set Names" zu verwenden. Siehe: http://php.net/manual/de/mysqli.set-charset.php

Zeichensätze und die Web-Client-Seite

Obwohl nicht Kernthema dieses Artikels werfen wir noch einen ergänzenden Blick auf den Datenaustausch des PHP/Web-Servers mit Web-Clients (z.B. einem Browser). Die Zeichensatzthematik setzt sich natürlich auch auf dieser Kommunikationsstrecke fort; der linke Teil der obigen Skizze verdeutlicht das am Beispiel von Ajax/Ajaj-Programmen. Diese erfordern i.d.R. UTF-8 für einen ordnungsgemäßen Datenaustausch mit dem Web-Server.

Ist der Parameter "default_charset" in der php.ini-Datei aber auf "iso-8859-1" gesetzt, so muss man ein- und ausgehende Daten entsprechend konvertieren. Dafür eignen sich die Funktionen utf8_decode() für einlaufende POST-/GET-Daten aus Ajax-Programmen und utf8_encode() bei der Erzeugung des Ajax/Ajaj-Outputs in Richtung Web-Client.

Für reinen HTML-Output gilt analoges; dabei sind aber auch HTTP- und HTML-Header-Anweisungen für Character Sets zu setzen. Siehe hierzu:
http://www.html-info.eu/php/php-als-script-sprache/item/zeichensatz-latin1-oder-unicode-utf-8.html

Fazit

In unbeabsichtigter Weise kann htmlentities() plötzlich zu einer Testfunktion für die Konsistenz zwischen

  • der Zeichensatz-Einstellungen durch "SET NAMES" bzw. mysqli_set_charset() für die MySQL/MariaDB-Datenbankanbindung an ein PHP-Programm
  • und den Zeichensatzeinstellungen für das PHP-Modul selbst auf dem Webserver

werden - und in letzter Konsequenz zu leeren Strings auf Webseiten führen.

Es lohnt sich vor einem Einsatz von "SET NAMES" - oder besser mysqli_set_charset() - eigentlich immer, die Einstellungen in der "php.ini" bzgl. des "default_charset" und verwandter Parameter abzufragen und daraus angemessene Konsequenzen für den Aufbau der Datenbankverbindung zu ziehen.

Dies gilt vor allem dann, wenn man im Rahmen von Tests und Produktivierungen auf verschiedenen Servern arbeiten will oder muss - und dabei nicht alle Konfigurationsparameter der Server selbst beeinflussen kann und darf.

Neben dem Setzen von Server- und Verbindungsparametern kann man auf festgestellte oder vorgegebene Zeichensatzanforderungen oder Zeichensatzdiskrepanzen aber auch gezielt mit verschiedenen Funktionen reagieren, die PHP für eine Zeichensatz-Detektion und eine explizite Zeichensatzkonvertierung von Strings anbietet.

MySQL – effiziente Prüfung von Tabellenspalten auf (ausschließlich) vorhandene Default-Einträge

Einige meiner Leser wissen, dass ich mich für PHP-basierte Simulationsrechnungen mit dem Thema des Uploads von CSV-Daten-Files für viele Tabellen befassen muss. Nun haben wir eine Situation, in der eine Tabelle zwar viele Spalten enthalten kann, aber für bestimmte Simulationen nicht alle Spalten zwingend mit Werten gefüllt werden müssen. Um die Upload-Zeiten gering zu halten, werden diese Spalten in den CSV-Dateien gar nicht übermittelt. Das durchzuführende LOAD DATA INFILE Statement wird entsprechend angepasst und versorgt die Spalten, für die das File keine Daten liefert, mit Default-Werten.

Nun erfordert jede unserer Simulationsrechnungen eine vorherige Prüfung von Konsistenzbedingungen. Dabei ist es u.a. wichtig, welche Spalten ausschließlich mit Default-Werten gefüllt wurden und welche nicht. Die Meinung eines beteiligten Entwicklers war, dass man diese Information - wenn nötig - unmittelbar aus dem tatsächlich gegebenen Tabellenzustand ermitteln sollte, um immer eine verlässliche Entscheidungsgrundlage zu haben.

Als Voraussetzung nehmen wir an, dass wir aus Platzgründen keine Indices für die einzelnen oder gekoppelten Daten-Spalten der zu untersuchenden Tabelle angelegt haben.

Abfrage über mehrere SQL-Statements für jede Spalte

Der erste naive Anlauf war dementsprechend, in mehreren SQL-Abfragen Spalte für Spalte auf die Anzahl der gesetzten Default-Werte zu prüfen und mit der Gesamtzahl der Tabellen-Records zu vergleichen. Das einzelne Select würde dann etwa so aussehen:

SQL-Typ1:
SELECT COUNT(*) FROM myTable WHERE colx=defaultValue;

Das Ganze pro Spalte für alle zu untersuchenden Tabellen. Ein solches Vorgehen erschien mir ineffizient - insbesondere wenn eine Tabelle viele zu untersuchende Spalten aufweist und man das Ganze für viele Tabellen machen muss. Der Grund:

Jede dieser Abfragen führt einen Full Table Scan durch, denn man wird in den seltensten Fällen einen Index über jede einzelne Spalte oder alle Spalten gelegt haben.

Wir reden in unserem Fall zudem über potentiell große Tabellen mit mehreren 10-Millionen Einträgen.

Ist die Abfrage in einem einzigen SQL-Statement effizienter?

Zunächst dachte ich deshalb darüber nach, ob man die Aufgabe nicht in einem einzigen Statement durchführen kann. Ergebnis:

SQL-Typ 2:
SELECT
SUM(IF(col1 = default_val_col1, 1,0)) as num_def_col1,
SUM(IF(col2 = default_val_col2, 1, 0)) as num_def_col2,
SUM(IF(col3 = default_val_col3, 1, 0)) as num_def_col3,
...
...
SUM(IF(coln = default_val_coln, 1, 0)) as num_def_coln
FROM myTable;

Das ist zwar schön und der Ansatz ist sicher für bestimmte andere Auswertungen auch von grundsätzlichem Interesse (s. etwa: http://www.randomsnippets.com/2008/10/05/how-to-count-values-with-mysql-queries/). So kann man ähnliche Statements z.B. einsetzen, wenn man herausfinden will, wie oft vorgegebene, unterschiedliche Werte in genau einer Spalte vorkommen.

Aber ist das in unserem Fall vom Zeitbedarf her wirklich effizienter als der erste Ansatz? Die Antwort lautet: Nur geringfügig.

Die folgenden zwei Abbildungen zeigen typische Werte für SQL-Typ 1 und SQL-Typ2 auf einem einfachen Testserver für eine Tabelle mit ca. 10 Millionen Einträgen und 6 untersuchten Wertespalten:

Messung für Statement vom SQL-Typ1 für eine Spalte

sqlff1

Messung für Statement vom SQL-Typ2 für 6 (!) Spalten

sqlff2

Wir kommen (nach Multiplikation mit der Anzahl der Spalten) auf etwa 6 sec für Typ1 und 5.3 sec Gesamtlaufzeit für Typ2. Der Unterschied ist also nicht weltbewegend. (Hinweis: Ein zusätzlich durchzuführendes Count(*) kostet praktisch keine Zeit; wir haben das hier deshalb weggelassen.)

Warum erhalten wir solch ein Ergebnis?
Nun die Anzahl der zu treffenden Entscheidungen ist praktisch identisch und gezählt wird in beiden Fällen. Demgegenüber tritt das mehrfache Durchlaufen der Tabelle bei Typ 1 zum systematischen Bereitstellen der Daten einer Zeile in den Hintergrund. Im besonderen, wenn die Tabelle schon im Cache vorliegen sollte.

Noch ineffizienter wird ein Ansatz, bei dem mit COUNT(Distinct) gearbeitet würde und bei den Spalten die nur einen Wert aufweisen, diesen Wert zusätzlich ermittelt würde :

Allein das Statement für unsere Testtabelle

SELECT COUNT( DISTINCT sssim ) AS dist_sssim, COUNT( DISTINCT mult ) AS dist_mult, COUNT( DISTINCT dobs ) AS dist_dobs, COUNT( DISTINCT sigmad ) AS dist_sigmad, COUNT( DISTINCT sigmalt ) AS dist_sigmalt, COUNT( DISTINCT fexp ) AS dist_fexp
FROM `alien_ss_comb_itm`

dauert hier 7.6 sec. Dies liegt daran, dass hier temporäre Datenstrukturen bzw. Tabellen erzeugt werden.

Die langsamste Abfrage-Alternative

Es gibt natürlich eine weitere Alternative zum Typ 1 - Statement:

SQL-Typ3:
SELECT colx as dist_colx, COUNT(colx) as num FROM myTable GROUP BY colx;

Die Anzahl der Treffer, der Ausgabewert "num" und der ggf. gefundene alleinige Wert können auch hier dafür verwendet werden, um festzustellen, ob die Spalte nur mit Default-Werten gefüllt ist.
Für dieses Statement dauert in unserer Testtabelle die Abfrage über eine einzige Spalte allerdings bereits 2.5 sec:

sqlff3

Auch hier ist das Anlegen von temporären Datenstrukturen und der erforderliche Einsatz von Filesort in der MySQL-DB die Ursache der schlechten Performance.

Zu kostspielig? Alternativen?

Offenbar ist so eine Prüfung für große Tabellen relativ kostspielig. Mehrere Sekunden, um festzustellen, welche Spalten mit regulären Daten gefüllt wurden? Geht gar nicht ...

Zugegeben: Das hängt immer davon ab, in welchen Zusammenhang - also Art von Upload- oder Serverlauf - man eine solche Prüfung einbettet. Dauert ein Simulationslauf für große Tabellen 15 Minuten, so sind 5 sec egal. Auch bei Uploadzeiten von 50 sec kann man 10% Zuwachs verschmerzen. Auch zugegeben: Man ist von keiner weiteren Information außer dem aktuellen Tabellenzustand selbst abhängig.

Würde das Anlegen eines Index pro Spalte helfen?
Natürlich würde das SQL-Statements vom Typ 1 extrem beschleunigen! Aber kann man sich Indices pro Spalte denn bei großen Tabellen überhaupt leisten? Wohl eher nicht: Indices kosten u.U. und je nach Kardinalität enorm viel Platz und ihr Anlegen erhöht den Bedarf an CPU-Zeit im Rahmen eines Dateiimports beträchtlich. Wenn man also Indices über einzelne Daten-Spalten nicht noch für andere Zwecke benötigt, sollte man deshalb eher die Finger davon lassen.

Beschleunigung von erforderlichen Abfragen auf den Füllgrad von Spalten mit Defaultwerten über eine Hilfstabelle

Muss man die vorgesehene Prüfung überhaupt über SQL-Abfragen auf den Spalten der zu untersuchenden Tabelle machen?

In unserem Fall, in dem es nur darum geht, ob in einer Spalte aussschließlich Default-Werte vorliegen, natürlich nicht! Der einzig sinnvolle Weg führt mal wieder über eine Hilfstabelle, die bereits auf Grund einer Analyse des Aufbaus der importierten CSV-Datei erstellt wird.

Im Fall der Verwendung von "LOAD DATA INFILE" genügt dabei ein Check der ggf. vorhandenen Header-Zeile mit Spaltenbezeichnungen und der ersten Datenzeile; ein solcher Check ist sowieso obligatorisch, um das "LOAD DATA INFILE" Statement um explizite Angaben zum Füllen der Spalten zu ergänzen, für die die CSV-Datei keine Daten liefert. Ein so ergänztes "LOAD DATA INFILE"-Statement ist viel schneller als das Füllen mit Defaultwerte der Bank selbst zu überlassen. Einen Check der Korrektheit der Struktur der übergebenen Daten benötigt man im übrigen sowieso.

Lösung: Auf Basis der CSV-Datei-Analyse füllt man nach einem erfolgreichen Laden der CSV-Daten in die Ziel-Tabelle zusätzlich eine kleine Hilfstabelle, in der für jede Tabelle und Spalte Werte eingetragen werden, die besagen, wie oft der Default-Wert vorliegt und ob er ausschließlich vorliegt.

Alle nachfolgenden Prüfungen greifen dann auf den Inhalt dieser Hilfstabelle zu. Das ist selbst bei einem zusätzlichen Vergleich von Zeitstempeln zwischen Hilfs-Tabelle und Original-Tabelle um Größenordnungen schneller als ein Statement vom Typ 2 - nicht nur bei großen Tabellen.

Sollte die Hilfstabelle aus irgendeinem Grunde mal nicht gefüllt sein, oder die Zeitstempel nicht passen, kann man ja in dem Programm, das die Belegung von Spalten mit Defaultwerten prüfen will, immer noch auf das Statement vom Typ 2 als Fallback zurückgreifen. Aber das ist maximal nur genau einmal erforderlich!

Genau so haben wir es dann in unserem Projekt auch gemacht!

Es liegt im übrigen auf der Hand, wie man mit ein wenig Parametrierung eine allgemeinere PHP-Funktion erstellt, die ermittelt, wie oft spaltenspezifische, vorgegebene Werte in einer importierten Datentabelle auftauchen. So ist die (dynamische) Erzeugung von SQL-Statements vom Typ 2 für Tabellenspalten ohne Index also für bestimmte Auswertungen ggf. doch von Nutzen.

MySQL – SELECT INTO OUTPUT FILE – vermeide zusätzliche UNION Statements!

Wenn es um den Export relativ großer Tabellen in CSV-Dateien geht, greift man bei MySQL-Datenbanken auf eine spezielle Form des SELECT Statements, nämlich "SELECT INTO OUTPUT FILE ... ". Ich nenne das nachfolgend der Einfachheit halber ein "Export SELECT". Siehe hierzu:
http://dev.mysql.com/doc/refman/5.7/en/select-into.html

Oftmals benötigt man aber nicht nur die reinen Daten sondern eine CSV-Datei mit einer führenden Zeile, in der die Bezeichnungen der einzelnen Felder/Spalten auftauchen ("column heading"). Die gängige Empfehlung vieler Beiträge zu diesem Thema ist, das Ergebnis des oben genannten Statements "SELECT INTO OUTPUT FILE ... " per UNION mit einem "künstlichen SELECT-Statement zu verbinden, dass genau die Strings der ersten Zeile erzeugt. Siehe hierzu etwa
http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format (und dort einen entsprechenden Kommentar zum Lösungsansatz der Forums-Frage)
oder aber
http://www.mysqltutorial.org/mysql-export-table-to-csv/
und dort der Abschnitt zu "Export Data with Column Headings".

Entsprechende Vorschläge folgen dem Muster A

(SELECT 'Name of Column 1', 'Name of Column 2', 'Name of Column 3')
UNION
(SELECT column_name_1, column_name_2, column_name_3
FROM TABLE_NAME
INTO OUTFILE 'absolute_path_to_csv_file'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n');

Festzustellen ist hierzu Folgendes:

Ein solches Statement funktioniert zwar und produziert das gewünschte Ergebnis; aber es reduziert die Performance des Daten-Exports in eine Datei drastisch - speziell wenn man mit großen Tabellen und mehreren Millionenen oder gar Hunderten von Millionen Records arbeitet. Dann entfaltet sich das UNION-Statement zu einem echten Performance-Killer!

In einem konkreten Testbeispiel konnte ich den Export aus einer Datei mit einer Million Records vergleichen. Der Export mit einem Union Statement dauerte fast 50 Sekunden; läßt man die Kopfzeile dagegen nach dem Muster B

SELECT column_name_1, column_name_2, column_name_3
FROM TABLE_NAME
INTO OUTFILE 'absolute_path_to_csv_file'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

weg, so dauerte der Export nur 1,8 Sekunden. Das Verhältnis wird noch schlimmer bei größeren Record-Zahlen der Tabelle.

Ein wenig tieferes Nachdenken und eine SQL-Analyse mit "Explain" machen auch klar, woran das liegt: Der Einsatz von UNIONS erfordert das Zusammenfügen der Resultsets der verschiedenen SELECTs. Dazu muss jedes Resultset aber erstmal generiert werden. Dazu müssen temporäre Datenhaltungsstrukturen aufgebaut werden. Dies kollidiert jedoch mit der ansonsten speziell optimierten Funktionalität des "SELECT INTO OUTPUT FILE"-Statements, nämlich die Tabelleninhalte aus den Datenbankfiles mit speziellen Methoden direkt auszulesen und sofort in eine Datei zu schreiben.

Ich kann daher nur jedem raten, beim Export wirklich großer Tabellen in CSV-Dateien auf jegliche Kombination des "SELECT INTO OUTPUT FILE"-Statements mit anderen SELECTS unter Rückgriff auf "UNION" zu verzichten. Das zieht ggf. den Verzicht auf eine erste führende Zeile nach sich.

Änderung und Nachtrag 08.01.2015:
In Fällen mit kleineren Dateien von bis zu n x 100 MByte kann es - je nach RAM-Ausstattung des Systems - u.U. besser sein, zunächst den reinen Daten-Export (gem. Muster B) durchzuführen und danach die erzeugte Datei mit PHP File-Funktionen oder Systemfunktionen nachzueditieren und die gewünschte erste Zeile mit der Feldinformation hinzuzufügen. Das ist aber zu testen. In meinem Testfall mit 1 Million Datensätzen war das File kleiner als 50 MByte und lag auf einer SSD. Zudem ist das System hinsichtlich der Plattenzugriffe gut gepuffert. In diesem Fall erhöhte sich die benötigte Zeit durch das Hinzufügen der Kopfzeile untr PHP kaum spür- und messbar über die 1,8 Sekunden hinaus. Die Änderung war gegenüber dem oben erwähnten Zeitunterschied des UNION-Statements völlig vernachlässigbar.

Bei Dateigrößen von mehreren hundert Mbyte oder gar im GByte-Bereich wird ein nachträgliches Editieren unter PHP aber ebenfalls zu Problemen führen, da das File erst einmal zu lesen und dann mit dem String für die erste Zeil zu konkatenieren ist. Man muss in Tests prüfen,

  • ob das für solche Operationen erlaubte Memory unter PHP hinreichend ist (und es ggf. anpassen)
  • und ob sich die Lesezeit unter PHP als kleiner oder größer als die von MySQL aufgebrachte Zeit für ein UNION herausstellt.

Am besten erscheint es mir, bei wirklich großen Datenmengen im Sinne der Performance schlicht und einfach auf die führende Kopfzeile zu verzichten. Derjenige, der das exportierte CSV-File in irgendeiner Weise nutzen will, muss eben über die Feldreihenfolge Bescheid wissen.