Mod BD UD8 ADMINIST

De MediaWiki
Ir a la navegación Ir a la búsqueda

Introducción

  • En esta unidad vamos a ver diferentes actividades orientadas a la administración y mejora de acceso a los datos en una base de datos relacional empleando Mysql.
Veremos:
  • El uso de índices para mejorar la velocidad de acceso a datos.
  • Creación de eventos: Los eventos van a permitir ejecutar código SQL y que este se ejecute cada cierto tiempo o en una fecha-hora determinada.
  • Creación de usuarios y privilegios: Durante todo este curso hemos realizado todas las operaciones siendo root, un usuario creado por defecto en Mysql y que tiene todos los permisos. Veremos que esto no es lo adecuado y que debemos de limitar el acceso a los recursos en base a los privilegios que tenga cada usuario.
  • Copias de seguridad: Aprenderemos cómo crear copias de seguridad de nuestra base de datos y como recuperarla en caso de catástrofe.
  • Importación / Exportación: También relacionado con el punto anterior, veremos como importar y exportar nuestra base de datos a diferentes formatos.


  • Para aprender todo lo anterior, vamos a abandonar la base de datos con la que trabajamos en la UD7, y haremos uso de una base de datos de prueba que suministra Mysql de nombre employees.
Dicha base de datos no es muy compleja (sólo tiene 6 tablas) pero incorpora millones de registros que nos va a permitir realizar pruebas de 'optimización' de acceso a la base de datos.
Vamos a instalar la base de datos employees.
Mysql sp 00.jpg


Los pasos para instalarla los tenéis en este enlace.
Básicamente tenemos que:
  • Descargar de github la base de datos. Desde Linux, en una consola podemos escribir la orden: wget https://github.com/datacharmer/test_db/archive/master.zip
  • Descomprimimos el archivo descargado desde consola con la orden: unzip master.zip
  • Dentro del directorio test-db-master/ se encuentra el archivo employees.sql que es el que crea todas las tablas y añade los datos a cada una de ellas. Para ejecutarlo, ejecutaremos desde consola la orden: sudo mysql -uroot -p < employees.sql (suponemos que root tiene password). Una vez hecho esto ya tenemos la base de datos de nombre employees creada.




Indices

  • La función principal que tiene un índice es la de acelerar las consultas SELECT que se hagan sobre una base de datos.
En Mysql disponemos de el registro de consultar lentas que podemos activar para determinar cuales son las consultas que más ralentizan el Mysql, analizarlas y ver si podemos mejorarlas cambiándolas o creando índices.
  • Un índice es una estructura de datos en la cual los valores guardados son los datos de una o más columnas de una tabla de forma ordenada. Cada entrada del índice apuntará a la ubicación del registro en la tabla.
De esta forma, la búsqueda de registros por un campo indexado será mucho más rápida que si no lo está.
  • Como en todo, el uso de registros lleva aparejado ciertos problemas (sino fuera así podríamos crear índices para todas las columnas de una tabla):
  • Es una estructura de datos que ocupa espacio a mayores del espacio de la propia tabla.
  • En caso de operaciones de inserción, borrado o modificación el índice se debe rehacer con la consiguiente carga de trabajo para el gestor.


IMPORTANTE:

  • Toda clave primaria debe tener un índice asociado (de hecho se crea automáticamente).
Toda clave foránea debe de tener asociado un índice (si lo hacemos gráficamente ya crea el índice, pero si somos nosotros los que ejecutamos la orden CREATE TABLE tendremos que crear un índice por cada columna que sea clave foránea).


  • Un ejemplo obtenido de la wiki:
Mysql index 1.png
En este ejemplo, la clave primaria tiene asociado un índice de tipo BTREE, en el que las claves están ordenadas, estando al lado izquierdo las menores y al lado derecho las mayores con respecto al valor referenciado de un nodo dado.


  • ¿ Cuando voy a hacer uso de índices ?
  • En cláusulas WHERE
  • En cláusulas ORDER BY
Si tengo una orden SQL como:
SELECT nombre FROM ALUMNOS
WHERE edad between 10 and 20
Tardará menos en ejecutarse si tengo un índice asociado a la edad que si no lo tengo ya que no tendrá que recorrer todas las filas comprobando la cláusula Where.



Crear índices

  • Podemos crear un índice de dos formas:


  • Veamos un ejemplo sobre una supuesta tabla ALUMNOS sobre la que queramos indexar su nombre:
CREATE INDEX idx_Alumnos_nombre ON ALUMNOS (nombre)
Siendo ALUMNOS el nombre de la tabla y nombre la columna a indexar.
ALTER TABLE ALUMNOS ADD INDEX idx_Alumnos_nombre (nombre)
Siendo Alumnos el nombre de la tabla y nombre la columna a indexar.


  • Algunos de los modificadores que podemos añadir a la creación de un índice:
  • UNIQUE: índice formado por campos cuyo valor no puede repetirse.
  • PRIMARY: Es el índice que conforma la clave primaria de una tabla. Los valores no pueden repetirse. Es recomendable que sea numérica y en caso de no existir, se debe crear una columna de tipo 'autonumérico'.
  • full-text: Es un tipo de índice 'especial' que se aplica a campos de texto de gran tamaño, como el tipo TEXT y sus variantes, aunque también puede realizarse sobre char y varchar. Al crear un índice de este tipo, cada una de las palabras del texto es 'indexada' para poder hacer búsquedas en las que se busca sólo alguna de las palabras del mismo. Esto se realiza haciendo uso de la orden SQL MATCH(). A diferencia de LIKE, este comando busca por palabras y permite buscar en varias columnas, indicar si deben aparecer ciertas palabras en la columna o que no aparezcan ciertas otras,...
  • SPATIAL: Son índices usados para tipos de datos espaciales como LINE o CURVE.


  • Indicar que cuando creamos una tabla, también podemos 'crear índices' de la forma:
CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB;
Se puede poner KEY o INDEX.
Fijarse que no crea un índice para emp_no a pesar de ser clave foránea. Esto es así ya que al formar parte de la clave primaria y estar en primer lugar, Mysql ya crea un índice para dicha columna.



Eventos


Usuarios y privilegios


Copias de seguridad


Importación/Exportación







Enlace a la página principal de la UD8

Enlace a la página principal del curso




-- Ángel D. Fernández González -- (2020).