AA9-Ev4 Optimización de la Base de Datos Carlos Antonio Espinosa Orozco CC No. 77.033.890 Cómo optimizar una base de datos mediante el Asistente para la optimización de motor de base de datos La optimización del acceso a los datos es vital para el tiempo de carga de la página, debido a que suele ser el factor que más afecta al tiempo que tiene que esperar el navegador para recibir el HTML. Este tiempo de espera es muy importante, ya que el resto de recursos de la página (imágenes, scripts y hojas de estilo), no se empiezan a bajar hasta que el navegador no lee el HTML desde el que se hace referencia a estos recursos. Este tipo de optimización es probablemente la más compleja de todas, en primer lugar porque depende de dos factores variables en el tiempo: por un lado, de cómo y de qué tipo son las consultas que se van a realizar y, por otro, de la carga de trabajo t rabajo que tenga que soportar el servidor o servidores. En segundo lugar por la gran cantidad de conocimientos que hay que tener para saber reescribir consultas, reescribir el código que ejecuta las consultas, crear índices, vistas materializadas, particiones horizontales y verticales, réplicas, tablas de apoyo, saber elegir los tipos de datos a usar, saber optimizar el esquema sin perder la lógica del modelo de negocio, saber ajustar los parámetros de configuración del SGBD, conocer y saber usar sistemas de caché externos. Además cada uno de estos puntos, de los que hay muchos libros escritos, se debe abordar de forma distinta dependiendo de qué SGBD
SQL Server 2008 R2 Puede utilizar la GUI (interfaz gráfica de usuario) usua rio) del Asistente para la optimización de motor de base de datos para optimizar bases de datos mediante tablas o archivos de carga de trabajo. La GUI del Asistente para la optimización de motor de base de datos le permite ver con facilidad los resultados de la sesión de optimización actual y los resultados de sesiones anteriores. Para obtener más información, Para optimizar una base de datos mediante una tabla o archivo de carga de trabajo como entrada 1. Determine las características de la base de datos (índices, vistas indizadas y particiones) que desea que el Asistente para la optimización de motor de base de datos tenga en cuenta para agregarlas, quitarlas o mantenerlas durante el
2. 3.
4. 5.
análisis. Para obtener más información, vea Acerca de las cargas de trabajo y Consideraciones para usar el Asistente para la optimización de motor de base de datos. Cree una carga de trabajo. Para obtener más información, vea Cómo crear cargas de trabajo Inicie el Asistente para la optimización de motor de base de datos e inicie sesión en una instancia de Microsoft SQL Server. Para obtener más información, vea Iniciar el Asistente para la optimización de motor de base de datos. En la ficha General, escriba un nombre en Nombre de sesión para crear una nueva sesión de optimización. Seleccione Archivo de carga de trabajo o Tabla y escriba la ruta de acceso al archivo o el nombre de la tabla en el cuadro de texto adyacente.
El formato para especificar una tabla es: database_name.schema_name.table_name
1. Para buscar una tabla o archivo de carga de trabajo, haga clic en el botón Examinar. El Asistente para la optimización de motor de base de datos presupone que los archivos de carga de trabajo son archivos de sustitución incremental. Para obtener más información acerca de los archivos de sustitución incremental, Al usar una tabla de traza como una carga de trabajo, esa tabla debe existir en el mismo servidor que el Asistente para la optimización de motor de base de datos está optimizando. Si crea una tabla de traza en otro servidor, muévala al servidor en el que el Asistente para la optimización de motor de base de datos está realizando la optimización antes de utilizarla como carga de trabajo. 2. Seleccione las bases de datos y tablas en las que desea ejecutar la carga de trabajo seleccionada en el paso 5. Para seleccionar las tablas, haga clic en la flecha Tablas seleccionadas. 3. Seleccione Guardar registro de optimización para guardar una copia del registro de optimización. Desactive la casilla de verificación si no desea guardar una copia del registro de optimización. Puede ver el registro de optimización después del análisis; para ello, abra la sesión y seleccione la ficha Progreso. 4. Haga clic en la ficha Opciones de optimización y seleccione las opciones que figuran en la lista. Para obtener más información, vea Opciones de optimización disponibles. 5. Haga clic en el botón Iniciar análisis de la barra de herramientas. Si desea detener la sesión de optimización una vez iniciada, en el menú Acciones seleccione una de las siguientes opciones:
Detener análisis (Con recomendaciones) detiene la sesión de optimización y pregunta al usuario si desea que el Asistente para la optimización de motor de base de datos genere recomendaciones basadas en el análisis realizado hasta este punto. Detener análisis detiene la sesión de optimización sin generar ninguna o recomendación. Optimización de consultas o
Para finalizar, algunos breves consejos para optimizar consultas:
Cambiar los OR por IN, cuando tenemos más de un valor para comparar. Minimizar el coste de los JOIN: La concatenación natural o JOIN es la operación más costosa de las bases de datos relaciones, ya que requiere realizar una multiplicación cartesiana y una selección de valores. Algunas técnicas que podemos usar para minimizar su efecto consisten en: Reordenarlos para concatenar primero las relaciones con menos filas para reducir el número de cruces. Crear subconsultas en donde se filtren o limiten el número de filas de las relaciones grandes antes de realizar los siguientes JOINs. A veces, dividir una consulta en varias, es mejor que hacerlo todo con una sola consulta, de forma que podemos obtener en una primera consulta unos pocos identificadores que podemos pasar con un IN a la siguiente consulta, en lugar de realizar un JOIN. Cambiar los JOIN por EXISTS si no se va a mostrar ningún dato de la relación con la que se realiza el cruce. Tener en cuenta el problema del N + 1: El n+1 se produce normalmente cuando tenemos un listado en el que para mostrarlo como queremos, por cada ítem necesitamos realizar una consulta adicional (el más uno del n+1). En este caso, suele ser mejor realizar uno o varios JOIN adicionales, en la consulta que recupera el listado de ítems. De esta forma obtenemos el listado tal y como lo necesitamos, y no se tienen que lanzar consultas adicionales para cada ítem. Especificar siempre los nombres de las columnas en las SELECT, si no el SGBD leerá todas las filas del disco. El asterisco se debe usar sí y solo sí se utiliza COUNT, en cuyo caso el SGBD sabrá que no tiene que leer todas las columnas. Crear índices: los índices permiten un acceso a los datos no secuencial mucho más rápido, pero son costosos de crear, así que no es conveniente su uso si tenemos muchas más lecturas que escrituras. Debemos analizar el plan de ejecución de las consultas (cada SGBD tiene su manera de verlo) para saber donde debemos crear índices. Normalmente, crearemos los índices en claves ajenas y en las columnas que se usen
con ORDER BY o WHERE. Si se crean índices compuestos, se deben poner las columnas en el mismo orden que se vayan a usar en las consultas
Cachear las consultas más frecuentes Activar la caché de base de datos del SGBD a veces puede empeorar el rendimiento. Esto depende del volumen de datos al que se acceda más frecuentemente. Si el volumen de datos es alto y se actualiza frecuentemente, estaremos siempre escribiendo en la caché en lugar de leer de ella. Así que normalmente es preferible recurrir a un sistema de caché externo, en el cual podamos controlar que se van a cachear realmente los datos más frecuentes y más frescos. Si el sistema de caché no nos provee de esta funcionalidad, podemos implementarla nosotros. Una buena forma de hacerlo sin el sobrecoste de almacenar el número de veces que se accede a una consulta, es generar un número aleatorio y cachear la consulta si ese número pasa de cierto valor. Por ejemplo, supongamos que generamos un número aleatorio entre 1 y 100 y la consulta se cachea si dicho número es menor que 10, de esta forma se guardará la consulta en caché con una probabilidad del 10% en cada petición, así si la consulta tiene más peticiones, será más probable que se guarde en caché.
Optimizar la paginación La paginación suele ser una tarea costosa cuando tenemos que mostrar varios números de página, porque para eso se tiene que calcular el número de filas en la relación, lo que puede requerir una lectura completa de la misma, dependiendo del SGBD. Siempre será más eficiente mostrar enlaces de anterior y siguiente, recuperando todas las filas a mostrar y una más, de manera que se muestra el enlace de siguiente página, si nos llega esta fila adicional desde la BD. Alternativamente, podemos mantener en una relación independiente el número de filas. Otra cosa que puede afectar a las paginaciones, es que cuando estamos en una página interior se deben descartar un cierto número de filas, pertenecientes a las páginas anteriores, y que son leídas del disco con todas las columnas que aparezcan en la consulta. Una forma de evitar esto es realizar un JOIN con una subconsulta que recupere los identificadores que necesitamos antes de leer toda la información. Ejemplo: SELECT dato1, dato2, dato3 FROM Foo INNER JOIN( SELECT id FROM Foo ORDER BY dato1 LIMIT 50,10) AS FooPaginado USING(id)
