Pasos en el procesamiento de una consulta

10. Sistemas de gestión de BD

10. Sistemas de gestión de BD




 

En este capítulo se profundiza sobre el sistema de gestión de una base de datos. Se tratarán conceptos relativos a la administración de una base de datos como son la restauración, monitorización, benchmarking, planificación de consultas, importación, exportación, etc.

 

Cualquier administrador de base de datos o persona que en un futuro quiera administrar una base de datos tiene que tener muy claros los conceptos de este capítulo y profundizar en cada uno de sus apartados para poder gestionar una base de datos de forma eficiente y efectiva.

 

10.1 Funcionalidades y objetivos.

 

Las bases de datos son una herramienta que se ha convertido en fundamental para muchos sistemas. De hecho la mayoría de sistemas están desarrollados a partir de una base de datos.

 

Múltiples dispositivos utilizan bases de datos tanto internas como externas a ellos, la práctica totalidad de los sitios web utilizan bases de datos, cualquier sistema de información utiliza una o varias bases de datos, etc. Actualmente no se concibe un sistema sin una base de datos detrás de él.

 

Existen múltiples gestores de bases de datos cada uno con una tecnología diferente pero todos comparten una misma filosofía: la base de datos es un conjunto de datos que además refleja las relaciones existentes entre ellos. Cualquier base de datos se diseña con un objetivo concreto y está organizada de una forma lógica. Integridad y seguridad son características de cualquier base de datos.

 

10.2 Arquitectura funcional.

 

Dentro de cualquier arquitectura de una base de datos se pueden distinguir siempre tres factores comunes:

 

  • Se separan siempre los datos de los programas de aplicación. Esa es la esencia de una base de datos. Independiente de una aplicación existe una base de datos la cual podría estar en cualquier tipo de sistema (Oracle, MySQL, SQL Server, etc).

  • Toda la información de una base de datos se almacena en un repositorio común llamado catálogo. El catálogo es una base de datos en sí misma. También se denomina al catálogo metadatos. En dichos metadatos se almacenan todos los esquemas de bases de datos existentes en el sistema como son las tablas, los usuarios, índices, privilegios, etc. Los administradores o incluso algunos usuarios con privilegios pueden realizar consultas y actualizaciones al catálogo con lo cual se pueden realizar operaciones de transformación de la base de datos de una manera eficiente.

  • Se establecen múltiples vistas de usuario las cuales son independientes unas de otras. Cada usuario tiene una vista de la base de datos que es la que necesita ocultándose al mismo el resto de la base de datos. Obviamente un administrador de bases de datos o DBA tiene acceso al conjunto completo de datos.

10.3 El componente de procesamiento de peticiones:

 

El procesamiento de una consulta sigue una serie de pasos los cuales muchas veces dependen del gestor de bases de datos. En la siguiente figura se puede analizar los distintos pasos por los que pasa una consulta hasta ser ejecutada:

Pasos en el procesamiento de una consulta

Figura 10.1 Pasos en el procesamiento de una consulta.

 

Generalmente los gestores (al igual que otras herramientas como los compiladores) utilizan un código intermedio el cual es más sencillo de tratar para lograr una optimización y una evaluación o ejecución.

 

Asimismo, el gestor genera varios planes de ejecución (forma en la que se va a ejecutar la consulta) y al final elegirá el más optimizado (el más eficiente). El plan de ejecución se estudiará en profundidad en las siguientes secciones.

 

10.3.1 Seguridad.

 

La seguridad en una base de datos es un aspecto importante a tener en cuenta puesto que la información o los datos tienen gran relevancia para las organizaciones.

 

Muchas organizaciones clasifican los datos según su nivel de importancia. Dependiendo del nivel, los datos tienen un tratamiento distinto.

 

La seguridad en una base de datos se asegura haciendo que solo los usuarios autorizados accedan a los datos que están protegidos. En una base de datos es el administrador o DBA el responsable de establecer la seguridad y las reglas de seguridad de la misma.

 

