Optimización de Bases de datos Relacionales

This entry was posted by on martes, 18 octubre, 2011 at

Aunque no soy DBA, las bases de datos relacionales es uno de los temas que más me ha interesado en mi vida profesional, y a través de los años he disfrutado trabajando con ellas, detectando debilidades y puntos clave para lograr el adecuado funcionamiento de estas. Por lo que me gustaría compartir un poco sobre el tema.

Primero definamos que es una base de datos Relacional, básicamente es una base de datos que sigue un modelo relacional, es decir un es un almacén de datos que está conformada estructuralmente por tablas que tienen interconexiones entre ellas (relacionales).

Como cualquier modelo de base de datos la idea es la manipulación de datos y hoy por hoy es el modelo más utilizado para sistemas transaccionales (aclaro que no todo sistema debe utilizar una BD relacional, se debe de analizar si otros modeles aplican mejor, tales como Bases de Datos XML, Bases de Datos Orientadas a objetos, Multidimensionales – muy populares para inteligencia de negocios, entre otros). Las operaciones básicas sobre datos en este modelo se realizan mediante operaciones de algebra relacional (muy orientadas a teoría de conjuntos), por lo que es común observar una consulta a una base de datos expresada de manera algebraica para determinar si existe alguna manera de hacerla más eficiente.

El lenguaje que utilizan los motores de bases de datos para el manejo de datos es SQL (Structure Query Language) , que es un lenguaje declarativo y existen un estándar definido por ANSI (la última versión es SQL:2008), sin embargo la mayoría de motores de bases de datos que implementan SQL están “un paso atrás” del estándar por lo que tienen sus propios sabores con pequeñas variaciones. Actualmente se trabaja en mejoras sobre una variación denominada Fuzzy Structucture Query Language (FSQL) que pretende aplicar lógica difusa sobre bases de datos relaciones difusas.

Entrando un poco en detalle sobre la optimización de motores de bases de datos relaciones, factores importantes a considerar:

  • Administración de Datos (Almacenamiento Físico)
  • Cardinalidad
  • Índices
  • Programación de Consultas

Administración de Datos

Consiste en la manera en cómo están almacenados físicamente los datos, dependiendo de factores como disponibilidad de datos, recursos disponibles, entre otros, vamos a encontrar modelos de almacenamiento en donde los datos se encuentran duplicados en más de una unidad de almacenamiento o bien la información estar “distribuidos” en varios.

Es muy importante indicar que el mayor cuello de botella en un modelo de bases de datos relaciones y al que lamentablemente no se tiene mucho control es el almacenamiento físico. Y la razón es muy simple, aun en nuestros días los medios de almacenamiento son lentos pues los mecanismos son muy lentos.

A pesar que ya existen dispositivos con una baja latencia y en general bastante rápidos (como los SSD) su uso todavía no es masivo y la mayoría de sistemas transaccionales que utilizan bases de datos relaciones utilizan discos duros “normales” (personalmente considero que el cambio se va a dar a un corto plazo).

Algunos datos importantes a considerar es que generalmente los gestores de bases de datos dividen físicamente la información en “paginas” que son bloques de 8kb (en motores de bases de datos robustos como Oracle es configurable) y las divisiones de paginas son denominados “Page Breaks” , la recomendación en este punto es disminuir la cantidad de páginas necesarios para mostrar datos y que se aproveche toda una unidad, es decir disminuir los page breacks (recordar que esto conlleva a una menor lectura de disco).

Cardinalidad

La cardinalidad se da entre campos de tablas y existen dos posibles escenarios:

•          Alta cardinalidad

–        Potenciales campos sobre los cuales se pueden usar índices.

–        Extremo : valores únicos

•          Baja cardinalidad

–        Los índices no son muy efectivos.

–        Extremo: valores iguales

Lo importante radica en poder determinar fácilmente cuales campos en una tabla son de alta cardinalidad y se podrían crear índices para mejoras los tiempos de respuesta ante consultas.

Indices

Existen dos tipos de índices, los Clustered Index y los non Clustered Index

Clustered Indexes

–        Almacena físicamente los datos en el orden del índice dentro de la página.

–        Generalmente es la llave primaria (pero no necesariamente)

–        Solo puede haber uno en cada tabla.

