SQL-Leistungsprobleme aufgrund „unnötiger, aber erforderlicher Arbeit“

Zum Verständnis des Artikels erforderlicher Wissensstand: allgemeines Verständnis von Datenbanken und SQL, einige praktische Erfahrungen mit DBMS. Das wahrscheinlich Wichtigste, was Sie lernen können, um effektive SQL-Abfragen zu schreiben, ist die Indizierung. An zweiter Stelle, ganz dicht dahinter, steht jedoch die Erkenntnis, dass viele SQL-Clients von der Datenbank eine Menge „unnötige, aber notwendige Arbeit“ verlangen . Sprich mir nach: Was ist „unnötige, aber obligatorische Arbeit“? Wie Captain Obvious uns erzählt, sagte sie:

Unnötig

Lassen Sie unsere Client-Anwendung die folgenden Daten benötigen: Nichts Ungewöhnliches. Wir arbeiten mit einer Filmdatenbank (z. B. der Sakila-Datenbank ) und möchten den Benutzern den Titel und die Bewertung aller Filme anzeigen. Die folgende Abfrage kann das benötigte Ergebnis liefern:

SELECT title, rating
FROM film

Unsere Anwendung (oder unser ORM) führt jedoch stattdessen diese Abfrage aus:

SELECT *
FROM film

Was bekommen wir als Ergebnis? Erraten. Wir erhalten viele nutzlose Informationen: Auf der rechten Seite können Sie sogar sehen, wie einige komplexe JSON-Dateien geladen werden:

• von der Festplatte
• zwischenspeichern
• von Draht
• im Gedenken an den Kunden
• und schließlich weggeworfen [als unnötig]

Ja, wir werfen die meisten dieser Informationen weg. Alle Maßnahmen zur Extraktion dieser Informationen erwiesen sich als völlig nutzlos. Ist es wahr? Ist es wahr.

Obligatorisch

Und jetzt – das Schlimmste. Obwohl Optimierer mittlerweile viel können, sind diese Aktionen für die Datenbank zwingend erforderlich. Die Datenbank kann nicht erkennen, dass die Clientanwendung 95 % dieser Daten nicht benötigt. Und das ist nur das einfachste Beispiel. Stellen Sie sich vor, Sie verbinden mehrere Tabellen ... Na und, sagen Sie, aber Datenbanken sind schnell? Lassen Sie mich Sie über einige Dinge aufklären, an die Sie wahrscheinlich noch nicht gedacht haben. Natürlich hat die Ausführungszeit einer einzelnen Anfrage keinen wirklichen Einfluss. Okay, es lief eineinhalb Mal langsamer, aber wir schaffen das schon, oder? Zur Bequemlichkeit? Manchmal ist das wahr. Aber wenn Sie immer die Leistung zugunsten der Bequemlichkeit opfern , summieren sich diese kleinen Dinge. Wir werden nicht mehr über Leistung (die Geschwindigkeit der Ausführung einzelner Anfragen) sprechen, sondern über den Durchsatz (Systemantwortzeit), und dann werden ernsthafte Probleme auftreten, die nicht so einfach zu lösen sind. Dann geht die Skalierbarkeit verloren. Werfen wir einen Blick auf die Ausführungspläne, in diesem Fall das Oracle DBMS:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |   166K|
|   1 |  TABLE ACCESS FULL| FILM |  1000 |   166K|
--------------------------------------------------

im Vergleich zu:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 | 20000 |
|   1 |  TABLE ACCESS FULL| FILM |  1000 | 20000 |
--------------------------------------------------

Beim Ausführen einer SELECT *-Abfrage anstelle von SELECT title verbraucht die Bewertung achtmal mehr Speicher in der Datenbank. Nichts Unerwartetes, oder? Wir wussten, dass das passieren würde. Dennoch stimmen wir dem bei vielen unserer Anfragen zu, bei denen wir einfach nicht alle diese Daten benötigen. Wir erzeugen unnötige, aber zwingende Arbeit für die Datenbank , die sich immer weiter anhäuft. Wir verbrauchen achtmal mehr Speicher als benötigt (der Multiplikator ändert sich natürlich). In allen anderen Phasen (Festplatten-E/A, Datenübertragung über das Netzwerk, Speicherverbrauch durch den Client) sind die Probleme hingegen genau die gleichen, aber ich überspringe sie und schaue mir stattdessen ... an.

Verwendung von Indizes

