Saltar al contenido

Tutorial de índice MySQL: crear, agregar y soltar

¿Qué es un índice?

Los índices en MySQL ordenan los datos de forma secuencial organizada. Se crean en las columnas que se utilizarán para filtrar los datos. Piense en un índice como una lista alfabética. Es más fácil ordenar los nombres alfabéticamente que editarlos.

El uso de un índice en tablas que se actualizan con frecuencia puede provocar un rendimiento deficiente. Esto se debe a que MySQL crea un nuevo bloque de índice cada vez que se agregan o actualizan datos en la tabla. Normalmente, los índices deben usarse en tablas cuyos datos no cambian con frecuencia pero que se usan mucho en consultas de búsqueda seleccionadas.

¿Qué utiliza Index?

A nadie le gustan los sistemas lentos. El alto rendimiento del sistema es extremadamente importante en casi todos los sistemas de bases de datos. La mayoría de las empresas invierten mucho en hardware para que la recuperación y manipulación de datos se pueda realizar más rápidamente. Pero existen límites para las inversiones en hardware que puede realizar una empresa. Optimizar su base de datos es una solución mejor y más barata.

El retraso en el tiempo de respuesta generalmente se debe a que los registros se almacenan aleatoriamente en las tablas de la base de datos. Las consultas de búsqueda deben recorrer los registros completos almacenados aleatoriamente uno tras otro para encontrar los datos deseados. Esto da como resultado bases de datos de bajo rendimiento para recuperar datos de tablas grandes. Por lo tanto, los índices que clasifican los datos se utilizan para facilitar la búsqueda.

Sintaxis: Crear índice

Los índices se pueden definir de 2 formas

  1. En el momento en que se creó una tabla
  2. Después de crear una tabla

Ejemplo:

Para nuestro myflixdb esperamos mucha búsqueda en la base de datos de nombres completos.

Agregaremos la columna «full_names» al índice en una nueva tabla «members_indexed».

El guión que se muestra a continuación nos ayuda a lograrlo.

CREATE TABLE `members_indexed` (
  `membership_number` int(11) NOT NULL AUTO_INCREMENT,
  `full_names` varchar(150) DEFAULT NULL,
  `gender` varchar(6) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `physical_address` varchar(255) DEFAULT NULL,
  `postal_address` varchar(255) DEFAULT NULL,
  `contact_number` varchar(75) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`membership_number`),INDEX(full_names)
) ENGINE=InnoDB;

Ejecute el script SQL anterior en MySQL workbench contra «myflixdb».

La actualización de myflixdb muestra la tabla recién creada llamada baill_indexed.

«Nota« El nodo de índice de la tabla members_indexed contiene «nombres_completos».

A medida que se expande la base de miembros y aumenta el número de registros, las consultas de búsqueda en la tabla members_indexed utilizando las cláusulas PLACE y ORDER serán mucho más rápidas en comparación con las realizadas por la tabla de no miembros del índice definido.

Agregar sintaxis de índice básica

El ejemplo anterior creó el índice al definir la tabla de la base de datos. Supongamos que ya tenemos una tabla definida y tiene consultas de búsqueda muy lentas. Tardan demasiado en devolver los resultados. Después de investigar el problema, descubrimos que podemos mejorar significativamente el rendimiento del sistema creando INDEX en la columna más utilizada en la cláusula PLACE.

Podemos usar la siguiente consulta para agregar un índice

CREATE INDEX id_index ON table_name(column_name);

Creemos que las consultas de búsqueda en la tabla de películas son muy lentas y queremos usar un índice de «título de película» para acelerar las consultas, podemos usar el siguiente script para lograrlo.

CREATE INDEX `title_index` ON `movies`(`title`);

La ejecución de la consulta anterior crea un índice del campo de título en la tabla de películas.

Esto significa que todas las consultas de búsqueda en la tabla de películas utilizarán el «título» más rápido.

Las consultas de búsqueda en otros campos de la tabla de películas serán incluso más lentas en comparación con las que se basan en el campo indexado.

Nota: Puede crear índices en varias columnas si es necesario, según los campos que desee utilizar para el motor de búsqueda de su base de datos.

Si desea ver los índices definidos en una tabla en particular, puede usar el siguiente script para hacerlo.

SHOW INDEXES FROM table_name;

Ahora miramos todos los índices definidos en la tabla de películas en myflixdb.

SHOW INDEXES FROM `movies`;

La ejecución del script anterior en un banco de trabajo MySQL contra myflixdb nos da resultados en el índice creado.

Nota: MySQL ya indexa las claves primarias y externas en la tabla. Cada índice tiene su propio nombre único y también se muestra la columna en la que está definido.

Sintaxis: Drop index

El comando desplegable se utiliza para eliminar índices ya definidos en una tabla.

Puede haber ocasiones en las que ya tenga un índice definido en una tabla que se actualiza con frecuencia. Es posible que desee eliminar los índices de dicha tabla para mejorar el rendimiento de las consultas UPDATE e INSERT. La siguiente es la sintaxis básica utilizada para dejar un índice en una tabla.

DROP INDEX `index_id` ON `table_name`;

Veamos ahora un ejemplo práctico.

DROP INDEX ` full_names` ON `members_indexed`;

Al ejecutar el comando anterior, se elimina el índice con id `full_names` de la tabla members_indexed.

Resumen

  • Los índices son muy poderosos para mejorar en gran medida el rendimiento de las consultas de búsqueda de MySQL.
  • Los índices pueden definirse al crear una tabla o agregarse más tarde después de que la tabla ya se haya creado.
  • Puede definir índices en más de una columna de una tabla.
  • SHOW INDEX FROM table_name se utiliza para mostrar los índices definidos en una tabla.
  • El comando DROP se usa para extraer un índice definido en una tabla en particular.

Entradas relacionadas

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *