Optimización de Consultas Tema_2

Optimización de Consultas

Docente: Maria Hurtado Calle

Introducción

• La complejidad de una consulta realizada a la base de datos por medio del lenguaje SQL, puede tomar un tiempo considerable obteniendo no siempre una respuesta óptima para el usuario final.

• Es en este momento donde se realiza las siguientes preguntas:

• ¿Por qué optimizar las consultas?

• ¿Qué es la optimización de consultas?

Introducción¿Por qué optimizar las consultas?

•Porque necesitamos mejorar los tiempos de respuesta en un sistema de gestión de base de datos relacional y así también que la aplicación muestre peticiones de respuesta inmediata a los usuarios finales.

¿Qué es la optimización de consultas?

•La optimización en el proceso de modificar un sistema para mejorar su eficiencia o también el uso de los recursos disponibles (como CPU).

Definiciones… a recordar

Consultas SQL: Son las consultas realizadas a la base de datos por medio del lenguaje SQL y dependiendo de la respuesta podemos obtener: muchos datos, pocos datos, algún dato o ningún dato.

Instrucciones SQL: Usando el lenguaje SQL podemos comúnmente usar: Select, From, Where, Order by, Group by, insert, delete, etc. Donde cada una de ellas genera tiempos de ejecución y tienen un tiempo de ejecución.

Definiciones… a recordar

• Tupla: Es un registro o fila de la tabla.

• Relación : Es un conjunto de tuplas.

• Índices: Son campos que permiten realizar búsquedas en las bases de datos.

Son la parte importante a la optimización de consultas.

Proceso de Optimización

• El Proceso de Optimización de consultas tiene los cuatro puntos importantes.

•Representación interna de consultas.

•Conversión a forma canónica.

• Elección de procedimientos de bajo nivel.

•Generación y elección de planes de consultas.


Representación interna de consultas.

•Su característica principal es que la base de datos debe ser relacionalmente completo, es decir que para obtener información de esta, el usuario pueda navegar completamente por toda la Base de Datos sin ninguna dificultad.

Conversión a forma canónica.

•Al realizar la optimización de consulta puede que se haya optimizado la consulta pero puede que esta no sea la mejor respuesta al ejecutarlo (es decir podría tomar mas tiempo de ejecución que antes de optimizarlo)

•EL rendimiento o tiempo de respuesta debe ser el mas optimo o mejor en este nivel.


Elección de procedimientos a bajo nivel.

• Implica la evaluación de la consulta previamente transformada en el punto anterior es decir la verificación de la consulta optimizada.

Generación y elección de planes de consultas.

•Un plan de consulta es la verificación de costos que implica en la base de datos los costos de comunicación, costos de procedimientos, costos de ejecución.

Ejemplo: Es decir si tenemos una consulta para realizar la búsquedas de algún dato en una tabla donde tenemos 15000 registros, debemos tomar en cuenta cual es costo de ejecución de esta consulta.

•Una consulta que tarda 6 segundos en su tiempo de respuesta y que con la nueva optimización tarde 15 segundos esto ya implica un costo de ejecución.

•En optimización de query’s los costos de optimización de consultas deben generar un tiempo de ejecución mínimo.

Tiempos de ejecución

•Una consulta a una tabla especifica en una base de datos determinada nos da diferentes tiempos de ejecución dependiendo de la cantidad de usuarios que estén accediendo a esta misma tabla.


• Que acceda un usuario el tiempo de respuesta será inmediata pero que accedan 2000 usuarios ya el tiempo de ejecución tomara mas tiempo.

• Dependiendo de la cantidad de usuarios que accedan a esta misma consulta pueden llegar a colapsar la base de datos o la misma aplicación cual seria totalmente incorrecto.

• ¿Cómo ayudan a optimizar los tiempos de ejecución?

•Costos de comunicación.

•Costos de almacenamiento.

•Costos de procesamiento.

Tiempos de ejecuciónEjemplo:

En una entidad bancaria “Banco Unión” donde tiene diferentes sucursales y cientos de personas que realizan un sin fin de transacciones desde pagar una cuenta hasta realizar transferencias.

El simple hecho que cada persona sea atendido y el tiempo que se toma en responder una aplicación (y por debajo la base de datos) a una determinada búsqueda, en este caso el nombre de la Persona puede llegar a tomar mucho tiempo y por ende terminar en una desconformidad del usuario por la excesiva demora en su atención.

