Transcript Presentación powerpoint

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:
 Examen del plan de ejecución.
 Forzar el uso de un índice determinado (with
(index (...))).
 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 (SP2).
Consultar la siguiente página para descargarlo:
http://www.microsoft.com/downloads/details.aspx?F
amilyID=d07219b2-1e23-49c8-8f0c63fa18f26d3a&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)CrearBDCredito.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.v9/MS.SQLSVR.v9.es/udb9/html/17d5daa1-8f1446e2-9cea-0ed520217d1e.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:
 Crear:
create index nombreÍndice on
tabla(atributos)
 Borrar:
drop index tabla.nombreÍndice
 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
15
g Arantza 15
e Inés 16
16
24
a Iker 8
m Roberto 28
© A. Jaime, C. Domínguez 2007
24
28
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
2
c
5
a
8
j
9
f
5
9
15
g
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
16
h
24
e
24
m
j Iker 8
m Roberto 28
© A. Jaime, C. Domínguez 2007
28
m
13
Plan de ejecución: Table Scan y
Clustered Index Scan
a
Table Scan
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
15
16
h
24
e
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
24
28
m
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
g
Recorrido raíz  hoja
en IP, IA, IS-c ó IS-nc
Puede recorrer varias
hojas a partir de la
encontrada
h
j
j
m
IP
DBD Laboratorios índices
© 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
Recorrido raíz  hoja
en IP, IA, IS-c ó IS-nc
Puede recorrer varias
hojas a partir de la
encontrada
DBD Laboratorios índices
g
15
16
h
15
16
h
24
e
j
m
9
15
g
g
h
j
5
j
m
IP
© A. Jaime, C. Domínguez 2007
8
28
24
28
m
17
Plan de ejecución: RID Lookup
(índices físicos)
2
j
c
Juan
Feli
5
2
h Ignacio 24
f Luisa 9
5
8
5
9
9
Index seek
15
Nested loops
(inner join)
15
g Arantza 15
e Inés 16
16
RID lookup
Representa
R2-ciclo simple
DBD Laboratorios índices
24
a Iker 8
m Roberto 28
© A. Jaime, C. Domínguez 2007
24
28
18
Plan de ejecución: Búsqueda de claves
(índices lógicos)
5
2
2
c
5
a
24
9
8
j
9
f
a
IP
c
c
e
f
f
a
c
e
f
5
9
Index seek
15
g
g
Nested loops
(inner join)
g
Búsqueda
de claves
g
h
j
f
g
Representa
R2-ciclo simple
DBD Laboratorios índices
16
h
24
e
h
j
j
m
15
15
16
j
m
© A. Jaime, C. Domínguez 2007
8
28
24
28
m
19
Comparación consulta simple
con índices lógicos
PK_cliente:

IS-nc(apellido1)
Index Seek + Busq. claves
1.408 bloques
apNomTfn:

clustered index scan
94 bloques
apellido1:

IP(id)
8 bloques
IS-nc(apellido1, apellido2, nombre, tfno)
Index Seek
ap1_ap2NomTfn:IS-nc(apellido1) en hojas (apellido2, nombre, tfno)

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
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
FROM
WHERE
AND
AND
id, nombre, region
cliente
nombre LIKE 'K%'
region IN ('La Rioja', 'País Vasco')
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:

48 bloques
Nombre:

IS-nc(nombre)
623 bloques
Region:

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)

21 bloques
NomReg:

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 apellido1 = '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

Con OR: NomApReg IS-nc(nombre,apellido1,region)
o

Con UNION: NomApReg y ApNomReg
o

53 bloques
13 bloques
Con UNION ALL: NomApReg y ApNomReg
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:
 Si sirve una solución con UNIÓN (mejor UNION ALL) usarla.
 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 (R1)
Nested Loops
/ Inner Join
Recorrido de
la tabla si no
tiene IP ni IA
Nested Loops
/ Inner Join
Representación de
ciclo único (R2)
DBD Laboratorios índices
© A. Jaime, C. Domínguez 2007
34
Comparación de consultas con JOIN
ing
Sin ningún IS:

17 ing + 64 ext = 81 bloques
Con IS en extranjera/foránea:

17 ing + 51 ext = 68 bloques
Con IS que incluyen
ext
ing
ext cliente
ing cantCli
WHERE y SELECT:

2 ing + 51 ext = 53 bloques
DBD Laboratorios índices
© 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.apellido1, 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
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:

Con un fichero intermedio hash (dir. calculado)

Recorriendo un índice/fichero ordenado
adecuadamente

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), apellido1
FROM
cliente
WHERE region='Aragon'
GROUP BY apellido1
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:


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)VistasMaterializadas.sql
Allí se analiza la misma consulta con agregados anterior:
SELECT cliente, SUM(cantidad) AS Total
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:

SCHEMABOUND: impide modificar la definición de la tabla base

COUNT_BIG en SELECT cuando hay funciones agregadas
Problema:

Puede afectar al rendimiento de INSERT / DELETE / UPDATE
sobre la tabla base
Ventajas:

El resultado de la consulta se mantiene continuamente
calculado

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.apellido1, avg(i.cantidad)
FROM cliente as c JOIN ingreso as i
ON c.id=i.cliente
WHERE c.region = 'Aragón'
GROUP BY c.id, c.apellido1
DBD Laboratorios índices
© A. Jaime, C. Domínguez 2007
48