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 nal! "s en este momento donde se realiza las siguientes preguntas: #$or %u& optimizar las consultas' #Qu& es la optimización de consultas' • •
Introducción #$or #$or %u& optimizar las consultas' $or%ue necesitamos mejorar los tiempos de respuesta en un sistema de gestión de base de datos relacional ( as) tambi&n %ue la aplicación muestre peticiones de respuesta respuesta inmediata a los usuarios nales! #Qu& es la optimización de consultas' La optimización en el proceso de modicar un sistema para mejorar su eciencia o tambi&n el uso de los recursos recursos disponibles *como C$+! •
•
Deniciones- a recordar Consultas SQL: Son las consultas realizadas a la base de datos por medio del lenguaje SQL ( dependiendo de la respuesta podemos obtener: muc.os datos, pocos datos, alg/n dato o ning/n dato! Instrucciones SQL: +sando el lenguaje SQL podemos com/nmente usar: Select, 0rom, 1.ere, Order b(, 2roup b(, insert, delete, etc! Donde cada una de ellas genera tiempos de ejecución ( tienen un tiempo de ejecución!
Deniciones- a recordar
• • •
3upla: "s un registro o la de la tabla! 4elación : "s un conjunto de tuplas! 5ndices: Son campos %ue permiten realizar b/s%uedas en las bases de datos! Son la parte importante a la optimización de consultas!
$roceso de Optimización •
"l $roceso de Optimización de consultas tiene los cuatro puntos importantes! 4epresentación interna de consultas! Con6ersión a 7orma canónica! "lección de procedimientos de bajo ni6el! 2eneración ( elección de planes de consultas! • • • •
$roceso de Optimización Representación interna de consultas.
Su caracter)stica principal es %ue la base de datos debe ser relacionalmente completo, es decir %ue para obtener in7ormación de esta, el usuario pueda na6egar completamente por toda la 8ase de Datos sin ninguna dicultad!
•
Conversión a forma canónica.
9l realizar la optimización de consulta puede %ue se .a(a optimizado la consulta pero puede %ue esta no sea la mejor respuesta al ejecutarlo *es decir podr)a tomar mas tiempo de ejecución %ue antes de optimizarlo
•
"L rendimiento o tiempo de respuesta debe ser el mas optimo o mejor en este ni6el!
•
$roceso de Optimización Elección de procedimientos a bajo nivel.
Implica la e6aluación de la consulta pre6iamente trans7ormada en el punto anterior es decir la 6ericación de la consulta optimizada!
•
Generación y elección de planes de consultas.
+n plan de consulta es la 6ericación de costos %ue implica en la base de datos los costos de comunicación, costos de procedimientos, costos de ejecución! Ejemplo: "s decir si tenemos una consulta para realizar la b/s%uedas de alg/n dato en una tabla donde tenemos ;<<< registros, debemos tomar en cuenta cual es costo de ejecución de esta consulta! +na consulta %ue tarda = segundos en su tiempo de respuesta ( %ue con la nue6a optimización tarde ; segundos esto (a implica un costo de ejecución! "n optimización de %uer(>s los costos de optimización de consultas deben generar un tiempo de ejecución m)nimo!
•
•
•
3iempos de ejecución
+na consulta a una tabla especica en una base de datos determinada nos da di7erentes tiempos de ejecución dependiendo de la cantidad de usuarios %ue est&n accediendo a esta misma tabla! •
3iempos de ejecución •
•
•
Que acceda un usuario el tiempo de respuesta ser? inmediata pero %ue accedan @<<< usuarios (a el tiempo de ejecución tomara mas tiempo! Dependiendo de la cantidad de usuarios %ue accedan a esta misma consulta pueden llegar a colapsar la base de datos o la misma aplicación cual seria totalmente incorrecto! #Cómo a(udan a optimizar los tiempos de ejecución' •
•
Costos de comunicación! Costos de almacenamiento!
3iempos de ejecución Ejemplo:
"n una entidad bancaria A8anco +niónB donde tiene di7erentes sucursales ( cientos de personas %ue realizan un sin n de transacciones desde pagar una cuenta .asta realizar trans7erencias! "l simple .ec.o %ue cada persona sea atendido ( el tiempo %ue se toma en responder una aplicación *( por debajo la base de datos a una determinada b/s%ueda, en este caso el nombre de la $ersona puede llegar a tomar muc.o tiempo ( por ende terminar en una descon7ormidad del usuario por la ecesi6a demora en su atención!
3iempos de ejecución
Mejores practicas para optimización de consultas • • • • • •
o utilizar el S"L"C3 E! "n un S"L"C3 seleccionar solo los campos %ue se necesitan! +tilizar el I"4, L"03, 4I2H3 FOI! Denir el 9LI9S a las tablas en el S"L"C3! "6itar el Cast ( 7ormulas dentro de la consulta SQL! "l orden del 04OM debe ir de la tabla de menor registros a ma(or!
Mejores practicas para optimización de consultas •
$asos a seguir para optimizar una consulta: Identicar las consultas lentas %ue est?n tomando muc.o tiempo de ejecución! +na 6ez %ue est?n identicadas las consultas es 6ericar por%ue estas consultas son lentas o tienen un tiempo de respuesta etensa! +sar el comando explain %ue muestra el plan de ejecución de la consulta! •
•
•
Mejores practicas para optimización de consultas •
$asos a seguir para optimizar una consulta: Comando explain %ue muestra el plan de ejecución de la consulta para los di7erentes S28D! •
Mejores practicas para optimización de consultas Ejemplo 1 (Indice: Se tienen dos tablas donde la relación es *,n ( la consulta actual ejecutada en el S28D es lenta! G+n $a)s puede tener 6arias ciudades! G+na ciudad solamente puede pertenecer a un $a)s!
Mejores practicas para optimización de consultas
Mejores practicas para optimización de consultas
• •
•
•
La consulta en color 6erde en la consulta actual *cual es lenta! "l comando explain nos retorna una tabla, donde indica %ue pasos esta realizando el S28D! Los elementos mas importantes son las dos ultimas columnas de cada la! Las dos ultimas columnas de la primera la nos indica %ue el S28D nos trajo un registro usando un )ndice
Mejores practicas para optimización de consultas
•
•
+n )ndice es una de las maneras mas r?pidas para traer in7ormación dentro de un S28D! Internamente esta usando estructura de datos complejas como .as.tables, etc!
Mejores practicas para optimización de consultas
•
• •
•
+n )ndice se comporta como un directorio de paginas blancas o las agendas tele7ónicas de COM3"CO! $ensemos en %ue nos preguntan cual es el numero de tel&7ono de A3iolo 2onzalesB! 4?pidamente 6amos a la agenda tele7onica ( buscamos por apellidos %ue empiecen por 2, donde buscamos todos los 2onzales ( despu&s todos los 3iolo ( se de6uel6e para este caso el AGB! "s de esta manera como 7unciona el )ndice!
Mejores practicas para optimización de consultas •
Continuando con el mismo ejemplo, el S28D busca los registros %ue comiencen con A"B ( de6uel6e una sola la!
Mejores practicas para optimización de consultas •
Las dos ultimas columnas de la segunda la nos indica %ue el S28D nos trajo
Mejores practicas para optimización de consultas
Mejores practicas para optimización de consultas
•
• •
Similar al caso anterior el manejador esta manejando una b/s%ueda secuencial, comenzando por la A9B ( pregunta la ciudad de A9ac.enB tiene como código de pa)s A"B ( este de6uel6e no! as) se .ace el recorrido con cada registro de la tabla .asta
Mejores practicas para optimización de consultas •
•
9.ora en lugar de buscar secuencialmente los registros, solamente traer? los registros de enezuela! 9ctualmente esta le(endo registros en lugar de
Mejores practicas para optimización de consultas •
"jemplo @ *Indice
Mejores practicas para optimización de consultas •
Los )ndices 7ueron creados en sus lla6es primarias •
•
•
•
$roducto: *$N idproducto "ti%ueta: *$N ideti%ueta $roducto"ti%ueta: *idproducto, ideti%ueta PP es un )ndice compuesto
"n la siguientes consultas #cual de las consultas son las mas optimas'
Mejores practicas para optimización de consultas
Mejores practicas para optimización de consultas •
La respuesta es: La primera consulta es la mas optima, por%ue una esta usando )ndice ( la otra no! "l orden como se crean los )ndices es mu( importante para este caso! Debemos tomar en cuenta %ue cuando la tabla A$roducto"ti%uetaB 7ue creada en sus atributos el primer )ndice a ser creado 7ue AidproductoB ( luego Aideti%uetaB! Como la consulta de la iz%uerda usa el AidproductoB primero, entonces esta .ace uso del )ndice! O39: o importa %ue este usando la lla6e compuesta con tal %ue use el primero en esta secuencia! •
• •
•
Mejores practicas para optimización de consultas •
La consulta derec.a no .ace uso del )ndice dado %ue usa Aideti%uetaB primero ( en nuestra tabla relacional A$roducto"ti%uetaB no .a( un )ndice %ue establezca como primer campo!
Mejores practicas para optimización de consultas "Cómo solucionar este problema# "s crear un nue6o )ndice, donde el primer )ndice ser? Aideti%uetaB! •
Mejores practicas para optimización de consultas Ejemplo $: %ub& consultas o consultas anidadas.
"s 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! 4elacion ,,n entre Orden ( $roducto G +na orden puede tener 6arios productos
Mejores practicas para optimización de consultas •
•
4ealizar una consulta para obtener el producto mas caro %ue esta en una orden! "isten dos maneras de .acer esta consulta, ( las dos traen la misma respuesta!
Mejores practicas para optimización de consultas
•
Las dos realizan la misma consulta con la di7erencia %ue: •
•
•
•
La consulta del lado iz%uierdo realiza una sub&consulta, en la sección del S"L"C3! La consulta del lado derec.o realizan un I''ER )I'.
$ara e7ecto de ambas consultas, (a los )ndices est?n optimizados! Cuando usamos el comando explain en ambas consultas, obtenemos la siguiente
Mejores practicas para optimización de consultas •
$or la respuesta obtenida la consulta del lado iz%uierdo tomo muc.o mas tiempo el de la derec.a!
"*or +u, # •
•
La consulta del lado iz%uierdo tiene %ue ejecutar la subGconsulta cada 6ez %ue se obtiene un nue6o producto! Suponiendo %ue tenemos un millón de productos, entonces la subG consulta se ejecuta un millón de 6eces!
Mejores practicas para optimización de consultas •
La consulta del lado derec.o, se ejecuta siempre a tra6&s de dos consultas: +na consulta sobre producto otra consulta sobre orden luego 6iene la unión de ambas • • •
Mejores practicas para optimización de consultas "Cómo podemos mejorar las consultas como pro-ramadordesarrollador#
Cuando desarrollen consultas no lo .agan pensando como un programador procedimental! *3ratar de pensar O procedimental! "n SQL b?sico no eisten estructuras como 7or, .ile, repeat! 3raten de .ablar en lenguaje SQL! Hacer uso de FOIs cuando sea posible! Intentar O usar consultas anidadas! Cuando no sea posible usar consultas anidadas, usar una consulta anidada en el 04OM! o colocar consultas anidadas en el S"L"C3 ni en el 1H"4"! •
•
• • •
•
Mejores practicas para optimización de consultas •
"n el siguiente ejemplo se esta creando una tabla temporal llamada mi orden como consulta anidada!
Mejores practicas para optimización de consultas
Continuando con otros tips:
$rimero: Hacer un buen es%uemaPmodelo *normalizado de base de datos! Modelar la base de datos desde lo mas sencillo ( despu&s normalizar! +na 6ez %ue se termino de normalizar se puede comenzar a desnormalizar si .a( necesidad *no es obligatorio! "jemplo: 3enemos una tabla CLI"3", donde tiene atributos %ue no se utilizan muc.o pero %ue si son necesarios! R"ntonces lo %ue se puede .acer es crear otra tabla relacionada a CLI"3" con un cardinalidad *,! R"sto a la .ora de realizar consultas nos a.orra tiempo de lectura, espacio en memoria ambas importantes para la rapidez de la consulta! •
•
Mejores practicas para optimización de consultas Continuando con otros tips:
Otra manera de mejorar la 6elocidad en la base de datos es realizar los c?lculos en una .ora especica del d)a, pre7eriblemente la noc.e ( poner estos resultados en un campo! Ejemplo: Supongamos %ue %ueremos saber en la +98 por 7acultad todos los promedios de los estudiantes ( seg/n a su nota lle6en una posición *es decir ,@,,-- n posición! R$ara realizar este re%uerimiento se necesita saber todos los estudiantes acti6os por 7acultad! RSe necesita obtener todas las notas por cada estudiante, ( a estas se deben •
Mejores practicas para optimización de consultas Continuando con otros tips:
R"n
6ez de .acer estos pasos cada 6ez %ue se solicita este re%uerimiento, lo %ue se .ace es ejecutar esta consulta en la noc.e una sola 6ez ( los datos se guardan en un campo para todos los estudiantes! RLuego cuando alguien re%uiera saber las posiciones de los estudiantes, simplemente se lee el campo! R$or ultimo, el tiempo %ue se a.orra en este proceso es inmenso!
Mejores practicas para optimización de consultas Continuando con otros tips:
+sa los tipos de datos o estructura de datos adecuados: R o utilizar una 94CH94*;< para almacenar un Carnet de Identidad, donde las cedulas son .asta m?imo de < d)gitos! R Mas caracteres implican mas espacio para los )ndices, mas espacio para almacenar, mas espacio para leer, mas espacio en memoria, ( todo esto se traduce en lentitud! Otro punto importante para mejorar la 6elocidad es usar las instrucciones del S28D %ue estamos usando, buscar en su manual o google cuales son las mejores instrucciones %ue otras para el manejador! •
•
Mejores practicas para optimización de consultas
