Tag Archives: tunning

MySql tunning

Uno de los primeras cosas que hay que saber antes de empezar a optimizar consultas en MySql es conocer la existencia de la cláusula EXPLAIN.

Con EXPLAIN podemos obtener el plan de ejecución de una consulta dada, como se muestra en el siguiente ejemplo:

EXPLAIN SELECT * FROM People

Resultado:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE People ALL NULL NULL NULL NULL 979

En el ejemplo anterior el plan de ejecución es un simple full scan (type = ALL) de toda la tabla People.

Vamos a ver otro ejemplo:

EXPLAIN SELECT * FROM People WHERE gender = 'Male'

Resultado:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE People ALL NULL NULL NULL NULL 979 Using where

En este caso vemos que la consulta devuelve al motor de base de datos sigue devolviendo todos los registros en un full scan.  En este caso podemos optimizar la consulta creando un índice para gender (Idx_gender). Y de esta forma el resultado de EXPLAIN será:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE People ref Idx_gender Idx_gender 22 const 489 Using where

Ahora se puede ver como ya no se requiere un full scan (type != ALL) y únicamente la filas con valores en el índice serán las seleccionadas.

Hasta ahora se puede intuir que cuando el tipo es ALL y no queremos un full scan (todas las filas), siempre se puede optimizar. Lo más sencillo es con la creación de índices que agilicen las búsquedas más frecuentes.

A continuación teneís el detalle de todos los campos que devuelve EXPLAIN:

Id: El identificador de la SELECT. Es un número secuencial.

Select_type: Tipo de la SELECT.

  • SIMPLE: SELECT simple, sin UNION o subconsultas.
  • PRIMARY: Se refiere a la consulta “más externa”; a la principal.
  • UNION: Resto de consulta/s (segunda, tercera, etc.) en una UNION.
  • DEPENDENT UNION: Resto de consulta/s (segunda, tercera, etc.) en una UNION que depende una una consulta “externa”; PRIMARY.
  • UNION RESULT: Resultado de una UNION.
  • SUBQUERY: Primera consulta de una subconsulta.
  • DEPENDENT SUBQUERY: Primera consulta de una subconsulta que depende una consulta “externa”.
  • DERIVED: Tabla derivada en una SELECT; subquery en la cláusula FROM.
  • UNCACHEABLE SUBQUERY: Subconsulta que no puede ser almacenada en caché, o re-evaluada.
  • UNCACHEABLE UNION: Resto de consulta/s (segunda, tercera, etc.) en una UNION que no puede ser almacenada en caché, o re-evaluada.

Table: tabla a la que se refiere la fila del resultado.

Type: el tipo de JOIN. Los tipos de muestran a continuación ordenados desde el mejor al peor.

  • system: la tabla únicamente tiene una fila y es una tabla del sistema. Es un caso especial del tipo const.
  • const: en la tabla coincide una única fila que el optimizador tomo como constante. También se utiliza cuando se comparan todas las partes de una clave primaria o un índice único (UNIQUE) con un valor constante.
  • Eq_ref: una fila de una tabla es leída por cada combinación de filas en una segunda tabla. Se utiliza cuando se usan todas las partes de una clave primaria o un índice único (UNIQUE).
  • ref: todas las filas que coincidan con con los valores de un índice serán leídos por cada combinación de filas de las tablas anteriores. En JOIN se utiliza cuando no se puede seleccionar una única fila en base al valor de la clave. Puede ser usada, por el optimizador, en columnas indexadas que se comparan con =, <=>.
  • fulltext: la JOIN se realiza con un índice FULLTEXT.
  • ref or null: el tipo de JOIN es como ref pero hay una búsqueda extra para campos nulos. Este tipo se suele utilizar para resolver subconsultas.
  • index_merge: se utiliza la optimización index merge.
  • unique_subquery: sustituye a ref en algunas subconsultas (del tipo SELECT clave_primaria) que utilizan IN.
  • index_subquery: sustituye a ref en algunas subconsultas (del tipo SELECT valor_columna) que utilizan IN y que no son únicas.
  • range: cuando se devuelve filas dentro de un rango.
  • index: igual que un full scan pero cuando se escanea el índice.
  • ALL: full scan. Normalmente se debe evitar.

Possible_keys: Muestra que índices puede seleccionar MySql para devolver las filas.

Key: Indica que índice va a utilizar MySql para devolver las filas.

Key_len: tamaño del índice que se va a utilizar.

Ref: columna/s o constante/s que se van a comparar con el índice.

Rows: indica el número de filas que se prevee que MySql examine. En tablas InnoDB es un valor aproximado.

Extra: información adicional sobre como MySql resolverá la consulta.

Para más información sobre el resultado de EXPLAIN por favor mirar en la página web de MySql.

Espero que os halla sido de utilidad este breve artículo de cómo utilizar EXPLAIN para optimizar vuestras consultas a bases de datos.

¡Un saludo a tod@s!