[MySQL 5.1.40] DB design best practices

[BaseBallBatBoy]

Hallo!

Ich entwerfe eine Datenbank von Grund auf neu. Da ich bei diesem Neudesign komplette Freiheit habe, möchte ich diese selbstverständlich so gut wie möglich gestalten.

Daher die Frage an euch: was sind die "best practices" im DB design? Eigene Erfahrungen, Tipps und evtl. Links zu entsprechenden Seiten (Englisch ist auch OK)?

Hinweis: Die DB wird ähnlich viele read wie write Operationen verkraften müssen.

Ausserdem habe ich auch noch einige ganz spezifische Fragen:
- Ich habe eine Tabelle "countries". Diese besteht aus dem "country_code" (dreistelliger ISO Ländercode, ISO 3166-1 alpha-3) sowie dem "country_name". Nun meine Frage: wie sinnvoll ist es country_code, also ein Textfeld, als PK zu verwenden auch wenn dieser code garantiert unique ist? Oder sollte man aus Performancegründen für einen PK immer int wählen? Dh. in diesem Fall eine zusätzliche Spalte "id"? Was ist der Unterschied?
- Ich werde meine DB normalisieren (3NF). Das hat zur Folge, dass Datenbankabfragen oft viele joins beinhalten werden. In diesem Fall habe ich an eine View gedacht. Meine Frage: Wie gut ist die Performance einer View verglichen zu einer Abfrage die ich selber zusammenstelle?
- Wenn eine View joins beinhaltet, kann man kein insert darauf ausführen. Gibt es trotzdem einen einfachen Weg, Daten in die betroffenen Tabellen einzufügen ohne diese einzeln anzusprechen? Stored procedures?
- Korrekter Gebrauch von indexes? Wie eintscheidet man welche Spalten man indexiert und welche nicht?
- Ich werde eine Spalte mit einem Freitext haben. Diesen Freitext werde ich mit einem like '%keyword%' nach einem keyword durchsuchen. Gibt es eine Methode um diese Suche nach einem Keyword irgendwo im Text zu beschleunigen?

PS: Mein RDBMS ist MySQL 5.1.40, ENGINE=InnoDB, DEFAULT CHARSET=latin1

Gruss
BBBB

 

[Yaslaw]

Viele Fragen. Ich hab mal einige Antworten zusammengestellt. Der Weisheit letzter Schluss ist es aber sicher auch nicht.

- Ich habe eine Tabelle "countries". Diese besteht aus dem "country_code" (dreistelliger ISO Ländercode, ISO 3166-1 alpha-3) sowie dem "country_name". Nun meine Frage: wie sinnvoll ist es country_code, also ein Textfeld, als PK zu verwenden auch wenn dieser code garantiert unique ist? Oder sollte man aus Performancegründen für einen PK immer int wählen? Dh. in diesem Fall eine zusätzliche Spalte "id"? Was ist der Unterschied?

item: Der ISO-Code ist in den anderen Tabellen lesbar und muss je nachdem nicht mal über die countries-Tabelle übersetzt werden. Wenn du damit JOINs einsparen kannst, weil auf dem Report ein 'CH' genügt und nicht 'Schweiz' ausgegeben werden muss, ersparst du dir Performance weil du den JOIN nicht hast. Das hast du grad bei eindeutig lesbaren Codes häufig, dass de eine Tabelle hast um die Eingabe einzuschränken, Dropdowns abzufüllen etc, jedoch in Reports den Code ausgibst.
item: Ist wie mit den Indexen etc. Die Menge und die Art wie du nachher drauf zugreiffen musst, gibt den Ton an.

- Ich werde meine DB normalisieren (3NF). Das hat zur Folge, dass Datenbankabfragen oft viele joins beinhalten werden. In diesem Fall habe ich an eine View gedacht. Meine Frage: Wie gut ist die Performance einer View verglichen zu einer Abfrage die ich selber zusammenstelle?

item: Eine View ist eine Abfrage. MySQL sollte eigentlich in der Lage sein, den Explainplan entsprechend aufzubauen wenn man über eine Abfrage mit Bedinungen auf eine View zugreift.
item: Kommt drauf an, wie komplex nachher die zugreifenden Views sind

- Wenn eine View joins beinhaltet, kann man kein insert darauf ausführen. Gibt es trotzdem einen einfachen Weg, Daten in die betroffenen Tabellen einzufügen ohne diese einzeln anzusprechen? Stored procedures?

