Optimización de bases de datos

Optimización de bases de datos

Hasta hace poco las bases de datos raramente superaban el gigabyte de capacidad, hoy en día esto es un hecho común incluso en las PYMES.

Gestionar dicha cantidad de información no es fácil, principalmente cuando una base de datos ya está en producción y se nota un bajo rendimiento en sus consultas, bien sea porque fue mal diseñada o porque no se esperaba un crecimiento tan grande.

El usuario valora enormemente que una web sea rápida y eficiente. Sabiendo esto cuanto mejor mantengamos nuestra base de datos mejor rendimiento obtendremos. Los resultados se obtendrán más rápidamente, y en consecuencia, se podrán mostrar antes.

Para conseguir mejorar nuestra base de datos y obtener unos resultados óptimos hay varios puntos que debemos considerar:

Optimizar la base de datos

• Diseñar la base de datos: es necesario cuidar que estén bien indicadas las tablas, campos y sus relaciones en función de las necesidades que tengamos. Nos facilitará el mantenimiento y garantizará un rendimiento adecuado a nuestras necesidades. Para conseguir un buen diseño de las tablas que integrarán nuestra base de datos suele utilizarse un Modelo Relacional, este es un modelo de datos basado en la lógica de predicados y en la teoría de conjuntos, donde se extraen los elementos, propiedades y relaciones entre los mismos, que se traducen en la base de datos en tablas, campos, índices y claves relacionadas.
• Una vez extraídas las tablas de la base de datos relacional, lo principal para un buen funcionamiento de nuestra base de datos es disponer de los índices correctos en las tablas, sobre los que trabajará MySQL (es el más utilizado en el mundo, de código abierto con sistema de gestión de base de datos de modelo relacional (RDBMS), que se ejecuta como un servidor que proporciona acceso multiusuario a una serie de bases de datos). La indexación, tanto de claves primarias como extranjeras, se puede obtener del modelo relacional.
• Las claves primarias identifican explícitamente a cada elemento de una tabla, esto es, pensando en cada relación como si fuese una tabla que está compuesta por registros (cada fila de la tabla sería un registro o tupla), y columnas (también llamadas campos).
• Las claves extranjeras marcan las relaciones entre tablas.

Disponer de índices en los campos adecuados optimizará sus resultados:

• Para mejorar una consulta (SELECT), hay que crear un índice sobre los campos que son utilizados en las búsquedas (los que aparecen en las cláusulas WHERE o JOIN).
• Utilice índices sobre campos con valores únicos. Los índices funcionan peor si el campo tiene valores duplicados.
• Trate de que los índices sean cortos. Si indexa un campo de texto, evite hacerlo sobre campos de longitud variable, y acorte siempre el tamaño del índice a lo que considere más adecuado. Por ejemplo, si un campo CHAR tiene 200 caracteres y sabe que los valores se distinguen en los primeros 20 caracteres, indexe sólo hasta dicho tamaño de campo. Ahorrará espacio y ganará velocidad de respuesta.
• No cree índices innecesarios. Estos se actualizan con cada cambio en la tabla asociada y pueden ralentizar las modificaciones de la misma.
• Cuando haya hecho varios cambios o exclusiones en alguna tabla ejecute la sentencia OPTIMIZE TABLE, que reparará y además ordenará la tabla para mejorar el rendimiento.
• Sea coherente con los tipos de componen sus tablas y elija siempre los más adecuados.
• Utilice los mismos tipos de campos para el mismo tipo de información en distintas tablas. Si necesitara cruzar tablas con campos del mismo tipo ganará en rapidez.
• Evite en lo posible el uso de campos de tamaño variable. Los campos de longitud fija (como CHAR) son más eficientes que los de longitud variable (VARCHAR, BLOB o TEXT).
  Utilice campos numéricos frente a campos de texto.
  Trate de usar campos que no puedan tener valores nulos (Not Null). Los valores nulos ralentizan las lecturas.
