Wie optimiert man die performance einer MySQL-Ansicht

Ich habe eine view wie folgt definiert:

CREATE VIEW vw_data_summary AS
SELECT
    a.b_id AS b_id
    b.txt_field AS b_txt_field
    a.txt_field AS a_txt_field
    SUM(a.amount) AS amount
FROM
    a JOIN b
WHERE
    a.b_id = b.id
GROUP BY
    a.b_id, b.txt_field, a.txt_field

Tabelle a hat und 650.000 Einträge, während die Tabelle b hat rund 3.200 Datensätze. Es gibt einen index auf a.b_id. Die Tische wachsen mit einer rate von 1.500 Dokumenten pro Tag auf der Tabelle ein, und 50 Datensätze pro Tag, die auf Tabelle b.

Wenn ich ein select auf die view, dauert es etwa 25 Sekunden. Ich bin besorgt, dass die Tabellen weiterhin in der Größe wachsen, die Leistung dieser Abfrage wird schnell abgebaut.

Was kann ich tun, um entweder einzelne Tabellen oder die Sicht selbst, um die Leistung zu verbessern?

ERKLÄREN

Läuft explain auf der wählen Sie oben ergibt sich die folgende Ausgabe:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE          b   ALL PRIMARY NULL    NULL    NULL    3078    Using temporary; Using filesort
1   SIMPLE          a   ref b_id    b_id    4   b.id    134

TABELLE DEFINITIONEN

CREATE TABLE IF NOT EXISTS `b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txt_field` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `txt_field` (`txt_field`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `b_id` int(11) NOT NULL,
  `txt_field` varchar(255) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `txt_field` (`txt_field`),
  KEY `b_id` (`b_id`),
  KEY `new_index` (`txt_field`,`b_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
  • Wäre es nicht richtig, das zu posten, auf der DBA-Website?
  • Ich nehme an, - ich bin nicht Mitglied dieser site noch, und ich habe gesehen, ähnliche Fragen werden hier gepostet.
  • Haben Sie ausführen EXPLAIN <select from your view> noch? Wenn ja post-Ergebnisse in textueller form, die in Ihrer Frage, wenn Nein Mach es und poste die Ergebnisse.
  • Was sind die Datentypen und Längen der a.txt_field und b.txt_field?
  • Sie sind beide varchar-Felder
  • Siehe oben. Ich fügte hinzu, die Indizes, aber Sie nicht erscheinen, haben keinerlei Auswirkungen auf die performance der Abfrage.
  • Wenn Sie uns helfen wollen das optimieren einer Abfrage oder Ansicht, Sie brauchen, um uns zu zeigen, die Tabelle und der index-Definitionen, sowie die Zeilenanzahl für alle Tabellen. Vielleicht die Tabellen sind schlecht definiert. Vielleicht sind die Indizes sind nicht richtig erstellt. Vielleicht haben Sie nicht über einen index auf die Spalte, die Sie dachte, Sie Taten. Ohne zu sehen, die Tabelle und der index-Definitionen, können wir nicht sagen. Wir müssen auch die Zeile zählt, weil, die beeinflussen können, query-Optimierung erheblich. Wenn Sie wissen, wie zu tun, ein EXPLAIN oder erhalten Sie einen Ausführungsplan, die Ergebnisse in der Frage, wie gut.
  • Andy - Indizes aufgeführt wurden, aber ich habe die definition der Tabelle da, so dass Sie sehen können. Die Zeilenanzahl und die Explain-Ergebnisse wurden in der Frage.

InformationsquelleAutor Elie | 2013-09-09
  1. 3

    Fügen Sie einen index auf (b_id, txt_field, amount) und erstellen Sie die Ansicht mit:

    CREATE VIEW vw_data_summary_2 AS
SELECT
    a.b_id AS b_id
    b.txt_field AS b_txt_field
    a.txt_field AS a_txt_field
    SUM(a.amount) AS amount
FROM
    a JOIN b
      ON
    a.b_id = b.id
GROUP BY
    a.b_id, a.txt_field ;

    Wenn Sie hatte eine foreign key im a(b_id) dass references b(id) Sie könnten auch versuchen, die änderung der JOIN zu LEFT JOIN und Leistung vergleichen. In diesem Fall, der Blick könnte auch definiert werden mit:

    CREATE VIEW vw_data_summary_4 AS
SELECT
    a.b_id AS b_id
    (SELECT b.txt_field FROM b WHERE a.b_id = b.id) AS b_txt_field
    a.txt_field AS a_txt_field
    SUM(a.amount) AS amount
FROM
    a 
GROUP BY
    a.b_id, a.txt_field ;
    • Es ist eine foreign key-Beziehung - ich werde versuchen, die Neudefinition der Ansicht mit sub-select statt, und sehen, ob es hilft.
    InformationsquelleAutor ypercubeᵀᴹ
  2. 2

    Bist du mit InnoDB - froh, dass Sie erwähnt.

    Hier ist eine Checkliste:

    • Ihre Tabelle optimieren

    Wird dies organisieren Sie Ihre Tabelle auf Ihrem server für eine schnellere Eingabe/Ausgabe -

    OPTIMIZE TABLE b;
OPTIMIZE TABLE a;

    Erfahren Sie mehr über InnoDB-Optimierung bei: MySQL

    Was ich nicht verstehe, ist, warum Sie erstellt haben, einen Schlüssel für jede einzelne Säule von dir, das ist ein wenig überflüssig - wie konnte man nur binden und in mehrere Spalten als ein einzelner Schlüssel (composite index) - vor allem, wenn Sie nur gehen, um zu vergleichen, eine einzelne Spalte in eine andere Spalte aus einer anderen Tabelle. Am besten man erstellt einen index in der gleichen Reihenfolge wie die Gruppe auch.

    Auch logisch, Sie würden nur den Vergleich einer einzelnen Taste index bei Verwendung der GROUP BY-eher als das Sortieren durch jeden Schlüssel, und dann, wenn Sie neben dem richtigen Wert.

    • ein.b_id - könnte eine FOREIGN KEY

    • Schließlich überprüfen Sie Ihre Speicher, erwägen Sie die Verwendung von InnoDB-Puffer-Optionen, wie gut.

    Hoffe, das hilft. Um anderen zu helfen, wenn man diese unterstützt Sie, einen Kommentar zu schreiben, man hatte den größten Einfluss.

    • Der Grund für all die Indizes ist, weil ich bin zeigt nur eine kleine Teilmenge der Spalten in der original - Tische- aber die eigentlichen Tabellen mehrere Spalten, die sind nicht indiziert. Ich werde versuchen, die Vorschläge, die Sie gemacht, und wie in Kommentar Auswirkungen der einzelnen, wenn ich ins Büro.
    InformationsquelleAutor Duniyadnd
Schreibe einen Kommentar