optimizacion consultas

15
7.- Procesamiento y optimización de consultas Los SGBD procesan, optimizan y ejecutan las consultas de alto nivel, utilizando técnicas y algoritmos. Cuando se tiene una consulta en SQL, ésta debe ser analizada léxica y sintácticamente, luego validada. EL analizador léxico identifica los componentes del lenguaje en el texto de la consulta, el analizador sintáctico revisa la sintaxis de la consulta para determinar si está formulada de acuerdo a las reglas sintácticas del lenguaje de consulta. La validación consiste en comprobar que todos los nombres de los atributos y de relaciones sean válidos. A continuación se crea una representación interna de la consulta, por lo regular en forma de estructura de datos de árbol o grafo. Esto se denomina árbol o grafo de consulta. En seguida, el SGBD debe crear una estrategia de ejecución para obtener el resultado de la consulta a partir de los archivos internos de la base de datos. Por lo general, una consulta tiene muchas posibles estrategias de ejecución, y el proceso de elegir la más adecuada se conoce como optimización de consultas. El módulo optimizador de consultas se encarga de producir un plan de ejecución y el generador de código genera el código necesario para ejecutarlo. El procesador de base de datos en tiempo de ejecución se encarga de ejecutar el código de la consulta, sea compilado o interpretado, para producir el resultado de la consulta. El optimizador en realidad no escoge siempre el plan más óptimo, ya que encontrar la estrategia óptima pude consumir mucho tiempo. Solo escoge una estrategia razonablemente eficiente. Algoritmos de optimización y procesamiento de consultas Algoritmos para ordenación externa La ordenación es uno de los algoritmos principales utilizados en el procesamiento de las consultas, Por ejemplo, siempre que en una consulta SQL se especifique una cláusula ORDER BY, el resultado de la consulta debe ser ordenado. La ordenación es también un componente clave en los algoritmos de ordenación y mezclado utilizados en las operaciones de JOIN y en algunas otras (como UNION y INTERSECTION), y en los algoritmos de eliminación de duplicados de la operación PROYECTO (cuando en una consulta SQL se especifica la opción DISTINCT en la cláusula SELECT). Es posible evitar la ordenación si existe el índice adecuado que permita un acceso ordenado a los registros.

Transcript of optimizacion consultas

Page 1: optimizacion consultas

7.- Procesamiento y optimización de consultasLos SGBD procesan, optimizan y ejecutan las consultas de alto nivel, utilizando técnicas y algoritmos. Cuando se tiene una consulta en SQL, ésta debe ser analizada léxica y sintácticamente, luego validada. EL analizador léxico identifica los componentes del lenguaje en el texto de la consulta, el analizador sintáctico revisa la sintaxis de la consulta para determinar si está formulada de acuerdo a las reglas sintácticas del lenguaje de consulta. La validación consiste en comprobar que todos los nombres de los atributos y de relaciones sean válidos.

A continuación se crea una representación interna de la consulta, por lo regular en forma de estructura de datos de árbol o grafo. Esto se denomina árbol o grafo de consulta. En seguida, el SGBD debe crear una estrategia de ejecución para obtener el resultado de la consulta a partir de los archivos internos de la base de datos. Por lo general, una consulta tiene muchas posibles estrategias de ejecución, y el proceso de elegir la más adecuada se conoce como optimización de consultas.

El módulo optimizador de consultas se encarga de producir un plan de ejecución y el generador de código genera el código necesario para ejecutarlo. El procesador de base de datos en tiempo de ejecución se encarga de ejecutar el código de la consulta, sea compilado o interpretado, para producir el resultado de la consulta.

El optimizador en realidad no escoge siempre el plan más óptimo, ya que encontrar la estrategia óptima pude consumir mucho tiempo. Solo escoge una estrategia razonablemente eficiente.

Algoritmos de optimización y procesamiento de consultas Algoritmos para ordenación externaLa ordenación es uno de los algoritmos principales utilizados en el procesamiento de las consultas, Por ejemplo, siempre que en una consulta SQL se especifique una cláusula ORDER BY, el resultado de la consulta debe ser ordenado. La ordenación es también un componente clave en los algoritmos de ordenación y mezclado utilizados en las operaciones de JOIN y en algunas otras (como UNION y INTERSECTION), y en los algoritmos de eliminación de duplicados de la operación PROYECTO (cuando en una consulta SQL se especifica la opción DISTINCT en la cláusula SELECT). Es posible evitar la ordenación si existe el índice adecuado que permita un acceso ordenado a los registros.La ordenación externa tiene relación con los algoritmos de ordenación que son adecuados para los ficheros de tamaño grande con registros almacenados en disco que no caben en su totalidad en la memoria principal, como sucede con la mayoría de los ficheros de bases de datos.

El algoritmo de ordenación externa más habitual es el que utiliza una estrategia de ordenación-mezcla, que comienza con la ordenación de pequeños subficheros (denominados porciones) del fichero principal y, a continuación, mezcla esos subficheros ordenados para crear subficheros ordenados más grandes que, a su vez, serán mezclados nuevamente. El algoritmo de ordenación-mezcla, al igual que los algoritmos de bases de datos, necesita de un espacio temporal en memoria principal donde se realiza la ordenación y la mezcla de las porciones.

