Guten Abend, ich habe eine InnoDB-Datenbank, in der ich per Abfrage diverse Tabellen joine und per Datumsvergleich nach freien Terminen suche. Da ich nach freien Zeitfenstern in festgelegten Intervallen suche, nutze ich eine Hilfstabelle mit allen Intervallen pro Tag (00:00:00, 00:05:00, ..., 23:55:00) und suche dann für einen gewissen Zeitraum für jeden Tag die entsprechenden Zeitfenster. Die Such-Performance ist nicht überragend, daher habe ich versucht diese durch das Anlegen von Indizes zu verbessern. Die Suche ist dadurch auch schon signifikant schneller geworden, aber ich frage mich, in welchem Maße Indizes normalerweise verwendet werden? Ich habe z.B. für die Datums- und Uhrzeitfelder Indizes erstellt, aber auch für die Intervalle. Was ist nun aber mit den zahlreichen foreign keys, die in vielen Tabellen vorhanden sind? Jeder Termin ist z.B. einem Kursleiter und einem Ort zugeordnet, die in der Suche dann unter anderem als Filter-Kriterium verwendet werden. Setzt man auf kursleiter_id und ort_id in allen beteiligten Tabellen ebenfalls Indizes? Und gehen Indizes primär zu Lasten des Speichers, vergrößern also lediglich den Platzbedarf der DB, oder können diese sogar die Performance verringern? Viele Grüße Ron
Ron schrieb: > Und gehen Indizes primär zu Lasten des Speichers, vergrößern also > lediglich den Platzbedarf der DB, oder können diese sogar die > Performance verringern? ja, beim einfügen und ändern von Daten. Im Idealfall liegt für jeden Suchanfrage ein passenden Index. Dabei ist es aber nicht immer Hilfreich einfach nur jeden Spalte zu Indizieren, teilweise braucht man auch einen Index über mehrere Spalten - da ist dann aber die Reihenfolge wichtig. Pausschal kann man da kaum etwas sagen, dafür müsste man die Tabellen sehen und die abfragen.
Peter II schrieb: > ... und die abfragen. Ganz wichtiger Punkt!! Blind Indizes anzulegen ist meist kontraproduktiv!
Ein Index, der in keinem Query verwendet wird, der bremst. Nicht bei der Abfrage, dafür aber aufgrund erhöhten Aufwands bei Insert/Update/Delete Operationen. Ob ein Index verwendet wird, also Nutzen bringt, hängt vom Query ab. Database Engines optimieren die Arbeitsweise von Queries anhand diverser Parameter, wie beispielsweise der Anzahl Rows in den angesprochenen Tabellen. Databanksysteme bieten oft Werkzeuge, mit der die tatsächlich umgesetzte Arbeitsweise beschrieben wird. Man sollte berücksichtigen, dass eine Test-Tabelle mit 200 Rows nicht unbedingt zum gleichen Ergebnis führt wie die Produktionsversion mit 100 Mio (1). Der tiefere Sinn von Foreign Keys liegt in der Wahrung der Integrität der Datenbank. Damit lässt sich ggf. sicherstellen, dass man eine Row nicht löschen kann, so lange noch darauf verwiesen wird. Andernfalls hat man irgendwann IDs, die ins Nirvana verweisen. (2) (1) Standardfehler von kleinen Entwicklungsbüros. Anwender klagt "zu langsam", Entwickler sagt, "zu wenig CPU, zu wenig Speicher, zu langsame IO". Admin guckt nach und sagt, "da fehlen bloss ein paar Indizes". (2) Selbstgelernte Entwickler ohne spezifische Einsicht in relationale Datenbanken kommen oft ohne sie aus. Kann sich irgendwann rächen.
:
Bearbeitet durch User
>Was ist nun aber mit den zahlreichen foreign keys, die in vielen >Tabellen vorhanden sind? Jeder Termin ist z.B. einem Kursleiter und >einem Ort zugeordnet, die in der Suche dann unter anderem als >Filter-Kriterium verwendet werden. Setzt man auf kursleiter_id und Normalerweise ja. Aber wenn die Query noch andere Filterkriterien diese Tabelle nutzt, die bereits selbst sehr selektiv sind, dann bringen weitere selektive Indices nicht mehr viel. Wie andere schon sagten - muß man in Zusammenhang mit der DDL der Tabellen und der Abfrage sehen.
>Was ist nun aber mit den zahlreichen foreign keys, die in vielen >Tabellen vorhanden sind? Jeder Termin ist z.B. einem Kursleiter und >einem Ort zugeordnet, die in der Suche dann unter anderem als >Filter-Kriterium verwendet werden. Setzt man auf kursleiter_id und Normalerweise ja. Aber wenn die Query noch andere Filterkriterien diese Tabelle nutzt, die bereits selbst sehr selektiv sind, dann bringen weitere selektive Indices nicht mehr viel. Wie andere schon sagten - muß man in Zusammenhang mit der DDL der Tabellen und der Abfrage und der Datencharakteristik sehen.
Ron schrieb: > ich habe eine InnoDB-Datenbank, in der ich per Abfrage diverse Tabellen > joine und per Datumsvergleich nach freien Terminen suche. Da ich nach > freien Zeitfenstern in festgelegten Intervallen suche, nutze ich eine > Hilfstabelle mit allen Intervallen pro Tag (00:00:00, 00:05:00, ..., > 23:55:00) und suche dann für einen gewissen Zeitraum für jeden Tag die > entsprechenden Zeitfenster. > Die Such-Performance ist nicht überragend, daher habe ich versucht diese > durch das Anlegen von Indizes zu verbessern. Die Suche ist dadurch auch > schon signifikant schneller geworden, aber ich frage mich, in welchem > Maße Indizes normalerweise verwendet werden? Das solltest Du nicht Dich fragen, sondern Deine Datenbank. ;-) Nein, im Ernst: für solche Fragen bieten die meisten Datenbanken ein Werkzeug, um den Ausführungsplan auszugeben, den der Query Optimizer erzeugt -- und da steht dann drin, welche Indizes von der Abfrage verwendet werden. Dieses Werkzeug heißt häufig "EXPLAIN" und wird dem zu analysierenden Query einfach vorangestellt, statt "SELECT foo, bar FROM baz" führst Du dann also "EXPLAIN SELECT foo, bar FROM baz" aus, etwa im Kommandozeilenclient, und bekommst dann den Ausführungsplan angezeigt. Indizes in Datenbanken sind ein sehr umfangreiches Thema. Wie verschiedene Vorredner schon sehr richtig angemerkt haben, kosten Indizes Performance bei INSERTs und UPDATEs auf die betreffenden Felder und ganz allgemein bei DELETEs, weil die geänderten Daten dann natürlich nicht nur in der Tabelle sondern eben auch in den Indizes nachgepflegt werden müssen. Dafür können sie die Performance von SELECTs allerdings wesentlich erhöhen -- wenn sie benutzt werden, versteht sich. Ob sie benutzt werden, läßt sich nicht einmal anhand der DDL-Schemata der Datenbank vorhersagen. Von meinen (zum Teil mehrere hundert GB großen) PostgreSQL-Datenbanken kann ich sagen, daß Indizes für kleine Tabellen häufig gar nicht benutzt werden, weil ein sequentieller Scan der Tabelle(n) dann trotz allem schneller sein kann -- das hängt aber von der Art des Index (B-Tree, LSM-Tree, ...) und vielen anderen Faktoren ab. Auch sonst können Datenbanken sehr zickig bei der Frage sein, welcher Query welchen Index nutzt -- oft kann da sogar die Reihenfolge der Felder in Index und Abfrage eine Rolle spielen. Ein weiterer Punkt ist die Frage, ob die Indizes in den Arbeitsspeicher passen oder auf die Festplatte ausgelagert werden müssen. Eine Datenbank wie PostgreSQL führt dazu Statistiken, welche Indizes wie oft abgefragt worden sind, hält die am häufigsten genutzten im RAM und lagert seltener genutzte auf die Festplatte aus. Das macht die Sache dann noch erheblich komplizierter, denn solche Indizes können schneller sein als sequentielle Tabellenscans, müssen sie aber nicht. Die beschriebene Komplexität macht es schwierig bis unmöglich, allgemein gültige Ratschläge zu geben. Aber ein paar Tipps habe ich dennoch. 1. EXPLAIN ist Dein Freund -- und zwar nicht nur nach dem Anlegen der DB mit ein paar kleinen Testdaten, sondern unter produktionsnahen Bedingungen mit einer produktiven oder zumindest produktionsähnlichen Workload. Solche Workloads lassen sich etwa mit sogenannten Faker-Libs erzeugen, die für verschiedene Programmiersprachen verfügbar sind. Auch im Betrieb ist es sinnvoll, immer mal wieder mit EXPLAIN zu prüfen, ob die Indizes noch zum Datenbankschema, zur Workload und den verwendeten Queries passen. 2. Wenn INSERT-, UPDATE- und / oder DELETE-Performance wichtiger sind als die SELECT-Performance, oder wenn Indizes nicht verwendet werden, sind Indizes kontraproduktiv. Bei Bulk-Loads -- wenn Du also viele Daten in die Datenbank pumpen willst, etwa bei einem Initial Load -- ist es meistens sinnvoll, die Indizes zu deaktivieren oder sogar zu löschen, und sie erst nach der Befüllung der DB wieder einzuschalten bzw. zu erzeugen. 3. Datenbanken wie PostgreSQL halten Statistiken für die Nutzung und die Hitrates ihrer Indizes vor. Damit lassen sich ungenutzte und unproduktive Indizes recht gut herausfinden; diese Statistiken fließen übrigens auch in die Planung des Query Optimizer mit ein. 4. Kluge Datenmodelle und Queries nutzen oft genausoviel und manchmal sogar mehr als das Anlegen von Indizes. Dabei darf man in begründeten Ausnahmen sogar schonmal von der Normalform abweichen, aber: das müssen Ausnahmen bleiben, und sie müssen begründet sein. 5. Datenbanken sind ziemlich empfindlich, was ihre Konfiguration angeht, und werden häufig sehr konservativ konfiguriert ausgeliefert. Meistens ist es, flankierend zum Anlegen von Indizes, sehr sinnvoll, die Konfiguration der Datenbank zu tunen und ihr etwa mehr Speicher zuzuteilen. Hier spielen auch die Anzahl der DB-Threads oder -Prozesse, das Festspeichermedium und weitere Faktoren eine Rolle. Das ist aber wieder ein sehr komplexes Thema für sich, auf das ich hier nicht eingehen möchte. Nur soviel dazu: häufig lohnt es sich, den Festspeicher der Datenbank auf ein schnelles Medium wie eine SATA-SSD oder sogar eine PCIe-SSD (NVMe) zu verlegen, die wesentlich höhere Datendurchsätze und erheblich kleinere Zugriffszeiten insbesondere bei Random-Seeks vorweisen können. 6. Viele Datenbanken bieten die Möglichkeit, Tabellen zu partitionieren. Dadurch lassen sich eine Tabelle und deren Indizes auf mehrere kleinere Tabellen und Indizes verteilen, was sich oft besonders bei zeitbasierten Daten wie etwa Log- oder Transaktionsdaten anbietet, wenn auf die alten Tabellen nicht mehr oder nurmehr selten zugegriffen werden muß. Dann kann die Datenbank die Indizes für die alten Tabellen auf die Platte schreiben (oder der DBA sie löschen), während die Indizes (und ggf. Tabellen) der aktuellen, oft genutzten Tabellen im schnellen Arbeitsspeicher liegen. 7. Ansonsten gelten die beiden Merksätze, die bei Performanceoptimierungen immer gelten: a) Measure, don't guess sowie b) Premature Optimization is the root of all evil. Will sagen: Performanceoptimierungen beruhen nicht auf Vermutungen, sondern auf Messungen, und Standardrezepte funktionieren nicht. Die einzig richtige Vorgehensweise ist also: Datenbank anlegen, mit einer produktionsnahen Workload befüllen, und diese mit den Queries testen die in der Produktion genutzt werden -- und zwar nicht nur mit den Queries selbst, sondern auch die Anzahlen der jeweiligen Queries sollten in etwa jenen der Produktion entsprechen. (Optimierungen von Queries, die selten ausgeführt werden, haben naturgemäß weniger Einfluß auf das Ergebnis als Optimierungen von Queries, die sehr oft ausgeführt werden.) Nachdem mit dieser Vorgehensweise eine Basislinie als Referenz festgelegt wurde, geht erst die eigentliche Optimierung los: immer nur einen einzigen Parameter verändern, dann testen. Wenn der Parameter keine Verbesserung gebracht hat, diesen wieder zurücksetzen, ansonsten beibehalten. Und dann wieder von vorne: einen einzigen Parameter verändern, dann testen, und so weiter. Das ist eine ziemlich aufwändige Angelegenheit, die dadurch nicht einfacher wird, daß verschiedene Parameter sich gegenseitig beeinflussen. Mit der Zeit bekommt man ein gewisses Gefühl für die Abhängigkeiten und Einflüsse, aber darauf sollte man sich nie verlassen: schon mit der neuen Version der Datenbank kann vieles wieder ganz anders aussehen. Darum zuletzt noch ein Rat: treib's nicht zum Exzess. Leg' Dir ein SLA zu, also ein Service Level Agreement zur Not mit Dir selbst, in dem Du dann (realistisch!) beschreibst, welche Ziele zu erreichen willst. Konzentrier Deine Arbeit darauf, diese Ziele zu erreichen, vor allem bei besonders wichtigen, besonders lange laufenden sowie besonders häufig ausgeführten Queries. Für PostgreSQL gibt es einen Loganalyzer namens pgfouine, der ausgibt, welche Queries wie häufig ausgeführt worden, und wie lange diese gelaufen sind -- vielleicht gibt es so etwas auch für MySQL. Wie dem auch sei: es hat keinen Zweck, eine "optimale" Performance zu erreichen, weil man dazu am Ende jedes Optimierungslaufes wieder von vorne beginnen müßte und dann, wie gesagt, mit der nächsten Major- oder Minor-Version der DB wieder alles anders aussehen kann. In diesem Sinne: viel Spaß und Erfolg bei Deinem Vorhaben!
Thread beobachten
Seitenaufteilung abschaltenBitte melde dich an um einen Beitrag zu schreiben. Anmeldung ist kostenlos und dauert nur eine Minute.
Bestehender Account
Schon ein Account bei Google/GoogleMail? Keine Anmeldung erforderlich!
Mit Google-Account einloggen
Mit Google-Account einloggen
Noch kein Account? Hier anmelden.