DB-Design: Index richtig verwenden

[yankee]

Hi @ll,

ich habe mir gerade ein Script geschrieben, welches eine 1,3 MB Tabelle durchgeht und in einer Schleife ca. 20.000 querys darauf ausfuehrt. Nur Select-Abfragen mit einem WHERE auf eine INT-Spalte.
Das Script hat 90 Sekunden gebraucht und damit einen schnitt von 46 Querys/Sekunde geschafft.
Nachdem ich das Skript ein paar mal laufen gelassen habe, kam ich auf die Idee, dass das doch ein wunderbares Beispiel dafuer ist um mal zu testen, welche Auswirkungen ein Index auf die angesprochene INT-Spalte sich auswirkt.
Also eben phpmyadmin auf, angelegt und mein Script mal wieder ausgefuehrt...
Ich habe erwartet, dass mein Script vielleicht so 10% schneller ist. Bei 50% haette ich gestaunt. 10mal so schnell haette ich nicht fuer moeglich gehalten.
Als ich dann meinen Skript startete und gleichzeitig mich aus meinem Stuhl erhob um mein Muesli wieder aufzufuellen konnte ich mich direkt wieder auf die Sitzflaeche fallen lassen, denn mein Script war fertig. Geamtdauer: 0.45 Sekunden. Querys pro Sekunde: 9108.
Wow. Das isty eine Geschwindigkeitserhoehung um den Faktor 180! *dumm aus der Waesche guck* EINHUNDERTACHTZIG!
Ich habe sogar den index wieder entfernt und mein script nochmal laufen lassen um sowas wie einen query-cache auszuschliessen. Dann hatte ich wieder nur noch 50querys/Sekunde. 4 querys mehr pro Sekunde, als bei meinem ersten Versuch, aber bedeutend langsamer als bei meinem Versuch mit index.

Gut, Ende der Story, wie ich darauf komme und start mit der Frage:
Ist es sinnvoll auf jede spalte, die in irgendeinem WHERE oder ORDER BY oder GROUP BY vorkommt ein index zu legen? Was haelt mich daran zurueck einfach ueberall einen Index drauf zu legen? Haben indices irgendwelche negativen effekte (viel HDD-speicher oder so..)

[marabu]

Hi.

Das sind die Freuden des Datenbank-Entwurfs. Jeder Index verteuert INSERT, UPDATE und DELETE und begünstigt SELECT. Üblicherweise macht man eine Datenmengenbetrachtung und erstellt ein Benutzungsprofil. Außerdem wird ein RDBMS administriert und eine der vornehmen Aufgaben des DBA ist es die Indexe nach Bedarf zu erstellen oder zu entfernen, je nach aktueller Anforderung. Bei einem INSERT-lastigen Profil wird man wohl viele Indexe entfernen und bei einem SELECT-lastigen Profil wieder erzeugen. Build-From-Scratch geht sehr schnell - im Vergleich mit der ständigen Aktualisierung eines Index. Einige RDBMS haben einen so intelligenten Planersteller, dass dieser bei der Kostenanalyse mit dem Index-Manager kommunizieren kann um einen Index ON-DEMAND zu erstellen.

Grüße vom marabu

[yankee]

In meinem DB Projekt habe ich schon massenhaft INSERTs und UPDATEs. Aber das sind dann mal so 16 Datensaetze die ich manipuliere. Bei SELECTs ziehe ich mir allerdings immer gleich ziemlich viele Daten aus der DB...
Also wenn bei einem index die inserts und updates so 20% langsamer sind, dann macht das nichts. Wenn die jetzt dann aber auch 180 mal langsamer sind....

Also wie stark faellt das denn ins Gewicht?

[marabu]

Natürlich beinflusst eine pauschale Indizierung alle DML-Statements gewaltig, aber oft merkt der Benutzer deines Programms nichts davon - zumindest nicht direkt - weil andere Aktionen im Programm einen bestimmenden Zeitfaktor haben und die Vervierfachung der INSERT-Kosten immer noch klein gegen die Zeit für den Aufbau der Listenansicht ist (Beispiel). Es gibt keine allgemeingültige Formel. Erfahrung und technische Expertise sind hilfreich. Wenn du vorab für ein lupenreines konzeptuelles Datenmodell gesorgt hast und bei der Implementierung keine Böcke geschossen hast, dann lässt sich im laufenden Betrieb fast alles richten.

marabu

[yankee]

mhh... Also ich habe mal gerade ein paar mehr indecis in meine Datenbank hinzugefuegt. So ueberall, wo ich dachte, dass das sinvoll ist und habe dann mal mein Hauptinsertscript getestet.
Tatsaechlich laufen die inserts jetzt sogar 3x SCHNELLER als vorher. Das duerfte wohl daran liegen, dass mein Insertscript selbst auch wieder Selects macht um ids in anderen Tabellen nachzuschauen um die dann wieder richtig in eine ander Tabelle zu inserten...

Also ich wuerde mal sagen fuer meine Zwecke sind diese Idices in jedem Fall mal eine super Sache.
Danke fuer deine Erklaerungen, marabu!