Diseo de Bases de Datos Laboratorios Diseo fsico

Diseño de Bases de Datos Laboratorios Diseño físico con índices en SQL Server A. Jaime y C. Domínguez

Objetivos Ejecución de scripts con el SQLServer Management Studio: l Examen del plan de ejecución. l Forzar el uso de un índice determinado (with (index (. . . ))). l Estudio de índices interesantes: consulta simple, con and, con or, con join, con agregados y vistas materializadas. DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 2

Nota sobre SQL Server 2005 Para que funcione correctamente todo lo que se estudia en este laboratorio es necesario haber instalado el service pack 2 (SP 2). Consultar la siguiente página para descargarlo: http: //www. microsoft. com/downloads/details. aspx? F amily. ID=d 07219 b 2 -1 e 23 -49 c 8 -8 f 0 c 63 fa 18 f 26 d 3 a&displaylang=es DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 3

Crear la BD “Credito” Inicia SQL Server Management Studio Abre el fichero (1)Crear. BDCredito. sql y haz click en: . . . tardará un rato. ! Ejecutar 57 bloques 20. 000 filas 183 bloques 461 bloques 100. 000 filas DBD Laboratorios índices 10. 000 filas © A. Jaime, C. Domínguez 2007 17 bloques 3. 100 filas 4

Scripts T-SQL Disponemos de 6 scripts, numerados del 2 al 7, con ejemplos comentados (para facilitar el auto-estudio). En estas sesiones de laboratorio examinaremos estos ejemplos y realizaremos ejercicios similares. DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 5

Plan de ejecución y forzar el uso de índices concretos El plan de ejecución es un gráfico, y se muestra en una solapa seleccionando previamente consulta Incluir plan de ejecución real: Se fuerza el uso de índices concretos con with: FROM Cliente with (index (apellido)) DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 6

Sin WITH en FROM: resultado “óptimo” Sin WITH, el optimizador elige la que considera mejor opción entre las disponibles. Trata de tomar la de menor nº de lecturas lógicas. Para que el optimizador pueda elegir una buena estrategia debe disponer de índices interesantes. Esos índices NO los crea el propio optimizador sino el diseñador de BD (nosotros). Dicha tarea se conoce como diseño físico. DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 7

Conteo del nº de bloques El nº de bloques se ve en la solapa “Mensajes” como “lecturas lógicas”: Para verlos hay que ejecutar previamente la instrucción set statistics io on. SQL Server usa mucha memoria para disponer de gran parte de la BD y reducir transferencias desde/a disco. Las lecturas lógicas son en realidad en memoria (las físicas son las reales al disco) y nos sirven para comparar diferentes ejecuciones de la misma consulta. DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 8

Varios planes de ejecución juntos Seleccionar varias consultas (sin go) y ejecutarlas. En la ayuda on-line se explican todos los iconos de los planes de ejecución: ms-help: //MS. SQLCC. v 9/MS. SQLSVR. v 9. es/udb 9/html/17 d 5 daa 1 -8 f 1446 e 2 -9 cea-0 ed 520217 d 1 e. htm DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 9

Crear, modificar y borrar índices Usando la ventana de administrar índices y claves: Explorador de objetos Click en la BD y buscar la tabla click dcho en la tabla y elegir “diseño” icono “administrar índices y claves” Algunas instrucciones T-SQL para índices: l Crear: create index nombreÍndice on tabla(atributos) l Borrar: drop index tabla. nombreÍndice l Mostrar: exec sp_helpindex tabla DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 10

Ventana “administrar índices y claves” Columnas sobre las que se define el índice Nombre del índice Si es o no clave Si las filas quedarán ordenadas por las columnas del índice DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 Nombre del índice 11

Índices físicos en SQL Server Los índices secundarios de tablas sin índice “CLUSTERED” (agrupado), es decir sin IP ni IA, son índices normales o físicos, como el del ejemplo. Un índice CLUSTERED mantiene las filas ordenadas por los atributos que forman el índice (el del ejemplo NO es). Todo índice CLUSTERED es físico. Si definimos sobre la tabla un índice CLUSTERED (sólo puede tener uno) todos los índices secundarios se reconstruyen como índices lógicos (ver pagina siguiente). DBD Laboratorios índices 2 j c Juan Feli 5 2 h Ignacio 24 f Luisa 9 5 8 5 9 9 15 g Arantza 15 e Inés 16 15 16 24 a Iker 8 m Roberto 28 © A. Jaime, C. Domínguez 2007 28 24 12

