Practica 04 (1)

Sistemas Manejadores de Bases de Datos Pr´ actica actica 04: Optimizaci´ Optimiz aci´ on on de consultas Profes Pro fesor: or: Dr. Javier Javie r Garc Gar c´ıa Garc Gar c´ıa Ayudante: Luis Roberto Cruz Mart´ Mart´ınez Forma de entrega: entrega: individual individual

0.1

Meta

Que el alumno comprenda la importancia que tiene el proceso de diseño de consultas y el impacto que tiene esto en el desempeño no del sistema.

0.2 0.2

Objeti bjetiv vos

Al finalizar esta práctica actica el alumno será capaz de: 1. Identifica Identificarr las equivalenc equivalencias ias entre las distintas distintas cláusulas ausulas de SQL. 2. Identifica Identificarr cuando utilizar est´ as as equivalencias. 3. Desarrollar un proceso concreto para la creación de consultas eficientes.

0.3 0.3

Des Desarro rrollo llo

En general es posible obtener el resultado de una consulta de muy diversas formas, esto se debe en parte a que existen operaciones equivalentes dentro de SQL y en parte a que el proceso de dise˜ no de una consulta obedece en gran parte a la intuición no on del programador. programador. La desvent desventaja aja de lo anterior, es que posiblemente no se genere una consulta que tenga el mejor desempeño posible a´ un un cuando el planificador de consultas del SMBD haga la optimización on interna.Por ello es importante conocer ciertos elementos, por ejemplo técnicas ecnicas y equivalencias equivalencias entre operaciones, que permitan crear consultas que se ejecuten de la manera más eficiente. El catalogo del sistema

Aún un cuando parece redundante, es importante ejecutar tareas de mantenimiento de manera constant stante, e, sobre sobre todo en las tablas tablas don donde de se mantie mantienen nen ´ındice ındicess que est´ están an siendo siendo utilizados utilizados continuamente.En PostgreSQL, es recomendable ejecutar la cláusula ANALYZE ANALYZE sobre los ´ındices para conservar conservar actualizados a ctualizados los valores valores estad´ estad´ısticos que utiliza el SMBD al generar los planes de ejecución. on.

1

Eliminaci´ on on de atributos

La primera y más as simple de las mejoras que hay que tomar en cuenta es la eliminación de atributos. Consideran Considerando do una consulta del tipo: “Obtener “Obtener los nombres nombres de los clientes clientes que han realizado realizado una compra”, la solución on m´ as as simple es la siguiente. SELECT SELECT nombre nombre cliente cliente FROM cliente, cliente, compra compra WHERE WHERE cliente. cliente.id id cliente cliente = compra.i compra.id d cliente; cliente;

La sentencia SQL anterior es correcta, mas no es la más as eficiente, sobretodo si toda esta información on se encuen encuentra tra en una base de datos datos distri distribui buida. da. Inter Internam namen ente te el sistem sistemaa acarre acarreaa todos todos los atributos que pertenecen a las dos tablas, y con ello realiza la operación correspondiente al producto cartesiano. La evidencia se hace mayor cuando se consideran atributos que son de tamaño muy extenso, por ejemplo imágenes agenes o archivos binarios. Una sentencia equivalente que obtiene únicamente unicamente los atributos de inter´ interés es tiene la siguiente estructura: SELECT SELECT nombre nombre cliente cliente FROM (SELEC (SELECT T id client cliente, e, nombre nombre client cliente e FROM FROM clien cliente) te) AS clien clienteO teOp p NATURAL NATURAL JOIN (SELEC (SELECT T id client cliente e FROM FROM compra compra) ) AS compra compraOp; Op;

El agrupamiento

El agrupamiento es una operación on muy importante ya que permite encontrar respuesta a un amplio conjunto de consultas distintas. Sin embargo es importante utilizarlo adecuadamente. Considerando la consulta “Obtener el identificador de cada cliente as´ı como el total de sus compras”, generalmente se opta por realizar una operación on de agrupamien agrupamiento to sobre el resultado resultado obtenido de la reunión on de tablas, esto es, realizar la reunión on de las tablas ‘cliente’ y ‘compra’ y posteriormente aplicar aplicar la operaci´ operación on de agrupamiento a la tabla resultante, la sentencia en SQL queda de la siguiente forma: SELECT SELECT id cliente, cliente, COUNT(id COUNT(id cliente) cliente) AS cantidad cantidad FROM cliente NATURAL JOIN compra compra GROUP GROUP BY id client cliente; e;

