Pregunta La mejor manera de indexar esta tabla muy grande


Tengo la siguiente tabla

CREATE TABLE DiaryEntries
(
 [userId] [uniqueidentifier] NOT NULL,
 [setOn] [datetime] NOT NULL, -- always set to GETDATE().
 [entry] [nvarchar](255) NULL
)

Cada usuario insertará alrededor de 3 entradas por día. Habrá alrededor de 1'000'000 usuarios. Esto significa 3'000'000 nuevos registros en esta tabla todos los días. Una vez que un registro tiene más de 1 mes, lo eliminamos.

La mayoría de las consultas tienen la siguiente cláusula WHERE:

WHERE userId = @userId AND setOn > @setOn

La mayoría de las consultas no devuelven más de 3 filas, excepto una que devuelve todas las filas insertadas dentro de este mes (que es como máximo 90 filas).

La fecha y el ID de usuario no se pueden cambiar una vez que se inserta el registro.

Ahora mi pregunta es: ¿cómo organizar mejor el índice para esta tabla? Estoy atascado con las dos alternativas:

  1. Índice agrupado en (userId, setOn): esto me dará búsquedas rápidas, pero me preocupa el exceso de división de páginas, porque insertaremos muchos valores intermedios (la misma ID de usuario pero fecha diferente).
  2. Índices no agrupados en (userId) y en (setOn): esto también causará divisiones de páginas en (userId) índice (pero ¿es tan caro como en la primera opción?). La búsqueda se ralentiza porque estamos utilizando el índice NC.
  3. El índice agrupado en una columna adicional (id) y el índice no agrupado en (userId, setOn): esto eliminará la división de páginas de la tabla de datos, pero aún causará algunas en el índice NC. Esta opción tampoco es óptima para la búsqueda, ya que buscamos usando el índice NC.

¿Cuáles son tus sugerencias? ¿Alguna otra opción?

PD - Gracias por tu tiempo.


Después de 2 días de reflexionar, se me ocurrió una solución diferente a este problema.

CREATE TABLE MonthlyDiaries
(
 [userId] uniqueidentifier NOT NULL,
 [setOn] datetime NOT NULL, -- always set to GETDATE().

 [entry1_1] bigint NULL, -- FK to the 1st entry of the 1st day of the month.
 [entry1_2] bigint NULL, -- FK to the 2nd entry of the 1st day of the month.
 [entry1_3] bigint NULL,
 [entry2_1] bigint NULL,
 [entry2_2] bigint NULL,
 [entry2_3] bigint NULL,
 ...
 [entry31_1] bigint NULL,
 [entry31_2] bigint NULL,
 [entry31_3] bigint NULL,
 PRIMARY KEY (userId, setOn)
)
CREATE TABLE DiaryEntries
(
 [id] bigint IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 [entry] nvarchar(255) NOT NULL
)

Básicamente agrupé 31 días en una sola fila. Esto significa que inserto un nuevo registro solo una vez al mes por usuario. Esto reduce la división de páginas de 3 veces al día por usuario a una vez al mes por usuario. Obviamente hay inconvenientes, aquí hay algunos de ellos.

  • El tamaño de la fila es enorme, sin embargo, en 99.999% de las veces solo pregunto una fila de MonthlyDiaries.
  • Estoy potencialmente usando más espacio de lo que necesito, porque algunos días podría no tiene entradas. No es un gran trato.
  • Para encontrar una entrada para un día específico requerirá una búsqueda de índice adicional en Entradas del diario. Creo que no va Ser un gran costo, ya que soy recuperando no más de 90 filas y En el 80% de los casos solo recupero 1 fila.

En general, creo que es una buena compensación: reducir las divisiones de 3 páginas / día / usuario a solo 1 página / mes / usuario, pero a cambio, pagar un precio pequeño al hacer mis búsquedas un poco más lentas. ¿Qué piensas?


4
2017-11-22 07:03


origen


Quizás cambiar el título a "La mejor manera de indexar esta tabla tan grande" le atraiga más atención. Además, esto es más una cuestión de stackoverflow. - Justin Dearing
Buen puntero Gracias. - niaher
Estoy de acuerdo con que esto es más una configuración de SO vs servidor de preguntas. - Jason D


