Mod BD UD4 Deseño Físico

De MediaWiki
(dif) ← Revisión anterior | Revisión actual (dif) | Revisión siguiente → (dif)
Ir a la navegación Ir a la búsqueda

Sumario

Modelo de datos

Conceptualmente, podemos definir o deseño físico dunha base de datos coma un proceso que, a partir do seu deseño lóxico e de información sobre o seu uso esperado, creará unha configuración física da base de datos adaptada a contorna onde se aloxará e que permite o almacenamento e a explotación dos datos da base de datos cun rendemento axeitado.

Imos estudar en detalle esta definición para entender o concepto de deseño físico.

a) Entradas: o deseño físico parte da seguiente información:

  • o esquema lóxico: é o resultado do deseño lóxico da base de datos e conten a lista de táboas necesarias para almacenar a información relevante, incluyendo cando menos as súas columnas, claves primarias e foráneas.
  • información sobre o uso esperado da base de datos: estimación sobre os volúmenes de datos e transaccións que o sistema deberá procesar; deberá tamén conter unha estimación sobre as operacións SQL que se utilizarán, sobre qué datos, con qué frecuencia, e a calidade de servizo agardada.

b) Saída: tomarase un conxunto de decisións sobre as estruturas físicas máis axeitadas a utilizar, xenerando unha configuración física da base de datos. Esta configuración poderá estar composta por unha combinación axeitada dos espazos de almacenaxe, un conxunto de índices para mellorar o rendemento das consultas, un particionamiento de táboas axeitado, un conxunto de vistas materializadas e outros elementos adicionais, coma por exemplo disparadores que regulen regras de negocio complexas ou procedementos almacenados na base de datos.

c) Adaptado á contorna: o deseño físico pode verse influido polo SXBD onde se implemente a base de datos, os dispositivos de almacenamento non volátil onde se garden os datos da base de datos, e a contorna hardware onde se aloxe o SXBD.

  • SGBD: o paso dun deseño lóxico a físico require un coñecemento profundo do SXBD onde se vaia implementar a base de datos. Deberáse ter entre outros dun coñocemento dos seguintes elementos:
  • Soporte ofrecido á integridade referencial
  • Tipos de índices disponibles
  • Tipos de datos disponibles
  • Tipos de restriccións de integridade disponibles
  • Parámetros de configuración que poidan afectar ó rendimiento (coma por exemplo o tamaño de páxina e a xestión de datos e de concurrencia utilizados)
  • Construccións SQL disponibles de soporte ó deseño físico
  • Particularidades do SXBD utilizado (e en consecuencia da linguaxe SQL) en relación á definición de elementos relacionados co deseño físico da base de datos
  • Contorna de almacenamento: O hardware disponible e as súas capacidades (velocidade de acceso, sistemas de replicación, etc.) permitirán definir distintas configuracións físicas para mellorar o rendimiento da base de datos (non é o mesmo almacenar a base de datos nun PC, que nun servidor con múltiples discos ou outros dispositivos de almacenamento non volátiles, que nun sistema distribuido).

d) Que permita o almacenamento e explotación dos datos cun rendemento axeitado: o obxectivo do deseño físico é obter un bo rendemento da base de datos nunha contorna real. Referímonos con rendemento básicamente ó tempo de resposta a operacións de consulta ou actualización, á carga de transaccións a procesar e á disponibilidade da base de datos.

Elementos de deseño físico

Os elementos ofrecidos polo SXBD para afinar o modelo físico da base de datos son principalmente os seguintes:

  • Espacios para tablas
  • Índices
  • Vistas materializadas
  • Particións

Implementación de restricións

A continuación detallaranse varias posibilidades de impoñer restriccións (constraints) ós datos. Podes atopar:

Claves primarias

Unha clave primaria defínese coma un campo ou combinación de campos que idetifican de forma única un rexistro dunha táboa.

No caso de que a clave primaria este conformada por un só atributo crease así:

 Create Table pedidos
 (
 id_pedido INT(4) PRIMARY KEY,
 id_cliente INT(4) NOT NULL,
 id_artigo INT(4)NOT NULL,
 data DATE,
 cantidade INT(4),
 total INT(4),
 PRIMARY KEY(id_pedido)
 )

No caso no cal se requira que a clave primaria este formada por varias columnas, sería:

 Create Table pedidos
 (
 id_pedido INT(4) NOT NULL AUTO_INCREMENT,
 id_cliente INT(4) NOT NULL,
 id_artigo INT(4)NOT NULL,
 data DATE,
 cantidade INT(4),
 total INT(4),
 CONSTRAINT PK_Pedido PRIMARY KEY (id_pedido,id_cliente,id_artigo)
 )

Claves alleas

Unha clave foránea ou allea considérase como un ou máis campos dunha táboa que fann referencia a uno ou varios campos que son clave primaria noutra táboa, indicando como estan relacionadas as táboas.

Os datos nos campos de clave allea e clave primaria deben coincidir no seu tipo de dato, sen importar o nome que teñan.

Exemplo. 
Na táboa pedido, débese facer a referencia á táboa cliente e a táboa artígo creando as chaves foráneas.
 
 Create Table pedidos
 (
 id_pedido INT(4) NOT NULL AUTO_INCREMENT,
 id_cliente INT(4) NOT NULL,
 id_artigo INT(4)NOT NULL,
 data DATE,cantidade INT(4),
 total INT(4),
 CONSTRAINT id_clente_fk REFERENCES clientes(dni),
 CONSTRAINT id_artigo_fk REFERENCES articulo(id_artigo),
 CONSTRAINT PK_Pedido PRIMARY KEY (id_pedido,id_cliente,id_artigo)
 );

