Imprimir_mysql.docx

UNIVERSIDAD NACIONAL DE INGENIERÍA FACULTAD DE INGENIERÍA INDUSTRIAL Y DE SISTEMAS

MySQL Trabajo Monográfico - DBMS Curso y Sección: ST214 – U – Administración de Base de datos Docente: ING. MANUEL HELARF VELARDE CARPIO INTEGRANTES:  CASTRO RAMOS, ALBERTO  HUAMANYAURI CHUQUIYURI, EDSON CARLOS  MAURTUA BARDALES, JESÚS ANTOUANETTE

Marzo de 2019

20132611A 20132614K

MySQL | Trabajo Monográfico |

Contenido INTRODUCCIÓN ....................................................................................................................... 3 CAPÍTULO 1 : CONCEPTOS GENERALES............................................................................. 4 1.1 BASE DE DATOS .................................................................................................................................................... 4 1.2 MODELOS DE BASE DE DATOS............................................................................................................................ 4 1.2.1 Jerárquico....................................................................................................................................................................... 4 1.2.2 Red .................................................................................................................................................................................. 4 1.2.3 Relacional ...................................................................................................................................................................... 5 1.3 SISTEMA DE GESTIÓN DE BASE DE DATOS......................................................................................................... 5 1.3.1 Características de un DBMS ................................................................................................................................... 6 1.4 ARQUITECTURA DE LOS SISTEMAS MANEJADORES DE BASE DE DATOS ........................................................ 6 1.5 LENGUAJE DE CONSULTA ESTRUCTURADA – SQL .......................................................................................... 7 1.5.1 Objeto de modelo relacional ................................................................................................................................... 8 CAPÍTULO 2 : MYSQL ............................................................................................................... 9 2.1 PANORÁMICA DEL SISTEMA DE GESTIÓN DE BASE DE DATOS MYSQL ........................................................... 9 2.1.1 Historia de MySQL ...................................................................................................................................................10 2.1.2 Principales características de MySQL .................................................................................................................11 2.1.3 Estabilidad en MySQL .............................................................................................................................................14 2.2 ADMINISTRACIÓN DE BASES DE DATOS ........................................................................................................ 15 2.2.1 El servidor MySQL y scripts de arranque del servidor ..................................................................................15 2.2.2 El gestor de instancias de MySQL .......................................................................................................................15 2.2.3 Configuración del servidor MySQL ......................................................................................................................15 2.2.4 El proceso de cierre del servidor MySQL ..........................................................................................................15 2.2.5 Cuestiones de seguridad general .........................................................................................................................15 2.2.6 El sistema de privilegios de acceso de MySQL................................................................................................15 2.2.7 Gestión de la cuenta de usuario MySQL...........................................................................................................15 2.2.8 Prevención de desastres y recuperaciones ........................................................................................................15 CAPÍTULO 3 : MYSQL ENTERPRISE ..................................................................................... 16 3.1 HERRAMIENTAS ................................................................................................................................................... 16 3.2 VISTAS DE CATÁLOGO ....................................................................................................................................... 16 3.3 TIPO DE DATOS .................................................................................................................................................. 17 3.3.1 Tipos numéricos ........................................................................................................................................................17 3.3.2 Tipos fecha ..................................................................................................................................................................18 3.3.3 Tipos cadena ..............................................................................................................................................................19 3.4 ÍNDICES................................................................................................................................................................. 20 3.4.1 ¿Qué es un puntero? ................................................................................................................................................20 3.4.2 ¿Para qué MySQL usa índices? .............................................................................................................................21 3.4.3 ¿Qué tipos de índices hay? .....................................................................................................................................21 3.5 DIMENSIONES MÁXIMAS ..................................................................................................................................... 22 3.6 ARQUITECTURA .................................................................................................................................................. 23 3.6.1 ¿Cómo seleccionar el motor de almacenamiento? .........................................................................................25 3.6.2 Los conectores............................................................................................................................................................26

[1]

MySQL | Trabajo Monográfico | 3.6.3 El gestor de conexiones ..........................................................................................................................................27 3.6.4 El procesamiento y optimización de consultas ...............................................................................................27 3.6.5 La caché de consultas .............................................................................................................................................28 3.6.6 El Control de Concurrencia ....................................................................................................................................28 3.6.7 La gestión de transacciones y recuperación .....................................................................................................28 3.7 INSTALACIÓN, REQUERIMIENTOS Y COMPATIBILIDAD................................................................................... 29 3.8 CONFIGURACIÓN ............................................................................................................................................... 30 3.9 LICENCIA .............................................................................................................................................................. 33 CAPÍTULO 4 : ANÁLISIS COMPARATIVO ........................................................................... 34 4.1 EJECUCIÓN DE LAS PRUEBAS EN LOS SGBD .................................................................................................... 35 4.2 PRUEBA INSERCIÓN DE DATOS .......................................................................................................................... 35 4.3 PRUEBA CONSULTAS DE REGISTROS ................................................................................................................. 37 4.3.1 Análisis Comparativo en el primer entorno “Servidores sin Tuning” .......................................................37 4.3.2 Análisis Comparativo en el segundo entorno “Servidores con Tuning” ..................................................39 4.4 RESULTADOS GENERALES DE LAS PRUEBAS ...................................................................................................... 41 4.4.1 Categoría 1 .................................................................................................................................................................41 4.4.2 Categoría 2 .................................................................................................................................................................42 CONCLUSIONES...................................................................................................................... 43 REFERENCIAS BIBLIOGRÁFICAS ......................................................................................... 43

Índice de Ilustraciones ILUSTRACIÓN 1: NIVELES DE ESTRUCTURA EN UN DBMS ............................................................................................... 7

Índice de Tablas TABLA 3-1 LAS TABLAS A LAS CUALES PODEMOS ACCEDER MEDIANTE INFORMATION_SCHEMA ................... 16 TABLA 3-2: TAMAÑO DE ALMACENAMIENTO POR TIPO DE CAMPO NUMÉRICOS ..................................................... 18 TABLA 3-3: TAMAÑO DE ALMACENAMIENTO POR TIPO DE CAMPO FECHA............................................................... 19 TABLA 3-4: TAMAÑO DE ALMACENAMIENTO POR TIPO DE CAMPO CADENA ........................................................... 20 TABLA 3-5: LÍMITE DE TAMAÑO DE FICHEROS DE SISTEMAS OPERATIVOS. ................................................................... 22 TABLA 3-6: TABLA DE MOTORES EN MYSQL .................................................................................................................. 26 TABLA 4-1: CATEGORÍA 1 .................................................................................................................................................. 41 TABLA 4-2: CATEGORÍA 2 .................................................................................................................................................. 42

[2]

MySQL | Trabajo Monográfico |

INTRODUCCIÓN Relato de todo el documento, mencionar capítulo por caítulo Este trabajo trata de recabar información sobre los Sistemas Manejadores de Base de Datos (DBMS) en particular el MySQL, dar a conocer su importancia, las aplicaciones y otros. Las bases de datos son elementos tecnológicos fundamentales para el desarrollo empresarial en el mundo moderno, encontrándose en el mercado muchas herramientas comerciales y gratuitas que cada día mejoran su rendimiento. Los sistemas manejadores de base de datos facilitan el trabajo de los ingenieros de soporte y administradores de sistemas, permitiendo que existan controles más estrictos y mejores consultas que sirvan para la toma de decisiones de las empresas hoy en día. MySQL es una herramienta

[3]

MySQL | Trabajo Monográfico |

CAPÍTULO 1 :

Conceptos Generales

1.1 Base de Datos Una base de datos está constituida por un conjunto de información relevante para una empresa o entidad y los procedimientos para almacenar, controlar, gestionar y administrar esa información. Además, la información contenida en una base de datos cumple una serie de requisitos o características:  Los datos están interrelacionados, sin redundancias innecesarias.  Los datos son independientes de los programas que los usan.  Se emplean métodos determinados para incluir datos nuevos y para borrar, modificar o recuperar los datos almacenados.

1.2 Modelos de Base de Datos 1.2.1 Jerárquico Este modelo tiene una estructura primaria y secundaria que es similar a un árbol invertido, que es lo que constituye la jerarquía. Los datos están organizados en nodos, el equivalente lógico de tablas en una base de datos relacional. Un nodo principal puede tener muchos nodos secundarios, pero un nodo secundario sólo puede tener un nodo principal. Aunque el modelo ha sido muy utilizado, a menudo se considera inadecuado para muchas aplicaciones debido a su estructura inflexible y la falta de apoyo de relaciones complejas. Aun algunas implementaciones como IMS de IBM han introducido características que trabajan en torno a estas limitaciones.