Respuestas:


Asumiré que tiene buenas razones para usar las guías como identificadores.

La fragmentación es principalmente un problema para los escaneos, y menos para las búsquedas. La fragmentación tiene un gran impacto en la lectura anticipada, y las búsquedas no usan ni necesitan lectura anticipada. Un índice no fragmentado con una mala elección de columna tendrá un rendimiento siempre peor que un índice de fragmento del 99% con columnas buenas y utilizables. Si hubiera descrito las consultas de estilo de informe de DW que exploran las tablas, entonces le recomendaría que se centre en eliminar la fragmentación, pero para la carga que describe, tiene más sentido centrarse en búsquedas eficientes (de cobertura) y en exploraciones de rango pequeño.

Dado que su patrón de acceso siempre está controlado por @userId, esta debe ser la columna más a la izquierda en el índice agrupado. También agregaría setOn como la segunda columna en el índice agrupado porque agrega algún valor marginal en la mayoría de las consultas (digo marginal porque el @userId es tan selectivo, en el peor de los casos es de 90 registros de 90 mil., Que el filtro adicional agregado por @setOn no es crítico). No agregaría ningún índice no agrupado, de las consultas que describe no hay necesidad de ninguna.

El único problema es la eliminación de registros antiguos (la retención de 30 días). Yo recomendaría no usar un índice NC secundario para satisfacer esto. Preferiría implementar un esquema de partición semanal con ventana deslizante, vea Cómo implementar una ventana deslizante automática en una tabla particionada en SQL Server 2005. Con esta solución, los registros antiguos se eliminan mediante un interruptor de partición, que es la forma más eficiente posible. Un esquema de partición diario satisfaría el requisito de retención de 30 días con mayor precisión y quizás valga la pena probar y probar. Dudo en recomendar 30 particiones directamente porque describe algunas consultas que tienen el potencial de buscar registros particulares de @userId en cada partición, y 31 particiones pueden crear problemas de rendimiento bajo una carga pesada. Prueba y mide ambos mejor.


5
2017-11-23 21:31



Hola remus Es cierto lo que dice: "La fragmentación es principalmente un problema para los escaneos, y no tanto para las búsquedas". Pero, ¿qué pasa con el costo de la división de página real, cuando se inserta un registro intermedio? Entiendo que este costo es muy alto. Teniendo en cuenta que la mayoría de mis INSERTs causarán una división de páginas, ¿no es este un problema importante? (Por favor, perdóneme si estoy entendiendo algo mal, porque todavía soy un principiante en este campo). - niaher
'Muy alto' es relativo. Más alto que insertar al final de la tabla, seguro. Pero solo estamos hablando de 3 mil. Inserta un día, eso es 35 por segundo. Incluso con un pico de 10x no es exactamente una tasa alta. La división de páginas y otros puntos finos se vuelven relevantes a tasas de miles de inserciones por segundo. - Remus Rusanu
Wow gracias. Este es probablemente el dato más útil / alentador que he recibido en días. Sin embargo, como mi decisión final, se me ocurrió un diseño completamente diferente. Lo publicaremos aquí pronto. - niaher
La división de páginas también aumenta la cantidad de registro de transacciones generado:sqlskills.com/BLOGS/PAUL/post/… - SuperCoolMoss


Primero agregue una restricción predeterminada en su tabla. Segundo, agregue un esquema de particionamiento. Tercero reescribe tu consulta más común.

El índice agrupado se debe establecer en setOn, usuario Id. Esto elimina la posibilidad de que el índice se fragmente. Debe usar la partición de la tabla para dividir la tabla, de modo que cada mes se almacene en un archivo separado. Esto reducirá el mantenimiento. Puede buscar en línea un script de ventana deslizante de partición que puede ejecutar cada mes para crear una nueva tabla para el próximo mes, eliminar el mes más antiguo y ajustar el esquema de partición. También puede mover los meses realmente antiguos a una tabla de archivo si el almacenamiento no le preocupa.

Sus consultas donde la cláusula debe estar en el formulario:

WHERE setOn > @setOn AND userId = @userId

O cuando regresas todo el mes:

