HeiddiSQL 7.0

Ya está disponible la versión 7.0 de este magnífico gestor de bases de datos. Entre los cambios más notables destaca:

  • Soporte básico para Microsoft SQL Server
  • Botón para detener consultas largas
  • Gestión de usuarios: propuesta de password aleatorio y soporte SSL
  • Soporte para autenticación en MariaDB 5.2, con indicación con iconos de MariaDB y Percona Server
  • Correcciones varias en el uso con Wine
  • Recuperación el SQL original en VIEW editor

Puedes consultar el resto de cambios de la versión 7.0 aquí. Descargar HeiddiSQL 7.0

Tipos de índices en MySQL

Los índices son un grupo de datos vinculado a una o varias columnas que almacena una relación entre el contenido y la fila en la que se encuentra. Con esto se agilizan las búsquedas en una tabla al evitar que MySQL tenga que recorrer toda la tabla para obtener los datos solicitados.

Por tanto la creación de índices es de vital importancia para acelerar las consultas a base de datos, sobre todo cuando las tablas presentan un tamaño considerable. Pero los índices no son el remedio a todos los males, puesto que estos cambian cada vez que la columna asociada se modifica, no se deberían crear indices sobre columnas en las que son frecuentes las operaciones de escritura. También habrá que tener en cuenta que los índices ocupan espacio, en algunos caso más que la tabla a la que hacen referencia.

En MySQL hay cinco tipos de índices:

  • PRIMARY KEY: Este índice se ha creado para generar consultas especialmente rápidas, debe ser único y no se admite el almacenamiento de NULL.
  • KEY o INDEX: Son usados indistintamente por MySQL, permite crear indices sobre una columna, sobre varias columnas o sobre partes de una columna.
  • UNIQUE: Este tipo de índice no permite el almacenamiento de valores iguales.
  • FULLTEXT: Permiten realizar búsquedas de palabras. Sólo pueden usarse sobre columnas CHAR, VARCHAR o TEXT
  • SPATIAL: Este tipo de índices solo puede usarse sobre columnas de datos geométricos (spatial) y en el motor MyISAM

Funcionamiento del proceso de consulta en MySQL

Cuando una consulta, del tipo que sea (INSERT, SELECT, GRANT, …) es enviada a MySQL se ejecuta un procedimiento que consta de cuatro pasos: la caché de consultas, el parseado, la planificación y la ejecución.

      1.- La caché de consultas: Cuando se recibe una consulta de tipo SELECT se ejecuta un algoritmo de hash y comprueba si tiene alguna consulta idéntica almacenada, en caso afirmativo devolverá el resultado cacheado. La caché de consultas es muy útil en tablas que no cambian frecuentemente y donde se realizan muchas peticiones idénticas, suele ser el caso de páginas web dinámicas que muestran el resultado almacenado en base de datos. Se activa con la directiva query_cache_type en el fiche my.cnf (OFF o 0 desactiva la caché, ON o 1 activa excepto cuando se usa SQL_NO_CACHE y 2 solo guarda resultados en caché cuando se indica expresamente con SQL_CACHE).

      2.- Parseado: Si la consulta no se encuentra en caché se parsea, comprobando la sintaxis de la consulta y dividiéndola en sus elementos básicos. Se identifica el tipo de consulta, las tablas que están involucradas y se gestiona la cláusula WHERE para su ejecución.

      3.- Planificación: Una identificados todos los elementos que intervendrán en la consulta llega el momento de decidir como será ejecutada. Con EXPLAIN podemos ver el plan que seguirá MySQL para resolver una consulta, lo que nos ayudará a modificar las intenciones de resolución escogiendo la que más nos convenga.

      4.- Ejecución: Con todo decidido resta ejecutar la consulta y devolver el resultado.

Y hasta aquí todo el misterio que encierra la ejecución de una consulta sobre MySQL. El modo en el que ayudamos a que MySQL resuelva eficazmente las consultas realizadas determina en la mayoría de los casos el resultado final de rendimiento de una base de datos.

Habitualmente la mayor parte de las bases de datos están mal aprovechas, bien porque han crecido más de lo planteado o por un mal diseño del almacenamiento. Muchas veces se suele intentar solucionar a golpe de mejora de hardware, cuando en realidad una buena optimización ayudando a MySQL a ejecutar los procesos de consulta suele repercutir en una mejora mucho mayor que ofrecerle un hardware más potente.

