MySQL: Índices ▷ MySQL - Programar Plus

Los datos expuestos en este blog, son solo de índole informativo. Por favor realiza siempre una copia de seguridad antes de realizar cualquier cambio en tu proyecto.

MySQL: Índices

Este tutorial de MySQL explica cómo Crear, soltar y renombrar índices Sintaxis y ejemplos en MySQL.

¿Qué son los índices en MySQL?

La indexación es un método de ajuste del rendimiento que permite una recuperación más rápida de los registros. Un índice crea una entrada para cada valor que aparece en la columna indexada.

crear índice

Hay dos formas de crear un índice. Puede crear índices cuando crea una tabla por primera vez con la declaración CREATE TABLE, o puede usar la declaración CREATE INDEX después de crear la tabla.

sintaxis

La sintaxis para crear un índice usando la instrucción CREATE TABLE en MySQL es:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
  column_n datatype [ NULL | NOT NULL ],

  INDEX index_name [ USING BTREE | HASH ]
    (index_col1 [(length)] [ASC | DESC], 
     index_col2 [(length)] [ASC | DESC],
     ...
     index_col_n [(length)] [ASC | DESC])
);

La sintaxis para crear un índice usando la instrucción CREATE INDEX en MySQL es:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
  [ USING BTREE | HASH ]
  ON table_name
    (index_col1 [(length)] [ASC | DESC], 
     index_col2 [(length)] [ASC | DESC],
     ...
     index_col_n [(length)] [ASC | DESC]);

Solo opcional. El modificador ÚNICO indica que la combinación de valores en la columna indexada debe ser única. Texto completo opcional. El modificador FULLTEXT indexa toda la columna y no permite prefijos. Esta opción es compatible con tablas InnoDB y MyISAM. El espacio es opcional. El modificador SPATIAL indexa toda la columna y no permite que las columnas indexadas contengan valores NULL. Esta opción es compatible con las tablas InnoDB (a partir de MySQL 5.7) y MyISAM. index_name El nombre asignado al índice. table_name El nombre de la tabla en la que se va a crear el índice. index_col1, index_col2, … index_col_n Columnas a utilizar en el índice. La longitud es opcional. Si se especifica, solo se indexa el prefijo de la columna en lugar de la columna completa. Para columnas de cadenas no binarias, este valor es el número dado de caracteres de la columna para indexar. Para columnas de cadenas binarias, este valor es el número dado de bytes de la columna a indexar. ASC es opcional. El índice se ordena en orden ascendente por esa columna. DESC opcional. El índice se ordena en orden descendente por esa columna.

ejemplo

Veamos un ejemplo de cómo crear un índice en MySQL usando la sentencia CREATE TABLE. Esta instrucción creará tanto la tabla como el índice.

Por ejemplo:

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id),
  INDEX contacts_idx (last_name, first_name)
);

En este ejemplo, creamos la tabla de contactos y un índice llamado contactos_idx que consta de las columnas apellido y nombre.

A continuación, le mostraremos cómo crear primero la tabla y luego usar la declaración CREATE INDEX para crear el índice.

Por ejemplo:

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

CREATE INDEX contacts_idx
  ON contacts (last_name, first_name);

En este ejemplo, la instrucción CREATE TABLE creará la tabla de contactos. La declaración CREATE INDEX creará un índice llamado contactos_idx que consta de los campos apellido y nombre.

índice único

Para crear un índice único en una tabla, especifique la palabra clave ÚNICA al crear el índice. Nuevamente, esto se puede hacer con la declaración CREATE TABLE o la declaración CREATE INDEX.

Por ejemplo:

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id),
  UNIQUE INDEX contacts_idx (last_name, first_name)
);

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

CREATE UNIQUE INDEX contacts_idx
  ON contacts (last_name, first_name);

Ambos ejemplos crearán índices únicos en los campos last_name y first_name, por lo que la combinación de estos campos siempre debe contener valores únicos y no duplicados. Esta es una excelente manera de hacer cumplir la integridad en la base de datos si necesita valores únicos en columnas que no forman parte de la clave principal.

índice de caída

Puede eliminar índices en MySQL usando la instrucción DROP INDEX.

sintaxis

La sintaxis para eliminar un índice usando la instrucción DROP INDEX en MySQL es:

DROP INDEX index_name
  ON table_name;

index_name El nombre del índice a descartar. table_name El nombre de la tabla en la que se creó el índice.

ejemplo

Veamos un ejemplo de cómo soltar un índice en MySQL.

Por ejemplo:

DROP INDEX contacts_idx
  ON contacts;

En este ejemplo, eliminamos un índice llamado contactos_idx de la tabla de contactos.

renombrar índice

Puede cambiar el nombre de los índices en MySQL. Hay dos sintaxis diferentes dependiendo de su versión de MySQL.

sintaxis

La sintaxis para cambiar el nombre de un índice utilizando la declaración ALTER TABLE en MySQL 5.6 y versiones posteriores es:

ALTER TABLE table_name
  DROP INDEX index_name,
  ADD INDEX new_index_name [ USING BTREE | HASH ]
    (index_col1 [(length)] [ASC | DESC], 
     index_col2 [(length)] [ASC | DESC],
     ...
     index_col_n [(length)] [ASC | DESC]);

La sintaxis para cambiar el nombre de un índice en MySQL 5.7 o posterior es:

ALTER TABLE table_name
  RENAME INDEX index_name TO new_index_name;

table_name El nombre de la tabla en la que se creó el índice. index_name El nombre del índice para cambiar el nombre. new_index_name El nuevo nombre del índice.

ejemplo

Veamos un ejemplo de cómo renombrar un índice en MySQL. En versiones anteriores de MySQL, necesitaría usar la declaración ALTER TABLE para eliminar primero el índice anterior y luego volver a crear el índice nuevo.

Por ejemplo (MySQL 5.6 y anteriores):

ALTER TABLE contacts
  DROP INDEX contacts_idx,
  ADD INDEX contacts_new_index (last_name, first_name);

En este ejemplo, cambiamos el nombre del índice llamado contactos_idx a contactos_nuevo_índice. Esto se hace eliminando el índice anterior y luego agregando el nuevo.

A partir de MySQL 5.7, puede usar la declaración ALTER TABLE con la cláusula RENAME INDEX para cambiar el nombre de un índice.

Por ejemplo (MySQL 5.7 y posteriores):

ALTER TABLE contacts
  RENAME INDEX contacts_idx TO contacts_new_index;

Esto también cambia el nombre del índice de contactos_idx a contactos_nuevo_índice. Si no está seguro de qué versión de MySQL está ejecutando, es más seguro usar la primera sintaxis para cambiar el nombre de un índice.