Optimización de base de datos

Ramón Saquete

Escrito por Ramón Saquete

optimización de base de datosYa comenté en otra entrada sobre imágenes y WPO que los factores que más suelen afectar al tiempo de carga global, son las imágenes en el front-end y el acceso a base de datos en el back-end. Hoy toca hablar de optimización de base de datos.

La optimización del acceso a los datos es vital para el tiempo de carga de la página, debido a que suele ser el factor que más afecta al tiempo que tiene que esperar el navegador para recibir el HTML. Este tiempo de espera es muy importante, ya que el resto de recursos de la página (imágenes, scripts y hojas de estilo), no se empiezan a bajar hasta que el navegador no lee el HTML desde el que se hace referencia a estos recursos.

Este tipo de optimización es probablemente la más compleja de todas, en primer lugar porque depende de dos factores variables en el tiempo: por un lado, de cómo y de qué tipo son las consultas que se van a realizar y, por otro, de la carga de trabajo que tenga que soportar el servidor o servidores. En segundo lugar por la gran cantidad de conocimientos que hay que tener para saber reescribir consultas, reescribir el código que ejecuta las consultas, crear índices, vistas materializadas, particiones horizontales y verticales, réplicas, tablas de apoyo, saber elegir los tipos de datos a usar, saber optimizar el esquema sin perder la lógica del modelo de negocio, saber ajustar los parámetros de configuración del SGBD, conocer y saber usar sistemas de caché externos. Además cada uno de estos puntos, de los que hay muchos libros escritos, se debe abordar de forma distinta dependiendo de qué SGBD se trate (MySql, Sql Server, Oracle,…) e incluso de qué versión.
sistemas gestores de bases de datos nosql

Si nos vamos a SGDB no relacionales o NoSQL, tenemos una gran variedad de tipos de modelo de datos: modelos clave/valor, orientados a grafos, columnares, multidimensionales, orientadas a objetos, etc. Pero el modelo de datos que más ha popularizado el término NoSQL, son las bases de datos orientadas a documentos, donde tenemos parejas clave/valor, donde el valor suele ser una estructura jerárquica. Muchos desarrolladores se tiran a la piscina al usar estos nuevos SGBD (MongoDb, Google BigTable, Apache Cassandra,…), pensando en la velocidad que van a ganar, pero sin pensar en que esa velocidad que logran estos SGBD, la consiguen no implementando características ACID ni operaciones JOIN. Las primeras permiten no terminar con datos corruptos e inservibles, y al elegir un NoSQL, se supone que las operaciones JOIN no se van a necesitar nunca.

Lo que pasa en estos casos es que los requisitos del cliente cambian, ya sea durante el desarrollo o el mantenimiento, por lo que el esquema de la base de datos tiene que modificarse, dejando de ser el SGBD NoSQL una alternativa que se pueda ajustar al modelo de negocio, siendo al final el programador el que termina implementado en el código lo que hace el SGBD relacional, pero con código mucho más lento del que tendríamos con uno de estos SGBD. Con esto no estoy diciendo que con los SGBD Relacionales, no haya que implementar código para ajustar el modelo lógico de la base de datos al modelo conceptual del negocio, si no que será mucho menos que con un SGBD NoSQL.

Para lo que sí que vienen muy bien los NoSQL, sobre todo los del tipo clave/valor, es para usarlos como sistemas de caché, ya sea de sólo memoria como memcached o híbrido como Redis que son un buen complemento a cualquier SGBD Relacional que tenga que realizar muchas lecturas.
Ahora que ya os he esbozado un pequeña idea del panorama general, veamos brevemente, unos pocos trucos interesantes para situaciones comunes en las aplicaciones Web.
Para las siguientes explicaciones voy a usar la nomenclatura estándar. Comento esto para que tengáis en cuenta que cuando digo relación me refiero a lo que muchos llaman incorrectamente tabla en SGBD relacionales.

Consultas rápidas de estructuras en árbol

Las estructuras de datos en árbol son muy comunes en la Web, las tenemos, por ejemplo, en migas de pan, menús con submenús anidados e hilos de comentarios. Normalmente siempre que se quiere implementar en una base de datos relacional, se crea una clave ajena reflexiva que va de una columna padre hacia la clave primaria de la relación. Ejemplo:

IDPadre

IDPadre
1NULL
21
32

El problema es que para saber cuáles son todos los niveles anteriores de un nodo o todos los descendientes tendremos que hacer varias consultas o varias operaciones JOIN con la propia tabla, una por cada nivel del árbol y esto es muy ineficiente.
Hay muchas soluciones posibles a este problema, pero la que más me gusta, es la de almacenar todos los nodos que cuelgan de otro en una nueva relación muchos a muchos también reflexiva, en la que tendremos como clave primaría compuesta, el nodo ancestro y el nodo descendiente que serán a su vez claves ajenas a la clave primaria de la relación original. Contaremos además con otra columna para saber el nivel de distancia entre los nodos. Ejemplo que muestra la misma información que en el ejemplo anterior:

AncestroDescendienteNivel
121
132
221

De esta forma, con un solo JOIN con la relación original podemos obtener todos los ancestros o los descendientes de un nodo y mostrarlos en la Web.

Optimizar la paginación

