Transcript MySQL - ABD-UCV-Computacion

MariaDB y MySQL
Integrantes:
•María José Gutiérrez
•Juan Carlos Ferreira
•Ronald Prado
Agenda:











MySQL.
Características de MySQL.
MariaDB.
Características de MariaDB.
Arquitectura de MySQL.
Arquitectura de MariaDB
Manejo de Memoria.
Tipos de Archivos.
Índices en MySQL.
Concurrencia.
Recuperación.
MySQL
 MySQL es un sistema de administración para bases de
datos relacionales que provee una solución robusta a los
usuarios con poderosas herramientas multi-usuario,
soluciones de base de datos SQL (structured Query
Language) multi-threaded. Es rápido, robusto y fácil de
utilizar.
Características de MySQL






Las principales características de este gestor de bases de
datos son las siguientes:
Aprovecha la potencia de sistemas multiprocesador, gracias
a su implementación multihilo.
Soporta gran cantidad de tipos de datos para las columnas.
Dispone de API's en gran cantidad de lenguajes (C, C++,
Java, PHP, etc).
Gran portabilidad entre sistemas.
Soporta hasta 32 índices por tabla.
Gestión de usuarios y passwords, manteniendo un muy
buen nivel de seguridad en los datos.
MariaDB
 MariaDB es un servidor de base de datos derivado de MySQL con
licencia GPL. Está soportado por Michael Monty Widenius
(fundador de MySQL) y la comunidad de desarrolladores de
software libre.
 Tiene una alta compatibilidad con MySQL ya que posee las
mismas órdenes, interfaces, APIs y bibliotecas, siendo su objetivo
poder cambiar un servidor por otro directamente.
Características de MariaDB
 Uno de las características a favor de MariaDB es que no hace falta hacer
ninguna modificación en toda la estructura de la base de datos que
tenemos, ni siquiera hay que tocar una línea de código, porque MariaDB es
100% compatible con MySQL.
 Mejoras de Velocidad:




Existen algunas mejoras al código DBUG
para hacer su ejecución mas rápida cuando se compila.
La tabla de chequeo de redundancia es mas rápida .
El uso del motor aria permite realizar consultas complejas
rápidamente.
Replicación rápida y segura.
Arquitectura de MySQL
Arquitectura de MySQL
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.
Arquitectura de MySQL
La gestión de conexiones es responsable
de mantener las múltiples conexiones
de los clientes. 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.
Arquitectura de MySQL
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.
Arquitectura de MySQL
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.
Arquitectura de MySQL
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é.
Arquitectura de MySQL
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.
Arquitectura de MySQL
La recuperación permite “volver hacia
atrás” (rollback) partes de una
transacción.
Arquitectura de MySQL
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.
Arquitectura de MySQL
Los motores de almacenamiento son
una interfaz abstracta con funciones
comunes de gestión de datos en el nivel
físico.
Arquitectura de MySQL
EL motor de almacenamiento InnoDB
proporcionan tablas transaccionales.
Arquitectura de MySQL
Trata tablas no transaccionales.
Proporciona almacenamiento y
recuperación de datos rápida.
Arquitectura de MySQL
El motor de almacenamiento MEMORY
proporciona tablas en memoria.
Arquitectura de MySQL
El motor de almacenamiento
FEDERATED guarda datos en una base
de datos remota.
Arquitectura de MariaDB
 Mas motores de almacenamiento: Adicionalmente a los motores
estándar de MySQL, los siguientes motores están incluidos en los
paquetes binarios y fuente de MariaDB:
 Aria: Un motor de almacenamiento a prueba de fallos basado en
MyISAM.
 PBXT: Un motor de almacenamiento transaccional con una gran
cantidad de nuevas características.
 XtraDB: El reemplazo del motor InnoDB basado en el plug-in de
InnoDB
 FederatedX: El reemplazo del motor Federated.
