¿Cómo optimizar los índices de mysql para que las operaciones INSERT se realicen rápidamente en una tabla grande con escrituras y lecturas frecuentes?
Tengo una mesalista de vigilancia Contiene hoy casi 3Mil registros.
mysql> select count(*) from watchlist;
+----------+
| count(*) |
+----------+
| 2957994 |
+----------+
Se utiliza como registro para registrar las vistas de páginas de productos en un gran sitio de comercio electrónico (más de 50,000 productos). Registra el productID del producto visto, la dirección IP y USER_AGENT del visor. Y una marca de tiempo de cuando sucede:
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 | |
+-----------+--------------+------+-----+-------------------+-------+
Luego, los datos se informan en varias páginas a lo largo del sitio tanto en el back-end (por ejemplo, verificando lo que GoogleBot está indexando) como en el front-end (por ejemplo, un cuadro de barra lateral para "Productos vistos recientemente" y una página que muestra a los usuarios qué " A la gente de tu región también le gustaron "etc.)
Para que estas páginas y barras laterales de "informes" se carguen rápidamente, coloco índices en los campos relevantes:
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 | |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Sin los ÍNDICES, las páginas con la barra lateral, por ejemplo, gastarían entre 30 y 45 segundos en la ejecución de una consulta para obtener los 7 ProductID más recientes. Con los índices toma <0.2sec.
El problema es esecon Los ÍNDICES de las propias páginas del producto demoran más y más en cargarse porque a medida que la tabla crece, las operaciones de escritura toman más de 5 segundos. Además hay un pico en lamysqld proceso que representa un 10-15% de la CPU disponible cada vez que se visualiza una página de producto (aproximadamente una vez cada 2 segundos). Ya tuvimos que actualizar el hardware del servidor porque en un servidor anterior estaba alcanzando el 100% y causó que mysqld se bloqueara.
Mi plan es intentar una solución de 2 mesas. Una tabla para las operaciones INSERT y otra para las operaciones SELECT. Planeopurga la tabla INSERT cada vez que alcance 1000 registros utilizando un TRIGGER, y copie los 900 registros más antiguos en la tabla SELECT. Las páginas del informe son una mezcla de tiempo real (vistos recientemente) y análisis (qué región), pero las páginas en tiempo real tienden a necesitar solo un puñado de registros nuevos, mientras que las páginas analíticas no necesitan conocer la más reciente tendencia (es decir, las últimas 1000 vistas). Así que puedo usar la tabla pequeña para la primera y la tabla grande para los últimos informes.
Mi pregunta: ¿Es esta una solución ideal para este problema?
También: Con TRIGGERS en MySQL es posibleagradable el trigger_statement para que tome más tiempo, pero no consume mucha CPU? Estaría ejecutando untrabajo cron Cada 30 minutos que se procesa, y que realiza la purga si es necesario, ¿será una mejor solución?