Tengo una tabla MySQL que contiene 20 000 000 filas y columnas como (user_id, register_timestamp, etc.). He escrito una consulta a continuación para obtener un recuento de los usuarios registrados por día. La consulta estaba tardando mucho en ejecutarse. ¿Agregar un índice a la columna register_timestamp mejorará el tiempo de ejecución?
select date(registered_timestamp), count(userid) from table group by 1Considere usar esta consulta para obtener una lista de fechas y el número de registros en cada fecha.
SELECT date(registered_timestamp) date, COUNT(*) FROM table GROUP BY date(registered_timestamp) Luego, un índice en table(registered_timestamp) ayudará un poco porque es un índice de cobertura.
Si adapta su consulta para devolver fechas de un rango limitado, por ejemplo.
SELECT date(registered_timestamp) date, COUNT(*) FROM table WHERE registered_timestamp >= CURDATE() - INTERVAL 8 DAY AND registered_timestamp < CURDATE() GROUP BY date(registered_timestamp)el índice ayudará. (Esta consulta devuelve los resultados de la semana que terminó ayer). Sin embargo, el índice no ayudará con esta consulta.
SELECT date(registered_timestamp) date, COUNT(*) FROM table WHERE DATE(registered_timestamp) >= CURDATE() - INTERVAL 8 DAY /* slow! */ GROUP BY date(registered_timestamp)porque la función en la columna hace que la consulta no se pueda analizar .
Probablemente pueda solucionar este problema de rendimiento con una columna generada por MySQL. Este comando:
ALTER TABLE `table` ADD registered_date DATE GENERATED ALWAYS AS DATE(registered_timestamp) STORED;Luego puede agregar un índice en la columna generada
CREATE INDEX regdate ON `table` ( registered_date );Luego, puede usar esa columna generada (derivada) en su consulta y obtener mucha ayuda de ese índice.
SELECT registered_date, COUNT(*) FROM table GROUP BY registered_date;Pero cuidado, crear la columna generada y su índice llevará un tiempo.
select date(registered_timestamp), count(userid) from table group by 1 Se beneficiaría de INDEX(registered_timestamp, userid) pero solo porque dicho índice está "cubriendo". La consulta aún necesitará leer cada fila del índice y hacer una ordenación de archivos.
Si el ID de userid es la PRIMARY KEY , entonces esto le daría las mismas respuestas sin molestarse en verificar que cada ID de userid NOT NULL .
select date(registered_timestamp), count(*) from table group by 1 E INDEX(registered_timestamp) sería equivalente a la sugerencia anterior. (Esto se debe a que InnoDB agrega implícitamente el PK).
Si esta consulta es común, entonces podría crear y mantener una "tabla de resumen", que recopila el recuento todas las noches de los registros del día. Entonces la consulta sería una búsqueda mucho más rápida desde esa tabla más pequeña.