Entre las reglas de seguridad de una base de datos se establece que todo usuario tiene que tener una cuenta y una contraseña con la cual se autentique. Además se concederán una serie de privilegios para poder acceder, modificar o crear datos en las estructuras creadas en la base de datos. También deberá tener privilegios asignados para poder modificar, crear o eliminar dichas estructuras.

 

Generalmente los administradores o DBA conceden o quitan privilegios en la base de datos con las sentencias GRANT y REVOKE.

10.3.2 Concurrencia.

 

Gracias a la multiprogramación se pueden procesar varias transacciones al mismo tiempo tanto si el sistema tiene una CPU como si tienen varias CPU. Cuando un equipo tiene varias CPU el procesamiento simultáneo de transacciones es algo natural mientras que si solamente existe una, el sistema operativo deberá repartir el tiempo entre las distintas transacciones.

 

El objetivo de la concurrencia es:

  • Aumentar la utilización de la CPU al mantenerla menos tiempo ociosa.

  • Mejorar la productividad (al estar menos tiempo ociosa la CPU se ejecutan más transacciones por minuto).

  • También se reduce el tiempo medio de respuesta de las transacciones al procesarse las transacciones pequeñas mucho más rápido.

10.3.3 Transaccionalidad.

 

Las bases de datos agrupan las operaciones a realizar sobre la misma base de datos mediante transacciones.

 

Una transacción es una operación independiente o individual que realiza una operación sobre la base de datos. Cada transacción puede terminar de forma correcta o incorrecta pero nunca se quedará a medias (si termina de forma incorrecta el sistema se queda como si no se hubiese ejecutado dicha transacción).

 

Los gestores de bases de datos utilizan las transacciones para mantener la propia base de datos en un estado consistente y estable.

 

10.3.4 Procesamiento de consultas:

 

Antes de realizar cualquier consulta, el optimizador tiene que revisarla y ofrecer el mejor plan de ejecución. Para ello, el optimizador tiene en cuenta las entidades lógicas de la base de datos (índices y entidades) que a su vez generalmente se corresponden a entidades físicas dentro de la misma base de datos (en los nuevos tipos de bases de datos como bases de datos orientadas a objetos, bases de datos semiestructuradas, etc, esta correspondencia no existe).

 

El optimizador antes de realizar el plan de ejecución evalúa la organización física estimando la cardinalidad (el número de filas) de cada una de las tablas implicadas y los índices de las mismas.

 

Analizando el tipo de índice, sus campos y la cardinalidad de las tablas puede establecer el plan de ejecución más eficiente posible. Dependiendo de la estructura física y como este sintácticamente escrita la sentencia, el optimizador puede tomar decisiones para reducir la cardinalidad de las operaciones a realizar, ejecutar aquellas en los que los índices las puedan acelerar y organizarlas de tal manera que el resultado sea el menos costoso.

 

Para la optimización anterior se tenía en cuenta la organización física de la base de datos y la estructura sintáctica de la consulta. Un buen optimizador generalmente ofrece buenos planes de ejecución. Muchas veces es difícil encontrar opciones más eficientes a la hora de realizar una consulta pero en ocasiones la estructura lógica de una base de datos tiene mucha redundancia. Existen optimizadores semánticos que toman nota de dicha redundancia y proponen planes de ejecución optimizados equivalentes.

 

Existen diferentes técnicas de optimización. Algunas se basan en la heurística (basadas en la sapiencia de los expertos) mientras que otras evalúan solamente el coste (estiman los costos de las diferentes propuestas de ejecución y eligen al final la que tiene el coste menor).

10.4 El componente de gestión de los datos:

 

Una de las tareas de un administrador de bases de datos o DBA es la gestión de los datos. En dicha gestión muchas veces hay que restaurar datos, recuperar índices, etc.

 

Estas operaciones es necesario controlarlas porque según mi experiencia como administrador cuando se trabaja con un sistema los problemas surgen y con más frecuencia cuanto más intensivo sea su uso.

 

10.4.1 Restauración.

 