Durante la fase de ordenación, las porciones del fichero que caben en el espacio temporal disponible se leen en memoria principal, se ordenan utilizando un algoritmo de ordenación interna y se vuelven a escribir en disco en forma de subficheros temporales ordenados (o porciones). El tamaño de una porción y el número inicial de porciones (nR) viene marcado por el número de bloques de fichero (b) y el espacio temporal disponible (nB). Por ejemplo, si nB = 5 bloques y el tamaño del fichero es b = 1.024 bloques, entonces nR = [b/ nB] o 205 porciones iniciales, cada una de ellas con 5 bloques

Page 2: optimizacion consultas

(excepto la última que tendrá 4 bloques). Por tanto, tras la fase de ordenación se almacenarán 205 porciones ordenadas en disco como ficheros temporales.

En la fase de mezclado, las porciones ordenadas se mezclan en una o más pasadas. El grado de mezclado (dM) es el número de porciones que pueden ser mezcladas en cada pasada. Se necesita un bloque de espacio temporal en cada pasada para almacenar un bloque de cada una de las porciones que están siendo mezcladas y se necesita otro bloque que contenga un bloque del resultado de la mezcla. Según esto, dM es el valor más pequeño entre (nB - 1) y nR y el número de pasadas es [(logdM(nR))]. En nuestro ejemplo, dM = 4 (mezcla en cuatro pasadas); por tanto, las 205 porciones ordenadas iniciales se mezclarían y se quedarían en 52 al final de la primera pasada. Éstas serían mezcladas nuevamente quedando 13, después 4, y después 1 porción, lo que significa que se necesitan cuatro pasadas. El valor mínimo de 2 para dM corresponde al caso peor de la ejecución del algoritmo, que es:

(2 * b) + (2 * (b * (log2 b))).

El primer término representa el número de accesos a bloques durante la fase de ordenación, ya que a cada bloque del fichero se accede dos veces: una vez para ser leído a memoria y otra vez para escribir nuevamente los registros en disco una vez ordenados. El segundo término representa el número de accesos a bloques durante la fase de mezclado, suponiendo el peor caso en el que dM vale 2. En general, el logaritmo se toma en base dM y la expresión que indica el número de bloques accedidos queda de este modo:

(2 * b) + (2 * (b * (logdM nR))).

Algoritmos para las operaciones SELECT y JOIN

Implementación de la operación SELECT:

Métodos de búsqueda en una selección simple .

Es posible utilizar varios algoritmos de búsqueda para realizar la selección de registros en un fichero. Estos algoritmos se conocen, a veces, como exploraciones de fichero, ya que exploran los registros del fichero en el que se realiza la búsqueda y encuentran los registros que satisfacen una condición de selección. Si el algoritmo de búsqueda implica la utilización de un índice, esta búsqueda mediante un Índice se denomina exploración indexada. Los siguientes métodos de búsqueda (S 1 a S6) son ejemplos de algunos de los algoritmos de búsqueda que se pueden utilizar para implementar una operación de selección.

S1-Búsqueda lineal (fuerza bruta). Extraer todos los registros del fichero y comprobar si sus valores de atributos satisfacen la condición de selección.

S2-Búsqueda binaria. Si la condición de selección implica una comparación de igualdad sobre un atributo clave con el que está ordenado el fichero se puede utilizar la búsqueda binaria, que es más eficaz que la búsqueda lineal. Un ejemplo es OP1 si Dni es el atributo de ordenación para el fichero EMPLEADO.

S3-Utilización de un índice primario (o clave hash o clave de dispersión). Si la condición de selección implica una comparación de igualdad sobre un atributo clave con un índice primario (o clave hash) (por ejemplo, Dni = '123456789' en OP1), se utilizará el índice primario (o clave hash) para encontrar el registro. Observe que con esta condición se extrae un único registro (como mucho).

S4-Utilización de un índice primario para encontrar varios registros. Si la condición de comparación es >, >=, <, o <= sobre un campo clave con un índice primario (por ejemplo, NúmeroDpto > 5 en OP2), se utilizará el índice para encontrar el registro que satisfaga la correspondiente condición de igualdad (NúmeroDpto = 5); a continuación, se extraerán los registros posteriores en el fichero ordenado. Si la condición es NúmeroDpto < 5, se extraerán todos los registros precedentes.

S5-Utilización de un índice agrupado para encontrar varios registros. Si la condición de selección implica una comparación de igualdad sobre un atributo que no es clave mediante un índice agrupado (por ejemplo, Dno = 5 en OP3), se utilizará el índice para extraer todos los registros que cumplan la condición.

S6-Utilización de un Índice secundario (árbol B+) sobre una comparación de igualdad. Este método de búsqueda se puede utilizar para extraer un único registro si el campo indexado es una clave (tiene valores

Page 3: optimizacion consultas

únicos) o para extraer varios registros si el campo indexado no es clave. Esto también se puede utilizar en las comparaciones del tipo >, >=, <, o <=.

El método S 1 se aplica a cualquier fichero, pero los demás métodos dependen de si se dispone de la ruta de acceso adecuada al atributo utilizado en la condición de selección. Los métodos S4 y S6 pueden utilizarse para extraer registros en un rango determinado (por ejemplo, 30000 <= Sueldo <= 35000). Las consultas que implican condiciones de este tipo se denominan consultas de rango. Métodos de búsqueda en selecciones complejas. Cuando la condición de una operación SELECT es una condición conjuntiva (es decir, si está formada por varias condiciones simples conectadas mediante la conjunción lógica ANO como, por ejemplo, el caso OP4 anterior) el DBMS puede utilizar los siguientes métodos adicionales para implementar la operación:

S7-Selección conjuntiva utilizando un Índice individual. Si un atributo implicado en cualquier condición simple y única de la condición conjuntiva tiene una ruta de acceso que permite el uso de uno de los métodos S2 a S6, se utilizará esa condición para extraer los registros y, posteriormente, comprobar si cada registro extraído satisface las condiciones simples restantes de la condición conjuntiva.

S8-Selección conjuntiva utilizando un Índice compuesto. Si dos o más atributos están implicados en condiciones de igualdad de la condición conjuntiva y existe un índice compuesto (o estructura hash) sobre los campos combinados (por ejemplo, si se ha creado un índice sobre la clave compuesta [DniEmpleado, NumProy] del fichero TRABAJA_EN en OP5), podemos utilizar el índice directamente.

S9-Selección conjuntiva mediante intersección de punteros a registros. Si se encuentran disponibles índices secundarios (u otras rutas de acceso) sobre más de uno de los campos implicados en condiciones simples de la condición conjuntiva, y si los índices incluyen punteros a registros (en lugar de punteros a bloques) entonces se puede utilizar cada uno de los índices para extraer el conjunto de punteros a registros que satisfacen la condición individual. La intersección de estos conjuntos de punteros a registros da como resultado los punteros a registros que satisfacen la condición conjuntiva y que serán utilizados posteriormente para extraer esos registros de forma directa. Si sólo tienen índices secundarios algunas de las condiciones, cada uno de los registros extraídos será revisado posteriormente para determinar si cumple las condiciones restantes.

Nota: Un puntero a registro identifica de manera única a un registro y proporciona la dirección del registro en el disco; por tanto, también se le denomina identificador de registro

Siempre que una condición única especifique la selección (como en el caso de OP1, OP2 u OP3), podemos limitarnos a comprobar si existe una ruta de acceso sobre el atributo implicado en esa condición. Si existe una ruta de acceso, se utilizará el método que corresponde a dicha ruta; en caso contrario, se puede utilizar el modelo de búsqueda de fuerza bruta lineal del método S l. Es necesaria la optimización de consultas en una operación SELECT en la mayoría de los casos de condiciones de selección conjuntivas en las que más de uno de los atributos implicados en la condición dispone de una ruta de acceso. El optimizador deberá elegir la ruta de acceso que extraiga el menor número de registros del modo más eficaz mediante la estimación de los diferentes costes y la elección del método con el menor coste estimado.

Cuando el optimizador tiene que elegir entre las distintas condiciones simples dentro de una condición de selección conjuntiva, lo que hace normalmente es tener en cuenta la selectividad de cada una de las condiciones. La selectividad se define como la relación entre el número de registros (tuplas) que satisfacen la condición y el número total de registros (tuplas) del fichero y, de acuerdo con esto, es un número entre cero y 1 (selectividad cero significa que ningún registro satisface la condición y 1 significa que todos los registros satisfacen la condición).

Una condición disyuntiva (en la cual las condiciones simples están conectadas mediante la operación lógica OR en lugar de estarlo mediante ANO) es mucho más difícil de procesar y optimizar que una condición de selección conjuntiva.

Tomemos como ejemplo OP4':

Con una condición de este tipo no se puede realizar demasiada optimización, ya que los registros que satisfacen la condición disyuntiva son la unión de los registros que satisfacen las condiciones individuales. Por tanto, si una cualquiera de las condiciones no dispone de ruta de acceso, nos veremos obligados a utilizar el modelo de búsqueda de fuerza bruta lineal. Sólo si existe una ruta de acceso para todas las condiciones podremos optimizar la selección obteniendo los registros (o los identificadores de registro) que satisfacen cada una de las condiciones para después utilizar la operación de unión para eliminar duplicados. Un DBMS suele disponer de muchos de los métodos discutidos anteriormente y, por lo general, muchos otros métodos adicionales. El optimizador de consultas elegirá el más adecuado para la ejecución de cada operación SELECT en una consulta.

Implementación de la operación JOIN

Page 4: optimizacion consultas

Métodos para la implementación de concatenaciones:

JI-Concatenación de bucle anidado (fuerza bruta). Para cada registro t de R (bucle externo), obtiene todos los registros s de S (bucle interno) y comprueba si los dos registros satisfacen la condición de concatenación t [A] = s[B].

J2-Concatenación de bucle simple (utilizando una estructura de acceso para obtener los registros que cumplen la condición). Si existe un Índice (o clave hash) para uno de los atributos de la concatenación (por ejemplo, B de S), obtiene todos los registros t de R, uno a la vez (bucle simple), y a continuación utiliza la estructura de acceso para obtener directamente todos los registros s de S que cumplen s[B] = t [A].

J3-Concatenación de ordenación-mezcla. Si los registros de R y S se encuentran físicamente clasificados (ordenados) por el valor de los atributos de concatenación A y B respectivamente, podemos implementar la concatenación del modo más eficiente posible. Ambos ficheros serán explorados concurrentemente siguiendo el orden de los atributos de concatenación y emparejando los registros que tienen los mismos valores para A y B. Si los ficheros no se encuentran ordenados, podrían ser ordenados previamente mediante una ordenación externa (consulte la Sección 15.2). Según este método, las parejas de los bloques de los ficheros se copian en los búferes de memoria en orden y los registros de cada fichero se exploran sólo una vez cada uno para comprobar su emparejamiento con el otro fichero (a menos que A y B no sean atributos clave, en cuyo caso el método necesita ser modificado ligeramente). En la Figura l5.3(a) se muestra un esquema del algoritmo de concatenación de clasificación-mezcla. Utilizamos R(i) para referirnos al i-ésimo registro de R. Se puede utilizar una variación de la concatenación de clasificación-mezcla cuando existen los Índices secundarios en ambos atributos de concatenación. Los Índices proporcionan la posibilidad de acceder (explorar) a los registros siguiendo el orden de los atributos de concatenación, pero ya que los registros se encuentran dispersos por todos los bloques del fichero este método puede resultar bastante ineficaz, ya que todos los accesos a registros pueden implicar el acceso a distintos bloques en disco.

