Índices, Vistas y Usuarios

Los índices se utilizan para obtener datos de una columna concreta de las tablas de una forma más rápida. Optimizan el rendimiento de las consultas SELECT cuando buscamos datos en columnas indexadas.

Cuando buscamos una coincidencia de un dato de una columna MySQL debe recorrer uno a uno cada registro buscando la coincidencia. Las búsquedas sobre datos con gran cantidad de registros ven afectado su rendimiento.

Los índices crean un sistema de organización con el que asocian un valor de una columna con la posición del registro en la tabla, permitiendo encontrar valores sin la necesidad de recorrer todos los registros de la tabla. Los índices emplean estructuras de datos de árbol y tablas hash.

Se recomienda su uso en aquellas columnas sobre las que se vayan a realizar búsquedas en una tabla. Por ejemplo, si tenemos una tabla donde almacenamos información sobre Libros, nos podría interesar crear un índice en el campo autor, puesto que puede ser muy común buscar qué libros ha escrito un autor determinado.

Del mismo modo hay que actualizar el índice cada vez que se modifica la columna en la tabla por lo que no es conveniente indexar columnas que se modifiquen con mucha frecuencia.

En resumen, los índices generan un beneficio en tablas con gran cantidad de datos en el que las condiciones de búsqueda emparejan muy pocas coincidencias.

La principal ventaja es la reducción del tiempo de ejecución de consultas SELECT:

• Búsquedas a partir de una condición WHERE sobre columnas de la condición indexadas
• Emparejar filas de otras tablas en consultas de union JOIN
• Ordenaciones y Agrupamientos ORDER BY y GROUP BY sobre columnas indexadas
• Recuperación de datos en consultas simples directamente a partir del índice

También suponen algunas desventajas:

• Las operaciones INSERT, UPDATE y DELETE que se realizan sobre tablas con índices se realizan más lentas. Esto es debido a que después de estas operaciones se deben actualizar los índices.
• Los índices ocupan tamaño en disco ya que generan su propia estructura de búsqueda, pudiendo ocupar más que la tabla en sí.
• Penaliza la modificaciones de datos en una columna indexada.

Los índices se aplican a una columna en concreto, pudiendo haber distintas columnas con distintos índices en la misma tabla. Los tipos habituales de índice son:

• PRIMARY: para columnas con valores únicos, con la restricción de que solo puede existir un índice de este tipo por tabla. Se asigna automáticamente a toda columna/s definidas como PRIMARY KEY.
• INDEX: índice normal, permite valores duplicados, usado para mejorar el tiempo de ejecución.
• UNIQUE: para columnas con valor único, restringiendo la existencia de datos duplicados.
• FULLTEXT: se emplean para realizar búsquedas sobre texto (CHAR, VARCHAR y TEXT). Su funcionamiento es más complejo que los anteriores por lo que se especializa únicamente en búsquedas sobre columnas de texto.

Además el motor InnoBD que usa MySql/MariaDB por defecto en las tablas, asigna también un índice a toda clave ajena que creemos.

Existen distintas formas de añadir índices a una tabla:

-- En la creación de la tabla
CREATE TABLE libro(
id INT ...,
titulo ...,
autor VARCHAR(20),
INDEX autor_index (autor)
);
 
-- Después de crear la tabla
CREATE INDEX indice_autor ON libro(autor);
-- ó
ALTER TABLE libro ADD INDEX [nombre_indice] (autor);
 
-- Índices PRIMARY y UNIQUE
ALTER TABLE nombre_tabla ADD PRIMARY KEY (nombre_columna);
 
ALTER TABLE nombre_tabla ADD UNIQUE [nombre_indice] (nombre_columna);
CREATE UNIQUE INDEX nombre_indice ON nombre_tabla(nombre_columna);
 
-- Índices FULLTEXT
ALTER TABLE nombre_tabla ADD FULLTEXT [nombre_indice] (nombre_columna);
CREATE FULLTEXT INDEX nombre_indice ON nombre_tabla(nombre_columna);

Podemos hacer con la sentencia DROP o con ALTER TABLE:

DROP INDEX nombre_indice ON nombre tabla;
-- ó
ALTER TABLE nombre_tabla DROP INDEX nombre_indice;
 
-- Para índices PRIMARY
DROP INDEX 'PRIMARY' ON nombre_tabla;
-- ó
ALTER TABLE nombre_tabla DROP PRIMARY KEY;

Una vista se puede entender como una consulta de selección almacenada en el sistema bajo un nombre, que permite ser ejecutada directamente a través de ese nombre. Al definir una vista creamos una tabla virtual, es decir, una tabla que no almacena datos propios sino que muestra datos almacenados en otras tablas.

Hay que tener en cuenta que realmente la consulta que se ha creado como vista no se encuentra almacenada sino que tiene que ser generada cada vez que se deba utilizar.

Cuando creamos una vista a partir de una consulta sobre una o más tablas, las futuras modificaciones en las tablas no se propagarán a la vista; si se añaden columnas a las tablas no se mostrarán en la vista, y si se eliminan columnas de las tablas que aparecían en la vista, la ejecucón dará un error. Funciona del mismo modo que funciona al consulta con la que se ha creado.

