Rendimiento — Diseño de bases de datos

• EXPLAIN
• Índices
• Desnormalización

La cuestión del rendimiento de la base de datos y sus consultas se vuelve cada vez más relevante con el tiempo. A medida que el proyecto crece, las tablas se llenan de datos y las relaciones se vuelven más complejas, aumenta la probabilidad de enfrentar un funcionamiento lento y bloqueos indeseados.

Estas cuestiones rara vez afectan a los principiantes. Pero necesitas saber cómo lidiar con estos problemas, ya que en el futuro tendrás que enfrentarte a ellos. Por lo tanto, en esta lección, cubriremos las principales áreas de rendimiento de la base de datos.

EXPLAIN

SQL es un lenguaje declarativo, es decir, con él describimos QUÉ queremos obtener, no CÓMO. Pero eso no satisface a la máquina, ya que la base de datos necesita saber cómo llegar a esos datos.

En la base de datos se implementa un subsistema denominado planificador o scheduler. Este crea un plan de consulta o query plan, que describe cómo se extraerán los datos almacenados en la base de datos. Al elaborar un plan, el planificador toma en cuenta diversos factores, como las métricas de uso y la información sobre la cantidad de datos en las tablas.

Puedes visualizar el resultado del planificador utilizando el comando EXPLAIN:

EXPLAIN SELECT * FROM users
  JOIN topics ON users.id = topics.user_id
  WHERE users.created_at > '10.10.2018';

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Hash Join  (cost=10.66..23.59 rows=42 width=2377)
   Hash Cond: (topics.user_id = users.id)
   ->  Seq Scan on topics  (cost=0.00..11.30 rows=130 width=572)
   ->  Hash  (cost=10.50..10.50 rows=13 width=1805)
         ->  Seq Scan on users  (cost=0.00..10.50 rows=13 width=1805)
               Filter: (created_at > '2018-10-10 00:00:00'::timestamp without time zone)
(6 rows)

La ejecución de la consulta va de adentro hacia afuera, comenzando con el bloque más movido. Cada operación comienza con la flecha ->. Luego, los datos obtenidos en estos pasos se pasan al siguiente nivel hasta llegar a la cima. En el ejemplo anterior, la secuencia es: Seq Scan -> Hash -> Seq Scan.

Índices

El plan de consulta se puede utilizar de varias maneras, como reescribir o dividir la consulta para hacerla más eficiente. Algunas consultas ya están bastante optimizadas, por lo que para mejorar aún más su rendimiento utilizamos índices (index). Los índices son estructuras especiales dentro de la base de datos que se crean para acelerar la búsqueda. Un índice en una base de datos es similar al índice temático de un libro:

-- Ejemplo de creación de un índice en el campo birthday de la tabla users
CREATE INDEX ON users(birthday);

Esta consulta crea un índice en la tabla users en el campo birthday. Ahora las consultas con una condición o clasificación por birthday funcionarán más rápido debido al uso del índice durante la preparación de los datos. Ejemplo:

SELECT * from users WHERE birthday = '2000-01-01';

La creación de un índice no garantiza la eficiencia. Mucho depende de si se creó el índice correcto, cuántos datos hay en la tabla, qué consultas se realizan a esta tabla, cuántos índices había ya.

PostgreSQL ofrece seis tipos diferentes de índices, cada uno adecuado para situaciones específicas. Para utilizarlos eficazmente, es importante comprender varios aspectos:

• Funcionamiento de los índices: Es fundamental conocer las estructuras de datos y algoritmos que los sustentan. En particular, debemos entender los árboles balanceados, como el btree.

• Clasificación de los tipos de consultas: Diferentes tipos de consultas, como las consultas por rango o las que utilizan el operador LIKE, se optimizan y funcionan de manera distinta.

• Impacto de varias construcciones SQL en el plan de consulta: Algunas construcciones SQL, como ORDER BY, pueden ser costosas y a menudo requieren un recorrido completo de la tabla.

Desnormalización

Otro enfoque para la optimización es la desnormalización, un proceso que se opone a la normalización. Aunque no es un concepto reconocido en la teoría relacional y va en contra de sus principios, se utiliza en la práctica debido a que la redundancia puede simplificar las consultas al mantener los datos más accesibles y fáciles de extraer.

El costo de la desnormalización incluye un mayor volumen de datos y, a veces, la necesidad de sincronizar los datos manualmente. Por ejemplo, si almacenamos el nombre de usuario en varias tablas, cualquier cambio en el nombre requiere actualizaciones en todas las tablas donde se utiliza. Sin embargo, la desnormalización puede reducir significativamente el número de consultas con uniones (joins).

Hemos cubierto los principales enfoques de optimización de la base de datos. Este tema rara vez afecta a los principiantes, pero, por ejemplo, en una entrevista de trabajo a veces hacen preguntas sobre esto. Si das una buena respuesta, tendrás más posibilidades de conseguir el trabajo.

El rendimiento de la base de datos es un tema serio, por lo que se han escrito varios libros al respecto. En esta lección, hemos cubierto las áreas principales, y puedes aprender el resto practicando.

Materiales adicionales

1. Tipos de índices / PostgreSQL (en inglés)
2. B-árbol 🌳 Wikipedia