Las bases de datos al igual que otro tipo de sistemas están expuestos a fallos de diversa índole. Estos fallos pueden ser intencionados o provocados por los usuarios o el mismo administrador o pueden ser debidos a un fallo hardware o software.

 

Lo que siempre tiene que tener un sistema es una manera de restaurar o recuperarse a un estado estable del mismo (previo al fallo).

Actualmente los sistemas gestores de bases de datos proporcionan incluso alta disponibilidad. El sistema durante la recuperación está disponible minimizándose o eliminando, de esa manera, el tiempo en el que la base de datos no está operativa.

10.4.2 Reconstrucción.

 

Generalmente en las bases de datos es frecuente la corrupción de una tabla o de un índice (generalmente en estos últimos es más frecuente).

 

Muchos gestores como MySQL permiten comprobar si existen errores en tablas (o en sus propios índices). Por ejemplo MySQL tiene el comando myisamchk tabla el cual encuentra cualquier tipo de problema en una tabla ISAM.

 

Para reparar tablas MySQL tiene los comandos CHECK TABLE y REPAIR TABLE.

Algunos errores que puede contener una tabla pueden ser bloqueos no resueltos en los datos, corrupción de índices, problemas en los datos, corrupción en los mismos datos, etc.

 

Oracle generalmente es muy estable y rara vez hay que reconstruir tablas o índices. Muchas veces se reconstruyen índices por motivos de rendimiento. Si se quiere reconstruir un índice se ejecutaría el siguiente comando:

ALTER INDEX <index_name> REBUILD;

 

10.5 Gestión del rendimiento

 

En esta sección se estudiarán las herramientas más útiles que tienen los administradores de bases de datos para gestionar el rendimiento de un sistema como son el plan de las consultas, las herramientas de monitorización y los benchmark.

 

10.5.1 Plan de las consultas.

 

El plan de las consultas o plan de ejecución es el que explica cómo dependiendo de las tablas, columnas, índices y condiciones de la cláusula WHERE se va a ejecutar una sentencia SQL.

 

Los gestores de bases de datos generalmente tiene un optimizador que dependiendo de las circunstancias anteriormente citadas organizan el trabajo a realizar en la base de datos para que la consulta se realice de la forma más eficiente y rápida posible.

 

Por ejemplo si tenemos una sentencia con un join de varias tablas, el objetivo del optimizador sería no hacer una comparación de todas y cada una de las combinaciones de filas (si existe la posibilidad).

 

El conjunto de operaciones que el optimizador elige para realizar la consulta de forma más eficiente se denomina plan de ejecución o también explain plan.

El objetivo de un administrador de base de datos es analizar el plan de ejecución y ver si es el más optimizado y por el contrario si se puede reescribir la consulta para que obteniendo el mismo resultado el plan de ejecución sea más eficiente.

 

Generalmente para mejorar las consultas el administrador cuenta con la posibilidad de gestionar los índices los cuales a veces funcionan de forma mágica sobre la lentitud de un sistema o consultas concretas. Obviamente, también el administrador cuenta con la experiencia, que le va a dictar si dada una consulta, un plan de ejecución y la estructura dada de base de datos (índices y tablas):

  • La consulta está convenientemente optimizada y no se puede hacer nada más.

  • La consulta se puede reescribir para optimizarla más.

  • Se puede crear, modificar o borrar algún índice para hacerla más eficiente.

 

Para visualizar el plan de ejecución de una consulta en MySQL basta con anteponer la cláusula EXPLAIN antes de la sentencia SQL.

 

Toad y el plan de una consulta en Oracle

Figura 10.2. Toad y el plan de una consulta en Oracle (explain plan).

 

10.5.2 Monitores.

 

Existen muchas herramientas de monitorización en el mercado. Los administradores aprecian mucho las herramientas todo en uno. Aquellas herramientas con las que se puede modelar la base de datos, realizar consultas, tareas administrativas, cambiar la configuración de la base de datos, administrar usuario, realizar backups, migraciones y como no, monitorizar la base de datos y su entorno.

 

