mysql - 您如何设计数据库以允许快速多列搜索？

Question

我正在使用 MySQL 从 RETS 数据创建房地产搜索，但这是一个一般性问题。如果您有多个列，您希望用户能够根据这些列来过滤他们的搜索结果，您如何优化它？

例如，http://www.charlestonrealestateguide.com/listings.php有 16 个左右的可选过滤器。诚然，他最多只有 11,000 个条目(我有相同的数据)，但我不认为搜索是用一个巨大的 WHERE AND AND AND ... 子句执行的。或者这通常是通过一个巨大的多列索引来完成的？

Newegg、Amazon 和无数其他公司也拥有适用于大量数据的又酷又快速的过滤系统。他们是怎么做到的呢？倾向于提供范围而不是空输入是否有数据库优化原因，或者仅仅是为了用户方便？

Answer 1

我相信this post通过 Explain Extended解决你的问题。它又长又详细，展示了许多例子。我将剪切/粘贴他的摘要来满足您的胃口:

In some cases, a range predicate (like "less than", "greater than" or "between") can be rewritten as an IN predicate against the list of values that could satisfy the range condition.

Depending on the column datatype, check constraints and statistics, that list could be comprised of all possible values defined by the column’s domain; all possible values defined by column’s minimal and maximal value, or all actual distinct values contained in the table. In the latter case, a loose index scan could be used to retrieve the list of such values.

Since an equality condition is applied to each value in the list, more access and join methods could be used to build the query plain, including range conditions on secondary index columns, hash lookups etc.

Whenever the optimizer builds a plan for a query that contains a range predicate, it should consider rewriting the range condition as an IN predicate and use the latter method if it proves more efficient.