item: Einzel ansprechen oder Stored procedures. Alles andere ist Mumpitz. Wenn du in mehrere Tabellen je nur eine Zeile einfügen musst, ist zuviel normalisiert. Meistens hat man eine Tabelle wo ein Eintrag rein muss und Tabellen wo dann die Verknüpfungen gespeichert werden. zB. Eintrag einer Adresse: 1 Eintrag in die Tabelle Addresse, 3 Einträge in die Tabelle Kontakte (Tel, Fax, Mail, Homepage etc).

- Korrekter Gebrauch von indexes? Wie eintscheidet man welche Spalten man indexiert und welche nicht?

item: Entsprechend den Suchkriterien, den Filterkriterien, den Fremdschlüsseln.
item: Grundsätzlich gilt:
Performance-Verbesserungen sind keine eindeutige und einfache Sache. Es ist ein testen und heran tasten. Auswerten von Explainplans, SQL umschreiben - ggf. total umschreiben, Indexe anhand des Plans neu setzen, weiter testen, Anzahl Datensätze der Tabelle vergleichen, ggf. Subqueries machen um die Datenmenge so früh wie möglich klein zu kriegen etc.

- Ich werde eine Spalte mit einem Freitext haben. Diesen Freitext werde ich mit einem like '%keyword%' nach einem keyword durchsuchen. Gibt es eine Methode um diese Suche nach einem Keyword irgendwo im Text zu beschleunigen?

item: Nur wenn du eine sinnvolle Logik hast, mit der du im Vorfeld bestimmen kannst, welches Keywords sind. Dan kannst du diese mit einem Trigger beim INSERT und UPDATE aus dem Text extrahieren und in eine eigene Tabelle speichern.

 

[BaseBallBatBoy]

item: Der ISO-Code ist in den anderen Tabellen lesbar und muss je nachdem nicht mal über die countries-Tabelle übersetzt werden. Wenn du damit JOINs einsparen kannst, weil auf dem Report ein 'CH' genügt und nicht 'Schweiz' ausgegeben werden muss, ersparst du dir Performance weil du den JOIN nicht hast. Das hast du grad bei eindeutig lesbaren Codes häufig, dass de eine Tabelle hast um die Eingabe einzuschränken, Dropdowns abzufüllen etc, jedoch in Reports den Code ausgibst.
item: Ist wie mit den Indexen etc. Die Menge und die Art wie du nachher drauf zugreiffen musst, gibt den Ton an.

Gehen wir mal davon aus, dass ich auf dem Report trotzdem den kompletten Namen ausgeben muss.
So wie ich das aber verstehe scheint der Datentyp des PKs wohl nicht allzu kritisch zu sein.

item: Nur wenn du eine sinnvolle Logik hast, mit der du im Vorfeld bestimmen kannst, welches Keywords sind. Dan kannst du diese mit einem Trigger beim INSERT und UPDATE aus dem Text extrahieren und in eine eigene Tabelle speichern.

Nein, ich habe im Vorfeld keine Ahnung welches die Keywords sein könnten. Daher geht das wohl nicht schneller.
Eine Variante wär evtl. noch MATCH .... AGAINST mit einem fulltext index. Der geht aber nur bei MyISAM und nicht bei InnoDB. Müsste da wohl eine eigene MyISAM Tabellle mit einem fulltext index haben. Frag mich aber wie viel schneller das wär... Ausserdem kann man damit auch nur ganze Wörter finden.


Ausserdem habe ich mir noch was weiteres überlegt. Nehmen wir mal an wir haben ein Feld "severity" mit den, momentan, möglichen Werten "emergency", "high", "medium", "low" (ist hier nur als Beispiel zu verstehen, kann auch was ganz anderes sein). Wie würdet ihr das implementieren? ENUM, VARCHAR, INT und eine JOIN Tabelle?

Hier auch mal ein interessanter Artikel zu dem Thema: http://www.mysqlperformanceblog.com...s-varchar-vs-int-joined-table-what-is-faster/

Meine Gedanken dazu:
- Wenn man einen neuen Typ einfügen möchte wirds mit ENUM recht mühsam, bei einer JOINed table ists einfach ein INSERT
- Sollte man dieses Feld "severity" auch bei anderen Tabellen verwenden wollen, kann man die JOIN Tabelle ganz einfach wiederverwenden, ENUM und VARCHAR müsste man hingegen 'neu schreiben'
- Ich weis jedoch nicht wie sich die Performance von ENUM verhält wenn man mehr Auswahl hat (ie. 50, 100, 1000, ...)
- Momentan tendiere ich eher zu INT + JOIN einfach aus dem simplen Grund da es flexibler ist bei zukünftigen Veränderungen. Dafür ist der JOIN halt nicht gratis...

