SQL
Índices

Introducción

Observaciones

Creando un índice

CREATE INDEX ix_cars_employee_id ON Cars (EmployeeId);

Esto creará un índice para la columna EmployeeId en la tabla Cars . Este índice mejorará la velocidad de las consultas que solicitan al servidor que ordene o seleccione los valores en EmployeeId , como los siguientes:

SELECT * FROM Cars WHERE EmployeeId = 1

El índice puede contener más de 1 columna, como en el siguiente;

CREATE INDEX ix_cars_e_c_o_ids ON Cars (EmployeeId, CarId, OwnerId);

En este caso, el índice sería útil para consultas que soliciten ordenar o seleccionar por todas las columnas incluidas, si el conjunto de condiciones se ordena de la misma manera. Eso significa que al recuperar los datos, puede encontrar las filas para recuperar usando el índice, en lugar de mirar a través de la tabla completa.

Por ejemplo, el siguiente caso utilizaría el segundo índice;

SELECT * FROM Cars WHERE EmployeeId = 1 Order by CarId DESC

Sin embargo, si el orden difiere, el índice no tiene las mismas ventajas, como en el siguiente;

SELECT * FROM Cars WHERE OwnerId = 17 Order by CarId DESC

El índice no es tan útil porque la base de datos debe recuperar el índice completo, en todos los valores de EmployeeId y CarID, para encontrar qué elementos tienen OwnerId = 17 .

(El índice puede seguir utilizándose; puede darse el caso de que el optimizador de consultas encuentre que recuperar el índice y filtrar en el OwnerId , luego recuperar solo las filas necesarias es más rápido que recuperar la tabla completa, especialmente si la tabla es grande).

Índices agrupados, únicos y ordenados

Los índices pueden tener varias características que se pueden establecer en la creación o alterando los índices existentes.

CREATE CLUSTERED INDEX ix_clust_employee_id ON Employees(EmployeeId, Email);  

La declaración SQL anterior crea un nuevo índice agrupado en los empleados. Los índices agrupados son índices que dictan la estructura real de la tabla; La tabla en sí está ordenada para que coincida con la estructura del índice. Eso significa que puede haber como máximo un índice agrupado en una tabla. Si ya existe un índice agrupado en la tabla, la declaración anterior fallará. (Las tablas sin índices agrupados también se denominan montones).

CREATE UNIQUE INDEX uq_customers_email ON Customers(Email);

Esto creará un índice único para la columna Correo electrónico en la tabla Clientes . Este índice, junto con la aceleración de consultas como un índice normal, también forzará que cada dirección de correo electrónico en esa columna sea única. Si se inserta o actualiza una fila con un valor de correo electrónico no único, la inserción o actualización fallará, de forma predeterminada.

CREATE UNIQUE INDEX ix_eid_desc ON Customers(EmployeeID);

Esto crea un índice en los clientes que también crea una restricción de tabla que indica que el EmployeeID debe ser único. (Esto fallará si la columna no es actualmente única; en este caso, si hay empleados que comparten una ID).

CREATE INDEX ix_eid_desc ON Customers(EmployeeID Desc);

Esto crea un índice que se ordena en orden descendente. De forma predeterminada, los índices (en el servidor MSSQL, al menos) son ascendentes, pero se pueden cambiar.

Inserción con un índice único

UPDATE Customers SET Email = "[email protected]" WHERE id = 1;

Esto fallará si se establece un índice único en la columna Correo electrónico de Clientes . Sin embargo, se puede definir un comportamiento alternativo para este caso:

UPDATE Customers SET Email = "[email protected]" WHERE id = 1 ON DUPLICATE KEY; 

SAP ASE: índice de caída

Este comando caerá índice en la tabla. Funciona en servidor SAP ASE .

Sintaxis:

DROP INDEX [table name].[index name]

Ejemplo:

DROP INDEX Cars.index_1

Índice ordenado

Si usa un índice que está ordenado de la forma en que lo recuperaría, la instrucción SELECT no haría una clasificación adicional cuando estaba en recuperación.

CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC);

Cuando ejecutas la consulta

SELECT * FROM scoreboard ORDER BY score DESC;

El sistema de base de datos no haría una clasificación adicional, ya que puede hacer una búsqueda de índice en ese orden.

