如何有效搜索潜在的大型数据库? [英] How do I efficiently search a potentially large database?

问题描述

这更多的是讨论.

我们有一个多租户的系统，它将具有可以包含数百万行的表.我们的用户界面允许用户使用许多不同的搜索条件对这些表执行搜索-因此他们可以将这些条件进行任意组合.

We have a system which is multitenanted and will have tables that can have millions of rows. Our UI allows users to perform searches against these tables with many different search criterias -- so they can have any combination of these criteria.

为数据库中的所有这些搜索列建立索引或将完整表加载到内存中然后进行过滤是不切实际的.

It is not practical to index all these search columns in the database or to load the full tables in memory and then filter.

有人能为我指出解决该问题的模式/设计的正确方向吗?

推荐答案

我不知道有什么模式可以解决您所描述的情况.无限数量的行，完全即席的查询，许多同时用户?这不是必须的；那就是一切".

I'm not aware of any pattern to solve the situation you describe. An unlimited number of rows, completely ad hoc queries, many simultaneous users? That's not a requirement; that's "anything goes".

我将假定这是一个报表数据库，而不是事务数据库.数据是只读的.正确吗?

I'll assume that this is a reporting database, not transactional. The data is read-only. Is that correct?

具有星型模式的数据仓库将按照经过精心设计的维度来规定查询.用户可以汇总维度(例如，时间维度允许用户汇总到日，周，月，季度，年等).但是，这样做的逻辑是在数据库上执行的，并在存储过程中进行了编码.

A data warehouse with a star schema would prescribe queries along thoughtfully designed dimensions. Users can roll up dimensions (e.g. time dimension allows users to roll up to day, week, month, quarter, year, etc.). But the logic to do so is executed on the database and coded in stored procedures.

我会挑战这样一个断言，即用户需要在中间层上数百万行.没有用户可以一次获取数百万行. Google一次返回25条单个查询返回的数百万个页面.

I'd challenge the assertion that users need millions of rows on the middle tier. No user can take in millions of rows at once. Google returns the millions of pages returned by a single query twenty-five at a time.

也许您可以流化以分离方式使用的数据集，并使用Excel或其他工具进行分析.但这是我能想到的最佳方案.

Maybe you can stream down data sets that are used in a detached manner, analyzed using Excel or some other tool. But that's the best scenario I can envision.

这篇关于如何有效搜索潜在的大型数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！