Ich habe eine Abfrage von SQLAlchemy ORM generiert. Es soll stream_items für einen bestimmten Kurs zusammen mit all seinen Teilen - Ressourcen, Inhaltstextblöcke usw. und den Benutzern, die sie gepostet haben, abrufen. Diese Abfrage scheint jedoch extrem langsam zu sein. Sie benötigt Minuten in unserer Produktionsdatenbank mit etwa 20.000 Benutzern in der Datenbank, etwa 25 stream_items für den Kurs und ein paar Inhaltstextblöcke pro stream_item. Beachten Sie, dass außer den Benutzern in der Datenbank nur sehr wenige andere Datensätze vorhanden sind, da wir viele Benutzer, aber sehr wenig Inhalt importiert haben.
Bearbeiten: Beachten Sie, dass jede Objekt-ID ein Fremdschlüssel in der Tabelle franklin_object ist.
Ich habe versucht, die Abfrage zu betrachten, und habe einige beunruhigende Bits identifiziert (mit Blick auf den EXPLAIN-Ausgang)
Allerdings weiß ich wirklich nicht, was ich mit diesen beiden tun soll, besonders mit den letzten beiden.
Hier ist die Abfrage:
%Vor%Und der EXPLAIN-Ausgang:
%Vor%Wie reduziere ich die ALL-Abfragen auf etwas schneller? Welche anderen Möglichkeiten kann ich beschleunigen?
Ist franklin_objects ein Antipattern? Die Funktionsweise ist so, dass die Tabelle franklin_object zwei Spalten hat: id und type. Dann ist jeder Typ eine Tabelle mit einem Primärschlüssel, der ein Fremdschlüssel in franklin_object ist.
Der Code, der das sql erzeugt, ist etwas in der Art von:
stream_item_query = StreamItem.query.options(db.joinedload('stream_items'),db.joinedload('contents_included_in'),db.joinedload('contents.resources'),db.joinedload('contents.objects'),db.subqueryload('likers'))
stream_items = stream_item_query.filter(StreamItem.parent_id == community_id).order_by(db.desc(StreamItem.stream_sort_at)).all()
Wow, dieser verletzte mein Gehirn ein wenig. Der Versuch, herauszufinden, was die Abfrage macht, was alle Tabellen sind und die Beziehungen waren mühsam. Wenn Sie eine ähnliche Erfahrung gemacht haben, ist dies der erste Hinweis, dass Sie wahrscheinlich zu viel in dieser einzelnen Abfrage versuchen.
Ich schlage vor, Ihren gesamten Ansatz zu überdenken.
SQLAlchemy ist ein ziemlich nettes Werkzeug, und ich werde es nicht (oder Ihre Wahl von mysql) bash, aber wie bei den meisten ORM-Tools müssen Sie die Kosten mit ihrer Verwendung berücksichtigen. Ein Beispiel ist dieses franklin_object -Tabellengeschäft. Ist das ein Anti-Muster? Ja und Nein. Es ergibt einen Sinn aus rein äußerer Perspektive. Sie können bestimmen, welche Tabellen abzufragen sind, indem Sie in dieser Tabelle nach id suchen. Aus einer relationalen Abfrageperspektive erfüllt es sehr wenig Zweck. Ich könnte jede Instanz von franklin_object aus Ihrer Abfrage entfernen und nichts als ... die Spalten von franklin_object verlieren. Wenn das eine praktikable Option ist, würde ich das sofort tun.
Betrachten wir diese Verknüpfung mit franklin_object weiter. Mit Blick auf die Unterabfragen haben sie alle die gleiche Form:
Es gibt nicht viele Informationen für die Datenbank, so weit Sie diesen Teil der Abfrage unabhängig von Statistiken optimieren können. Wenn franklin_object durch Angabe von type in einer where -Klausel eingeschränkt würde, wäre die Abfrage möglicherweise besser. Könnte sein.
Dies ist besonders problematisch mit der Tabelle USER, da diese Tabelle viele Datensätze enthält (so sagen Sie). Da Sie die meisten Spalten abfragen und der Optimierer nicht genau ermitteln kann, wie viele Zeilen abgerufen werden, ist es sinnvoll, einen vollständigen Tabellenscan durchzuführen. In deinem Fall, zweimal.
Ein weiterer Aspekt ist die schiere Anzahl der Joins. Wenn wir alle Referenzen franklin_object herausnehmen, gibt es immer noch 11 Joins. Das ist nicht schlimm, wenn Ihr Datenmodell relationaler ist, aber das ist es nicht. Die generierte Abfrage gibt der Datenbank keine große Hilfe, um herauszufinden, wie die Abfrage am besten durchgeführt werden kann, und macht daher keine gute Arbeit. Vielleicht könntest du das mit Hinweisen und so weiter mildern, aber ich wette, dass dich das auf lange Sicht beißen wird.
Sie benutzen ein ORM-Werkzeug, also wirklich . Sie erhalten nichts, wenn Sie eine so große Anfrage auf einmal erledigen. Es könnte ein bisschen für die Leistung aufgeteilt werden. Führen Sie Lazy-Retrieves durch, um große, komplizierte Abfragen zu vermeiden. Ich würde sagen versuchen, nur um zu sehen, wie es geht, alles faul zu machen. Leistung wird wahrscheinlich in Ordnung sein, würde ich besser sagen. Nicht großartig, wahrscheinlich nicht einmal akzeptabel, aber besser als in der Lage zu sein, Kaffee zu holen, während die Datenbank aufrüttelt.
Dann fang an, die Dinge in stromlinienförmige Stücke zusammenzufügen. Verknüpfen Sie logisch sinnvolle Objekte wie resource und contents_resources . Ein anderes Beispiel, die Verbindung zwischen stream_item , likers und user ist doppelt vorhanden. Machen Sie diese eine Abfrage und lassen Sie SQLAlchemy seine Sache machen.
Als letzten Ausweg könnte eine Art Caching-Mechanismus implementiert werden. Vielleicht die Tische irgendwo denormalisieren. Auf einem sich langsam ändernden, readlastigen System könnten Sie diese Tabellen in eine andere Struktur einspeisen, in der die Abfragen einfach und schnell sind. Das heißt, die Verarbeitung im Voraus durchzuführen und in einer einzigen Tabelle zu speichern.
Viel Glück
Tags und Links sql mysql sqlalchemy orm