MySQL Index für Group By / Order By

Sehen Abfrage unten. Was index sollte ich auf den Tisch, damit die Abfrage den index verwenden und vermeiden Sie die Verwendung von temporären und filesort? Ich habe versucht viele verschiedene Kombinationen von Indizes und Lesen Beratung hier, aber ich kann nicht scheinen, um es herauszufinden. Meine erklären entweder sagt Using Where (kein index), oder Using Where Using Temporary, Using Filesort

Hier ist eine vereinfachte Abfrage. Alle Spalten, die ganze zahlen sind.

SELECT c1, Sum(c2) 
FROM table 
WHERE c3 IS NOT NULL 
AND c4 = 2011 
AND c5 = 0 
AND c6 In (6,9,11)  
GROUP BY c1
  • Beachten Sie, dass MySQL kann nur ein index pro Tabelle, so, wenn Sie einen index auf eine Spalte(s) optimieren Sie die GRUPPE, INDEM Sie könnten am Ende verletzen sich selbst, wie die WHERE-Klauseln sind nicht optimierten und letztlich mehr Arbeit muss getan werden (dh full table scan).
  • Ich verstehe, dass, aber es ist eine Möglichkeit, um einen einzigen index, der verwendet werden kann, indem sowohl das GROUP BY und WHERE-Klauseln?
InformationsquelleAutor bradvido | 2011-09-19
  1. 7

    Dies sollte Ihnen dabei helfen. Schreiben Sie Ihre Abfrage wie folgt: 

    SELECT c1, Sum(c2) 
FROM table 
WHERE c4 = 2011 
AND c5 = 0 
AND c6 In (6,9,11)
AND c3 IS NOT NULL   
GROUP BY c1

    Nun erstellen Sie einen zusammengesetzten index für die Spalten (c4, c5, c6) mit den Spalten IN dieser REIHENFOLGE. Die Spalten im index erscheinen soll-in der gleichen Reihenfolge wie die Spalten in der WHERE-Klausel. Andernfalls wird der index nicht funktionieren. Die Selektivität dieser index ist schmal genug, dass ein filesort in der temporären Tabelle (für die Gruppe) sollte schnell sein.

    Den Grund zu verschieben c3 an das Ende der Abfrage ist die folgende. Als ein Beispiel, lassen Sie uns davon ausgehen, dass c3 kann Werte zwischen 0 und 100 (oder NULL sein). Wenn Sie ausführen, eine "IST NICHT NULL" - Abfrage, dann Mysql muss Durchlaufen fast alle B-Baum-index-mit Ausnahme der Kanten, entsprechen NULL. Daher, MySQL entscheidet, dass ein full table scan ist ein einfacher option, als zu Fuß durch die verschiedenen Pfade in der index. Auf der anderen Seite, werden Sie sehen, dass, wenn Ihre Abfrage wurde eine "NULL" und Ihr index war (c3, c4, c5, c6) und dann Mysql infact diesen index. Dies ist, weil in diesem Fall nur Mysql benötigt um die traverse Teil der index-Baum entsprechend der NULL-Wert.

    Die Art, die Indizes von MySQL braucht, ist sehr viel abhängig von der Abfrage in Frage. Erstellen von Indizes für alle Spalten, da @louis vorgeschlagen, ist KEINE gute Idee!

    • Danke für die ausführliche Erklärung!
    • "Die Spalten im index erscheinen soll-in der gleichen Reihenfolge wie die Spalten in der WHERE-Klausel. Andernfalls wird der index nicht funktionieren." Der Abfrage-Optimierer kümmert sich nicht, wie die WHERE Klausel ist bestellt. SELECT ... FROM foo WHERE bar = 1 AND baz < 5 haben genau die gleichen execution-plan wie SELECT ... FROM foo WHERE baz < 5 AND bar = 1 und Indizes verwenden, in genau der gleichen Weise. Die Art der Bedingung (Gleichheit oder Ungleichheit, die selektive oder nicht) ist, was wichtig ist, nicht dort, wo es scheint, in der WHERE - Klausel.
    • Ich bin mir nicht sicher, ob dies wahr ist. Aus meiner Erfahrung, die Reihenfolge der index-definition-Angelegenheiten, aber nicht die Reihenfolge der where-Klausel.
    • Vereinbaren Sie mit Shane N - der Optimierer wird wieder um es für Sie automatisch vor der Ausführung
    InformationsquelleAutor srivani
  2. 0

    Ich glaube, das Problem ist die Klausel 'ORDER BY 2 DESC'. Auch wenn die c2-indizierte SUMME(C2) nicht.

    Als für die Indizes, die Sie 'sollte' haben, das hängt davon ab, die Daten so kann ich nicht wirklich kommentieren.

    • Für Argumente Willen, lassen Sie uns sagen, dass ich nicht mit der ORDER BY-Klausel. (Ich habe entfernt es aus der Frage). Ich kann immer noch nicht finden, einen index zu arbeiten.
    InformationsquelleAutor Hugh Jones
  3. -1

    Durch Erfahrung, muss ich sagen : erstellen von Indizes für alle Spalten in der "where" - Klausel (aber hier nicht c6)

    Zumindest, c4 und c5.

    "Group by" - Klausel wird die Reihenfolge der Ergebnisse. Wenn Sie viele Datensätze im Ergebnis KANN es nützlich sein, den index c1 zu.

    c3 wird nur getestet, wie "not null". Aber Indexierung es können auch Dinge verbessern, das ist getestet werden.

    Hopz, das war hilfreich.

    InformationsquelleAutor Louis
Schreibe einen Kommentar