sql - Implementación eficiente de búsqueda facetada en bases de datos relacionales.

database database-design

Question

Estoy tratando de implementar una búsqueda facetada o etiquetado con filtrado de etiquetas múltiples. En la navegación por facetas, solo se muestran las categorías que no están vacías y el número de elementos en la categoría que también coinciden con los criterios ya aplicados se presenta entre paréntesis.

Puedo obtener todos los artículos que tienen categorías asignadas usando INNER JOINs y obtener el número de artículos en todas las categorías usando COUNT y GROUP BY , sin embargo, no estoy seguro de cómo se escalará a millones de objetos y miles de etiquetas. Especialmente el conteo.

Sé que hay algunas soluciones no relacionales como Lucene + SOLR , pero también he encontrado algunas implementaciones basadas en RDBMS de código cerrado que se dice que tienen una gran capacidad empresarial como FacetMap.com o el software Endeca , por lo que debe haber una Manera eficiente de realizar búsquedas facetadas en bases de datos relacionales.

¿Alguien tiene experiencia en la búsqueda facetada y podría dar algunos consejos?

¿Almacenar en caché los recuentos de cada categoría? ¿Tal vez utilizar alguna técnica inteligente inteligente que actualice los contadores?

Editar:

Un ejemplo de navegación facetada se puede encontrar aquí: Flamenco .

Actualmente tengo el esquema estándar de 3 tablas (artículos, etiquetas y artículos_tags como se describe aquí: http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html#toxi ) más una tabla para las facetas. Cada etiqueta tiene asignada una faceta.

Answer 1

La búsqueda facetada es un problema analítico, lo que significa que el diseño dimensional es una buena apuesta. Aka, lo que buscas contra debe estar en forma tabular.

Incluye todas las columnas de interés en tu tabla analítica.

Poner valores continuos en cubos.

Use columnas booleanas para "muchos" elementos como categorías o etiquetas, por ejemplo, si hay tres etiquetas "foo", "barra" y "baz", tendría tres columnas booleanas.

Utilice una vista materializada para crear su tabla analítica.

Índice de la mierda fuera de ella. Algunas bases de datos soportan índices para este tipo de aplicación.

Solo filtrar una vez.

Unión de sus resultados.

Cree vistas materializadas pre-agregadas para consultas comunes.

Este artículo también podría ayudarle: https://blog.jooq.org/2017/04/20/how-to-calculate-multiple-aggregate-functions-in-a-single-query/

with filtered as (
    select
    *
    from cars_analytic
    where
        [some search conditions]
)

--for each facet:

select
    ''brand'' as facet,
    brand as value,
    count(*) as count
from
    filtered
group by
    brand

union

select
    ''cool-tag'' as facet,
    ''cool-tag''as value,
    count(*) as count
from
    filtered
where
    cool_tag

union

...


-- sort at the end
order by
    facet,
    count desc,
    value

100,000 registros con 5 facetas en ~ 150 ms.

Answer 2

OMI, las bases de datos relacionales no son tan buenas en la búsqueda. Obtendría un mejor rendimiento de un motor de búsqueda dedicado (como Solr / Lucene).

Answer 3

Respecto a los conteos, ¿por qué tirarlos a través de SQL? Tendrá que recorrer el conjunto de resultados en su código de todos modos, así que ¿por qué no hacer su conteo allí?

Actualmente estoy usando este enfoque en una aplicación de búsqueda facetada que estoy desarrollando y funciona bien. La única parte difícil es configurar tu código para que no genere la faceta hasta que alcance una nueva faceta. En ese momento, genere la faceta y la cantidad de filas que encontró para ella.

Este enfoque supone que está retirando una lista de todos los elementos coincidentes y, por lo tanto, varias filas con la misma faceta. Cuando ordena este resultado por faceta, es fácil obtener el recuento en su código.

Answer 4

Solo puedo confirmar lo que dice Nils. RDBMS no es bueno para la búsqueda multidimensional. He trabajado con algunas soluciones inteligentes, almacenando contadores de caché, utilizando desencadenadores, etc. Pero al final, el indexador externo dedicado siempre gana.

Quizá, si transforma sus datos en un modelo dimensional y los alimenta a algún OLAP [me refiero al motor MDX], funcionará bien. Pero parece una solución demasiado pesada, y definitivamente NO será en tiempo real.

Por el contrario, la solución con un motor de indexación dedicado (piense en Lucene, piense en Sphinx ) se puede hacer casi en tiempo real con actualizaciones de índice incrementales.