WHERE setOn BETWEEN @setOnBegin AND @setOnEnd AND userId = @userId

Su nuevo diseño de esquema, sin la partición se vería así:

-- Stub table for foreign key
CREATE TABLE Users
(
 [userId] [uniqueidentifier] NOT NULL
  CONSTRAINT PK_Users PRIMARY KEY NONCLUSTERED
  CONSTRAINT DF_Users_userId DEFAULT NEWID(),
 [userName] VARCHAR(50) NOT NULL
)
GO

CREATE TABLE DiaryEntries
(
 [userId] [uniqueidentifier] NOT NULL
  CONSTRAINT FK_DiaryEntries_Users FOREIGN KEY REFERENCES Users,
 [setOn] [datetime] NOT NULL
  CONSTRAINT DF_DiaryEntries_setOn DEFAULT GETDATE(),
 [entry] [nvarchar](255) NULL,
 CONSTRAINT PK_DiaryEntries PRIMARY KEY CLUSTERED (setOn, userId)
)
GO

Después de que consigas que funcione, debes agregar particiones. Para eso, comienza con Esta entrada de blog para alguna teoría Entonces empieza a leer este documento técnico de MSDN. El documento técnico está escrito para 2005 y hubo mejoras en la partición en 2008 que no he investigado, por lo que la solución podría ser más sencilla en 2008.


6
2017-11-22 13:10



Hola Justin. Gracias por su respuesta. Eché un vistazo a los enlaces que me diste, muy útiles. Por mucho que me gusten sus consejos sobre la partición, veo un problema con tener un índice en (setOn, userId). El problema es que dado que no especificamos el valor exacto de setOn, sino un rango, el conjunto de resultados del (setOn> @setOn) será enorme. Encontrar el ID de usuario correcto a partir de este conjunto de resultados será muy costoso, ya que no hay un orden especial de cómo se posicionan los valores de ID de usuario (a menos que, por supuesto, los valores establecidos) sean idénticos. Por favor, corríjame si estoy equivocado. - niaher
Niaher, la mejor manera de saberlo con certeza es cargar algunos datos de prueba y observar los planes de consulta estimados y reales. Si solo selecciona las particiones y realiza una búsqueda de índice agrupado, su consulta es lo más óptima posible. Eso es, por supuesto, a menos que haya un plan de particionamiento / agrupamiento más óptimo. Es posible que desee experimentar con un índice condicional (índice con una cláusula where) solo en el ID de usuario para el intervalo de fechas en el que se encuentran la mayoría de las consultas. - Justin Dearing


No estoy aquí para criticar tu solución, ni estoy en condiciones de hacerlo, ya que no sé lo suficiente sobre el problema. Aquí está mi opinión:

  • Si lo único no es feliz. sobre es usar demasiado espacio en disco debido al tamaño de la fila, echa un vistazo columnas dispersas  De esa manera todos los nulos no ocupan tanto espacio!
  • Tener las claves externas va a ralentizar considerablemente sus inserciones, has probado esto?

2
2017-11-28 13:13





Desea que las nuevas filas se inserten al final del archivo físico de la tabla, como un archivo de diario, porque se insertan tantas filas cada día.

Por lo tanto las filas deben ordenarse en orden cronológico.

Por lo tanto, setOn debería ser la primera parte de la clave principal. - O, idealmente, agregue una columna 'postId', que es solo un número entero que se autoincremente.

Si no desea una columna postId, la clave principal será (setOn, userId), de lo contrario, simplemente puede ser postId.

Así, hemos obtenido tiempos de inserción rápidos. Ahora queremos tiempos de recuperación rápidos cuando seleccionamos por usuario-ID.

Para esto necesitamos agregar un índice secundario a la tabla, que debe estar en useId. Dado que solo hay 90 registros por usuario, esto es suficiente para que rdbms pueda recuperar rápidamente todas las filas para ese usuario (todas las 90, dado que solo son filas de un mes a la vez), y luego la tabla escanea esas 90 filas. que será cegadoramente rápido.

El índice puede ser cualquier árbol b estándar, árbol rojo-negro, índice, lo que venga con su base de datos.