J4-Concatenación de dispersión (hash). Los registros de los ficheros R y S se encuentran clasificados en el mismo fichero de dispersión, utilizando la misma función de dispersión sobre los atributos A de R y B de S como claves de dispersión. En primer lugar, una única pasada sobre el fichero con menor número de registros (por ejemplo, R ) reparte sus registros en los bloques del fichero de dispersión; esto se denomina fase de particionamiento, ya que los registros de R se palticionan sobre los bloques, de dispersión. En la segunda fase, denominada fase de prueba, se hace una única pasada por el otro fichero (S) tomando cada uno de los registros, probando en el bloque adecuado y emparejándolos con los registros correspondientes de R en ese bloque. En esta descripción simplificada de la concatenación de dispersión se supone que el fichero de menor tamaño cabe en su totalidad en los bloques de memoria tras la primera fase. Veremos más adelante algunas variaciones de la concatenación de dispersión en las que no se presupone esta afirmación.

Algoritmos para las operaciones de proyección y de conjunto

Una operación PROYECCIÓN π<lista de atributos>(R) es fácil de implementar si <lista de atributos> incluye una clave de la relación R, ya que en este caso el resultado de la operación tendrá el mismo número de tuplas que R, pero con sólo los valores de los atributos de <lista de atributos> en cada tupla. Si <lista de atributos> no incluye una clave de R, las tuplas duplicadas deben ser eliminadas. Esto se hace habitualmente ordenando el resultado de la operación y eliminando después las tuplas duplicadas, que aparecen consecutivamente tras la ordenación. Un esquema del algoritmo aparece en la Figura 15.3(b). También se puede usar la dispersión para eliminar duplicados: a medida que cada registro es asignado a un bloque en memoria del fichero de dispersión, se comprueba con los ya existentes en ese bloque; si se trata de un duplicado, no será insertado.. Resulta útil recordar aquí que en las consultas SQL la acción predeterminada es no eliminar los duplicados del resultado de la consulta; sólo si se incluye la palabra clave DISTINCT se eliminarán los duplicados de dicho resultado.

Las operaciones de conjunto (UNION, INTERSECTION, SET DIFFERENCE Y CARTESIAN PRODUCT) son a veces difíciles de implementar. En particular, la operación CARTESIAN PRODUCT R X S es muy costosa porque su resultado incluye un registro para cada combinación de registros de R y S. Además, los atributos del resultado incluyen todos los atributos de R y S. Si R tiene n registros y j atributos y S tiene m registros y k atributos, la relación resultante tendrá n * m registros y j + k atributos. Por tanto, es importante evitar la operación CARTESIAN PRODUCT y sustituir otras operaciones equivalentes durante la optimización de consultas (consulte la Sección 15.7).

Page 5: optimizacion consultas

Las otras tres operaciones de conjunto (UNION, INTERSECTION y SET DIFFERENCE) se aplican sólo a relaciones compatibles en unión, las cuales tienen el mismo número de atributos y los mismos dominios de atributo. La forma habitual de implementar estas operaciones es utilizar variaciones de la técnica de ordenación-mezcla: las dos relaciones se ordenan por los mismos atributos y, después de la ordenación, una única exploración por cada relación es suficiente para obtener el resultado. Por ejemplo, podemos implementar la operación UNION, R U S, explorando y mezclando de forma concurrente ambos ficheros y, cuando se encuentre la misma tupla en ambas relaciones, sólo se guardará una de ellas en la mezcla resultante. En el caso de la operación INTERSECTION, R n S, guardaremos en la mezcla resultante sólo esas tuplas que aparecen en ambas relaciones. En las Figuras 15 .3( c) a ( e) aparece un esquema de la implementación de estas operaciones mediante ordenación y mezcla. Algunos de los detalles no aparecen en estos algoritmos.

También se puede utilizar el método de dispersión para implementar UN ION, INTERSECTION y SET DIFFERENCE. Una de las tablas se particiona y la otra se usa para probar en la partición adecuada. Por ejemplo, para implementar R U S, en primer lugar se realiza la dispersión (se particiona) en los registros de R; a continuación, se realiza la dispersión (se prueba) en los registros de S, pero no se insertan los registros duplicados en los bloques de memoria. Para implementar R n S, en primer lugar se particionan los registros de R en el fichero de dispersión. Posteriormente, mientras se realiza la dispersión en cada uno de los registros de S, se realiza la prueba para comprobar si se encuentra un registro idéntico de R en el bloque de memoria y, si es así, se agrega el registro al fichero resultante. Para implementar R - S, en primer lugar se particionan los registros de R en los bloques en memoria del fichero de dispersión. Mientras se realiza la dispersión (se prueba) de cada registro de S, si se encuentra un registro idéntico en el bloque de memoria, se elimina ese registro del bloque.

