Hace unas semanas tuve la tarea de optimizar una base de datos mysql, así que hoy voy a compartir mi experiencia y describir lo que hice para lograrlo.
Cosas de las que NO voy a hablar y que debes revisar antes de seguir:
- Problemas de espacio con la base de datos: se supone que si tu página web está en continuo crecimiento, debes tener un buen plan en tu hosting para no tener que preocuparte por problemas de espacio en disco. En mi caso, el sitio en el que trabajé contaba con un buen plan de VPS (Servidor Virtual Privado), así que el espacio no era el problema (la base de datos podría pesar tantos GB como quisiera).
- Normalización de DB: si has aplicado algo de ingeniería en tu base de datos desde el principio, no tendrías que estar viendo este detalle ahora, salvo por supuesto que se haya realizado grandes cambios en el sitio y se tenga que “reestructurar” la base de datos. En cualquier caso, no se hablará de esto aquí porque hay mucha información en internet al respecto.
- Problemas de servidor: si eres el administrador del sitio, debes asegurarte que el problema no tiene nada que ver con tu servidor o servicio de hosting. Por ejemplo, hostings como Godaddy son conocidos por ser de los peores del mercado (aun con la enorme publicidad con la que cuenta), así que antes de realizar cualquier acción brusca en tu sitio web, cerciórate primero que el problema no sea ése.
- Crear un backup de tu base de datos: Esto debería estar de forma implícita desde el comienzo de cualquier artículo o tutorial. Sin importar qué “optimización” vayas a realizar, o qué tan confiable sea el artículo que te indique cambios a realizar en una base de datos, por al amor de Dios SIEMPRE haz una copia de respaldo (backup) de toda tu base de datos.
Entonces, ¿cuál fue exactamente el problema que tuve?
El sitio web en el que trabajé era enorme y las tablas donde se guardaban los detalles de las órdenes de compra, simplemente no dejaban de crecer. La cantidad de tuplas que almacenaba cada tabla iba desde 70k hasta 300k, las cuales continuaban aumentando cada día.
Esto ocasionaba que el sitio web se colgara o se pusiera muy lento cuando comenzaban a ejecutarse consultas relacionadas a las compras, como por ejemplo: historial de compras del cliente, estadísticas de la tienda, reportes diversos, etc.
En algunos casos lo recomendable sería simplemente subir a un plan superior de VPS, pero después de analizar su base de datos me di cuenta que aquello no serviría de nada y el sitio seguiría lento.
Quizás alguno de ustedes se haya hecho la pregunta:
Hey, si tu problema solamente radica en las órdenes de compra, ¿entonces por qué simplemente no eliminaste las órdenes de compras viejas?
Lastimosamente eso no se puede realizar en la mayoría de los casos, ya que como dije antes toda esa información es de bastante utilidad para el sitio al sacar historiales y estadísticas diversas, ya sea para los clientes como también para los administradores del sitio. Además, las bases de datos están hechas precisamente para eso: ALMACENAR DATOS, de modo que no se debería ELIMINAR nada, salvo por supuesto “información basura” que realmente no tenga utilidad alguna en el sitio.
Bueno, ya le di demasiadas vueltas al asunto. AHORA VAYAMOS AL GRANO!
Solución
Después de buscar bastante por la red y preguntar en foros, finalmente logré optimizar la base de datos realizando únicamente dos acciones:
1) Cambiar el motor de almacenamiento a InnoDB
La base de datos del sitio estaba usando el motor de almacenamiento MyISAM, el cual tenía entendido proporcionaba una alta velocidad de acceso, sobre todo cuando solamente se necesita “leer datos” y no así en la “escritura”. Así que como el sitio se lenteaba al leer datos para los reportes y estadísticas, pues pensé que el problema no tenía que ver con MyISAM.
Sin embargo llegué a descubrir que lo más recomendable era siempre manejar InnoDB, aunque lo más experimentados en el tema saben en qué casos conviene mejor usar MyISAM. En todo caso, si no sabes qué motor de almacenamiento usar en mysql, ve siempre por InnoDB.
Personalmente al principio creí que iba a ser mucho más lento el sitio con InnoDB, pero todas mis dudas y temores fueron disueltos cuando apliqué la última acción que describo abajo.
2) Agregar una gran cantidad de índices
He aquí el factor más importante para optimizar una base de datos… la creación de índices.
Si bien ya sabía que los índices agilizan bastante las consultas, me di cuenta de mi error al percatarme de que no tenía una buena cantidad de índices en mi base de datos.
Sin entrar en muchos detalles, les diré que existen varios tipos de índices, pero de momento solo nos interesarán los siguientes dos:
- Índices de claves primarias: es decir el primary key con el que estoy seguro que cuentan todas sus bases de datos. No pueden tener valores duplicados.
- Índices ordinarios: aquellos que son creados de forma “manual” y que no tienen nada que ver con la llave primaria de la tabla. Estos índices pueden tener valores duplicados siempre y cuando su campo no tenga el atributo UNIQUE.
Para optimizar nuestra base de datos, tendremos que crear una buena cantidad de índices “ordinarios” para agilizar nuestras consultas.
¿Y cómo creo índices para mi tabla?
Lo primero que debes hacer es IDENTIFICAR qué campos deberían tener índices. Esto debe ser analizado para cada tabla. Por lo general deberías crear un índice a todo campo o columna que trabaje como llave foránea o foreign key. Es decir, aquellos campos que sirven para conectar tus tablas en consultas matriciales.
Por ejemplo, si tengo la tabla “order_detail” y uno de sus campos es “customer_id”, entonces obviamente que a través del campo “customer_id” puedo obtener información de una tabla “customer_information” realizando una consulta matricial.
Por lo tanto, yo debería agregar un índice para este campo realizando la siguiente consulta:
ALTER TABLE `order_detail` ADD INDEX ( `customer_id` ) ;
Y listo! El índice se creará correctamente en la base de datos. Esta consulta sólo debe realizarse una sola vez, similar a la creación de llaves primarias.
Esto nos servirá de mucho cuando se ejecute una consulta que involucren las tablas “order_detail” y “customer_information”, ya que gracias al índice creado ahora se ejecutará mucho más rápido sin importar la cantidad de tuplas que tengan dichas tablas (lean acerca de índices si quieren saber el por qué).
Ahora sólo toca crear los índices que vean convenientes para cada tabla, ya que uno solo no basta.
Por ejemplo, en la misma tabla “order_detail” yo podría tener campos como “address_id”, “shipping_id”, “coupon_id”, etc. de modo que me correspondería crear un índice para cada campo mencionado. Luego debería ponerme a examinar cada tabla de la base de datos y crear los índices respectivos.
Y eso es todo. Una vez hayan agregado una buena cantidad de índices (si su DB es grande), verán los GRANDES RESULTADOS al volver a navegar en su sitio web o sistema: historiales, reportes, estadísticas y toda información extensa será ejecutada en un abrir y cerrar de ojos.
Notas finales
- En mi caso tuve que agregar alrededor de 300 índices para optimizar mi base de datos.
- Si el nombre de todos los campos de tu base de datos siguen algún estándar o nomenclatura, te darás cuenta que puedes agilizar el proceso de creación de índices mediante un simple script PHP (tarea para la casa). Todo depende del patrón que veas en los nombres, por ejemplo: tu script podría crear un índice en todos los campos cuyo nombre termine en “_id”.
- Si tu base de datos pesa demasiado y te toma mucho tiempo crear un backup, una buena alternativa seria usar SSH para conectarte de manera remota al servidor de tu sitio y de esta forma crear un backup a través de mysqldump. Obviamente, todo esto tiene que ser hecho desde la consola/terminal (como Putty) y a través de comandos. Quizás uno de estos días cree un artículo más detallado sobre este tema.