[MySQL] - Index aktualisieren

[anddie]

Hi,

ich habe in einer Tabelle meiner Datenbank eine Spalte, auf die ich einen Index setzen will (muss öfter nach den Inhalten gruppieren oder die mit anderen Tabellen verknüpfen).
Blöderweise ist es so, dass für diese Spalte nicht zwingend gefüllt werden muss, also NULL sein darf.
Das hat nun zur Folge, dass mit jeder Zeile, wo in der Spalte NULL steht, die Kardinalität des Indexes um 1 steigt. So alle 2 Tage, wenn ich dran denke, lösche ich den Index (da ist die Kardinalität meist zwischen 1500 und 3000) und setze ihn neu (dann ist die Kardinalität wieder brav bei 50).

Da täglich ein Script über die Datenbank rennt und ein "OPTIMIZE TABLE" ausführt, wäre ich jetzt für einen Befehl dankbar, der den Index auch gleich mit aktualisiert (sozusagen ein "REPAIR INDEX" oder so).
Gibt es da was?

anddie

 

[ice-breaker]

die Kardinalität ist eine in MySQL eingebaute Funktion (meine jedoch nur in InnoDB genutzt?) um die Anzahl (verschiedener?) Datensätze pro Spalte zu speichern, um weitere Performance herauszuholen.
Willst du den neu aufbauen lassen musst MySQL anweisen die Tabelle neu zu analysieren (ANALYZE TABLE)

 

[ZeroCCC]

Blöderweise ist es so, dass für diese Spalte nicht zwingend gefüllt werden muss, also NULL sein darf.
Das hat nun zur Folge, dass mit jeder Zeile, wo in der Spalte NULL steht, die Kardinalität des Indexes um 1 steigt. So alle 2 Tage, wenn ich dran denke, lösche ich den Index (da ist die Kardinalität meist zwischen 1500 und 3000) und setze ihn neu (dann ist die Kardinalität wieder brav bei 50).

Und das bringt dir was? Den Wert nutzt Mysql unteranderm um zu bestimmen welcher Index das meiste bringt. Wenn du meinst dass du das besser weißt als Mysql dann nehm USE INDEX.

 

[anddie]

Willst du den neu aufbauen lassen musst MySQL anweisen die Tabelle neu zu analysieren (ANALYZE TABLE)

Leider nicht, aber das hat mich auf die Idee mit einem "REPAIR TABLE" gebracht und das hat dann funktioniert.

Und das bringt dir was? Den Wert nutzt Mysql unteranderm um zu bestimmen welcher Index das meiste bringt. Wenn du meinst dass du das besser weißt als Mysql dann nehm USE INDEX.

Wenn ich in der Spalte folgende Werte eintrage A, B, A, B. Dann habe ich eine Kardinalität von 2. Wenn ich jetzt A, B, A, B, NULL eintrage, dann ist die Kardinalität 3. Wenn jetzt A, B, A, B, NULL, NULL, NULL, NULL drin steht, sollte nach meinem Verständnis die Kardinalität immer noch bei 3 sein. Sie ist dann aber bei 6. Und das ist meiner Meinung nach halt falsch und ich fürchte, das führt dann auch zu Performance-Problemen.

anddie

 

[ice-breaker]

Wenn ich in der Spalte folgende Werte eintrage A, B, A, B. Dann habe ich eine Kardinalität von 2. Wenn ich jetzt A, B, A, B, NULL eintrage, dann ist die Kardinalität 3. Wenn jetzt A, B, A, B, NULL, NULL, NULL, NULL drin steht, sollte nach meinem Verständnis die Kardinalität immer noch bei 3 sein. Sie ist dann aber bei 6. Und das ist meiner Meinung nach halt falsch und ich fürchte, das führt dann auch zu Performance-Problemen.

MySQL weiß in 90% der Fällen sehr gut, welchen Index er nehmen muss, nur in Randbedingungen mit Joins kann es mal Probleme geben. Mit einem Analyze Table weist man MySQL dann an die Daten über die Entscheidung des Indexes zu aktualisieren, wenn der nichts an deiner Kardinalität ändert, dann hat diese mit der Performance nichts zu tun.

 

[ZeroCCC]

Wenn ich in der Spalte folgende Werte eintrage A, B, A, B. Dann habe ich eine Kardinalität von 2. Wenn ich jetzt A, B, A, B, NULL eintrage, dann ist die Kardinalität 3. Wenn jetzt A, B, A, B, NULL, NULL, NULL, NULL drin steht, sollte nach meinem Verständnis die Kardinalität immer noch bei 3 sein. Sie ist dann aber bei 6. Und das ist meiner Meinung nach halt falsch und ich fürchte, das führt dann auch zu Performance-Problemen.