Manejo de Memoria
 El servidor MySQL utiliza espacio en disco para almacenar lo siguiente:
 Los programas cliente y servidor, y sus librerías.
 Los archivos de registro ("logs") y de estado.
 Las bases de datos.
 Los archivos de formato de tablas ("*.frm") para todos los motores de
almacenamiento, y los archivos de datos y archivos de índices para algunos
motores de almacenamiento.
 Los archivos de "tablespaces" de InnoDB, si el motor de almacenamiento
InnoDB está activado.
 Tablas temporales internas que han sobrepasado
el límite de tamaño en memoria y deben ser convertidas
a tablas en disco.
Tipos de Archivos
 El motor de almacenamiento MyISAM almacena en el disco
duro cada tabla en tres archivos:
 .frm archivos de formato de la tabla.
 .MYD (MyData) archivos de datos.
 .MYI (MyIndex) archivos índices.
Cuyos nombres que comienzan con el nombre de la tabla y tienen una
extensión para indicar el tipo de archivo.
Indices
 Estructuras de datos que permiten al SMBD localizar de una
manera mas rápida un registro dentro de un archivo de datos.
 Los índices (como en el caso de los libros) sirven para agilizar las
consultas de las tablas, evitando que MySQL tenga que revisar
todos los datos disponibles para devolver el resultado.
 En MySQL el número máximo de índices por tabla y la
longitud máxima del índice se define por el motor de
almacenamiento.
 Todos los motores de almacenamiento de apoyo (support)
permiten por lo menos 16 índices por tabla y una longitud total
del índice de al menos 256 bytes. La mayoría de los motores de
almacenamiento tienen límites más altos.
¿Cómo usa MySQL los índices?
 Para buscar filas que coincidan con una cláusula
WHERE rápidamente.
 Para recuperar filas de otras tablas al realizar joins.
 Para encontrar el valor MIN() y MAX() de una columna
de índice específicos key_col.
Tipos de Índice en MySQL
 PRIMARY
KEY: Índice diseñado para consultas
especialmente rápidas. Todos sus campos deben ser
UNICOS y no admite NULL
 UNIQUE: es aquel que no permite almacenar dos datos
iguales.
 FULLTEXT: (soportado sólo por MyISAM)permite realizar
búsquedas de palabras. Se pueden crear FULLTEXT sobre
columnas tipo CHAR, VARCHAR o TEXT.
Una vez creado se pueden hacer búsquedas de la siguiente
manera:
SELECT * FROM nombre_tabla WHERE
MATCH(nombre_indice_fulltext) AGAINST('palabra_a_buscar');
Tipos de Índice en MySQL
 SPATIAL: su uso común es en tablas geom que
almacene columnas del tipo GEOMETRY. Para tablas
MyISAM, MySQL puede crear índices SPATIAL del
mismo modo que los índices regulares , pero
agregando la palabra reservada SPATIAL, como se verá
más adelante.
 Prefijo: Con col_name ( N ) de sintaxis en una
especificación de índice, puede crear un índice que
utiliza sólo la primera N caracteres de una columna de
cadena. Indexación sólo un prefijo de valores de
columna de esta manera puede hacer un fichero índice
mucho menor.
Regla de “la izquierda”
 Si necesitamos un SELECT ... WHERE columna_1 = X AND columna_2
= Y y ya tenemos un índice con la columna_1, podemos crear un
segundo índice con la columna 2, o mejor todavía, crear un único índice
combinado con las columnas 1 y 2. Estos son los índices multicolumna,
o compuestos.
 No obstante si tenemos índices multicolumna y se utilizan en las
clausulas WHERE, se debe incluir siempre de izquierda a derecha las
columnas indexadas; o el índice NO se usará:
 Supongamos un INDEX usuario (id, name, adress), y una
cláusula SELECT ... WHERE NAME = x. Este Select no aprovechará el
índice. Tampoco lo haría un SELECT ... WHERE ID =X AND ADRESS =
Y. Cualquier consulta que incluya una columna parte del INDEX sin
incluir además las columnas a su izquierda, no hará uso del índice.
 Por tanto en nuestro ejemplo sólo sacarían provecho del índice las
