Sind mehrere Indizes in der MYSQL-Tabelle Grund für langsame UPDATES und INSERTS?

Dec 10 2020

Die Leistung meiner (LAMP-Stack-) Site hat sich in den letzten Tagen trotz fehlender Code-Updates erheblich verschlechtert. Es scheint, dass nur Einfügungen, Aktualisierungen und Löschungen einer bestimmten MySQL-Tabelle das Problem verursachen. Das Laden einer Seite, die einen Eintrag der Jobtabelle aktualisiert, einfügt oder löscht, dauert etwa 10 Sekunden. (EG UPDATE jobs SET title = 'sdfldsfjlk' WHERE job_id = 134324)

SELECT Abfragen scheinen wie zuvor ausgeführt zu werden, obwohl sie langsamer zu sein scheinen, wenn gleichzeitig ein Update stattfindet.

Die Tabelle enthält rund 180.000 Einträge. In der PHPMyAdmin-Ansicht habe ich festgestellt, dass zusätzlich zum "normalen" Index für das Primärfeld ein Index für das Feld "entry_date" vorhanden ist (siehe Abbildung). Könnte das in diesem Fall ein Problem sein? Ich habe keine Ahnung, warum ein Index für dieses Feld erstellt worden wäre.

Wenn nicht, was könnte die Ursache des Problems sein? Ich habe den Speicherplatz auf der Festplatte überprüft, was in Ordnung zu sein scheint. (7 GB verfügbar) gemäß df.

SHOW CREATE TABLE job\G

