tener - indices mysql

¿Cuál es la diferencia entre un escaneo de tabla y un escaneo de índice agrupado?

Question

Dado que tanto un Table Scan como un Clustered Index Scan escanean esencialmente todos los registros en la tabla, ¿por qué es supuestamente mejor un escaneo de índice agrupado?

Como ejemplo, ¿cuál es la diferencia de rendimiento entre los siguientes cuando hay muchos registros ?:

declare @temp table(
    SomeColumn varchar(50)
)

insert into @temp
select ''SomeVal''

select * from @temp

-----------------------------

declare @temp table(
    RowID int not null identity(1,1) primary key,
    SomeColumn varchar(50)
)

insert into @temp
select ''SomeVal''

select * from @temp

Answer 1

En una tabla sin un índice agrupado (una tabla de acumulación), las páginas de datos no están vinculadas entre sí, por lo que recorrer páginas requiere una búsqueda en el Mapa de asignación de índices .

Sin embargo, una tabla agrupada tiene sus páginas de datos enlazadas en una lista doblemente vinculada , lo que hace que los escaneos secuenciales sean un poco más rápidos. Por supuesto, a cambio, usted tiene la responsabilidad de tratar de mantener las páginas de datos en orden en INSERT , UPDATE y DELETE . Sin embargo, una tabla de acumulación requiere una segunda escritura en el IAM.

Si su consulta tiene un operador RANGE (por ejemplo: SELECT * FROM TABLE WHERE Id BETWEEN 1 AND 100 ), entonces una tabla agrupada (estando en un orden garantizado) sería más eficiente, ya que podría usar las páginas de índice para encontrar los datos relevantes página (s) Un montón tendría que escanear todas las filas, ya que no puede confiar en el pedido.

Y, por supuesto, un índice agrupado le permite hacer una BUSQUEDA DE ÍNDICES CLUSTER, que es bastante óptima para el rendimiento ... un montón sin índices siempre daría como resultado una exploración de tabla.

Asi que:

• Para su consulta de ejemplo donde selecciona todas las filas, la única diferencia es la lista doblemente enlazada que mantiene un índice agrupado. Esto debería hacer que su tabla agrupada sea un poco más rápida que un montón con una gran cantidad de filas.

• Para una consulta con una cláusula WHERE que pueda ser (al menos parcialmente) satisfecha por el índice agrupado, saldrá adelante debido al pedido, por lo que no tendrá que escanear toda la tabla.

• Para una consulta que no está satisfecha con el índice agrupado, es casi incluso ... otra vez, la única diferencia es esa lista doblemente enlazada para el escaneo secuencial. En cualquier caso, no eres óptimo.

• Para INSERT , UPDATE y DELETE un montón puede o no ganar. El montón no tiene que mantener el orden, pero sí requiere una segunda escritura en el IAM. Creo que la diferencia de rendimiento relativa sería insignificante, pero también bastante dependiente de los datos.

Microsoft tiene un whitepaper que compara un índice agrupado con un índice no agrupado equivalente en un montón (no exactamente el mismo que mencioné anteriormente, pero que está cerca). Su conclusión es básicamente colocar un índice agrupado en todas las tablas. Haré todo lo posible para resumir sus resultados (una vez más, tenga en cuenta que en realidad están comparando un índice no agrupado con un índice agrupado, pero creo que es relativamente comparable):

• INSERT rendimiento: el índice agrupado gana aproximadamente un 3% debido a la segunda escritura necesaria para un montón.
• UPDATE rendimiento: el índice agrupado gana aproximadamente un 8% debido a la segunda búsqueda necesaria para un montón.
• DELETE rendimiento: el índice agrupado gana aproximadamente un 18% debido a la segunda búsqueda necesaria y la segunda eliminación necesaria del IAM para un montón.
• rendimiento único SELECT : el índice agrupado gana aproximadamente un 16% debido a la segunda búsqueda necesaria para un montón.
• rango SELECT rendimiento: el índice agrupado gana aproximadamente un 29% debido al orden aleatorio de un montón.
• INSERT concurrente: la tabla de acumulación gana un 30% con carga debido a divisiones de página para el índice agrupado.

Answer 2

Un escaneo de tabla tiene que examinar cada fila de la tabla. El análisis de índice agrupado solo necesita escanear el índice. No escanea cada registro en la tabla. Ese es el punto, realmente, de los índices.

Answer 3

http://msdn.microsoft.com/en-us/library/aa216840(SQL.80).aspx

El operador lógico y físico de Escaneo de índice agrupado escanea el índice agrupado especificado en la columna Argumento. Cuando está presente un predicado WHERE :() opcional, solo se devuelven aquellas filas que satisfacen el predicado. Si la columna Argument contiene la cláusula ORDERED, el procesador de consultas ha solicitado que se devuelva el resultado de las filas en el orden en que el índice agrupado las haya ordenado. Si la cláusula ORDERED no está presente, el motor de almacenamiento escaneará el índice de la manera óptima (no garantizando que la salida se clasifique).

http://msdn.microsoft.com/en-us/library/aa178416(SQL.80).aspx

El operador lógico y físico de Table Scan recupera todas las filas de la tabla especificada en la columna Argument. Si aparece un predicado WHERE :() en la columna Argument, solo se devuelven aquellas filas que satisfacen el predicado.