consultas SELECT ... WHERE ID = x, o WHERE ID = X AND NAME =
y o WHERE ID = x AND NAME = y AND ADRESS = Z
Creando un índice en MySQL
 CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX
index_name [index_type]
ON tbl_name (index_col_name,...) [index_type]
 index_col_name: col_name [(length)] [ASC | DESC]
 index_type: USING {BTREE | HASH}
Uso de PRIMARY KEY
 El PRIMARY KEY de una tabla representa la columna o
el conjunto de columnas que se utilizan en la mayoría
de las preguntas vitales.
 Tiene un índice asociado, para el rendimiento de
consulta rápida. Beneficios de rendimiento de consulta
de la optimización NOT NULL, ya que no puede
incluir ningún valor NULL.
 Con el motor de almacenamiento InnoDB, los datos
de la tabla se organizan físicamente para hacer ultrarápido y búsquedas de tipos sobre la base de la
columna de clave principal o columnas.
Índices de columna
 El tipo más común de índice implica una sola columna,
el almacenamiento de copias de los valores de esa
columna en una estructura de datos, lo que permite
búsquedas rápidas de las filas con los valores de la
columna correspondiente
 La estructura de datos B-TREE permite encontrar
rápidamente el índice de un valor específico, un
conjunto de valores, o un rango de valores, lo que
corresponde
a
los
operadores
como = , > , ≤ ,BETWEEN , IN , y así sucesivamente, en
un WHERE cláusula.
Índices de múltiples columnas
 MySQL puede crear índices compuestos (es decir, los
índices de varias columnas). Un índice puede ser de
hasta 16 columnas.
 Un índice de múltiples columnas puede ser
