Optimizando consultas Mysql
01 06 2008
La optimización de las consultas a la base de datos de tus aplicaciones web es de vital importancia, no sólo ha la hora de medir el tiempo en el que una página se carga sino también cuando el tráfico de tu máquina se incrementa o cuando el hardware tiene trabajo extra debido a un mal diseño de esas consultas.
Incluso aunque tengamos implementadas soluciones de caché (librerías de mysql con cache, memcache o similares) la optimización de las consultas sigue teniendo un relevancia plena. En este artículo resumiremos que herramientas podemos emplear para optimizar, detectar y solucionar problemas en las consultas a tu base de datos que lastran tu aplicación llegando ha hacerla inutilizable o requiriendo una inversión mayor en equipo cuando no debería.
La primera de las herramientas, interna al propio servidor mysql es la sentencia explain, explain también es un alias del comando describe cuando se escribe a continuación un nombre de tabla. Explain o describe devolverá información sobre la estructura de la tabla. La otra sintaxis propia de explain, y la que nos interesa, es la que sirve para analizar como mysql ejecuta una sentencia select.
EXPLAIN [EXTENDED] SELECT select_options
Explain hará que el optimizar de mysql nos devuelva información sobre como se procesará la sentencia SELECT incluyendo información de que tablas usará como utilizará los joins, en que orden y en que modo utilizará los indices que haya definidos en las tablas. Todo esta información se conoce con el nombre de información sobre plan de ejecución de mysql.
Esta información es muy valiosa y se puede emplear para optimizar nuestros selects. Con esta información podremos por ejemplo decidir añadir nuevos índices a nuestras tablas o modificar nuestros selects para que se fuerte un determinado orden para nuestros joins.
Explain devuelve una fila de información por cada tabla empleada en la consulta. Las tablas son listadas en el orden en el que mysql las leerá a la hora de procesar la consulta. Si añadimos EXTENDED mysql nos dará información adicional en forma de warning, para poder verla debemos ejecutar un SHOW WARNINGS. La información extra contiene como el optimizador da
nombre cada una de las tablas y columnas de la sentencia select y puede que otras notas sobre el proceso de optimización.
Veamoslo con un ejemplo bien simple:
mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> explain select 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
Cada columna que devuelve EXPLAIN tiene el siguiente significado:
id - identificador, es sólo una secuencia numérica autoincrementada para cada una de las filas del explain
select_type - Debe ser uno de los siguiente tipos
SIMPLE - No usa subqueries ni UNION
PRIMARY - Último SELECT
UNION - Segunda o última sentencia SELECT en una UNION
DEPENDENT UNION - Segunda o última sentencia SELECT en una UNION, dependiente de otra query
SUBQUERY - Primera SELECT en una subquery
DEPENDENT SUBQUERY - Primera SELECT en una subquery, dependiente de otra query
DERIVED - Tabla SELECT derivada (subquery en una clausula FROM)
UNCACHEABLE SUBQUERY - Una subquery cuyo resultado no puede ser cacheado y debe ser re-evaluado para cada file de la otra query.
table - la tabla a la que se refiere la información
tipo - el tipo de unión empleado, que puede ser:
sytem - SYSTEM SELECT sólo hay una fila en la tabla (tipo especial del tipo const)
const - La tabla tiene como mucho un sola fila resultado, que es leída al principio de la operación convirtiéndose en una constante para el resto de las operaciones del select (operación muy rápida)
eq_ref - Una fila es leída de la tabla por cada combinación de filas en las tablas previas.
ref - Todas las filas con valores de índices validos son leídos de esta tabla por cada combinación de filas de las tablas previas.
fulltext - El join se utiliza mediante un índice FULLTEXT
ref_or_null - Este tipo de join es como el ref, pero además MySQL hace una busqueda extra por filas que contengan valores NULL
index_merge - la columna key en la fila de salida contiene una lista de indices a utilizar, y key_len contiene una lista de las claves más largas para los índices utilizados.
unique_subquery - Este tipo sustituye al ref para algunas subqueries IN de el siguiente tipo de estructuras: value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery - Este tipo join es similar al unique_subquery. Este sustituye subqueries IN, pero funciona para índices no únicos en subqueries del siguiente tipo: value IN (SELECT key_column FROM single_table WHERE some_expr)
range - Solamente las columnas que están en un rango dado son retornadas, empleando un índice para seleccionar las columnas. La columna key indica que índice es utilizado. key_len contiene el índice más largo de la parte del índice más larga utilizada. ref es NULL para este tipo.
index - Este ipo de join es el mismo que el de ALL, con la excepción de que sólo se realiza el escaneado a través de un índice. Esto es mas rápido que ALL ya que los ficheros índice son generalmente más pequeños que el fichero de datos.
all - Se realiza un escaneado de toda la tabla por cada combinación de filas de las tablas anteriores.
Estos tipos están ordenados en cuando a lo mejor o peor en cuanto a optimización. Así un tipo all sería lo peor y un tipo system sería lo mejor. Reducir de un tipo a otro, cambiando o creando índices para una determinada tabla o modificando la estrucutra misma de la petición es la tarea básica que debemos intentar para optimizar nuestra querys.
possible_keys - La columna indica que índices se pueden seleccionar para ser usados a la hora de encontrar filas en la tabla.
key - Índica que índice es empleado finalmente (podría se uno que no estuviera listado en las possible_keys).
key_len - Longitud del índice empleado
ref - Muestra que columnas o constante son empleadas para comparar con el índice
rows - Número de filas que MySQL piensa que debe examinar al ejecutar la petición
extra - Información adicional sobre la consulta.
Cuando modifiques la estructura de una tabla o añadas índices recuerda ejecutar un:
ANALIZE TABLE nombre_tabla;
Esto hará que el optimizador analize adecuadamente la tabla, los datos los usará para ayudarle a determinar la mejor estrategia en consultas posteriores. Tras modificar la tabla ejecuta ANALIZE y vuelve a ejecutar un EXPLAIN para observar las modificaciónes en el plan de ejecución de la consulta.
Comentarios :
No hay comentarios »
Incluso aunque tengamos implementadas soluciones de caché (librerías de mysql con cache, memcache o similares) la optimización de las consultas sigue teniendo un relevancia plena. En este artículo resumiremos que herramientas podemos emplear para optimizar, detectar y solucionar problemas en las consultas a tu base de datos que lastran tu aplicación llegando ha hacerla inutilizable o requiriendo una inversión mayor en equipo cuando no debería.
La primera de las herramientas, interna al propio servidor mysql es la sentencia explain, explain también es un alias del comando describe cuando se escribe a continuación un nombre de tabla. Explain o describe devolverá información sobre la estructura de la tabla. La otra sintaxis propia de explain, y la que nos interesa, es la que sirve para analizar como mysql ejecuta una sentencia select.
EXPLAIN [EXTENDED] SELECT select_options
Explain hará que el optimizar de mysql nos devuelva información sobre como se procesará la sentencia SELECT incluyendo información de que tablas usará como utilizará los joins, en que orden y en que modo utilizará los indices que haya definidos en las tablas. Todo esta información se conoce con el nombre de información sobre plan de ejecución de mysql.
Esta información es muy valiosa y se puede emplear para optimizar nuestros selects. Con esta información podremos por ejemplo decidir añadir nuevos índices a nuestras tablas o modificar nuestros selects para que se fuerte un determinado orden para nuestros joins.
Explain devuelve una fila de información por cada tabla empleada en la consulta. Las tablas son listadas en el orden en el que mysql las leerá a la hora de procesar la consulta. Si añadimos EXTENDED mysql nos dará información adicional en forma de warning, para poder verla debemos ejecutar un SHOW WARNINGS. La información extra contiene como el optimizador da
nombre cada una de las tablas y columnas de la sentencia select y puede que otras notas sobre el proceso de optimización.
Veamoslo con un ejemplo bien simple:
mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> explain select 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
Cada columna que devuelve EXPLAIN tiene el siguiente significado:
id - identificador, es sólo una secuencia numérica autoincrementada para cada una de las filas del explain
select_type - Debe ser uno de los siguiente tipos
SIMPLE - No usa subqueries ni UNION
PRIMARY - Último SELECT
UNION - Segunda o última sentencia SELECT en una UNION
DEPENDENT UNION - Segunda o última sentencia SELECT en una UNION, dependiente de otra query
SUBQUERY - Primera SELECT en una subquery
DEPENDENT SUBQUERY - Primera SELECT en una subquery, dependiente de otra query
DERIVED - Tabla SELECT derivada (subquery en una clausula FROM)
UNCACHEABLE SUBQUERY - Una subquery cuyo resultado no puede ser cacheado y debe ser re-evaluado para cada file de la otra query.
table - la tabla a la que se refiere la información
tipo - el tipo de unión empleado, que puede ser:
sytem - SYSTEM SELECT sólo hay una fila en la tabla (tipo especial del tipo const)
const - La tabla tiene como mucho un sola fila resultado, que es leída al principio de la operación convirtiéndose en una constante para el resto de las operaciones del select (operación muy rápida)
eq_ref - Una fila es leída de la tabla por cada combinación de filas en las tablas previas.
ref - Todas las filas con valores de índices validos son leídos de esta tabla por cada combinación de filas de las tablas previas.
fulltext - El join se utiliza mediante un índice FULLTEXT
ref_or_null - Este tipo de join es como el ref, pero además MySQL hace una busqueda extra por filas que contengan valores NULL
index_merge - la columna key en la fila de salida contiene una lista de indices a utilizar, y key_len contiene una lista de las claves más largas para los índices utilizados.
unique_subquery - Este tipo sustituye al ref para algunas subqueries IN de el siguiente tipo de estructuras: value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery - Este tipo join es similar al unique_subquery. Este sustituye subqueries IN, pero funciona para índices no únicos en subqueries del siguiente tipo: value IN (SELECT key_column FROM single_table WHERE some_expr)
range - Solamente las columnas que están en un rango dado son retornadas, empleando un índice para seleccionar las columnas. La columna key indica que índice es utilizado. key_len contiene el índice más largo de la parte del índice más larga utilizada. ref es NULL para este tipo.
index - Este ipo de join es el mismo que el de ALL, con la excepción de que sólo se realiza el escaneado a través de un índice. Esto es mas rápido que ALL ya que los ficheros índice son generalmente más pequeños que el fichero de datos.
all - Se realiza un escaneado de toda la tabla por cada combinación de filas de las tablas anteriores.
Estos tipos están ordenados en cuando a lo mejor o peor en cuanto a optimización. Así un tipo all sería lo peor y un tipo system sería lo mejor. Reducir de un tipo a otro, cambiando o creando índices para una determinada tabla o modificando la estrucutra misma de la petición es la tarea básica que debemos intentar para optimizar nuestra querys.
possible_keys - La columna indica que índices se pueden seleccionar para ser usados a la hora de encontrar filas en la tabla.
key - Índica que índice es empleado finalmente (podría se uno que no estuviera listado en las possible_keys).
key_len - Longitud del índice empleado
ref - Muestra que columnas o constante son empleadas para comparar con el índice
rows - Número de filas que MySQL piensa que debe examinar al ejecutar la petición
extra - Información adicional sobre la consulta.
Cuando modifiques la estructura de una tabla o añadas índices recuerda ejecutar un:
ANALIZE TABLE nombre_tabla;
Esto hará que el optimizador analize adecuadamente la tabla, los datos los usará para ayudarle a determinar la mejor estrategia en consultas posteriores. Tras modificar la tabla ejecuta ANALIZE y vuelve a ejecutar un EXPLAIN para observar las modificaciónes en el plan de ejecución de la consulta.
Categorías : Programación
Referencias : No hay referencias »
