Indexación y alternativas para columnas de baja selectividad

¿Cuál es el range de tácticas disponibles para seleccionar loggings en columnas de baja selectividad?

Un ejemplo podría ser una tabla de pedidos en la que, durante muchos años, acumule una gran cantidad de pedidos completados, pero a menudo necesita seleccionar pedidos activos. Un pedido puede pasar por un ciclo de vida tal como colocado, asignado, almacenado, despachado al cliente, facturado y pagado. Una order adicionalmente puede ser cancelada, retenida, etc. La mayoría de los loggings eventualmente estarán en el estado final (por ejemplo, pagados) pero a menudo puede necesitar seleccionar, por ejemplo, pedidos asignados. En este caso, una lectura secuencial sería lenta.

Preguntas similares sobre indexing
MySQL: columnas de baja cardinalidad / selectividad = ¿cómo indexar?
¿Los índices chupan en SQL?
¿Qué son los índices y cómo puedo usarlos para optimizar las consultas en mi database?
Definición de índices: ¿qué columnas e impacto de performance?
y muchos otros relacionados de forma decreciente.

Los enfoques sobre los que he leído (en stackoverflow y en otros lugares) incluyen

  • Use un índice de bitmap
  • Use un índice parcial ( create index x on t(c2) where c1='a' )
  • Use un índice agrupado?
  • No indexar columnas de baja selectividad, use lectura secuencial
  • Partición de los datos (por ejemplo, en varias tablas con esquema idéntico)
  • Use una tabla suplementaria (por ejemplo, active_customers(customer_id)

Mi DBMS actual no admite las tres primeras opciones enumeradas anteriormente y el rest parece problemático. ¿Hay algún otro enfoque comúnmente utilizado?

Actualización: He visto – indexe su columna de baja selectividad, pero solo select valores de alta selectividad.

Estoy de acuerdo con la twig Sinrazón Sin embargo . Pero hay algunas cosas que debe saber sobre este caso.

Esto se llama sesgo y sesgo mata. Este es un uso perfecto para un índice parcial en el que excluiría el 95% de las facturas pagadas y solo indexaría las statistics más interesantes y selectivas. Pero no tienes eso. Puede dividir horizontalmente todas las filas en tablas / particiones separadas, pero luego debe contabilizar la migration de filas (pasar de un estado a otro) y eso es costoso. El DBMS tiene que realizar una actualización, una eliminación y una inserción para cambiar el estado. Si eres un sistema de alto volumen que duele.

Olvídate de lo que dijiste sobre si indexar según la selectividad o no, porque poner un índice en una columna que cambia rápidamente también suele ser una mala idea. Su índice tendrá bloques calientes donde se eliminarán todos los pasos 1 y otro donde se insertan todos los pasos 2 y, por cierto, algunos pasos 2 se eliminarán al mismo time en el paso 3. Esto no se escalará bien.

Recomendaría dividir verticalmente su estado en una tabla (s) separada.

Su tabla de facturas tendrá un PK y todas las columnas, excepto el estado.

Su estado puede manejar de dos maneras. Esa tabla tendrá el valor de PK como FK en la tabla de facturas, el Estado y una timestamp para cuando ingresó ese estado. Lo mejor es una tabla con particiones horizontales en estado. Tendrás una partición para cada estado posible. Por lo tanto, si encuentra uno o un estado "Colocado", la partición se cortará y solo se leerá la partición que necesita, que es una cantidad muy pequeña de bloques. Debido a que la fila es muy estrecha, es posible que obtenga 400 estados de factura en un solo bloque. Buscar ese estado de cualquier factura es fácil ya que hay un índice global en PK.

Si su RDBMS no admite particiones con la migration de filas, necesitará administrar estas particiones como tablas y eliminarlas de una e insertlas en otra. Encapsulará estos movimientos en una transacción en un procedimiento, por lo que mantendrá los datos limpios. Cada factura está en una y solo una tabla de estado. La parte más difícil es consultar por ID de factura, deberá verificar cada tabla para ver dónde está.

Usted tiene otra opción Puede escribir estados pagados o no. Si se trata de una tabla particionada, puede eliminar la factura de la tabla de estado de la factura cuando se mueve a pagado. (Por supuesto, escribirá un logging pagado en la tabla de historial mencionada en el material adicional). Luego harás una combinación externa a la tabla de estado y los nulos significarán pagado. Si casi nunca consultas el estado pagado, realmente no hay razón para hacer una consulta rápida.

Material adicional

en cualquier caso, querrá hacer un seguimiento de estos movimientos en una tabla de informes. Cada vez que actualice un estado, querrá escribirlo en una tabla de historial. Eventualmente querrá analizar lo que llamo times de tránsito. ¿Cuál es el time promedio entre llenado y pagado, por mes? ¿Eso está aumentando como resultado de la mala economía? ¿cuál es el time de tránsito de colocado a lleno, por mes. ¿Los meses de verano tardan más debido a que faltan cuerpos en vacaciones? tú entiendes. Al actualizar esa columna, estás perdiendo esas respuestas, por lo que necesitarás insert ese logging de historial en tus procedimientos.

De todos los enfoques que ha enumerado, solo uno (uso de lectura secuencial) es un enfoque que tiene algo que ver con la baja selectividad (bueno, el grupo también puede calificar).

Si tiene una baja selectividad en una columna, esto significa que los escaneos funcionarán mejor que la búsqueda.

El índice se puede usar para hacer

  • búsquedas de índice: verifique el índice, recupere el logging, repita
  • escaneos de índice: escanee el índice y obtenga los valores directamente del índice

de lo contrario, no es muy útil.

Si la selectividad es baja, significa que se leerá una gran parte del índice y, si se utilizan búsquedas, se leerá gran parte de los datos, en algún order aleatorio. Esto es ineficiente si cubre un porcentaje significativo de la tabla subyacente, por lo que el mejor método sería hacer una lectura secuencial (que también es lenta).

Entonces, si la selectividad es baja, no hay mucho que pueda hacer (la agrupación puede ayudar).

Sin embargo , no estoy convencido de que comprenda que en su ejemplo no tiene baja selectividad. Como dices, se pagarán la mayoría de las inputs y se asignarán muy pocas inputs. Estas inputs (asignadas) tendrán alta selectividad . Especialmente si hay condiciones adicionales y si hay un índice compuesto que contiene esas condiciones adicionales.

Entonces, podrías golpearte la cabeza contra un no problema.

Ahora bien, es cierto que puede mejorar aún más el performance si particiona los datos o utiliza una tabla adicional (si es necesario).

El particionamiento es un enfoque que almacena la misma tabla en áreas separadas basadas en datos: los desarrolladores de SQL no tienen que acceder a tablas separadas.

Creo que es ideal para el problema descrito: puede encontrar más información sobre Informix aquí: http://www.dbmag.intelligententerprise.com/blog/main/archives/2008/09/data_partitioni.html