Índices lógicos en SQL Server a El índice de la izda del dibujo es CLUSTERED ¿en qué se nota? Como la tabla tiene índice primario (clustered), sus IS son lógicos. c c e f f g DBD Laboratorios índices a c Juan Feli 5 2 e Ignacio 24 f Luisa 9 g 2 c 5 a 8 j 9 f 5 9 15 g 15 h El IS de la dcha del dibujo es lógico ¿en qué se distingue de los anteriores? Bloques con las filas de la tabla j g Arantza 15 h Inés 16 j m j Iker 8 m Roberto 28 © A. Jaime, C. Domínguez 2007 16 h 24 e 28 m 24 13

Plan de ejecución: Table Scan y Clustered Index Scan Table Scan a IP c Recorrido secuencial de tabla montón Clustered Index Scan c e f f a c 5 2 e f 24 9 g h 15 16 j m 8 28 g g h j Recorrido secuencial de tabla con IP o IA DBD Laboratorios índices j © A. Jaime, C. Domínguez 2007 m 14

Plan de ejecución: Index Scan a c Index Scan c e f f a c 5 2 2 c 5 a e f 24 9 8 j 9 f g g Recorrido de todas las hojas de un IS-c ó IS-nc j j m IP DBD Laboratorios índices 15 16 h m 9 15 g g h j 5 © A. Jaime, C. Domínguez 2007 8 28 15 16 h 24 e 28 m 24 15

Plan de ejecución: Clustered Index Seek a c Clustered Index Seek c e f f a c 5 2 e f 24 9 g h 15 16 j m 8 28 g Recorrido raíz hoja en IP, IA, IS-c ó IS-nc Puede recorrer varias hojas a partir de la encontrada DBD Laboratorios índices g h j j m IP © A. Jaime, C. Domínguez 2007 16

Plan de ejecución: Index Seek IS a c Index Seek c e f f a c 5 2 2 c 5 a e f 24 9 8 j 9 f g g Recorrido raíz hoja en IP, IA, IS-c ó IS-nc Puede recorrer varias hojas a partir de la encontrada DBD Laboratorios índices 15 16 h j m 9 15 g g h j 5 j m IP © A. Jaime, C. Domínguez 2007 8 28 15 16 h 24 e 28 m 24 17

Plan de ejecución: RID Lookup (índices físicos) 2 j c Juan Feli 5 2 h Ignacio 24 f Luisa 9 Index seek g Arantza 15 e Inés 16 RID lookup DBD Laboratorios índices 8 5 9 9 15 Nested loops (inner join) Representa R 2 -ciclo simple 5 15 16 24 a Iker 8 m Roberto 28 © A. Jaime, C. Domínguez 2007 28 24 18

Plan de ejecución: Búsqueda de claves (índices lógicos) a IP c c e f f a c e f 5 2 2 c 5 a 24 9 8 j 9 f Index seek g Búsqueda de claves Representa R 2 -ciclo simple DBD Laboratorios índices j f g g h 15 16 j m 8 28 h j j m 9 15 g g Nested loops (inner join) 5 © A. Jaime, C. Domínguez 2007 15 16 h 24 e 28 m 24 19

Comparación consulta simple con índices lógicos PK_cliente: l 94 bloques apellido 1: l clustered index scan IS-nc(apellido 1) 1. 408 bloques ap. Nom. Tfn: l IP(id) 8 bloques Index Seek + Busq. claves IS-nc(apellido 1, apellido 2, nombre, tfno) Index Seek ap 1_ap 2 Nom. Tfn: IS-nc(apellido 1) en hojas (apellido 2, nombre, tfno) l 8 bloques DBD Laboratorios índices Index Seek © A. Jaime, C. Domínguez 2007 20

Índices interesantes para consultas simples La mejor opción es un índice que permita resolver la consulta recorriendo SÓLO nodos del índice (sin visitar las filas de la tabla). Conviene que el índice incluya TODOS los atributos de WHERE seguidos de los de SELECT. Los de SELECT pueden situarse sólo en las hojas con la opción include de create index DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 21

Ejercicio: consulta simple Elimina todos los índices que pueda tener la tabla cargo salvo el primario (PK_cargo). Crea índices interesantes para la siguiente consulta: SELECT cliente, id, fecha cliente FROM cargo WHERE cliente BETWEEN 6000 and 7000 ¿Puedes bajar de 32 lecturas lógicas? DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 22

Consultas con AND Abre el segundo script: (3)AND. sql Allí se analiza la siguiente consulta sobre la tabla cliente: SELECT id, nombre, region FROM cliente WHERE nombre LIKE 'K%' AND region IN ('La Rioja', 'País Vasco') AND id > 5000 Sigue los pasos del script. Trata de entender qué ocurre en cada momento. Qué índice da mejores resultados y por qué DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 23

Iconos nuevos Hash Match / Inner Join DBD Laboratorios índices Filter © A. Jaime, C. Domínguez 2007 24