Was sind eure Gedanken dazu?

 

[Yaslaw]

ENUM würd ich nur bei eindeutigen und wenigen Werten machen. Also zum Beispiel Geschlecht. Da gibts 'm' und 'w'. Bei allem anderen verzichte ich euf Enums. Auch wenn der Kunde am Anfang behaubtet, dass es bei den 4 severities bleibt, kaum hat ers es im Einsatz will er noch 'nice to have' und wietere Zustände.
Darum würd ich auch mit ID und JOIN arbeiten

 

[BaseBallBatBoy]

ENUM würd ich nur bei eindeutigen und wenigen Werten machen. Also zum Beispiel Geschlecht. Da gibts 'm' und 'w'. Bei allem anderen verzichte ich euf Enums. Auch wenn der Kunde am Anfang behaubtet, dass es bei den 4 severities bleibt, kaum hat ers es im Einsatz will er noch 'nice to have' und wietere Zustände.
Darum würd ich auch mit ID und JOIN arbeiten

OK, schön dass ich nicht der Einzige bin der das so sieht...

PS: Das mit dem Geschlecht ist auch gar nicht so trivial wie es auf den ersten Blick erscheinen mag. Ich kenne zufälligerweise ein Beispiel aus der Medizin wo es dafür ganze fünf Typen gibt! Männlich, Weiblich, 'ehemals Männlich nun Weiblich', 'ehemals Weiblich nun Männlich' und Neutrum. Es kommt also sehr auf das Einsatzgebiet an...

 

[Yaslaw]

Da fehlt noch etwas... (ja, ich kenne so jemanden)
'ehemals weiblich nun männlich, währe gerne wieder weiblich'

 

[BaseBallBatBoy]

In der Zwischenzeit habe ich auf dem Web ein sehr gutes Buch gefunden:
http://www.inga.ba.gov.br/comites/uploads/content/file/high-performance-mysql-second-edition.pdf

Enthält einige interessante Hinweise...

Eine weitere Frage die bei mir aufgetaucht ist: wie verhält sich die Performance beim vergleichen von INT's gegenüber der Performance von TINYINT's, SMALLINT's oder MEDIUMINT's (Vergleich von gleichen Typen natürlich)? Besonders im Hinblick auf SELECT mit JOIN. Mir ist klar dass ein INT 4 Bytes kostet und TINYINT nur 1 Byte. Mich interessiert hier aber nur der Speed beim vergleichen.

 

[BaseBallBatBoy]

Hi!

Folgendes Beispielszenario: wir haben die Tabellen "Products" 1:N "Units" 1:N "Parts". Wie es die Beziehungen schon zeigen sind die mit PK und FK untereinander verknüpft. (ein Bespiel: Auto > Motor > Kolben). Jede Tabelle hat zudem ein Feld active TINYINT(1). Das wird verwendet wenn z.B. Autos nicht mehr Produziert werden. Dann kann man das Produkt inaktiv setzen und kann so das Löschen vermeiden. Wenn ich nun ein Produkt inaktiviere sollen auch all seine Einheiten und all seine Bauteile inaktiviert werden. Daher habe ich an Trigger gedacht.

Nun die Frage: Kann ein Trigger auch von einem anderen Trigger ausgelöst werden? Also löst tg_before_update_products auch tg_before_update_units aus? Und ganz allgemein: ist das eine gute Lösung?

DELIMITER $$
CREATE TRIGGER `tg_before_update_products` 
BEFORE UPDATE on `Products`
FOR EACH ROW
BEGIN
	DECLARE mychanged INT DEFAULT 0;
	IF NEW.active != OLD.active THEN
		SET mychanged = 1;
	END IF;
	IF mychanged = 1 THEN
		UPDATE Units
		SET active = NEW.active, 
		WHERE product_id = OLD.id;
	END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER `tg_before_update_units` 
BEFORE UPDATE on `Units`
FOR EACH ROW
BEGIN
	DECLARE mychanged INT DEFAULT 0;
	IF NEW.active != OLD.active THEN
		SET mychanged = 1;
	END IF;
	IF mychanged = 1 THEN
		UPDATE Parts
		SET active = NEW.active, 
		WHERE unit_id = OLD.id;
	END IF;
END$$
DELIMITER ;