MySQL: Mejorar las consultas a la base de datos

Como segunda parte de la optimización de MySQL podemos mejorar las consultas que realicemos sobre la base de datos. A pesar de ser una tarea tediosa, de vez en cuando es conveniente realizar una revisión de las consultas, comprobando que siguen siendo igual de eficaces que cuando las planteamos.

  • Como primera recomendación al hacer una consulta a una tabla es no usar “*” en el SELECT. ¿Si no se van a usar todos y cada uno de los campos definidos en esa tabla que sentido tiene que usemos toda la información? Ninguno, pues eso, definir la consulta con SELECT campo1, campo2, … FROM dará mejor resultado
  • Probar la consulta que vamos a implementar con EXPLAIN y analizar el resultado. Intentaremos que las consultas que realicemos no contengan ALL en la columna type, puesto que en ese caso se hará un escaneo completo de la tabla.
  • Los campos utilizados con la cláusula WHERE son buenos candidatos a ser índices, comprueba con EXPLAIN como los usaría MySQL para determinar si está aconsejado su uso.
  • Evitar el uso innecesario de LIKE y RLIKE, pues realizar comparaciones extensas llevará mucho más tiempo. Si se usan tablas MyISAM pueden utilizarse índices FULLTEXT sobre los campos de texto donde se realice la comparación.
  • Al insertar registros, resulta más eficiente la escritura de varias sentencias la mismo tiempo que varias por sepado. Con INSERT DELAYED puedes generar una cola que esperará hasta que la tabla no esté siendo utlizada (No puede usarse con todos los motores). Utilizando INSERT LOW_PRIORITY darás prioridad a las sentencias de escritura y con SELECT HIGH_PRIORITY darás prioridad a las sentencias de consulta.
  • Cuando la cantidad de resultados a devolver por MySQL no va a ser utilizada en su conjunto, como cuando se usa un paginador, devolver la cantidad apropiada utilizando la cláusula LIMIT.

Ante todo hay que mantener una premisa básica: no guardar datos innecesarios y no extraer información que no se va a utilizar.

Optimización de bases de datos MySQL

Mantener una base de datos optimizada es una de las claves para lograr una mejora importante en el rendimiento, tanto a nivel de base de datos como una mejora significativa en el rendimiento de las aplicaciones que hacen uso de ellas. Aunque voy a enfocarlo en MySQL, podría aplicarse a la mayoría de gestores de base de datos.

Podríamos centrarnos en tres escenarios a la hora de acometer una optimización de MySQL: la optimización de la base de datos, la mejora de las consultas que se realizan sobre ella y por último, pero no menos importante, el pulido de los programas que trabajan con los datos. Hoy vamos a centrarnos en la primera parte.

Optimizar la base de datos

  • El primer punto a tener en cuenta será el diseño de la base de datos en el momento de su creación. No hay mejor remedio que hacer bien las cosas desde un principio. Habrá que pararse a calcular la carga que va a soportar MySQL, tanto a nivel de accesos como la cantidad de datos de habrá que almacenar. También es importante pensar como se extraerán los datos a la hora de hacer las tablas y que relaciones mantendrán esos datos con otras tablas y campos.
  • Para el uso de cada tipo de campo sobre nuestras tablas debemos buscar siempre los más adecuados al tipo de dato que tendremos que almacenar. Un campo incorrecto forzará a trabajar más de lo necesario a nuestro motor de base de datos. Debemos evitar el uso de campos de longitud variable si no los necesitamos, intentaremos usar campos numéricos en lugar de campos de texto y evitaremos que los campos puedan contener NULL (ralentizará las consultas innecesariamente).
  • Una vez tenemos el modelo de nuestra base de datos, debemos pensar en como se consultarán los datos para generar los índices. Cuando mejores sean los indices que tengamos, con mayor rapidez realizará MySQL las consultas solicitadas. Para ello podemos hacer uso de EXPLAIN para que nos muestre como se harán las consultas y que índices utilizará MySQL. Como orientación será bueno tener índices sobre aquellos campos que sean utilizados en las clausulas WHERE o JOIN, y no crees índices innecesarios, se actualizarán con cada modificación haciendo un trabajo innecesario.
  • Otro punto importante y en el que la mayoría no se fijan, piensa si no será mejor que la base de datos viva por su cuenta y que las aplicaciones accedan a los datos a través de procedimientos almacenados y funciones. De este modo será posible realizar modificaciones no previstas sin tener que tocar nada en las aplicaciones que acceden a los datos.
  • Hasta aquí sobre la creación y diseño, ahora viene el apartado de mantenimiento de los datos. Primer consejo y el más importante: mantener la base de datos con la información necesaria. Para ello cada cierto tiempo hay que realizar limpieza y eliminar o archivar aquellos datos que ya no sean necesarios. Por otra parte, cuando se han realizado muchos cambios debemos volver a reorganizar la información, para ello ejecutamos OPTIMIZE TABLE, que se encargará de reparar y optimizar los datos e índices de las tablas.
  • Puede que en determinado momento se produzca una inserción masiva de datos por alguna circunstancia imprevista. Llegados a este punto, lo mejor es volver a analizar si el diseño de la base de datos se sigue correspondiendo con el objetivo definido durante su diseño inicial. Suele ser una de las cosas que más pereza da realizar, sobre todo porque tendrá incidencia sobre las aplicaciones que ya utilizan la base de datos, pero que mejor resultado puede dar cuando hablamos de optimización. En este apartado es cuando vienen muy bien tener procedimientos almacenados y funciones.