Comparación 1ª consulta con AND PK_cliente: l 48 bloques Nombre: l IS-nc(nombre) 623 bloques Region: l IP(id) SELECT id, nombre, region FROM cliente WHERE nombre LIKE 'K%‘ AND region IN('La Rioja', 'País Vasco') AND id > 5000 IS-nc(region) 6. 246 bloques Nombre+Region: IS-nc(nombre) + IS-nc(region) l 21 bloques Nom. Reg: l IS-nc(nombre, region) 4 bloques DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 25

Índices interesantes para AND Cada subcondición añadida con AND va limitando el resultado. Interesa empezar con un índice sobre una columna de WHERE que obtenga pocas tuplas (selectiva). Si hay un índice así, puede valernos. Si no, se intenta con varias columnas de WHERE que obtengan pocas tuplas. No tienen por qué ser todas. Si no hay nada selectivo probar a cubrir la consulta (todos los atributos de WHERE y SELECT) DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 26

Ejercicio: consulta con AND Crea índices interesantes para la siguiente consulta: SELECT cliente, id, fecha FROM cargo WHERE cliente BETWEEN 6000 and 7000 AND cantidad BETWEEN 500 and 800 ¿Puedes bajar de 43 lecturas lógicas (no 44, sino 43)? DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 27

Consultas con OR Abre el tercer script: (4)OR. sql Allí se analiza la siguiente consulta y otras similares: SELECT nombre, apellido, region, id FROM cliente WHERE nombre = 'Pepe' OR apellido 1 = 'Pérez' Sigue los pasos del script. Cuál de los índices da mejores resultados en cada caso y por qué DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 28

Iconos nuevos Sort/Distinct Merge Join/Union DBD Laboratorios índices Concatenation © A. Jaime, C. Domínguez 2007 29

Comparación consulta con OR l Con OR: Nom. Ap. Reg IS-nc(nombre, apellido 1, region) o l Con UNION: Nom. Ap. Reg y Ap. Nom. Reg o l 53 bloques 13 bloques Con UNION ALL: Nom. Ap. Reg y Ap. Nom. Reg o 13 bloques DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 30

Índices interesantes para OR Una fila forma parte del resultado si una subcondición cualquiera del WHERE es cierta OR es similar a UNIÓN: l Si sirve una solución con UNIÓN (mejor UNION ALL) usarla. l Si SELECT incluye un identificador de fila (como la clave primaria), entonces OR y UNIÓN son equivalentes. Si no, pueden dar soluciones diferentes. Interesa encontrar índices útiles para cada subconsulta del WHERE (de las unidas con OR) Si nada va bien podemos buscar índices interesantes para toda la condición del WHERE Ir probando hasta dar con una solución satisfactoria DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 31

Ejercicio: consulta con OR Crea índices interesantes y, si es conveniente, transforma la siguiente consulta: SELECT id, cantidad, extracto, cliente FROM cargo WHERE cantidad > 975 OR extracto = 20000 ¿Puedes bajar de 326 lecturas lógicas con or? ¿y de 44 con union? DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 32

Sesión 2 Consultas con JOIN Abre el quinto script: (5)JOIN. sql Allí se analizan la siguiente consulta con JOIN. SELECT i. id, e. id FROM ingreso as i JOIN extracto as e ON i. cliente=e. cliente WHERE i. cantidad between 1000 and 2000 DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 33

Plan de ejecución: Iconos nuevos Representación de ciclo anidado (R 1) Nested Loops / Inner Join Recorrido de la tabla si no tiene IP ni IA Nested Loops / Inner Join Representación de ciclo único (R 2) DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 34

Comparación de consultas con JOIN ing Sin ningún IS: l 17 ing + 64 ext = 81 bloques Con IS en extranjera/foránea: l 17 ing + 51 ext = 68 bloques Con IS que incluyen 2 ing + 51 ext = 53 bloques DBD Laboratorios índices ing ext cliente ing cant. Cli WHERE y SELECT: l ext © A. Jaime, C. Domínguez 2007 ext cliente 35

Índices interesantes para JOIN Crear un índice para cada clave primaria y uno para cada clave foránea/extranjera de los JOIN. Crear índices adecuados para la condición WHERE (igual que en las consultas sobre una tabla). Probar a crear un índice en cada tabla que incluya sus atributos de WHERE y JOIN. Crearlo en ambos órdenes (primero los de WHERE y primero los de JOIN). Probar a añadir a los últimos índices creados, además de los atributos de WHERE y JOIN, los de SELECT. Si hay más de un JOIN, analizar el que suponga mayor costo. Si no es suficiente estudiar el siguiente más costoso. Continuar estudiando JOIN hasta encontrar una solución satisfactoria. DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 36