Outras restriccións

Restriccion de Obligatoriedade - NOT NULL

A restricción NOT NULL permite definir se o campo aceptará ou non valores nulos. Neste caso, todas as columnas dunha táboa que teñan a restricción Not Null precisan ter un valor para ser almacenado.

 --Exemplo.
 --Na creación dunha táboa chamada pedidos, pódese restrinxir algunha das suas cúmnas con Not Null.
 Create Table pedidos
 (
 id_pedido INT(4) NOT NULL,
 id_cliente INT(4) NOT NULL,
 id_artigo INT(4)NOT NULL,
 data DATE,
 cantidade INT(4),
 total INT(4)
 )

Restricción de Unicidade - UNIQUE

A restricción UNIQUE obriga a que o contido dunha ou máis columnas non poidan repetir valores. A forma xeneral para colocar esta restricción é a seguiente:

    CREATE TABLE table_name(  
        col1 datatype,  
        col2 datatype UNIQUE,  
        ...  
    );
 --Exemplo: o dni será unico, pero pode haber valores NULL
 CREATE TABLE cliente(
 dni VARCHAR2(9) UNIQUE);
 --Exemplo.
 --Na cración da táboa pedidos, inclúese unha restrición de unicidade.
 Create Table pedidos 
 (
 id_pedido INT(4) NOT NULL UNIQUE, 
 id_cliente INT(4) NOT NULL,
 id_artigo INT(4)NOT NULL,
 data DATE,
 cantidade INT(4),
 total INT(4)
 )

Restrincción de Dominio - CHECK

A restricción CHECK limita os valores do dominio. Por exemplo, para limitar a idade a partir dos 18, sería:

 --Exemplo.
 --Limitar o campo age a valores iguales ou maiores de 18 anos
 CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
 );

Ferramentas gráficas

Achegadas polo sistema xestor

MySQL posibilita mediante a ferramenta MySQL Workbench a implementación e xestión da base de datos, permitiondo de xeito unificado e visual a labor dos arquitectos, programadores e administradores da base de datos, facilitando:

  • o deseño, modelado, xeneración e xestión das bases de datos. Inclúe o que se require para crear complexos modelos ER, enxeñería directa e inversa, relización de cambios de xestión complexos, elaborar a docuentación, etc.
  • o desenvolvemento. Facilita a creación, execución e optimización de consultas SQL. O editor SQL prove coloreado de sintaxe, autocompletado, reuso de snippets (pequenos esbozos de código utilizados para reutilización) e historial de execución de SQL.
  • a administración. Permite administrar entornos MySQL (servidores, usuarios, backups e recuperacións, auditoría, saúde da base de datos, etc.)
  • o control de rendemento (Visual Performance Dashboard ou cadro de control de rendemento). Permite mellorar o rendemento das aplicacións en MySQL co uso do Performance Dashboard, e a xeneración de Performance Reports (informes de rendemento) que facilitan a identificación de uso IO, as consultas SQL que están penalizando o rendemento, etc. E permite optimizar esas querys co Visual Explain Plan
  • a migración. Permite realizar a mesma de versióna anteiores de MySQL a máis recentes, ou a outros sistemas xestores de bases de datos coma Microsoft SQL Server, Microsoft Access, Sybase ASE, PostreSQL, etc, tanto en contornas Windows coma en outras

De outros proveedores

Dentro do software libre, son moi coñecidas estas dúas ferramentas:

Como software comercial, podemos indicar o uso de:

No anterior epígrafe, falamos de MySQL workbench, por ser a ferramenta visual de MySQL. Pero é común que cada SXBD teña a súa propia ferramenta. Por exemplo:

Linguaxe de definición de datos (DDL)

A DDL (Data Definition Language ou linguaxe de definición de datos) é a parte de SQL adicada á definición dos datos. As sentencias DDL son as seguentes:

CREATE
utilízase para crear objetos como bases de datos, tablas, vistas, índices, triggers e procedementos almacenados
DROP
utilízase para eliminar os obxectos da base de datos
ALTER
utilízase para modificar os obxectos da base de datos
SHOW
utilízase para consultar os obxectos da base de datos

Outras sentenzas de utilidade relacionadas son:

USE
utilízase para indicar a base de datos coa que queremos traballar
DESCRIBE
utilízase para amosar información sobre a estrutura dunha táboa

Crear/eliminar/modificar unha base de datos

Crear

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] nome_base_datos;
DATABASE e SCHEMA son sinónimos.
IF NOT EXISTS crea la base de datos sólo si no existe una base de datos con el mismo nombre.
 --Exemplo.
 CREATE DATABASE nome_base_datos;
Nota. Se non especificamos o set de caracteres na creación da base de datos, usarase latin1 por defecto.
As bases de datos que crearemos usarán o set de caracteres utf8 o utf8mb4.
CREATE DATABASE nome_base_datos CHARACTER SET utf8;

Eliminar

DROP {DATABASE | SCHEMA} [IF EXISTS] nome_base_datos;
DATABASE y SCHEMA son sinónimos.
IF EXISTS elimina la la base de datos sólo si ya existe.
 --Exemplo.
 DROP DATABASE nome_base_datos;

Modificar unha base de datos

