Salvador Mendez - Página 6 de 23 - Viendo el mundo en código

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:

query_cache_type

2.- Parseado:

3.- Planificación:

4.- Ejecución:

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

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.