Index vorteilhaft gestalten

M

MXYPTLK

Benutzer
Beiträge
10
Hallo,

ich versuche möglichst nutzbare und performante Indizes anzulegen. Dazu habe ich eine grundelegende Frage.

Mir begegnen oft Datenbanken, in denen ein PrimaryKey exitiert und der als gruppierter Index verwendet wird. Dazu weitere Indizes zu jeweils nur einem Feld der Tabelle.

Vielleicht mal an einem Beispiel:

Es gibt eine Tabelle KUNDEN mit dem PK "ID", dazu weitere Indizes (Nachname, Ort, Postleitzahl). In den nicht gruppierten Indizes wird aber nur das jeweilige Tabellenfeld eingebunden - und bspw. nicht der PK. Ist das sinnvoll? Ich meine, in den jeweiligen Indes müsste auch die ID aufgenommen werden, denn nur so existiert eine Verbindung vom Indexeintrag zum DAtensatz der Tabelle.

Denke ich da völlig falsch?


Eine weitere Frage noch: Wie kann man Tabellen reorganisieren? eine Reorg von Indizes ist mir klar.

Bin für jede Hilfe sehr dankbar.

Gruß
Mxyptlk
 
Werbung:
Wenn Du z.B. eine Tabelle mit ID (Primary Key) und Name hast, und Du machst einen Index auf (ID, Name) und Deine Abfrage ist WHERE name = 'Mustermann', wird Dir der Index nichts nützen
 
akretschmer schrieb:
Wenn Du z.B. eine Tabelle mit ID (Primary Key) und Name hast, und Du machst einen Index auf (ID, Name) und Deine Abfrage ist WHERE name = 'Mustermann', wird Dir der Index nichts nützen
Zum Vergrößern anklicken....
Um das mal kurz zu zeigen:

Code: 
test=# drop table demo;
DROP TABLE
test=# create table demo(id int generated always as identity primary key, zahl int);
CREATE TABLE
test=# insert into demo (zahl) select * from generate_series(1,1000000) z;
INSERT 0 1000000
test=# explain analyse select * from demo where zahl = 42;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..11133.59 rows=5000 width=8) (actual time=1.344..52.618 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on demo  (cost=0.00..9633.59 rows=2083 width=8) (actual time=25.426..41.655 rows=0 loops=3)
         Filter: (zahl = 42)
         Rows Removed by Filter: 333333
 Planning Time: 0.337 ms
 Execution Time: 52.768 ms
(8 rows)

test=# create index idx_id_zahl on demo(id, zahl);
CREATE INDEX
test=# explain analyse select * from demo where zahl = 42;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..10633.43 rows=1 width=8) (actual time=0.215..35.388 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on demo  (cost=0.00..9633.33 rows=1 width=8) (actual time=14.845..25.900 rows=0 loops=3)
         Filter: (zahl = 42)
         Rows Removed by Filter: 333333
 Planning Time: 0.461 ms
 Execution Time: 35.450 ms
(8 rows)

test=# create index idx_zahl on demo(zahl);
CREATE INDEX
test=# explain analyse select * from demo where zahl = 42;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Index Scan using idx_zahl on demo  (cost=0.42..8.44 rows=1 width=8) (actual time=0.036..0.050 rows=1 loops=1)
   Index Cond: (zahl = 42)
 Planning Time: 0.262 ms
 Execution Time: 0.125 ms
(4 rows)

test=#

Also: Sinn und Unsinn von Indexen hängt auch immer von der Abfrage ab.
 
>Wenn Du z.B. eine Tabelle mit ID (Primary Key) und Name hast, und Du machst einen Index auf (ID, Name) und Deine Abfrage ist WHERE name = 'Mustermann', wird Dir der Index nichts nützen


Kannst du mir erklären warum nicht? Warum würde / wäre das Feld ID im Index mit dem Namen überflüssig?

Mir leuchtet ein, dass der Name im Index gefunden wird, wo ist aber der Verweis aud die ID in den Tabellendaten?
 
wenn Du einen Index über (a,b,c,d,e) hast, und Deine Where-Condition beinhaltet a, dann kann er genutzt werden. Wenn aber im Where nur c,d,e ist, dann nicht. In der Reihenfolge der Indexdefinition müssen die ersten ersten Spalten auch im Where sein.

Dazu kommt: wenn Du einen PK hast, brauchst Du auch nur nach dem PK zu suchen. In der Regel suchst Du aber nicht nach dem Wert des PK, sondern, wie im Beispiel, nach einer Zahl. Im Index hast Du dann einen Zeiger bzw. Verweis auf die Zeile der Tabelle (physische Location der Tabelle), im Beispiel PostgreSQL (was ich verwende) wäre das die CTID-Spalte (das ist eine verstecktem aber durchaus aufrufbare Spalte:

Code: 
test=# select * from demo where zahl = 42;
 id | zahl 
----+------
 42 |   42
(1 row)

test=# select ctid, * from demo where zahl = 42;
  ctid  | id | zahl 
--------+----+------
 (0,42) | 42 |   42
(1 row)

test=#

(0,42) bedeutet: physischer Block 0 der Tabelle, 42. Position/Datensatz in diesem Block.

In anderen DB-Systemen mag das anders realisiert sein.
 
Ich kann deinen Gedankengang nachvollziehen: Du betrachtest den Index wie z.B. ein Stichwortverzeichnis am Ende eines Buches. Du suchst das Stichwort und am Ende der Zeile steht die Seitenangabe. Diese Seitenangabe ist für die DB aber nicht der PK, das macht die DB intern. Du definierst nur Attribute nach denen Indizes angelegt werden aber nicht, wie vom Indexeintrag auf den Datensatz geschlossen wird.

Die Tabellen werden nicht "reorganisiert", sie haben keine Reihenfolge. Ein Index schon, Tabellen hängen neue Informationen immer nur an.
 
Werbung:

Ähnliche Themen

Andreas Weichert
Optimaler Clustered Index
Antworten
2
Aufrufe
152
ukulele
ukulele
Zurück
Oben