SQL: Was genau bewirken Primärschlüssel und Indizes?

Question

Ich habe vor kurzem begonnen, meine erste ernsthafte Anwendung zu entwickeln, die eine SQL-Datenbank verwendet, und ich verwende phpMyAdmin, um die Tabellen einzurichten. Es gibt ein paar optionale "Features", die ich verschiedenen Spalten geben kann, und ich bin nicht ganz sicher, was sie tun:

• Primärschlüssel
• Index

Ich weiß, wozu eine PK dient und wie man sie verwendet, aber ich schätze, meine Frage ist, wozu man sie braucht - wie unterscheidet sie sich von der bloßen Einstellung einer Spalte auf "Unique", abgesehen von der Tatsache, dass man nur eine PK haben kann? Ist sie nur dazu da, den Programmierer wissen zu lassen, dass dieser Wert den Datensatz eindeutig identifiziert? Oder hat sie auch einige besondere Eigenschaften?

Ich habe keine Ahnung, was "Index" tut - in der Tat, die einzigen Male, die ich je gesehen habe es in Gebrauch sind (1), dass meine Primärschlüssel scheinen indiziert werden, und (2) Ich habe gehört, dass die Indizierung ist irgendwie im Zusammenhang mit der Leistung; dass Sie indizierte Spalten wollen, aber nicht zu viele. Wie entscheidet man, welche Spalten indiziert werden sollen, und was genau wird damit erreicht?

bearbeiten: sollte man Spalten indizieren, die man wahrscheinlich ORDER BY möchte?

Herzlichen Dank,

Mala

Answer 1

Der Primärschlüssel ist im Grunde eine eindeutige, indizierte Spalte, die als "offizielle" ID der Zeilen in dieser Tabelle fungiert. Am wichtigsten ist, dass er im Allgemeinen für Fremdschlüsselbeziehungen verwendet wird, d. h. wenn eine andere Tabelle auf eine Zeile in der ersten Tabelle verweist, enthält sie eine Kopie des Primärschlüssels dieser Zeile.

Beachten Sie, dass es möglich ist, einen zusammengesetzten Primärschlüssel zu haben, d. h. einen, der aus mehr als einer Spalte besteht.

Indizes verbessern die Nachschlagezeiten. Sie sind in der Regel baumbasiert, so dass die Suche nach einer bestimmten Zeile über einen Index O(log(n)) Zeit benötigt, anstatt die gesamte Tabelle zu durchsuchen.

Im Allgemeinen wird jede Spalte in einer großen Tabelle, die häufig verwendet wird, in WHERE , ORDER BY oder (insbesondere) JOIN Klauseln sollten einen Index haben. Da der Index für jede Klausel aktualisiert werden muss INSERT , UPDATE o DELETE verlangsamt sie diese Vorgänge. Wenn Sie nur wenige Schreibvorgänge und viele Lesevorgänge haben, sollten Sie nach Herzenslust indexieren. Wenn Sie sowohl viele Schreibvorgänge als auch viele Abfragen haben, die Indizes für viele Spalten erfordern, dann haben Sie ein großes Problem.

Answer 2

Der Unterschied zwischen einem Primärschlüssel und einem eindeutigen Schlüssel lässt sich am besten anhand eines Beispiels erklären.

Wir haben eine Tabelle mit Benutzern:

USER_ID number 
NAME varchar(30)
EMAIL varchar(50)

In dieser Tabelle ist die USER_ID der Primärschlüssel. Der NAME ist nicht eindeutig - es gibt eine Menge John Smiths und Muhammed Khans auf der Welt. Die EMAIL muss eindeutig sein, sonst würde das weltweite E-Mail-System nicht funktionieren. Wir haben also eine eindeutige Einschränkung auf EMAIL gesetzt.

Warum brauchen wir dann einen separaten Primärschlüssel? Aus drei Gründen:

