una - ¿Debo indexar un campo de bit en SQL Server?
obtener los indices de una base de datos sql server (19)
Recuerdo haber leído en un momento que indexar un campo con baja cardinalidad (un número bajo de valores distintos) no vale realmente la pena. Admito que no sé lo suficiente sobre cómo funcionan los índices para entender por qué es así.
Entonces, ¿qué sucede si tengo una tabla con 100 millones de filas y estoy seleccionando registros donde un campo de bit es 1? Y digamos que en cualquier punto del tiempo, solo hay un puñado de registros donde el campo de bit es 1 (en oposición a 0). ¿Vale la pena indexar ese campo de bit o no? ¿Por qué?
Por supuesto, puedo probarlo y verificar el plan de ejecución, y lo haré, pero también me interesa la teoría que lo respalda. ¿Cuándo importa la cardinalidad y cuándo no?
"Recuerdo haber leído en un momento que indexar un campo con baja cardinalidad (un número bajo de valores distintos) no vale realmente la pena"
Eso porque SQL Server casi siempre encontrará que es más eficiente hacer un escaneo de tabla que leer el índice. Así que, básicamente, su índice nunca se utilizará y es un desperdicio mantenerlo. Como otros han dicho, podría estar bien en un índice compuesto.
¿Es esta una consulta común? Puede valer la pena al buscar el "puñado" de registros pero no le ayudará mucho en las otras filas. ¿Hay otras formas de identificar los datos?
100 millones de registros con solo unos pocos teniendo el campo de bit establecido en 1? Sí, creo que indexar el campo de bit definitivamente aceleraría la consulta de los bits = 1 registros. Debería obtener el tiempo de búsqueda logarítmica del índice y luego solo tocar las pocas páginas con bit = 1 registros. De lo contrario, tendría que tocar todas las páginas de la tabla de registro de 100 millones.
Por otra parte, definitivamente no soy un experto en bases de datos y podría estar perdiendo algo importante.
Como han dicho otros, querrás medir esto. No recuerdo dónde he leído esto, pero una columna necesita tener una cardinalidad muy alta (alrededor del 95%) para que un índice sea efectivo. Su mejor prueba para esto sería construir el índice y examinar los planes de ejecución para los valores 0 y 1 del campo BIT. Si ve una operación de búsqueda de índice en el plan de ejecución, sabrá que se usará su índice.
Su mejor curso de acción sería probar con un SELECCIONAR * BASE * DESDE la tabla DONDE BitField = 1; consulta y desarrolla lentamente la funcionalidad a partir de ahí paso a paso hasta que tengas una consulta realista para tu aplicación, examinando el plan de ejecución con cada paso para asegurarte de que la búsqueda del índice aún se esté utilizando. Es cierto que no hay garantía de que este plan de ejecución se utilizará en producción, pero hay muchas posibilidades de que así sea.
Parte de la información se puede encontrar en los foros de sql-server-performance.com y en el article referencia
Considere qué es un índice en SQL, y el índice es realmente una porción de memoria que apunta a otros fragmentos de memoria (es decir, punteros a filas). El índice está dividido en páginas para que partes del índice puedan cargarse y descargarse de la memoria dependiendo del uso.
Cuando solicita un conjunto de filas, SQL usa el índice para buscar las filas más rápidamente que el escaneo de tablas (mirando cada fila).
SQL tiene índices agrupados y no agrupados. Mi comprensión de los índices agrupados es que agrupan valores de índice similares en la misma página. De esta forma, cuando solicite todas las filas que coincidan con un valor de índice, SQL puede devolver esas filas desde una página de memoria agrupada. Esta es la razón por la que tratar de agrupar el índice de una columna GUID es una mala idea: no intente agrupar los valores aleatorios.
Cuando indiza una columna entera, el índice de SQL contiene un conjunto de filas para cada valor de índice. Si tiene un rango de 1 a 10, entonces tendría 10 punteros de índice. Dependiendo de cuántas filas hay, esto se puede paginar de manera diferente. Si su consulta busca el índice "1" y luego el nombre contiene "Fred" (asumiendo que la columna Nombre no está indexada), SQL obtiene el conjunto de filas que coinciden con "1" muy rápidamente, luego escanea la tabla para encontrar el resto.
Entonces, lo que realmente está haciendo SQL es intentar reducir el conjunto de trabajo (número de filas) sobre el que debe iterar.
Cuando indexa un campo de bit (o un rango estrecho), solo reduce el conjunto de trabajo por el número de filas que coinciden con ese valor. Si tiene un número pequeño de filas que coincidan, reduciría mucho su conjunto de trabajo. Para una gran cantidad de filas con una distribución de 50/50, es posible que le compre muy poco rendimiento y mantenga el índice actualizado.
La razón por la que todo el mundo dice probar es porque SQL contiene un optimizador muy inteligente y complejo que puede ignorar un índice si decide que el escaneo de tabla es más rápido, o puede usar un ordenamiento, o puede organizar páginas de memoria como prefiera.
Debe ser inteligente aquí para realizar consultas, debe saber el valor de carga en su columna si la carga de verdad está más en su sistema y desea verificar todos los valores verdaderos para verificar que su consulta no sea falsa. Esto ayudará mucho , solo es un truco.
En caso de que no lo haya leído, Jason Massie escribió recientemente un artículo que discutió este tema.
http://statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/302/Never-Index-a-BIT.aspx
Editar: Ubicación de artículo nuevo - http://sqlserverpedia.com/blog/sql-server-bloggers/never-index-a-bit
Máquina de Wayback para ubicación de artículo previamente "nueva": http://web.archive.org/web/20120201122503/http://sqlserverpedia.com/blog/sql-server-bloggers/never-index-a-bit/
La nueva ubicación de SQL Server Pedia es Toadworld, que tiene un nuevo artículo de Kenneth Fisher sobre este tema:
Ian Boyd tiene razón cuando dice que no puede hacerlo a través de Enterprise Manager para SQL 2000 (consulte su nota sobre cómo crearlo a través de T-SQL.
La cardinalidad es un factor, la otra es qué tan bien divide el índice sus datos. Si tiene aproximadamente la mitad 1s y la mitad 0s, entonces ayudará. (Suponiendo que ese índice es una mejor ruta para elegir que algún otro índice). Sin embargo, ¿con qué frecuencia está insertando y actualizando? La adición de índices para el rendimiento SELECT también perjudica a INSERTAR, ACTUALIZAR y ELIMINAR el rendimiento, así que tenlo en cuenta.
Yo diría, si el 1s a 0s (o viceversa) no es mejor que el 75% al 25%, no te molestes.
Me encontré con esta pregunta por medio de otra. Suponiendo que su afirmación de que solo un puñado de registros asume el valor de 1 (y que esos son los que le interesan), entonces un índice filtrado podría ser una buena opción. Algo como:
create index [IX_foobar] on dbo.Foobar (FooID) where yourBitColumn = 1
Esto creará un índice sustancialmente más pequeño que el optimizador es lo suficientemente inteligente como para usarlo cuando ese es un predicado en su consulta.
No puede indexar un campo de bit en SQL Server 2000, como se indicó en los Libros en línea en el momento:
poco
Tipo de datos enteros 1, 0 o NULO.
Observaciones
Las columnas de tipo bit no pueden tener índices en ellas.
Sí, si solo tiene un puñado de filas, de millones, un índice lo ayudará. Pero si quiere hacerlo en este caso, debe hacer que la columna sea una tinyint .
Nota : Enterprise Manager no le permitirá crear un índice en una columna de bit. Si lo desea, puede crear manualmente un índice en una columna de bit:
CREATE INDEX IX_Users_IsActiveUsername ON Users
(
IsActive,
Username
)
Pero SQL Server 2000 en realidad no usará dicho índice, ejecutando una consulta donde el índice sería un candidato perfecto, por ejemplo:
SELECT TOP 1 Username
FROM Users
WHERE IsActive = 0
SQL Server 2000 hará un escaneo de tabla en su lugar, actuando como si el índice ni siquiera existiera. Si cambia la columna a tinyint SQL Server 2000 hará una búsqueda de índice. Además, la siguiente consulta no cubierta:
SELECT TOP 1 *
FROM Users
WHERE IsActive = 0
Realizará una búsqueda de índice, seguida de una búsqueda de marcador.
SQL Server 2005 tiene soporte limitado para índices en columnas de bits. Por ejemplo:
SELECT TOP 1 Username
FROM Users
WHERE IsActive = 0
causará una búsqueda de índice a través del índice de cobertura. Pero el caso no cubierto:
SELECT TOP 1 *
FROM Users
WHERE IsActive = 0
no provocará una búsqueda de índice seguida de una búsqueda de marcador, sino que realizará una exploración de tabla (o exploración de índice agrupado), en lugar de realizar la búsqueda de índice seguida de una búsqueda de marcador.
Verificado por experimentación y observación directa.
Por sí solo, no, ya que resulta en muy poca selectividad. Como parte de un índice compuesto. muy posiblemente, pero solo después de otras columnas de igualdad.
Por supuesto que vale la pena, especialmente si necesita recuperar los datos por ese valor. Sería similar a usar una matriz dispersa en lugar de usar una matriz normal.
Ahora con SQL 2008 puede usar funciones de particionamiento, y puede filtrar los datos que van en un índice. La desventaja de versiones anteriores sería que el índice se haría para todos los datos, pero esto se puede optimizar almacenando los valores interesantes en un grupo de archivos separado.
Si bien no creo que indexaría SOLO una columna de bit por sí mismo, es muy común incluir columnas de bits como parte de un índice compuesto.
Un ejemplo simple sería un índice en ACTIVE, LASTNAME en lugar de solo lastname, cuando su aplicación casi siempre busca clientes activos.
Si desea saber si un índice tiene los efectos que desea: probar y probar nuevamente.
En general, no desea un índice que no limite suficientemente su tabla, debido al costo de mantener un índice. (costo> ganancia). Pero si el índice en su caso reducirá la tabla a la mitad, puede ganar algo pero ponerlo sobre la mesa. Todo depende del tamaño / estructura exacta de su tabla y de cómo la está usando (número de lecturas / escrituras).
Si su distribución es bastante conocida y desequilibrada, como el 99% de las filas son bit = 1 y el 1% son bit = 0, cuando hace una cláusula WHERE con bit = 1, una exploración de tabla completa será más o menos al mismo tiempo el escaneo del índice Si desea tener una consulta rápida donde bit = 0, la mejor manera que conozco es crear un índice filtrado, agregando una cláusula WHERE bit = 0. De esta manera, ese índice solo almacenará la fila del 1%. Luego, haciendo un WHERE bit = 0 simplemente dejará que el optimizador de consultas elija ese índice, y todas las filas serán bit = 0. También tiene la ventaja de tener una cantidad muy pequeña de espacio en disco requerido comparar un índice completo en el bit .
Si su objetivo es consultar los registros donde el valor del campo de bit es igual a ''1'' más rápido, puede probar una vista indexada de su tabla base que solo contiene registros donde su campo de bit es igual a ''1''. En la edición empresarial, si una consulta puede hacer uso de una vista indexada en lugar de una tabla específica para mejorar el rendimiento de la consulta, usará la vista. En teoría, esto aumentaría la velocidad de las consultas de selección que solo buscan registros con un valor de campo de bit de ''1''.
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx
Todo esto supone que eres Microsoft SQL Server 2005 Enterprise. Lo mismo podría aplicarse a 2008, no estoy familiarizado con esa versión.
respuesta muy tarde ...
Sí, puede ser útil según el equipo de SQL CAT (actualizado, se ha consolidado)
medir el tiempo de respuesta antes y después y ver si vale la pena; teóricamente, debería mejorar el rendimiento de las consultas utilizando los campos indexados, pero realmente depende de la distribución de los valores verdaderos / falsos y de los otros campos implicados en las consultas que le preocupan.