Mejores practicas para optimización de consultas

• No utilizar el SELECT *.

• En un SELECT seleccionar solo los campos que se necesitan.

• Utilizar el INNER, LEFT, RIGHT JOIN.

• Definir el ALIAS a las tablas en el SELECT.

• Evitar el Cast y formulas dentro de la consulta SQL.

• El orden del FROM debe ir de la tabla de menor registros a mayor.


• Pasos a seguir para optimizar una consulta:

• Identificar las consultas lentas que están tomando mucho tiempo de ejecución.

• Una vez que están identificadas las consultas es verificar porque estas consultas son lentas o tienen un tiempo de respuesta extensa.

• Usar el comando explain que muestra el plan de ejecución de la consulta.


• Pasos a seguir para optimizar una consulta:

• Comando explain que muestra el plan de ejecución de la consulta para los diferentes SGBD.


Ejemplo 1 (Indice): Se tienen dos tablas donde la relación es (1,n) y la consulta actual ejecutada en el SGBD es lenta.

-Un País puede tener varias ciudades.

-Una ciudad solamente puede pertenecer a un País.


• La consulta en color verde en la consulta actual (cual es lenta).

• El comando explain nos retorna una tabla, donde indica que pasos esta realizando el SGBD.

• Los elementos mas importantes son las dos ultimas columnas de cada fila.

• Las dos ultimas columnas de la primera fila nos indica que el SGBD nos trajo un registro usando un índice


• Un índice es una de las maneras mas rápidas para traer información dentro de un SGBD.

• Internamente esta usando estructura de datos complejas como hashtables, etc.


• Un índice se comporta como un directorio de paginas blancas o las agendas telefónicas de COMTECO.

• Pensemos en que nos preguntan cual es el numero de teléfono de “Tiofilo Gonzales”.

• Rápidamente vamos a la agenda telefonica y buscamos por apellidos que empiecen por G, donde buscamos todos los Gonzales y después todos los Tiofilo y se devuelve para este caso el “111-1111”.

• Es de esta manera como funciona el índice.


• Continuando con el mismo ejemplo, el SGBD busca los registros que comiencen con “VEN” y devuelve una sola fila.


• Las dos ultimas columnas de la segunda fila nos indica que el SGBD nos trajo 4079 registros y no esta usando un índice.


• Similar al caso anterior el manejador esta manejando una búsqueda secuencial, comenzando por la “A” y pregunta la ciudad de “Aachen” tiene como código de país “VEN” y este devuelve no.

• Y así se hace el recorrido con cada registro de la tabla hasta 4079 veces.

• SOLUCION: Para mejorar esta consulta creamos un índice en el campo donde estamos buscando con la siguiente instrucción:


• Ahora en lugar de buscar secuencialmente los registros, solamente traerá los registros de Venezuela.

• Actualmente esta leyendo 3 registros en lugar de 4079 y se ahorro algunos segundos de procesamiento


• Ejemplo 2 (Indice)


• Los índices fueron creados en sus llaves primarias

• Producto: (PK) id_producto

• Etiqueta: (PK) id_etiqueta

• ProductoxEtiqueta: (id_producto, id_etiqueta) // es un índice compuesto

• En la siguientes consultas ¿cual de las consultas son las mas optimas?


• La respuesta es:

• La primera consulta es la mas optima, porque una esta usando índice y la otra no.

• El orden como se crean los índices es muy importante para este caso.

• Debemos tomar en cuenta que cuando la tabla “ProductoxEtiqueta” fue creada en sus atributos el primer índice a ser creado fue “id_producto” y luego “id_etiqueta”.

• Como la consulta de la izquerda usa el “id_producto” primero, entonces esta hace uso del índice.

NOTA: No importa que este usando la llave compuesta con tal que use el primero en esta secuencia.


• La consulta derecha no hace uso del índice dado que usa “id_etiqueta” primero y en nuestra tabla relacional “ProductoxEtiqueta” no hay un índice que establezca como primer campo.


¿Cómo solucionar este problema?

•Es crear un nuevo índice, donde el primer índice será “id_etiqueta”.


Ejemplo 3: Sub- consultas o consultas anidadas.Es otro método usado por los programadores, pero si están no se realizan de manera correcta pueden causar tiempos de respuesta largos en sus consultas.Relacion 1,,n entre Orden y Producto - Una orden puede tener varios productos