Mejorar las consultas a la base de datos

Liberada HeidiSQL 6.0

HeidiSQL es una interfaz para la administración de MySQL que permite gestionar las bases de datos a través de una intuitiva interfaz para Windows, disponible para 2000, XP, Vista y 7, aunque también se puede instalar en Linux con Wine.

Esta nueva versión de HeidiSQL [descarga] es el resultado de 331 cambios realizados sobre la versión 5.1 y que incluye algunas nuevas funcionalidades como:

  • Resultados por lotes en una misma ficha de consulta
  • Múltiples conexiones en una sola instancia de la aplicación
  • Resultados de la consulta editables
  • Administración de usuarios utilizando la sintaxis GRANT y REVOKE
  • Ejecución de la consulta actual con [Mayús + Ctrl + F9]
  • Vista previa de imágenes incluidas en campos BLOB

HeidiSQL es una buena alternativa a MySQL Workbench para la gestión de bases de datos, además incluye una versión “portable” para poder llevarlo siempre cargado en un USB y tenerlo disponible en cualquier momento, nunca se sabe cuando se va a necesitar acceder a una base de datos.

Procedimientos almacenados en MySQL

Los procedimientos almacenados son un conjunto de instrucciones SQL que se almacenan en el servidor permitiendo hacer referencia al procedimiento almacenado para ejecutar esas funciones en lugar de tener que llamarlas individualmente.

Para poder ejecutar procedimientos almacenados es necesario tener la tabla proc en la base de datos mysql y contar con los permisos necesarios para trabajar con ellos. Estos son los permisos relacionados con los procedimientos:

  • CREATE ROUTINE: Permite crear procedimientos almacenados
  • ALTER ROUTINE: Da acceso a la modificación y borrado, se aplica por defecto al creador de un procedimiento.
  • EXECUTE: Requerido para poder ejecutar procedimientos almacenados, se otorga automáticamente al creador del procedimiento. Por defecto, quien tiene acceso a la base de datos también lo tiene para ejecutar los prodecimientos almacenados para esa base de datos.

Vamos a ver un ejemplo de como trabajar con procedimientos almacenados con un sencillo ejemplo. Para crearlo usaremos:

CREATE PROCEDURE sumar (IN valor INTEGER, OUT resultado INTEGER)
SELECT valor+valor INTO resultado;

Lo que estamos indicando es que se cree el procedimiento “sumar” con dos parámetros “valor” y “resultado”, ambos del tipo INTEGER. El primero es un parametro de entrada y el segundo la variable donde almacenaremos el valor de salida. Ahora vamos a llamarlo usando:

CALL sumar(4, @resultado);

El procedimiento se encargara de hacer la suma de 4+4 y almacenar la salida en la variable @resultado. Para visualizar lo que contiene la variable:

SELECT @resultado;

Los procedimientos son una buena forma de trabajar con los datos almacenados en una base de datos, dejando a MySQL el trabajo de formatear y filtrar el manejo de los datos, por lo que la cantidad de información que ha de viajar entre la base de datos y nuestro programa se reduce considerablemente. Por otra parte usando dos servidores, uno para el programa y otro para la base de datos, podríamos trasladar parte de la carga de procesamiento a la base de datos.