El resultado es completamente correcto, pero nuevamente estamos acarreando información redundante dentro de nuestra consulta.La optimización on de la consulta se sigue de las siguientes observacione aciones. s. Primer Primerame ament ntee hay hay que observ observar que al consul consultar tar la tabla tabla de ‘compr ‘compra’ a’ se obtien obtienen en los identificadores de los clientes que han hecho una compra, por lo tanto es en esta relación donde es posible agrupar el resultado para que la relación que participe en la reunión on con la tabla ‘cliente’ sea de menor tama˜ no. De esta forma, ocupara menos memoria y se evitan lecturas innecesarias a disco. no. Dado esto, se puede imaginar que siempre es conveniente llevar a cabo una agrupación on antes de una reunión. on. Pero Pero no es as´ as´ı, se ha visto visto que el tama˜ no de los grupos formados por la sentencia no GROUP BY influye en el rendimiento de la consulta, lo cual se explica de la siguiente forma: Si el número umero de grupos formados durante el agrupamiento es semejante al número umero de elementos de la tabla original, entonces no se obtiene ganancia al reducir el tamaño no de la misma sino que por el 2

contrari contrario, o, se introduce una sobrecarga sobrecarga de trabajo al realizar realizar el agrupamien agrupamiento to y la consulta se ejecuta ejecuta similarmente a la primera forma. Actividad

1. Ejecuta Ejecuta las sentenc sentencias ias SQL anteriore anterioress y obt´ en en su plan de ejecuci´ ejecución on utilizando la sentencia EXPLAIN.Para ello considere dos tablas de la base de datos del curso (i.e. la base de TPCH), entre más as grandes sean las tablas mejor. 2. Examina Examina los distinto distintoss algoritmos algoritmos utilizados utilizados por el planificador planificador de consultas consultas de PostgreSQ PostgreSQL. L. 3. Inhabilita los algoritmos utilizados con la sentencia SET y ejecuta nuevamente las sentencias SQL. 4. Compara Compara el rendimiento rendimiento de las consultas.¿ consultas.¿Exist Existee alguna mejora considerable considerable?? ´ Indices Indices compuestos compuestos

Los ´ındices compuestos son simplemente aquellos que están an basados en mútiples utiples atributos. atributos. PostPostgreSQL unicamente usa un ´ındice ındice por p or tabla cuando están an procesando procesando una consulta. consulta. Esto significa significa que si se tienen varias columnas que frecuentemente aparecen juntas en una cl ásula WHERE, se presenta presenta la oportunidad oportunidad de acelerar acelerar estas consultas consultas al crear un ´ındice ındice compuesto. compuesto. Pero Pero es preciso preciso tener tener cuidado, cuidado, ya que se puede interpret interpretar ar que el contar contar con un ´ındice ındice compuesto compuesto permite acelerar acelerar arbitrariamente las consultas que se ejecutan sobre los atributos donde se encuentra el ´ındice. Esto es parcialmente cierto cuando se observa que si una tabla tiene un ´ındice ındice formado por mútiples utiples columnas, cualquier prefijo puede ser usado por el optimizador de consultas de PostgreSQL para encontrar las tuplas. Por ejemplo, considérese erese una base de datos hipotética etica en la cual se crea un ´ındice compuesto sobre las columnas de la tabla cliente[nombre cliente, ap pat cliente, ap mat cliente], entonces se tienen capacidades de b´ uqueda con ´ındices ındices sobre ‘nombre ‘ nombre cliente’, ‘nombre cliente, ap pat cliente’ y ‘nombre cliente, ap pat cliente, ap mat cliente’ . CREATE CREATE INDEX INDEX indic indice e compue compuesto sto1 1 ON clien cliente te (nombr (nombre e client cliente, e, ap pat client cliente, e, ap mat clien cliente) te); ;