Die meisten Datenbanken haben heute bereits das Konzept der Abdeckung von Indizes erkannt . Ein abdeckender Index ist selbst kein spezieller Indextyp. Es kann sich jedoch herausstellen, dass es sich um einen „speziellen Index“ für eine bestimmte Abfrage handelt, entweder „zufällig“ oder weil es so beabsichtigt war. Betrachten Sie die folgende Abfrage:

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

Hinsichtlich der Umsetzung gibt es nichts Unerwartetes. Dies ist eine einfache Anfrage. Sehen Sie sich den Bereich nach Index an, greifen Sie auf die Tabelle zu – und schon sind Sie fertig:

-------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR               |     8 |
|*  2 |   INDEX RANGE SCAN          | IDX_ACTOR_LAST_NAME |     8 |
-------------------------------------------------------------------

Guter Plan, nicht wahr? Wenn wir das wirklich brauchten, dann nein: Offensichtlich verschwenden wir Speicher usw. Betrachten wir diese Abfrage als Alternative:

SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'

Sein Plan ist dieser:

----------------------------------------------------
| Id  | Operation        | Name            | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT |                 |     8 |
|*  1 |  INDEX RANGE SCAN| IDX_ACTOR_NAMES |     8 |
----------------------------------------------------

Wir konnten den Zugriff auf die Tabelle vollständig eliminieren, da ein Index vorhanden war, der alle Anforderungen unserer Abfrage erfüllt: ein abdeckender Index. Ist es wichtig? Und wie! Mit diesem Ansatz können Sie einige Abfragen um eine Größenordnung beschleunigen (oder sie um eine Größenordnung verlangsamen, wenn der Index nach einigen Änderungen nicht mehr abdeckt). Abdeckindizes können nicht immer verwendet werden. Sie müssen für Indizes bezahlen und sollten nicht zu viele davon hinzufügen. Aber in diesem Fall ist alles klar. Lassen Sie uns die Leistung bewerten:

SET SERVEROUTPUT ON
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT NUMBER := 100000;
BEGIN
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Наихудший вариант Anfrageа: перерасход памяти ПЛЮС доступ к таблице
      SELECT *
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 1 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Улучшенный Anfrage, но все равно с доступом к таблице
      SELECT /*+INDEX(actor(last_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 2 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Оптимальный Anfrage: покрывающий индекс
      SELECT /*+INDEX(actor(last_name, first_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 3 : ' || (SYSTIMESTAMP - v_ts));
END;
/

Beachten Sie, dass die Akteurtabelle nur 4 Spalten hat, sodass der Leistungsunterschied zwischen den Anweisungen 1 und 2 nicht so groß, aber dennoch erheblich ist. Ich möchte außerdem anmerken, dass ich Hinweise des Oracle-Optimierers verwendet habe, damit der Optimierer den einen oder anderen spezifischen Index für die Abfrage auswählt. Operator 3 ist der unangefochtene Gewinner unseres Rennens. Die Leistung ist viel besser und wir sprechen von einer äußerst einfachen Abfrage. Auch hier gilt: Wenn wir SELECT * schreiben, erzeugen wir unnötige, aber obligatorische Arbeit für die Datenbank , die sie nicht optimieren kann. Sie wird den abdeckenden Index nicht auswählen, da dieser einen etwas höheren Overhead verursacht als der von ihr gewählte Index LAST_NAME und sie unter anderem immer noch auf die Tabelle zugreifen muss, um beispielsweise eine nutzlose LAST_UPDATE-Spalte abzurufen. Aber je tiefer wir SELECT * analysieren, desto schlimmer wird es. Lass uns reden über...

SQL-Konvertierungen

Optimierer funktionieren so gut, weil sie SQL-Abfragen transformieren ( ich habe in meinem letzten Vortrag bei den Voxxed Days in Zürich darüber gesprochen, wie das funktioniert ). Beispielsweise gibt es eine äußerst leistungsfähige „Exception JOIN“-Transformation. Betrachten Sie die folgende Hilfsansicht, die wir erstellen mussten, um zu vermeiden, dass alle diese Tabellen jedes Mal manuell verknüpft werden:

CREATE VIEW v_customer AS
SELECT
  c.first_name, c.last_name,
  a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)

Diese Ansicht führt einfach alle „...-zu-eins“-Verknüpfungen zwischen der Kundentabelle CUSTOMER und verschiedenen Tabellen für Teile ihrer Adresse durch. Danke, Normalisierung. Stellen Sie sich vor, dass wir uns, nachdem wir ein wenig mit dieser Ansicht gearbeitet haben, daran gewöhnt haben und die ihr zugrunde liegenden Tabellen vergessen haben. Und jetzt führen wir die folgende Abfrage aus:

SELECT *
FROM v_customer

Als Ergebnis erhalten wir einen sehr beeindruckenden Plan:

----------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost |
----------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   599 | 47920 |   14 |
|*  1 |  HASH JOIN           |          |   599 | 47920 |   14 |
|   2 |   TABLE ACCESS FULL  | COUNTRY  |   109 |  1526 |    2 |
|*  3 |   HASH JOIN          |          |   599 | 39534 |   11 |
|   4 |    TABLE ACCESS FULL | CITY     |   600 | 10800 |    3 |
|*  5 |    HASH JOIN         |          |   599 | 28752 |    8 |
|   6 |     TABLE ACCESS FULL| CUSTOMER |   599 | 11381 |    4 |
|   7 |     TABLE ACCESS FULL| ADDRESS  |   603 | 17487 |    3 |
----------------------------------------------------------------

Nun, natürlich. Die Datenbank führt all diese Verknüpfungen und vollständigen Tabellenscans durch, weil wir ihr gesagt haben, dass sie all diese Daten abrufen soll. Stellen Sie sich nun noch einmal vor, alles, was wir wirklich brauchten, wäre Folgendes: Was im Ernst, richtig? Jetzt beginnen Sie zu verstehen, wovon ich spreche. Aber stellen Sie sich vor, wir hätten aus den Fehlern der Vergangenheit etwas gelernt und führen diese, optimalere Abfrage aus:

SELECT first_name, last_name
FROM v_customer

Schauen wir uns nun an, was passiert ist!

------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   599 | 16173 |     4 |
|   1 |  NESTED LOOPS      |             |   599 | 16173 |     4 |
|   2 |   TABLE ACCESS FULL| CUSTOMER    |   599 | 11381 |     4 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C007120 |     1 |     8 |     0 |
------------------------------------------------------------------

Drastische Änderungen zum Besseren in Bezug auf die Ausführung. Die Verknüpfungen wurden entfernt, weil der Optimierer jetzt erkennen kann, dass sie nutzlos sind , und wenn er das erkennen kann (und Sie diese Arbeit nicht durch Auswahl von * zur Pflicht gemacht haben), dann kann er diese ganze Arbeit einfach nicht erledigen. Warum ist das in diesem Fall so? Der Fremdschlüssel CUSTOMER.ADDRESS_ID zum Primärschlüssel ADDRESS.ADDRESS_ID garantiert genau einen Wert des letzteren, was bedeutet, dass die JOIN-Operation ein „...-zu-eins“-Join ist, der die Anzahl der Zeilen weder erhöht noch verringert . Und da wir überhaupt keine Zeilen auswählen oder anfordern, macht es überhaupt keinen Sinn, sie zu laden. Das Entfernen des JOIN wird das Ergebnis der Abfrage wahrscheinlich überhaupt nicht beeinflussen. Datenbanken tun dies ständig. Sie können die folgende Abfrage für fast jede Datenbank ausführen:

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (SELECT 1 / 0)

In diesem Fall können Sie damit rechnen, dass eine arithmetische Ausnahme ausgelöst wird, etwa bei der Ausführung der folgenden Abfrage:

SELECT 1 / 0 FROM dual

Aber das passiert nicht. Der Optimierer (oder sogar der Parser) kann sicherstellen, dass keine ausgewählten Listenelemente im EXISTS-Prädikat (SELECT ..) das Ergebnis der Abfrage ändern, sodass keine Notwendigkeit besteht, sie auszuführen. So!

Inzwischen...

Eines der ärgerlichsten Probleme bei ORMs ist, dass sie so einfach SELECT *-Abfragen zu schreiben sind. Tatsächlich werden sie beispielsweise in HQL/JPQL im Allgemeinen standardmäßig verwendet. Wir können die SELECT-Klausel ganz weglassen, weil wir die gesamte Entität abrufen, oder? Zum Beispiel:

FROM v_customer

Vlad Mihalcea, ein Experte und Befürworter der Entwicklung mit Hibernate , empfiehlt beispielsweise, fast immer [qualifizierte] Abfragen zu verwenden, wenn Sie sicher sind, dass Sie nach dem Auschecken keine Änderungen speichern möchten. ORMs erleichtern die Lösung des Problems der Persistenz von Objektgraphen erheblich. Hinweis: Persistenz. Die Aufgaben der tatsächlichen Änderung von Objektdiagrammen und der Speicherung von Änderungen sind untrennbar miteinander verbunden. Aber wenn Sie das nicht tun, warum sollten Sie sich dann die Mühe machen, die Essenz zu extrahieren? Warum nicht eine [verfeinerte] Anfrage schreiben? Um es klar zu sagen: Unter dem Gesichtspunkt der Leistung ist das Schreiben einer speziell auf Ihren spezifischen Anwendungsfall zugeschnittenen Abfrage offensichtlich besser als jede andere Option. Es könnte Ihnen egal sein, da Ihr Datensatz klein ist und es keine Rolle spielt. Großartig. Wenn Sie jedoch irgendwann Skalierbarkeit benötigen, wird es eine ziemliche Herausforderung sein, Ihre Anwendungen so umzugestalten, dass sie Abfragen anstelle der zwingenden Durchquerung des Entitätsgraphen verwenden. Und Sie werden etwas ohne tun können.

Reihen zählen, um herauszufinden, ob etwas vorhanden ist

Eine der schlimmsten Ressourcenverschwendungen ist das Ausführen von COUNT(*)-Abfragen, nur um zu sehen, ob sich etwas in der Datenbank befindet. Wir müssen beispielsweise herausfinden, ob ein bestimmter Benutzer überhaupt Bestellungen hat. Und wir führen die Anfrage aus:

SELECT count(*)
FROM orders
WHERE user_id = :user_id

Grundschule. Wenn COUNT = 0, dann gibt es keine Bestellungen. Ansonsten ja. Die Leistung wird nicht so schlecht sein, da wir wahrscheinlich einen Index für die Spalte ORDERS.USER_ID haben. Aber wie wird Ihrer Meinung nach die Leistung der obigen Abfrage mit der folgenden Option verglichen:

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
)

Es bedarf keines Expertenwissens, um herauszufinden, dass ein wahres Existenzprädikat die Suche nach weiteren Zeichenfolgen einstellt, sobald es die erste gefunden hat . Wenn das Ergebnis also „keine Bestellungen“ lautet, ist die Geschwindigkeit vergleichbar. Wenn das Ergebnis jedoch „Ja, es liegen Bestellungen vor“ lautet, wird die Antwort in dem Fall, in dem die genaue Menge nicht gezählt werden muss, viel schneller eingehen . Die genaue Zahl interessiert uns schließlich nicht. Allerdings haben wir die Datenbank angewiesen, es zu berechnen ( unnötige Arbeit ), und die Datenbank weiß nicht, dass wir alle Ergebnisse größer als 1 ignorieren ( erforderliche Arbeit ). Natürlich wäre es viel schlimmer, wenn wir list.size() für eine JPA-gestützte Sammlung aufrufen würden, um die gleichen Ergebnisse zu erzielen. Darüber habe ich bereits früher in meinem Blog geschrieben und Vergleichstests beider Optionen durchgeführt ...

Abschluss

Dieser Artikel stellt das Offensichtliche dar. Zwingen Sie die Datenbank nicht zu unnötigen, aber erforderlichen Arbeiten . Dies ist unnötig , da Sie aufgrund der Anforderungen wissen, dass eine bestimmte Arbeit nicht erledigt werden muss. Allerdings weisen Sie die Datenbank an, dies zu tun. Dies ist erforderlich, da die Datenbank nicht sicherstellen kann, dass diese Arbeit unnötig ist . Diese Informationen stehen nur dem Client zur Verfügung, dem Server jedoch nicht. Die Datenbank muss es also ausführen. Der Artikel konzentrierte sich auf SELECT *, hauptsächlich weil es ein so praktisches Objekt ist, das man sich ansehen kann. Dies gilt jedoch nicht nur für Datenbanken. Dies gilt für alle verteilten Algorithmen, bei denen der Client den Server anweist, unnötige, aber erforderliche Arbeit zu erledigen . Wie viele N+1- Aufgaben gibt es in Ihrer durchschnittlichen AngularJS-Anwendung, bei der die Benutzeroberfläche das Ergebnis von Dienst A durchläuft und Dienst B mehrmals aufruft, anstatt alle Aufrufe an B in einen einzigen Aufruf zu packen? Dies kommt sehr häufig vor. Die Lösung ist immer dieselbe. Je mehr Informationen Sie der Entität zur Verfügung stellen, die Ihre Befehle ausführt, desto schneller führt sie diese Befehle (theoretisch) aus. Schreiben Sie optimale Abfragen. Stets. Ihr gesamtes System wird es Ihnen danken. Originaler Artikel