1. der numerische Schlüssel ist effizienter wenn er in Fremdschlüsseln verwendet wird Beziehungen, da er weniger Platz benötigt
2. die E-Mail kann sich ändern (zum Beispiel Wechsel des Providers), aber der Benutzer ist immer noch derselbe; eine Änderung der eines Primärschlüsselwertes in einem Schema ist immer ein Alptraum
3. ist es immer eine schlechte Idee, die sensible oder private Informationen als einen Fremdschlüssel

Answer 3

Im relationalen Modell kann jede Spalte oder jeder Satz von Spalten, die garantiert sowohl vorhanden als auch eindeutig in der Tabelle sind, als Kandidatenschlüssel für die Tabelle bezeichnet werden. "Vorhanden" bedeutet "NOT NULL". Beim Datenbankdesign ist es üblich, einen der Kandidatenschlüssel als Primärschlüssel zu bezeichnen und Verweise auf den Primärschlüssel zu verwenden, um sich auf die gesamte Zeile oder auf den Gegenstand zu beziehen, den die Zeile beschreibt.

In SQL bedeutet eine PRIMARY KEY-Beschränkung eine NOT NULL-Beschränkung für jede Primärschlüsselspalte und eine UNIQUE-Beschränkung für alle Primärschlüsselspalten zusammen. In der Praxis stellen sich viele Primärschlüssel als einzelne Spalten heraus.

Bei den meisten DBMS-Produkten führt eine PRIMARY KEY-Beschränkung auch dazu, dass automatisch ein Index für die Primärschlüsselspalten erstellt wird. Dies beschleunigt die Überprüfungsaktivität des Systems, wenn neue Einträge für den Primärschlüssel gemacht werden, um sicherzustellen, dass der neue Wert nicht einen bestehenden Wert dupliziert. Außerdem werden Suchvorgänge auf der Grundlage des Primärschlüsselwerts und Verknüpfungen zwischen dem Primärschlüssel und einem Fremdschlüssel, der darauf verweist, beschleunigt. Wie stark die Beschleunigung ausfällt, hängt davon ab, wie der Abfrageoptimierer arbeitet.

Ursprünglich suchten die Entwickler relationaler Datenbanken nach natürlichen Schlüsseln in den gegebenen Daten. In den letzten Jahren geht die Tendenz dahin, immer eine Spalte namens ID, eine ganze Zahl, als erste Spalte und als Primärschlüssel jeder Tabelle anzulegen. Die automatische Erstellungsfunktion des DBMS wird verwendet, um sicherzustellen, dass dieser Schlüssel eindeutig ist. Diese Tendenz ist in den "Oslo Design Standards" dokumentiert. Es handelt sich nicht unbedingt um ein relationales Design, aber es erfüllt einige unmittelbare Bedürfnisse derjenigen, die es befolgen. Ich empfehle diese Praxis nicht, aber ich erkenne an, dass sie die vorherrschende Praxis ist.

Ein Index ist eine Datenstruktur, die einen schnellen Zugriff auf einige Zeilen einer Tabelle ermöglicht, basierend auf einer Beschreibung der Spalten der Tabelle, die indiziert werden. Der Index besteht aus Kopien bestimmter Tabellenspalten, den sogenannten Indexschlüsseln, die mit Zeigern auf die Tabellenzeilen durchsetzt sind. Die Zeiger sind im Allgemeinen für die DBMS-Benutzer nicht sichtbar. Indizes arbeiten mit dem Abfrageoptimierer zusammen. Der Benutzer gibt in SQL an, welche Daten gesucht werden, und der Optimierer entwickelt Indexstrategien und andere Strategien, um das Gesuchte in eine Strategie zum Auffinden zu übersetzen. Es gibt eine Art Organisationsprinzip, wie z. B. Sortierung oder Hashing, das es ermöglicht, einen Index für schnelle Suchvorgänge und bestimmte andere Zwecke zu verwenden. Dies alles ist DBMS-intern, sobald der Datenbankersteller den Index erstellt oder den Primärschlüssel deklariert hat.

Es können Indizes erstellt werden, die nichts mit dem Primärschlüssel zu tun haben. Ein Primärschlüssel kann auch ohne einen Index existieren, obwohl dies im Allgemeinen eine sehr schlechte Idee ist.