Técnicas para la optimización de bases de datos

No guardes datos constantes, o casi constantes

Alguna vez habrás necesitado hacer un formulario donde el usuario elija un país, una provincia,... o una licencia (Creative Commons, Copyright,...). Estos listados son constantes o casi constantes. Muchas veces lo que se tiende a hacer es guardar estos datos constantes en una tabla y hacer restricciones referenciales en el resto de tablas para evitar valores incorrectos. Mi consejo es que estos datos constantes se guarden en el código de la aplicación (o en un fichero de configuración), y que la validación también la haga la aplicación, no la base de datos.

Así por ejemplo podríamos tener una lista de países (en inglés):

countries = ['Afghanistan',..., 'Zimbabwe']

Pero... querremos internacionalizar la aplicación. Bien, para internacionalizar los datos constantes y ganar espacio en la base de datos podemos crear identificadores que sean independientes del lenguaje. En algunos casos podemos encontrar que para algunos tipos de daots ya alguien se ha preocupado de este asunto. Por ejemplo para los identificar países podemos usar su código ISO en vez del nombre.

countries = ['AF',..., 'ZW']

Podemos crear un fichero de internacionalización para inglés:

country.AF = Afghanistan
...
country.ZW = Zimbabwe

Y otro para español:

country.AF = Afganistán
...
country.ZW = Zimbabue

Si para nuestros datos no contamos con identificadores pre-existentes, tendremos que craer los nuestros. Por ejemplo en debugmodeon tenemos una lista de identificadores para las licencias de los artículos:

public static List<String> licenses = Arrays.asList("copyright", "pd", "by", "by-nc",
            "by-nc-nd", "by-nc-sa", "by-nd", "by-sa");

En caso de que estos datos cambien, habrá que cambiar el código, o en su defecto el fichero de configuración correspondiente. Pero este es un iconveniente pequeño porque se supone que estos datos no cambian, o cambian de forma muy excepcional. Por ejemplo ¿cada cuánto cambia la lista de países del mundo?

Utilizando esta téncica ahorraremos muchas consultas a la base de datos y nuestra aplicación será más rápida.

Guardar estructuras de datos en campos de texto

En ocasiones nuestro modelo de datos tendrá algunas estructuras complejas. Lo más frecuente es mapear estas estructuras a tablas. Más o menos cada objeto tendrá su tabla. Si por ejemplo tenemos una aplicación de guía de teléfonos con esta estructura de clases:

class Persona {
    public String nombre;
    public List<Telefono> telefonos;
}

class Telefono {
    public String tipo; // trabajo, personal, móvil, fax,...
    public String numero;
}

Lo típico será crear dos tablas: personas y teléfonos. Tendremos una clave foránea en la tabla de teléfonos que apuntará al registro correspondiente de la persona con ese número de teléfono. Para consultar los datos de varias personas y sus teléfonos tendremos que hacer una consulta con JOIN.

Bien, desde mi punto de vista no tiene mucho sentido este diseño si tenemos en cuenta que LA ÚNICA consulta que vamos a hacer sobre la tabla de teléfonos siempre está vinculada a una consulta con la tabla de personas. Lo que yo propongo es tener un campo de texto (o binario) en la tabla personas en el que se guarden TODOS los teléfonos de esa persona en algún formato concreto. El formato de esta columna puede ser XML, JSON,... o un formato propio. Así por ejemplo podríamos tener los teléfonos en JSON:

mysql> select * from personas;
+----+---------+-------------------------------+
| id | nombre  | telefonos                     |
+----+---------+-------------------------------+
|  1 | Alfonso | {"home": "xxx", "job": "yyy"} | 
|  2 | Maria   | {"home": "zzz", "job": "www"} | 
+----+---------+-------------------------------+
2 rows in set (0.00 sec)

La penalización extra de tener que parsear y formatear a JSON es inferior que el hecho de tener que hacer contínuamente JOINs en la base de datos.

En el blog de desarrollo de Flickr explican cómo han realizado la búsqueda de contactos. Cuentan que guardan todos los contactos de un usuario en un campo de texto. El formato que utilizan para guardar los datos consiste simplemente en concatenar cadenas utilizando caracteres no imprimibles y luego utilizan la función split() de JavaScript para parsear el texto. Según los datos que manejan consiguen parsear 818Kb de información de 10.000 contactos en 173 milisegundos.

