Hier im Forum bekommt ihr bei euren fragen schnelle hilfe.Hier geht es rund um das Web SeitenProgrammieren.Alles rund ums Javascript,Html,Php,Css und Sql.Auf fast allen Fragen haben wir eine Antwort.
Der Soforthilfe-chat verspricht das ,was sein Name sagt. Hier sind Leute Online die sofort ihre hilfe anbieten.Seht in der OnlineListe nach und wenn einer Online ist werdet ihr auch antwort bekommen. Admine ,Moderatoren und Helfer sind unsere Spezialisten in Sachen Web Programierung

Sie sind hier : sebastian1012.bplaced.net/ homepage-neu / kreuz-und-quer / tutorials-info-neuigkeiten-datenbank / index.php

Random Datenbank Themen, Tutorials und co

24.09.2019 21:23:46

PHP Performance Forum eröffnet

Heute gibts mal keinen neuen Tipp, wie man die Performance der eigenen Scripte, Einstellungen oder Abfragen beschleunigen kann. Dafür aber eine tolle Möglichkeit über das Thema Performance-Optimierung zu diskutieren.
Wer Interesse hat, sich mit Gleichgesinnten über die Optimierung von PHP, Servern und Datenbanken zu unterhalten, sollte mal in das PHP Performance Forum schauen. Hoffe, dass damit eine Plattform zum Diskutieren und Kommentieren sowie Vorschlagen und Kritisieren geschaffen ist. Viel Spaß damit!

datenbank

24.09.2019 21:23:26

Auslesen der zuletzt eingefügten ID (auto increment)

Recht häufig benötigt man nach einer INSERT-Operation auf eine Tabelle mit einer automatisch inkrementierten ID als Primärschlüssel den durch diese Operation erzeugten Auto-Increment-Wert. Beispielsweise dafür, um anschließend weitere Operationen mit diesem Datensatz vorzunehmen oder die ID in anderen Tabellen als Fremdschlüssel zu verwenden. Zwei unterschiedliche Wege diese ID herauszufinden sollen in diesem Beitrag verglichen werden.

Denis hat vor einiger Zeit in den Themenvorschlägen vorgeschlagen, dieses Thema einmal näher zu untersuchen und dabei folgende zwei Wege vorgeschlagen.

1. Per SQL-Abfrage
Hierbei wird nach der INSERT-Query noch eine weitere Query an MySQL abgesendet, die einen Datensatz zurückgibt, der allein die ID enthält:

mysql_query("INSERT INTO tabelle (spalte1,spalte2,...) VALUES ('wert1','wert2',...)"); $rs = mysql_query("SELECT LAST_INSERT_ID()"); $id = mysql_result($rs,0);

2. Per PHP
Eine andere Möglichkeit ist die Abfrage durch PHP erledigen zu lassen. Dies geschieht über den Befehl mysql_insert_id().

mysql_query("INSERT INTO tabelle (spalte1,spalte2,...) VALUES ('wert1','wert2',...)"); $id = mysql_insert_id();

Grundsätzlich erzeugen beide Varianten also gleiche Lösungen. Es fällt aber auf, dass man im Fall 1 zusätzlich noch das Result Set auslesen muss, um an den Wert zu gelangen.
Die Ergebnisse sehen aber folgendermaßen aus:

Datei Gesamtlaufzeit durchschnittliche Laufzeit pro Durchlauf Verhältnis zur schnellsten Variante
result_mysql_insert_id.php 6.168871 s 6.169 ms 100 %
result_last_insert_id.php 6.178885 s 6.179 ms 100 %

Es ist zu erkennen, dass beide Varianten gleich schnell sind. Hinsichtlich der Performance gibt es also keine Unterschiede.
Die Gründe kann ich nicht 100%ig verifizieren, jedoch könnte ich mir vorstellen, dass mysql_insert_id() intern exakt die gleiche SQL-Abfrage ausführt und letzten Endes bei beiden Wegen exakt dasselbe getan wird.
Ich werde weiterhin mysql_insert_id() bevorzugen, weil man dafür einfach eine Zeile weniger benötigt, aber das sei jedem selbst überlassen. Wer nach Lines of Code bezahlt wird, sollte natürlich LAST_INSERT_ID() abfragen ????