Ejercicio: consulta con JOIN Crea índices interesantes para la siguiente consulta que se encuentra al final del script. Antes haz la siguiente modificación sobre la tabla cliente: UPDATE cliente SET region = 'Aragón' WHERE id%19=0 or id%21=0 or id%22=0 SELECT c. id, c. apellido 1, k. cantidad FROM cliente as c JOIN cargo as k ON c. id=k. cliente WHERE c. region = 'Aragón' ¿Qué algoritmo utiliza? ¿Puedes bajar de 236 + 11 bloques? DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 37

Consultas con agregados (como SUM) Abre el sexto script: (6)Agregadas. sql Allí se analiza la siguiente consulta sobre la tabla cargo: SELECT cliente, SUM(cantidad) AS Total SUM FROM cargo GROUP BY cliente ORDER BY cliente Sigue los pasos del script. Cuál de los índices da mejores resultados y por qué DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 38

Plan de ejecución: Iconos nuevos Stream aggregate / Aggregate DBD Laboratorios índices Cálculo de SUM, AVG, . . aprovechando el orden del recorrido © A. Jaime, C. Domínguez 2007 39

Comparación consulta con SUM Sólo con IP: 461 bloques IS(cantidad, cliente): 237 bloques Con IS(cliente, cantidad): 237 bloques DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 40

Índices interesantes para agregados (como SUM) Se calcula de dos formas: l Con un fichero intermedio hash (dir. calculado) l Recorriendo un índice/fichero ordenado adecuadamente l Conviene usar la 2ª opción para evitar el coste de crear el fichero intermedio Interesa crear índices que incluyan a los atributos de GROUP BY, a los de WHERE y a los de SELECT (sobre los que se aplica la función, por ejemplo SUM) DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 41

Ejercicio: consulta con agregados Crea índices consulta: interesantes para la siguiente SELECT MAX(saldo), apellido 1 FROM cliente WHERE region='Aragon' GROUP BY apellido 1 ORDER BY MAX(saldo) ¿Puedes bajar de un total de 9 bloques? DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 42

Cómo crear vistas (vistas normales, no materializadas) Como vimos en el primer laboratorio, es posible crear vistas: l l Con Microsoft SQL Management Studio: en Vistas Nueva Vista. Se abre el asistente para creación de vistas que coincide con el QBE de las consultas. Con el analizador de consultas: usando la sintaxis de SQL create view. . . Una vista puede entenderse como una tabla virtual o como una consulta almacenada. DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 43

Uso de vistas materializadas (vistas indexadas de SQL Server) Abre el sexto script: (7)Vistas. Materializadas. sql Allí se analiza la misma consulta con agregados anterior: SELECT cliente, SUM(cantidad) AS Total SUM FROM cargo GROUP BY cliente ORDER BY cliente Sigue los pasos del script. Cómo se consigue ahora el mejor resultado y por qué DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 44

Comparación: sólo índices frente a vistas materializadas Sin vista: 237 bloques Recorrido del índice anterior Con vista indexada (materializada): 25 bloques Recorrido de la vista indexada DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 45

Uso de vistas materializadas (indexed views) Sólo podemos utilizarlas con: con l SCHEMABOUND: impide l COUNT_BIG modificar la definición de la tabla base en SELECT cuando hay funciones agregadas Problema: Problema l Puede afectar al rendimiento de INSERT / DELETE / UPDATE sobre la tabla base Ventajas: l El resultado de la consulta se mantiene continuamente calculado l Se reduce el uso de Cache (ver ejemplos de ampliación) DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 46

Ejercicio: uso de vistas materializadas Crear una vista indexada para la siguiente consulta. Comparar con el mejor resultado logrado en (4)JOIN. sql (53) SELECT i. id, e. id FROM dbo. ingreso as i JOIN dbo. extracto as e ON i. cliente=e. cliente WHERE i. cantidad between 1000 and 2000 Ayuda: construir el índice sobre i. id y e. id (columnas del select). ¿Te da 2 bloques? (frente a 53: mejor opción con índices). DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 47

Ejercicios: mezclando cosas Aporta soluciones tanto con vistas materializadas como sólo con índices: SELECT c. id, e. id FROM cargo as c JOIN extracto as e ON c. cliente=e. cliente WHERE c. cantidad=1000 OR year(e. fecha) < 2003 SELECT c. id, c. apellido 1, avg(i. cantidad) avg FROM cliente as c JOIN ingreso as i ON c. id=i. cliente WHERE c. region = 'Aragón' GROUP BY c. id, c. apellido 1 DBD Laboratorios índices © A. Jaime, C. Domínguez 2007 48