Crear índice en MySQL: tutorial de índice MySQL [2022]
Publicado: 2021-01-01Un científico de datos o un programador tendrá que trabajar con grandes cantidades de datos y debe poder manejarlos de manera eficiente para una ejecución más rápida. También necesitan saber cómo se organizan los datos y los métodos más rápidos para acceder fácilmente a los datos particulares necesarios.
Dado que MySQL es un sistema de administración de bases de datos relacionales que tiene aplicaciones en varios sectores, como bases de datos web, aplicaciones de comercio electrónico, redes sociales, etc., los científicos de datos deben conocer todas las técnicas relacionadas con MySQL, una de las cuales son los índices. El artículo describe cómo crear un índice en MySQL.
Tabla de contenido
¿Qué es un índice?
En MySQL, los índices se utilizan para ordenar los datos de forma secuencial organizada y recuperar los datos de manera eficiente y rápida. Se crean utilizando una o más columnas que se utilizarán para filtrar datos. Los índices son un tipo de tabla que mantienen un campo principal o de índice y un puntero a cada registro de la tabla principal.
Para una mejor comprensión, se puede utilizar como ejemplo el índice de un libro. En lugar de buscar una página recorriendo todas las páginas de un libro, se puede consultar el índice del libro para encontrar la página que uno está buscando.
En la mayoría de los sistemas de bases de datos, se necesita un rendimiento alto y rápido. Muchas empresas invierten grandes sumas de dinero en hardware para una recuperación y manipulación de datos más rápida. Pero al optimizar la base de datos, los costos pueden reducirse.
El tiempo de respuesta suele ser más largo porque los registros se almacenan aleatoriamente y las consultas de búsqueda tienen que encontrar los datos necesarios recorriendo todos los registros almacenados. Los índices hacen que esta búsqueda sea más fácil y rápida.
Leer: Cambiar el nombre del nombre de la columna en SQL
Crear índice
Puede crear un índice en MySQL en una tabla usando la siguiente sintaxis básica.
CREAR ÍNDICE nombre_índice nombre_tabla (nombre_columna);
También puede crear un índice único en una tabla. Esto significa que el nombre es único y que dos filas no pueden tener el mismo nombre. La siguiente sintaxis se puede utilizar para esto.
CREAR ÍNDICE ÚNICO index_name ON table_name (column_name);
Si se crea un índice secundario para una columna, MySQL almacena los valores en una estructura de datos separada, como B-Tree y Hash. En MySQL, si las columnas son columnas de cadena, puede crear un índice para la parte inicial de los valores de columna de la columna de cadena usando la sintaxis:
nombre_columna(longitud)
Para crear la parte clave del prefijo de columna cuando se crea la tabla, se puede usar la siguiente sintaxis:
CREAR TABLA nombre_tabla(
columna_lista,
INDICE(nombre_columna(longitud))
);
Y para agregar un índice a una tabla existente:
CREATE INDEX index_name ON table_name (column_name(longitud));
La longitud aquí es el número de caracteres si es un tipo de cadena no binaria como CHAR, VARCHAR y TEXT y el número de bytes si es un tipo de cadena binaria como BINARY, VARBINARY y BLOB. También puede crear partes clave de prefijo de columna para las columnas CHAR, VARCHAR, BINARY y VARBINARY en MySQL. Es necesario especificar las partes clave del prefijo de la columna si ha creado índices para las columnas BLOB y TEXT.
En el caso de los índices FULLTEXT que solo son compatibles con las tablas InnoDB y MyISAM, solo pueden incluir columnas CHAR, VARCHAR y TEXT. No admite la indexación de prefijos de columna y, si se especifica alguna longitud de prefijo, se ignora.
Debe leer: Ideas de proyectos SQL
Alteración de tablas para agregar o quitar índice
Para agregar índices a una tabla, se pueden usar cuatro tipos de instrucciones:
- Para agregar una clave principal:
ALTER TABLE table_name ADD PRIMARY KEY (column_list);
Los valores indexados aquí deben ser únicos y no pueden ser NULL.
- Para un índice con valores únicos:
ALTER TABLE table_name ADD UNIQUE index_name (column_list);
Los valores deben ser únicos, excepto NULL, que puede aparecer varias veces.
- Para agregar un índice ordinario:
ALTER TABLE table_name ADD INDEX index_name (column_list);
Aquí los valores pueden aparecer más de una vez.
- Para crear un índice FULLTEXT especial:
ALTER TABLE table_name ADD FULLTEXT index_name (column_list);
El índice FULLTEXT se puede utilizar con fines de búsqueda de texto.
En tablas existentes
Para agregar un índice en cualquier tabla existente, se puede usar la siguiente sintaxis:
ALTER TABLE table_name ADD INDEX (index_name);
Para eliminar un índice en una tabla, se usa la instrucción DROP INDEX:
ALTER TABLE table_name DROP INDEX (index_name);
Mostrar información de índice
Para listar todos los índices asociados con cualquier tabla, se usa el comando SHOW INDEX.
MOSTRAR ÍNDICE DESDE table_name\G
La '\G' se usa para crear la lista en un formato vertical, esto evita el ajuste de línea larga.
Lea también: Preguntas y respuestas de la entrevista de SQL
Índices descendentes
InnoDB puede almacenar las entradas en orden descendente cuando el índice es un índice descendente. Entonces, cuando se solicita el orden descendente en la consulta, el optimizador elige este índice. Esto es más eficiente para consultas con cláusulas ORDER BY. Estos solo son compatibles con el motor de almacenamiento InnoDB.
La sintaxis para crear o agregar índices descendentes es como las sintaxis de modificación o creación utilizadas anteriormente.
ALTER TABLE table_name ADD index_name (col1_name desc, col2_name asc);
CREAR ÍNDICE index_name ON table_name (col1_name desc, col2_name asc);
Índices invisibles
Para marcar índices como no disponibles para el optimizador de consultas, se pueden usar índices invisibles. MySQL actualiza los índices invisibles cuando cambian los datos en las columnas asociadas con el índice. Los índices son visibles de forma predeterminada y para hacerlos invisibles, debe declarar explícitamente la visibilidad en el momento de la creación o mediante el comando ALTER TABLE.
Para crear un índice invisible, se utiliza la siguiente sintaxis:
CREAR ÍNDICE nombre_índice ON nombre_tabla (c1, c2…) INVISIBLE;
Para cambiar la visibilidad de los índices existentes, se utiliza la siguiente sintaxis:
ALTER TABLE nombre_tabla ALTER INDEX nombre_índice [VISIBLE | INVISIBLE];
Inscríbase en cursos de desarrollo de software en línea de las mejores universidades del mundo. Obtenga programas Executive PG, programas de certificados avanzados o programas de maestría para acelerar su carrera.
Conclusión
El rendimiento de las consultas de búsqueda de MySQL se puede mejorar mediante el uso de índices. Estos se pueden definir cuando se crea una tabla o se pueden agregar más tarde si la tabla ya se creó.
Este artículo dio una breve descripción de qué es un índice y por qué se usa. La siguiente sección explica cómo crear un índice en MySQL, cómo se puede agregar a una tabla existente y cómo crear un índice único.
También se describieron los diferentes comandos para modificar tablas y crear o eliminar un índice junto con la visualización de la información del índice. También se explicaron brevemente los índices descendentes e invisibles.
En general, aprender y practicar en todas las bases de datos mencionadas en este artículo se sumará a su experiencia. Revise la descripción general del curso, el aprendizaje y las oportunidades laborales relacionadas con él: plataformas como upGrad ofrecen cursos potentes diseñados por institutos de gran reputación como IIIT-B en Executive PG Program Full Stack Development.