Para MySQL existe una herramienta que realiza muchas de las funciones citadas anteriormente como es MySQL Workbench o también TOAD para Oracle.

 

TOAD. Herramienta de monitorización de la salud de la base de datos

Figura 10.3. TOAD. Herramienta de monitorización de la salud de la base de datos.

 

Oracle también tiene muchas herramientas de monitorización como por ejemplo SolarWinds o MyOra (esta última gratuita).

MySQL Workbench

Figura 10.4. MySQL Workbench

 

10.5.3 Benchmarks.

 

El rendimiento de un sistema gestor de base de datos suele depender de muchos factores. Muchas veces se hacen pruebas en un sistema las cuales indican fijar una configuración determinada pero cuando cambian las circunstancias de la base de datos y de la carga de trabajo, esta configuración deja de ser la más eficiente.

 

Generalmente cuando se quiere evaluar la velocidad de un sistema al realizar una sola o una serie de operaciones lo que se suele hacer es una serie de test con el sistema sin carga de trabajo. Generalmente ese es el trabajo típico de los benchmark.

 

Los benchmark suelen tardar bastante tiempo en ejecutarse y son bastante exhaustivos. Algunas de las conclusiones de los benchmark suele ser cambiar la configuración hardware o de almacenamiento o incluso migrar a una nueva versión del sistema gestor de base de datos.

 

Se recomienda realizar benchmark con bajas y altas cargas de trabajo y lo que es más interesante, con la carga de trabajo típica que va a tener el servidor. A veces a priori se desconoce cual va a ser dicha carga con lo cual hay que estimarla o bien dejar esta prueba para cuando se conozca con más certeza.

 

Consejo de un administrador de base de datos.

Muchas veces el rendimiento de un sistema de base de datos viene dado por el tamaño de caché. En muchos sistemas estableciendo un tamaño de caché óptimo hace que el sistema vaya más fluido y sea más eficiente.

 

Cuando se quiere optimizar una base de datos generalmente se echa mano de los índices. Es cierto que una correcta gestión de los índices puede mejorar bastante el rendimiento pero también hay que explorar otras opciones.

 

MySQL tiene una función integrada llamada benchmark() la cual evalúa expresiones y realiza pruebas sobre ellas. Cuando tenemos consultas que tardan mucho en ejecutarse siempre es bueno realizar test para ver qué funciones o qué expresiones están causando dicho retardo.

 

La función benchmark() toma dos parámetros, uno es el número de veces que va a realizar dicha operación y otro es la expresión que se va a testear.

 

Veamos un ejemplo de cómo funciona dicha herramienta:

 

mysql> SELECT BENCHMARK(20000000, «SELECT CONCAT(‘ra’,’-m’,’a’)»);

+—————————————————–+

| BENCHMARK(20000000, «SELECT CONCAT(‘ra’,’-m’,’a’)») |

+—————————————————–+

| 0 |

+—————————————————–+

1 row in set (0.12 sec)

 

En el ejemplo anterior se puede observar como se realiza 20.000.000 veces la operación CONCAT() y tarda 0,12 segundos. Cuando tenemos que analizar una consulta es bueno analizar partes de ella y sus distintas variantes para encontrar la consulta más óptima.

10.6 Herramientas:

 

Demos un repaso a las herramientas más utilizadas por los administradores de bases de datos como son las utilidades de administración, importación/exportación y acceso y consulta.

10.6.1 Administración.

 

Herramientas de administración de base de datos disponibles en CPanel

Figura 10.5. Herramientas de administración de base de datos disponibles en CPanel.

 

En la actualidad los administradores de bases de datos muchas veces tenemos que administrar una base de datos a través de un hosting. De hecho, la mayoría de bases de datos que son administradas son a través de hostings o alojamientos web.

 

Desde un hosting es imposible realizar todas las operaciones que se pueden hacer si se tiene acceso físico a la base de datos pero generalmente la mayoría de las operaciones sí se podrán realizar desde este interfaz web.

Herramienta MySQL Databases