Del midmo modo, ya que una vista depende de la estructura de las tablas de origen, no se pueden añadir índices a una vista. Sin embargo podemos crear vistas usando la cláusula ORDER BY en su creación. Si posteriormente al consultar sobre una vista, usamos ORDER BY anularemos el órden inicial.

Las vistas se usan principalmente con fines organizativos: permiten seleccionar qué información de una base de datos se muestra en la vista, permitiendo ocultar el resto. No tiene una finalidad de mejora de rendimiento.

• Simplicidad: En lugar de ejecutar constantemente una consulta de varias tablas, podemos ejecutar directamente la vista.
• Seguridad: podemos limitar el acceso a ciertas columnas con datos no autorizados a usuarios que deben consultar y procesar información de esas tablas.
• Mantiene la apariencia original: podemos modificar la estructura de las tablas que almacenan los datos sin que la estructura de la vista se vea afectada.
• Sustituye a consultas sobre subconsultas en la cláusula FROM.

Como hemos indicado, las vistas son una solución para ciertos aspectos por lo que no generan importantes desventajas.

CREATE [OR REPLACE] VIEW nombre_vista AS consulta_SELECT

Veamos un ejemplo:

-- Vista que almacena la consulta que mostraría el número de 
-- pistas que hay en cada polideportivo
CREATE [OR REPLACE] VIEW pistas_por_polideportivo
AS
SELECT PP.id, PP.nombre, COUNT(*) AS cantidad
FROM polideportivos PP, pistas P
WHERE PP.id = P.id_polideportivo
GROUP BY PP.id

Si ahora suponemos que nos pidieran conocer el polideportivo o polideportivos que más pistas tiene, sólo tendríamos que realizar una consulta utilizando la vista creada anteriormente.

-- Nombre del polideportivo que más pistas tiene
SELECT nombre
FROM pistas_por_polideportivo
WHERE cantidad = (SELECT (MAX(cantidad) 
                  FROM pistas_por_polideportivos)

Nos permite eliminar una vista creada:

DROP VIEW vista_cantidad_reservas;

Una de las funciones del SGBD es la de proporcionar seguridad en el acceso a los datos a través de mecanismos de control de acceso.

En SQL, y así lo hacen todos los SGBD relacionales, se sigue un modelo Usuario-Privilegio para otorgar acceso a los objetos de la Base de Datos. Existen una serie de privilegios predefinidos y es el administrador del SGBD el encargado de asignar o no los privilegios ¹⁾ a los usuarios sobre determinados objetos (tablas, procedimientos, vistas, etc).

Supongamos que somos los administradores de un SGBD MySQL y tenemos que proporcionar acceso a una Base de Datos para una aplicación biblioteca a un desarrollador de mi compañía:

-- Si no hemos creado la base de datos, podemos hacerlo ahora
CREATE DATABASE biblioteca;
 
-- Crea el usuario asignándole contraseña
CREATE USER 'desarrollador' IDENTIFIED BY 'mipassword';
 
-- Indicando el host de conexion del usuario
-- Desde cualquier host (igual que si se omite)
CREATE USER 'desarrollador'@'%' IDENTIFIED BY 'mipassword';
 
-- Usuario para conectarse localmente
CREATE USER 'desarrollador'@'localhost' IDENTIFIED BY 'mipassword';
 
-- Usuario para conectarse remotamente
CREATE USER 'desarrollador'@'146.167.34.15' IDENTIFIED BY 'mipassword';
 
-- Ver los usuarios 
SELECT 'desarrollador' FROM mysql.user;
SELECT * FROM mysql.user;

Permite conceder privilegios sobre un objeto a un usuario de la Base de Datos.

    GRANT <privilegio>
    ON <objeto>
    TO <usuario>
    [WITH GRANT OPTIONS]

Permite eliminar el privilegio sobre un objeto a un usuario.

    REVOKE <privilegio>
    ON <objeto>
    FROM <usuario>

Ejemplos:

-- Uso sin permisos para acceder desde local
mysql> GRANT USAGE ON *.* TO 'user'@localhost IDENTIFIED BY 'pass';
 
-- Uso sin permisos para acceder desde remoto
mysql> GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY 'pass';
 
-- Conceder todos los privilegios sobre una base de datos en local
GRANT ALL PRIVILEGES ON `mibd`.* TO 'user'@localhost;
 
-- Privilegios desde remoto
GRANT ALL PRIVILEGES ON `mibd`.* TO 'user'@'%';
 
-- Mostrar privilegios
mysql> SHOW GRANTS FOR 'user'@localhost; 
 
-- Eliminar privilegios
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@localhost;
REVOKE USAGE ON *.* FROM 'user'@localhost;
 
-- Aplicar los cambios realizados
FLUSH PRIVILEGES;
 
-- Verificar permisos
mysql> SHOW GRANTS FOR 'user'@localhost;

© 2024 Santiago Faci y Fernando Valdeón