ALTER {DATABASE | SCHEMA} [nombre_base_datos] alter_specification [, alter_especification] ...
 --Exemplo.
 ALTER DATABASE nome_base_datos CHARACTER SET utf8;

Consultar a listaxe de bases de datos disponibles

 SHOW DATABASES;

Amosa un listado con todas as bases de datos as que ten acceso ó usuario co que conectamos a MySQL.

Seleccionar unha base de datos

 -- Exemplo.
 USE nome_base_datos;

Emprégase para indicar a base de datos coa que queremos traballar

Amosar a sentenza SQL de creación dunha base de datos

 -- Exemplo.
 SHOW CREATE DATABASE nome_base_datos;

Pódese utilizar para visualizar a sentenza SQL que sería necesaria executar para crear a base de datos que lle estamos indicando como parámetro.

Crear/eliminar/modificar táboas

Crear

A continuación amosase unha versión simplificada da sintaxe necesaria para a creación dunha táboa en MySQL.

Para unha definición más exhaustiva:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
   (create_definition,...)
   [table_options]

create_definition:
   col_name column_definition
 | [CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,...)
 | [CONSTRAINT [symbol]] FOREIGN KEY (index_col_name,...) reference_definition
 | CHECK (expr)

column_definition:
   data_type [NOT NULL | NULL] [DEFAULT default_value]
     [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]

reference_definition:
   REFERENCES tbl_name (index_col_name,...)
     [ON DELETE reference_option]
     [ON UPDATE reference_option]

reference_option:
   RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
   table_option [[,] table_option] ...

table_option:
   AUTO_INCREMENT [=] value
 | [DEFAULT] CHARACTER SET [=] charset_name
 | [DEFAULT] COLLATE [=] collation_name
 | ENGINE [=] engine_name
 1  --Exemplo:
 2  Create Table pedidos
 3  (
 4  id_pedido INT(4),
 5  id_cliente INT(4) ,
 6  id_artigo INT(4),
 7  data DATE,
 8  cantidade INT(4),
 9  total INT(4)
10  )

Restricciones sobre as columnas da táboa

Podemos aplicar as seguintes restriccións sobre as columnas da táboa:

NOT NULL ou NULL: Indica se a columna permite almacenar valores nulos ou non
DEFAULT: Permite indicar un valor inicial por defecto se non especificamos ningún na inserción
AUTO_INCREMENT: Serve para indicar que é unha columna autonumérica. O seu valor increméntase automáticamente en cada inserción dunha fila. So se utiliza en campos de tipo entero
UNIQUE KEY: Indica que o valor da columna é único e non poden aparecer dous valores iguais na mesma columna
PRIMARY KEY: Para indicar que unha columna ou varias son clave primaria
CHECK: Nos permite realizar restriccións sobre unha columna

Exemplo 1:

 DROP DATABASE IF EXISTS proveedores;
 CREATE DATABASE proveedores CHARSET utf8mb4;
 USE proveedores;

 CREATE TABLE categoria (
  codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(100) NOT NULL
 );

 CREATE TABLE peza (
  codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(100) NOT NULL,
  color VARCHAR(50) NOT NULL,
  precio DECIMAL(7,2) NOT NULL CHECK (precio > 0),  
  codigo_categoria INT UNSIGNED NOT NULL,
  FOREIGN KEY (codigo_categoria) REFERENCES categoria(codigo)
 );

Exemplo 2:

 DROP DATABASE IF EXISTS axencia;
 CREATE DATABASE axencia CHARSET utf8mb4;
 USE axencia;

 CREATE TABLE turista (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(50) NOT NULL,
  apelidos VARCHAR(100) NOT NULL,
  enderezo VARCHAR(100) NOT NULL,
  telefono VARCHAR(9) NOT NULL
 );

 CREATE TABLE hotel (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(50) NOT NULL,
  enderezo VARCHAR(100) NOT NULL,
  cidade VARCHAR(25) NOT NULL,
  plazas INTEGER NOT NULL,
  telefono VARCHAR(9) NOT NULL
 );

 CREATE TABLE reserva (
  id_turista INT UNSIGNED NOT NULL,
  id_hotel INT UNSIGNED NOT NULL,
  fecha_entrada DATETIME NOT NULL,
  fecha_salida DATETIME NOT NULL,
  regimen ENUM('MP', 'PC'),
  PRIMARY KEY (id_turista,id_hotel),
  FOREIGN KEY (id_turista) REFERENCES turista(id),
  FOREIGN KEY (id_hotel) REFERENCES hotel(id)
 );

Opcións na declaración de claves alleas (FOREIGN KEY)

ON DELETE e ON UPDATE: permitennos indicar o efecto que provoca o borrado ou a actualización dos datos que están referenciados por claves alleas. As opcións que podemos especificar son as seguintes:
RESTRICT: Impide que se poidan actualizar ou eliminar as filas que teñen valores referenciados por claves alleas (opción por defecto en MySQL)
CASCADE: Permite actualizar ou eliminar as filas que teñen valores referenciados por claves alleas.
SET NULL: Asigna o valor NULL as filas que teñen valores referenciados por claves alleas
NO ACTION: É unha palabra clave do estándar SQL. En MySQL é equivalente a RESTRICT.
SET DEFAULT: Non é posible utilizar esta opción cando trabajamos co motor de almacenamento InnoDB (podes atopar máis información na documentación oficial de MySQL)

