Optimización de consultas

Se ha sugerido que este artículo o sección sea fusionado con Optimizador de consultas (discusión). Una vez que hayas realizado la fusión de artículos, pide la fusión de historiales aquí.

Cuando hablamos de optimización de consultas nos referimos a mejorar los tiempos de respuesta en un sistema de gestión de bases de datos relacional, pues la optimización es el proceso de modificar un sistema para mejorar su eficiencia o también el uso de los recursos disponibles.

En bases de datos relacionales el lenguaje de consultas SQL es el más utilizado por el común de los programadores y desarrolladores para obtener información desde la base de datos. La complejidad que pueden alcanzar algunas consultas puede ser tal, que el diseño de una consulta puede tomar un tiempo considerable, obteniendo no siempre una respuesta óptima.

Tuplas

Una tupla de una relación o de una tabla corresponde a una fila de aquella tabla. Las tuplas están comúnmente desordenadas puesto que matemáticamente una relación se define como un conjunto y no como una lista. No existen tuplas duplicadas en una relación o tabla dado el hecho de que una relación es un conjunto y los conjuntos por definición no permiten elementos duplicados.

Un corolario importante en este punto es que la llave primaria siempre existe dada la condición de unicidad de las tuplas, por lo tanto, como mínimo la combinación de todos los atributos de una tabla puede servir para la conformación de la llave primaria, sin embargo usualmente no es necesario incluir todos los atributos, comúnmente algunas combinaciones mínimas son suficientes.

Relación

Formalmente, una relación R es un conjunto de n-tuplas.

Las propiedades fundamentales de una relación son:

• No hay tuplas repetidas.
• Las tuplas no están ordenadas.
• Los atributos no están ordenados.

Cuando vamos a realizar este proceso debemos tener en cuenta aspectos tales como:

• Evaluación de que la consulta es algebraicamente más correcta
• Evaluación de la carga sobre los recursos del sistema

Ejemplo

A continuación podemos observar un ejemplo de la problemática de optimización. En este simple problema tenemos tablas de “Suppliers” (S) y “Orders” (SP)con 100 administradores y 10.000 pedidos. Consulta: “Obtener los nombres de los suministradores que nos sirven la pieza P2”. Consideraremos que sólo 50 tuplas de SP corresponden a la pieza P2.

SELECT DISTINCT S.NOMBRE FROM S, SP WHERE S.S#=SP.S# AND SP.P#=”P2”;

En el anterior ejercicio tenemos un producto cartesiano S x SP 100 x 10.000 = 1.000.000 de tuplas leídas. Probablemente 1.000.000 de tuplas escritas en memoria virtual. Cuando utilizamos la sentencia WHERE pasamos de 1.000.000 de tuplas leídas a 50 tuplas, luego realizamos una proyección sobre S.NOMBRE, dando como resultado un máximo de 50 tuplas.

Procedimientos

Seleccionar en SP las tuplas de la pieza P2. Se realizará lectura de 10.000 tuplas dando como resultado: 50 tuplas. Hacer JOIN de la tabla anterior. Con la tabla S se realizara lectura de 100 tuplas, dando como resultado: 50 tuplas con proyección sobre S.NOMBRE, dando como resultado un máximo de 50 tuplas.

Conclusión

El segundo procedimiento es unas 300 veces mejor, ya que el primero realiza 3.000000.000 operaciones de E/S, frente a 10..100 del segundo.

¿Dónde incide la optimización?

• El coste de comunicación de acceso a almacenamiento secundario.
• El coste de almacenamiento.
• El coste de computación.
• El optimizador interviene también en las actualizaciones y borrados.

El proceso de optimización

1.1. Representación interna de consultas. 1.2. Conversión a forma canónica. 1.3. Elección de procedimientos de bajo nivel. 1.4. Generación y elección de planes de consulta.

Representación interna de consultas

• Características:

• • Ser relacionalmente completo.
  • Suministrar un punto de partida sólido para las siguientes fases.
  • Proporcionar un grado de libertad suficiente para realizar las posibles optimizaciones.

• Sistemas de representación:

• • Álgebra relacional
  • Cálculo relacional
  • El árbol sintáctico abstracto o árbol de consulta.

Conversión a forma canónica

En la conversión canónica encontramos que hay optimizaciones previas que tienen un resultado positivo seguro. En este paso debemos encontrar la expresión equivalente de una consulta dada en la que se mejore de alguna manera el rendimiento. Esta expresión equivalente será la FORMA CANONICA de dicha consulta.

Elección de procedimientos de bajo nivel

En la elección de procedimientos de bajo nivel se debe evaluar la consulta previamente transformada, también encontraremos existencia de índices u otras rutas de acceso y la distribución de los valores de los datos almacenados. Luego se realizará un agrupamiento físico de los registros.

• Un optimizador debe tener algunos procedimientos disponibles para una operación de join tales como:
• Un procedimiento para el caso en que la condición sea a través de una clave candidata.
• Un procedimiento para el caso en que el campo de restricción (en alfa unión) esté indexado.
• Un procedimiento para el caso en que el campo de restricción no esté indexado pero sí agrupados los datos físicamente.

Generación y elección de planes de consulta

Cuando elegimos un plan una de las primeras cosas que debemos tener en cuenta para escogerlo es la estimación de costes. Estos costes dependen de muchos aspectos tales como: el nº de operaciones de entrada/salida del disco requeridas, la utilización de la CPU. Una consulta suele implicar la generación de resultados intermedios, estos resultados estarán directamente relacionados con el número de E/S.