1.2.2 Red Este modelo aborda algunas de las limitaciones del modelo jerárquico. Los datos están organizados en tipos de registro, el equivalente lógico de tablas en una base de datos relacional. Al igual que el modelo jerárquico, el modelo de red usa la estructura de un árbol invertido, pero los tipos de registro se organizan en una estructura que relaciona pares de tipos de registro en propietarios y miembros. Cualquier tipo de registro puede participar en cualquier conjunto con otros tipos de registro en la base de datos, que apoya a las consultas y relaciones más complejas de lo que es posible en el modelo jerárquico. Hasta el modelo de red tiene sus limitaciones, y la más seria es la complejidad. Al acceder a la base de datos, el usuario debe estar familiarizado con la estructura y mantener un seguimiento cuidadoso de dónde está y cómo llegó ahí. También es difícil cambiar la estructura sin afectar las aplicaciones que interactúan con la base de datos.

[4]

MySQL | Trabajo Monográfico |

1.2.3 Relacional Este modelo aborda algunas de las limitaciones de los modelos jerárquicos y de red. En una base de datos de modelo jerárquico o de red, la aplicación se basa en una implementación determinada de esa base de datos, que luego es codificada en la aplicación. Si agrega un nuevo atributo (elemento de datos) a la base de datos, debe modificar la aplicación, aun cuando no se use ese atributo. Sin embargo, una base de datos relacional es independiente de la aplicación; puede hacer modificaciones no destructivas a la estructura sin afectar la aplicación. Además, la estructura de la base de datos relacional se basa en la relación, o tabla, junto con la habilidad de definir relaciones complejas entre ellas. Se puede acceder directamente a cada relación sin la lentitud de las limitaciones de los modelos jerárquicos o propietario/ miembro que requiere de una navegación a través de una estructura compleja de datos. Aunque aún se usan en muchas organizaciones, las bases de datos de modelo jerárquico y de red ahora se consideran como soluciones heredadas. El modelo relacional es el más ampliamente aplicado en los sistemas de negocios modernos, y es el modelo relacional el que proporciona la base para SQL.

1.3 Sistema de Gestión de Base de Datos Un Sistema de Gestión de Bases de Datos (SGBD) o sus siglas en inglés DBMS (Data Base Management System) es una aplicación comercial que permite construir y gestionar bases de datos, proporcionando al usuario de la Base de Datos las herramientas necesarias para realizar, al menos, las siguientes tareas:  Definir las estructuras de los datos.  Manipular los datos. Es decir, insertar nuevos datos, así como modificar, borrar y consultar los datos existentes.  Mantener la integridad de la información.  Proporcionar control de la privacidad y seguridad de los datos en la Base de Datos, permitiendo sólo el acceso a los mismos a los usuarios autorizados. Nota.- La herramienta más difundida para realizar todas estas tareas es el lenguaje SQL. Algunos de los productos comerciales más difundidos son:  ORACLE de Oracle Corporation.  DB2 de I.B.M. Corporation  SYBASE de Sybase Inc.  Informix de Informix Software Inc.  SQL Server de Microsoft Corporation. [5]

MySQL | Trabajo Monográfico |

1.3.1 Características de un DBMS  Abstracción de la información. Ahorran a los usuarios detalles acerca del almacenamiento físico de los datos.  Independencia. La independencia de los datos consiste en la capacidad de modificar el esquema (físico o lógico) de una base de datos sin tener que realizar cambios en las aplicaciones que se sirven de ella.  Redundancia mínima. Un buen diseño de una base de datos logrará evitar la aparición de información repetida o redundante.  Consistencia. Vigilar que aquella información que aparece repetida se actualice de forma coherente, es decir, que todos los datos repetidos se actualicen de forma simultánea.  Seguridad. Deben garantizar que esta información se encuentra asegurada frente a usuarios malintencionados.  Integridad. Se trata de adoptar las medidas necesarias para garantizar la validez de los datos almacenados.  Respaldo y recuperación. Deben proporcionar una forma eficiente de realizar copias de respaldo de la información almacenada en ellos.  Control de la concurrencia. Lo más habitual es que sean muchas las personas que acceden a una base de datos; ésta debe controlar este acceso concurrente a la información, que podría derivar en inconsistencias.

1.4 Arquitectura de los Sistemas Manejadores de Base de Datos Entre las características referentes al manejo de bases de datos tenemos la separación de los programas y los datos, el manejo de múltiples vistas de usuario y el empleo de un catálogo para almacenar la descripción (esquema) de los datos. La arquitectura que definimos para los sistemas de bases de datos, se denomina Arquitectura de tres esquemas o niveles. Una arquitectura propuesta a utilizar en un DBMS es la que especifica la ANSI/SPARC. Los diferentes esquemas que se pueden configurar la base de datos, se detallan así: Nivel Interno: Esquema que describe la estructura física de almacenamiento de la base de datos: definición de almacenamiento de registros, método de representación de datos y acceso utilizado. Es estructurado por el Administrador de Base de Datos (DBA, Data Base Administrator). [6]

MySQL | Trabajo Monográfico | Nivel Conceptual: Esquema que describe la estructura de toda la base de datos, definido por el DBA. Se incluye todas las entidades de la base de datos Nivel externo: Incluye varios esquemas externos o vistas de usuario. Cada uno de los cuales describe la base de datos de acuerdo con las necesidades de un grupo de usuarios determinado. Este nivel es definido por el usuario o por el programador de aplicaciones en común acuerdo con el DBA. Esta arquitectura utiliza la independencia con respecto a los datos, la cual es la capacidad para modificar el esquema de un nivel del sistema de base de datos sin tener que modificar el esquema del nivel inmediato superior. Para tal caso se puede analizar la independencia de dos formas:

Ilustración 1: Niveles de estructura en un DBMS