Exemplo 1:

 DROP DATABASE IF EXISTS proveedores;
 CREATE DATABASE proveedores CHARSET utf8mb4;
 USE proveedores;

 CREATE TABLE categoria (
  codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(100) NOT NULL
 );

 CREATE TABLE peza (
  codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(100) NOT NULL,
  color VARCHAR(50) NOT NULL,
  precio DECIMAL(7,2) NOT NULL,  
  codigo_categoria INT UNSIGNED NOT NULL,
  FOREIGN KEY (codigo_categoria) REFERENCES categoria(codigo)
  ON DELETE RESTRICT
  ON UPDATE RESTRICT
 );

 INSERT INTO categoria VALUES (1, 'Categoria A');
 INSERT INTO categoria VALUES (2, 'Categoria B');
 INSERT INTO categoria VALUES (3, 'Categoria C');

 INSERT INTO peza VALUES (1, 'Peza 1', 'Blanco', 25.90, 1);
 INSERT INTO peza VALUES (2, 'Peza 2', 'Verde', 32.75, 1);
 INSERT INTO peza VALUES (3, 'Peza 3', 'Vermello', 12.00, 2);
 INSERT INTO peza VALUES (4, 'Peza 4', 'Azul', 24.50, 2);
Poderíamos borrar a Categoría A da táboa categoria?
E a Categoría C?
Poderíamos actualizar a Categoría A da táboa categoria?

Exemplo 2:

 DROP DATABASE IF EXISTS proveedores;
 CREATE DATABASE proveedores CHARSET utf8mb4;
 USE proveedores;

 CREATE TABLE categoria (
  codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(100) NOT NULL
 );

 CREATE TABLE peza (
  codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(100) NOT NULL,
  color VARCHAR(50) NOT NULL,
  precio DECIMAL(7,2) NOT NULL,  
  codigo_categoria INT UNSIGNED NOT NULL,
  FOREIGN KEY (codigo_categoria) REFERENCES categoria(codigo)
  ON DELETE CASCADE
  ON UPDATE CASCADE
 );

 INSERT INTO categoria VALUES (1, 'Categoria A');
 INSERT INTO categoria VALUES (2, 'Categoria B');
 INSERT INTO categoria VALUES (3, 'Categoria C');

 INSERT INTO peza VALUES (1, 'Peza 1', 'Blanco', 25.90, 1);
 INSERT INTO peza VALUES (2, 'Peza 2', 'Verde', 32.75, 1);
 INSERT INTO peza VALUES (3, 'Peza 3', 'Vermello', 12.00, 2);
 INSERT INTO peza VALUES (4, 'Peza 4', 'Azul', 24.50, 2);
Poderíamos borrar a Categoría A da táboa categoria?
Qué lle acontece ás pezas que pertencen a Categoría A despois de borrala?
Poderíamos actualizar a Categoría A da táboa categoria?
Qué lle acontece ás pezas que pertencen a Categoría A despois de actualizala?

Exemplo 3:

 1  DROP DATABASE IF EXISTS proveedores;
 2  CREATE DATABASE proveedores CHARSET utf8mb4;
 3  USE proveedores;
 4 
 5  CREATE TABLE categoria (
 6   codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 7   nome VARCHAR(100) NOT NULL
 8  );
 9 
10  CREATE TABLE peza (
11   codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
12   nome VARCHAR(100) NOT NULL,
13   color VARCHAR(50) NOT NULL,
14   precio DECIMAL(7,2) NOT NULL,  
15   codigo_categoria INT UNSIGNED,
16   FOREIGN KEY (codigo_categoria) REFERENCES categoria(codigo)
17   ON DELETE SET NUL
18   ON UPDATE SET NULL
19  );
20 
21  INSERT INTO categoria VALUES (1, 'Categoria A');
22  INSERT INTO categoria VALUES (2, 'Categoria B');
23  INSERT INTO categoria VALUES (3, 'Categoria C');
24 
25  INSERT INTO peza VALUES (1, 'Peza 1', 'Blanco', 25.90, 1);
26  INSERT INTO peza VALUES (2, 'Peza 2', 'Verde', 32.75, 1);
27  INSERT INTO peza VALUES (3, 'Peza 3', 'Vermello', 12.00, 2);
28  INSERT INTO peza VALUES (4, 'Peza 4', 'Azul', 24.50, 2);
Podríamos borrar a Categoría A da tabla categoria?
Qué lle ocurre as pezas que pertecen a Categoría A despois de borrala?
Poderíamos actualizar a Categoría A da táboa categoria?
Qué lle ocurre as pezas que pertenecen a Categoría A despois de actualizala?

Opcións a ter en conta na creación das taboas

Algunhas das opcións que podemos indicar durante a creación das táboas son as seguintes:

AUTO_INCREMENT: Permite indicar o valor inicial que vamos a usar no campo definido como AUTO_INCREMENT
CHARACTER SET: Especifica o set de caracteres que vamos a utilizar na táboa
COLLATE: Especifica o tipo de cotexamento que ímos a utilizar na táboa.
ENGINE: Especifica o motor de almacenamento que ímos a utilizar para a táboa. Os máis habituales en MySQL son InnoDB (por defecto) e MyISAM

