 Tutoriales Índices y optimización de consultas
Analizando como se usan los índices
EXPLAIN muestra (explica) como son procesadas las sentencias SELECT por MySQL, como se usan los índices, y como se unen las tablas. Utilizar EXPLAIN puede ayudarnos a seleccionar mejores índices y escribir nuestras consultas más óptimamente. Lo único que tenemos que hacer es agregar la palabra EXPLAIN al inicio de la consulta para que MySQL nos diga como la está ejecutando. En vez de ejecutar la consulta, MySQL reportará la lista de índices que se podrían usar en la consulta y lo que conoce acerca de ellos.
EXPLAIN SELECT nombre, apellidos FROM usuarios WHERE id = 1;
A continuación explicaremos que significa cada una de estas columnas.
- table
- La tabla a la que se refieren las demás columnas en esta tabla.
- type
- El tipo de unión que se está usando. Desde la mejor hasta la peor, los tipos de uniones son system, const, eq_ref, ref, range, index, y ALL.
- system
- La tabla tiene sólo una fila.
- const
- La tabla tiene como máximo una fila que coincide, la cual será leída en el inicio de la consulta. Ya que hay sólo una fila, los valores de la columna en esta fila pueden ser considerados como constantes por el optimizador. Las tablas const son muy rápidas ya que son leídas sólo una vez. const es usado cuando se comparan todas las partes de una clave PRIMARY/UNIQUE con constantes.
- eq_ref
- Una fila será leída de esta tabla por cada combinación de filas de las tablas previas. Este es usado cuando todas las partes de un índice son usadas por la consulta y el índice es UNIQUE o PRIMARY KEY.
- ref
- Todas las filas con valores en el índice que coincidan serán leídos desde esta tabla por cada combinación de filas de las tablas previas. ref es usado si la consulta usa sólo un prefijo más a la izquierda de la clave, o si la clave no es UNIQUE o PRIMARY KEY. Si la clave que es usada coincide sólo con pocas filas, esta union es buena.
- range
- Sólo serán recuperadas las filas que estén en un rango dado, usando un índice para seleccionar las filas. La columna key indica cual índice es usado, y el valor key_len contiene la parte más grande de la clave que fue usada. La columna ref será NULL para este tipo.
- index
- Este es el mismo que ALL, excepto que sólo el índice es escaneado. Este es usualmente más rápido que ALL, ya que el índice es usualmente de menor tamaño que la tabla completa.
- ALL
- Un escaneo completo de tabla será hecho por cada combinación de filas de las tablas previas. Este es normalmente no bueno si la tabla es la primera no marcada const, y usualmente muy malo en todos los otros casos.
- possible_keys
- Los posibles índices que pueden aplicar a la tabla. Si está vacía esta celda, no hay posibles índices a utilizar.
- key
- El índice que ha sido seleccionado. Si tiene un valor NULL, entonces ningún índice será utilizado.
- key_len
- La longitud del índice usado. Entre más pequeño sea este valor, mejor.
- ref
- Las columnas del índice que se está usando, o una constante si esta es posible.
- rows
- Número de filas que considerá MySQL debe analizar para regresar los datos requeridos.
- extra
- Información extra acerca de como MySQL resolverá la consulta. Aquí se muestra una explicación de los posibles textos que podemos encontrar en esta columna.
- Distinct
- Una vez que MySQL ha encontrado una fila que coincida con la combinación de filas, éste no buscará más.
- Not exists
- MySQL fue capaz de hacer una optimización LEFT JOIN sobre la consulta y no examinará más filas en la tabla para la combinación de filas previa después de que encuentre una fila que coincida con el criterio LEFT JOIN.
- range checked for each record (index map: #)
- MySQL no encontró un buen índice que usar, así que para cada combinación de filas en las tablas precedentes, hará un chequeo en cual índice usar (si hay alguno), y usará este índice para recuperar las filas desde la tabla. Esto no es lo más rápido, pero es mejor que hacer un join sin un índice.
- Using filesort
- Cuando veamos esto, la consulta necesita ser optimizada. MySQL necesita hacer un paso extra para encontrar la forma de ordernar las filas que serán regresadas.
- Using index
- La información de las columnas es recuperada desde la tabla usando sólo información en el índice, sin tener que leer la fila actual. Esto sucede cuando todas las columnas requeridas son parte del mismo índice.
- Using temporary
- Cuando veamos esto, la consulta necesita ser optimizada. Para resolver la consulta MySQL necesita crear una tabla temporal para mantener el resultado. Esto sucede típicamente cuando se hace un ORDER BY sobre un conjunto de columnas diferente al usado en un GROUP BY.
- Where used
- Una cláusula WHERE será usada para restringir cuales filas serán comparadas en contra de la siguiente tabla o enviada al cliente. Si no deseamos regresar todas las filas desde la tabla, y el join es del tipo ALL o index, es muy probable que hayamos escrito algo mal en la consulta.
Si deseamos obtener consultas que se ejecuten lo más rápido posible, debemos ser cuidadosos cuando veamos información extra del tipo Using filesort o Using temporary.
Podemos obtener una buena indicación de que tan buena es una consulta al multiplicar todos los valores de la columna rows en la salida de EXPLAIN. Esto nos dice aproximadamente cuántas filas debe examinar MySQL para ejecutar una consulta. De lo que se trata es que podamos ir mejorando una consulta progresivamente usando la información proporcionada por EXPLAIN.
 Cómo evitar escaneos completos de tablas
La salida de EXPLAIN mostrará ALL en la columna type cuando MySQL hace un escaneo de tabla para resolver una consulta. Esto sucede usualmente bajo las siguiente condiciones:
- La tabla es demasiado pequeña que es más rápido hacer el escaneo de la tabla que buscar una índice. Este es el caso común para tablas con menos de 10 filas.
- No hay restricciones usables en las cláusulas ON o WHERE para las columnas indexadas.
- Se están comparando columnas indexadas con valores constantes y MySQL ha calculado que las constantes cubren una gran parte de la tabla y que el escaneo completo será más rápido.
- Se está usando una clave con baja cardinalidad (muchas filas que coinciden con el valor clave) en contra de otra columna. En este caso, MySQL asume que el usar el índice probablemente se harán una gran cantidad de búsquedas adicionales de claves y que un escaneo de la tabla será más rápido.
Para tablas pequeñas, un escaneo de la tabla es frecuentemente apropiado. Para tablas muy grandes, podemos intentar las siguientes técnicas para evitar que el optimizador de consultas de MySQL escoja incorrectamente un escaneo completo.
|
|
|
