MySQL: Sinn von Primärschlüsseln/Indizes

C

cr4m0

Angesehenes Mitglied
Ich habe ein kleines Private-Nachrichten-System programmiert. Dafür gibt es eine Tabelle in MySQL:
id - INT(11) - Primärschlüssel
an - INT(11)
von - INT(11)
usw.
Wenn jetzt ein User seine PNs im Posteingang abrufen will, dann nutze ich den folgenden Befehl:
"SELECT x, y, z FROM pn WHERE an = USERID"
Meine Frage lautet nun: Kann ich diese Abfrage irgendwie durch Primärschlüssel oder Indizes beschleunigen bzw. kann ich die Last für die Datenbank und den Server reduzieren?
Oder was ist sonst der Sinn der Auswahl "Primärschlüssel/Index/Unique/Volltext"?
 
Ein Index beschleunigt das Suchen wenn nach der Eigenschaft gesucht wird.
Stell dir vor du hast einen Haufen Bücher und suchst das Buch „MySql für Einsteiger“ oder du hast die Bücher alpahbetisch geordnet, wo wirst du sie schneller finden?

Der Primary Key ist ein eindeutiger Schlüssel, kommt als Wert also nur einmal vor. Für diese Spalte ist ein Index normalerweise hinterlegt.
 
Also sollte man für alle Spalten, die häufig per "WHERE spalte = x" ausgelesen werden, einen Index anlegegen, oder? Dasselbe gilt dann für "WHERE spalte LIKE 'x'", oder? Oder sollte man da lieber Fulltext nehmen?
Beschleunigt sich die Abfrage dann automatisch oder muss man auch die Abfrage etwas ändern?
 
Ein Problem bei Indices ist, daß jeder Index die Tabellenbearbeitung verlangsamt.

Denn es muß nicht nur die Zeile, sondern auch der Index abgeglichen werden.

Bei Tabellen mit sehr vielen Änderungen sollte man also tunlichst mit Indices sparsam sein.

Dann hängt das etwas von der Tabellengröße ab. Ist die Tabelle zu klein, dann wird sie ohnehin vollständig gescannt. Wenn man da einen (unnötigen) Index definiert, dann benötigt der Optimierer länger, um einen 'passenden Plan' zu finden - und womöglich ist die Nutzung des Index dann teurer als ein Fullscan.

Deshalb: Eine Tabelle mit zwanzig Spalten und nur einem Primärschlüssel dürfte nicht gut sein, dieselbe Tabelle aber mit 20 Indices zu bestücken (pro Spalte einen) ist auch nicht sinnvoll.

Fremdschlüsselspalten sind allerdings in der Regel gute Kandidaten für Indices.

Schließlich: Die Indexkandidatenspalte sollte möglichst viele verschiedenartige Werte enthalten. Bei einer Spalte, wo es nur drei oder vier verschiedene Werte gibt, diskriminiert (= unterscheidet) ein Index zu wenig.
 
QUOTE (cr4m0 @ Mi 5.03.2008, 17:40) Ich habe ein kleines Private-Nachrichten-System programmiert. Dafür gibt es eine Tabelle in MySQL

Wenn das nur klein ist spielt ein Index nur eine marginale Rolle.

Du kannst auch mysql_unbuffered_query benutzen.
Dann werden die Daten ohne Zwischenspeicher direkt abgeliefert,
was die Auslieferung des Ergebnisses immens beschleunigt.

Diese Methode hat aber auch Nachteile.

Der nächste query kann die gleiche Tabelle erst aufmachen, wenn der unbuffered abgeliefert ist. Solange steht der inner Warteschleife. Ausserdem kann man in den Datensätzen nicht mehr navigieren.
num_rows und seek u.s.w. geht dann nicht mehr
Das ist wohl der Hauptnachteil.

Es kommt immer auf die Grösse der Tabelle und Anzahl Zugriffe an.
 
Zum Sinn eines Indizes würde ich mal, ähnlich wie von hatschi im zweiten Posting vorgeschlagen, Dir mal überlegen, wie lange Du brauchst um einen Namen in einem unsortierten Telefonbuch und in einem sortiertem mittels diesem Algo: http://de.wikipedia.org/wiki/Bin%C3%A4re_Suche zu finden. Das gibt Dir einen Anhaltspunkt über die Effizienz (Rechenaufgabe: wenn sich die Userbase verdoppelt um wieviel steigt die Anzahl der zu vergleichenden Felder).

Aber ansonsten würde ich mir nicht zu viele Gedanken machen - solange alles so läuft wie gewünscht, musst Du Dir über Optimierungen keine Gedanken machen. In Deinem Beispiel sind lediglich Integer Felder zu sortieren - das geht schnell - ich denke nicht, dass sich ein richtiger Index so schnell bemerkbar macht. Sobald Du aber eine solche Grösse erreicht hast, so dass die Anwendung nicht mehr performant läuft, kann ein kluges Datenbankdesign viel Geld sparen. Dieses zeichnet sich aber durch mehr aus, als nur durch die geeignete Indexwahl. z.B.: Kann das Feld auch negative Werte annehmen?(signed/unsigned) Was sagt EXPLAIN? Welche Caches verwendet MySQL. Relationale Datenbanken vs. Performance? Wie oft werden die Daten aktualisiert oder werden sie nur gelesen...

Fazit: lasse Deine Datenbanken mit dem Fortschritt des Projektes wachsen und zerbreche Dir am Anfang nicht den Kopf. Wenn es aber so weit ist, sei bereit auch Geld in diesen Bereich zu investieren.
 
QUOTE Fazit: lasse Deine Datenbanken mit dem Fortschritt des Projektes wachsen und zerbreche Dir am Anfang nicht den Kopf.

Die Struktur in einem bestehenden Projekt umzuarbeiten ist recht schwer, ein gutes DB-Design zahlt sich sicher aus. Da man aber immer mal beginnen muss und sein theoretisches Wissen in der Praxis verwenden kann hilft es eh nichts, Projekt machen, Fehler machen, Projekt verbessern und beim nächsten Mal schon vom Start weg besser sein.

Der Punkt von Jürgen, dass ein Index auch einen „Preis“ hat, ist sicher nicht zu vernachlässigen. Bei einem Projekt mit ordentlich Last muss man sich auch die Queries genau ansehen, einerseits kann man die zum Teil ja doch noch optimieren, aber umgekehrt kann ein Index, der von einer besonders häufigen und komplexen Query benutzt wird, ordentlich helfen.
 
Sie müssen sich einloggen oder registrieren, um hier antworten zu können.
Zurück
Oben