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



  


Google

Optimización de consultas en MySQL

Este artículo es una introducción hacia la aplicación y correcto uso de la indexación en bases de datos realizadas con MySQL.

A pesar de que esta herramienta es la más importante que poseemos para elaborar consultas más rápidas, gran parte de los nuevos desarrolladores no la usa por desconocimiento o porque simplemente no la comprenden.


1. Introducción

La mejor forma de optimización de una Base de Datos consiste en un buen diseño inicial, tanto lógico como físico. El objetivo de la optimización consiste en minimizar el tiempo de respuesta para cada petición y maximizar el rendimiento de todo el sistema disminuyendo el tráfico de red, el acceso a disco y el tiempo de CPU.

Los planteamientos sobre la optimización deben ser propuestos durante todo el ciclo de desarrollo, y no sólo una vez el sistema ya esta implementado.

Cuando desarrollamos una aplicación de base de datos, debemos tener en cuenta no sólo que funcionen nuestras consultas sino que estas tengan la suficiente eficiencia como para que nuestra base de datos preste un servicio muy rápido. Para conseguir este objetivo podemos usar indexación de tablas.

  • Cuando use las funciones MIN() o MAN(), el valor mayor o menor de una columna indexada se puede encontrar fácilmente sin buscar en todas las filas.
  • MySQL utiliza a menudo índices para realizar operaciones de clasificación rápidamente sin examinar todas las filas.
  • A veces MySQL evita leer el fichero de datos por completo.

    1.1. Desventajas

    Al utilizar indexación en las tablas, podemos apreciar dos debilidades principalmente en nuestra base de datos:

  • Si tiene muchos índices, nuestro fichero puede alcanzar nuestro tamaño máximo de fichero antes que el de datos.
  • Los índices agilizan la recuperación pero hace más lentas las operaciones de insertar, borrar, así como las actualizaciones de valores en las columnas indexadas(es decir la mayor parte de las operaciones que implican escritura) porque la escritura no afecta sólo a la fila de datos, sino a menudo también a los índices.

    1.2. Elección de índices

    Algunas características a tener en cuenta a la hora de elegir los índices de nuestras tablas son las siguientes:

  • Indexe las columnas sobre las que realiza la búsqueda, no sobre las que selecciona
    Las mejores columnas para indexar son las que aparecen en la cláusula WHERE o las nombradas en las cláusulas de unión

  • Utilice índices únicos
    Los índices trabajan mejor sobre columnas con valores únicos, y peor con aquellas que tienen muchos valores duplicados. Por ejemplo, si una columna contiene edades y tiene varios valores diferentes, un índice diferenciará las filas fácilmente, sin embargo, no le ayudará tanto si se utiliza en una columna para registrar el sexo y que contiene sólo los dos valores "M" y "F" (cualquiera que sea que busque, tomará casi la mitad de las filas).

  • Utilice índices cortos
    Si va a indexar una columna de cadenas, especifique una longitud prefijada, siempre que sea razonable hacerlo así, por ejemplo, si tiene una columna CHART (200), no indexe la columna entera si la mayor parte de los valores son únicos dentro de los 10 o 20 primeros caracteres. Indexar estos 10 o 20 primeros caracteres le ahorrará mucho espacio en el índice, y probablemente hará más rápidas su consultas.

  • Aproveche los prefijos más a la izquierda
    Cuando crea un índice de n columnas, en realidad crea n índices que MySQL puede usar, Un índice de columna múltiple actua como varios índices porque cualquier grupo de columnas situado más a la izquierda en el índice se puede utilizar para hacer corresponder filas.

  • No abuse de los índices
    Los índices deben ser actualizados, y posiblemente reorganizados, cuando modifique los contenidos de sus tablas, y cuantos más índices tenga más tiempo lleva. Si tiene un índice que raramente, o nunca, se usa esta volviendo más lenta en forma innecesaria las modificaciones de la tabla. Ademas, en las operaciones de recuperación MySQL considera los índices al generar un plan de ejecución.

  • Considere el tipo de comparaciones a realizar en una columna
    Los índices se usan para operaciones =, > y BETWEEN. También se usan para operaciones LIKE, cuando el patrón tiene un prefijo literal. Si sólo usa una columna para otro tipo de operaciones, tales como STRCMP(), no tiene sentido indexarlo.

    1.3. El optimizador de consulta MySQL

    Algunas formas de optimizar las consultas en MySQL son:

  • Comparando columnas que tengan el mismo tipo
  • Intente hacer que las columnas indexadas permanezcan solas en las comparaciones
  • No utilice comodines al comienzo de un patrón LIKE
  • Ayude al optimizador a estimar mejor la efectividad de los índices
  • Use EXPLAIN para verificar la operación del optimizador.
  • Las pruebas sustituyen a los formularios de las consultas, pero se ejecuta más de una vez.

    1.4. Elección del tipo de columna y eficacia en la consulta

    A continuación se enumeran algunas pautas importantes para elegir las columnas que ayudarán a las consultas a ejecutarse un poco mas rápido:

  • Use columnas de longitud fija en lugar de columnas de longitud variable
  • No defina columnas más largas de lo estrictamente necesario.
  • Declare columnas que sean NOT NULL.
  • Considere usar columnas ENUM
  • Use PROCEDURE ANALYSE()
  • Empaquete datos en un blob
  • Use OPTIMIZE TABLE para tablas que son objeto de fragmentación
  • Use índice sintético
  • Evite recuperar valores BLOB o TEXT grandes a menos que deba hacerlo necesariamente.

    1.5. Cargar datos eficazmente

    La carga de un volumen es más rápida que la carga sencilla de filas porque la caché del índice no necesita ser actualizada después de que se cargue cada registro. Puede hacerse al final de la batería de registros.

    La carga es más rápida cuando la tabla no tiene índices que cuando está indexada. Si hay índices, no sólo se debe añadir el registro al a fila de datos, si no que también se debe modificar cada índice para reflejar la edición de un nuevo registro.

    Las sentencias SQL más cortas son más rápidas que las sentencias más largas porque aquellas implican menos análisis en la parte del servidor y porque se pueden enviar por la red desde el cliente al servidor más rápidamente.

    El optimizador de consultas de MySQL es ahora más inteligente en el uso de índices para resolver las consultas. Algunas consultas que requieren un ordenamiento extra son ahora significantemente más rápidas.

    Cambios al código de la caché de claves han producido un significante aumento en el tiempo de ejecución durante algunas consultas basadas en índices. Esto es especialmente útil en servidores que tienen demasiada carga.

    Si alguna vez has querido eliminar registros relacionados de múltiples tablas al mismo tiempo, ahora MySQL 4.0 dispondrá de borrados multi-tablas. Al especificar múltiples tablas y la cláusula WHERE correcta, MySQL hará sin problemas lo que esperas. También se pueden agregar opciones ORDER BY y LIMIT a las consultas DELETE, para obtener un mejor control sobre cuántos registros son eliminados y el orden en el que son eliminados dichos registros.

    El sistema de replicación de MySQL ha sido mejorado notablemente. Muchos de los cambios fueron hechos en anticipación del sistema de replicación fail-safe. En 4.0, el proceso mismo de replicación es multi-hilo en los servidores esclavos. Si el servidor principal llega a fallar, es ahora mucho más probable que cada esclavo tenga los datos necesarios para hacer por sí mismo una recuperación de los datos y trabajar como si fuera el servidor maestro. Los registros de replicación ahora contienen los marcadores de transacción necesarios para asegurarse que las transacciones son replicadas apropiadamente.

    El número de variables de estado en MySQL casi se ha duplicado. Ahora se puede tener un panorama mucho más claro y amplio de lo que está pasando dentro de MySQL. Muchas de las herramientas de administración de MySQL creadas por terceros han sido actualizadas para utilizar estas nuevas variables.

    1.6. Algunas mejoras de MySQL 4.0 desde la versión 3.23

    Es importante destacar que muchos de los cambios realizados al motor MySQL están relacionados con la forma en que se diseñan o se ejecutan las consultas:

    El optimizador de consultas de MySQL es ahora más inteligente en el uso de índices para resolver las consultas. Algunas consultas que requieren un ordenamiento extra son ahora significantemente más rápidas.

    En MySQL 3.23, se necesitaba recompilar MySQL para ajustar las opciones de índices de texto completo, tales como la longitud mínima de una palabra. En 4.0, las opciones de índices de texto completo han sido movidas al archivo de configuración de MySQL, así que solamente se tienen que hacer las adecuaciones necesarias y reiniciar MySQL para que los cambios tengan efecto. Muchos fallos en las búsquedas de texto completo han sido corregidos también.

    Cambios al código de la caché de claves han producido un significante aumento en el tiempo de ejecución durante algunas consultas basadas en índices. Esto es especialmente útil en servidores que tienen demasiada carga.

    Si alguna vez has querido eliminar registros relacionados de múltiples tablas al mismo tiempo, ahora MySQL 4.0 dispondrá de borrados multi-tablas. Al especificar múltiples tablas y la cláusula WHERE correcta, MySQL hará sin problemas lo que esperas. También se pueden agregar opciones ORDER BY y LIMIT a las consultas DELETE, para obtener un mejor control sobre cuántos registros son eliminados y el orden en el que son eliminados dichos registros.

    El sistema de replicación de MySQL ha sido mejorado notablemente. Muchos de los cambios fueron hechos en anticipación del sistema de replicación /fail-safe/. En 4.0, el proceso mismo de replicación es multi-hilo en los servidores esclavos. Si el servidor principal llega a fallar, es ahora mucho más probable que cada esclavo tenga los datos necesarios para hacer por sí mismo una recuperación de los datos y trabajar como si fuera el servidor maestro. Los registros de replicación ahora contienen los marcadores de transacción necesarios para asegurarse que las transacciones son replicadas apropiadamente.

    El número de variables de estado en MySQL casi se ha duplicado. Ahora se puede tener un panorama mucho más claro y amplio de lo que está pasando dentro de MySQL. Muchas de las herramientas de administración de MySQL creadas por terceros han sido actualizadas para utilizar estas nuevas variables.



    Autor: Andrés Javier Pulido Bernal
    Artículo publicado en Grupo Linux Universidad Distrital

    Este artículo ha recibido 25856 visitas.



  • ¿Conoces nuestros servicios de alojamiento web y servidores?


    Opciones


    - Enviar por email
    - Imprimir
    - Añadir a favoritos

    - Univers Dimensis
    - Listado de artículos
    - Ver ránking general


    [ Publicar artículo ]


    Artículos más leidos en Internet:

    · Errores comunes de PHP en los CMS (46868)
    · Seguridad en accesos remotos (45982)
    · Tipos de redes inalámbricas (45310)
    · Los ROOT SERVER - El árbol de 13 raíces (39425)
    · ¿Qué es un proxy? (37640)

    Vota este artículo

    Malo
    Regular
    Bueno
    Muy bueno
    Excelente
    Puntos: 0 - Votos: 0


      

    · 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.008 segundos)