Die verwendeten Scripte und die Ergebnisse gibts natürlich zum Download.

Das war also mal ein Beitrag, der euch nicht dazu bringen wird, gleich alle Scripte zu überarbeiten. Ich hoffe aber trotzdem, dass er informativ ist und ihr euch zukünftig nicht mehr den Kopf zerbrechen müsst. ????

datenbank

24.09.2019 21:23:45

Performanter Zufall – gibts das?

Ich möchte das Problem des zufälligen Selektierens von Datensätzen nochmal betrachten, da die im Beitrag Zufälligen Datensatz auswählen gefundene Lösung nicht wirklich akzeptabel ist, da sie nicht zufällig ist. Bei aller Performance, die diese Abfrage bringt, sollte doch die ursprüngliche Funktionalität nicht verloren gehen: Die Datensätze müssen zufällig sein und keinem Schema folgen.

In der Foren-Diskussion zu diesem Thema wurde von langleY ein weiterer Ansatz geliefert, der zwar mehr Speicher benötigt, aber letztlich zu meiner Empfehlung hinführt. Seinen Vorschlag in etwas abgewandelter Form möchte ich hier kurz erörtern:

Wir fügen der Tabelle, aus der die zufälligen Datensätze selektiert werden sollen, eine zusätzliche Spalte hinzu, in die wir Zufallszahlen schreiben. Da Zufallszahlen der Funktion RAND() in MySQL Dezimalzahlen zwischen 0 und 1 sind, können wir als Datentyp UNSIGNED FLOAT wählen. Außerdem legen wir fest, dass die Spalte NOT NULL ist. Ich habe der Spalte den Namen rand_value gegeben – dies nur, damit die SQL-Anweisungen weiter unten verständlich sind.

Über die Anweisung

UPDATE tabelle SET rand_value=RAND()

schreiben wir willkürlich Zufallszahlen in die Tabellenspalte – für jeden Datensatz wird eine generiert. Das ganze dauert bei meiner Tabelle mit etwa 250000 Einträgen etwa 3 Sekunden – das ist die Zeit, die bei einem SELECT * FROM tabelle ORDER BY RAND() ebenfalls benötigt würde – nur eben bei jeder Abfrage und nicht nur ein mal, wie mit der Zusatzspalte.

Nun war langleYs Vorschlag, dass man in einem SELECT-Statement noch eine Zufallszahl per RAND() erzeugt und etwa 10 mal so viele Werte selektiert, wie man im Endeffekt haben möchte. Diese Werte ordnet man dann wieder in einer zufälligen Reihenfolge an und wählt daraus 10 Datensätze aus. Als SQL:

SELECT *  FROM (    SELECT *    FROM tabelle    WHERE rand_value >= RAND()    LIMIT 100  )t  ORDER BY RAND()  LIMIT 10

Und das funktioniert auf den ersten Blick auch recht gut. Wenn wir aber genauer hinsehen, bleibt der Zufall auf der Strecke.
Das Problem ist vor allem die WHERE-Bedingung. Da die RAND()-Funktion auf lange Sicht gleichverteilte Werte ermittelt (jeder mögliche Wert kommt ungefähr genauso oft vor wie alle anderen), passiert es also genauso oft, dass wir kleinere als auch größere Zufallszahlen generieren. Wir gehen ebenfalls davon aus, dass die Zufallszahlen in der Spalte rand_value über alle Datensätze ebenfalls etwa gleichverteilt sind.
Nun gehen wir von einem Beispiel aus: Als Zufallszahl im SELECT-Statement wird 0,3 ermittelt. Das bedeutet, dass etwa 70 % der Datensätze größere Zufallszahlen in der Spalte rand_value haben und ca 30% kleinere Werte. Wenn wir nun aber in der WHERE-Bedingung alle Datensätze wählen, die größer (bzw. gleich) dieser Zufallszahl sind und keine zusätzliche Sortierung vornehmen sondern einfach die ersten 100 Werte nehmen, die in der Datenbank stehen, führt das dazu, dass sehr viele Datensätze vom Anfang der Tabelle selektiert würden und sehr wenige vom Ende. Je kleiner die Zufallszahl des SELECT-Statements ist, desto größer ist die Wahrscheinlichkeit, dass einfach die ersten 100 Werte genommen werden, die in der Tabelle stehen. Und man stelle sich vor, dass einer der ersten Datensätze der Tabelle eine sehr hohe Zufallszahl bekommt. Dann ist er fortan in den meisten Fällen unter den von der Subquery selektierten Datensätzen.