Para coñeoer todas as opciones posibles podemos consultar a sintaxe de creación de táboas na documentación oficial de MySQL, xa que só foron mencionadas as de uso máis común, Tamén podemos atopar na documentación oficial de MySQL máis información sobre os diferentes motores de almacenamento disponibles en MySQL.

 --Exemplo
 -- Eliminación da base de datos proveedores se xa existe
 DROP DATABASE IF EXISTS proveedores;
 -- Creación con charset utf8mb4
 CREATE DATABASE proveedores CHARSET utf8mb4;
 -- Uso para continuar traballando con esa base de datos
 USE proveedores;

 CREATE TABLE categoria (
  codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(100) NOT NULL
 )  ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000;

 CREATE TABLE peza (
  codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(100) NOT NULL,
  color VARCHAR(50) NOT NULL,
  precio FLOAT NOT NULL,  
  codigo_categoria INT UNSIGNED NOT NULL,
  FOREIGN KEY (codigo_categoria) REFERENCES categoria(codigo)
 )  ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000;

Neste exemplo selecionnouse para cada unha das táboas as seguientes opcións de configuración:

  • InnoDB como motor de base de datos
  • utf8 coma o set de caracteres
  • o valor 1000 como valor inical para las columnas de tipo AUTO_INCREMENT

Eliminar unha taboa

DROP [TEMPORARY] TABLE [IF EXISTS] nome_taboa [, nome_taboa];
 --Exemplos
 -- Borra táboa nome_taboa (se non existe nome_taboa dará un erro)
 DROP TABLE nome_taboa;
 -- Borra táboa nome_taboa (se non existe non fará nada)
 DROP TABLE IF EXISTS nome_taboa;
 -- Borra táboas nome_taboa_1 e nome_taboa_"
 DROP TABLE nome_taboa_1, nome_taboa_2;

Modificar unha táboa

Moitas veces é preciso modificar os atributos dunha táboa, engadir novos campos ou eliminar outros.

Se a táboa non ten datos podemos eliminala e volver a creala cos cambios; pero cando se trata dunha táboa que xa conten datos temos que facer uso da senteza ALTER TABLE.

A continuación amósase a sintaxe precisa para a modificación dunha táboa en MySQL

Podes consultar:

ALTER TABLE tbl_name
   [alter_specification [, alter_specification] ...]
   [partition_options]
alter_specification:
   table_options
 | ADD [COLUMN] col_name column_definition
       [FIRST | AFTER col_name]
 | ADD [COLUMN] (col_name column_definition,...)
 | ADD {INDEX|KEY} [index_name]
       [index_type] (index_col_name,...) [index_option] ...
 | ADD [CONSTRAINT [symbol]] PRIMARY KEY
       [index_type] (index_col_name,...) [index_option] ...
 | ADD [CONSTRAINT [symbol]]
       UNIQUE [INDEX|KEY] [index_name]
       [index_type] (index_col_name,...) [index_option] ...
 | ADD FULLTEXT [INDEX|KEY] [index_name]
       (index_col_name,...) [index_option] ...
 | ADD SPATIAL [INDEX|KEY] [index_name]
       (index_col_name,...) [index_option] ...
 | ADD [CONSTRAINT [symbol]]
       FOREIGN KEY [index_name] (index_col_name,...)
       reference_definition
 | ALGORITHM [=] {DEFAULT|INPLACE|COPY}
 | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
 | CHANGE [COLUMN] old_col_name new_col_name column_definition
       [FIRST|AFTER col_name]
 | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
 | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
 | {DISABLE|ENABLE} KEYS
 | {DISCARD|IMPORT} TABLESPACE
 | DROP [COLUMN] col_name
 | DROP {INDEX|KEY} index_name
 | DROP PRIMARY KEY
 | DROP FOREIGN KEY fk_symbol
 | FORCE
 | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
 | MODIFY [COLUMN] col_name column_definition
       [FIRST | AFTER col_name]
 | ORDER BY col_name [, col_name] ...
 | RENAME {INDEX|KEY} old_index_name TO new_index_name
 | RENAME [TO|AS] new_tbl_name
 | {WITHOUT|WITH} VALIDATION
 | ADD PARTITION (partition_definition)
 | DROP PARTITION partition_names
 | DISCARD PARTITION {partition_names | ALL} TABLESPACE
 | IMPORT PARTITION {partition_names | ALL} TABLESPACE
 | TRUNCATE PARTITION {partition_names | ALL}
 | COALESCE PARTITION number
 | REORGANIZE PARTITION partition_names INTO (partition_definitions)
 | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
 | ANALYZE PARTITION {partition_names | ALL}
 | CHECK PARTITION {partition_names | ALL}
 | OPTIMIZE PARTITION {partition_names | ALL}
 | REBUILD PARTITION {partition_names | ALL}
 | REPAIR PARTITION {partition_names | ALL}
 | REMOVE PARTITIONING
 | UPGRADE PARTITIONING
index_col_name:
   col_name [(length)] [ASC | DESC]
index_type:
   USING {BTREE | HASH}
index_option:
   KEY_BLOCK_SIZE [=] value
 | index_type
 | WITH PARSER parser_name
 | COMMENT 'string'
table_options:
   table_option [[,] table_option] ...