• Mantenga siempre sus tablas con la información necesaria (ni más ni menos):
  Si crea tablas con el atributo row_format, use el tipo fixed, en vez de dynamic, ya que las tablas se consultarán de modo más rápido.
• Haga “limpieza” cada cierto tiempo. Si observa que sus tablas tienen muchos registros (han crecido mucho de tamaño), analice sus datos para comprobar si algunos registros están anticuados y pueden eliminarse o archivarse. Reducir el tamaño de sus tablas mejorará su rendimiento.

Mejorar las consultas a la base de datos

Tenga siempre presente las consultas tipo que se van a realizar sobre su base de datos, durante el proceso de diseño. Eso le permitirá optimizar al máximo su estructura para obtener los mejores resultados.

• Cuando se realiza una consulta SELECT, hay que evitar en lo posible el uso del comodín “*”, e indicar sólo los campos imprescindibles que se necesitan. Eso reducirá el tamaño de la consulta.
• Evite en lo posible el uso de LIKE. Las comparaciones entre campos de texto (BLOB, TEXT…) ralentizan las consultas. Si fuera necesario, cree índices fulltext para los campos de texto sobre los que vaya a efectuar consultas.
• Evite también el uso de GROUP BY, ORDER BY o HAVING.
• Pruebe sus consultas con anterioridad, mediante el comando EXPLAIN. Le mostrará un listado informativo sobre cómo se realiza la consulta. Sintaxis: Explain select * from tabla
• Optimice la cláusula WHERE (en inglés):Para añadir registros a sus tablas, es más eficiente realizar una inserción múltiple que varias inserciones por separado. Sintaxis: INSERT INTO table (campo1, campo2) VALUES (1, ‘valor1’), (2, ‘valor2’)
• Evite el uso de paréntesis innecesarios.
• Use COUNT (*) sólo en consultas sin cláusula WHERE y que afecten a una única tabla.
• Si conoce que el resultado de una cláusula GROUP BY o DISTINCT va a ser muy reducido haga uso de la opción SQL_SMALL_RESULT. MySQL usará tablas temporales de acceso rápido para el resultado en vez métodos de ordenación.
• Fije la inserción de datos para evitar esperas innecesarias, mediante el uso de la sentencia INSERT DELAYED. La inserción se encola (agrupándose en bloques para ejecutarse de forma más eficiente) a la espera de que la tabla afectada no esté siendo utilizada por ningún otro proceso.
• Son prioritarias las sentencias de lectura (SELECT) frente a las de escritura (INSERT):Si tiene dudas sobre el rendimiento de alguna expresión, utilice la función: BENCHMARK (contador, expresión) Le mostrará el tiempo que tarda en ejecutarse.
• Con INSERT LOW_PRIORITY, consigue que las sentencias de inserción esperen a que no haya otros procesos leyendo la tabla para ejecutarse.
• Con SELECT HIGH_PRIORITY, consigue que cualquier otro proceso simultáneo de actualización o inserción de datos espere a que se realice la consulta.

Mejorar los scripts

• Reduzca el tiempo y número de conexiones a la base de datos: Numere los resultados. Limite la presentación a un número máximo (por ejemplo, 10 por página) con la opción LIMIT al final de su consulta.
• Evite que el tratamiento de la información y la presentación de la misma se realicen dentro del tiempo de conexión. Para esto, almacene la consulta en alguna variable para tratar los datos posteriormente.
• Si necesita hacer varias consultas independientes, agrúpelas para realizar una única conexión para todas ellas.
• Utilice algún sistema de caché para reducir los accesos a la base de datos, en casos de información poco cambiante que se consulte con frecuencia. Por ejemplo, puede usar alguna variable de sesión si hay datos de alguna búsqueda que se tengan que mostrar en varias páginas. Eso evitará repetir la consulta por cada página.
• Realice consultas sólo de la información imprescindible. No meta en el SELECT campos que no vaya a necesitar, y no olvide introducir un criterio de búsqueda correcto en el WHERE si relaciona varias tablas