Create Table: CREATE TABLE `job` (
    `job_id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` int(11) NOT NULL DEFAULT '0', 
    `entry_date` date NOT NULL DEFAULT '0000-00-00', 
    `timescale` varchar(20) COLLATE latin1_german2_ci NOT NULL DEFAULT '0000-00-00', 
    `title` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `description` text COLLATE latin1_german2_ci NOT NULL, 
    `start_date` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `address_town` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `address_county` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode1` varchar(4) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode2` char(3) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `status` tinyint(4) NOT NULL DEFAULT '0', 
    `cat_id` int(4) NOT NULL DEFAULT '0', 
    `price` decimal(4,2) NOT NULL DEFAULT '1.00', 
    `emailcount` smallint(5) NOT NULL DEFAULT '-1', 
    `emailcount2` int(11) NOT NULL DEFAULT '-1', 
    `recemailcount` int(11) NOT NULL DEFAULT '-1', 
    `archive` tinyint(4) NOT NULL DEFAULT '0', 
    `post_url` varchar(100) COLLATE latin1_german2_ci NOT NULL, 
    PRIMARY KEY (`job_id`), 
    KEY `entrydatejob_id` (`entry_date`,`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=235844
           DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci

UPDATE - Zunächst einmal vielen Dank an alle Mitwirkenden. Ich schätze es sehr. In den letzten Tagen hörte das Problem auf, was es noch schwieriger machte, herauszufinden, wo das Problem liegen könnte. Aber jetzt scheint es wieder zurückgekehrt zu sein. Lassen Sie mich zunächst den in der Google Cloud gehosteten Maschinentyp angeben: g1-small (1 vCPU, 1,7 GB Speicher). Ich werde dies mit den weiteren Informationen aktualisieren, die von den Kommentatoren angefordert wurden.

Antworten

6 ypercubeᵀᴹ Dec 10 2020 at 19:22

Um die spezifische Frage zu beantworten:

Nein , ein (entry_date)oder zwei zusätzliche Indizes würden die Leistung beim Aktualisieren einer anderen titleSpalte nicht beeinträchtigen .

Zusätzlich:

Nein , die Version von MySQL 5.6 ist nicht zu alt, auch wenn einige moderne Funktionen (wie Fensterfunktionen) fehlen. Sie sollten eine anständige Leistung mit anständiger Hardware haben.

Wir können nur über das vorliegende Problem spekulieren, aber was könnte falsch sein oder es erklären:

alte, ineffiziente Hardware: Überprüfen Sie die Festplattenspezifikationen und messen Sie die Leistung.
fragmentierter Index / Tabelle. Überprüfen Sie hier MySQL-Dokumente und alte Fragen / Antworten zum Defragmentieren von MyISAM-Tabellen (OPTIMIZE TABLE).

Zu guter Letzt:

Ihre Tabelle verwendet die MyISAM-Engine , eine alte Nachricht. InnODB hat es vor Jahren als Standard-Engine in MySQL ersetzt. Es gibt keine aktive Entwicklung dieses Motors. Im Vergleich zu InnoDB (Transaktionen, Fremdschlüsseleinschränkungen usw.) und der Leistung bei den meisten Arbeitslasten fehlen mehrere Funktionen . Ich empfehle Ihnen dringend, Ihre Tabelle zu ändern (nach dem Testen natürlich, dass Ihre Apps und Verfahren nicht kaputt gehen), um InnoDB zu verwenden.
Wichtig ist zu Ihrer Frage kann InnoDB durchführen SELECTund UPDATEgleichzeitig die ( in der Regel). MyISAM sperrt den Tisch vollständig. Das Update oder die Auswahl muss abgeschlossen sein, bevor die Auswahl oder das Update überhaupt beginnen kann.
Stellen Sie beim Wechsel von MyISAM zu InnoDB unbedingt key_buffer_sizeund ein innodb_buffer_pool_size.

3 J.D. Dec 10 2020 at 19:20

Indizes wirken sich geringfügig auf die Leistung von INSERT- und DELETE-Operationen aus. Dies ist jedoch im Allgemeinen vernachlässigbar, insbesondere wenn Ihre Indizes gut geplant sind und Sie nicht über Bord gehen, indem Sie 30 Indizes mit jeweils 30 verschiedenen Feldkombinationen in dieselbe Tabelle einfügen. (Im Allgemeinen halte ich mich an eine 5 x 5-Richtlinie - maximal 5 Indizes, maximal 5 Felder pro Index. Natürlich ist dies nur eine Richtlinie und keine harte Regel).

Abgesehen davon werden Indizes tatsächlich verwendet, um die Leistung von UPDATE- und SELECT-Abfragen zu erhöhen, da der Index verwendet wird, um die Zeilen zu UPDATE oder SELECT zu lokalisieren.

Die drastische Änderung, die Sie sehen, hängt zweifellos mit dem von Ihnen entdeckten entry_date-Index zusammen (wissen Sie, ob dieser kürzlich hinzugefügt wurde oder bereits vor der Leistungsänderung vorhanden war?).

Zwei Dinge, die Sie prüfen sollten, sind:

Wenn die vorherigen Indizes für Ihre Abfragen weiterhin verwendet werden, insbesondere für UPDATE- und SELECT-Abfragen (und wenn sie gesucht werden oder gerade ein Scanvorgang ausgeführt wird). Dies kann sich aufgrund von Änderungen in der Tabellenstatistik der Daten im Laufe der Zeit ändern. Mit der ANALYZE- Anweisung können Sie die Statistiken einer Tabelle überprüfen.
Die andere Sache, die Sie untersuchen können, ist die Indexfragmentierung , die im Laufe der Zeit natürlich vorkommt. Dies geschieht, wenn einer Tabelle weitere Daten hinzugefügt werden. (Im Allgemeinen sollte dies auf einem kleinen Tisch wie Ihrem kein Problem sein, aber es lohnt sich trotzdem, es zu überprüfen.)

Ich werde meine Antwort weiterhin mit weiteren Dingen aktualisieren, die ich prüfen muss, wenn ich an sie denke. Das Ausführen einer EXPLAIN für Ihre Abfragen kann Ihnen auch dabei helfen, das Problem zu erkennen. Versuchen Sie, die langsam laufenden Anweisungen UPDATE und INSERT zu trennen und zu verstehen, ob sie von derselben Grundursache betroffen sind (da sie wiederum etwas umgekehrt zu Indizes wirken, sind INSERT vernachlässigbar langsamer, aber UPDATEs profitieren normalerweise vom richtigen Index und sollte schneller sein.)