¿Cómo se utiliza un índice en una tabla grande?

El índice en una tabla grande acelera significativamente las operaciones de selección de datos (SELECT), así como las operaciones de modificación (UPDATE, DELETE) cuando se utiliza la condición WHERE con columnas indexadas. Sin un índice, la base de datos debe escanear toda la tabla (escaneo completo de la tabla), lo cual es muy lento en grandes volúmenes de datos.

Al usar un índice, la base de datos primero accede a la estructura del índice (generalmente un árbol B), que es mucho más pequeña que la tabla misma y contiene referencias ordenadas a las filas de datos. Al encontrar las referencias necesarias en el índice, la base de datos accede rápidamente directamente a las filas requeridas.

Importante:

• Los índices ocupan espacio en disco.
• La creación y mantenimiento de índices durante operaciones de inserción (INSERT) y actualización (UPDATE) requiere recursos adicionales.
• Demasiados índices o índices mal elegidos pueden ralentizar las operaciones de escritura.

Ejemplo de creación de índice en Ruby on Rails:

# Creación de un índice en la columna 'email' en la tabla 'users'
add_index :users, :email

# Creación de un índice único en la columna 'username'
add_index :users, :username, unique: true

# Creación de un índice compuesto en las columnas 'user_id' y 'product_id'
add_index :orders, [:user_id, :product_id]

Ejemplo de uso de un índice en SQL (ejecutado por la base de datos):

-- Consulta que utilizará el índice en la columna email
SELECT * FROM users WHERE email = 'test@example.com';