La inserción se reducirá ligeramente al insertarla en el índice, pero no demasiado. Las estructuras arbóreas son bastante buenas para manejar inserciones aleatorias.

Dado que el índice UserId se basa en el conjunto de UserIds, que es un conjunto estable, el árbol debería ser bastante estable y no necesitar un gran rebalanceo: solo los nodos de hoja al final cambiarán a medida que se agreguen y eliminen entradas de diario, lo que Realmente no cambia demasiado la forma del árbol.


1
2017-11-29 18:02





No soy un fan de tu nueva solución. Solo introducirá nuevos problemas, el mayor es que las ACTUALIZACIONES son (normalmente) más lentas que los INSERTOS, y crean un mayor riesgo de bloqueo cuando se llevan a cabo las actualizaciones.

Si está preocupado por la división de páginas, todo lo que debe hacer es ajustar el "Factor de llenado"para el índice agrupado. FillFactor define qué parte de cada página se deja en blanco (de forma predeterminada) para permitir cambios o inserciones.

Establecer un factor de relleno razonable significa que se inserta no debería causa (como muchas) divisiones de página, y tus depuraciones de registros antiguos significan que se debe liberar más espacio en esas páginas, manteniendo un espacio libre (algo) consistente por página.

Desafortunadamente, el valor predeterminado de SQL suele ser 0 (lo que significa lo mismo que 100), lo que significa que todas las páginas están completamente llenas, lo que causa muchas divisiones de páginas. Muchas personas recomiendan un valor de 90 (10% de espacio libre en cada página de datos). No puedo decirte cuál sería el ideal para tu mesa, pero si tu ultra paranoico acerca de la división de páginas, prueba 75 o incluso menos, si puedes ahorrar espacio de disco extra. Hay algunos contadores de perfmon que puede monitorear para observar las divisiones de página, o puede ejecutar consultas para informarle el porcentaje de espacio libre en cada página de datos.

Con respecto a los detalles de los índices en su tabla (la versión original), recomendaría un índice agrupado en ([userId], [setOn]), por las razones que Remus mencionó.

También necesitará un índice no agrupado en ([setOn]) para que su consulta de "eliminar registros antiguos" no tenga que realizar un análisis completo de la tabla para encontrar todos los registros antiguos.

Tampoco soy un fanático de los GUID para identificadores simples, la mayoría del tiempo, pero me imagino que probablemente sea un poco tarde para cambiar.

Editar: Algunos cálculos preliminares en un factor de relleno estimado para esta tabla.

Para cada usuario, 3 nuevas entradas por día, guardadas 30 días, así que ~ 90 entradas totales. Asumiendo que haces un diario purga de todos los registros de más de 30 días (en lugar de purgar solo cada 30 días), entonces solo agrega / elimina menos del 5% de los registros diariamente.

Por lo tanto, un factor de relleno de 90 (10% de espacio libre en cada página) debería ser más que suficiente.

Si solo estas purificando mensual, entonces estarías dejando que se acumulen casi 60 días antes de eliminar los 30 más antiguos, lo que significa que necesitarás un factor de relleno del 50%.

Recomiendo encarecidamente una purga diaria.

Editar 2: Después de una consideración adicional, un índice no agrupado en [setOn] puede no ser lo suficientemente selectivo para ser utilizado por su consulta de purga (un solo día es 1/30 o 3.3% de las filas, lo que está justo en el borde de "útil" ). De todos modos, podría realizar una exploración de índice agrupado, incluso si el índice existe. Probablemente vale la pena probar tanto con como sin este índice adicional.


1
2017-11-30 15:24





Yo sugiero:

  1. Índice agrupado en userid
  2. Índice de cobertura no agrupado en seton & entry, o simplemente un índice no agrupado en seton

0
2017-11-22 07:39





Una forma de resolver esto es tener una tabla para cada día.

Con una tabla de registros de 3M, no es un problema tener un índice agrupado en userid y seton. Su tiempo de inserción sería mucho menor.

Puede ejecutar un mantenimiento en la tabla del día al final del día, por lo que la tabla no está fragmentada y el tiempo de respuesta sería correcto.

También puede crear una vista en las tablas para obtener datos durante todo un mes.


0
2017-11-27 16:50