Desnormaliza datos que no cambian

Normalizar una base de datos permite evitar datos duplicados. De modo que sabemos que un dato concreto sólo se encuentra en un registro de la base de datos, por lo que en caso de necesitar modificarlo sólo hay que cambiarlo en un sitio.

Sin embargo... si sabemos que un dato no va a cambiar nunca podemos replicarlo tantas veces necesitemos por la base de datos sin mayores preocupaciones, porque nunca tendremos la necesidad de cambiarlo.

Esto ocurre por ejemplo en aplicaciones que manejan históricos (logs) de cosas. Por ejemplo una aplicación de bolsa. Podemos tener en una tabla los datos de la cotización en bolsa de una empresa y sabemos que, por ejemplo, el valor de sus acciones al cerrar la cotización el 7 de abril de 2009 siempre será el mismo, por que es un hecho histórico y no cambia. Podríamos realizar varias tablas según las consultas que necesitamos:

• Una tabla que guarde todos los datos de cotización de todas las empresas a cada minuto.
• Una tabla que guarde sólo los datos de cotización de todas las empresas al cierre de cada sesión.
• Una tabla que guarde sólo los máximos anuales de cotización de todas las empresas.
• Una tabla que guarde sólo los datos de cotización de una empresa...

Pero ¿para qué necesitamos replicar datos? Pues muy simple, para que las consultas se ejecuten lo más rápido posible sobre todo si tenemos millones de consultas por minuto como puede ser el caso de las cotizaciones en bolsa. Además no sólo podemos replicar los datos en diversas tablas sino que podríamos replicar los datos en diferentes servidores y balancear la carga de las consultas.

Este tipo de datos son diferentes a los datos constantes que comentaba al principio del artículo. Los datos constantes los conocemos en tiempo de desarrollo. Sin embargo aquí me estoy refiriendo a los datos que iremos recolectando durante el tiempo de vida de la aplicación y que no cambiarán, pero que en tiempo de desarrollo no conocemos.

Desnormaliza datos que cambian, pero poco

Hay también datos que cambian, pero cambian de manera muy infrecuente. Ante este tipo de datos y en caso de que las consultas sean un cuello de botella, podemos desnormalizar esos datos. Por ejemplo si tenemos una base de datos de empleados, departamentos y sus jefes...

class Empleado {
    public String nombre;
    public Departamento departamento;
}

class Departamento {
    public String nombre;
    public Empleado jefe;
}

Tener que consultar el jefe de un empleado con esta estructura require tener que consultar el departamento de un empleado y luego obtener el jefe de ese departamento.

En caso de que esta consulta sea muy frecuente y haya un cuello de botella podríamos optar por desnormalizar estos datos y poner una columna "jefe" en la tabla "empleados". Esto supone que cuando un departamento cambia de jefe habría que actualizar todos los empleados que trabajan en ese departamento, pero en caso de problemas de rendimiento podría ser necesario utilizar esta técnica.

Actualiza de forma perezosa

En el ejemplo anterior es posible que haya miles de empleados trabajando en un departamento. En tal caso cuando un departamento cambia de jefe habrá que actualizar miles de registros. ¿Necesitamos que esos datos se actualicen a la vez en ese precismo momento? No, no es crítico. Podemos realizar una tarea programada que en segundo plano vaya actualizando los registros poco a poco. No es importante que un dato de esta naturaleza esté desincronizado unos segundos o minutos.

Sobre todo antes de utilizar estas dos últimas técnicas primero habrá que detectar un problema de cuello de botella, un problema de rendimiento. Y en segundo lugar habrá que estudiar si es aceptable o no que los datos estén durante un tiempo desincronizados.

Evita usar OR: desnormaliza datos dentro del mismo registro

Supongamos ahora que estamos realizando el sistema informático de una universidad. Queremos obtener aquellos alumnos con opción a una beca. Para optar a la beca hay una curiosa cláusula que require que el alumno haya superado más de 30 créditos troncales o más de 30 créditos optativos. La consulta podría ser la siguiente:

SELECT * FROM alumnos WHERE creditos_optativos_superados > 30 OR creditos_troncales_superados > 30

Bien, la consulta es correcta, pero más eficiente sería tener una columna opcion_beca de tipo booleano que se calcule al insertar o actualizar un registro.

SELECT * FROM alumnos WHERE opcion_beca=TRUE

En el código de la aplicación tendríamos que calcular la columna antes de insertar o actualizar un registro de la tabla alumnos.

De este modo la consulta es más sencilla, y a la base de datos le va a costar mucho menos ejecutarla. Además probablemente la tabla ocupará menos en el disco porque, aunque hay una columna adicional, sólo tendremos que crear un índice para una columna booleana, y no un índice para dos columnas de tipo entero.

Desnormaliza fechas, texto,... dentro del mismo registro. Evita usar funciones en una consulta

Supongamos que en una aplicación tenemos que obtener información de registros según una fecha. Supongamos que queremos obtener los registros que han ocurrido en enero (sin importar el año).

SELECT * FROM eventos WHERE MONTH(fecha_evento) = 1

Probablemente la base de datos tendrá que escanear todos los registros de la tabla para calcular el mes de cada fecha y compararlo con el mes que deseamos. ¿Cómo podemos mejorar esto? Mi propuesta es desglosar la fecha, precalcular el mes antes de insertar o actualizar el registro. Así quedaría la consulta:

SELECT * FROM eventos WHERE mes_evento=1

Podemos desglosar la fecha en más campos según necesitemos en nuestras consultas: año, mes, número de semana, trimestre, semestre,...

También puede ocurrir algo similar con columnas de texto. Por ejemplo si tenemos una base de datos con URLs y por algún motivo queremos obtener las URLs de un protocolo en concreto. Por ejemplo para obtener las URLs de tipo HTTPS podríamos hacer lo siguiente:

SELECT * FROM links WHERE SUBSTRING(url, 1, 5) = 'https';

Pero esto obligaría a la base de datos a escanear toda la tabla ejecutando el substring sobre cada uno de los registros. Podríamos crear una columna scheme que guardase sólo el tipo de protocolo (http, https, ftp, gopher,...).

También podemos desnormalizar cálculos. Por ejemplo en esta página los usuarios pueden votar un artículo. Guardamos en la base de datos el número total de votos (numerovotos) y la suma de todos los votos (sumavotos). Podríamos querer hacer un listado ordenado por la valoración media:

SELECT *, (suma_votos/numero_votos) AS media FROM articulos ORDER BY media DESC;

Pero de nuevo esto obligaría a la base de datos a escanear la tabla entera. Lo mejor sería tener una columna con la valoración media precalculada.

Utiliza contadores

Normalmente querremos paginar resultados en nuestras aplicaciones. Y sería conveniente indicarle al usuario el número de resultados totales de una consulta, pero sólo mostrar unos cuantos. ¿Cómo obtener sólo el número total de resultados sin obtener todos los registros?

SELECT COUNT(*) FROM tabla WHERE ...

La consulta es correcta, pero cuando el número de registros crezca veremos que la consulta cada vez es más y más lenta. La base de datos aunque no devuelva los resultados, sólo cuántos hay, tiene que ejecutar la consulta casi como si fuera a devolver los resultados. La solución es tener contadores e ir incrementándolos y decrementándolos manualmente. Por ejemplo en esta página cada vez que un usuario escribe un artículo se incrementa un contador de artículos de ese usuario; cada vez que un usuario añade a otro usuario a su lista de contactos se incrementa un contados con el número de contactos de ese usuario, etc.

Reflexión final

Algunas técnicas expuestas en este artículo son sencillas, otras con más complejas. Pero en definitiva para aplicar cada una de estas técnicas habrá que estudiar cada caso. Usarlas o no depende fundamentalmente del tipo de consultas que necesitemos en cada aplicación y la naturaleza de los datos que vamos a manejar.

Algunas técnicas pueden resultar polémicas como la desnormalización y la actualización perezosa, pero en mi opinión son técnicas que en aplicaciones altamente escalables tarde o temprano hay que utilizar.