Bajar un índice, o desactivarlo y reconstruirlo

DROP INDEX ix_cars_employee_id ON Cars;  

Podemos usar el comando DROP para borrar nuestro índice. En este ejemplo vamos a DROP el índice denominado ix_cars_employee_id en los coches de mesa.

Esto elimina el índice por completo, y si el índice está agrupado, eliminará cualquier agrupamiento. No se puede reconstruir sin volver a crear el índice, que puede ser lento y costoso computacionalmente. Como alternativa, el índice se puede desactivar:

ALTER INDEX ix_cars_employee_id ON Cars DISABLE; 

Esto permite que la tabla retenga la estructura, junto con los metadatos sobre el índice.

Críticamente, esto retiene las estadísticas del índice, de modo que es posible evaluar fácilmente el cambio. Si se justifica, el índice puede luego reconstruirse, en lugar de recrearse completamente;

ALTER INDEX ix_cars_employee_id ON Cars REBUILD;

Índice único que permite NULLS

CREATE UNIQUE INDEX idx_license_id 
   ON Person(DrivingLicenseID) WHERE DrivingLicenseID IS NOT NULL
GO

Este esquema permite una relación 0..1: las personas pueden tener cero o un permiso de conducir y cada licencia solo puede pertenecer a una persona

Índice de reconstrucción

A lo largo del tiempo, los índices B-Tree pueden fragmentarse debido a la actualización / eliminación / inserción de datos. En la terminología de SQLServer podemos tener interno (página de índice que está medio vacía) y externo (el orden lógico de la página no corresponde al orden físico). La reconstrucción del índice es muy similar a soltarlo y recrearlo.

Podemos reconstruir un índice con

ALTER INDEX index_name REBUILD; 

Por defecto, el índice de reconstrucción es una operación fuera de línea que bloquea la tabla y evita el uso de DML, pero muchos RDBMS permiten la reconstrucción en línea. Además, algunos proveedores de bases de datos ofrecen alternativas a la reconstrucción de índices como REORGANIZE (SQLServer) o COALESCE / SHRINK SPACE (Oracle).

Índice agrupado

Cuando se utiliza el índice agrupado, las filas de la tabla se ordenan por la columna a la que se aplica el índice agrupado. Por lo tanto, solo puede haber un índice agrupado en la tabla porque no puede ordenar la tabla por dos columnas diferentes.

En general, es mejor usar un índice agrupado cuando se realizan lecturas en tablas de big data. La parte negativa del índice agrupado es cuando se escribe en la tabla y los datos se deben reorganizar (reordenar).

Un ejemplo de creación de un índice agrupado en una tabla Empleados en la columna Empleado_ Apellido:

CREATE CLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

Índice no agrupado

Los índices no agrupados se almacenan por separado de la tabla. Cada índice en esta estructura contiene un puntero a la fila en la tabla que representa.

Estos punteros se llaman localizadores de fila. La estructura del localizador de filas depende de si las páginas de datos se almacenan en un montón o en una tabla agrupada. Para un montón, un localizador de fila es un puntero a la fila. Para una tabla agrupada, el localizador de filas es la clave de índice agrupado.

Un ejemplo de creación de un índice no agrupado en la tabla Empleados y columna Empleado_ Apellido:

CREATE NONCLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

Puede haber múltiples índices no agrupados en la tabla. Las operaciones de lectura son generalmente más lentas con índices no agrupados que con índices agrupados, ya que tiene que ir primero al índice y luego a la tabla. Sin embargo, no hay restricciones en las operaciones de escritura.

Indice parcial o filtrado

SQL Server y SQLite permiten crear índices que contienen no solo un subconjunto de columnas, sino también un subconjunto de filas.

Considere una cantidad creciente de pedidos con order_state_id igual a finalizado (2), y una cantidad estable de pedidos con order_state_id equal a iniciado (1).

Si su empresa utiliza consultas como esta:

SELECT id, comment
  FROM orders
 WHERE order_state_id =  1
   AND product_id = @some_value;

La indexación parcial le permite limitar el índice, incluidos solo los pedidos no finalizados:

CREATE INDEX Started_Orders
          ON orders(product_id)
       WHERE order_state_id = 1;

Este índice será más pequeño que un índice sin filtrar, lo que ahorra espacio y reduce el costo de actualización del índice.