sql - tipos - ¿Por qué no puedo simplemente agregar un índice que incluya todas las columnas?

indices con columnas incluidas sql server

Question

Tengo una tabla en la base de datos de SQL Server que quiero poder buscar y recuperar datos lo más rápido posible. No me importa cuánto tiempo lleva insertarse en la mesa, solo me interesa la velocidad a la que puedo obtener datos.

El problema es que se accede a la tabla con 20 o más tipos diferentes de consultas. Esto hace que sea una tarea tediosa agregar un índice especialmente diseñado para cada consulta. En cambio, estoy considerando simplemente agregar un índice que incluya TODAS las columnas de la tabla. No es algo que normalmente haría en el "buen" diseño de la base de datos, así que supongo que hay una buena razón por la que no debería hacerlo.

¿Alguien puede decirme por qué no debería hacer esto?

ACTUALIZACIÓN: Olvidé mencionar, tampoco me importa el tamaño de mi base de datos. Está bien que signifique que el tamaño de mi base de datos crecerá más de lo necesario para

Answer 1

En cambio, estoy considerando simplemente agregar un índice que incluya TODAS las columnas de la tabla.

Esto siempre es una mala idea Los índices en la base de datos no son una especie de polvo de duendecillo que funciona mágicamente. Debe analizar sus consultas y de acuerdo con qué y cómo se está consultando: agregue índices.

No es tan simple como "agregar todo para indexar y tomar una siesta"

Answer 2

... si agrega un índice que contiene todas las columnas y una consulta puede usar ese índice, lo escaneará en el orden de la clave principal. Lo que significa golpear casi todos los registros. El tiempo promedio de búsqueda sería O (n / 2) .. lo mismo que llegar a la base de datos real.

Necesita leer un poco acerca de los índices.

Podría ser útil considerar un índice en una tabla como un diccionario en C #.

var nameIndex = new Dictionary<String, List<int>>();

Eso significa que la columna de nombre está indexada y devolverá una lista de claves principales.

var nameOccupationIndex = new Dictionary<String, List<Dictionary<String, List<int>>>>();

Eso significa que las columnas de columna + ocupación de nombre están indexadas. Ahora imagina que el índice contiene 10 columnas diferentes, anidadas tan profundamente que contiene cada fila de tu tabla.

Esto no es exactamente cómo funciona, te importa. Pero debería darle una idea de cómo podrían funcionar los índices si se implementara en C #. Lo que debe hacer es crear índices basados ​​en una o dos claves que se consulten exhaustivamente, de modo que el índice sea más útil que escanear toda la tabla.

Answer 3

1) tamaño, un índice construye esencialmente una copia de los datos en esa columna, una estructura de fácil búsqueda, como un árbol binario (no conozco las especificaciones de SQL Server). 2) Usted mencionó la velocidad, las estructuras de índice son más lentas para agregar.

Answer 4

En primer lugar, un índice en SQL Server solo puede tener 900 bytes como máximo en su entrada de índice. Solo eso hace que sea imposible tener un índice con todas las columnas.

Sobre todo: tal índice no tiene ningún sentido. ¿¿Qué estás intentando lograr??

Considere esto: si tiene un índice en (LastName, FirstName, Street, City) , ese índice no podrá ser utilizado para acelerar las consultas en

• FirstName solo
• City
• Street

Ese índice sería útil para búsquedas en

• (LastName) , o
• (LastName, FirstName) , o
• (LastName, FirstName, Street) , o
• (LastName, FirstName, Street, City)

pero realmente nada más, ¡ciertamente no si buscas solo Street o simplemente City !

El orden de las columnas en su índice hace una gran diferencia, y el optimizador de consultas no puede usar ninguna columna en algún lugar en el medio de un índice para las búsquedas.

Considere su directorio telefónico: es probable que sea por Apellido, Nombre, tal vez Calle. Entonces, ¿esa indexación te ayuda a encontrar todos los "Joe''s" en tu ciudad? Todas las personas que viven en "Main Street" ?? No, primero puede buscar por Apellido; luego se vuelve más específico dentro de ese conjunto de datos. Simplemente tener un índice sobre todo no ayuda a acelerar la búsqueda de todas las columnas.

Si desea poder buscar por Street , necesita agregar un índice por separado en (Street) (y posiblemente otra columna o dos que tengan sentido).

Si desea poder buscar por Occupation o cualquier otra cosa, necesita otro índice específico para eso.

¡El hecho de que su columna exista en un índice no significa que acelerará todas las búsquedas de esa columna!

La regla principal es: use la menor cantidad de índices posible: demasiados índices pueden ser incluso peores para un sistema que no tener ningún índice ... construya su sistema, monitoree su desempeño y encuentre las consultas que más cuestan, entonces optimícelos, por ejemplo, agregando índices.

No solo indexe ciegamente cada columna solo porque puede (esto es una garantía para el mal funcionamiento del sistema) cualquier índice también requiere mantenimiento y mantenimiento, de modo que cuantos más índices tenga, más sufrirán sus operaciones de INSERTAR, ACTUALIZAR y ELIMINAR (obtener más lento) ya que todos esos índices necesitan ser actualizados.

Answer 5

Ese índice sería idéntico a su tabla (posiblemente ordenado en otro orden).
No acelerará tus consultas.

Answer 6

Está teniendo un malentendido fundamental sobre cómo funcionan los índices.

Lea esta explicación " cómo funcionan los índices de varias columnas ".

La siguiente pregunta que podría tener es por qué no crear un índice por columna, pero eso también es un callejón sin salida si trata de alcanzar el mejor rendimiento de selección.

Puede sentir que es una tarea tediosa , pero yo diría que es una tarea obligatoria para indexar con cuidado. La indexación descuidada contraataca, como en este ejemplo .

Nota: Estoy firmemente convencido de que la indexación correcta da sus frutos y sé que muchas personas tienen las mismas preguntas que usted. Es por eso que estoy escribiendo un libro gratis sobre eso. Los enlaces de arriba refieren las páginas que pueden ayudarlo a responder su pregunta. Sin embargo, es posible que también desee leerlo desde el beginning .

Answer 7

Si se trata de una operación de tipo almacén de datos donde las consultas están altamente optimizadas para las consultas de LECTURA, y si tiene 20 formas de diseccionar los datos, por ejemplo

WHERE cláusula implica ..

 Q1: status, type, customer
 Q2: price, customer, band
 Q3: sale_month, band, type, status
 Q4: customer
 etc

Y tiene absolutamente mucho espacio de almacenamiento rápido para quemar, luego, por supuesto, cree un índice para CADA columna individual, por separado . Entonces, una tabla de 20 columnas tendrá 20 índices, uno para cada columna individual . Probablemente podría decir que ignore las columnas de bits o las columnas de baja cardinalidad, pero como vamos tan lejos, ¿para qué molestarse? (Con esa advertencia). Simplemente se sentarán allí y agitarán la hora de ESCRIBIR, pero si no te importa esa parte de la imagen, entonces todos estamos bien.

Analice sus 20 consultas, y si tiene consultas calientes (las más recientes) que aún no van más rápido, planifíquelo usando SSMS (presione Ctrl-L) con una consulta en la ventana de consulta. Le dirá qué índice puede ayudar a las consultas, simplemente créelo; créelos a todos, recordando por completo que esto aumenta nuevamente el costo de escritura, el tamaño del archivo de respaldo, el tiempo de mantenimiento de la base de datos, etc.