Wie kann man MySQL-Indizes so optimieren, dass INSERT-Operationen in einer großen Tabelle mit häufigen Schreib- und Lesevorgängen schnell ausgeführt werden?
Ich habe einen tisch Beobachtungsliste enthält heute fast 3Mil Aufzeichnungen.
mysql> select count(*) from watchlist;
+----------+
| count(*) |
+----------+
| 2957994 |
+----------+
Es wird als Protokoll verwendet, um die Aufrufe von Produktseiten auf einer großen E-Commerce-Website (über 50.000 Produkte) aufzuzeichnen. Es zeichnet die Produkt-ID des betrachteten Produkts, die IP-Adresse und USER_AGENT des Betrachters auf. Und ein Zeitstempel, wann es passiert:
mysql> show columns from watchlist;
+-----------+--------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+-------------------+-------+
| productID | int(11) | NO | MUL | 0 | |
| ip | varchar(16) | YES | | NULL | |
| added_on | timestamp | NO | MUL | CURRENT_TIMESTAMP | |
| agent | varchar(220) | YES | MUL | NULL | |
+-----------+--------------+------+-----+-------------------+-------+
Die Daten werden dann auf mehreren Seiten der Website sowohl im Back-End (z. B. Überprüfung der Indizierung des GoogleBot) als auch im Front-End (z. B. ein Seitenleistenfeld für "Kürzlich angesehene Produkte" und eine Seite, auf der den Nutzern angezeigt wird, was " Leute aus deiner Region mochten auch "etc.).
Damit diese "Bericht" -Seiten und Seitenleisten schnell geladen werden, habe ich Indizes für relevante Felder erstellt:
mysql> show indexes from watchlist;
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| watchlist | 1 | added_on | 1 | added_on | A | NULL | NULL | NULL | | BTREE | |
| watchlist | 1 | productID | 1 | productID | A | NULL | NULL | NULL | | BTREE | |
| watchlist | 1 | agent | 1 | agent | A | NULL | NULL | NULL | YES | BTREE | |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Ohne die INDEXES würden Seiten mit der Seitenleiste beispielsweise 30-45 Sekunden lang eine Abfrage ausführen, um die 7 neuesten ProductIDs abzurufen. Bei den Indizes dauert es <0,2s.
Das Problem ist, dassmit In den INDEXEN dauert das Laden der Produktseiten selbst immer länger, da die Schreibvorgänge mit dem Anwachsen der Tabelle über 5 Sekunden dauern. Zusätzlich befindet sich ein Spike auf demmysqld Bei jedem Aufruf einer Produktseite wird ein Prozess ausgeführt, der 10-15% der verfügbaren CPU ausmacht (ungefähr alle 2 Sekunden). Wir mussten die Serverhardware bereits aktualisieren, da sie auf einem früheren Server 100% erreichte und mysqld zum Absturz brachte.
Mein Plan ist es, eine 2-Tisch-Lösung zu versuchen. Eine Tabelle für INSERT-Operationen und eine andere für SELECT-Operationen. Ich habe vorreinigen die INSERT-Tabelle, wenn sie 1000 Datensätze mit einem TRIGGER erreicht, und kopieren Sie die ältesten 900 Datensätze in die SELECT-Tabelle. Die Berichtsseiten sind eine Mischung aus Echtzeitseiten (kürzlich angezeigt) und Analyseseiten (welche Region). Auf den Echtzeitseiten sind jedoch in der Regel nur einige wenige neue Datensätze erforderlich, während die Analyseseiten nicht über die neuesten Informationen verfügen müssen Trend (dh die letzten 1000 Aufrufe). So kann ich den kleinen Tisch für die ersteren und den großen Tisch für die letzteren Berichte verwenden.
Meine Frage: Ist dies eine ideale Lösung für dieses Problem?
Ebenfalls: Mit TRIGGERS in MySQL ist das möglichnett die trigger_statement so, dass es länger dauert, aber nicht viel CPU verbraucht? Würde ein laufenCron-Job alle 30min ist das gescheitert, und was führt die spülung bei bedarf besser aus?