La paginación suele ser una tarea costosa cuando tenemos que mostrar varios números de página, porque para eso se tiene que calcular el número de filas en la relación, lo que puede requerir una lectura completa de la misma, dependiendo del SGBD. Siempre será más eficiente mostrar enlaces de anterior y siguiente, recuperando todas las filas a mostrar y una más, de manera que se muestra el enlace de siguiente página, si nos llega esta fila adicional desde la BD. Alternativamente, podemos mantener en una relación independiente el número de filas.
Otra cosa que puede afectar a las paginaciones, es que cuando estamos en una página interior se deben descartar un cierto número de filas, pertenecientes a las páginas anteriores, y que son leídas del disco con todas las columnas que aparezcan en la consulta. Una forma de evitar esto es realizar un JOIN con una subconsulta que recupere los identificadores que necesitamos antes de leer toda la información. Ejemplo:
SELECT dato1, dato2, dato3 FROM Foo INNER JOIN( SELECT id FROM Foo ORDER BY dato1 LIMIT 50,10) AS FooPaginado USING(id);

Mostrar filas aleatorias

Suele ser bastante común mostrar resultados aleatorios de una relación para dar sensación de frescura en los contenidos. El problema es que esto se suele implementar de formas bastante ineficientes, como por ejemplo ordenar por un valor aleatorio, lo que suele provocar que se lean todas las filas de la tabla. En MySQL, si tenemos identificadores autonuméricos, lo mejor es cruzar con identificadores aleatorios. A continuación un ejemplo de cómo implementar esto, para recuperar una fila aleatoria de la relación Foo:

MySQL: SELECT name
  FROM Foo AS f1 JOIN
       (SELECT (RAND() * (SELECT MAX(id) FROM Foo)) AS id)
        AS f2
 WHERE f1.id >= f2.id
 ORDER BY f1.id ASC
 LIMIT 1

En otros SGBD tenemos soluciones propietarias:

Sql Server: SELECT * 
       FROM Foo 
       TABLESAMPLE (1 ROWS);
Oracle: SELECT * 
       FROM (SELECT * 
              FROM Bugs SAMPLE (1) 
              ORDER BY dbms_random.value) 
       WHERE ROWNUM=1

Cachear las consultas más frecuentes

Activar la caché de base de datos del SGBD a veces puede empeorar el rendimiento. Esto depende del volumen de datos al que se acceda más frecuentemente. Si el volumen de datos es alto y se actualiza frecuentemente, estaremos siempre escribiendo en la caché en lugar de leer de ella. Así que normalmente es preferible recurrir a un sistema de caché externo, en el cual podamos controlar que se van a cachear realmente los datos más frecuentes y más frescos. Si el sistema de caché no nos provee de esta funcionalidad, podemos implementarla nosotros. Una buena forma de hacerlo sin el sobrecoste de almacenar el número de veces que se accede a una consulta, es generar un número aleatorio y cachear la consulta si ese número pasa de cierto valor. Por ejemplo, supongamos que generamos un número aleatorio entre 1 y 100 y la consulta se cachea si dicho número es menor que 10, de esta forma se guardará la consulta en caché con una probabilidad del 10% en cada petición, así si la consulta tiene más peticiones, será más probable que se guarde en caché.

Optimización de consultas

Para finalizar, algunos breves consejos para optimizar consultas:

  • Cambiar los OR por IN, cuando tenemos más de un valor para comparar.
  • Minimizar el coste de los JOIN: La concatenación natural o JOIN es la operación más costosa de las bases de datos relaciones, ya que requiere realizar una multiplicación cartesiana y una selección de valores. Algunas técnicas que podemos usar para minimizar su efecto consisten en:
    • Reordenarlos para concatenar primero las relaciones con menos filas para reducir el número de cruces.
    • Crear subconsultas en donde se filtren o limiten el número de filas de las relaciones grandes antes de realizar los siguientes JOINs.
    • A veces, dividir una consulta en varias, es mejor que hacerlo todo con una sola consulta, de forma que podemos obtener en una primera consulta unos pocos identificadores que podemos pasar con un IN a la siguiente consulta, en lugar de realizar un JOIN.
    • Cambiar los JOIN por EXISTS si no se va a mostrar ningún dato de la relación con la que se realiza el cruce.
  • Tener en cuenta el problema del N + 1: El n+1 se produce normalmente cuando tenemos un listado en el que para mostrarlo como queremos, por cada ítem necesitamos realizar una consulta adicional (el más uno del n+1). En este caso, suele ser mejor realizar uno o varios JOIN adicionales, en la consulta que recupera el listado de ítems. De esta forma obtenemos el listado tal y como lo necesitamos, y no se tienen que lanzar consultas adicionales para cada ítem.
  • Especificar siempre los nombres de las columnas en las SELECT, si no el SGBD leerá todas las filas del disco. El asterisco se debe usar sí y solo sí se utiliza COUNT, en cuyo caso el SGBD sabrá que no tiene que leer todas las columnas.
  • Crear índices: los índices permiten un acceso a los datos no secuencial mucho más rápido, pero son costosos de crear, así que no es conveniente su uso si tenemos muchas más lecturas que escrituras. Debemos analizar el plan de ejecución de las consultas (cada SGBD tiene su manera de verlo) para saber donde debemos crear índices.
    Normalmente, crearemos los índices en claves ajenas y en las columnas que se usen con ORDER BY o WHERE. Si se crean índices compuestos, se deben poner las columnas en el mismo orden que se vayan a usar en las consultas.

Hasta aquí eso es todo, espero que lo hayáis disfrutado.

  •  | 
  • Publicado el
Ramón Saquete
Ramón Saquete
Desarrollador web y consultor SEO técnico en Human Level. Graduado en Ingeniería Informática e Ingeniería Técnica en Informática de Sistemas. También es Técnico Superior en Desarrollo de Aplicaciones Informáticas y posteriormente obtuvo la Certificación de Aptitud Pedagógica. Experto en WPO e indexabilidad.

¿Y tú qué opinas? Deja un comentario

Por si acaso, tu email no se mostrará ;)

Entradas relacionadas

es