table_option:
   AUTO_INCREMENT [=] value
 | AVG_ROW_LENGTH [=] value
 | [DEFAULT] CHARACTER SET [=] charset_name
 | CHECKSUM [=] {0 | 1}
 | [DEFAULT] COLLATE [=] collation_name
 | COMMENT [=] 'string'
 | COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}
 | CONNECTION [=] 'connect_string'
 | {DATA|INDEX} DIRECTORY [=] 'absolute path to directory'
 | DELAY_KEY_WRITE [=] {0 | 1}
 | ENCRYPTION [=] {'Y' | 'N'}
 | ENGINE [=] engine_name
 | INSERT_METHOD [=] { NO | FIRST | LAST }
 | KEY_BLOCK_SIZE [=] value
 | MAX_ROWS [=] value
 | MIN_ROWS [=] value
 | PACK_KEYS [=] {0 | 1 | DEFAULT}
 | PASSWORD [=] 'string'
 | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
 | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
 | STATS_PERSISTENT [=] {DEFAULT|0|1}
 | STATS_SAMPLE_PAGES [=] value
 | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
 | UNION [=] (tbl_name[,tbl_name]...)
partition_options:
   (see CREATE TABLE options)

Exemplo de ALTER TABLE <tbl_name> MODIFY

MODIFY permitenos modificar o tipo de dato dunha columna e os seus atributos.

Se temos a seguinte táboa creada:

 CREATE TABLE usuario (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(25)
);

E queremos modificar a columna nome para que poida almacenar 50 caracteres e ademáis que sexa NOT NULL, usaríamos a sentenza:

 ALTER TABLE usuario MODIFY nombre VARCHAR(50) NOT NULL;

Despois de executar esta sentenza a táboa quedaría así:

 CREATE TABLE usuario (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(50) NOT NULL
);

Exemplo de ALTER TABLE <tbl_name> CHANGE

CHANGE permitenos:

  • renomear unha columna
  • modificar o tipo de dato dunha columna
  • modificar os seus atributos

Temos a seguinte táboa creada:

 CREATE TABLE usuario (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome_de_usuario VARCHAR(25)
 );

E queremos renomear o nome da columna nome_de_usuario coma nome, que poida almacenar 50 caracteres e ademáis que sexa NOT NULL. Usaremos a sentenza:

 ALTER TABLE usuario CHANGE nome_de_usuario nome VARCHAR(50) NOT NULL;

Despois de executar esta sentenza a táboa quedaría así:

 CREATE TABLE usuario (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(50) NOT NULL
);

Exemplo de ALTER TABLE <tbl_name> ALTER

ALTER permítenos asignar un valor por defecto a unha columna ou eliminar o valor por defecto que teña establecido.

Temos a seguinte táboa creada:

1  CREATE TABLE usuario (
2   id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
3   nome VARCHAR(50) NOT NULL,
4   sexo ENUM('H', 'M') NOT NULL
5  );

E queremos que o valor por defecto da columna sexo sexa M. Usaremos a sentenza:

1  ALTER TABLE usuario ALTER sexo SET DEFAULT 'M';

Despois de executar esta sentenza a táboa quedaría así:

1  CREATE TABLE usuario (
2   id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
3   nombre VARCHAR(50) NOT NULL,
4   sexo ENUM('H', 'M') NOT NULL DEFAULT 'M'
5  );

Se aora quixeramos eliminar o valor por defecto da columna sexo, usamos a seguinte sentenza:

1  ALTER TABLE usuario ALTER sexo DROP DEFAULT;

Exemplo de ALTER TABLE <tbl_name> ADD

ADD permitenos engadir novas columnas a unha táboa. 
Cos modificadores FIRST e AFTER podemos elixir o lugar da táboa onde queremos insertar a nova columna. 
FIRST coloca a nova columna en primeiro lugar e AFTER a coloca detrás da columna que se especifique. 
Se non se especifica nada a nova columna engádese detrás da última columna da táboa

Supoñemos que temos a seguinte táboa creada:

1  CREATE TABLE usuario (
2   id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
3   nome VARCHAR(50) NOT NULL,
4   sexo ENUM('H', 'M') NOT NULL
5  );

E queremos engadir a columna data_nacemento de tipo DATE:

1  ALTER TABLE usuario ADD data_nacimento DATE NOT NULL;

Neste caso a nova columna engádese detrás da última columna, sexo. A táboa quedaría así:

1  CREATE TABLE usuario (
2   id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
3   nome VARCHAR(50) NOT NULL,
4   sexo ENUM('H', 'M') NOT NULL,
5   data_nacemento DATE NOT NULL
6  );

Aora queremos engadir as columnas apelido1 e apelido2 detrás da columna nome.

1  ALTER TABLE usuario ADD apelido1 VARCHAR(50) NOT NULL AFTER nome;
2  ALTER TABLE usuario ADD apelido2 VARCHAR(50) AFTER apelido1;

Despois de executar todas as sentenzas á taboa quedaría así:

1  CREATE TABLE usuario (
2   id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
3   nome VARCHAR(50) NOT NULL,
4   apelido1 VARCHAR(50) NOT NULL,
5   apelido2 VARCHAR(50),
6   sexo ENUM('H', 'M') NOT NULL DEFAULT 'M',
7   data_nacemento DATE NOT NULL
8  );

Exemplo de ALTER TABLE <tbl_name> DROP

DROP permítenos eliminar unha columna da táboa.

Temos a seguinte táboa creada:

1  CREATE TABLE usuario (
2   id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
3   nome VARCHAR(50) NOT NULL,
4   apelido1 VARCHAR(50) NOT NULL,
5   apelido2 VARCHAR(50),
6   sexo ENUM('H', 'M') NOT NULL DEFAULT 'M',
7   data_nacemento DATE NOT NULL
8  );

E queremos eliminar a columna data_nacemento. Neste caso usaríamos a sentenza:

