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

Configurar Subversion para controlar las versiones del código

Para los que nos dedicamos al desarrollo de software es de vital importancia poder hacer un seguimiento de todos los cambios que realizamos en una aplicación, sobre todo cuando se trata de trabajar en equipo con distintos programadores realizando distintas tareas, e incluso llevar un control de los cambios que realizan distintos equipos dentro de un mismo software.

Cuando el proyecto está bajo el paraguas del software libre, herramientas como las proporcionadas por www.sourceforge.net son de mucha ayuda. Entre ellas se encuentra el uso de Subversion o CSV para la gestión de versiones. Personalmente prefiero subversion.

Este es un pequeño HOWTO de como configurar subversion corriendo bajo Apache, se presupone que se tiene instalado Apache, Subversion, el módulo DAV para Apache y las herramientas de administración de Subversion.

Lo primero que debemos hacer es crear un directorio para nuestro repositorio. Nuestro directorio principal para guardar nuestro control de versiones sobre subversion será /var/subversion/, dentro crearemos un subdirectorio donde se almacenarán los datos con subversion:

# mkdir /var/subversion/repositorio

Ahora debemos crear la estructura de subversion para almacenar las versiones y asignarle permisos para poder acceder:

# svnadmin create /var/subversion/repositorio/
# chmod 777 -R /var/subversion/repositorio/

Con esto ya tenemos listo el repositorio, ahora debemos generar el acceso a través de URL, para ello usaremos el módulo de Apache WebDav. Editaremos el fichero de módulo DAV de Apache (en Devian lo encontraremos en /etc/apache2/mods-available/dav_svn.conf), al final del fichero incluiremos las siguientes líneas:

# Acceso repositorio SVN

DAV svn
AuthType Basic
AuthName «Servidor Subversion»
SVNPATH /var/subversion/repositorio

En «Location» debemos poner la URL por la que queremos acceder al repositorio y en SVNPATH debemos colocar la ruta absoluta hacia el directorio que contendrá los ficheros de nuestro repositorio. Con esto ya debería estar funcionando nuestro repositorio con Subversion, basta con enlazarlo desde cualquier IDE que soporte control de versiones y comenzar a guardar las versiones de vuestros proyectos.

Como subversion no es sólo lo que he comentado, existe un estupendo manual donde podéis encontrar todas las funciones que ofrece este gestor de versiones.

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.

Los primeros pasos en el mundo del golf

Hasta hace unos meses el golf estaba lejos de lo que consideraba deporte. Al menos en su parte amateur, pues en la vertiente profesional tenía claro que meter una minúscula bola en un pequeño agujero a muchos metros de distancia no podía ser nada fácil. Pero la vertiente amateur no la tenía tan clara, me parecía más un jueguecito para mayores con mucho tiempo libre que un deporte.

La cosa comenzó a cambiar habrá un par de meses, por primera vez pisé un campo de golf para «tirar unas bolas» en el campo de prácticas. La primera impresión confirmaba mi concepto del golf: chorradas para mayores con tiempo libre.

Días más tarde una conversación con un compañero de trabajo sobre como había que tirar, como había que colocarse, etc. picó mi curiosidad y decidí volver al campo de prácticas, a estas alturas ya me habían dicho que no podía pisar el campo hasta que no supieses jugar.

En esa segunda visita al campo comencé a darme cuenta de algo que llamó aún más mi atención: a pesar de que soy un chico joven que hace deporte con frecuencia era incapaz de lanzar más lejos que un hombre que debía rozar los 60 años y que se encontraba practicando a escasos metros de mi.

Lo que más me llamaba la atención era el sonido de su palo, su tiro tenía un sonido parecido a un látigo cuando corta el viento y yo era incapaz de hacerlo. Además el sonido cuando su palo impactaba con la bola era limpio, suave, digamos que sonaba bien. Por más que lo intenté no conseguí arrancar ese sonido de mis tiros.

Creo que eso fue lo que me llevó a plantearme ir a clases de golf. Paralelamente un compañero de trabajo me comentó que estaba preparando con unos amigos un torneo de golf, ese fue el empujón final. Cuando quise darme cuenta mi competitividad había tomado el control y ya estaba pidiendo apuntarme.

En ese punto es en el que me encuentro ahora, hoy he recibido mi cuarta clase de golf y espero poder tener dentro de poco el handicap que me permita salir al campo y poder competir con otros golfistas.

En cuanto a mi visión del golf, ha cambiado radicalmente, exige coordinación, cierta condición física, una buena situación geoespacial del propio cuerpo y sobre todo mucha técnica. Pero sobre todo el golf es un deporte que engancha.

Por cierto, tengo un profesor que es un crack, con cuatro clases ha conseguido que algunas veces, más bien pocas, consiga arrancar ese sonido del palo cortando el aire y ese «tit» particular de cuanto la cabeza del palo golpea correctamente la bola.

Limitar el tamaño máximo del correo con Qmail

Algo muy frecuente, más de lo que parece a pesar de que el sentido común indique lo contrario, es que los usuarios de un servidor de correo intenten enviar auténticos «ladrillos» a través del correo electrónico. Para la mayor parte de los usuarios 100 KB o 100 MB les suena más o menos a lo mismo e intentarán enviarlo a través del correo sin siquiera pararse a pensar si es o no razonable.

Un amigo hizo una buena comparación para que se vea la diferencia, en el caso de los 100 MB es como si se intentase enviar por correo postal una lavadora, a todo el mundo le parecería una autentica barbaridad.

Para evitarlo, podemos limitar el tamaño máximo de los correos que son enviados con Qmail. Para ello nada más sencillo que indicar el tamaño máximo en el fichero /var/qmail/control/databytes que se quiere establecer expresado en bytes. Por ejemplo para limitar el tamaño de los correos a 10 MB escribimos en el fichero 10485760 .

Guardamos el fichero y reiniciamos el servicio, con esto ya estará establecido el límite máximo del tamaño del correo. El usuario recibirá un mensaje o un correo de aviso de que no puede enviar más «ladrillos» por email.