Algunas características que deberían tener los campos sobre las cuales se van a crear Clustered Indexes son:

–        Pequeño, preferiblemente integer (o al menos datos numéricos).

–        Único.

–        Usado en búsquedas por rangos.

–        Usado en cláusulas GROUP BY y ORDER BY.

–        Valores poco o no modificables

Non Clustered Index

–        Crea un índice de los datos a ordenar

–        Generalmente usado en llaves foráneas y  columnas usadas en joins

–        Se almacenan en tablas del sistema, por lo que para utilizarlo se hace lectura sobre estas tablas

–        Se deben de utilizar para campos con alta cardinalidad.

Es muy importante dejar claro que no siempre el crear un índice va a mejorar los tiempos de respuesta en bases de datos relaciones y algunas razones por las que al crear un índice no se obtegan buenos resultados, o incluso se empeoren son las siguientes:

  • Full Table Scan vs Indice: En algunas ocasiones en donde la cantidad de datos es muy poca, no vale la pena crear un índice pues si es necesario utilizar índices non-clustered se van a leer “tablas extras” por lo que los tiempos de respuesta no van a mejorar.
  • Sistemas de “mucha escritura”: Debemos recordar que cada vez que se ingrece o se modifique un dato que contiene un índice, será necesario “re-ordenar” los datos, por lo que en sistemas en donde hay mucha modificación de datos constantemente se van a tener que re-construir los datos por lo que el tiempo que se demora en guardar los cambios realizados va a aumentar.
  • Entre Otros.

Programación de Consultas

Nota: Debo de aclarar que mi mayor experiencia en el campo es como desarrollador, por lo que mucho del enfoque del artículo está dirigido a este apartado.

Aunque como ya se menciono anteriormente teóricamente los cuellos de botella deberían de ser en aspectos físicos del modelo de datos, pero en la práctica lo que más afecta a que se produzcan retardos cuando se trabaja con bases de datos relaciones es por una mala programación de las consultas. Evidentemente es responsabilidad del desarrollador garantizar un adecuado funcionamiento del sistema sin embargo pequeños detalles a veces pasan desapercibidos a la hora de escribir consultas SQL.

El hecho fundamental en la optimización de consultas o incluso en la propia escritura de la consulta es que se debe hacer un uso inteligente de los recursos. Cuando realizamos operaciones de “uniones de datos” tales como unions, joins, etc. , lo que el motor de base de datos realiza internamente es crear nuevos conjuntos de datos que son almacenados temporalmente  en tablas dinámicas conocidas como “tablas temporales”, por lo que una meta que se debe seguir cuando se está escribiendo SQL es acotar el tamaño y el numero de tablas temporales.

A continuación voy a mencionar una serie de recomendaciones generales:

•          Utilizar el EXPLAIN PLAN: Todos los IDEs actuales permiten al desarrollador analizar una consulta y determinar cual es el esfuerzo que realizaría el motor de base de datos para retornar los registros solicitados.

•           Evitar el uso de funciones agregadas (cambio de tipos, manejo de strings) en SELECT y WHERE, tales como Convert, cast, isnull, NVL, etc.

•           Crear constraints PK, FK y UNIQUE: En campos que lo permitan.

•          Evitar NOT: Evita el uso de índices pues implica barrido …WHERE NOT (bloodtype = ‘O’) Cambiarlo por: WHERE bloodtype < ‘O’ or bloodtype > ‘O’

•          LIKE: Se usará índice si no empieza por comodín … WHERE N LIKE ‘A%’ en lugar de LIKE ‘%A%’

•          ORDER BY: Usar pocas columnas (ORDER BY A,B,C), Preferiblemente por tipos numéricos,  Preferiblemente por columnas UNIQUE. Considerar que GROUP BY, DISTINCT, CREATE INDEX y UNION  hacen ordenamientos implícitos

•          GROUP BY: Implica ordenamiento, entonces usar pocas columnas con tipos pequeños. Si hay ORDER BY, usar el mismo orden porque primero ordena y luego agrupa. No se usarán índices si hay WHERE porque los índices no aplican para tablas temporales

Cuando trabajamos con conjunto de datos mediante operaciones de Join, el motor de base de datos utiliza diferentes algoritmos para crear las tablas temporales según considere (el motor de base de datos) sea más eficiente, sin embargo mediante hints podríamos ”obligar” al motor de base de datos a utilizar un algoritmo en especifico.

