Auswirkungen des Datenvolumens


Die Datenmenge, die in einer Datenbank gespeichert ist, hat großen Einfluss auf die Performance. Es wird üblicherweise akzeptiert, dass eine Datenbank langsamer wird, wenn sie wächst. Aber um wie viel wird sie langsamer, wenn sich die Datenmenge verdoppelt? Und wie kann man dieses Verhältnis verbessern? Das sind die Kernfragen, wenn man über Skalierbarkeit nachdenkt.

Als Beispiel betrachten wir die Ausführungszeit der folgenden Abfrage. Dafür verwenden wir zwei verschiedenen Indizierungs-Varianten, die wir vorerst nicht kennen. Im Verlauf dieses Abschnitts werden wir den Indexdefinitionen auf den Grund gehen.

SELECT count(*)
  FROM scale_data
 WHERE section = ?
   AND id2 = ?

Bei dieser Abfrage spielt die Spalte SECTION eine besondere Rolle: Sie kontrolliert das Datenvolumen. Je höher die SECTION-Nummer, desto höher das Datenvolumen.

Abbildung 3.1 zeigt die Antwortzeiten bei einer SECTION mit geringem Datenvolumen.

Abbildung 3.1. Performance-Vergleich


Zwischen den beiden Indizierungs-Varianten ist ein deutlicher Geschwin­dig­keitsunterschied zu erkennen. Mit einer Ausführungszeit von weniger als einer zehntel Sekunde sind aber beide Varianten sehr schnell – für die meisten Anwendungen wahrscheinlich schnell genug. Diese Messung offenbart aber nur einen Messpunkt. Bei Skalierbarkeit geht es darum, zu wissen, wie sich zum Beispiel ein steigendes Datenvolumen auf die Antwortzeit auswirkt.

Wichtig

Skalierbarkeit zeigt die Abhängigkeit der Performance von Faktoren wie zum Beispiel dem Datenvolumen.

Performance ist nur ein Punkt auf der Skalierungs-Kurve.

Abbildung 3.2 zeigt die Antwortzeiten daher in Abhängigkeit der SECTION-Nummer – das heißt, für ein steigendes Datenvolumen.

Abbildung 3.2. Skalierung über Datenvolumen


Der Chart zeigt wachsende Antwortzeiten für beide Varianten. Am rechten Ende des Charts, wenn das Datenvolumen hundertmal so groß ist, benötigt die schnellere Variante gut doppelt so lange wie ursprünglich. Die Antwort­zeit der langsameren Variante ist aber um den Faktor 20 auf über eine Sekunde gestiegen.

Tipp

In Anhang C, „Beispiel Schema, gibt es die Skripte um diesen Test in einer Oracle-, SQL Server- oder PostgreSQL-Datenbank durchzuführen.

Die Antwortzeit einer SQL-Abfrage ist von vielen Faktoren abhängig, unter anderem vom Datenvolumen. Nur weil eine Abfrage unter gewissen Testbedingungen schnell genug ist, heißt das nicht, dass sie auch unter Produktionsbedingungen ausreichend schnell ist. Das trifft insbesondere auf Entwicklungsumgebungen zu, die oft nur einen Bruchteil des Datenbe­standes des Produktionssystemes haben.

Es ist aber nicht überraschend, dass die Abfragen langsamer werden, wenn das Datenvolumen steigt. Der eklatante Unterschied ist aber doch etwas unerwartet. Wie kommt es zu den unterschiedlichen Wachstums-Raten?

Ein Blick auf die Ausführungspläne sollte genügen, um den Grund zu sehen:

DB2
-------------------------------------------------------------
ID | Operation           |                        Rows | Cost
 1 | RETURN              |                             |  208
 2 |  GRPBY (COMPLETE)   |         1 of 4456 (   .02%) |  208
 3 |   IXSCAN SCALE_SLOW | 4456 of 135449700 (   .00%) |  208
Explain Plan
-------------------------------------------------------------
ID | Operation           |                        Rows | Cost
 1 | RETURN              |                             |  296
 2 |  GRPBY (COMPLETE)   |         1 of 4456 (   .02%) |  296
 3 |   IXSCAN SCALE_FAST | 4456 of 135449700 (   .00%) |  296
MySQL
+------+------------+---------+-------+------+-----------------------+
| type | key        | key_len | ref   | rows | Extra                 |
+------+------------+---------+-------+------+-----------------------+
| ref  | scale_slow | 6       | const |    1 | Using index condition |
+------+------------+---------+-------+------+-----------------------+
+------+------------+---------+-------------+------+-------+
| type | key        | key_len | ref         | rows | Extra |
+------+------------+---------+-------------+------+-------+
| ref  | scale_fast | 12      | const,const |    1 |       |
+------+------------+---------+-------------+------+-------+
Oracle
------------------------------------------------------
| Id | Operation         | Name       | Rows  | Cost |
------------------------------------------------------
|  0 | SELECT STATEMENT  |            |     1 |  972 |
|  1 |  SORT AGGREGATE   |            |     1 |      |
|* 2 |   INDEX RANGE SCAN| SCALE_SLOW |  3000 |  972 |
------------------------------------------------------
------------------------------------------------------
| Id   Operation         | Name       | Rows  | Cost |
------------------------------------------------------
|  0 | SELECT STATEMENT  |            |     1 |   13 |
|  1 |  SORT AGGREGATE   |            |     1 |      |
|* 2 |   INDEX RANGE SCAN| SCALE_FAST |  3000 |   13 |
------------------------------------------------------
SQL Server

