¿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 ordenada alfabéticamente. Es más fácil buscar nombres que han sido ordenados alfabéticamente que aquellos que no están ordenados.
El uso de un índice en tablas que se actualizan con frecuencia puede resultar en 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. 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. El alto rendimiento del sistema es de suma 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 sea más rápida. Pero existe un límite para las inversiones en hardware que puede realizar una empresa. Optimizar su base de datos es una solución mejor y más barata.
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 formas
- En el momento de la creación de la mesa
- Después de que se haya creado la tabla
Para nuestro myflixdb esperamos muchas búsquedas en la base de datos por nombre completo.
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".
Al actualizar myflixdb, se muestra la tabla recién creada llamada members_indexed.
La tabla "Note" members_indexed tiene "full_names" en el nodo de índices.
A medida que la base de miembros se expande y aumenta el número de registros, las consultas de búsqueda en la tabla members_indexed que usan las cláusulas WHERE y ORDER BY serán mucho más rápidas en comparación con las que se realizaron 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 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 INDICE en la columna más utilizada en la cláusula WHERE.
Podemos usar 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 usar 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 del título en la tabla de películas.
Esto significa que todas las consultas de búsqueda en la tabla de películas que utilizan 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 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;
Echemos ahora un vistazo a todos los índices definidos en la tabla de películas en myflixdb.
SHOW INDEXES FROM `movies`;
La ejecución del script anterior en MySQL workbench contra myflixdb nos da resultados en el índice creado.
Nota: MySQL ya ha indexado las claves principal y externa de 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 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`;
Al ejecutar el comando anterior, se elimina el índice con id `full_names` de la tabla members_indexed.
Resumen
- Los índices son muy poderosos cuando se trata de mejorar en gran medida el rendimiento de las consultas de búsqueda de MySQL.
- Los índices se pueden definir al crear una tabla o agregarlos más adelante 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 utiliza para eliminar un índice definido en una tabla determinada.
