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

Utilidad y uso de EXPLAIN en MySQL

logo_mysql

A la hora de realizar una buena optimización de las consultas de MySQL habrá que prestar mucha atención a lo que dice la cláusula EXPLAIN. Su sintaxis es muy sencilla, basta anteponerlo a la consulta que queremos realizar para obtener toda la información.

EXPLAIN SELECT columna1, columna2 FROM tabla1 WHERE columna3 = ‘2’\G

El resultado que obtendremos será el plan de ejecución de la consulta a la base de datos. Una explicación de como accederá MySQL a las diferentes columnas involucradas en la consulta. EXPLAIN devolverá:

  • id: Es el identificador que EXPLAIN asignará a la consulta.
  • select_type: Tipo de consulta a analizar. Por ejemplo, si se trata de una consulta sencilla su valor será SIMPLE.
  • table: Nombre de la tabla a la que hacen referencia el resto de datos en la fila. Hay que tener en cuenta que el orden de las filas será el que utilizará MySQL para acceder a los datos.
  • type: Indica como MySQL combinará los datos de esa tabla.
  • possible_keys: Lista de los indices que se podrían utilizar, aunque podría no usarse ninguno.
  • key: Índice que finalmente se usará, si no se usa ninguno el valor del campo será NULL.
  • key_len: Tamaño del indice utilizado, si no se usó ninguno contendrá NULL.
  • ref: Muestra con que campo está relacionado el índice seleccionado.
  • rows: Número de registros que se tendrán que recuperar para ejecutar la consulta.
  • extra: Información adicional sobre la forma en que se obtendrán los datos.

Con esta información es posible encontrar donde se producen los cuellos de botella en las consultas que realizamos a la base de datos, y nos permitirá optimizarlas para evitar que una consulta lenta provoque un retraso injustificado en la devolución de resultados a los usuarios. En el manual de MySQL puedes encontrar más información.

Replicación en MySQL 5.0

La replicación en MySQL se realiza entre un servidor Maestro (el que contine los datos originales) y uno o varios servidores Esclavos (los que replican los datos). Se realiza utilizando el log binario de MySQL, cuando se realiza un cambio en la base de datos del servidor Maestro, esto cambios son enviados al Esclavo para que los actualice. Hay que tener en cuenta que la replicación sólo se puede realizar en una dirección, del Maestro hacia el Esclavo, y no bidireccinalmente.

El servidor Esclavo mantiene una conexión permanentemente abierta con el Maestro y se comprueba la ultima posición escrita en el log binario del Maestro, cuando se detectan cambios estos se replican el las tablas del servidor Esclavo.

Antes de ponerse a configurar MySQL con una replicación, hay que tener en cuenta la compatibilidad entre versiones. Como norma general debería de intentarse que la versión de MySQL del Maestro y del Esclavo fuesen la misma, y lo más actualizada posible. De todos modos, un Esclavo con una versión más actualizada que el Maestro suele funcionar, pero no al contrario.

Una vez que conocemos como funciona, y realizadas las comprobaciones de versiones podemos configurar Maestro y Esclavo para que ejecuten la replicación. Comenzaremos por la configuración del Maestro. Debemos editar el fichero my.cnf que encontraremos normalmente en /etc/my.cnf o en /etc/mysql/my.cnf dependiendo del sistema que usemos.

Continúa leyendo Replicación en MySQL 5.0

MySQL: Acceso a un servidor remoto

La instalación por defecto de MySQL no da acceso a servidores remotos. Para poder conectarse en remoto a la base de datos de MySQL son necesarias dos cosas:

  • La primera que el usuario de MySQL con el que se quiere acceder tenga permisos para acceder desde cualquier servidor. Para lograr esto es necesario indicar en el campo Host de la tabla user de la base de datos mysql el parametro que indica cualquier servidor que es el simbolo ‘%’.  En el manual de MySQL tienes más información sobre como administrar usuarios de base de datos.
  • Lo segundo es modificar en el fichero my.cnf (si mysql corre sobre una maquina linux) o my.ini (en el caso de un servidor Windows) el parámetro bind-address, que por defecto viene con 127.0.0.1, y cambiarlo a 0.0.0.0 para que sea accesible desde cualquier servidor.