Reihenfolge der Spalten in Constraints wichtig?

Themen rund um den praktischen Einsatz von Firebird. Fragen zu SQL, Performance, Datenbankstrukturen, etc.

Moderator: thorben.braun

Antworten
Benutzeravatar
martin.koeditz
Beiträge: 443
Registriert: Sa 31. Mär 2018, 14:35

Hallo zusammen,

macht die Reihenfolge der Spalten eines Constraints einen Unterschied bei der späteren Abfrage, im Sinne von Performance-Gewinnen?

Als Beispiel habe ich einen zusammengestzten Primärschlüssel:

Code: Alles auswählen

CREATE TABLE TBL_TRANSLATION 
(
  CLIENT_ID                     BIGINT         NOT NULL,
  TR_KEY                       VARCHAR(   128) NOT NULL COLLATE UTF8,
  TR_LOCALE                    VARCHAR(     2) DEFAULT 'de' NOT NULL COLLATE UTF8,
  TR_TRANSLATION               VARCHAR(  4000) NOT NULL COLLATE UTF8,
  IS_USER_DEFINED             SMALLINT         DEFAULT 0 NOT NULL,
  CREATOR_ID                    BIGINT,
  CREATE_TIME                TIMESTAMP         DEFAULT LOCALTIMESTAMP,
  EDITOR_ID                     BIGINT,
  EDIT_TIME                  TIMESTAMP         DEFAULT LOCALTIMESTAMP,
 CONSTRAINT PK_TRANSLATION PRIMARY KEY (CLIENT_ID, TR_KEY, TR_LOCALE)
);
Macht die Reihenfolge der Felder bei späteren Abfragen einen Unterschied?
Ist also

Code: Alles auswählen

PRIMARY KEY (CLIENT_ID, TR_KEY, TR_LOCALE)
genauso performant wie z.B.

Code: Alles auswählen

PRIMARY KEY (TR_LOCALE, CLIENT_ID, TR_KEY)
Gleiches gilt für UNIQUE KEYS. Habe mir da noch nie Gedanken zu gemacht. M.E. müsste das aufgrund der Hashstruktur egal sein. Aber sicher bin ich mir nicht. Hat hier jemand Erfahrungswerte?

Gruß
Martin
Martin Köditz
it & synergy GmbH
bfuerchau
Beiträge: 485
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

Die Reihenfolge ist nur bedingt wichtig.
Die Präferenz für einen Index liegt i.W. auf der Where-/Join-/Group by/Order By-Bedingung.
Die Reihenfolge bei "=" Vergleichen spielt da nur eine Rolle bzgl. der Verwendung:

Beispiel: Key=F1 F2 F3

select * from mytable
where F1='A'

select * from mytable
where F1='A' and F2 = 'B'

select * from mytable
where F1='A' and F3=C and F2='B'

werden meist den Index verwenden.

select * from mytable
where F2='B'

wird den Index nicht verwenden, da F1 höherwertig ist.

select * from mytable
where F1 > 'D'

Hier kann der Index verwendet werden, da mit dem Key gestartet werden kann.

select * from mytable
where F1 <= 'D'

U.U. wird der key verwendet, meist jedoch nicht. Allerdings kann man hier wiederum einen Descending-Index erstellen:
Descending Key = F1, F2, F3

Zu beachten ist hierbei, dass der komplette Key dann absteigend ist.
Ein Index kann kann auch verwendet werden, wenn nur die ersten Felder verwendet werden und die anderen Felder der Bedingungen (s.o.) nicht in einem Index stecken.

Meine persönliche Erfahrung ist:
Mit den Vergleichen =, >, >=, between, start with, like "xxx%", in (...) muss der Index aufsteigend sein, bei <, <= sollte der Index absteigend sein.
Bei like "%xxx%", not between wird kein Index verwendet.
Bei "not xxx" kann ein index verwendet werden, wenn sich die Bedingung ins Gegenteil umdrehen lässt, als "not =" in "<>" usw.
Die Indexprüfung gilt für jede "or"-Gruppe separat.
Also: where K1='A' and K1 = 'B' or K1='X' and V1 starts with 'A'
Führt u.U. zu 2 separaten Indexzugriffen, die dann am Ende kombiniert werden.

Nun gibt es zusätzlich noch verschiedene Indexverfahren:
Binärer Baum: Schlüsselverteilung nach der binären Halbierungsmethode
Hierbei wird ein binärer Baum aufgebaut, so dass man je Schlüsselebene (k1, K2, ...) sich daran entlang bewegt um den Schlüssel zu finden.

"Name entfallen": Verwendung mehrerer Indizes und AND-Verknüpfung der Ergebnisse.
Hierbei werden die "relativen Satznummern" einer jeden Zeile als 1 bit in einer endlosen Bitmap erstellt. 0 = nicht gefundener, 1 = gefundener Schlüssel
Die AND-Verknüpfung ergibt dann das Gesamtergebnis der zu lesenden Satznummern.

Es gibt, vor allem bei InMemory-DB's, noch weitere Methoden (z.b. Hashtable).

Man kann ganz schön viele Indizes erstellen, was jedoch auch zu Performanceverlusten führen kann, ins besonders wenn man das Satzversionskonzept zu stark vernachlässigt hat.