EMPLEO DE LA HEURÍSTICA EN LA OPTIMIZACIÓN DE CONSULTAS

Es una técnica que se encuentra normalmente bajo la forma de un árbol de consultas o de una estructura de datos de consultas en grafo y que sirve para mejorar su ejecución de una consulta. El analizador de una consulta de alto nivel genera en primer lugar una representación interna inicial, que es optimizada posteriormente según unas reglas heurísticas. Tras esto, se genera un plan de ejecución de consultas para ejecutar grupos de operaciones basadas en las rutas de acceso disponibles para los ficheros implicados en la consulta.

La principal regla heurística es aplicar las operaciones de SELECT y PROJECT (restricción y proyección) antes de aplicar JOIN (reunión) u otras operaciones binarias. Esto es porque el tamaño del archivo resultante de una operación binaria es una función de los tamaños de los archivos de entrada (en algunos casos función multiplicativa). Las operaciones SELECT y PROJECT reducen el tamaño de un fichero y, por tanto, deberían ser aplicadas antes de JOIN u otra operación binaria.

Notación para los árboles de consultas y los grafos de consultasUn árbol de consultas es una estructura de datos en árbol que equivale a una expresión de álgebra relacional. Representa las relaciones de entrada a una consulta como los nodos hoja del árbol y las operaciones del álgebra relacional como nodos internos. Una ejecución del árbol de consultas consiste en ejecutar una operación en un nodo interno siempre que estén disponibles sus operandos y después reemplazar ese nodo interno por la relación que resulta de la ejecución de la operación. La ejecución finaliza cuando se ejecuta el nodo raíz y se genera la relación resultante de la consulta. El árbol de consultas representa un orden determinado en las operaciones para la ejecución de una consulta.

Una forma más neutral de representar una consulta es la notación en grafo de consultas. Las relaciones de la consulta se representan mediante nodos de relación, (círculos sencillos). Las condiciones de selección (valores constantes por ejemplo edad=15) de la consulta, se representan mediante nodos de constantes, (círculos dobles u óvalos). Las condiciones de selección y de concatenación se representan mediante los bordes del grafo. Por último, los atributos a obtener de cada relación se muestran dentro de corchetes sobre cada relación. La representación del grafo de consultas no indica un orden para ejecutar en primer lugar ciertas operaciones. Sólo existe un único grafo para cada consulta. Aunque algunas técnicas de optimización se basaban en grafos de consultas, hoy en día se prefieren, por lo general, los árboles de consultas, ya que en la práctica el optimizador de consultas necesita conocer el orden de las operaciones para la ejecución de la consulta, lo cual no es posible en los grafos de consultas.

Optimización heurística de los árboles de consultas

Por lo general, muchas expresiones en álgebra relacional (y, por tanto, muchos árboles de consultas distintos) son equivalentes; esto es, equivalen a la misma consulta.

El analizador de consultas generará, normalmente, un árbol de consultas inicial estándar que equivalga a una consulta SQL, sin hacer ninguna optimización, este árbol de consulta inicial se representa con un árbol de consultas canónico

Page 6: optimizacion consultas

pero que es muy ineficaz si se ejecuta directamente, es por esto que el optimizador heurístico de consultas es quien tiene la tarea de transformar este árbol de consultas inicial en un árbol de consultas final de ejecución eficiente.

El optimizador debe incluir reglas de equivalencia entre las expresiones de álgebra relacional que puedan ser aplicadas al árbol inicial. Las reglas de optimización heurística de consultas utilizarán estas expresiones de equivalencia para transformar el árbol inicial en el árbol de consultas final optimizado.

Reglas generales de transformación para las operaciones de álgebra relacional.

En resumen las reglas heurísticas para la optimización algebraica principalmente es aplicar en primer lugar las operaciones que reducen el tamaño de los resultados intermedios. Eso significa ejecutar tan pronto como sea posible las operaciones SELECT para reducir el número de tuplas y las operaciones PROJECT para reducir el número de atributos. Esto se lleva a cabo desplazando las operaciones SELECT y PROJECT hacia abajo en el árbol lo más lejos posible. Además, las operaciones SELECT y JOIN más restrictivas (es decir, con las relaciones resultantes con el menor número de tuplas o con el tamaño absoluto menor) deberían ser ejecutadas antes que otras operaciones similares. Esto se hace reordenando los nodos hoja del árbol entre sí evitando los productos cartesianos y ajustando el resto del árbol adecuadamente.

Page 7: optimizacion consultas

Descripción de un algoritmo de optimización algebraica heurística.

Los pasos de un algoritmo que utiliza algunas de las reglas descritas anteriormente para transformar un árbol de consultas inicial en un árbol optimizado que se ejecute de forma más eficiente. Los pasos del algoritmo son los que se muestran a continuación:

1. Utilizando la regla 1, descomponer cualquier operación SELECT con condiciones conjuntivas en una cascada de operaciones SELECT. Esto permite un mayor grado de libertad para mover las operaciones SELECT hacia abajo por las distintas ramas del árbol.

2. Utilizando las reglas 2, 4, 6 y 10 relativas a la conmutatividad de SELECT con otras operaciones, desplazar cada operación SELECT hacia abajo por el árbol tan lejos como lo permitan los atributos incluidos en la condición de selección.

