Performance: Umstrukturierung der DB (ids, spaltenanzahl)

[dunas]

Hallo,

ich habe eine Frage zur Optimierung meiner Datenbankstruktur. Ich arbeite zur Zeit an einer Webapplikation mit Spring, Spring Security und Hibernate. Ich nutze den MySQL Server 5.1.
Ich habe meine Frage auch schon in einem SpringForum gestellt, doch dort riet man mir ein mySQl Forum aufzusuchen.

Folgendes Problem: Als ich vor einem Jahr mit meinem Projekt begann, recherchierte ich den von Spring empfohlenen Aufbau der User und Authorities Tabelle (für den Login). Ich fand folgende Konfiguration:

create table users (
	username varchar(50) NOT NULL, 
	password varchar(50) NOT NULL, 
	enabled boolean NOT NULL, 
	primary key(username)) 
	ENGINE=InnoDB;

create table authorities (
	username varchar(50) NOT NULL, 
	authority varchar(50) NOT NULL, 
	primary key(username), 
	foreign key(username) references users(username) 
	ON DELETE CASCADE ON UPDATE CASCADE) 
	ENGINE=InnoDB;

Diese Konfiguration funktioniert auch, aber heute habe ich mir Gedanken darüber gemacht, die Performance zu verbessern und deshalb IDs zu benutzen. Deshalb habe ich erneut recherchiert und auf einmal bin ich auf die aktuelle(!) von Spring empfohlene Konfiguration gestoßen:

 create table users(
      username varchar_ignorecase(50) not null primary key,
      password varchar_ignorecase(50) not null,
      enabled boolean not null);

  create table authorities (
      username varchar_ignorecase(50) not null,
      authority varchar_ignorecase(50) not null,
      constraint fk_authorities_users foreign key(username) references users(username));
      create unique index ix_auth_username on authorities (username,authority);

Deswegen habe ich mehrere Fragen: Einmal zum Nutzen der IDs und einmal zu dieser, von meiner abweichenden, Konfiguration:

1. Die Änderungen in der Authorities-Tabelle kann ich doch ignorieren, oder? Ich habe nur jeweils EINE Rolle per Username und es darf keine mehrfachen Einträge für einen usernamen dort geben (primary key). Deshalb brauche ich keine unique indexes, wie in der neuen Konfiguration vorgeschlagen, das sehe ich doch richtig? (Sorry, bin nicht so der Profi :-))

2. Bei Users wurde varchar_ignorecase(50) hinzugefügt. Diese Eigenschaft kann ich für MySQL gar nicht finden, sie existiert dort nicht, richtig? Wenn sich ein User einloggt, macht es bei mir zur Zeit keinen Unterschied, ob man user oder uSer (etc) eintippt. Ich denke, dieses ignorecase sollte das gleiche erreichen, von daher kann ich es sicher auch vernachlässigen?

3. Zu den IDs:
Da es in den Tabellen keine IDs gibt, muss ich in jeder anderen Tabelle, die auf den User verweist, immer den usernamen verwenden. (Zb Userinformation(username, email, ....))
Würde man IDs nehmen, würde dies doch auch die Performance verbessern, oder sehe ich das falsch? Ich könnte mir deshalb vorstellen, dass es Sinn machen würde, die Konfiguration folgendermaßen zu ändern:

create table users (
	id int NOT NULL AUTO_INCREMENT,
	username varchar(50) NOT NULL, 
	password varchar(50) NOT NULL, 
	enabled boolean NOT NULL, 
	primary key(id), unique(username)) 
	ENGINE=InnoDB;

der username wäre immer noch unique, aber pk wäre die id. mit dieser könnte man dann in den anderen tabellen arbeiten:

create table authorities (
	userid int NOT NULL, 
	authority varchar(50) NOT NULL, 
	primary key(userid), 
	foreign key(userid) references users(id) 
	ON DELETE CASCADE ON UPDATE CASCADE) 
	ENGINE=InnoDB;

das würde eine menge chars einsparen, würde dies nicht die performance verbessern?

wäre nett, wenn mir jemand antworten würde :-)

im übrigen, ist es nicht auch so, dass sich die performance verbessert, wenn man nicht nur kürzere einträge, sondern auch weniger spalten pro tabelle hat?
ich habe zb für den Geburtstag innerhalb der userinformationstabelle die spalten: day, month und year. Wenn ich sie durch die Tabelle "birthday" (type date) ersetze, würde das die performance verbessern?


vielen Dank für eine Beantwortung meiner Fragen! :-)

[dimitri]

