Hosting, registro y alojamiento web, dominios y servidores
Tu servidor de alojamiento web, servidores y registro de dominios (acceso a página principal)
Favoritos | Recomiéndanos | Enlázate | Contacto | Buscador   
Acerca de Dimensis | Informaciones legales   
··································   
[ Acceso a la Página Principal ]   
 
  BLOG Consultas Soporte Programación Utilidades Artículos Mapa web Área privada  


Soporte técnico sobre alojamiento web y servidores de Dimensis

Información para administrar tus recursos, resolver dudas, contactar con nuestro soporte y estar siempre al día



Artículos publicados en Univers Dimensis

· Artículos por temas
· Definiciones
· Archivo de artículos
· Ranking (top)
· Sindicación (RSS)

· Artículos en catalán



  
MySql: Índices y optimización de consultas

Procesamiento de consultas

Las reglas que usa MySQL para decidir como obtener los datos de una consulta pueden llegar a ser difíciles de entender si dichas consultas son algo complejas. Afortunadamente hay unas pocas reglas y un comando que nos permiten tener un mejor entendimiento de qué es lo que está haciendo MySQL. Primero vamos a comentar las reglas:

  • MySQL no usará un índice si decide que será mucho más rápido escanear completamente una tabla. En general, si un índice le hace saber a MySQL que accesará apróximadamente el 30 por ciento de las filas de una tabla, MySQL abandona el índice y simplemente ejecuta un escaneo completo de la tabla.
  • Si múltiples índices pueden ser usados para satisfacer una consulta, MySQL usará el que sea más restrictivo -- esto es, con el que se obtengan el menor número de filas.
  • Si las columnas que estamos seleccionando forman parte de un índice, MySQL puede leer todos los datos que necesitamos desde el índice y nunca tocar la tabla en sí.
  • Cuando usamos varias tablas en una consulta, MySQL leerá primero los datos desde la tabla que regrese el menor número de filas. El orden en el que se especifican las tablas puede no ser el mismo que use MySQL. Esto afecta también el orden en el que son regresados finalmente los registros, así que debemos asegurarnos de usar una cláusula ORDER BY si necesitamos que los registros tengan un orden en particular.


Habiendo dicho esto, es importante tener en cuenta que algunas de las desiciones que toma MySQL están basadas en suposiciones, y al igual que nosotros los humanos que hacemos muchas suposiciones, puede que MySQL ocasionalmente haga alguna que sea incorrecta.

Si sospechamos que esto ha sucedido, o simplemente queremos entender qué es lo que está haciendo MySQL para procesar una consulta, podemos usar el comando EXPLAIN. La sección a continuación explica más a detalle el uso de EXPLAIN.

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.

Columna Descripción
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;


Página anterior Página anterior (3/5) - Siguiente página (5/5) Siguiente página


  

· DESTACADOS ·

Máquinas dedicadas y servidores virtuales
NAS para almacenamiento remoto accesible mediante NFS


· DESCUENTOS ·

Ofertas de alojamiento web y servidores
Allotjament de pàgines web en català



.:Webs destacadas:.

· el blog de DIMENSIS
· Cambio climático
· Productes del Camp


Productes del Camp
Especial Productes del Camp

Xarxa ECO



HacklabValls



1999-2019 Dimensis Global Communications (Los contenidos pueden compartirse bajo licencia Copyleft)
Alojamiento web, servidores virtuales, servidores dedicados y registro de dominios.
Optimizado para resolución de 800x600 y 1024x768. Requiere activación de cookies, Java y Flash.
WebSite realizado con PHP-Nuke, bajo licencia GNU/GPL. (Tiempo de carga: 0.005 segundos)