PostgreSQL no puede usar un ´ındice parcial si las columnas no forman un prefijo del ´ındice. Suponiendo que se cuenta con sentencias SELECT como estas: •

•

•

SELECT SELECT

FROM FROM client cliente e WHERE WHERE nombre nombre clien cliente te = ‘Javie ‘Javier’; r’;

SELECT SELECT FROM FROM client cliente e WHERE WHERE nombre nombre clien cliente te = ‘Javie ‘Javier’ r’ AND ap pat clien cliente te = ‘Garc ‘Garcia’ ia’; ; SELECT SELECT

FROM FROM client cliente e WHERE WHERE ap pat client cliente e = ‘Ville ‘Villeda’ da’; ;

Debido a la forma en que PostgreSQL construye los ´ındices compuestos, éste este puede usar el ´ındice ‘indice compuesto1’ sólo olo para resolver consultas basadas en el nombre, o en el nombre y los apellidos, sin embargo, embargo, no usar´ a el ´ındice en una consulta que haga referencia únicamente unicamente a la columnas de los apellidos. Por lo tanto, sólo olo las dos primeras consultas hacen uso del ´ındice ındice ‘indice compuesto1’. La idea es que los ´ındices compuestos pueden usarse frecuentemente frecuentemente para acelerar algunas consultas complejas, pero es necesario entender sus limitaciones y es recomendable ejecutar algún tipo de prueba en vez de asumir que estos ´ındices siempre ayudan a mejorar el rendimiento de una consulta. 3

Actividad

1. Ejecuta Ejecuta las sentenc sentencias ias SQL anterior anteriores, es, sustituy sustituyendo endo la tabla hipot´ hipotética etica cliente cliente por p or la tabla Customer de la base de datos del curso, y obtén en el plan de ejecución on de las consultas utilizando utilizando la sentencia EXPLAIN. 2. Verifica para que consultas se utilizan búsquedas usquedas exhaustivas y para cuales se se utilizan los ´ındi ın dice ces. s. Reunio Reu niones nes expl exp l´ıcitas ıcit as

La operación on de reunión on de tablas, es posiblemente la más a s utilizada dada la forma en que se encuen encuentra tran n estruc estructur turadas adas las bases bases de datos, datos, por ello ello es de partic particula ularr importa importanci nciaa optimi optimizar zar al máximo aximo a las consultas en este rubro. La operación on de reunión on (JOIN) se encarga de relacionar los valores que se encuentran en una tabla con los valores de otra tabla, la operación on es binaria por lo cual siempre trabaja teniendo como entradas a 2 tablas aún un cuando se tenga una expresión on que involucre más as tablas (cuando se utiliza una sub-consulta está se ejecuta antes para servir como fuente de datos a la operación de reunión on inmediata). Es posible controlar al planificador de consultas de modo que permita la utilización de reuniones expl´ expl´ıcitas. La importancia imp ortancia de este hecho radica en que el espacio de soluciones, los planes de ejecución on que se generán an para una consulta dada, es bastante amplio.Por ello, conviene, utilizar heur´ heur´ısticas en la obtención on de un plan de ejecución on eficiente. Por ejemplo, en una consulta como la siguiente:

SELE SELECT CT * FROM FROM a, b, c WHER WHERE E a.id a.id = b.id b.id AND AND b.re b.ref f = c.id c.id; ;