Zu 1.: Wenn Du dich in einer Multiuserumgebung bewegst, kann es immer passieren, dass ein anderer User die gleichen Daten eingibt und diese dann doppelt in der DB vorhanden sind.
Sofern also Eindeutigkeit benötigt wird, muss immer auch ein Unique Constraint angelegt werden, auch wenn es fachlich "ja nicht vorkommen kann" - es wird irgendwann passieren.

Zu 2.: Vermutlich. Wenn Gross-/Kleinschreibung egal ist, kann man das im Programm bzw. über SQL entsprechend ablegen. Dort sieht es dann auch ein anderer Entwickler auf den ersten Blick.

Zu 3: Bezüglich des geringeren Platzbedarfes hast Du natürlich recht - ob Du diesen Effekt bemerken wirst weiß ich nicht. Aber wenn Du einige tausen Transaktionen pro Minute durchführst könnte das schon ein wenig was bringen.
Davon abgesehen ist es einfach nicht richtig den Usernamen als PK zu verwenden, da der Username ein fachlicher Wert ist. Ein fachlicher Wert darf aber nie als PK verwendet werden.

im übrigen, ist es nicht auch so, dass sich die performance verbessert, wenn man nicht nur kürzere einträge, sondern auch weniger spalten pro tabelle hat?

Auch das ist theoretisch richtig.

ich habe zb für den Geburtstag innerhalb der userinformationstabelle die spalten: day, month und year. Wenn ich sie durch die Tabelle "birthday" (type date) ersetze, würde das die performance verbessern?

Unabhängig von der Performance ist es unsinnig ein Datum in seine Bestandteile aufzutrennen und sich damit jeglicher Datumsarithemtik zu berauben, die die Datenbankentwickler eingebaut haben.

Dim

[dunas]

herzlichen Dank für deine Antwort. Ich werde also alle von mir gemachten vorschläge umsetzen, da sie ja alle sinnvoll erscheinen. nur eine sache habe ich nicht verstanden.

Zu 1.: Wenn Du dich in einer Multiuserumgebung bewegst, kann es immer passieren, dass ein anderer User die gleichen Daten eingibt und diese dann doppelt in der DB vorhanden sind.
Sofern also Eindeutigkeit benötigt wird, muss immer auch ein Unique Constraint angelegt werden, auch wenn es fachlich "ja nicht vorkommen kann" - es wird irgendwann passieren.

wie soll das passieren? vor einer registration wird ja auch überprüft, ob der username schon vorhanden ist. leuchtet mir nicht ein. wenn genau gleichzeitig registriert wird, oder wie?
abgesehen davon: momentan ist der username der primary key => also keine doppelten einträge möglich
und in meinem zweiten vorschlag hatte ich username: unique eingefügt. wäre dann doch auch kein problem, oder? :-)

[dimitri]

wie soll das passieren? vor einer registration wird ja auch überprüft, ob der username schon vorhanden ist. leuchtet mir nicht ein. wenn genau gleichzeitig registriert wird, oder wie?

Genau gleichzeitig nicht, aber das ganze läuft ja wie folgt ab:
T1: Prüfung, ob der eingegebene Username vorhanden ist
T2: Prüfung ist ok, Username wird per INSERT eingetragen
T3: Commit;

Zwischen T2 und T3 steht der Name zwar schon in der DB ist aber noch nicht comittet kann also von einer anderen Session noch nicht gesehen werden. Hier würde die Prüfung also ebenfalls das Ergebnis bringen, dass der username noch frei ist obwohl er kurz darauf comittet und für alle sichtbar ist.

Bei einer Handvoll User ist die Wahrscheinlichkeit gering, aber je mehr User und je länger die Anwendung läuft desto höher die Wahrscheinlichkeit.

und in meinem zweiten vorschlag hatte ich username: unique eingefügt. wäre dann doch auch kein problem, oder? :-)

Klar. Evtl. hab ich Dich da dann falsch verstanden.

Dim

[dunas]

Du hast natürlich recht - danke für die Anregung, das muss ich im Hinterkopf behalten. Habe noch nicht so viel Erfahrung. :-)

[dunas]

hm, eine Sache ärgert mich noch. Jetzt, wo ich die Ids eingefügt habe, muss ich immer doppelt so viele Abfragen machen wie vorher. Von meiner Webapplikation bekomme ich immer den usernamen, wenn der user etwas will. Vorher habe ich nun einfach den entsprechenden Eintrag für den usernamen abgefragt. heute muss ich mir erst die Id holen aus der Tabelle users und dann die anderen Tabellen mit der Id abfragen. Ich bin mir nicht sicher, ob das nun von Vorteil ist. :-(

[Falk Prüfer]

Hallo,
warum erst die ID holen? Warum zwei Schritte?
Verwende einfach einen Join über die Tabellen.

Gruss Falk