nicht angemeldet
View mit Index
Rolf B
MudGuardIch hab da einen ziemlich komplexen Join mit mehreren WHERE Klausen. Das eine verklauselte Feld ist jedoch in der Tabelle nicht indiziert. Das könnte bei vielen Einträgen in der Tabelle zu Performanceproblemen führen, dem möchte ich vorbeugen Idee:
Ich erstelle ein View und lege in diesem View einen Index auf das besagte Feld. Frage vorab: Sorgt MySQL dann selbst dafür daß das View einschließlich Index aktuell ist? Etwa wenn die Basistabelle neue Einträge bekommt?
MFG
explain
SELECT
a.ent as entity,
b.val as datetime,
c.val as nickname,
d.val as body,
e.val as parent,
f.val as root,
g.val as subject,
h.val as thread,
i.val as timestamp,
j.val as threadroot
FROM forum a
JOIN forum b using(ent,att)
JOIN forum c using(ent)
JOIN forum d using(ent)
JOIN forum e using(ent)
JOIN forum f using(ent)
JOIN forum g using(ent)
JOIN forum h using(ent)
JOIN forum i using(ent)
JOIN forum j using(ent)
WHERE b.att = 'datetime'
AND c.att = 'nickname'
AND d.att = 'body'
AND e.att = 'parent'
AND f.att = 'root'
AND g.att = 'subject'
AND h.att = 'thread'
AND i.att = 'timestamp'
AND j.att = 'threadroot'
and h.val = 91761
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","g","ref","PRIMARY,ent,att","att","130","const",38,"Using where"
1,"SIMPLE","d","eq_ref","PRIMARY,ent,att","PRIMARY","260","myweb.g.ent,const",1,"Using where"
1,"SIMPLE","e","eq_ref","PRIMARY,ent,att","PRIMARY","260","myweb.g.ent,const",1,"Using where"
1,"SIMPLE","h","eq_ref","PRIMARY,ent,att","PRIMARY","260","myweb.g.ent,const",1,"Using where"
1,"SIMPLE","j","eq_ref","PRIMARY,ent,att","PRIMARY","260","myweb.h.ent,const",1,"Using where"
1,"SIMPLE","a","eq_ref","PRIMARY,ent,att","PRIMARY","260","myweb.d.ent,const",1,"Using where; Using index"
1,"SIMPLE","b","eq_ref","PRIMARY,ent,att","PRIMARY","260","myweb.j.ent,const",1,"Using where"
1,"SIMPLE","c","eq_ref","PRIMARY,ent,att","PRIMARY","260","myweb.a.ent,const",1,"Using where"
1,"SIMPLE","f","eq_ref","PRIMARY,ent,att","PRIMARY","260","myweb.j.ent,const",1,"Using where"
1,"SIMPLE","i","eq_ref","PRIMARY,ent,att","PRIMARY","260","myweb.j.ent,const",1,"Using where"
sieht eigentlich gut aus, kein Fullscan, nur 38 rows. Lohnt ich das view?
-
Hallo Email,
sieht eigentlich gut aus
Ich nehme an, dass Du den folgenden Kommentar schon erwartest und bereit bist, ihn zu trollen. Ich bringe ihn trotzdem.
Die spezielle Eigenschaft deines Modells ist, dass die DB-Struktur von der Struktur der Daten unabhängig ist. Die logische Datenstruktur entsteht erst durch die SQL Zugriffe. Damit entfernst Du aus deiner Implementierung jede potenzielle Optimierung, die Dir ein DBMS bieten könnte.
Sicherlich hast Du Datenmodellierung gelernt. Modelliere deine DB basierend auf deinem konzeptionellen Modell. Gerne darfst Du dann ein technisches Modell verwenden, das für Zugriffe optimiert ist. Dein technisches Modell ist allerdings für Zugriffe pessimiert.
Sorgt MySQL dann selbst dafür daß das View einschließlich Index aktuell ist?
Ein View ist das Ergebnis einer SQL Query. Rufst Du den View ab, wird unter der Haube die zugehörige Query durchgeführt. Indexe liegen auf den Tables, nicht auf dem View. D.h. ein View ist sowas wie eine virtuelle Table, die im Moment des View-Gebrauchs entsteht. Die Frage nach Aktualität ist damit nicht relevant.
Rolf
--
sumpsi - posui - clusi-
Hi,
Ein View ist das Ergebnis einer SQL Query. Rufst Du den View ab, wird unter der Haube die zugehörige Query durchgeführt.
wenn es kein materialized view ist.
cu,
Andreas a/k/a MudGuard-
Hallo Andreas,
kann MySQL die mittlerweile?
Rolf
--
sumpsi - posui - clusi-
Hallo Rolf,
kann MySQL die mittlerweile?
Nein. Man kann sie nur über Trigger und Tabellen nachbauen.
Don't do this at home, kids!
LG,
CK-
Hi,
kann MySQL die mittlerweile?
Nein. Man kann sie nur über Trigger und Tabellen nachbauen.
laut MySQL 8.0 Doku:
CREATE ALGORITHM = TEMPTABLE VIEW view_name ...cu,
Andreas a/k/a MudGuard-
Hallo MudGuard,
kann MySQL die mittlerweile?
Nein. Man kann sie nur über Trigger und Tabellen nachbauen.
laut MySQL 8.0 Doku:
CREATE ALGORITHM = TEMPTABLE VIEW view_name ...Oh. Du hast recht. Danke.
LG,
CK-
Hi,
Oh. Du hast recht.
Ich habe immer … mal wieder Recht!cu,
Andreas a/k/a MudGuard
-
-
-
-
-
-
Die spezielle Eigenschaft deines Modells ist, dass die DB-Struktur von der Struktur der Daten unabhängig ist.
Ich sags mal so: Zwischen Anwendung und Daten liegt ein Data Acces Layer der ausgetauscht werden kann ohne daß CODE geändert werden muss. So wird nur per Konfiguration der Anwendung mitgeteilt ob die in der Anwendung anfallenden Daten in einer Datei oder in einer MySQL Tabelle zu speichern sind.
Damit entfernst Du aus deiner Implementierung jede potenzielle Optimierung, die Dir ein DBMS bieten könnte.
Das ist so nicht richtig. Der Index wird ja genutzt. Wenn ein Objekt aus der DB zu lesen ist, sind es mit oder ohne JOIN gleichviel rows, nämlich soviele wie es Eigenschaften gibt.
MFG
-
Hallo Email,
Wenn ein Objekt aus der DB zu lesen ist, sind es mit oder ohne JOIN gleichviel rows, nämlich soviele wie es Eigenschaften gibt.
Das sehe ich anders. Wenn Du einen View aus 8 Tables baust, um 8 Columns zu lesen, dann muss die DB diese 8 Zugriffe auf Tablespaces machen.
Ein SQL Datenmodell, das an das Businessmodell angelehnt ist, macht weniger Zugriffe. Und jeder Zugriff kosten den Server Zeit.
Solange der DB Server die Daten komplett im RAM halten kann, wird es nicht auffallen. Der aktuelle Traffic des Self-Forums dürfte auf diese Weise noch gut handhabbar sein. Dein Forum dürfte auch nicht einbrechen. Aber Du baust Dir auf diese Weise eine technische Hypothek, die ab einem bestimmten Trafficvolumen abrupt eingefordert wird. Gerade weil dein Data Layer die Application von der DB entkoppelt, solltest Du Dir keine zusätzliche Modelltransformation hinzunehmen.
Der Index wird ja genutzt.
Nein. Die Indexe. Einer pro Table. Es ist viel mehr Overhead, den der DB-Server verarbeiten muss.
Rolf
--
sumpsi - posui - clusi-
Hallo Rolf,
Der aktuelle Traffic des Self-Forums dürfte auf diese Weise noch gut handhabbar sein.
bei über 250.000 Threads, über 1.700.000 Nachrichten und 30-120 Hits pro Minute (je nach Uhrzeit) bezweifle ich das.
Nicht, dass das sonderlich viel Traffic wäre, aber das EAV-Modell birgt echt viel Overhead…
LG,
CK -
Hallo Rolf,
Der Index wird ja genutzt.
Nein. Die Indexe. Einer pro Table. Es ist viel mehr Overhead, den der DB-Server verarbeiten muss.
Indizes sind vor allem auch keine magische Suppe, die man drüber kippt und dann ist alles schnell. Auch Zugriffe über den Index können langsam sein, z.B. weil sie nicht spezifisch genug sind.
LG,
CK -
Wenn ein Objekt aus der DB zu lesen ist, sind es mit oder ohne JOIN gleichviel rows, nämlich soviele wie es Eigenschaften gibt.
Das sehe ich anders. Wenn Du einen View aus 8 Tables baust, um 8 Columns zu lesen, dann muss die DB diese 8 Zugriffe auf Tablespaces machen.
Das kannst Du sehen wie Du willst. Für mich ist explain relevant.
Der Index wird ja genutzt.
Nein. Die Indexe. Einer pro Table. Es ist viel mehr Overhead, den der DB-Server verarbeiten muss.
Ich habe nur eine Tabelle siehe create:
CREATE TABLE forum( `ent` varchar(128) NOT NULL DEFAULT '', `att` varchar(128) NOT NULL DEFAULT '', `val` longtext, PRIMARY KEY (`ent`,`att`), KEY ent(ent), KEY att(att) ) DEFAULT CHARSET=Latin1Und somit benötigt ein Object eben für jede Eigenschaft genau eine Zeile. Exakt diese Anzahl Zeilen werden gelesen, dafür sorgt der Index und explain zeigt das auch. MFG
-
-
-
-
Habs mittlerweile selbst gemerkt, ein view kann nicht indiziert werden. Ein Performanceproblem ist jedoch noch lange nicht in Sichtweite. Also kein Grund das DB Design zu spezialisieren.
SWE
-
Hallo Email,
ich möchte das nicht mit dir diskutieren, da dich das eigentlich eh nicht interessiert. Ich möchte aber, dass eventuelle Leser sich über eines im Klaren sind:
explain SELECT a.ent as entity, b.val as datetime, c.val as nickname, d.val as body, e.val as parent, f.val as root, g.val as subject, h.val as thread, i.val as timestamp, j.val as threadroot FROM forum a JOIN forum b using(ent,att) JOIN forum c using(ent) JOIN forum d using(ent) JOIN forum e using(ent) JOIN forum f using(ent) JOIN forum g using(ent) JOIN forum h using(ent) JOIN forum i using(ent) JOIN forum j using(ent) WHERE b.att = 'datetime' AND c.att = 'nickname' AND d.att = 'body' AND e.att = 'parent' AND f.att = 'root' AND g.att = 'subject' AND h.att = 'thread' AND i.att = 'timestamp' AND j.att = 'threadroot' and h.val = 91761Dieses Datenbank-Layout ist ein Anti-Pattern. Es bringt viele Probleme mit sich für eine Flexibilität, die man höchstwahrscheinlich nie brauchen wird. Von Performance-Problemen (auf einmal sind einfache
INSERTs oderUPDATEs eine Menge Queries, auslesen ergibt so SQL-Monster wie oben zitiert, die DB kann kaum noch Annahmen darüber treffen wie zu optimieren ist, Indizes verlieren ihre Spezifität, die Datenmenge wächst enorm) über die Integrität der Daten (Fremdschlüssel, Constraints, Checks - auf einmal wird alles zu einem Problem. Im Grunde kann man nichtmal sicherstellen, dass alle Objekte einem bestimmten Schema entsprechen). Reporting wird ein echtes Problem. Für jede Spalte, die ich hinzufüge, kommt ein weitererJOINdazu.Argh. Ich bekomme Kopfschmerzen.
Es kann Gründe geben, warum man ein EAV-Design (Entity-Attribute-Value) tatsächlich benötigt, die sind jedoch sehr selten und man ist bei modernen Datenbanken dann idR immer noch besser mit JSON-Spalten aufgehoben.
Don't do this at home, kids!
LG,
CK-
Mein Forum ist immer noch performanter als das SELFFORUM.
MFG
-
Hallo Email,
Mein Forum ist immer noch performanter als das SELFFORUM.
👍 das ist schön.
LG,
CK-
Mein Forum ist immer noch performanter als das SELFFORUM.
👍 das ist schön.
Mich freut das auch. Alles Eigenbau, einschließlich des ORM für MySQL oder die Dateiversion.
Der JOIN ist ein workaround und wird nur gebraucht um einen ganzen Thread aus der Tabelle zu holen. Genau das könnte ich auch anders machen und zwar ohne die Eigenschaften im CODE namentlich zu benennen. Wenn die zu einem Thread gehörigen Entities bekannt sind, feuert ein
$dal->checkout($entity)nur noch ein prepared Statement.Aber wie Du schon sehr schön erkannt hast, das werden wir hier nicht weiter diskutieren. Nur Dein Begriff des Antipattern ist mal wieder total daneben. Aus derartigen Befangenheiten bin ich raus.
MFG
-
-
-