el planificador es libre de reunir las tablas dadas en cualquier orden, por ejemplo puede generar un plan de ejecución on tal que reúna una las tablas A y B utilizando la condición on WHERE a.id = b.id, y luego reunir C a esta tabla resultante utilizando la otra condición WHERE. O bien, puede reunir B con C y luego ese resultado con A. O puede unir A con C y luego reunir el resultado con B, pero ser´ ser´ıa ineficiente dado que el producto cartesiano de A y C se tendr´ tendr´ıa que materializar, dado que no hay condición on aplicable a la cláusula ausula WHERE para permitir la optimización on de la consulta. Es importante importante observar observar que el planificador planificador tendr´ tendr´ıa que generar generar todos los planes de ejecuci´ ejecución on posibles, verificar sus costos (que indudablemente son variables) y escoger el mejor. Cuando las consultas requieren de dos o tres tablas, no hay muchas opciones,pero cuando el número umero se incrementa, el proceso de encontrar el plan de ejecución (no de obtener obtener el resultado) resultado) puede tomar bastante bastante tiempo. El planificador de PostgreSQL cambiará de una búsqueda usqueda exhaustiva a una búsque us queda da gen´ gen ética eti ca probab pro babil il´´ıstica. ısti ca.La La búsqueda usqueda genética etica toma menos tiempo, tiemp o, pero no necesariamente necesariamente encuentra al mejor plan pla n de ejecuci´ ejecu ción. on. Para forzar al planificador a utilizar un orden estricto de reuniones (cuando se tienen más de dos tablas) se debe modificar el parámetro ametro ‘join collapse limit’ y asignarle el valor de 1 utilizando la cláusula ausula SET.

4

Actividad

1. Cambia el parámetro ametro ‘join collapse limit ’ a 1. 2. Ejecuta la siguiente consulta y obtén en el plan de ejecución on de la misma: SELECT SELECT * FROM FROM CUSTO CUSTOMER MER NATURAL NATURAL JOIN (ORDERS NATURAL NATURAL JOIN LINEITEM) LINEITEM) WHERE WHERE CLERK CLERK = K AND QUANTI QUANTITY TY = C ;

Donde

K

y

C

son valores que elijas para personalizar tu consulta.

3. Ahora, ejecuta la siguiente consulta y obten el plan de ejecución: SELECT SELECT * FROM FROM (CUST (CUSTOME OMER R NATURA NATURAL L JOIN JOIN ORDERS ORDERS) ) NATURAL NATURAL JOIN LINEITEM LINEITEM WHERE WHERE CLERK CLERK = K AND QUANTI QUANTITY TY = C ;

Donde

K

y

C

son valores que elijas para personalizar tu consulta.

4. Compara Compara el rendimien rendimiento to de las consultas. consultas. ¿Existe ¿Existe alguna mejora considerable? considerable?

5

0.4

Activi Activida dades des:: Implem Implemen entac taci´ i´ on de un optimizador de conon sultas

1. ¿Como guardan los sistemas manejadores de bases de datos sus catálogos alogos de datos? 2. ¿Que es un bloque de consulta? 3. ¿A que se refiere el termino forma normal conjunctiva? 4. ¿Porque es importante estimar el tamaño no de salida de un operador? 5. ¿Qué es el factor de reducción? on? 6. ¿Qué es mejor: factor de reducción on o histogramas?, justifique su respuesta. 7. ¿Qu´ ¿Qué método etodo es el preferido preferido por los sistemas sistemas manejadore manejadoress de bases de datos modernos modernos para estimar el tamaño no de la salida de sus operadores? 8. ¿Qué histograma arroja resultados más as precisos en la estimación on del tama˜ no no de salida de un operador: el equiwidth o el equidepth?, justifique su respuesta. 9. ¿Qué es una

σπx

expresi´ on?, ¿porque no consideran having y el group by? on?,

10. El optimizador de consultas (hablando en términos erminos de implementaci´ on) revisa todos los planes on) de trabajo y selecciona el mejor en cuanto a costo, ¿es verdad esto?, justifique su respuesta. 11. ¿Que significa que un operador se aplique al vuelo?, ¿para que operadores aplica dicho termino? termino? 12. Un operador puede recibir datos materializados o mediante pipeline, explique cada una de dichas formas. ¿Cual se prefiere utilizar en los optimizadores de consultas?

0.5 0.5

Ejer Ejerc cicio icioss