Eine weitere Kennzahl ist die "Index Selectivity", die per "set statistics index MyIndexName" berechnet wird.
Wenn man viele Daten verändert oder hinzufügt sollte diese ab und zu durchgeführt werden, da sonst der erstellte Index mangels Selektivität nicht verwendet wird.
Die Selektivität ist "1/Anzahl unterschiedlicher Schlüssel (distinct)".
Also
1 = nur 1 Schlüssel in allen Sätzen
0,5 = nur 2 Schlüssel in allen Sätzen

Je kleiner also dieser Wert desto wahrscheinlicher die Verwendung, falls der SQL passt.

Es gibt sogar computet Indizes. Hierbei ist es wichtig, genau den selben Ausdruck wie in der "Computed By"-Klausel definiert ist anzuwenden, da sonst der Index nicht gefunden wird.

Wenn in deinem Beispiel die "Client-ID" bereits eine eindeutige ID wäre (Identity) sollte der PK hier bereits reichen.

Ein Primary Key ist per Definition immer Unique, unterscheidet sich also nicht vom Unique Key. Nur ein kleiner Unterschied dabei: beim PrimaryKey darf kein Feld "not null" enthalten, beim Unique Key aber schon.

Es gibt nur wenige Tools, die nach einem Select die verwendeten Indizes auswerten. IBExpert macht dies und der .Net-Treiber macht dies auch. Somit kann man zumindest prüfen, ob ein Index verwendet wird und der SQL entsprechend performant ist.

Fazit:
Man kann seine SQL's als auch dei DB mit Indizes sehr viel performanter machen.
Benutzeravatar
martin.koeditz
Beiträge: 443
Registriert: Sa 31. Mär 2018, 14:35

Hallo bfuerchau,

danke für die umfassende Übersicht.

Firebird nutzt intern den B-Tree-Algorithmus. Da dieser "nur" in eine Richtung funktioniert, müssen absteigende Indizes separat erzeugt werden.
Ein Primary Key ist per Definition immer Unique, unterscheidet sich also nicht vom Unique Key. Nur ein kleiner Unterschied dabei: beim PrimaryKey darf kein Feld "not null" enthalten, beim Unique Key aber schon.
Vermutlich meintest du: beim PrimaryKey darf kein Feld "null" enthalten, beim Unique Key aber schon. ;)

Hier noch eine Anmerkung von Vlad Khorsun, der einige Optimierung während der Firebird-Konferenz gab:
Indizes sollten grundsätzlich nur verwendet werden, wenn die zu erwartende Ergebnismenge bis 20% des Abfragebestandes betrifft. Andernfalls ist das sequenzielle Lesen der Daten schneller. Das aber nur als Anhaltspunkt. Die Abfrage muss natürlich dem jeweiligen Fall angepasst. sein.

Gruß
Martin
Martin Köditz
it & synergy GmbH
bfuerchau
Beiträge: 485
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

"Indizes sollten grundsätzlich nur verwendet werden, wenn die zu erwartende Ergebnismenge bis 20% des Abfragebestandes betrifft. "

Entschuldige aber, wer verzapft solchen Unsinn?

Wahrscheinlicher ist es mit weniger als 20%.
Ein Index bringt immer etwas wenn die Selektivität relativ groß ist und meine Where-Klausel (join und group sind analog zu sehen) einen Index genau trifft.
Da macht es auch Sinn bei größeren Datenmengen (mehrere Millionen Sätze) nur 50 oder 70% tatsächlichzu lesen.

Unser BI System errechnet auf Grund der Where- und Join-Klauseln mögliche Indizes und erstellt diese dann auch. Hierüber wird eine Statistik geführt wie häufig eine Abfrage wahrscheinlich diesen Index verwendet, so dass regelmäßig nicht mehr als 100 Indizes je Tabelle vorhanden sind.
Beim ETL Prozess müssen Indizes aus Performancegründen deaktiviert und anschließend wieder aktiviert werden und das kann halt mal dauern.

Z.B.: wenn eine Tabelle 30 Indizes hat, sinkt die Insertrate auf unter 100 je Sekunde.
Habe ich nur den Primary Key aktiv, steigt die Insertrate je nach Satzläge durchaus auf bis zu 5000 je Sekunden. Da tut dann der Neuaufbei der Indizes nicht so weh, da dieser durchaus parallel über mehrere Connections laufen kann.

Dass die Tabelle nur B-Tree-Indizes hat, war mir schon klar. Jedoch die Zugriffsoptimierung verwendet durchaus auch die Methode, mehrere Indizes über eine Row-Bitmap zu kombinieren. Wo und wann genau die Grenze ist für welches Verfahren konnte ich noch nicht feststellen.
In den Anfangszeiten meiner BI-Lösung hatte ich für jedes Feld einen Index erstellt. DIes war dann aber doch nicht von Erfolg gekrönt.
Nun verwende ich, abhängig von der Blocksize, durchaus Compound-Keys über mehrere Felder bis zu 1KB und kann meine Abfragen von z.T. mehrere Minuten auf wenige Sekunden verkürzen.

Ich würde mit von Firebird auch einen Indexadvisor wie von anderen DB's durchaus mal wünschen.
Antworten