Der obere Ausführungsplan verwendet den Index scale_slow, der untere scale_fast. Dabei ist zu beachten, dass in beiden Fällen eine Index Seek-Operation verwendet wird—das gibt also keinen Hinweis darauf, warum der eine schneller ist als der andere.

Mit STATISTICS PROFILE ON kann man jedoch einen Unterschied sehen:

|--Compute Scalar
   |--Stream Aggregate(Count(*))
      |--Index Seek(OBJECT:scale_slow),
         SEEK:(scale_data.section=2),
         WHERE:(scale_data.id2=1234) ORDERED FORWARD)
|--Compute Scalar
   |--Stream Aggregate(Count(*))
      |--Index Seek(OBJECT:(scale_data.scale_fast),
         SEEK:(scale_data.section=1)
          AND  scale_data.id2=1234) ORDERED FORWARD)

Die Ausführungspläne sind nahezu ident – sie verwenden nur einen andereren Index. Obwohl der Cost-Wert den Geschwindigkeitsunterschied widerspiegelt, ist der Grund dafür im Ausführungsplan nicht zu erkennen.

Offenbar haben wir es wieder mit einem „langsamen Index“ zu tun; obwohl ein Index benutzt wird, ist die Ausführung langsam. Wir glauben aber nicht mehr an den Mythos vom „defekten Index“, sondern besinnen uns auf die zwei Zutaten, die einen Indexzugriff langsam machen: (1) der Tabellenzugriff; (2) einen großen Indexbereich durchsuchen.

Bei unseren Schlulungs-, Tuning-, und
Literaturangeboten ist für jeden was dabei

Da in keinem der beiden Ausführungsplänen ein Tabellenzugriff aufscheint (TABLE ACCESS BY INDEX ROWID), muss wohl beim einen Ausführungsplan ein größerer Indexbereich durchsucht werden als beim anderen. Aber woran erkennt man den durchsuchten Indexbereich in einem Ausführungs­plan? An den Zugriffsprädikaten natürlich!

Tipp

Beachte die Prädikats-Informationen.

Die Prädikats-Informationen sind keineswegs unnötige Details, die man wie oben weglassen kann. Ein Ausführungsplan ohne Prädikats-Informa­tionen ist unvollständig. Daher kann man die Ursache des Performance­unter­schiedes nicht aus den oberen Ausführungsplänen ablesen. In den vollständigen Ausführungsplänen ist der Grund aber zu sehen.

DB2
Explain Plan
-------------------------------------------------------------
ID | Operation           |                        Rows | Cost
 1 | RETURN              |                             |  208
 2 |  GRPBY (COMPLETE)   |         1 of 4456 (   .02%) |  208
 3 |   IXSCAN SCALE_SLOW | 4456 of 135449700 (   .00%) |  208

Predicate Information
 3 - START (Q1.SECTION = ?)
      STOP (Q1.SECTION = ?)
      SARG (Q1.ID2 = ?)
Explain Plan
-------------------------------------------------------------
ID | Operation           |                        Rows | Cost
 1 | RETURN              |                             |  296
 2 |  GRPBY (COMPLETE)   |         1 of 4456 (   .02%) |  296
 3 |   IXSCAN SCALE_FAST | 4456 of 135449700 (   .00%) |  296

Predicate Information
 3 - START (Q1.SECTION = ?)
     START (Q1.ID2 = ?)
      STOP (Q1.SECTION = ?)
      STOP (Q1.ID2 = ?)

Man beachte auch die Cost-Werte: Obwohl der zeite Ausführungsplan der effizientere ist, hat der erste den geringeren Cost-Wert. Der Optimizer würde also den falschen Wählen, wenn man beide Indizes anlegt.

MySQL
+------+------------+---------+-------+------+-----------------------+
| type | key        | key_len | ref   | rows | Extra                 |
+------+------------+---------+-------+------+-----------------------+
| ref  | scale_slow | 6       | const |    1 | Using index condition |
+------+------------+---------+-------+------+-----------------------+
+------+------------+---------+-------------+------+-------+
| type | key        | key_len | ref         | rows | Extra |
+------+------------+---------+-------------+------+-------+
| ref  | scale_fast | 12      | const,const |    1 |       |
+------+------------+---------+-------------+------+-------+
Oracle
------------------------------------------------------
| Id | Operation         | Name       | Rows  | Cost |
------------------------------------------------------
|  0 | SELECT STATEMENT  |            |     1 |  972 |
|  1 |  SORT AGGREGATE   |            |     1 |      |
|* 2 |   INDEX RANGE SCAN| SCALE_SLOW |  3000 |  972 |
------------------------------------------------------

