Datenbank-Praxis für Fortgeschrittene

Einsatz von Indizes

Indizes sind vergleichbar mit Registern in Büchern; wird nach einem Begriff gesucht, so findet man mit Hilfe eines Registers bzw. Index recht schnell die jeweiligen Vorkommen des Begriffs.

Bei MySQL gibt es verschiedene Möglichkeiten der Indizierung (die wichtigsten 3):

• PRIMARY KEY: Der Primary Key dient zur eindeutigen Identifizierung eines Datensatzes und kommt daher meist in der ID-Spalte zum Einsatz.
• INDEX KEY: Der Index Key erscheint sinnvoll, wenn man Tabellenfelder kürzeren Inhalts als Ganzes von MySQL erfassen und verwalten lassen möchte.
• FULLTEXT KEY: Im Gegensatz zum Index Key erfasst der Fulltext Key jedes Einzelwort eines Tabellenfeldes (Bedingung: Feldtyp CHAR, VARCHAR oder TEXT); um relevante Ergebnisse zu erzielen, sollten die Einträge in den Tabellenfeldern größere Mengen an Wörtern umfassen. Es empfiehlt sich, den Index erst nach dem Befüllen einer Tabelle mit Inhalten zu setzen.

Berechnungen in MySQL

Neben der Abfrage von Daten und Veränderungen am Datenbestand ist es auch möglich, in MySQL Rechenoperationen auszuführen. Zur Verfügung stehen die üblichen Grundrechenarten (+, -, /, *). Es sind aber auch komplexere mathematische Funktionen möglich (z.B. Cosinus, Sinus), oder beispielsweise auch die Berechnung der Distanz zwischen zwei GPS-Koordinaten.
Einige Beispiele:

Rechenoperation	Beschreibung
UPDATE tabellenname SET anzahl = anzahl + 3 WHERE id = 15;	Beim Eintrag mit der ID = 15 wird der Wert in der Spalte anzahl um 3 addiert.
UPDATE tabellenname SET anzahl = anzahl * anzahl2 WHERE id = 15;	Beim Eintrag mit der ID = 15 wird der Wert in der Spalte anzahl mit dem Wert aus der Spalte anzahl2 multipliziert.
SELECT 1 / 3;	Einfache Division.
SELECT 1 + 3;	Einfache Addition.
SELECT ROUND(1/3, 2);	Das Ergebnis der Division wird durch die eingebundene Funktion auf zwei Nachkommastellen gerundet.
SELECT AVG(anzahl) AS Durchschnitt FROM tabellenname;	Ermittlung eines Durchschnittswertes in aller in der Spalte anzahl befindlichen Werte.

Gewichtete Abfragen

Der sinnvolle Einsatz von gewichteten Abfragen ist in verschiedenen Anwendungsszenarien denkbar, beispielsweise um Wahrscheinlichkeiten zu ermitteln, aber auch, um Volltextsuchen zu optimieren. Letzteres möchten wir uns in diesem Abschnitt exemplarisch näher ansehen:

Mit Hilfe des Fulltext-Index wird in der damit versehenen Tabellenspalte eine Volltextsuche ermöglicht. Diese kann sinnvollerweise in ein Skript eingebunden werden. Zum Einsatz kommen dabei die Funktionen MATCH() und AGAINST():

• MATCH(): alle Spalten, die in die Suche eingebunden werden sollen, werden in dieser Funktion, kommasepariert angegeben.
• AGAINST(): enthält den String, nach dem gesucht werden soll.

Es existieren verschiedene Suchtypen, mit denen die Art der Suche definiert wird - u.a.:

• IN NATURAL LANGUAGE MODE: Der gesuchte String wird bei diesem Suchtyp als natürliche Sprache angesehen. Vorteilhaft erweist sich, dass sämtliche Wörter, die in mehr als 50% der passenden Datensätze vorkommen, aus dem Matching-Raster fallen, da sie als zu gewöhnlich erkannt werden.
• IN BOOLEAN MODE: Der gesuchte String kann bei diesem Suchtyp bestimmte Operatoren enthalten, die definieren, ob ein Wort enthalten sein muss (+) oder ein Wort nicht enthalten sein darf (-); außerdem kann eine Gewichtung der einzelnen Worte erfolgen.

Beispiele:
SELECT * FROM historiker
WHERE MATCH (name, ort)
AGAINST ('Max Mannheimer' IN NATURAL LANGUAGE MODE);
Mit dieser Abfrage würde man erhalten:
— "Max Mannheimer"
— alle Personen die "Max/imilian" mit Vornamen heißen
— alle Personen, die an der Universität "Mannheim" unterrichten

SELECT * FROM historiker
WHERE MATCH (name, ort)
AGAINST ('Max Mannheimer' IN BOOLEAN MODE);
Die Änderung des Suchtyps würde in diesem Fall noch zu keiner Veränderung am Suchergebnis führen. Hierzu müssen noch Operatoren zum Einsatz kommen, die der Boolean Mode zulässt:

SELECT * FROM historiker
WHERE MATCH (name, ort)
AGAINST ('+Max +Mannheimer', '-Mannheim' IN BOOLEAN MODE);
Mit dieser Abfrage würde man nun nur noch einen Treffer (Max Mannheimer) erhalten.

Gewichtung einer Suchanfrage:
SELECT `tabellenspalte` FROM `tabellenname` WHERE
(
3 * (MATCH(`spalte1`) AGAINST ('suchbegriff' IN BOOLEAN MODE))
+
1.5 * (MATCH(`spalte2`) AGAINST ('suchbegriff' IN BOOLEAN MODE))
)
AS relevance HAVING relevance > 0.5 ORDER BY relevance;
In diesem Fall ist Spalte 1 am wichtigsten für die Suche, gefolgt von Spalte 2.

Unterabfragen (Subqueries) und "IN"-Operator

• In einer SELECT-Anfrage kann als sogenannte Unterabfrage (Subquery) ein weiteres SELECT-Statement eingebunden werden. Die Unterabfrage wird durch Klammerung definiert und muss einen Korrelationsnamen erhalten. Unterabfragen sind u.a. dann sinnvoll, wenn es darum geht, Join-Abfragen zu beschleunigen.

Beispiel für eine einfache Unterabfrage:
~ Ermitteln Sie alle Historiker (Vorname, Name, Gehalt) aus der Tabelle Historiker, deren Gehalt höher ist als das Durschnittsgehalt aller Personen in dieser Tabelle, sortiert nach der Höhe des Gehalts in absteigender Reihenfolge:
SELECT vorname, name, gehalt FROM Historiker
WHERE gehalt > (SELECT AVG(gehalt) FROM Historiker)
ORDER BY gehalt DESC;

• Unterabfragen können verwendet werden, um in einer WHERE-Klausel eine Werteliste mit dem logischen Operator "IN" zu erzeugen. Der IN-Operator wird verwendet, um mehrere OR-Abfragen zu bündeln, d.h. um die Abfrage in einer Spalte auf mehr als einen Abfragewert zu erweitern.

Beispiel für eine einfache Abfrage mit IN-Operator:
SELECT vorname, name, land FROM Historiker
WHERE land IN ("Deutschland", "Frankreich", "Schweiz");

Anstelle:
SELECT vorname, name, land FROM Historiker
WHERE land = "Deutschland" OR land = "Frankreich" OR land = "Schweiz");

Einsatz von Views ("gespeicherten Abfragen")

Es besteht in MySQL die Möglichkeit, sogenannte "Views" oder "Sichten" zu erstellen. Dabei handelt es sich um eine Art virtuelle Tabelle, die auf dem Ergebnis eines SQL-Statements basiert:

• Ein View besteht aus Spalten und Zeilen wie eine 'echte' Tabelle. Die Tabellenfelder sind Felder einer oder mehrerer Tabellen aus einer Datenbank.
• Views sind in erster Linie dafür gedacht, Nutzern einen beschränkten Zugang zu den Daten zu gewähren. Für die Veränderung des Datenbestandes (INSERT, UPDATE, DELETE) sind Views nicht vorgesehen.
• Views erscheinen daher sinnvoll, wenn den Anwendern eines Webinterface nur eine begrenzte Anzahl an Spalten oder Zeilen zur Verfügung gestellt werden sollen. Die Anzahl der Spalten wird durch die Auswahl der Spalten in der SELECT-Anfrage geregelt, die Anzahl der Zeilen durch die WHERE-Klausel.
• In einem View können Abfragen aller Art durchgeführt werden.
• Views sind immer so aktuell, wie die dahinter liegenden Tabellen. Die Database Engine ruft das, dem View zugrunde liegende SQL-Statement stets neu ab, wenn ein Benutzer eine Anfrage stellt, und aktualisiert damit sukzessive den im View hinterlegten Datenbestand.
• Views lassen sich gut in der Programmierung einbinden; ändert sich beispielsweise ein Tabellenname, so muss dieser nur einmal in der Datenbank korrigiert werden. Die in ein Skript eingebundenen Views bleiben davon unberührt. Diese Vermeidung von Redundanzen (Stichwort: Normalisierung) vermeidet eine unnötige Suche und daraus resultierende Fehleranfälligkeit.

• Nachteil von Views: Vorhandene Spalten-Indizes (wie der Primary Key) gehen bei der Erstellung verloren. Je nach Datenmenge kann dies zu Lasten der Performance gehen.

Anlegen eines VIEWS (Grundgerüst):
CREATE VIEW viewname AS
SELECT ...
FROM ...
WHERE ...;
(WHERE-Klausel optional)

Update eines VIEWS (Grundgerüst):
CREATE OR REPLACE VIEW viewname AS
SELECT ...
FROM ...
WHERE ...;
(WHERE-Klausel optional)

Ein View kann - wie eine Tabelle - gelöscht werden: DROP VIEW viewname;


Zurück zu Erlernen einer Datenbanksprache: Structured Query Language (SQL) II ⇐