3. Utilizando las reglas 5 y 9 relativas a la conmutatividad y asociación de las operaciones binarias, reordenar las relaciones de los nodos hoja utilizando los siguientes criterios. En primer lugar, posicionar las relaciones de los nodos hoja con las operaciones SELECT más restrictivas de forma que sean ejecutadas en primer lugar en la representación del árbol de consultas. La definición de SELECT más restrictiva se refiere a las que generan una relación bien con el menor número de tuplas o bien con el menor tamaño absoluto. Otra posibilidad es definir la SELECT más restrictiva como aquella que tiene una selectividad más pequeña; esto resulta más práctico ya que a veces los cálculos de selectividades se encuentran dentro del catálogo del DBMS. En segundo lugar, asegurarse de que la ordenación de los nodos hoja no produce ninguna operación CARTESIAN PRODUCT; por ejemplo, si las dos relaciones con la SELECT más restrictiva no tienen una condición de concatenación directa entre ellas, puede ser conveniente cambiar el orden de los nodos hoja para evitar productos cartesianos.

4. Utilizando la regla 12, combinar una operación CARTESIAN PRODUCT con la siguiente operación SELECT del árbol para formar una operación JOIN, en el caso de que la condición represente una condición de concatenación.

5. Utilizando las reglas 3, 4, 7 Y 11 relativas a la secuenciación de PROJECT y a la conmutación de PROJECT con otras operaciones, descomponer y desplazar las listas de atributos de proyección hacia abajo por el árbol lo más lejos posible mediante la creación de nuevas operaciones PROJECT según sea necesario. Tras cada operación PROJECT sólo se deberían guardar aquellos atributos necesarios en el resultado de la consulta y en las operaciones siguientes.

6. Identificar los subárboles que representan grupos de operaciones que se pueden ejecutar mediante un único algoritmo.

Utilización de la selectividad y la estimación de costes en la optimización de consultasUn optimizador de consultas no debería basarse exclusivamente en reglas heurísticas; también debería calcular y comparar los costes de ejecución de una consulta utilizando diferentes estrategias de ejecución y debería elegir la estrategia con el menor coste estimado. Para que funcione este modelo se necesitan estimaciones de coste precisas para que la comparación entre las diferentes estrategias se realice con ecuanimidad y de forma real. Además, debemos limitar el número de estrategias a tener en cuenta ya que, en caso contrario, se perdería demasiado tiempo haciendo estimaciones de coste para un número tan elevado de estrategias de ejecución posibles.

De acuerdo con esto, este modelo es más adecuado en consultas compiladas en las cuales la optimización se realiza en tiempo de compilación, y el código para la estrategia de ejecución resultante se almacena y se ejecuta directamente en tiempo de ejecución. En el caso de las consultas interpretadas en las cuales todo el proceso, mostrado en la Figura 15.1, se realiza en tiempo de ejecución, una optimización a gran escala podría ralentizar el tiempo de respuesta. La optimización más elaborada está indicada en el caso de las consultas compiladas, mientras que una optimización parcial

Page 8: optimizacion consultas

que requiera menos tiempo funciona mejor en el caso de las consultas interpretadas. A este modelo se le denomina optimización de consultas basada en costes. Utiliza técnicas de optimización tradicionales que buscan en el espacio de soluciones de un problema la solución que minimice una función objetivo (función de coste). Las funciones de coste que se utilizan en la optimización de consultas son funciones estimadas, no funciones exactas; por tanto, la optimización podría elegir una estrategia de ejecución de consultas que no fuese la óptima.

Componentes de coste de ejecución de una consultaEl coste de ejecución de una consulta incluye los siguientes componentes:

1. Coste de acceso al almacenamiento secundario. Es el coste de la búsqueda, lectura y escritura de bloques de datos que residen en almacenamiento secundario, principalmente en disco. El coste de la búsqueda de registros en un fichero depende del tipo de las estructuras de acceso a dicho fichero, como, por ejemplo, la ordenación, la dispersión y los índices primarios y secundarios. Aparte de esto, factores como la ubicación contigua de los bloques del fichero en el mismo cilindro del disco o diseminados por el disco afectan al coste de acceso.

2. Coste de almacenamiento. Es el coste de almacenamiento de los ficheros intermedios generados por una estrategia de ejecución de la consulta.

3. Coste computacional. Es el coste de la ejecución de operaciones en memoria sobre los búferes de datos durante la ejecución de la consulta. Este tipo de operaciones incluye la búsqueda y la ordenación de los registros, la mezcla de registros durante una concatenación y la ejecución de cálculos sobre valores de los campos.

4. Coste de uso de memoria. Es el coste relativo al número de búferes de memoria que se necesitan durante la ejecución de la consulta.

5. Coste de comunicaciones. Es el coste del envío de la consulta y de sus resultados desde el sitio donde se ubica la base de datos hasta el sitio o el terminal donde se originó la consulta.

En el caso de bases de datos de gran tamaño, el objetivo principal es minimizar el coste de acceso al almacenamiento secundario. Las funciones de coste sencillas ignoran otro tipo de factores y comparan las diferentes estrategias de ejecución en función del número de transferencias de bloque entre el disco y la memoria principal.

En el caso de bases de datos de menor tamaño, en las que se puede almacenar en memoria la mayoría de los datos de los ficheros implicados en la consulta, el objetivo principal es minimizar el coste computacional.