1  ALTER TABLE usuario DROP data_nacemento;

Despois de executar esta sentenza a táboa quedaría así:

1  CREATE TABLE usuario (
2   id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
3   nome VARCHAR(50) NOT NULL,
4   apelido1 VARCHAR(50) NOT NULL,
5   apelido2 VARCHAR(50),
6   sexo ENUM('H', 'M') NOT NULL DEFAULT 'M'
7  );

Consultar o listado de taboas disponibles

1  SHOW TABLES;

Amosa unha listaxe de todas as taboas que existen na base de datos coa que estamos traballando.

Amosar información sobre a estrutura dunha táboa

1  DESCRIBE nome_taboa;
2 
3  --Tamén podemos utilizar:
4  DESC nome_taboa;

Estas sentenzas utilízanse para amosar información sobre a estrutura dunha táboa.

Amosar a sentenza SQL de creación dunha táboa

SHOW CREATE TABLE nome_taboa;

Pódese utilizar para visualizar a sentenza SQL que sería precisa executar para crear a tabla que lle estamos indicando como parámetro.

Tipos de datos

Os tipos de datos, aínda que con características semellantes e cuasiequivalentes en moitos casos, son específicos de cada SXBD.

Adoitan clasificarse por categorías. Son máis ou menos as mesmas para cada SXBD (pero con todo cada SXBD terá as súas especificidades), e concretamente para MySQL:

  • Numericas
  • De data e hora
  • De cadea (String)
  • Espaciais
  • De tipo JSON

Os de MySQL pódense consultar no manual de referencia

Podes consultar na web de w3schools de forma resumida as mesmas, comparando cos tipos de SQL Server e MS Access.

Tipos de datos definidos polo usuario

Algúns SXBD, coma SQL Server, permiten o uso de tipos de datos definidos polo usuario. Podes atopar algúns exemplos de cómo levalo a cabo:

Índices

Os índices en MySQL permiten localizar e devolver rexistros dunha forma sinxela e rápida.

Son especialmente útiles cuando queremos buscar elementos de entre os millóns e ata billóns de rexistros que pode conter unha táboa nun momento dado. Para moitos programadores, o manexo de índices e un asunto complexo e difícil de comprender. Algunhas veces parece funcionar ben; outras o sistema degrádase en rendemiento co tempo e o incremento de datos, Mais todo ten unha explicación e debemos saber que unha elección intelixente adoita mellorar significativamente a velocidade de consulta a nosa base de datos.

Debemos contar cun número significativo de rexistros nunha táboa para que os índices se noten realmente; pero non é precixo que sexan millóns.

Creando una boa estratexia de índices podemos mellorar sensiblemente a velocidade das consultas en táboas con varios miles de reistros. Claro está que a mellora adoita ser proporcional e depende moito desa cantidade de rexistros. Pola contra un uso inadecuado deles pode redundar en perda de rendemento.

Expliquemos o seu funcionamento cunha consulta como esta:

SELECT * FROM personas WHERE apelido="zaragoza"

Sen ningunha orde nos nodos datos, o xestor debe ler todos os rexistros da táboa "personas" e efectuar unha comparación entre o campo "apelido" e a cadea de caracteres "zaragoza" para atopar algunha coincidencia (na vida real haberá moitas coincidencias). A medida que esta base de datos sufra modificacións, coma un incremento no numero de rexistros, a consulta irá requirindo un maior esforzo da CPU e no uso de memoria necesaria para executarse.

Se tiveramos unha guía telefónica a man localizaríamos fácilmente a calquiera con apelido "zargoza" buscando no final da guía pola letra "Z". O método en sí está dado en función a como están ordenados os datos e no coñecemento dos mssmos. Dito doutro modo, localizamos rápidamente a "zaragoza" porque está ordenado por apelido e porque coñecemos o abecedario.

Se abrimos un libro técnico observamos que posúe un índice ó final do libro, contido por térmos ou conceptos importantes co seu correspondente numero de páxina. Se sabemos de qué trata o libro buscamos a palabra que nos interesa e atopamos a expresión xunto co seu número de páxina.

Os índices de base de datos son moi similares. De igual xeito que o escritor decide crear un índice de termos e conceptos importantes do seu libro, como administradores dunha base de datos decidimos crear un índice respecto a unha columna.

Creando índices en MySQL

Usando o exemplo inicial, para que a consulta anterior se executase máis rápido no noso sistema xestor de base de datos, interésanos crear un índice por apelido, cunha sentenza así:

1 ALTER TABLE personas ADD INDEX (apelido)

Así indicámoslle a MySQL que xenere unha lista ordeada de todos os apelidos da táboa personas.

Os índices desde a perspectiva do servidor de base de datos

Os índices almacenanse de forma que a base de datos (ou motor) poida eliminar rexistros ou filas determinadas do resultado dunha consulta executada; son dinámicos e a súa xestión é transparente para o usuario e o programador.

Sen ningunha Indexación, MySQL (e calquer outro xestor de base de datos) lee cada registro, consumindo tempo, utilizando moitas operacións de Entrada/Saída no disco e podendo incluso chegar a corromper o sistema de caché do servidor.

Un aspecto moi importante: non é recomendable crear un índice por cada columna dunha táboa!! MySQL necesita ter unha lista separada dos valores de índice e actualizalos conforme van cambiando. Cantos máis índices teñamos máis tempo consumirá nesa tarefa.