1. “La independencia lógica con respecto a los datos es la capacidad de modificar el esquema conceptual sin tener que alterar los esquemas externos ni los programas de aplicación” Tomado de:(http://www.ub.edu.ar/catedras/ingenieria/Datos/capitulo1/cap14.htm) 2. “La independencia física con respecto a los datos es la capacidad de modificar el esquema interno sin tener que alterar el esquema conceptual (o los externos)” Tomado de:(http://www.ub.edu.ar/catedras/ingenieria/Datos/capitulo1/cap14.htm). Un caso es que al crear estructuras de datos adicionales si la base de datos aún contiene los mismos datos, no deberá ser necesario modificar el esquema conceptual.

1.5 Lenguaje de Consulta Estructurada – SQL

[7]

MySQL | Trabajo Monográfico |

1.5.1 Objeto de modelo relacional El lenguaje SQL se basa en el modelo relacional, y hasta SQL-92, también el estándar SQL. Sin embargo, comenzando con SQL: 1999, el estándar SQL se extendió más allá del modelo relacional puro para incluir construcciones orientadas a objetos en el lenguaje. Estas construcciones se basan en los conceptos inherentes de programación orientada a objetos, una programación metodológica que define colecciones autónomas de estructura de datos y rutinas (llamadas objetos). Con la llegada de la programación orientada a objetos (junto con los avances tecnológicos en el hardware y software y la creciente complejidad de aplicaciones) se hizo cada vez más evidente que un lenguaje puramente relacional era insuficiente para satisfacer las demandas del mundo real. De preocupación específica fue el hecho que SQL no podía respaldar tipos de datos complejos y definidos por el usuario ni la extensibilidad requerida para aplicaciones más complejas. Impulsados por la competencia natural de la industria, los proveedores RDBMS se encargaron de aumentar sus productos e incorporar la funcionalidad orientada a objetos en sus sistemas. El estándar SQL sigue el ejemplo y extiende el modelo relacional con capacidades orientadas a objetos, como métodos, encapsulación, y tipos de datos complejos y definidos por el usuario, lo que hace a SQL un lenguaje de base de datos relacional a objeto.

[8]

MySQL | Trabajo Monográfico |

CAPÍTULO 2 :

MySQL

2.1 Panorámica del sistema de gestión de base de datos MySQL  MySQL es el sistema de gestión de base de datos SQL Open Source más popular. Una base de datos es una colección estructurada de datos. Puede ser cualquier cosa, desde una simple lista de compras hasta una galería de imágenes o la gran cantidad de información en una red corporativa. Para agregar, acceder y procesar datos almacenados en una base de datos informática, necesita un sistema de administración de bases de datos como el servidor MySQL. Dado que las computadoras son muy buenas para manejar grandes cantidades de datos, los sistemas de administración de bases de datos juegan un papel central en la informática, como utilidades independientes o como parte de otras aplicaciones.  Las bases de datos MySQL son relacionales. Una base de datos relacional almacena datos en tablas separadas en lugar de poner todos los datos en un gran almacén. Las estructuras de la base de datos están organizadas en archivos físicos optimizados para la velocidad. El modelo lógico, con objetos como bases de datos, tablas, vistas, filas y columnas, ofrece un entorno de programación flexible. Establece reglas que rigen las relaciones entre diferentes campos de datos, como uno a uno, uno a muchos, único, requerido u opcional, y "punteros" entre tablas diferentes. La base de datos impone estas reglas, de modo que con una base de datos bien diseñada, su aplicación nunca vea datos inconsistentes, duplicados, huérfanos, desactualizados o faltantes. La parte de SQL de "MySQL" significa "lenguaje de consulta estructurado". SQL es el lenguaje estandarizado más común utilizado para acceder a las bases de datos. Dependiendo de su entorno de programación, puede ingresar SQL directamente (por ejemplo, para generar informes), incrustar declaraciones SQL en código escrito en otro idioma, o usar una API específica de idioma que oculte la sintaxis SQL. Está definido por el estándar SQL ANSI / ISO. El estándar SQL ha estado evolucionando desde 1986 y existen varias versiones. Por ejemplo, "SQL-92" hace referencia al estándar publicado en 1992, "SQL: 1999" hace referencia al estándar publicado en 1999, y "SQL: 2003" hace referencia a la versión actual del estándar. Utilizamos la frase "el estándar SQL" para referirnos a la versión actual del Estándar SQL.  El software MySQL es de código abierto (Open Source). Código abierto significa que es posible para cualquiera usar y modificar el software. Cualquiera puede descargar el software MySQL de Internet y usarlo sin pagar nada. Si lo desea, puede estudiar el código fuente y modificarlo para adaptarlo a sus necesidades. El software MySQL usa la licencia GPL1, para definir lo que puede o no hacer con el software en diferentes situaciones. Si no se siente cómodo con la GPL o 1

GPL: Licencia pública general de GNU, http://www.fsf.org/licenses/

[9]

MySQL | Trabajo Monográfico | necesita insertar el código MySQL en una aplicación comercial, puede comprar una versión con licencia comercial2.  El servidor de base de datos MySQL es muy rápido, confiable, escalable y fácil de usar. Si eso es lo que estás buscando, debes intentarlo. El servidor MySQL puede ejecutarse cómodamente en una computadora de escritorio o portátil, junto con otras aplicaciones, servidores web, etc., que requieren poca o ninguna atención. Si dedica una máquina completa a MySQL, puede ajustar la configuración para aprovechar toda la memoria, la potencia de la CPU y la capacidad de E / S disponibles. MySQL también puede escalar hasta clusters de máquinas, conectadas en red. MySQL Server fue desarrollado originalmente para manejar grandes bases de datos mucho más rápido que las soluciones existentes y se ha utilizado con éxito en entornos de producción altamente exigentes durante varios años. Aunque en constante desarrollo, el servidor MySQL de hoy ofrece un conjunto de funciones rico y útil. Su conectividad, velocidad y seguridad hacen que MySQL Server sea muy adecuado para acceder a bases de datos en Internet.  El servidor MySQL funciona en entornos cliente / servidor o integrados. El software de base de datos MySQL es un sistema cliente / servidor que consiste en un servidor SQL multiproceso que admite diferentes back-ends, varios programas de cliente y bibliotecas, herramientas administrativas y una amplia gama de interfaces de programación de aplicaciones (API). También se proporciona el servidor MySQL como una biblioteca incrustada de subprocesos múltiples (multi-threaded) que puede vincular a su aplicación para obtener un producto autónomo más pequeño, más rápido y más fácil de administrar.  MySQL tiene una gran cantidad de software disponible MySQL Server tiene un conjunto práctico de características desarrolladas en estrecha cooperación con nuestros usuarios. Es muy probable que su aplicación o idioma favorito sea compatible con el servidor de base de datos MySQL.

La forma oficial de pronunciar "MySQL" es "My Ess Que Ell" (no "mi sicuel"), pero no nos importa si lo pronuncia como "mi secuela" o de alguna otra manera localizada.

2.1.1 Historia de MySQL Se empezó con la intención de usar mSQL para conectar las tablas utilizando sus propias rutinas rápidas de bajo nivel (ISAM). Sin embargo y tras algunas pruebas, 2

MySQL con licencia: Consulte la Introducción a las licencias de MySQL en http://www.mysql.com/company/legal/licensing/.

[10]

MySQL | Trabajo Monográfico | llegaron a la conclusión que mSQL no era lo suficientemente rápido o flexible para cubrir las necesidades. Esto provocó la creación de una nueva interfaz SQL para la base de datos pero casi con la misma interfaz API que mSQL. Esta API fue diseñada para permitir código de terceras partes que fue escrito para poder usarse con mSQL para ser fácilmente portado para el uso con MySQL. La derivación del nombre MySQL no está clara. El directorio base y un gran número de sus bibliotecas y herramientas de MySQL han tenido el prefijo "my" por más de 10 años. Sin embargo, la hija del cofundador Monty Widenius también se llama My. Así que cual de los dos dio el nombre a MySQL todavía es un misterio. El nombre del delfín de MySQL (el logo) es "Sakila", que fue elegido por los fundadores de MySQL AB de una gran lista de nombres sugerida por los usuarios en el concurso "Name the Dolphin" (ponle nombre al delfín). El nombre ganador fue enviado por Ambrose Twebaze, un desarrollador de software Open Source de Swaziland, África. Según Ambrose, el nombre femenino de Sakila tiene sus raíces en SiSwate, el idioma local de Swaziland. Sakila también es el nombre de una ciudad en Arusha, Tanzania, cerca del país de origen de Ambrose, Uganda.

2.1.2 Principales características de MySQL La siguiente lista describe algunas de las características más importantes del software de base de datos MySQL. 2.1.2.1 Interioridades y portabilidad.             

Escrito en C y en C++. Probado con un amplio rango de compiladores diferentes. Funciona en diferentes plataformas. Usa GNU Automake, Autoconf, y Libtool para portabilidad. APIs disponibles para C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, y Tcl. Uso completo de multi-threaded mediante subprocesos del kernel. Pueden usarse fácilmente multiple CPUs si están disponibles. Proporciona sistemas de almacenamientos transaccionales y no transaccionales. Usa tablas en disco B-tree (MyISAM) muy rápidas con compresión de índice. Relativamente sencillo de añadir otro sistema de almacenamiento. Esto es útil si desea añadir una interfaz SQL para una base de datos propia. Un sistema de reserva de memoria muy rápido basado en threads. Joins muy rápidos usando un multi-join de un paso optimizado. Tablas hash en memoria, que son usadas como tablas temporales. Las funciones SQL están implementadas usando una librería altamente optimizada y deben ser tan rápidas como sea posible. Normalmente no hay reserva de memoria tras toda la inicialización para consultas.

[11]

MySQL | Trabajo Monográfico |  El código MySQL se prueba con Purify (un detector de memoria perdida comercial) así como con Valgrind3.  El servidor está disponible como un programa separado para usar en un entorno de red cliente/servidor. También está disponible como biblioteca y puede ser incrustado (vinculado) en aplicaciones autónomas. Dichas aplicaciones pueden usarse por sí mismas o en entornos donde no hay red disponible. 2.1.2.2 Tipos de columnas  Diversos tipos de columnas: enteros con/sin signo de 1, 2, 3, 4, y 8 bytes de longitud, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, ENUM, y tipos espaciales OpenGIS.  Registros de longitud fija y longitud variable. 2.1.2.3 Sentencias y funciones  Soporte completo para operadores y funciones en las cláusulas de consultas SELECT y WHERE. Por ejemplo:

 Soporte completo para las cláusulas SQL GROUP BY y ORDER BY. Soporte de funciones de agrupación (COUNT(), COUNT(DISTINCT ...), AVG(), STD(), SUM(), MAX(), MIN(), y GROUP_CONCAT()).  Soporte para LEFT OUTER JOIN y RIGHT OUTER JOIN cumpliendo estándares de sintaxis SQL y ODBC.  Soporte para alias en tablas y columnas como lo requiere el estándar SQL.  DELETE, INSERT, REPLACE, y UPDATE devuelven el número de filas que han cambiado (han sido afectadas). Es posible devolver el número de filas que serían afectadas usando un flag al conectar con el servidor.  El comando específico de MySQL SHOW puede usarse para obtener información acerca de la base de datos, el motor de base de datos, tablas e índices. El comando EXPLAIN puede usarse para determinar cómo el optimizador resuelve una consulta.  Los nombres de funciones no colisionan con los nombres de tabla o columna. Por ejemplo, ABS es un nombre válido de columna. La única restricción es que para una llamada a una función, no se permiten espacios entre el nombre de función y el '(' a continuación.  Puede mezclar tablas de distintas bases de datos en la misma consulta. 2.1.2.4 Seguridad  Un sistema de privilegios y contraseñas que es muy flexible y seguro, y que permite verficación basada en el host. Las contraseñas son seguras 3

Valgrind: es una herramienta GPL (http://developer.kde.org/~sewardj/).

[12]

MySQL | Trabajo Monográfico | porque todo el tráfico de contraseñas está cifrado cuando se conecta con un servidor.Puede mezclar tablas de distintas bases de datos en la misma consulta. 2.1.2.5 Escalabilidad y límites  Soporte a grandes bases de datos. Usamos MySQL Server con bases de datos que contienen 50 millones de registros. También conocemos a usuarios que usan MySQL Server con 60.000 tablas y cerca de 5.000.000.000.000 de registros.  Se permiten hasta 64 índices por tabla (32 antes de MySQL 4.1.2). Cada índice puede consistir desde 1 hasta 16 columnas o partes de columnas. El máximo ancho de límite son 1000 bytes (500 antes de MySQL 4.1.2).Un índice puede usar prefijos de una columna para los tipos de columna CHAR, VARCHAR, BLOB, o TEXT. 2.1.2.6 Conectividad  Los clientes pueden conectar con el servidor MySQL usando sockets TCP/IP en cualquier plataforma. En sistemas Windows de la familia NT (NT, 2000, XP o 2003), los clientes pueden usar named pipes para la conexión. En sistemas Unix, los clientes pueden conectar usando ficheros socket Unix.  En MySQL 5.0, los servidores Windows soportan conexiones con memoria compartida si se inicializan con la opción --shared-memory. Los clientes pueden conectar a través de memoria compartida usando la opción --protocol=memory. 2.1.2.7 La Interfaz  La interfaz para el conector ODBC (MyODBC) proporciona a MySQL soporte para programas clientes que usen conexiones ODBC (Open Database Connectivity). Por ejemplo, puede usar MS Access para conectar al servidor MySQL. Los clientes pueden ejecutarse en Windows o Unix. El código fuente de MyODBC está disponible. Todas las funciones para ODBC 2.5 están soportadas, así como muchas otras.  La interfaz para el conector J MySQL proporciona soporte para clientes Java que usen conexiones JDBC. Estos clientes pueden ejecutarse en Windows o Unix. 2.1.2.8 Localización  El servidor puede proporcionar mensajes de error a los clientes en muchos idiomas.  Soporte completo para distintos conjuntos de caracteres, incluyendo latin1 (ISO-8859-1), german, big5, ujis, y más. Por ejemplo, los caracteres escandinavos 'â', 'ä' y 'ö' están permitidos en nombres de tablas y columnas. El soporte para Unicode está disponible.

[13]

MySQL | Trabajo Monográfico |  Todos los datos se guardan en el conjunto de caracteres elegido. Todas las comparaciones para columnas normales de cadenas de caracteres son case-insensitive.  La ordenación se realiza acorde al conjunto de caracteres elegido (usando colación Sueca por defecto). Es posible cambiarla cuando arranca el servidor MySQL. Para ver un ejemplo de ordenación muy avanzada, consulte el código Checo de ordenación. MySQL Server soporta diferentes conjuntos de caracteres que deben ser especificados en tiempo de compilación y de ejecución. 2.1.2.9 Clientes y herramientas  MySQL server tiene soporte para comandos SQL para chequear, optimizar, y reparar tablas. Estos comandos están disponibles a través de la línea de comandos y el cliente mysqlcheck. MySQL también incluye myisamchk, una utilidad de línea de comandos muy rápida para efectuar estas operaciones en tablas MyISAM.  Todos los programas MySQL pueden invocarse con las opciones --help o -? para obtener asistencia en línea.

2.1.3 Estabilidad en MySQL El código original se remonta a los principos de los años 80. En TcX, la predecesora de MySQL AB, el código MySQL ha funcionado en proyectos desde mediados de 1996 sin ningún problema. Cuando el software de base de datos MySQL fue distribuído entre un público más amplio, nuestros nuevos usuarios rápidamente encontraron trozos de código no probados. Cada nueva versión desde entonces ha tenido pocos problemas de portabilidad incluso considerando que cada nueva versión ha tenido muchas nuevas funcionalidades. Cada versión de MySQL Server ha sido usable. Los problemas han ocurrido únicamente cuando los usuarios han probado código de las "zonas grises". Naturalmente, los nuevos usuarios no conocen cuáles son estas zonas; sin embargo, siempre se trata de documentar dichas áreas conocidas a día de hoy. Las descripciones mayormente se corresponden con la versión 3.23, 4.0 y 4.1 de MySQL Server. Todos los bugs reportados y conocidos se arreglan en la última versión, con las excepciones listadas en las secciones de bugs y que están relacionados con problemas de diseño. El diseño de MySQL Server es multicapa, con módulos independientes.

[14]

MySQL | Trabajo Monográfico |

2.2 Administración de Bases de DATOS 2.2.1 El servidor MySQL y scripts de arranque del servidor 2.2.2 El gestor de instancias de MySQL 2.2.3 Configuración del servidor MySQL 2.2.4 El proceso de cierre del servidor MySQL 2.2.5 Cuestiones de seguridad general 2.2.6 El sistema de privilegios de acceso de MySQL La función primaria del sistema de privilegios de MySQL es autenticar un usuario conectándose desde un equipo dado, y asociar dicho usuario con privilegios en una base de datos tales como SELECT, INSERT, UPDATE, y DELETE. Funcionalidad adicional incluye la habilidad de tener usuarios anónimos y de dar privilegios para funciones específicas de MySQL tales como LOAD DATA INFILE y operaciones administrativas.

2.2.7 Gestión de la cuenta de usuario MySQL 2.2.8 Prevención de desastres y recuperaciones

[15]

MySQL | Trabajo Monográfico |

CAPÍTULO 3 :

MySQL Enterprise

3.1 Herramientas

3.2 Vistas de Catálogo El catálogo de un sistema de base de datos no es más que una base de datos en sí misma a veces llamada metabase. Los contenidos de dicha base de datos son llamados metadatos. Esencialmente se guardan objetos que puedan resultar de interés para el sistema, Las vistas de catálogo le permiten consultar metadatos acerca de los objetos de base de datos de MySQL, como tablas, columnas, privilegios, relaciones entre tablas, triggers. Algunas vistas de catálogo muestran información de todo el servidor, pero la mayoría es específica de la base de datos. Las vistas de catálogo se muestran en la carpeta Vistas\Vistas del sistema para cada base de datos en el Explorador de objetos de MySQL Server Management Studio. Aunque puede consultarlas utilizando la sintaxis estándar de Transact-SQL para las vistas definidas por el usuario, no se implementan realmente como vistas tradicionales en las tablas subyacentes, sino que consultan directamente los metadatos del sistema. Para acceder al contenido de esta base de datos y visualizar cada uno de los contenidos se puede hacer de la siguiente forma: USE information_schema; SELECT [nombre de la columna] FROM [nombre de la tabla] ó SELECT [nombre de la columna] FROM INFORMATION_SCHEMA.[nombre de la tabla] Tabla 3-1: Las tablas a las cuales podemos acceder mediante INFORMATION_SCHEMA

Vista de catálogo INFORMATION_SCHEMA SCHEMATA INFORMATION_SCHEMA TABLES INFORMATION_SCHEMA COLUMNS INFORMATION_SCHEMA STATISTICS INFORMATION_SCHEMA USER_PRIVILEGES INFORMATION_SCHEMA TABLE_PRIVILEGES INFORMATION_SCHEMA COLUMN_PRIVILEGES [16]

MySQL | Trabajo Monográfico | INFORMATION_SCHEMA CHARACTER_SETS INFORMATION_SCHEMA COLLATIONS INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY INFORMATION_SCHEMA TABLE_CONSTRAINTS INFORMATION_SCHEMA KEY_COLUMN_USAGE INFORMATION_SCHEMA ROUTINES INFORMATION_SCHEMA VIEWS INFORMATION_SCHEMA TRIGGERS

3.3 Tipo de Datos Los tipos de datos definen los valores de datos permitidos para cada columna de una tabla de base de datos. MySQL proporciona varios tipos de datos. Algunas categorías de tipos de datos que se utilizan normalmente en los lenguajes de programación tienen varios tipos de datos asociados de MySQL Server. Los tipos de datos que puede haber en un campo, se pueden agrupar en tres grandes grupos:

3.3.1 Tipos numéricos Existen tipos de datos numéricos, que se puede dividir en dos grandes grupos, los que están en coma flotante (con decimales) y los que no.  TinyInt: Es un número entero con o sin signo. Con signo el rango de valores válidos va desde -128 a 127. Sin signo, el rango de valores es de 0 a 255  Bit ó Bool: Un número entero que puede ser 0 ó 1  SmallInt: Número entero con o sin signo. Con signo el rango de valores va desde 32768 a 32767. Sin signo, el rango de valores es de 0 a 65535.  MediumInt: Número entero con o sin signo. Con signo el rango de valores va desde 8.388.608 a 8.388.607. Sin signo el rango va desde 0 a16777215.  Integer, Int: Número entero con o sin signo. Con signo el rango de valores va desde 2147483648 a 2147483647. Sin signo el rango va desde 0 a 429.4967.295  BigInt: Número entero con o sin signo. Con signo el rango de valores va desde 9.223.372.036.854.775.808 a 9.223.372.036.854.775.807. Sin signo el rango va desde 0 a 18.446.744.073.709.551.615. [17]

MySQL | Trabajo Monográfico |  Float: Número pequeño en coma flotante de precisión simple. Los valores válidos van desde -3.402823466E+38 a -1.175494351E-38, 0 y desde 1.175494351E38 a 3.402823466E+38.  xReal, Double: Número en coma flotante de precisión doble. Los valores permitidos van desde -1.7976931348623157E+308 a -2.2250738585072014E-308, 0 y desde 2.2250738585072014E-308 a 1.7976931348623157E+308  Decimal, Dec, Numeric: Número en coma flotante desempaquetado. El número se almacena como una cadena Tabla 3-2: Tamaño de almacenamiento por Tipo de Campo Numéricos

Tipo de Campo Tamaño de Almacenamiento TINYINT

1 byte

SMALLINT

2 bytes

MEDIUMINT

3 bytes

INT

4 bytes

INTEGER

4 bytes

BIGINT

8 bytes

FLOAT(X)

4 ú 8 bytes

FLOAT

4 bytes

DOUBLE

8 bytes

DOUBLE

8 bytes

PRECISION

8 bytes

REAL

8 bytes

3.3.2 Tipos fecha A la hora de almacenar fechas, hay que tener en cuenta que Mysql no comprueba de una manera estricta si una fecha es válida o no. Simplemente comprueba que el mes está comprendido entre 0 y 12 y que el día está comprendido entre 0 y 31.  Date: Tipo fecha, almacena una fecha. El rango de valores va desde el 1 de enero del 1001 al 31 de diciembre de 9999. El formato de almacenamiento es de año-mes-día.  DateTime: Combinación de fecha y hora. El rango de valores va desde el 1 de enero del 1001 a las 0 horas, 0 minutos y 0 segundos al 31 de diciembre del 9999 a las [18]

MySQL | Trabajo Monográfico | 23 horas, 59 minutos y 59 segundos. El formato de almacenamiento es de año-mes-día. horas:minutos:segundos  TimeStamp: Combinación de fecha y hora. El rango va desde el 1 de enero de 1970 al año 2037. El formato de almacenamiento depende del tamaño del campo:  Time: Almacena una hora. El rango de horas va desde -838 horas, 59 minutos y 59 segundos a 838, 59 minutos y 59 segundos. El formato de almacenamiento es de 'HH:MM:SS'  Year: Almacena un año. El rango de valores permitidos va desde el año 1901 al año 2155. El campo puede tener tamaño dos o tamaño 4 dependiendo de si queremos almacenar el año con dos o cuatro dígitos. Tabla 3-3: Tamaño de almacenamiento por Tipo de Campo Fecha

Tipo de Campo Tamaño de Almacenamiento DATE DATETIME TIMESTAMP TIME YEAR

3 bytes 8 bytes 4 bytes 3 bytes 1 byte

3.3.3 Tipos cadena  Char(n): Almacena una cadena de longitud fija. La cadena podrá contener desde 9 a 255 carácteres.  VarChar(n): Almacena una cadena de longitud variable, La cadena podrá contener desde 9 a 255 caracteres. Dentro de los tipos de cadena se pueden distinguir otros dos subtipos, los tipo Test y los tipo BLOB (Binary Large Object) La diferencia entre un tipo y otro es el tratamiento que reciben a la hora de realizar ordenamientos y comparaciones. Mientras que el tipo test se ordena sin tener en cuenta las Mayúsculas y las minúsculas, el tipo BLOB se orden teniéndolas en cuenta.  TinyText y TinyBlobl: Columna con una longitud máxima de 255 caracteres.  Blob y Text: Un texto con un máximo de 65535 caracteres.  MediumBlob y MediumText: Un texto con un máximo de 16.777.215 caracteres.  LonBlob y LongText:

[19]

MySQL | Trabajo Monográfico | Un texto con un máximo de caracteres 4.294.967.295. Hay que tener en cuenta que debido a los protocolos de comunicación los paquetes pueden tener un máximo de 16 Mb.  Enum: Campo que puede tener un único valor de una lista que se especifica. El tipo Enum acepta hasta 65535 valores distintos.  Set: Un campo que puede contener ninguno, uno o varios valores de una lista. La lista puede tener un máximo de 64 valores. Tabla 3-4: Tamaño de almacenamiento por Tipo de Campo Cadena

Tipo de Campo

Tamaño de Almacenamiento

CHAR(n)

N bytes

VARCHAR(n)

N+1 bytes

TINYBLOB, TINYTEXT

Longitud + 1 byte

BLOB, TEXT

Longitud + 2 bytes

MEDIUMBLOB, MEDIUMTEXT Longitud + 3 bytes LONGBLOB, LONGTEXT

Longitud + 4 bytes

ENUM(‘value1’,’value2’,….)

1 ó dos bytes dependiendo del número de valores 1,2,3,4 ó 8 bytes. dependiendo del número de valores

SET(‘value1’,’value2’,…)

3.4 Índices Un índice es un puntero a una fila de una determinada tabla de nuestra base de datos. Pero leyendo esto nacen las siguientes preguntas

3.4.1 ¿Qué es un puntero? Un puntero es una referencia que asocia el valor de una determinada columna (o el conjunto de valores de una serie de columnas) con las filas que contienen ese valor (o valores) en las columnas que componen el puntero. Los índices mejoran el tiempo de recuperación de los datos en las consultas realizadas contra nuestra base de datos. Pero los índices no son todas ventajas, pues, la creación de índices implica un aumento en el tiempo de ejecución sobre aquellas consultas de inserción, actualización y eliminación realizadas sobre los datos afectados por el índice (ya que tendrán que actualizarlo). Del mismo modo, los [20]

MySQL | Trabajo Monográfico | índices necesitan un espacio para almacenarse, por lo que también tienen un coste adicional en forma de espacio en disco. La creación de los índices es el primer paso para realizar optimizaciones en las consultas realizadas contra nuestra base de datos. Por ello, es importante conocer bien su funcionamiento y los efectos colaterales que puede producir.

3.4.2 ¿Para qué MySQL usa índices? MySQL emplea los índices para encontrar las filas que contienen los valores específicos de las columnas empleadas en la consulta de una forma más rápida. Si no existiesen índices, MySQL empezaría buscando por la primera fila de la tabla hasta la última buscando aquellas filas que cumplen con los valores establecidos para las columnas empleadas en la consulta. Esto implica que, cuantas más filas tenga la tabla, más tiempo tardará en realizar la consulta. En cambio, si la tabla contiene índices en lsa columnas empleadas en la consulta, MySQL tendría una referencia directa hacia los datos sin necesidad de recorrer secuencialmente todos ellos. En general, MySQL emplea los índices para las siguientes acciones: 

Encontrar las filas que cumplen la condición WHERE de la consulta cuyas columnas estén indexadas.



Para recuperar las filas de otras tablas cuando se emplean operaciones de tipo JOIN. Para ello, es importante que los índices sean del mismo tipo y tamaño ya que aumentará la eficiencia de la búsqueda. Por ejemplo: una operación de tipo JOIN sobre dos columnas que tengan un índice del tipo INT(10).



Disminuir el tiempo de ejecución de las consultas con ordenación (ORDER BY) o agrupamiento (GROUP BY) si todas las columnas presentes en los criterios forman parte de un índice.



Si la consulta emplea una condición simple cuya columna de la condición está indexada, las filas serán recuperadas directamente a partir del índice, sin pasar a consular la tabla.

3.4.3 ¿Qué tipos de índices hay? Existen distintos tipos de índices que se pueden crear y las condiciones que deben cumplir cada uno de ellos. 

INDEX (NON-UNIQUE): este tipo de índice se refiere a un índice normal, no único. Esto implica que admite valores duplicados para la columna (o columnas) que componen el índice. No aplica ninguna restricción especial a los datos de la columna (o columnas) que componen el índice, sino que se emplea simplemente para mejorar el tiempo de ejecución de las consultas.



UNIQUE: este tipo de índice se refiere a un índice en el que todas las columnas deben tener un valor único. Esto implica que no admite valores [21]

MySQL | Trabajo Monográfico | duplicados para la columna (o columnas) que componen el índice. Aplica la restricción de que los datos de la columna (o columnas) deben tener un valor único. 

PRIMARY: este tipo de índice se refiere a un índice en el que todas las columnas deben tener un valor único (al igual que en el caso del índice UNIQUE) pero con la limitación de que sólo puede existir un índice PRIMARY en cada una de las tablas. Aplica la restricción de que los datos de la columna (o columnas) deben tener un valor único.



FULLTEXT: estos índices se emplean para realizar búsquedas sobre texto (CHAR, VARCHAR y TEXT). Estos índices se componen por todas las palabras que están contenidas en la columna (o columnas) que contienen el índice. No aplica ninguna restricción especial a los datos de la columna (o columnas) que componen el índice sino que se emplea simplemente para mejorar el tiempo de ejecución de las consultas. Este tipo de índices sólo están soportados por InnoDB y MyISAM en MySQL 5.7.



SPATIAL: estos índices se emplean para realizar búsquedas sobre datos que componen formas geométricas representadas en el espacio. Este tipo de índices sólo están soportados por InnoDB y MyISAM en MySQL 5.7.

3.5 Dimensiones máximas En MySQL 5.0, usando el motor de almacenamiento MyISAM, el máximo tamaño de las tablas es de 65536 terabytes (256 ^ 7 - 1 bytes). Por lo tanto, el tamaño efectivo máximo para las bases de datos en MySQL usualmente los determinan los límites de tamaño de ficheros del sistema operativo, y no por límites internos de MySQL. El motor de almacenamiento InnoDB mantiene las tablas en un espacio que puede ser creado a partir de varios ficheros. Esto permite que una tabla supere el tamaño máximo individual de un fichero. Este espacio puede incluir particiones de disco, lo que permite tablas extremadamente grandes. El tamaño máximo del espacio de tablas es 64TB. La siguiente tabla lista algunos ejemplos de límites de tamaño de ficheros de sistemas operativos. Esto es sólo una burda guía y no pretende ser definitiva. Para la información más actual, asegúrese de consultar la documentación específica de su sistema operativo. Tabla 3-5: Límite de tamaño de ficheros de sistemas operativos.

Sistema operativo

Tamaño máximo de fichero 2GB(LFS: 4GB)

Linux 2.2-Intel 32-bit

[22]

MySQL | Trabajo Monográfico | Linux 2.4

(usando sistema ficheros ext3) 4TB 16TB

Solaris 9/10

de

Sistema de ficheros NetWare 8TB w/NSS win32 w/ Fat/ FAT32 2GB/4GB win32 w/NTFS

2TB

MacOS X w/ HFS+

2TB

En Linux 2.2, puede utilizar tablas MyISAM mayores de 2GB usando el parche para LFS (Large File Support) en el sistema de ficheros ext2. En Linux 2.4 y posteriores, existen parches para ReiserFS soportando grandes archivos (hasta 2TB). La mayoría de distribuciones Linux se basan en el kernel 2.4 o 2.6 e incluyen todos los parches LFS necesarios. Con JFS y XFS, se permiten ficheros mayores de un petabyte para Linux. Sin embargo, el tamaño máximo de ficheros todavía depende de diversos factores, uno de ellos siendo el sistema de ficheros usado para almacenar tablas MySQL.

3.6 Arquitectura La arquitectura de MySQL tiene como característica más notable el separar el motor de almacenamiento (que se encarga de los detalles de entrada-salida y representación de la información en memoria secundaria) de resto de los componentes de la arquitectura. Es decir, el diseño del gestor está preparado para que se pueda cambiar el gestor de almacenamiento. Esto permite incluso crear nuevos motores de almacenamiento especializados para ciertas tareas o tipos de aplicaciones. Esta representación gráfica es una visión abstracta de la arquitectura lógica de MySQL. La figura hace una división entre los componentes que conforman el servidor, las aplicaciones cliente que lo utilizan y las partes del sistema operativo en las que se basa el almacenamiento físico.

[23]

MySQL | Trabajo Monográfico |

Las uitilidades y herramientas de MySQL son los programas y aplicaciones que se incluyen con la distribución del gestor, o que pueden instalarse como aplicaciones adicionales. Estas incluyen las herramientas de backup, el navegador de consultas (QueryBrowser), las aplicaciones administrativas de interfaz gráfico y la herramienta de diseño MySQL WorkBench, entre otras. Motores de almacenamiento El elemento más notable de la arquitectura de MySQL es la denominada arquitectura de motores de almacenamiento reemplazables (pluggable storage engine architecture). La idea de esa arquitectura es hacer una interfaz abstracta con funciones comunes de gestión de datos en el nivel físico. De ese modo, el gestor de almacenamiento puede intercambiarse, e incluso un mismo servidor MySQL puede utilizar diferentes motores de almacenamiento para diferentes bases de datos o para diferentes tablas en la misma base de datos. Esto permite utilizar el motor de almacenamiento más adecuado para cada necesidad concreta. También permite que terceros puedan implementar motores de almacenamiento nuevos para necesidades específicas, o adaptar el código de los existentes a ciertos requisitos de almacenamiento. Así, las interfaces definidas por MySQL aíslan el resto de los componentes de la arquitectura de las complejidades de la gestión física de datos, facilitando el mantenimiento de los motores de almacenamiento. También esto permite que ciertos motores de almacenamiento no implementen parte de los servicios, lo cual les hace inapropiados para algunas aplicaciones, pero más eficientes para otros. Por ejemplo, un motor de almacenamiento que no implementa bloqueos en la base de datos no debe utilizarse en aplicaciones multi-usuario, pero la ausencia de sobrecarga de procesamiento en la

[24]

MySQL | Trabajo Monográfico | gestión de los bloqueos para el acceso concurrente lo hará mucho más eficiente para una aplicación monousuario. En consecuencia, una primera tarea de diseño físico en MySQL es la de decidir el motor de almacenamiento más apropiado. Los elementos que puede implementar un motor de almacenamiento son los siguientes:  Concurrencia. Es responsabilidad del motor implementar una política de bloqueos (o no implementar ninguna). Una estrategia de bloqueos por fila permite una mayor concurrencia, pero también consume más tiempo de procesamiento en aplicaciones en las que la concurrencia no es realmente grande.  Soporte de transacciones. No todas las aplicaciones necesitan soporte de transacciones.  Comprobación de la integridad referencial, declarada como restricciones en el DDL de SQL.  Almacenamiento físico, incluyendo todos los detalles de la representación en disco de la información.  Soporte de índices. Dado que la forma y tipo de los índices depende mucho de los detalles del almacenamiento físico, cada motor de almacenamiento proporciona sus propios métodos de indexación (aunque algunos como los árboles B casi siempre se utilizan).  Cachés de memoria. La eficiencia de los cachés de datos en memoria depende mucho de cómo procesan los datos las aplicaciones. MySQL implementa cachés comunes en el gestor de conexiones y la caché de consultas, pero algunos motores de almacenamiento pueden implementar cachés adicionales.  Otros elementos para ayudar al rendimiento, como puede ser el uso de múltiples hilos para operaciones paralelas o mejoras de rendimiento para la inserción masiva. Además de lo anterior, los motores de almacenamiento pueden implementar características no comunes, como la gestión de tipos de datos geoespaciales, o cualquier función adicional específica de cierto tipo de aplicaciones.

3.6.1 ¿Cómo seleccionar el motor de almacenamiento? No hay una receta única que permita definir el motor de almacenamiento. La selección debe hacerse una vez tenemos el modelo lógico de la base de datos y conocemos los requisitos de rendimiento y no funcionales de la aplicación o aplicaciones que vamos a construir.

[25]

MySQL | Trabajo Monográfico | La sentencia SHOW ENGINES nos muestra la lista de motores en MySQL, incluyendo el motor por defecto y los que no están disponibles con la configuración actual. El resultado para MySQL 5.1 es el siguiente: Tabla 3-6: Tabla de motores en MySQL

Motor

Comentario Motor predeterminado a partir de MySQL 3.23 con gran rendimiento Basado en Hash, almacenado en la memoria, útil para tablas temporales

MyISAM MEMORY InnoDB

Admite transacciones, bloqueo a nivel de fila y clases foráneas

BerkeyDB

Admite transacciones y bloqueo a nivel de página

BLACKHOLE

/dev/null motor de almacenamiento (todo lo que escribes desaparece)

EXAMPLE

Ejemplo de motor de almacenamiento

ARCHIVE

Motor de almacenamiento de archivos

CSV

Motor de almacenamiento CSV

Ndbcluster FEDERATED

Tablas agrupadas, basadas en la memoria y tolerantes a fallas Motor de almacenamiento Federated MySQL

MRG_MYISAM

Colección de tablas idénticas MyISAM

ISAM

Motor de almacenamiento obsoleto

De la tabla anterior solo podemos tener nociones iniciales del tipo de gestor, y dirigirnos a alguno de ellos para casos concretos, por ejemplo, ndbcluster tiene características únicas si necesitamos soporte para alta disponibilidad. No obstante, hace falta conocer más en profundidad las características de cada uno para tomar decisiones y en ocasiones es necesario hacer pruebas de rendimiento con varios de ellos para compáralos y seleccionar el que mejor se ajusta a nuestras necesidades.

3.6.2 Los conectores Los conectores son bibliotecas en diferentes lenguajes de programación que permiten la conexión (remota o local) con servidores MySQL y la ejecución de consultas. Por ejemplo, el conector Connector/J permite conectarse a MySQL desde cualquier aplicación programada en lenguaje Java, y utilizando el Java Database Connectivity (JDBC) API.

[26]

MySQL | Trabajo Monográfico |

3.6.3 El gestor de conexiones La gestión de conexiones es responsable de mantener las múltiples conexiones de los clientes. Un gestor de conexiones inexistente o laxo simplemente crearía una conexión por cada cliente conectado. No obstante, las conexiones consumen recursos de máquina, y crearlas y destruirlas son también procesos costosos. Por eso, el gestor de conexiones de MySQL puede configurarse para limitar el número de conexiones concurrentes, y también implementa un pool de conexiones. La idea es que muchas aplicaciones abren una conexión y la mantienen abierta y ociosa durante mucho tiempo (por ejemplo, durante toda la sesión de un usuario, que de vez en cuando se levanta para diferentes tareas mundanas como tomar café), y solo “de vez en cuando” se utiliza un hilo de ejecución para ejecutar una consulta, que, además, típicamente tarda como mucho unos milisegundos. No tiene sentido mantener una conexión ociosa para cada usuario. De aquí proviene la idea de los pools de conexiones: hay un número de conexiones disponibles, y cada vez que una aplicación hace una solicitud, se le asigna una conexión del pool que no esté ocupada. Lógicamente, la aplicación cliente no es consciente de este mecanismo. En términos, por ejemplo, de las interfaces JDBC, esto quiere decir que cada vez que enviamos una sentencia con llamadas como Statement.executeQuery() realmente puede que se cree una nueva colección, o quizá se tome una del pool. Es decir, las llamadas a Driver.getConnection() y a Connection.close() no siempre determinan el tiempo de vida de una conexión real en MySQL. Además de la reducción en el tiempo de establecimiento de conexión (si se reusa una conexión ya creada del pool), la técnica permite limitar el número de conexiones simultáneas. Dado que las conexiones consumen recursos, es mejor limitar este número que llevar a una carga excesiva en el servidor, que podría acabar en una caída del sistema o un comportamiento impredecible. Nótese que, gracias a los pools de conexiones, puede darse servicio a muchas conexiones concurrentes con un número limitado de conexiones que se reutilizan. El gestor de conexiones también se ocupa de la autentificación de los usuarios. La autentificación por defecto se basa en el nombre de usuario, la máquina desde la que se conecta y la password. El servidor puede también configurarse para soportar certificados X.509.

3.6.4 El procesamiento y optimización de consultas Cada vez que una consulta llega al gestor de MySQL, se analiza sintácticamente y se produce una representación intermedia de la misma. A partir de esa representación, MySQL toma una serie de decisiones, que pueden incluir el determinar el orden de lectura de las tablas, el uso de ciertos índices, o la re-escritura de la consulta en una forma más eficiente.

[27]

MySQL | Trabajo Monográfico | Existe la posibilidad de utilizar ciertas cláusulas en las consultas para ayudar al optimizador en su tarea, o bien podemos pedirle al servidor ciertas “explicaciones” sobre cómo ha planificado nuestras consultas, para entender mejor su funcionamiento. Dado que la optimización de las consultas depende de las capacidades del gestor de almacenamiento que se esté utilizando, el optimizador “pregunta” al gestor si soporta ciertas características, y de este modo, puede decidir el tipo de optimización más adecuado.

3.6.5 La caché de consultas MySQL implementa un caché de consultas, donde guarda consultas y sus resultados enteros. De este modo, el procesador de consultas, antes ni siquiera de plantear la optimización, busca la consulta en la caché, para evitarse realizar el trabajo en el caso de que tenga suerte y encuentre la consulta en la caché.

3.6.6 El Control de Concurrencia El control de concurrencia en un gestor de bases de datos es simplemente el mecanismo que se utiliza para evitar que lecturas o escrituras simultáneas a la misma porción de datos terminen en inconsistencias o efectos no deseados. El mecanismo que se utiliza para controlar este acceso es el de los bloqueos (locks). La idea es muy simple, cada vez que una aplicación quiere acceder a una porción de los datos, se le proporciona un bloqueo sobre los mismos. Lógicamente, varias aplicaciones que quieran leer simultáneamente no tienen ningún problema en hacerlo, de modo que para la lectura se proporcionan bloqueos compartidos (shared locks). Sin embargo, varios escritores o un escritor simultáneo con lectores pueden producir problemas. Por eso, para la escritura se proporcionan bloqueos exclusivos (exclusive locks). Aunque la idea parece simple, hay que tener en cuenta que la obtención y liberación de los bloqueos se realiza continuamente, y esto produce una sobrecarga en el procesamiento dentro del servidor. Además, hay diferentes políticas de bloqueo, por ejemplo, ¿es mejor bloquear cada tabla completa afectada o solo las filas de la tabla a las que quiere acceder una consulta? Dada la existencia de diferentes técnicas, el control de concurrencia en MySQL se divide entre el servidor y cada gestor de almacenamiento.

3.6.7 La gestión de transacciones y recuperación La gestión de transacciones permite dotar de semántica “todo o nada” a una consulta o a un conjunto de consultas que se declaran como una sola transacción. Es decir, si hay algún problema y parte de la consulta o algunas de las consultas no consiguen llevarse a cabo, el servidor anulará el efecto parcial de la parte que ya haya sido ejecutada. La recuperación permite “volver hacia atrás” (rollback) partes de una transacción. [28]

MySQL | Trabajo Monográfico | Para complicarlo aún más, puede que una transacción implique más de una base de datos, y en ocasiones, a más de un servidor (transacciones distribuidas). MySQL proporciona soporte para todos estos tipos de transacciones, siempre que los gestores de almacenamiento utilizados en las bases de datos implicadas también lo soporten.

3.7 Instalación, Requerimientos y Compatibilidad

[29]

MySQL | Trabajo Monográfico |

3.8 Configuración Una vez instalado el servidor deberemos ejecutar el programa para dar de alta y consultar las bases de datos que ocuparemos en nuestro sistema. 1. Ejecute el programa llamado “MySQL 5.7 Command Line Client”, ingrese la contraseña Root para poder tener acceso a la línea de comandos.

2. Para crear una base de datos deberá ingresar la línea: create database “nombre de la base de datos”; . Es recomendable que nombre a la base de datos como el RFC de la empresa que vaya a configurar. (deberá colocar el nombre sin las comillas) Puede generar un número ilimitado de bases de datos.

[30]

MySQL | Trabajo Monográfico | 3. Si el comando fue colocado correctamente mandará el siguiente mensaje.

4. Para consultar las bases de datos generadas ingresa la siguiente línea: show databases.

[31]

MySQL | Trabajo Monográfico | 5. Si el comando fue colocado correctamente aparecerá una lista con todas las bases de datos generadas, por default el servidor tiene algunas bases como podemos visualizar en la imagen.

6. Por último es necesario que tengamos a la mano la IP de la computadora donde fue instalado el servidor, para esto ejecute el programa “Simbolo de sistema” e ingrese la siguiente línea: ipconfig.

7. El programa mostrará cual es la dirección de IP, la cual nos servirá para configurar el servidor para el programa de contabilidad.

[32]

MySQL | Trabajo Monográfico |

3.9 Licencia Parte de ésta popularidad se debe también al hecho de que MySQL puede ser utilizada o modificada por cualquier persona o empresa sin ningún costo. El programa incorpora dos tipos de licencias: 1. La licencia GNU GPL: la cual permite a cualquier persona, empresa o entidad usar el programa sin ningúna restricción. También se da la libertad de modificar el producto y nuevamente re-distribuirlo bajo la misma licencia. Esta licencia se caracteriza por ser completamente gratuita. 2. MySQL también incorpora una licencia comercial con la cual las empresas pueden re-distribuir el producto bajo sus propios términos. La licencia sin embargo tiene un precio, pero no es costosa comparada con licencias de bases de datos comerciales como SQL Server de Microsoft. Estos dos tipos de licencias se aplican tanto al servidor como a las interfaces y librerías clientes como el C client library, mysqladmin, MySQLCC, mysqldump, libmysqlclient, MySQL Connector/ODBC y J. Y no se aplican a la documentación producida por MySQL AB la cual está bajo la licencia de propiedad intelectual. Algo importante para recalcar es que si uno utiliza la licencia GPL no obtendrá ningún tipo de asistencia técnica, soporte o garantía por parte de MySQL AB. Si vuestra empresa requiere de alguno de estos servicios es recomendable que se compre la licencia comercial. Sin embargo, en el internet existe un amplio grupo de usuarios que siempre están dispuestos a ayudaros con cualquier pregunta que tengáis. Para acceder a ellos podrás suscribirte a una lista de correo.

[33]

MySQL | Trabajo Monográfico |

CAPÍTULO 4 :

Análisis Comparativo

Actualmente no existe una fórmula que permita el cálculo del rendimiento de los gestores de bases de datos; es por ello que para poder realizar los cálculos y así determinar cuál de los dos servidores (SQL Server 2012, MySQL) es el mejor, se han determinado valores referenciales con el fin de evaluar el tiempo de respuesta para la prueba de inserción de registros y la prueba de consultas de datos. Se establecieron los porcentajes de referencia de 30 % para la prueba de inserción y del 70% para la prueba de consultas de datos, éstos valores se fijaron debido a la diferencia en la dificultad que presentan ambas transacciones

Para adquirir los valores y así lograr los porcentajes citados, se realizará antes un estudio del tiempo que toma la inserción y consulta de datos, permitiendo así asignar una puntuación entre 1 y 10. Se efectuarán comparaciones entre dos gestores (SQL Server y MySQL) en donde el menor tiempo de respuesta de un SGBD al momento de ejecutar una prueba, tomará el valor de 10 y se calculará un valor proporcional inverso para el otro gestor de base de datos. Con el valor proporcional inverso, se podrá obtener una estimación sobre el 100% y así poder realizar el análisis de comparación. A continuación, se determinará la escala de valores referenciales, los cuales permitirán determinar de forma porcentual, cuantitativa y cualitativa cuál de los gestores de bases de datos es mejor

[34]

MySQL | Trabajo Monográfico |

4.1 Ejecución de las pruebas en los SGBD Para determinar el tiempo de respuesta de los servidores y poder realizar las comparaciones, se realizaron dos tipos de pruebas: inserción y consultas de datos. Para ello se varió la cantidad de registros insertados y la cantidad de registros consultados, aunque la cantidad de consultas es constante.

4.2 Prueba inserción de datos A continuación, se detallarán los valores obtenidos al momento de insertar los datos a las tablas de las bases de datos MySQL y SQL Server 2012. La medición se efectuó en segundos y varió la cantidad de datos en los dos gestores.

[35]

MySQL | Trabajo Monográfico |

Una vez obtenidos los valores, se pasará a realizar los cálculos para determinar los valores cualitativos de los gestores;

Se puede apreciar que existe una mínima diferencia con respecto a MySQL durante la prueba de inserción, en donde se obtuvo un porcentaje de 91,16% por parte de MySQL, contra un porcentaje de 99,30% de SQL Server 2012. El valor cuantitativo de MySQL y de SQL Server fue de 5 y el valor cualitativo de ambos es de excelente, lo [36]

MySQL | Trabajo Monográfico | cual indica que ambos servidores, MySQL y SQL Server 2012, actúan de modo similar al momento de insertar datos.

4.3 Prueba consultas de registros Esta prueba se realizará en dos entornos distinto: 

En el primer entorno a los servidores no se les realizará ningún tuning, es decir, los parámetros de configuración tendrán los valores por defecto. Las cantidades de registros irán en aumento cada 30.000 datos aproximadamente, desde 29.674 datos hasta alcanzar una totalidad de 443.074 datos; estas medidas serán constantes para los dos servidores. Las consultas tendrán distintos grados de dificultad. Una vez obtenidos los tiempos de respuesta, se procederán a realizar las comparaciones



En el segundo entorno, se ajustarán los parámetros de configuración y se realizarán las mismas actividades mencionadas en el primer entorno.

4.3.1 Análisis Comparativo en el primer entorno “Servidores sin Tuning” 4.3.1.1 Consulta con una tabla

Con estos datos se procederá a realizar el análisis comparativo entre los gestores; además se realizarán los cálculos para determinar la puntuación sobre 10 que se le otorgará a cada medición lo que permitirá establecer los valores cualitativos y cuantitativos de los gestores [37]

MySQL | Trabajo Monográfico |

De acuerdo al rango de valores, SQL Server con un 94,40% tiene un valor cuantitativo de 5 lo que indica que su valor cualitativo es Excelente, mientras que MySQL al tener un porcentaje de 73,80% tiene un valor cuantitativo de 4 indicando que su valor cualitativo es de Sobresaliente. 4.3.1.2 Consultas con una tabla utilizando la cláusula GROUP BY

Con estos datos se procederá a realizar el análisis comparativo entre los gestores; además se realizarán los cálculos para determinar la puntuación sobre 10 que se le

[38]

MySQL | Trabajo Monográfico | otorgará a cada medición lo que permitirá establecer los valores cualitativos y cuantitativos de los gestores.

De acuerdo al rango de valores, MySQL al tener un porcentaje de 100% tiene un valor cuantitativo de 5 lo que indica que su valor cualitativo es Excelente, mientras que SQL Server al tener un porcentaje de 70,90% tiene un valor cuantitativo de 4 indicando que su valor cualitativo es de Sobresaliente.

4.3.2 Análisis Comparativo en el segundo entorno “Servidores con Tuning” 4.3.2.1 Consultas con una tabla y un criterio de selección.

[39]

MySQL | Trabajo Monográfico | Con estos datos se procederá a realizar el análisis comparativo entre los gestores; además se realizarán los cálculos para determinar la puntuación sobre 10 que se le otorgará a cada medición lo que permitirá establecer los valores cualitativos y cuantitativos de los gestores.

De acuerdo al rango de valores, SQL Server con un 100% tiene un valor cuantitativo de 5 lo que indica que su valor cualitativo es Excelente, mientras que MySQL al tener un porcentaje de 92,80% también tiene un valor cuantitativo de 5 indicando que su valor cualitativo es de Excelente 4.3.2.2 Consultas con una tabla utilizando la cláusula GROUP BY.

[40]

MySQL | Trabajo Monográfico | Con estos datos se procederá a realizar el análisis comparativo entre los gestores; además se realizarán los cálculos para determinar la puntuación sobre 10 que se le otorgará a cada medición lo que permitirá establecer los valores cualitativos y cuantitativos de los gestores.

De acuerdo al rango de valores, MySQL tiene un porcentaje de 94,30%, mientras que SQL Server tiene un porcentaje de 94,40%, esto indica que ambos gestores de bases de datos tienen un valor cuantitativo de 5 indicando que su valor cualitativo es Excelente.

4.4 Resultados generales de las pruebas 4.4.1 Categoría 1 Consultas con una Tabla y un criterio de selección Tabla 4-1: Categoría 1

[41]

MySQL | Trabajo Monográfico |

4.4.2 Categoría 2 Consultas con una tabla utilizando cláusula GROUP BY. Tabla 4-2: Categoría 2

[42]

MySQL | Trabajo Monográfico |

Conclusiones Referencias Bibliográficas GARCÍA Álvaro E. “Manual Práctico de SQL” SENA: Servicio Nacional de Aprendizaje, FAVA: Formación en Ambientes Virtuales de Aprendizaje, ”Sistema Manejadores de Base de Datos”, MONTERO Fernando. “Lenguaje SQL” OPPEL Andy, Robert Sheldon. “Fundamentos de SQL”, México. Mc Graw Hill, 3era Ed. 556pp. https://desarrolloweb.com/articulos/1054.php https://manuales.guebs.com/mysql-5.0/information-schema.html#otherinformation-schema-tables https://www.adictosaltrabajo.com/tutoriales/introduccion-a-indices-en-mysql/ https://archive.cnx.org/contents/fa33ef02-f882-4b8d-9f674be268dd6d03@1/visi-n-general-de-la-arquitectura-de-mysql-5-1 http://www.monografias.com/trabajos96/mysql-manual-su-instalacion/mysqlmanual-su-instalacion.shtml https://dev.mysql.com/downloads/windows/installer/8.0.html

[43]