Langsame Indizes, Teil II

Der vorherige Abschnitt zeigte, wie man durch eine veränderte Spaltenreihenfolge einen zusätzlichen Nutzen aus einem Index ziehen kann. Dabei wurden aber nur zwei Abfragen berücksichtigt. Wenn man einen bestehenden Index ändert, sind jedoch alle Zugriffe auf die Tabelle betroffen. Dieser Abschnitt erklärt, wie die Datenbank einen Index auswählt, und zeigt die möglichen Nebenwirkungen einer Indexänderung.

Der neue EMPLOYEES_PK-Index verbessert die Performance für Abfragen, die nur mit der SUBSIDIARY_ID suchen. Der Index wird dadurch aber für alle Abfragen nutzbar, die einen SUBSIDIARY_ID-Filter benutzen – unabhängig davon, ob sie noch andere Suchkriterien verwenden. Das sind also auch Abfragen, die zuvor einen anderen Index, mit einem anderen Teil der where-Klausel, verwendet haben. In diesem Fall, wenn es mehrere Zugriffswege für eine Abfrage gibt, ist es die Aufgabe des Optimizers, den besten Zugriffsweg auszuwählen.

Die Änderung des Indexes kann auch unangenehme Nebenwirkungen haben. In unserem Beispiel trifft es die interne Telefonbuch-Applikation, die seit der Betriebszusammenlegung sehr langsam geworden ist. Bei der ersten Analyse wurde die folgende SQL-Anweisung als Ursache identifiziert.

SELECT first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE last_name  = 'WINAND'
   AND subsidiary_id = 30

Dabei wird der folgende Ausführungsplan verwendet:

---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |   30 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |   30 |
|*2 |  INDEX RANGE SCAN          | EMPLOYEES_PK |   40 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("LAST_NAME"='WINAND')
  2 - access("SUBSIDIARY_ID"=30)

Der Ausführungsplan hat einen Cost-Wert von 30 und verwendet einen Index. Auf den ersten Blick sieht er also gut aus. Es ist aber auffällig, dass der geänderte Index verwendet wird. Der Verdacht liegt also nahe, dass die Indexänderung die Ursache des Problems ist. Wenn man dann noch bedenkt, dass der Index mit der alten Definition (EMPLOYEE_ID, SUBSIDIARY_ID) nicht für die Abfrage benutzt werden konnte, kann kein Zweifel mehr bestehen: Die Indexänderung machte die Abfrage langsam.

Zur weiteren Analyse wäre der Vergleich mit dem Ausführungsplan mit dem alten Index interessant. Dafür könnte man den Index natürlich zurückbauen. Viele Datenbanken bieten aber einfachere Möglichkeiten an, die Indexnutzung für eine Abfrage zu unterbinden. Das folgende Beispiel verwendet dafür einen Optimizer-Hint der Oracle Datenbank.

SELECT /*+ NO_INDEX(EMPLOYEES EMPLOYEES_PK) */ 
       first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE last_name  = 'WINAND'
   AND subsidiary_id = 30

Der Ausführungsplan, der vermutlich vor der Indexänderung verwendet wurde, verwendet überhaupt keinen Index:

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    1 |  477 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |    1 |  477 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("LAST_NAME"='WINAND' AND "SUBSIDIARY_ID"=30)

Obwohl der Full-Table-Scan die gesamte Tabelle lesen und verarbeiten muss, ist er in diesem Fall offenbar schneller als der Indexzugriff. Das ist besonders ungewöhnlich, da die Abfrage nur einen Treffer liefert. Gerade das Finden weniger Einträge sollte mit einem Index deutlich schneller sein. Ist es in diesem Fall aber nicht – der Index ist scheinbar langsam.

In solchen Fällen ist es am besten, den problematischen Ausführungsplan Schritt für Schritt durchzudenken. Die Ausführung beginnt mit einem INDEX RANGE SCAN auf dem EMPLOYEES_PK-Index. Da die Spalte LAST_NAME nicht im Index ist, kann der INDEX RANGE SCAN nur die SUBSIDIARY_ID be­rück­sich­tigen. Die Oracle Datenbank zeigt das im „Predicate Information“ Bereich des Ausführungsplanes an. Dort sieht man also, welche Bedingungen bei welcher Operation aufgelöst werden.

Der INDEX RANGE SCAN mit der Id 2 (Beispiel 2.1) verwendet nur die Bedingung SUBSIDIARY_ID=30. Das bedeutet, dass der Indexbaum benutzt wird, um den ersten Eintrag mit SUBSIDIARY_ID 30 zu finden. Danach wird die Blattknoten-Liste verfolgt, um alle Einträge mit dieser SUBSIDIARY_ID zu finden. Das Ergebnis des Indexzugriffs ist also eine Liste von ROWIDs, die der SUBSIDIARY_ID-Bedingung entsprechen. Das können, abhängig von der Größe der Zweigstelle, einige wenige oder aber tausende sein.