Porén, o manexo de índices require un equilibrio axeitado entre espazo de almacenamento e tempo. Unha táboa cun campo indexado de MySQL usa máis espazo e xeralmente un BIT extra (polo menos ata a versión 5 de MySQL) para as consultas.

Vantaxes e desvantaxes

Vantaxes:

  • a principal é que cando Mysql atopa un índice evitamos un "escaneo completo da táboa"; o que fai unha mellora considerable en tempo de resposta cando temos grandes cantidades de datos nas nosas táboas
  • e relacionado co anterior, ó evitar "escaneos completos das táboas" evitamos os seguintes problemas: sobrecarga de CPU e de disco, e problemas de concurrencia
  • evitamos que Mysql teña que facer lecturas secuenciales
  • será unha vantaxe para aqueles campos que non teñan datos duplicados; non así para os campos con valores que se repiten continuamente (por exemplo Masculino/Femenino), onde non é aconsellable

Desvantaxes (non debemos abusar deles xa que en determinadas situacións non suporán unha mellora):

  • non recomendables naquelas taboas nas que se utilizan frecuentemente operacións de escritura (Insert, Delete, Update); isto é porque os índices actualízanse cada vez que se modifica unha columna
  • non recomendables en táboas demasiado pequenas posto que non precisamos gañar tempo nas consultas
  • non son moi aconsellables cando pretendemos que a táboa sobre a que se aplica devolva unha gran cantidade de datos en cada consulta
  • hai que ter en conta que ocupan espazo (e en determinadas ocasións incluso máis espazo que os propios datos)

Tipos de índices

Os distintos tipos de índices que se poden crear e as condicións que deben cumprir son:

  • INDEX (NON-UNIQUE): este tipo de índice refírese a un índice normal, non único; implica que admite valores duplicados para a columna (ou columnas) que compoñen o índice. Non aplica ningunha restricción especial ós datos da columna (ou columnas) que compoñen o índice se non que se emprega simplemente para mellorar o tempo de execución das consultas.
  • UNIQUE: este tipo de índice refírese a un índice no que todas as columnas deben ter un valor único; implica que non admite valores duplicados para a columna (ou columnas) que compoñen o índice. Aplica a restricción de que os datos da columna (ou columnas) deben ter un valor único.
  • PRIMARY: este tipo de índice refírese a un índice no que todas as columnas deben ter un valor único (do mesmo xeito que no caso do índice UNIQUE) pero coa limitación de que só pode existir un índice PRIMARY en cada unha das táboas. Aplica a restricción de que os datos da columna (ou columnas) deben ter un valor único.
  • FULLTEXT: estos índices empréganse para realizar búsquedas sobre texto (CHAR, VARCHAR e TEXT). Estos índices compóñense por todas as palabras que están contidas na columna (ou columnas) que conteñen o índice. Non aplica ningunha restricción especial ós datos da columna (ou columnas) que forman o índice, xa que se emprega simplemente para mellorar o tempo de execución das consultas. Este tipo de índices só están soportados por InnoDB e MyISAM en MySQL 5.7.
  • SPATIAL: estos índices empréganse para realizar búsquedas sobre datos que compoñen formas xeométricas representadas no espazo. Este tipo de índices só están soportados por InnoDB e MyISAM en MySQL 5.7.

É importante destacar que todos estos índices poden construirse empregando unha ou máis columnas: a orde das columnas que se especifique é relevante para todos os índices agás para o FULLTEXT (xa que este índice mira en TODAS as columnas que compoñen o índice).

Para crear un índice empregarase a seguiente estrutura:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (index_col_name…) index_type;

index_name: nome do índice.
table_name: nome da táboa onde se vai crear o índice.
index_col_name: nome da columna (ou columnas) que formarán o índice.
index_type: tipo do índice; empregase con USING [BTREE | HASH].
 --Exemplo de creación de índice de tipo hash
 CREATE UNIQUE INDEX mi_indice_unico ON mi_tabla (mi_columna) USING HASH;

Estructuras nas que se almacenan os índices

Xa vistos os tipos de índices, veremos os distintos tipos de estruturas que se poden crear para almacenar os índices xunto coas características de cada unha delas:

  • B-TREE: este tipo de índice úsase para comparacións do tipo =, >, <, >=, <=, BETWEEN e LIKE (sempre e cando se utilice sobre constantes que non comecen por %). Para realizar búsquedas empregando este tipo de índice, empregarase calquera columna (ou conxunto de columnas) que formen o prefixo do índice. Por exemplo: se un índice está formado polas columnas [A, B, C], poderanse realizar búsquedas sobre: [A], [A, B] ou [A, B, C].
  • HASH: este tipo de índice só se usa para comparacións do tipo = ou <=>. Para este tipo de operacións son moi rápidos en comparación a outro tipo de estructura. Para realizar búsquedas empregando este tipo de índice, empregaranse todas as columnas que compoñen o índice.

Un índice pode ser almacenado en calquier tipo de estrutura pero, en función do uso que se lle vaia a dar, pode interesar crear o índice nun tipo determinado de estrutura ou en outro. Como norma xeral, un índice simpre se creará coa estructura de B-TREE, xa que é a estrutura máis empregada pola maioría de operacións.

 -- exemplo para mostrar información deos indíces da taboa tabla 
 show index from tabla;

Podes comprender en detalle a nivel teórico como funcionan as árbores B con este video ou con esta presentación. para MySQL, hai este exemplo comentado en video.

Referencias e créditos

Boletíns