diseño físico y rendimiento de la bd2
Post on 26-Jun-2015
669 Views
Preview:
TRANSCRIPT
Base de Datos
Profesor:
MSC Luis Serna Jherry
Diseño Físico
Recomendaciones en el modelo ER
Diseño físico de la BDImplementación y Ajuste
Optimización del rendimiento
Recomendaciones en Modelo ER
Denominación adecuada y definición de todas las entidades (tablas) como singulares y no plurales.
El nombre de la entidad (tabla) debe ser descriptible por si solo.
Denominación única de acuerdo al estándar de todos los atributos (campos) y definición apropiada de los principales, dentro de cada entidad.
Frase verbal (única) que denomine cada relación. Asignación adecuada de dominios (validaciones, valores
por omisión). Establecimiento de soporte para nulos en campos no PK. Asignación adecuada de integridad referencial. Creación de índices únicos (AK) y no únicos (IE)
necesarios. Solución del problema por lo menos en 3FN.
Diseño físico de la BD Es el proceso de elegir estructuras de
almacenamiento y caminos de acceso específicos para que los ficheros de la BD tengan buen rendimiento con las aplicaciones:
Organización de ficheros y caminos de acceso Diversos tipos de indexación Agrupación de registros relacionados en bloques de
disco Enlace de registros relacionados mediante apuntadores Técnicas de dispersión
Diseño Físico de la BD- Criterios a considerar -
Tiempo de respuesta: el que transcurre entre la introducción de una transacción y la obtención de la respuesta
Tiempo de acceso a la BD para obtener los elementos de información (bajo el control del DBMS)
Carga del sistema, tareas del SO y comunicación Aprovechamiento del espacio: cantidad de espacio
que ocupan los ficheros y sus estructuras de acceso (índices)
Productividad de las transacciones: número medio de transacciones que la BD puede procesar por minuto
Medido en las condiciones pico para el sistema
Análisis de consultas y transacciones Para elaborar el diseño físico de la base
de datos debemos tener una idea clara del uso que se le va a dar, definiendo a alto nivel las transacciones y consultas que se espera ejecutar en ella.
Diseño Físico de la BD- Criterios a considerar -
Análisis de Consultas y Transacciones
Para cada consulta establecer:a. Las tablas a las que accederáb. Los atributos sobre los que se especificarán
condiciones de selección (WHERE)c. Los atributos sobre los que se especificarán
condiciones de reunión o de enlace de tablasd. Los atributos cuyos valores se obtendrá en la
consulta
Los atributos de los incisos b y c son candidatos a constituir índices (estructuras de acceso)
Análisis de Consultas y Transacciones
Para cada transacción de actualización establecer:
a. Las tablas que actualizaráb. El tipo de operación en cada tabla (insertar, modificar o
eliminar)c. Los campos sobre los que se especificarán condiciones de
selección para operaciones de eliminación o modificaciónd. Los campos cuyos valores alterará una operación de
modificación
Los campos del inciso c son candidatos para índices Los campos del inciso d son candidatos a evitar en los
índices, ya que su modificación requerirá la actualización de estas estructuras de acceso.
Create Index CREATE UNIQUE INDEX index_name
ON table_name (column_name)
CREATE INDEX index_nameON table_name (column_name1,
column_name 2…)
CREATE INDEX idx_address_district ON Address (district);
Diseño físico de la BD El rendimiento de la BD depende del tamaño y
del número de registros que contienen los ficheros:
Estimación de estos valores para cada fichero Considerar el crecimiento esperado de cada uno
Se debe estimar los patrones de actualización y obtención de datos del fichero para todas las transacciones en conjunto.
Considerar la construcción de caminos de acceso primarios e índices secundarios para los atributos con los que se suelen seleccionar los registros.
Implementación y Ajuste Creación del esquema de la BD, con los ficheros vacíos Carga de datos (poblado de tablas)
Rutinas de conversión para migrar datos desde una versión anterior
Implementación de las transacciones Codificación de programas con instrucciones DML incrustadas Prueba de programas
Monitoreo del rendimiento en producción: Estadísticas sobre el número de invocaciones a las
transacciones o consultas predefinidas Actividades de entrada / salida sobre ficheros Conteo de páginas de ficheros o registros de índices Frecuencia de utilización de los índices
Optimización del rendimiento
Ajuste de índices Evaluar dinámicamente los requerimientos, que
pueden cambiar según época del año, día del mes o de la semana
Reorganizar los índices para obtener mejor rendimiento Ciertas consultas pueden tardar mucho en ejecutarse por
falta de un índice apropiado Puede haber índices que no se utilicen Puede haber índices que originen trabajo adicional por
estar definidos sobre atributos que sufren continuos cambios
Optimización del rendimiento
Ajuste de consultasIndicadores: Demasiados accesos al disco (por
ejemplo una consulta de emparejamiento exacto que recorre una tabla completa)
El plan de ejecución de consulta muestra que no se están usando los índices relevantes.
= >, < >=, <= LIKE <> Siempre mejor es operar sobre números
que sobre cadenas.
Ajuste de Consultas – Eficiencia de operadores -
Ajuste de Consultas - Casos
1. Muchos optimizadores no usan índices en presencia de:
Expresiones aritméticas SALARIO/365 > 10.50
Comparaciones numéricas de campos de diferente tamaño y precisión
ACANT = BCANT donde ACANT es de tipo Integer y BCANT es Smallinteger
Comparaciones con NULL FECHA IS NULL
Comparaciones de subcadenas APELLIDO LIKE ‘%EZ’
Ajuste de Consultas - Casos
2. Los índices podrían no usarse en consultas anidadas que utilizan IN:SELECT NSS FROM EMPLEADOWHERE DNO IN (SELECT DNUMERO
FROM DEPARTAMENTOWHERE NSS_JEFE = ‘3334444’)
Puede no utilizar el índice definido sobre DNO en EMPLEADO, mientras que la utilización de DNO = DNUMERO en la cláusula WHERE con una consulta de un solo bloque puede ocasionar que el índice sí se utilice.
Ajuste de Consultas - Casos
3. Algunos DISTINCT pueden ser redundantes y podrían evitarse sin modificar el resultado. Un DISTINCT generalmente provoca una operación de clasificación y debe evitarse siempre que sea posible
Ajuste de Consultas - Casos
4. El uso innecesario de tablas temporales puede evitarse juntando varias consultas en una sola, a menos que la relación temporal sea necesaria para algún resultado intermedio
Ajuste de Consultas - Casos
5. En algunas situaciones en las que se usa consultas correlacionadas son útiles las tablas temporales SELECT NSSFROM EMPLEADO EWHERE SALARIO = SELECT MAX(SALARIO)
FROM EMPLEADO AS MWHERE M.DNO = E.DNO)
Esto tiene el peligro potencial de buscar en toda la tabla M EMPLEADO interna para cada tupla de E
EMPLEADO externa.
Ajuste de Consultas - Casos
Para hacerlo más eficiente puede descomponerse en dos consultas, la primera de las cuales calcula el salario máximo de cada departamento:SELECT MAX(SALARIO) AS SALARIO_MAYOR, DNO INTO TEMPFROM EMPLEADOGROUP BY DNO; SELECT NSSFROM EMPLEADO, TEMPWHERE SALARIO = SALARIO_MAYOR AND EMPLEADO.DNO = TEMP.DNO
Ajuste de Consultas - Casos
6. De haber varias opciones posibles para la condición de reunión, elegir una que use un índice de agrupación (CLUSTER), y evitar aquellas que contengan comparaciones de cadenas:
Aún si el campo NOMBRE fuera una clave candidata tanto en EMPLEADO como en ALUMNO, es mejor usar
EMPLEADO.NSS = ALUMNO.NSS como condición de reunión, en lugar de
EMPLEADO.NOMBRE = ALUMNO.NOMBRE si NSS tiene un índice de agrupación en una o en ambas tablas.
Ajuste de Consultas - Casos
7. En algunos optimizadores de consultas el orden en el que aparecen las tablas en el FROM puede afectar el procesamiento de la reunión. En esos casos debe cambiarse el orden
para que procese primero la tabla con menos data, y la más grande se use con el índice correspondiente
Ajuste de Consultas - Casos
8. Algunos optimizadores dan peores tiempos con consultas anidadas que con sus equivalentes no anidadas. Hay 4 tipos de consultas anidadas:a) Subconsultas no correlacionadas con agregados
en la consulta interna b) Subconsultas no correlacionadas sin agregadosc) Subconsultas correlacionadas con agregados en
la consulta interna d) Subconsultas correlacionadas sin agregados
Ajuste de Consultas - Casos
a) Este tipo rara vez presenta problemas, porque la consulta interna se evalúa una sola vez
b) En este tipo se puede presentar el problema mostrado en el caso # 2, en el que no se usa el índice sobre DNO en EMPLEADOSELECT NSS FROM EMPLEADOWHERE DNO IN (SELECT DNUMERO
FROM DEPARTAMENTO
WHERE NSS_JEFE = ‘3334444’)
La transformación de subconsultas correlacionadas puede llevar a que se creen tablas temporales.
Ajuste de Consultas - Casos
9. Muchas aplicaciones se basan en vistas que definen los datos de interés para las aplicaciones.
A veces estas vistas pueden ser excesivas cuando la consulta puede realizarse directamente sobre la tabla base, en lugar de usar una vista que se ha definido sobre una reunión
Ajuste de Consultas - Casos
10. Una consulta con varias condiciones OR puede hacer que no se empleen los índices que existen:
SELECT NOMBRE, APELLIDO, SALARIO, EDADFROM EMPLEADOWHERE EDAD > 45 OR SALARIO < 5000
Alternativa:SELECT NOMBRE, APELLIDO, SALARIO, EDADFROM EMPLEADOWHERE EDAD > 45UNIONSELECT NOMBRE, APELLIDO, SALARIO, EDADFROM EMPLEADOWHERE SALARIO < 5000
Puede usar los índices definidos sobre SALARIO y sobre EDAD
Ajuste de Consultas - Casos
11. Las condiciones WHERE pueden reescribirse de modo que se utilicen índices por varias columnas:
SELECT REGION, TIPO_PROD, MES, VENTASFROM ESTADISTICA_VENTASWHERE REGION = 3 AND ((TIPO_PROD BETWEEN 1 AND 3) OR
(TIPO_PROD BETWEEN 8 AND 10))Puede usar un índice únicamente sobre REGION y debe buscar a través de todas las
páginas hoja del índice un emparejamiento con TIPO_PROD.
En cambio:SELECT REGION, TIPO_PROD, MES, VENTASFROM ESTADISTICA_VENTASWHERE (REGION = 3 AND (TIPO_PROD BETWEEN 1 AND 3)) OR (REGION
= 3 AND (TIPO_PROD BETWEEN 8 AND 10)) Puede usar un índice compuesto sobre (REGION, TIPO_PROD) y trabajará mucho
más eficientemente.
Ajuste del Diseño de la BD
1. Reunir tablas existentes, porque ciertos campos de dos o más tablas se necesitan juntos con frecuencia: pasar de FNBC a 3FN, 2FN ó 1FN (¡¡¡¡¡¡¡)
2. Para un cierto conjunto de tablas, elegir uno de entre varios diseños alternativos en la misma forma normal
3. Fragmentación vertical: una tabla de la forma R(k, a, b, c, d, …)
puede reemplazarse por varias tablas como R1(k, a, b), R2(k, c, d) y R3(k, …)
(Según la necesidad de acceso conjunto a los campos)
Ajuste del Diseño de la BD
4. Fragmentación horizontal: almacenar fragmentos horizontales de una tabla en tablas diferentes. Si se desea acceder a todos los datos la consulta debe combinarlas nuevamente.
5. Repetir uno o más campos de una tabla en otra, aún creando redundancia y anomalías potenciales. En este caso debe haber siempre una tabla principal donde el campo esté correctamente actualizado con absoluta seguridad.
RESUMEN El diseño conceptual es una descripción estable,
muy expresiva y general del contenido de la BD, que es independiente del DBMS
El diseño físico empieza por la elección del DBMS y está fuertemente marcado por éste.
El adecuado rendimiento de la BD depende en gran medida de las condiciones de implementación propias de cada instalación: volúmenes de datos, tiempos, carga de trabajo, etc.
El punto de partida para conseguir una BD eficiente es, siempre, un adecuado diseño conceptual.
top related