Как оптимизировать индексы mysql, чтобы операции INSERT происходили быстро на большой таблице с частыми операциями записи и чтения?
У меня есть столwatchlist содержащие сегодня почти 3Mil записей.
mysql> select count(*) from watchlist;
+----------+
| count(*) |
+----------+
| 2957994 |
+----------+
Он используется в качестве журнала для записи просмотров страниц товара на большом сайте электронной коммерции (более 50 000 товаров). Он записывает productID просматриваемого продукта, IP-адрес и USER_AGENT зрителя. И отметка времени, когда это произойдет:
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 | |
+-----------+--------------+------+-----+-------------------+-------+
Затем данные представляются на нескольких страницах сайта как на внутренней стороне (например, для проверки того, что GoogleBot индексирует), так и на внешней стороне (например, на боковой панели «Недавно просмотренные товары») и на странице, показывающей пользователям, что "Люди из вашего региона также понравились" и т. д.).
Так что это "отчет" страницы и боковые панели загружаются быстро Я ставлю индексы на соответствующие поля:
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 | |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Без INDEXES страницы с боковой панелью, например, потратили бы около 30-45 секунд на выполнение запроса, чтобы получить 7 самых последних ProductID. С индексами это занимает менее 0,2 сек.
Проблема в том, чтоwith ИНДЕКСЫ сами страницы продукта загружаются все дольше и дольше, потому что с ростом таблицы операции записи занимают более 5 секунд. Кроме того, есть всплеск наmysqld процесс, составляющий 10-15% доступного процессора каждый раз при просмотре страницы продукта (примерно раз в 2 с). Мы уже должны были обновить серверное оборудование, потому что на предыдущем сервере оно достигало 100% и вызывало сбой mysqld.
Мой план состоит в том, чтобы попробовать решение за 2 столами. Одна таблица для операций INSERT, а другая для операций SELECT. Я планируюpurge таблицу INSERT, когда она достигает 1000 записей, используя TRIGGER, и копируйте самые старые 900 записей в таблицу SELECT. Страницы отчета представляют собой смесь в реальном времени (недавно просмотренные) и аналитики (в каком регионе), но страницам в реальном времени, как правило, требуется лишь несколько свежих записей, в то время как аналитические страницы не должны знать о самых последних тренд (т.е. последние 1000 просмотров). Поэтому я могу использовать небольшую таблицу для первой и большую таблицу для последних отчетов.
My question: Это идеальное решение этой проблемы?
Also: С TRIGGERS в MySQL возможно лиnice trigger_statement, чтобы он занимал больше времени, но не потреблял много ресурсов процессора? Бы создаюcron job Каждые 30 минут, которые проходят, и которые выполняют очистку, если требуется, будут лучшим решением?