1. Cuando la consulta involucra involucra reuniones externas, externas, el planificador tiene menor libertad para elegir planes de ejecución. on. Explica porque sucede esto. Y genera dos consultas donde demuestres lo explicado anteriormente. Incluye planes de ejecución obtenidos. 2. Seg´ un lo visto en la tercera actividad de la práctica un actica podemos tener control e indicar al planificador el orden de ejecución on de las reuniones (JOIN). ¿De qué nos sirve esto? 3. Crea una sentencia en SQL equivalente a la siguiente consulta pero que se ejecute de manera más as eficiente efici ente (sin crear crea r más as ´ındices ınd ices): ): SELECT SELECT * FROM FROM CUSTO CUSTOMER MER NATUR NATURAL AL JOIN JOIN ORDERS ORDERS NATURA NATURAL L JOIN JOIN LINEI LINEITEM TEM WHERE WHERE QUANTI QUANTITY TY = K ;

Donde K es un valor que tu elijas (no debe ser tal que la consulta resultante sea vac´ ac´ıa). Genera el plan de ejecución, on, comparalo con el de la consulta presentada y explica porque se dieron las mejoras (No olvides incluir en el reporte la configuración on del equipo, tanto de hardware como de software, en donde ejecutaste las pruebas) 6

4. Considera Considera la siguiente siguiente consulta: SELECT SELECT LINENUMB LINENUMBER ER FROM LINEITEM LINEITEM WHERE WHERE DISCOUNT DISCOUNT  ALL (SELECT (SELECT DISCOUNT DISCOUNT FROM LINEITEM LINEITEM); );

¿Qu´ ¿Q ué inf i nform ormac aci´ ión on se obtiene? obtiene? ¿Qu´ ¿Qué puedes puedes decir con respecto respecto a su desempe˜ desempeño? no? Analiz Analizaa los planes de ejecución on y genera una consulta que obtenga la misma información on pero con un mejor desempeño.Explica no.Explica el plan de ejecución on de tu nueva consulta y comparalo con la presentada aqu´ı. 5. Para cada una de las consultas de la Práctica actica 03 realiza el mismo análisis alisis que para el inciso anterior. 6. Considera Considera la siguiente siguiente consulta: SELECT SELECT NAME, NAME, ADDRESS ADDRESS FROM FROM CUSTO CUSTOMER MER CROSS CROSS JOIN JOIN ORDERS ORDERS CROSS CROSS JOIN JOIN LINEIT LINEITEM EM WHERE WHERE NAME NAME LIKE LIKE ‘%e’ ‘%e’ AND DISCOU DISCOUNT NT = K ;

Utiliza EXPLAIN junto con ANALYZE para determinar el plan de ejecución on y su tiempo estimado, modifica el orden de las tablas y los distintos algoritmos para la reunión on y genera el mejor plan de ejecución. on. K es un valor que se deja a tu elección on (no se permiten que la consulta resultante resultante sea se a vac´ıa). ıa ).

7

Entrega: Para la entrega, deberá incluirse el código odigo SQL, para las consultas tanto de las actividades como de los ejerci ejercicio cios, s, separa separando ndo en distin distintos tos subdire subdirecto ctorio rioss ambos ambos rumbro rumbross e indica indicando ndo en el nombre nombre del archiv archivos os de los script scriptss de forma clara y concis concisaa a que activid actividad ad o ejerc ejercici icioo corres correspond ponden. en. En una llamada Im´ agenes agenes estarán an contenidas las impresiones separadas por actividad en subdirectorios que demuestre demuestren n que efectiv efectivamen amente te se realizaron realizaron (traten (traten de que sean las menos posibles). posibles). En lo que respecta a los ejercicios, deberá entregarse un reporte en formato pdf exclusivamente y donde estarán las respuestas a las preguntas hechas as´ı como el código odigo de las consultas pedidas en cada inciso y la impresión on de pantalla pantalla que corrobore fueron ejecutadas ejecutadas.. Deberá justificarse cada respuesta e incluir las referencias referencias que se consultaron consultaron para resolverlas resolverlas.. No olviden olviden que las actividades actividades valen el treinta treinta por ciento de la calificación on y el resto los ejercicios.

Contacto: El correo electrónico onico al que pueden enviar sus dudas, comentarios y sugerencias es [email protected] de igual forma en el aula del laboratorio se atenderá cualquier inquietud sobre la práctica. actica.

Fecha de entrega: 25 de Septiembre de 2011 hasta las 11:59 pm

¡Suerte!

8

Practica 04 (1)

Recommend Documents