MySQL Tutorial de índice: crear, agregar y soltar

¿Qué es un índice?

Índices en MySQL ordenar 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 ordenada alfabéticamente. Es más fácil buscar nombres que han sido ordenados alfabéticamente que aquellos que no lo están.

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

¿Para qué sirve un índice?

A nadie le gustan los sistemas lentos. Un alto rendimiento del sistema es de primordial importancia 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 puedan ser más rápidas. Pero las inversiones en hardware que una empresa puede realizar tienen un límite. Optimizar su base de datos es una solución mejor y más económica.

La lentitud 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 todos los registros almacenados aleatoriamente, uno tras otro, para localizar los datos deseados. Esto da como resultado bases de datos de bajo rendimiento cuando se trata de recuperar datos de tablas grandes. Por lo tanto, se utilizan índices que clasifican los datos para facilitar la búsqueda.

Sintaxis: Crear índice

Los índices se pueden definir de 2 maneras.

1. En el momento de la creación de la tabla.
2. Después de crear la tabla

Ejemplo:

Para nuestro myflixdb esperamos muchas búsquedas en la base de datos con el nombre completo.

Agregaremos la columna "nombres_completos" al índice en una nueva tabla "miembros_indexados".

El script 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 banco de trabajo contra el “myflixdb”.

Al actualizar myflixdb se muestra la tabla recién creada llamada member_indexed.

"Nota" La tabla member_indexed tiene "nombres_completos" en el nodo de índices.

A medida que la base de miembros se expande y el número de registros aumenta, las consultas de búsqueda en la tabla de miembros_indexados que utilizan las cláusulas WHERE y ORDER BY serán mucho más rápidas en comparación con las realizadas en la tabla de miembros sin el índice definido.

Agregar sintaxis básica de índice

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

Podemos utilizar la siguiente consulta para agregar índice

CREATE INDEX id_index ON table_name(column_name);

Supongamos que las consultas de búsqueda en la tabla de películas son muy lentas y queremos utilizar un índice en el “título de la 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 en el 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 utilizando el "título" serán más rápidas.

Sin embargo, las consultas de búsqueda en otros campos de la tabla de películas seguirán siendo más lentas en comparación con las basadas 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 particular, puede utilizar el siguiente script para hacerlo.

SHOW INDEXES FROM table_name;

Ahora echemos un vistazo a todos los índices definidos en la tabla de películas en myflixdb.

SHOW INDEXES FROM `movies`;

Ejecutando el script anterior en MySQL banco de trabajo contra myflixdb nos da resultados sobre el índice creado.

Nota: Las claves primaria y externa de la tabla ya han sido indexadas por MySQL. Cada índice tiene su propio nombre único y también se muestra la columna en la que está definido.

Sintaxis: índice de caída

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

Puede haber ocasiones en las que ya haya definido un índice 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 ACTUALIZAR e INSERTAR. La sintaxis básica utilizada para colocar un índice en una tabla es la siguiente.

DROP INDEX `index_id` ON `table_name`;

Veamos ahora un ejemplo práctico.

DROP INDEX ` full_names` ON `members_indexed`;

La ejecución del comando anterior elimina el índice con id `full_names` de la tabla member_indexed.

Resum

• Los índices son muy poderosos cuando se trata de mejorar en gran medida el rendimiento de MySQL consultas de búsqueda.
• Los índices se pueden definir al crear una tabla o agregarse más tarde, cuando la tabla ya se ha creado.
• Puede definir índices en más de una columna de una tabla.
• MOSTRAR ÍNDICE DESDE nombre_tabla se utiliza para mostrar los índices definidos en una tabla.
• El comando DROP se utiliza para eliminar un índice definido en una tabla determinada.