Jak zoptymalizować indeksy mysql, aby operacje INSERT odbywały się szybko na dużym stole z częstymi zapisami i odczytami?
Mam stoliklista obserwowana zawierające dziś prawie 3 milowe rekordy.
mysql> select count(*) from watchlist;
+----------+
| count(*) |
+----------+
| 2957994 |
+----------+
Jest używany jako dziennik do rejestrowania widoków stron produktu w dużej witrynie e-commerce (ponad 50 000 produktów). Rejestruje ID produktu wyświetlanego produktu, adres IP i USER_AGENT przeglądarki. I znacznik czasu, kiedy to się dzieje:
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 | |
+-----------+--------------+------+-----+-------------------+-------+
Dane są następnie raportowane na kilku stronach w całej witrynie zarówno na zapleczu (np. Sprawdzanie, co indeksuje GoogleBot), jak i na interfejsie (np. Pole paska bocznego dla „Ostatnio oglądanych produktów” i strona pokazująca użytkowników co ” Ludziom z twojego regionu podobały się również „itp.”.
Aby strony „raportu” i paski boczne ładowały się szybko, umieszczam indeksy w odpowiednich polach:
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 | |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Bez INDEKSÓW strony z bocznym paskiem na przykład wydałyby około 30-45 sekund na wykonanie zapytania, aby uzyskać 7 najnowszych ID produktów. Przy indeksach trwa to <0,2 sekundy.
Problemem jestz INDEKSY same strony produktów ładują się dłużej i dłużej, ponieważ w miarę wzrostu liczby operacji zapisywanie zajmuje więcej niż 5 sekund. Dodatkowo występuje skokmysqld proces wynoszący 10-15% dostępnego procesora za każdym razem, gdy przeglądana jest strona produktu (mniej więcej raz na 2 sekundy). Musieliśmy już zaktualizować sprzęt serwerowy, ponieważ na poprzednim serwerze osiągnął on 100% i spowodował awarię mysqld.
Moim planem jest wypróbowanie rozwiązania 2-stołowego. Jedna tabela dla operacji INSERT, a druga dla operacji SELECT. planujęoczyścić tabelę INSERT, gdy osiągnie 1000 rekordów przy użyciu TRIGGER, i skopiuj najstarsze 900 rekordów do tabeli SELECT. Strony raportu to połączenie czasu rzeczywistego (ostatnio oglądanego) i analizy (który region), ale strony czasu rzeczywistego potrzebują zazwyczaj tylko kilku nowych rekordów, podczas gdy strony analityczne nie muszą wiedzieć o najnowszych trend (tj. ostatnie 1000 wyświetleń). Mogę więc użyć małej tabeli dla pierwszej i dużej tabeli dla ostatnich raportów.
Moje pytanie: Czy to idealne rozwiązanie tego problemu?
Również: Z TRIGGERS w MySQL możnaładny parametr trigger_statement, dzięki czemu trwa dłużej, ale nie zużywa dużo procesora? Czy biegniezadanie cron co 30 minut, które jest ładniejsze i które w razie potrzeby wykonuje czyszczenie, jest lepszym rozwiązaniem?