Figura 10.6. Herramienta MySQL Databases

 

Por ejemplo, en la figura anterior se puede observar como desde la herramienta MySQL Databases se pueden gestionar el alta y baja de bases de datos, eliminar usuarios de dichas bases de datos, chequear o reparar cualquier base de datos existente. En realidad, estos dos últimos comandos ejecutan el comando mysqlchek el cual comprueba, analiza, repara y optimiza las tablas de una base de datos.

 

En ocasiones las bases de datos mysql se corrompen o se estropean y es necesario chequearlas y repararlas. Generalmente si no podemos ejecutar el comando mysqlcheck muchas veces tenemos la opción mediante un interfaz de poder repararlas y dejarlas operativas.

Herramienta MySQL Databases. Creación de usuarios

Figura 10.7. Herramienta MySQL Databases. Creación de usuarios

 

Otra operación básica de administración de una base de datos consiste en la creación de usuarios. Como se puede observar, la herramienta MySQL Databases permite gestionar los usuarios de cualquier base de datos a la que se tenga acceso.

10.6.2 Importación / Exportación masiva de datos.

 

La exportación de una base de datos es una operación sencilla y rutinaria por parte de un administrador. Generalmente tenemos dos opciones:

  • Mediante comandos. Los comandos dependen de la versión y del tipo de servidor de base de datos utilizado.

  • Mediante un interfaz.

 

Por ejemplo, si queremos exportar una base de  datos Oracle 9i el comando que tenemos que ejecutar es el siguiente:

 

$ exp system/***** file=/backup/completaBD.dmp full=Y log=/backup/logs/completaBD.log buffer=100000

 

Si lo que tenemos que utilizar es un interfaz, en la siguiente figura se muestra las opciones de exportación en MySQL de phpMyAdmin que es la herramienta de administración de MySQL más utilizada en la actualidad.

Exportación de una base de datos en phpMyAdmin

Figura 10.8. Exportación de una base de datos en phpMyAdmin

 

El formato de exportación es otra cosa a tener en cuenta. En phpMyAdmin se pueden exportar los datos en formato SQL. Este formato es muy útil porque es fácilmente inteligible y te permite ejecutar todo o solamente ciertas partes a la hora de importar los datos. La pega de este formato es que si la base de datos es muy voluminosa, la sobrecarga de los ficheros es muy grande y es más operativo utilizar un formato que reduzca más el tamaño del archivo de exportación.

Exportación de una base de datos en phpMyAdmin

Figura 10.9. Exportación de una base de datos en phpMyAdmin

 

La importación es el proceso inverso de la exportación. Generalmente una  exportación/importación de base de datos dentro del mismo gestor no debería generar problemas. Los problemas surgen cuando los motores de bases de datos son distintos o cuando las versiones de la base de datos a exportar/importar no coinciden.

10.6.3 Acceso y consulta de datos.

En primer lugar decir que hay infinidad de herramientas de base de datos para acceder a los datos y realizar consultas. Muchas de estas herramientas se conectan a la base de datos mediante ODBC o drivers nativos.

 

A estas herramientas se le exige que tengan un interfaz donde poder escribir sentencias SQL directamente o bien algún tipo de herramienta donde seleccionar campos y establecer criterios tanto de selección como de ordenación, etc, para que los usuarios menos avanzados puedan crear sentencias SQL. También son útiles las herramientas de filtrado en las que se muestran todos los datos y el usuario puede elegir campos, filtrarlos, ordenarlos, etc.

 

En la siguiente figura se muestra la herramienta de generación de consultas de phpMyAdmin.

 

Interfaz phpMyAdmin. Herramienta de creación de consultas

Figura 10.10. Interfaz phpMyAdmin. Herramienta de creación de consultas.

 

phpMyAdmin es una de las herramientas de bases de datos más utilizadas en la actualidad. Entre sus ventajas destaca su interfaz web el cual puede ser accedido remotamente desde cualquier dispositivo.

Deja una respuesta

Tu dirección de correo electrónico no será publicada.

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.