soporta - optimizar consultas lentas mysql

Agrupar por optimización de consultas

Question

La base de datos es MySQL con el motor MyISAM.

Definición de la tabla:

CREATE TABLE IF NOT EXISTS  matches  (
   id  int(11) NOT NULL AUTO_INCREMENT,
   game  int(11) NOT NULL,
   user  int(11) NOT NULL,
   opponent  int(11) NOT NULL,
   tournament  int(11) NOT NULL,
   score  int(11) NOT NULL,
   finish  tinyint(4) NOT NULL,
  PRIMARY KEY ( id ),
  KEY  game  ( game ),
  KEY  user  ( user ),
  KEY  i_gfu ( game , finish , user )
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3149047 ;

He establecido un índice en (game, finish, user) pero esta consulta GROUP BY todavía necesita 0.4 - 0.6 segundos para ejecutarse:

SELECT user AS player
     , COUNT( id ) AS times
FROM matches
WHERE finish = 1
  AND game = 19
GROUP BY user
ORDER BY times DESC

La salida EXPLAIN :

| id | select_type | table   | type | possible_keys | key   | key_len | 
|  1 |  SIMPLE     | matches |  ref | game,i_gfu    | i_gfu |    5    | 

|  ref        |   rows |   Extra                                      |
| const,const | 155855 | Using where; Using temporary; Using filesort |

¿Hay alguna manera de que pueda hacerlo más rápido? La tabla tiene aproximadamente 800,000 registros.

EDITAR: Cambié COUNT(id) en COUNT(*) y el tiempo se redujo a 0.08 - 0.12 segundos. Creo que lo he intentado antes de hacer el índice y olvidé cambiarlo de nuevo después.

En el resultado de explicación, el índice Using explica la aceleración:

|   rows |   Extra                                                   |
| 168029 | Using where; Using index; Using temporary; Using filesort |

(Pregunta complementaria: ¿esta caída de un factor de 5 es normal?)

Hay alrededor de 2000 usuarios, por lo que la clasificación final, incluso si usa FileSort, no perjudica el rendimiento. Intenté sin ORDER BY y todavía me toma casi el mismo tiempo.

Answer 1

Como han señalado otros, es posible que haya alcanzado el límite de su capacidad para ajustar la consulta en sí. A continuación, debe ver cuál es la configuración de las variables max_heap_table_size y tmp_table_size en su servidor. El valor predeterminado es 16 MB, que puede ser demasiado pequeño para su mesa.

Answer 2

Considero que la mayor parte del tiempo se dedica a la extracción y, lo que es más importante, a la clasificación (dos veces, incluida la omitida al leer el índice) de 150k filas de cada 800k. Dudo que pueda optimizarlo mucho más de lo que ya es.

Answer 3

Deshazte de la tecla ''juego'': es redundante con ''i_gfu''. Como ''id'' es unique count (id) simplemente devuelve el número de filas en cada grupo, por lo que puede deshacerse de eso y reemplazarlo con count (*). Pruébalo de esa manera y pega la salida de EXPLAIN:

SELECT user AS player, COUNT(*) AS times
FROM matches
WHERE finish = 1
AND game = 19
GROUP BY user
ORDER BY times DESC

Answer 4

Eh, duro. Intente reordenar su índice: ponga primero la columna del user (así que haga el índice (user, finish, game) ) ya que eso aumenta la posibilidad de que GROUP BY pueda usar el índice. Sin embargo, en general, GROUP BY solo puede usar índices si limita las funciones agregadas utilizadas a MIN y MAX (consulte http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html y http : //dev.mysql.com/doc/refman/5.5/en/loose-index-scan.html ). Su pedido no está realmente ayudando tampoco.

Answer 5

El EXPLAIN verifica que el índice (game, finish, user) se usó en la consulta. Ese parece ser el mejor índice posible para mí. ¿Podría ser un problema de hardware? ¿Cuál es la RAM y la CPU de tu sistema?

Answer 6

Una de las deficiencias de esta consulta es que ordena por agregado. Eso significa que no puede devolver ninguna fila hasta que se haya generado el conjunto de resultados completo; no puede existir ningún índice (para mysql myisam, de todos modos) para arreglar eso.

Sin embargo, puede desnormalizar sus datos con bastante facilidad para superar esto; Podría, por ejemplo, agregar un disparador de inserción / actualización para pegar un valor de conteo en una tabla de resumen, con un índice, para que pueda comenzar a devolver las filas de inmediato.