Der nächste Schritt ist der TABLE ACCESS BY INDEX ROWID. Er verwendet die ROWIDs aus dem vorherigen Schritt, um die vollständigen Zeilen – alle Spalten – aus der Tabelle zu laden. Erst wenn die LAST_NAME-Spalte verfügbar ist, kann der übrige Teil der where-Klausel geprüft werden. Das heißt, dass die Datenbank alle Zeilen für SUBSIDIARY_ID 30 laden muss, bevor sie die LAST_NAME-Bedingung anwenden kann.

Die Antwortzeit dieser Abfrage hängt also nicht von der Größe des Ergebnisses, sondern von der Anzahl der Mitarbeiter in der jeweiligen Zweigstelle ab. Wenn es nur wenige sind, wird der Indexzugriff schneller sein. Wenn es aber viele sind, kann ein FULL TABLE SCAN vorteilhaft sein, da er große Tabellenbereiche auf einmal lesen kann (siehe „Full-Table-Scan“).

Die Abfrage ist langsam, weil der Indexzugriff sehr viele Zeilen liefert – alle Mitarbeiter der ursprünglichen Firma – und die Datenbank jede Zeile einzeln aus der Tabelle laden muss. Die beiden Zutaten für einen langsamen Index treffen also zusammen: Es wird ein großer Indexbereich gelesen und anschließend ein Tabellenzugriff für jede Zeile durchgeführt.

Die Wahl des besten Ausführungsplanes hängt also auch von Tabellendaten ab. Daher verwendet der Optimizer Statistiken über den Tabelleninhalt. In diesem Fall ein Histogramm über die Verteilung der Mitarbeiter auf die Zweigstellen. Dadurch kann der Optimizer abschätzen, wie viele Zeilen der Indexzugriff auf eine bestimmte SUBSIDIARY_ID liefert und diese Zahl bei der Berechnung des Cost-Wertes berücksichtigen.

Wenn es keine Statistiken gibt – weil sie zum Beispiel für diese Demonstration absichtlich gelöscht wurden – verwendet der Optimizer Standardwerte. Die Oracle Datenbank geht dann von einem kleinen Index und einer mittleren Selektivität aus. Daraus ergibt sich die Schätzung, dass der INDEX RANGE SCAN 40 Zeilen liefert. Dieser Wert wird in der Rows-Spalte des Ausführungsplanes angezeigt (noch immer Beispiel 2.1). Offenbar eine grobe Fehleinschätzung, da in der betroffenen Zweigstelle 1000 Mitarbeiter beschäftigt sind.

Stellt man dem Optimizer akkurate Statistiken zur Verfügung, kann er eine bessere Abschätzung durchführen. Im folgenden Ausführungsplan geht er daher davon aus, dass der INDEX RANGE SCAN 1000 Einträge liefert. Dementsprechend hoch wird auch der Cost-Wert für den Tabellenzugriff angesetzt.

---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |  680 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |  680 |
|*2 |  INDEX RANGE SCAN          | EMPLOYEES_PK | 1000 |    4 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("LAST_NAME"='WINAND')
  2 - access("SUBSIDIARY_ID"=30)

Der Cost-Wert ist mit 680 sogar höher als beim Ausführungsplan ohne Index (477). Daher bevorzugt der Optimizer in dieser Situation automatisch den Ausführungsplan mit der TABLE ACCESS FULL Operation.

Dieses Beispiel eines „langsamen Indexes“ soll aber nicht darüber hinwegtäuschen, dass ein wohldefinierter Index die beste Lösung ist. Ein Index auf LAST_NAME unterstützt die Abfrage zum Beispiel sehr gut:

CREATE INDEX emp_name ON employees (last_name)

Mit diesem Index ermittelt der Optimizer den Cost-Wert 3.

--------------------------------------------------------------
| Id | Operation                   | Name      | Rows | Cost |
--------------------------------------------------------------
|  0 | SELECT STATEMENT            |           |    1 |    3 |
|* 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES |    1 |    3 |
|* 2 |   INDEX RANGE SCAN          | EMP_NAME  |    1 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SUBSIDIARY_ID"=30)
   2 - access("LAST_NAME"='WINAND')

Der Indexzugriff mit LAST_NAME liefert, nach Schätzung des Optimizers, nur eine Zeile. Daher muss auch nur eine Zeile aus der Tabelle geladen werden. Das ist definitiv schneller als die ganze Tabelle zu lesen. Ein wohldefinierter Index liefert also bessere Performance als der ursprüngliche Full-Table-Scan.

Der Unterschied der beiden Ausführungspläne in Beispiel 2.1 und Beispiel 2.2 ist nur sehr gering. Die Datenbank führt die gleichen Operationen durch und der Optimizer hat vergleichbare Cost-Werte ermittelt. Dennoch ist die Performance des zweiten Ausführungsplanes deutlich besser. Die Effizienz eines Indexzugriffes kann stark variieren – insbesondere wenn ein Tabellenzugriff folgt. Nur weil ein Index benutzt wird, heißt das nicht zwangsläufig, dass die Abfrage optimal ausgeführt wird.