En el caso de bases de datos distribuidas en las que se encuentran implicadas varias ubicaciones físicas también se debe minimizar el coste de las comunicaciones. Resulta difícil incluir todos los componentes de coste en una función (ponderada) de coste debido a la dificultad de asignar los pesos adecuados a los componentes de coste. Ésta es la razón por la que algunas funciones de coste tienen en cuenta un único factor: el acceso a disco. En la siguiente sección veremos la información que se necesita para formular las funciones de coste.

Información del catálogo utilizada en las funciones de coste

Para calcular los costes de las distintas estrategias de ejecución debemos tener en cuenta la información que se necesita en las funciones de coste. Esta información se puede almacenar en el catálogo del DBMS, donde es accedida por el optimizador de consultas. En primer lugar, debemos conocer el tamaño de cada fichero. En el caso de un fichero cuyos registros son todos del mismo tipo, necesitamos el número de registros (tuplas) (r), el tamaño (medio) de registro (R), y el número de bloques (b) (o sus valores aproximados). Es posible que también necesitemos el factor de bloqueo (bfr). También debemos tener en cuenta el método de acceso primario y los atribulas de acceso primario de cada fichero. Los registros del fichero pueden estar desordenados, ordenados por un atributo con o sin un índice primario o agrupado, o mezclados según un atributo clave. Se guarda información de todos los índices secundarios y de los atributos de indexación. Tenemos que conocer el número de niveles (x) de cada índice multinivel (primario, secundario o agrupado) para las funciones de coste que calculan el número de accesos a bloque que se producen durante la ejecución de la consulta. En algunas funciones de coste se necesita el número de bloques de índices de primer nivel (bi1). Otro parámetro importante es el número de valores distintos (d) de un atributo y su selectividad (sl), que es la porción de registros que satisfacen una condición de igualdad sobre el atributo. Esto permite el cálculo de la cardinalidad de selección (s = sl * r) de un atributo, que es el número medio de registros que cumplirán una condición de igualdad sobre ese atributo. En el caso de un atributo clave, d = r; sl = 1/r y s = 1. En el caso de un atributo no clave, si suponemos que los d valores distintos se encuentran distribuidos uniformemente por los registros, el cálculo será sl = (1/d) y, por tanto, s = (r/d). Cierta información, como el número de niveles de índice, es fácil de mantener ya que no varía con frecuencia. Sin embargo, otro tipo de información puede cambiar con frecuencia; por ejemplo, el número de registros r de un fichero cambia cada vez que se inserta o se elimina un registro. El optimizador de consultas necesitará estos valores con cierta exactitud, para utilizarlos en el cálculo del coste de las distintas estrategias. En las dos secciones siguientes revisaremos cómo se utilizan algunos de estos parámetros en las funciones de coste para un optimizador de consultas basado en costes.

Page 9: optimizacion consultas

Nota: La cardinalidad de selección se definió como el número medio de registros que satisfacen una condición de igualdad sobre un atributo, que es el número medio de registros que tienen el mismo valor para ese atributo y que, por tanto, serán unidos en un único registro en el otro fichero.

Ejemplos de funciones de coste en SELECT

En términos de número de transferencias de bloque entre la memoria y el disco. Estas funciones de coste son operaciones de cálculo que ignoran el tiempo de computación, el coste de almacenamiento y otros factores. El coste del método Si se podría referenciar como CSi accesos a bloque.

S1-Modelo de búsqueda lineal (fuerza bruta). Buscaremos por todos los registros del fichero para extraer todos los registros que satisfacen la condición de selección; según esto, CS1a = b. En el caso de una condición de igualdad sobre una clave, por término medio sólo se buscará en la mitad de los bloques del fichero antes de encontrar el registro, por tanto CS1b = (bI2) si se localiza el registro y CS1b = b si ningún registro satisface la condición.

S2-Búsqueda binaria. Esta búsqueda accede aproximadamente a CS2 = log2b + [(s/bfr)] - 1 bloques del fichero. Esto se reduce a log2b si la condición de igualdad se realiza sobre un atributo clave único, ya que s = 1 en este caso.

S3-Uso de un índice primario (S3a) o de una clave de dispersión (S3b) para obtener un único registro. Con un índice primario, se obtiene un bloque más que el número de niveles de índice; por tanto, CS3a = X + 1. En el caso de una clave de dispersión, la función de coste es aproximadamente CS3b = 1 si se trata de dispersión estática o lineal, y 2 en el caso de dispersión extensible (consulte el Capítulo 13).

S4-Uso de un índice de ordenación para obtener varios registros. Si la condición de comparación es >, >=, <, o <= sobre un campo clave con un índice de ordenación, apenas cumplirán la condición la mitad de los registros. Esto nos da una función de coste CS4 = x + (bI2). Es un cálculo poco preciso y, aunque puede que por lo general sea correcto, podría llegar a ser bastante inexacto en determinados casos.

S5-Uso de un índice agrupado para obtener varios registros. Dada una condición de igualdad, s registros cumplirán la condición, siendo s la cardinalidad de selección del atributo de indexación. Esto significa que [(s/bfr)] bloques de fichero serán accedidos, resultando que CS5 = x + [(s/bfr)].

S6-Uso de un índice secundario (árbol B+). En una condición de igualdad, s registros cumplirán la condición, siendo s la cardinalidad de selección del atributo de indexación. Sin embargo, debido a que no se trata de un índice agrupado, cada uno de los registros puede estar localizado en un bloque distinto, por tanto el cálculo de coste (en el caso peor) es CS6a = X + s. Esto se reduce a x + 1 para un atributo de indexación clave. Si la condición de comparación es >, >=, <, o <= y se supone que la mitad de los registros cumplen la condición, entonces (a grandes rasgos) se accede a la mitad de los bloques de índice de primer nivel y a la otra mitad se accede a través del índice. El cálculo de coste para este caso es, aproximadamente, CS6b = x + (bi1/2) + r/2). El factor r/2 se puede refinar si se dispone de mejores cálculos de selectividad.