Der andere Schwachpunkt der Lösung ist eine sehr hohe Zufallszahl, beispielsweise 0,98. Es kann niemand garantieren, dass es tatsächlich noch 10 Datensätze gibt, deren Zufallszahlen in der Spalte rand_value über diesem Wert liegen. Und in vielen Anwendungsfällen möchte man ja beim Einsatz von LIMIT exakt die angegebene Anzahl von Datensätzen zurückerhalten, nur selten weniger.

Also hab ich mir mal etwas Zeit genommen und an einer besseren Lösung gefeilt. Ich bin zu dem Schluss gekommen, dass man an der Lösung über die Extra-Spalte nicht vorbeikommt, da sonst in der Praxis per RAND()*MAX(ID) keine gleichverteilten Werte zustandekämen. Warum nicht, in der Theorie funktioniert das doch? Korrekt. (Wen die stochastischen Zusammenhänge nicht interessieren, dem gestatte ich den folgenden Abschnitt zu überspringen ???? )
Theoretisch ist gegen den Ansatz nix einzuwenden, allerdings ist die Spalte ID oft eine AUTO-INCREMENT-Spalte und wenn man Datensätze löscht, entstehen Lücken (auch als Löcher bezeichnet). Das ist nicht weiter wild, allerdings muss man sich nur mal eine Tabelle mit 2 Datensätzen vorstellen: Der erste hat die ID 1 und der zweite die ID 6 (die 4 Datensätze dazwischen wurden irgendwann mal gelöscht). Der Erwartungswert der RAND()-Funktion ist 0,5 (ist wie beim Münzwurf, nur diesmal mit einer stetigen Zufallsgröße, sorry hier schon mal für die vielen Mathe-Fachbegriffe). Der Erwartungswert von randID = RAND()*MAX(ID) ist somit 0,5*6 = 3. Wenn wir nun eine WHERE-Bedingung einsetzen WHERE ID>=randID (von mir aus auch kleiner-gleich, das macht keinen Unterschied), müsste eigentlich an der 3 gesplittet werden. Alles was kleiner 3 ist, müsste zu Datensatz 1 führen, alles, was größer ist zur 6. Doch das ist nicht der Fall. Auch wenn RAND()*MAX(ID) 1,1 ergibt, wird Datensatz 6 selektiert. Man kann durch die Ungleichung 1>=RAND()*6 einfach ermitteln, dass nur bei Zufallszahlen, die kleiner bzw. gleich 1/6 sind, Datensatz 1 selektiert würde. Sicherlich ist das ein extremes Beispiel, aber es verdeutlicht hoffentlich, warum diese Version nichts mit Zufall zu tun hat, wenn Löcher vorhanden sind (was in der Praxis wahrscheinlich in den meisten Tabellen der Fall ist).

So, jetzt weiter für Datenbankleute (ok, die Mathematiker dürfen auch dabei bleiben). Wir haben oben bereits festgestellt, dass die Funktion RAND() gleichverteilt ist – sie weist also keine Löcher auf. Deshalb müssen wir allein mit ihr arbeiten und können uns nicht auf die Spalte ID beziehen. Ebenfalls ist die Arbeit mit der WHERE-Klausel gefährlich, da es damit immer passieren kann, dass zu wenig Datensätze übrig bleiben, die der Bedingung entsprechen.
Mir ist nun eine mögliche Lösung eingefallen: Wir arbeiten mit ORDER BY. Back to the roots sozusagen, aber statt ORDER BY RAND() gehen wir einen kleinen Umweg:

SELECT artikelname  FROM tabelle  JOIN (   SELECT RAND() AS random    FROM tabelle    LIMIT 1 ) AS randTable ORDER BY ABS(rand_value-random) LIMIT 10;

Wir ermitteln nur eine einzige Zufallszahl in der SELECT-Abfrage und joinen diese auf alle Datensätze aus der Tabelle tabelle. Anschließend sortieren wir nach dem Abstand der in der Tabellenspalte rand_value gespeicherten Zufallszahlen zu der soeben berechneten Zufallszahl. Da die Zufallszahlen in rand_value über alle Datensätze gleichverteilt sowie unabhängig von der Reihenfolge der Datensätze selbst sind, und die Funktion RAND() ebenfalls gleichverteilt ist, erhalten wir auch wirklich zufällig gewählte Datensätze.

Mit der Query bekomme ich nach 0,65 Sekunden 10 zufällige Datensätze zurück. Ich denke diese Lösung ist durchaus eine gute Sache, zumal man durch den Einsatz eines Indizes die Geschwindigkeit noch weiter steigern könnte.
Nachteil dieser Lösung ist, dass alle INSERTs in der Anwendung angepasst werden müssten, da die zusätzlche Spalte rand_value initial mit einem Zufallswert gefüllt werden muss. Eine andere Möglichkeit das zu realisieren wäre ein Trigger (ab MySQL 5 möglich).

CREATE TRIGGER rand_trigger AFTER INSERT ON tabelle FOR EACH ROW BEGIN   UPDATE tabelle    SET rand_value=RAND()    WHERE ID=NEW.ID; END;

Ich hoffe es war nicht zu mathematisch, aber der Zufall ist nunmal ein mathematischer Sachverhalt. Falls Dinge nicht ausreichend erklärt wurden, bitte ich um ein kurzes Feedback in den Kommentaren! Vielen Dank, Vorlesung beendet ????

datenbank

24.09.2019 21:23:40

Was tut MySQL selbst für die Performance von SELECT-Abfragen?

WHERE-Klauseln sind eigentlich die einfachste Sache, wenn man mit SQL-Abfragen arbeitet. Doch viele Abfragen lassen sich noch weiter vereinfachen.
Da der Einsatz der WHERE-Bedingungen entscheidend für den daraus folgenden Einsatz von Indizes und somit der Performance ist, verarbeitet MySQL nach dem Absenden einer Abfrage diese und verändert sie, ohne die Logik zu beeinflussen. Dafür wird die Abfrage aber performanter. Ich möchte heute mal aufzeigen, was MySQL im Hintergrund so alles anstellt, damit Abfragen möglichst schnell behandelt werden.

MySQL führt Optimierungen durch den so genannten Query Optimizer durch. Zuerst ist da das Entfernen unnötiger Klammern, die man ja gern mal setzt, weil man die logischen Einheiten verdeutlichen will oder auch nicht hundertprozentig parat hat, ob OR oder AND Priorität hat.
Anschließend kommt das sogenannte Folding (Konstanten-Faltung). Darunter versteht man das Einsetzen von Konstanten, wenn sich diese auf die gleiche Spalte innerhalb der Query mehrmals beziehen. Aus dem Folding folgt das Entfernen von unnötigen Bedingungen.
Hier mal am Beispiel diese 3 Sachen gezeigt:

SELECT *  FROM tabelle WHERE ((a AND b) AND c OR (((a AND b) AND (c AND d))))   // unnötige Klammern entfernt SELECT *  FROM tabelle WHERE a AND b AND c OR a AND b AND c AND d   ... WHERE (a<b AND b=c) AND a=5   // Konstanten-Folding ... WHERE b>5 AND b=c AND a=5     ... WHERE (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)   // Bedingungsentfernung WHERE B=5 OR B=6