Predicate Information (identified by operation id):
   2 - access("SECTION"=TO_NUMBER(:A))
       filter("ID2"=TO_NUMBER(:B))
------------------------------------------------------
| Id   Operation         | Name       | Rows  | Cost |
------------------------------------------------------
|  0 | SELECT STATEMENT  |            |     1 |   13 |
|  1 |  SORT AGGREGATE   |            |     1 |      |
|* 2 |   INDEX RANGE SCAN| SCALE_FAST |  3000 |   13 |
------------------------------------------------------

Predicate Information (identified by operation id):
   2 - access("SECTION"=TO_NUMBER(:A) AND "ID2"=TO_NUMBER(:B))
SQL Server

Um den Unterschied im grafischen Ausführungsplan zu sehen, muss man mit der Maus über die Index Seek-Operation fahren und zwischen "Predicate" und "Seek Predicates" unterscheiden.

|--Compute Scalar
   |--Stream Aggregate(Count(*))
      |--Index Seek(OBJECT:scale_slow),
         SEEK:(scale_data.section=2),
         WHERE:(scale_data.id2=1234) ORDERED FORWARD)
|--Compute Scalar
   |--Stream Aggregate(Count(*))
      |--Index Seek(OBJECT:(scale_data.scale_fast),
         SEEK:(scale_data.section=1)
          AND  scale_data.id2=1234) ORDERED FORWARD)

Mit STATISTICS PROFILE ON werden Filterprädikate als WHERE angezeigt, Zugriffsprädikate jedoch als SEEK.

Beachte

Der Ausführungsplan wurde zum besseren Verständnis vereinfacht. In Abschnitt 1.3 wird erklärt, worauf man bei der Be­stim­mung von Zugriffs- und Filterprädikaten genau achten muss.

Nun ist der Unterschied offensichtlich: Mit dem Index SCALE_SLOW wird nur die SECTION-Bedingung als Zugriffsprädikat genutzt. Die Datenbank liest alle Zeilen der Sektion und verwirft jene, die das Filterprädikat auf der Spalte ID2 nicht erfüllen. Die Antwortzeit wächst also mit der Anzahl der Zeilen in der jeweiligen Sektion. Beim Index SCALE_FAST können beide Bedingungen als Zugriffsprädikat genutzt werden. Die Antwortzeit wächst daher nur mit der Anzahl der selektierten Zeilen.

Wichtig

Index-Filterprädikate sind wie Blindgänger: Sie können jederzeit ex­plo­dieren.

Das letzte Puzzle-Stück, das uns bei diesem Fall noch fehlt, sind die Index­de­finitionen. Wie könnten die beiden Indizes aussehen?

Bei SCALE_SLOW muss die Spalte SECTION an erster Stelle stehen – sonst könnte sie nicht mit einem Zugriffsprädikat genutzt werden. Die Spalte ID2 wird aber nicht als Zugriffprädikat verwendet – sie kann daher nicht direkt nach SECTION im Index sein. Der Index SCALE_SLOW muss also mindestens drei Spalten haben, von denen die SECTION die Erste, ID2 aber nicht die Zweite ist. Und genau so ist es in der Definition, die für den Test verwendet wurde:

CREATE INDEX scale_slow ON scale_data (section, id1, id2)

Durch die Spalte ID1 an zweiter Stelle kann die Bedingung auf ID2 nicht als Zugriffsprädikat genutzt werden.

Für alle Anwendungsentwickler […] sollte der schmale Band […] eine Pflichtlektüre sein — ADMIN-Magazin

Beim Index SCALE_FAST werden beide Bedingungen als Zugriffsprädikat genutzt, daher müssen die Spalten SECTION und ID2 an den ersten beiden Stellen der Indexdefinition stehen. Über die Reihenfolge kann man nichts sagen. Der tatsächlich verwendete Index beginnt mit SECTION und hat die ID1-Spalte zusätzlich an dritter Stelle:

CREATE INDEX scale_fast ON scale_data (section, id2, id1)

Die ID1-Spalte ist nur im Index, damit er nicht kleiner als SCALE_SLOW ist – sonst könnte der Eindruck entstehen, die Größe mache den Unterschied.

Wenn dir gefällt, wie ich die Dinge erkläre, wirst du meine Kurse lieben.

Über den Autor

Photo of Markus Winand
Markus Winand stimmt Entwickler auf SQL-Performance ein. Er hat das Buch SQL Performance Explained veröffentlicht und bietet inhouse Schulungen sowie Tuning-Leistungen auf http://winand.at/ an.