S7-Selección conjuntiva. Podemos utilizar S1 o uno de los métodos S2 a S6 vistos anteriormente. En el segundo caso, usamos una condición para obtener los registros y, posteriormente, comprobar en el búfer de memoria si cada registro obtenido cumple las condiciones de la conjunción restantes.

S8-Selección conjuntiva utilizando un Índice compuesto. Igual que en S3a, S5, o S6a, dependiendo del tipo de índice.

Ejemplos de funciones de coste en JOIN

Para desarrollar funciones de coste razonablemente precisas en las operaciones JOIN necesitamos tener una estimación del tamaño (número de tuplas) del fichero resultante tras la operación JOIN. Normalmente, esto se calcula como la relación entre el tamaño (número de tuplas) del fichero resultante de la concatenación y el tamaño del fichero PRODUCTO CARTESIANO, si ambos se aplican a los mismos ficheros de entrada, y se denomina selectividad de concatenación (js). Si llamamos |R| al número de tuplas de una relación R, tendremos:

Si no existe condición e para la concatenación, entonces js = 1 Y la concatenación es igual al PRODUCTO CARTESIANO. Si ninguna de las tuplas de las relaciones cumple la condición de concatenación, entonces js = 0. En general, 0 ≤ js ≤1. En el caso de una concatenación donde la condición e es una comparación de igualdad R.A = S.B, se presentan los dos casos especiales que se muestran a continuación:

Page 10: optimizacion consultas

Disponer de una selectividad de concatenación estimada para las condiciones de concatenación que se presentan con más asiduidad permite al optimizador de consultas calcular el tamaño del fichero resultante tras la operación de

concatenación, dados los tamaños de los dos ficheros de entrada, utilizando la fórmula Las operaciones de concatenación tienen la forma:

Donde A y E son atributos compatibles en dominio de R y S, respectivamente. Supongamos que R tiene bR bloques y que S tiene bs bloques:

Jl-Concatenación de bucle anidado. Supongamos que utilizamos R en el bucle externo; entonces tendríamos la siguiente función de coste para calcular el número de accesos a bloque para este método suponiendo que tenemos tres búferes en memoria. Supongamos también que el factor de bloqueo del fichero resultante es bfrRS y que la selectividad de la concatenación es conocida:

La última parte de la fórmula es el coste de la escritura del fichero resultante en disco. Esta fórmula de coste puede ser modificada para tener en cuenta diferentes cantidades de búferes en memoria.

J2-Concatenación de bucle simple (utilizando una estructura de acceso para obtener los registros que cumplen la condición). Si existe un índice sobre el atributo E de S con niveles de índice xB podemos obtener cada uno de los registros s de R y, posteriormente, utilizar el índice para obtener todos los registros emparejables t de S que satisfacen t[B] = s[A]. El coste depende del tipo de índice. Para un índice secundario donde SB es la cardinalidad de selección para el atributo de concatenación B de S, obtendremos:

Para un índice agrupado donde SB es la cardinalidad de selección de B, obtendremos:

Para un índice primario, obtendremos:

Si existe una clave de dispersión en uno de los dos atributos de concatenación (por ejemplo, B de S), obtendremos:

Donde h≥1 es la cantidad media de accesos a bloque para obtener un registro, dado el valor de su clave de dispersión.

J3-Concatenación de ordenación-mezcla. Si los ficheros ya se encuentran ordenados por los atributos de concatenación, la función de coste para este método es:

Si tuviéramos que ordenar los ficheros, sería necesario añadir el coste de la ordenación.

Optimización semántica de consultasExiste una propuesta de un modelo diferente de optimización de consultas denominada optimización semántica de consultas. Esta técnica, que puede utilizarse en combinación con las técnicas descritas anteriormente, se basa en restricciones especificadas sobre el esquema de la base de datos (como atributos únicos y otro tipo de restricciones más complejas) para modificar una consulta dentro de otra consulta que sea más eficiente en su ejecución. No discutiremos este modelo en detalle, sino que lo explicaremos mediante un ejemplo sencillo. Tomemos esta consulta SQL:

Esta consulta obtiene los nombres de los empleados que tienen un sueldo mayor que el de sus superiores. Supongamos que pusiésemos una restricción en el esquema de la base de datos que indicase que ningún empleado puede tener un

Page 11: optimizacion consultas

salario mayor que el de su jefe directo. Si el optimizador semántico de consultas comprueba la existencia de esta restricción, no tendrá la necesidad de ejecutar la consulta, ya que sabrá que el resultado de la consulta estará vacío. Esto puede ahorrar mucho tiempo si la comprobación de restricciones se puede hacer de una manera eficiente. Sin embargo, la búsqueda en muchas restricciones para encontrar aquellas que son aplicables a una consulta dada y que pueden optimizarla semánticamente puede llegar a consumir mucho tiempo. Mediante la inclusión de reglas activas en los sistemas de bases de datos (consulte el Capítulo 24), la optimización semántica de consultas podría llegar a estar plenamente incorporada a los DBMSs en un futuro.