diseño físico y rendimiento de la bd2

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


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


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’


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.


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


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


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.


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


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.


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


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


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.


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


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


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.

diseño físico y rendimiento de la bd2

Design