Imagen del autor
Mientras buscamos un tema en particular en un libro, primero visitaremos la página de índice (que está presente al comienzo de ese libro) y encontraremos qué número de página contiene nuestro tema de interés. Ahora, imagine lo inconveniente que es encontrar un tema en particular en un libro sin la página de índice. Para esto, tenemos que buscar en cada página del libro, lo cual consume mucho tiempo y es frustrante.
También ocurre un problema similar en SQL Server cuando recupera datos de la base de datos. Para superar esto, el servidor SQL también utiliza la indexación que acelera el proceso de recuperación de datos, y en este artículo cubriremos esa parte. Cubriremos por qué es necesaria la indexación y cómo podemos crear y eliminar índices de manera efectiva. El requisito previo de este tutorial es el conocimiento básico de los comandos SQL.
La indexación es un objeto de esquema que utiliza un puntero para recuperar datos de las filas, lo que reduce el tiempo de E/S (entrada/salida) para localizar los datos. La indexación se puede aplicar a una o más columnas que queremos buscar. Almacenan la columna en una estructura de datos separada llamada Árbol B. Una de las principales ventajas de B-Tree es que almacena los datos en orden.
Si se pregunta por qué los datos se pueden recuperar más rápido si están ordenados, debe leer sobre Búsqueda lineal vs búsqueda binaria.
La indexación es uno de los métodos más famosos para mejorar el rendimiento de las consultas SQL. Son pequeños, rápidos y notablemente optimizados para tablas relacionales. Cuando queremos buscar una fila sin indexar, el SQL realiza un escaneo de tabla completa de forma lineal. En otras palabras, SQL tiene que escanear cada fila para encontrar las condiciones coincidentes, lo que lleva mucho tiempo. Por otro lado, la indexación mantiene los datos ordenados, como se mencionó anteriormente.
Pero también debemos tener cuidado, la indexación crea una estructura de datos separada que requiere espacio adicional y eso puede volverse problemático cuando la base de datos es grande. Como buena práctica, la indexación es efectiva solo en las columnas que se usan con frecuencia y se puede evitar en las columnas que se usan con poca frecuencia. A continuación se presentan algunos escenarios en los que la indexación podría ser útil,
- El número de filas debe ser (>10000).
- La columna requerida contiene una gran cantidad de valores.
- La columna requerida no debe contener una gran cantidad de valores NULL.
- Es útil si con frecuencia ordenamos o agrupamos datos en función de columnas particulares. La indexación recupera rápidamente los datos ordenados en lugar de realizar un análisis completo.
Y la indexación se puede evitar cuando,
- La mesa es pequeña.
- O cuando los valores de la columna rara vez se usan.
- O cuando los valores de las columnas cambian con frecuencia.
También puede haber una posibilidad cuando el optimizador detecta que un escaneo de tabla completa toma menos tiempo que la tabla indexada, entonces la indexación no se puede usar, incluso si existe. Esto puede suceder cuando la tabla es pequeña o la columna se actualiza con frecuencia.
Antes de comenzar, debe configurar MySQL Workbench en su PC para seguir fácilmente el tutorial. Puedes referirte a así video de youtube para configurar su banco de trabajo.
Después de configurar su banco de trabajo, crearemos algunos datos aleatorios a partir de los cuales podemos ejecutar nuestras consultas.
Crear tabla:
-- Create a table to hold the random data CREATE TABLE employee_info (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), age INT, email VARCHAR(100));
Insertar datos:
-- Insert random data into the table INSERT INTO employee_info (name, age, email)
SELECT CONCAT('User', LPAD(ROW_NUMBER() OVER (), 5, '0')), FLOOR(RAND() * 50) + 20, CONCAT('user', LPAD(ROW_NUMBER() OVER (), 5, '0'), '@xyz.com')
FROM information_schema.tables
LIMIT 100;
Creará una tabla llamada employee_info tener atributos como nombre, edad y correo electrónico.
Mostrar los datos:
SELECT *
FROM employee_info;
Salida:
Fig. 1 Base de datos de muestra | Imagen por autor
Para crear un índice, podemos usar el comando CREAR así,
Sintaxis:
CREATE INDEX index_name ON TABLE_NAME (COLUMN_NAME);
En la consulta anterior, index_name es el nombre del índice, table_name es el nombre de la tabla y el column_name es el nombre de la columna sobre la que queremos aplicar la indexación.
ex-
CREATE INDEX age_index ON employee_info (age);
También podemos crear índices para múltiples columnas en la misma tabla,
CREATE INDEX index_name ON TABLE_NAME (col1, col2, col3, ....);
Índice único: También podemos crear un índice único para una columna en particular que no permita que se almacenen valores duplicados en esa columna. Esto mantiene la integridad de los datos y también mejora aún más el rendimiento.
CREATE UNIQUE INDEX index_name ON TABLE_NAME (COLUMN_NAME);
Nota: Los índices se pueden crear automáticamente para las columnas PRIMARY_KEY y UNIQUE. No tenemos que crearlos manualmente.
Eliminación de un índice:
Podemos usar el comando DROP para eliminar un índice particular de la tabla.
DROP INDEX index_name ON TABLE_NAME;
Necesitamos especificar el índice y los nombres de las tablas para eliminar el índice.
Mostrar índices:
También puede ver todos los índices presentes en su tabla.
Sintaxis:
SHOW INDEX
FROM TABLE_NAME;
ex-
SHOW INDEX
FROM employee_info;
Salida:
El siguiente comando crea un nuevo índice en la tabla existente.
Sintaxis:
ALTER TABLE TABLE_NAME ADD INDEX index_name (col1, col2, col3, ...);
Nota: ALTER no es un comando estándar de ANSI SQL. Por lo que puede variar entre otras bases de datos.
Por ex-
ALTER TABLE employee_info ADD INDEX name_index (name); SHOW INDEX
FROM employee_info;
Salida:
En el ejemplo anterior, hemos creado un nuevo índice en la tabla existente. Pero no podemos modificar un índice existente. Para esto, primero debemos eliminar el índice anterior y luego crear uno nuevo modificado.
Por ex-
DROP INDEX name_index ON employee_info; CREATE INDEX name_index ON employee_info (name, email); SHOW INDEX
FROM employee_info ;
Salida:
En este artículo, hemos cubierto una comprensión básica de la indexación de SQL. También se recomienda mantener la indexación estrecha, es decir, limitada a unas pocas columnas, porque una mayor indexación puede afectar negativamente al rendimiento. La indexación nos acelera las consultas SELECT y la cláusula WHERE, pero ralentiza las declaraciones de inserción y actualización. Por lo tanto, aplicar la indexación solo en las columnas de uso frecuente es una buena práctica.
Hasta entonces, sigue leyendo y sigue aprendiendo.
Garg ario es un B.Tech. Estudiante de Ingeniería Eléctrica, actualmente en el último año de la carrera. Su interés radica en el campo del Desarrollo Web y el Aprendizaje Automático. Ha perseguido este interés y estoy ansioso por trabajar más en estas direcciones.
- Distribución de relaciones públicas y contenido potenciado por SEO. Consiga amplificado hoy.
- PlatoData.Network Vertical Generativo Ai. Empodérate. Accede Aquí.
- PlatoAiStream. Inteligencia Web3. Conocimiento amplificado. Accede Aquí.
- PlatoESG. Automoción / vehículos eléctricos, Carbón, tecnología limpia, Energía, Ambiente, Solar, Gestión de residuos. Accede Aquí.
- Desplazamientos de bloque. Modernización de la propiedad de compensaciones ambientales. Accede Aquí.
- Fuente: https://www.kdnuggets.com/2023/07/database-optimization-exploring-indexes-sql.html?utm_source=rss&utm_medium=rss&utm_campaign=database-optimization-exploring-indexes-in-sql