Ja nach deinem Verständnis, aber du weißt nicht wie es wirklich funktioniert. Ich hab auch keine ahnung wie sich die Kardinalität zusammensetzt. Ich weiß nur das Mysql diesen Wert unteranderem dafür verwendet ein optimalen Index für eine Abfrage zu finden. (damit auch die JOIN Reihnfolge) Und das macht Mysql eigentlich recht gut. Ich hab bis jetzt nur 2 Queries gehabt wo ich manuel eingreifen musste weil Mysql mein Query "deoptimiert" hatte.

Ansonsten falls es dich beruhigt... ich hab grade mal in ein paar Tabellen angeschaut. Die Kardinalität entspricht scheinbar nie wirklich der einzigartigen Werte in der Spalte. (jedenfalls bei dennen wo man nen paar mehr Werte hat) In einer Tabelle hab ich ein Index mit einer Kardinalität von 104.962, aber "DISTINCT spalte" ergibt 130.054 Datensätze, bei insgesammt 10.811.125 Datensätzen. (starkes wachstum, 15.000-30.000 DS pro Tag, so gut wie keine Updates)
In einer anderen Kardinalität 13.644, DISTINCT 16.842, gesamt 29.851. (20-100 DS pro Tag, bis zu 10.000 Updates am Tag)
Und bei anderen Indezien verhält es sich auch kaum anders. Die einzigen Indizien die mit den einzigartigen Werten übereinstimmen sind welche die sehr weing Werte haben. (PS und Uniquies hauen auch hin)
Performance Probleme hab ich aber in keinster weiße. Von daher lass die Zahl Zahl sein. Solang alles so Funktionert wies soll ist doch alles bestens, da brauch man ja nicht auf zwag das ganze kapput fuschen

 

[anddie]

Ansonsten falls es dich beruhigt... ich hab grade mal in ein paar Tabellen angeschaut. Die Kardinalität entspricht scheinbar nie wirklich der einzigartigen Werte in der Spalte. (jedenfalls bei dennen wo man nen paar mehr Werte hat)

Bei mir halt schon, bis auf die eine Tabelle, wo es da NULL gibt. Und das sind eigentlich alles relativ große Datenbanken (insgesamt um die 6GB).
Allerdings werden bei uns auch täglich jede Menge alte Daten gelöscht und danach ein OPTIMIZE TABLE ausgeführt.

Aber ich werd das jetzt einfach mal so lassen und bissl beobachten. Mal sehen, wann die DB mit spinnen anfängt.

anddie

 

[ice-breaker]

Neija wenn das 6GB sind würde ich lieber über ein gescheiteres Datenbankdesign zur Performanceverbesserung nachdenken statt mir Sorgen um die Kardinalität machen

 

[anddie]

Neija wenn das 6GB sind würde ich lieber über ein gescheiteres Datenbankdesign zur Performanceverbesserung nachdenken statt mir Sorgen um die Kardinalität machen

Sind ja schon 3 Datenbanken, auf die sich das aufteilt. Und die Normalform ist auch brav eingehalten. Und genau deswegen mach ich mir Gedanken um meine Indexe, da ich die für die ganzen Joins benötige.

Ok, der Server langweilt sich trotzdem den halben Tag, aber bei einigen Abfragen braucht er schon mal ne Weile (vor allem tagsüber).

anddie

 

[ZeroCCC]

Ok, der Server langweilt sich trotzdem den halben Tag, aber bei einigen Abfragen braucht er schon mal ne Weile (vor allem tagsüber).

Wenns an irgendwelchen Indizien liegt erkennt man das schnell mit EXPLAIN. Die Statusvariablen sind unter umständen auch aufschlussreich.

Anonsten gilt eine auf die Hardware abgestimmte konfiguration der Datenbank und gezielte Denormalisierung. Es gibt Sachen die sind in der Normalform untragbar.

 

[ice-breaker]

Und die Normalform ist auch brav eingehalten.

na kein Wunder, dass die Datenbank nen Performance-Fresser ist, weisst du was Joins an Zeit kosten?
Eigentlich laufen keine großen Seiten auf Normalformen sondern einem Mischmasch, eben einem Misch wie man am wenigsten Tabellen verknüpfen muss.

 

[anddie]

na kein Wunder, dass die Datenbank nen Performance-Fresser ist, weisst du was Joins an Zeit kosten?

Ich kann es bei vielen Sachen nicht beeinflussen, da die Applikationen so in die Datenbank loggen.
Aber wie gesagt, ist ne lustige SUN (ok, nur ne V240), die langweilt sich aber trotzdem die meiste Zeit.

anddie

 

[ice-breaker]

Dann würde ich mal den MySQL slow-query-log anmachen und auch sonstige analyse-tools vom OS laufen lassen um zu gugn woran es in diesen wenigen momenten liegt.

 

[anddie]

um zu gugn woran es in diesen wenigen momenten liegt.

Naja, da gibts mehere Probleme.
1. Die Momente sind zu selten und nicht wirklich reproduzierbar
2. Ich habe auf den Servern nicht die Rechte dafür (bekomme die auch net)
3. Vielleicht bin ich verwöhnt und empfinde alles, was länger als 5 Sekunden dauert schon als lang.

anddie