considerado como un conjunto ordenado, las filas de
los cuales contienen los valores que se crean mediante
la concatenación de los valores de las columnas
indexadas.
Concurrencia
Concurrencia
Proceso en el que dos transacciones o más se ejecutan
independientemente y realizando todas las acciones
para las cuales fueron creadas y dejando a la base de
datos en un estado consistente.
Cada transacción concurrente debe cumplir con las
características ACID: Atomicidad, Consistencia,
aIslamiento, Durabilidad.
Concurrencia
En MySQL, esta misión se enfoca en el motor de Base de Datos
INNODB.
MySQL tiene al motor
de almacenamiento
INNODB como mecanismo
para el manejo de
concurrencia.
Motor de Almacenamiento
INNODB
* Dota a MySQL de un motor de almacenamiento
transaccional.
* Posee capacidades de COMMIT, ROLLBACK y
recuperación ante fallas.
* Máximo rendimiento al procesar grandes volúmenes de
datos.
* Soporta restricciones FOREIGN KEY.
Motor de Almacenamiento
INNODB
* Implementa dos protocolos para la concurrencia:
BLOQUEO A NIVEL DE FILAS
BLOQUEO A NIVEL DE TABLAS
y se puede configurar en modo MVCC (CONTROL
MULTIVERSIÓN)
Modos de Bloqueo INNODB
1) A nivel de Filas.
A este nivel tenemos dos categorías:
1.a) Compartido(S): permite a una transacción
solo leer una fila
1.b) Exclusivo(X): permite a una transacción
modificar o eliminar una fila
Modos de Bloqueo INNODB
Un extra…
INNODB soporta “bloqueo de granularidad
múltiple”
(bloqueos en registros y bloqueos en tablas
enteras, simultáneamente)
Modos de Bloqueo INNODB
2) A nivel de Tablas.
Denominados “bloqueos de Intención”, quiere
decir que: la transacción indica que tipo de bloqueo
requerirá sobre una fila de dicha tabla.
Modos de Bloqueo INNODB
Tipos de Bloqueo de Intención.
2.a) Intención Compartida(IS): T trata de
establecer bloqueos S en tuplas individuales de la
tabla.
2.b) Intención Exclusiva(IX): T trata de establecer
bloqueos X en todas las tuplas de la tabla.
Modos de Bloqueo INNODB
Protocolo de Bloqueo de Intención.
Idea Básica:
“Antes que una determinada transacción logre un
bloqueo(S)/bloqueo(X) en una determinada fila, antes
necesita establecer un bloqueo(S)/bloqueo(X) en la
tabla que contiene esta fila”
Modos de Bloqueo INNODB
Ejemplos de sentencias MySQL para bloqueo:
SELECT … FROM … LOCK IN SHARE MODE
LOCK TABLES tbl_name [AS alias]
{READ [LOCAL] | [LOW PRIORITY] WRITE} …
UNLOCK TABLES
Modos de Bloqueo INNODB
Tabla de compatibilidad entre tipos de bloqueos:
Configuración de los Niveles de
Aislamiento en INNODB
* Toda la actividad del usuario, se establece en una
transacción.
* INNODB, posee un modo de ejecución automática
(configurable) llamado AUTOCOMMIT.
Si (AUTOCOMMIT = 1) -> cada sentencia SQL es una
transacción individual
Si (AUTOCOMMIT = 0) -> cada usuario tiene una
transacción abierta
Niveles de Aislamiento en INNODB
Hay 4 niveles que ofrece INNODB para las transacciones:
READ COMITTED
READ UNCOMITTED (lectura sucia)
REPETEABLE READ
SERIALIZABLE
Recuperación
Recuperación
Se puede definir básicamente como todos aquellos
mecanismos y métodos por las cuales pasa una base de
datos para recuperar las acciones hechas por las
transacciones, dañadas debido a algún factor interno o
externo a la BD.
Recuperación en MySQL
1.- Tipos: MySQL implementa dos tipos o métodos de
recuperación.
1.a) Basada en BINARY LOG
1.b) Modificación Inmediata de la BD
(dependiendo del estado de AUTOCOMMIT {0,1}).
Recuperación en MySQL
2.- Estructuras.
* MySQL utiliza dos archivos de registros binarios:
ib_logfile0 y ib_logfile1 (son compartidos por todas las
tablas INNODB.
* El registro binario se encarga de actualizar la base de
datos tan completamente como sea posible (contiene todas
las copias hechas tras la copia de seguridad)
Recuperación en MySQL
2.- Estructuras.
* Uso de la herramienta “mysqldump”
* El cliente mysqldump puede usarse para volcar
una BD o para transferir datos a otro servidor
LOG de MySQL
Tiene dos propósitos importantes:
* La base de replicación consiste en: maestro envía
los eventos, contenidos en su log binario, a sus esclavos,
y ellos ejecutan estos eventos para hacer el mismo
procedimiento que el maestro
* Si se hace una restauración de un archivo de copia
de seguridad, los eventos en log binario (que se
guardaron luego de hacer esta copia de seguridad)
se re-ejecutan.
LOG de MySQL
Donde se almacenan los log binarios?
Normalmente siguen esta ruta de almacenamiento
en DISCO DURO:
“C:/…/var/log/mysql/<nombre_log>”
LOG de MySQL
Visualización de los logs binarios
En LINUX, se visualizan de esta manera:
root@equipo: /etc/mysql# ls –l /var/log/mysql
Referencias
 http://tallerbd.wikispaces.com/Manejador+de+Transac




ci%C3%B3n+y+Recuperaci%C3%B3n
http://ldc.usb.ve/~yudith/docencia/UCV/SistemasDistr
ibuidos/MecanismosRecuperaci%C3%B3nSMBDSahyra.
pdf
http://www.webtaller.com/construccion/lenguajes/mys
ql/lecciones/tipos_tablas_usadas_mysql.php
http://dev.mysql.com/doc/refman.html
http://ict.udlap.mx/people/carlos/is341/bases10.html
GRACIAS POR SU ATENCION…