• Realizar una consulta para obtener el producto mas caro que esta en una orden.

• Existen dos maneras de hacer esta consulta, y las dos traen la misma respuesta.


• Las dos realizan la misma consulta con la diferencia que:

• La consulta del lado izquierdo realiza una sub-consulta, en la sección del SELECT.

• La consulta del lado derecho realizan un INNER JOIN.

• Para efecto de ambas consultas, ya los índices están optimizados.

• Cuando usamos el comando explain en ambas consultas, obtenemos la siguiente respuesta.


• Por la respuesta obtenida la consulta del lado izquierdo tomo mucho mas tiempo el de la derecha.

¿Por qué ?

• La consulta del lado izquierdo tiene que ejecutar la sub-consulta cada vez que se obtiene un nuevo producto.

• Suponiendo que tenemos un millón de productos, entonces la sub-consulta se ejecuta un millón de veces.


• La consulta del lado derecho, se ejecuta siempre a través de dos consultas:

• Una consulta sobre producto

• Y otra consulta sobre orden

• Y luego viene la unión de ambas


¿Cómo podemos mejorar las consultas como programador/desarrollador?

•Cuando desarrollen consultas no lo hagan pensando como un programador procedimental. (Tratar de pensar NO procedimental.)

•En SQL básico no existen estructuras como for, while, repeat. Traten de hablar en lenguaje SQL.

•Hacer uso de JOINs cuando sea posible.

•Intentar NO usar consultas anidadas.

•Cuando no sea posible usar consultas anidadas, usar una consulta anidada en el FROM.

•No colocar consultas anidadas en el SELECT ni en el WHERE.


• En el siguiente ejemplo se esta creando una tabla temporal llamada mi orden como consulta anidada.


Continuando con otros tips:

•Primero: Hacer un buen esquema/modelo (normalizado) de base de datos.

Modelar la base de datos desde lo mas sencillo y después normalizar.

•Una vez que se termino de normalizar se puede comenzar a desnormalizar si hay necesidad (no es obligatorio).

Ejemplo: Tenemos una tabla CLIENTE, donde tiene atributos que no se utilizan mucho pero que si son necesarios.

‐Entonces lo que se puede hacer es crear otra tabla relacionada a CLIENTE con un cardinalidad (1,1).

‐Esto a la hora de realizar consultas nos ahorra tiempo de lectura, espacio en memoria ambas importantes para la rapidez de la consulta.



•Otra manera de mejorar la velocidad en la base de datos es realizar los cálculos en una hora especifica del día, preferiblemente la noche y poner estos resultados en un campo.

Ejemplo: Supongamos que queremos saber en la UAB por facultad todos los promedios de los estudiantes y según a su nota lleven una posición (es decir 1,2,3,…… n posición).

‐Para realizar este requerimiento se necesita saber todos los estudiantes activos por facultad.

‐Se necesita obtener todas las notas por cada estudiante, y a estas se deben aplicar unas formulas y por ultimo se deben ordenar de mayor a menor.



‐En vez de hacer estos pasos cada vez que se solicita este requerimiento, lo que se hace es ejecutar esta consulta en la noche una sola vez y los datos se guardan en un campo para todos los estudiantes.

‐Luego cuando alguien requiera saber las posiciones de los estudiantes, simplemente se lee el campo.

‐Por ultimo, el tiempo que se ahorra en este proceso es inmenso.



•Usa los tipos de datos o estructura de datos adecuados:

‐ No utilizar una VARCHAR(50) para almacenar un Carnet de Identidad, donde las cedulas son hasta máximo de 10 dígitos.

‐ Mas caracteres implican mas espacio para los índices, mas espacio para almacenar, mas espacio para leer, mas espacio en memoria, y todo esto se traduce en lentitud.

•Otro punto importante para mejorar la velocidad es usar las instrucciones del SGBD que estamos usando, buscar en su manual o google cuales son las mejores instrucciones que otras para el manejador.


• Los índices no funcionan cuando están siendo usadas dentro una formula.

• Si tenemos un índice en el campo fecha y usamos la función to_days() sobre fecha, se pierde el uso del índice.

• La manera de mejorar la velocidad es reescribir la consulta, tal que el índice no sea usado dentro la función.

Optimización de Consultas Tema_2

Documents

Transcript of Optimización de Consultas Tema_2