Índices y optimización de consultas

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.

  • Usar ANALIZE TABLE para actualizar la distribuci�n de claves para la tabla escaneada.
  • Usar FORCE INDEX en la tabla escaneada para decirle a MySQL que use el �ndice dado. Por ejemplo.
    SELECT * FROM tabla1, tabla2 FORCE INDEX (indiceParaColumna)
    WHERE tabla1.nombreColumna=tabla2.nombreColumna;

COMPARTE ESTE ARTÍCULO

COMPARTIR EN FACEBOOK
COMPARTIR EN TWITTER
COMPARTIR EN LINKEDIN
COMPARTIR EN WHATSAPP