Los diferentes Algoritmos que utilizados son:

Nested-Loop Join

Pseudocódigo

For (each row in outerTable) {

                For (each row in innerTable) {

                                If (outerTable.column matches innerTable.column)

                                                add rows to tmp table

                }

}

Características:

•          InnerTable requiere menos lecturas (pequeña o índice)

•          OuterTable es grande o con Exp. Restr. (se recorre una vez)

•          Tabla grande no-indexada (OUTER) con una tabla pequeña indexada por la columna del JOIN (INNER)

•          PK generalmente es InnerTable, entonces poner Exp. Restr. en FK.

•          Opción default del DBMS.

•          Es el único que usa caché. El uso de este caché aplica únicamente para el innerTable, entonces cuando se hace un JOIN de 2 tablas utilizando NESTED-LOOP, el innerTable debe ser la tabla más pequeña, ya que se maximiza la cantidad de hits del caché

Merge JOIN

 

Pseudocódigo

Sort(table1), GetFirstRow(table1)

Sort(table2),GetFirstRow (table2)

while (more rows on tables) {

                If (join-column in table1 < join-column in table2)   {

                GetNextRow(table1)

                } elseif  (join-column in table1 > join-column in table2) {

                GetNextRow(table2)

                } else {

                         GetNextRow(table1)

                         GetNextRow(table2)

                                add rows to tmp table

                }

}

Características

•          Recorre cada tabla solo una vez, no un ciclo

•          SELECT * FROM t1,t2 WHERE t1.c1= t2.c2

–        Cardinalidad similar con grandes cantidades de datos

–        Indices en las columnas del JOIN

–        Sin expresiones restrictivas

•          Si hay muchos datos y los datos vienen pre-ordenados (de un índice) es el algoritmo de JOIN más rápido.

•          Requiere mucho RAM

Hash JOIN

Pseudocódigo

hash(T1[i]) -> tabla hash / fase de crear usando outer table

If (hash(t2[i])) / fase de probar usando inner table

 

Caracteristicas:

•          Es una especie de NESTED LOOP con InnerTable usando hash, no índice.

•          Para grandes cantidades de datos, sin índices

•          Outer pequeña e Inner grande para minimizar tabla Hash

•          Usa mucha RAM

•          Solamente para EQUIJOIN, no para OUTER JOIN

Subqueries

El uso de subqueries es otro factor que afecta muchísimo el performance en una base de datos relaciones, por lo que es de suma importancia conocer claramente que son, cuando aplican, la implicaciones al utilizarla y en qué casos podemos reemplazarlas.

•          Cuando Usar?

–        Para dividir un query complejo en pasos simples

–        Cuando un query depende del resultado de otro query

•          Joins vs Subqueries

–        Subquery se deben ejecutar dos consultas antes de mostrar resultados

–        Join union (en mayoria de los casos inner joins)

Subquery como tabla derivada:

Se utilizan en los siguientes casos:

•          Recorset dentro de una consulta que funciona como una tabla

•          Funciona como tabla en la clausula from

•          Se optimiza con el resto del query

Un ejemplo puede ser:

Use Northwind

SELECT T.orderid,

                T.customerid

FROM  ( SELECT

                orderid,

                customerid

                FROM orders) AS T

Subquery como una Expresión

Se utilizan en los siguientes casos:

•          Es evaluada como una expresión individual

•          Se ejecuta una vez por cada consulta

Un ejemplo puede ser:

SELECT title,

                price,( SELECT AVG(price) FROM titles) AS average,

                price-(SELECT AVG(price) FROM titles) AS difference FROM titles

WHERE type=’popular_comp‘

No se debe satanizar el uso de subconsultas, pero si es necesario usarlas con prudencia y si es posibles realizar otras operaciones (como joins) no debemos caer en la mala práctica de utilizar una subconsulta.

 

No cabe duda que hay muchos factores a tomar en cuenta cuando se está optimizando una base de datos relacional, pero lo importante es poder determinar cuál es el la parte del engranaje que está causando problemas. Y en lo medida de lo posible deberíamos de procurar que desde la concepción de un sistema se tome en cuenta todos los factores que afectan el rendimiento de una base de datos.


Leave a Reply



Current ye@r *