Außerdem werden mehrmals verwendete Konstanten-Ausdrücke in einer Query nur einmal ausgewertet.
Wenn COUNT(*) ohne Bedingung aufgerufen wird, kann MySQL gleich auf die Tabelleninformationen zugreifen und somit sehr schnell ein Ergebnis zurückliefern.
Früherkennung ungültiger Konstanten: Manche Werte können einfach nicht vorkommen, z.B. negative Werte in einer UNSIGNED-Spalte, NULL-Werte in einer NOT-NULL-Spalte oder Werte, die außerhalb des Definitionsbereichs liegen (zu große Zahlen, zu lange Strings). MySQL erkennt das sehr schnell und liefert ein leeres Ergebnis zurück ohne ewig andere Aufgaben der Query auszuführen, nur um irgendwann mal festzustellen, dass das ja so gar nicht geht.

HAVING und WHERE werden vereinigt, wenn keine GROUP-Funktion genutzt wird.
Alle Konstanten-Tabellen werden zuerst gelesen, vor jeder anderen Tabelle in der Anfrage, wobei eine Konstantentabelle eine Tabelle mit 0 oder einer Zeile sein kann (recht selten) oder eine Tabelle, die bei einer WHERE-Klausel (oder auch bei einer JOIN-Klausel) auf einen UNIQUE- oder PRIMARY-Index abgefragt wird (häufiger).

Die beste Join-Kombination, um Tabellen zu verknüpfen, wird gefunden, wenn man alle Möglichkeiten probiert. Wenn alle Spalten in ORDER BY und in GROUP BY aus derselben Tabelle stammen, wird diese Tabelle vorzugsweise vorn hingestellt, wenn verknüpft wird.
Jeder Tabellen-Index wird abgefragt und der beste Index, der weniger als 30% der Zeilen überspannt, wird benutzt. Wenn ein solcher Index nicht gefunden werden kann, ist einTabellenscan (Full-Table-Scan) schneller und wird deshalb durchgeführt (deshalb immer per EXPLAIN überprüfen, ob Indizes genutzt werden).
In einigen Fällen kann MySQL Zeilen vom Index lesen, ohne überhaupt in der Daten-Datei nachzuschlagen. Wenn alle Spalten, die vom Index benutzt werden, numerisch sind, wird nur der Index-Baum benutzt, um die Anfrage aufzulösen.

Beim Einsatz von DISTINCT werden alle zu selektierenden Spalten in die GROUP BY-Menge hinzugefügt. Wird DISTINCT mit ORDER BY benutzt, ist oft eine temporäre Tabelle nötig.
Wird DISTINCT mit LIMIT verwendet, stoppt MySQL die Ausführung, sobald es die in LIMIT angegebene Zahl von eindeutigen Datensätzen gefunden hat.
Wenn Sie nicht Spalten aus allen benutzten Tabellen verwenden, hält MySQL mit dem Scannen der nicht benutzten Tabellen an, sobald er die erste Übereinstimmung gefunden hat.

SELECT DISTINCT t1.a FROM t1,t2 WHERE t1.a=t2.a;

Im Beispiel wird angenommen, dass t1 vor t2 benutzt wird (überprüfen Sie das mit EXPLAIN). In diesem Fall hört MySQL auf, von t2 zu lesen (für diese bestimmte Zeile in t1), sobald die erste Zeile in t2 gefunden wurde.

Ich hoffe ich konnte etwas Licht ins Dunkel bringen. MySQL führt nämlich keineswegs genau das aus, was sie ihm vorwerfen. Query Optimizing ist das A und O im Kampf um Performance der unterschiedlichen Datenbank-Systeme.

datenbank

24.09.2019 06:37:24

SQL_CALC_FOUND_ROWS führt zu unerklärlichen Abstürzen

Heute mal ein Beitrag über ein Thema, das mir wochen- wenn nicht sogar monatelang Kopfzerbrechen bereitet hat. Und zwar fiel mein Server immer mal aus unerklärlichen Gründen aus. Die Prozesse hingen sich auf und Feierabend. Natürlich gab es keine Reproduzierbarkeit des Fehlers – teilweise lief der Server auch mehrere Tage in Folge problemlos und dann krachte es wieder. Wie immer ohne Einträge in Logs oder sonstwo. Nun endlich habe ich das Problem gefunden…

