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).
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.

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 "gespeicherter Abfrage":

• Views sind immer so aktuell, wie die dahinter liegenden Tabellen, die durch beliebig viele Joins eingebunden werden können.
• In einem View können Abfragen aller Art durchgeführt werden.
• Views sind in erster Linie dafür gedacht, Daten auszulesen. Von einer Veränderung des Datenbestandes (INSERT, UPDATE, DELETE) ist in Views abzuraten.
• Views erscheinen dann 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.
• 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.
• Ein View kann - wie eine Tabelle - gelöscht werden: DROP VIEW viewname;

• 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 ...
INNER JOIN ...
WHERE ...;
(WHERE-Klausel optional)


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