Natürlich war der Fehler hausgemacht. Doch wollte ich es nicht wahrhaben, wo ich doch jedes Script nochmal untersucht habe, mich durch eingesetzte Fremdscripte gewühlt habe, die Datenbanken penibel überprüft habe.
Kurz gesagt: Es lag an einer hier im Blog empfohlenen SQL-Anweisung: SQL_CALC_FOUND_ROWS.

Diese Anweisung ist ja eigentlich ganz nützlich, erspart sie doch eine zusätzliche Abfrage für die Gesamtanzahl an Datensätzen, wenn man das Gesamtergebnis auf mehrere Seiten aufteilt. Schon damals kamen in den Kommentaren Stimmen gegen diese Maßnahme auf.
Marcel meinte Bezug nehmend auf einen Blogbeitrag vom MySQL Performance Blog:

Bei so einfachen Tabellen die auch noch Keys auf jeder Spalte haben, wie bei mysqlperformance ist Count natürlich schneller – hier geht es aber definitiv um komplexere Queries als bei mysqlperforrrrrmance gezeigt.

Damit hat er Recht. Ich habe es ja nicht umsonst geschrieben. Bei komplexen Abfragen wie Suchen mit MATCH oder LIKE-Funktion, GROUPing usw. spart SQL_CALC_FOUND_ROWS schon Zeit. Die eine Abfrage dauert zwar minimal länger, aber man spart sich eben eine zusätzliche Abfrage, die nur das Zählen der Gesamtzahl der Datensätze übernimmt.

Alex wies dann daraufhin, dass tatsächlich durch SQL_CALC_FOUND_ROWS ein Mehraufwand entsteht (weil ich ursprünglich geschrieben habe, dass kein Mehraufwand entstehen würde). Außerdem beschrieb er, dass es durchaus Abfragen gibt, die mit 2 Abfragen schneller sind als mit einer SQL_CALC_ROWS-Abfrage. Das stimmt natürlich (siehe oben).

Um zurück zu den Abstürzen zu kommen: Ich suchte also das gesamte Netz durch, weil man ja eigentlich nie der erste ist, der ein bestimmtes Problem hat. Irgendwann hab ich dann vor lauter Verzweiflung mal auf meinen eigenen Blogbeitrag geklickt – eben jenen zu SQL_CALC_FOUND_ROWS.
Und da lese ich mir die Kommentare durch und stoße auf den von mydeams:


In dem Projekt wo ich diese Methode genutzt habe, werden sehr viele MySQL Abfrage erstellt. ca. 1000 besucher hat die Seite am Tag. Mir/Uns ist aufgefallen, dass die Seite bzw. die MySQL DB sich nach na Zeit aufgehangen hat. Wegen dieses befehls. Habe ich das in der alten konventionellen Art gemacht mit nem entsprechendem Count … traten die Abstürze nimmer auf.

In der Prozessorliste von PHPMyAdmin hing er sich immer im Prozess “copy to tmp table” auf. Ich muss dazu aber auch sagen, dass die MySQL abfrage recht mächtig war, mit LIKE, LEFT JOIN etc. pp.

Ich griff also damit nach dem letzten Strohhalm und ersetzte alle SQL_CALC_FOUND_ROWS-Befehle durch die traditionelle 2-Abfragen-Methode mit COUNT().
Und das unglaubliche trat ein: Wie von mydeams prophezeit hatten die Abstürze dadurch tatsächlich eine Ende. Ein ganz großes Dankeschön an dieser Stelle! Deshalb freue ich ich über jeden Kommentar – er kann mal noch seeehr nützlich werden!

Ergo ziehe ich meine Empfehlung von SQL_CALC_FOUND_ROWS wieder zurück. Ob es an meiner Konfiguration lag, ob MySQL einen Bug hat, ich weiß es nicht. Ich weiß aber, dass ich in Zukunft einen großen Bogen um diese Funktion mache, denn es ist mir jetzt auch egal, ob der Seitenaufbau bei der Suche nun 0,2 Sekunden länger dauert, denn Performanceoptimierung darf natürlich nie auf Kosten der Stabilität gehen.

datenbank