mysql：多个索引建议 [英] mysql: multiple indexes advice

问题描述

我有一个表，我需要根据4列a，b，c，d
进行查询最常见的查询将是同时基于所有4列的选择，但是我需要能够快速搜索单独拍摄的每个列，或者它们的组合（例如a& b，a& d，b& c& d等）。
我可以为每个组合创建一个索引吗？或者最好只有一个& b& c& d的索引，一个用于a，一个用于b，一个用于c，一个用于d？在最后一种情况下，例如仅匹配& b的查询将加速，因为a和a都有索引？

I have a single table where I need to query based on 4 columns a,b,c,d The most common query will be a select based on all 4 columns at the same time, however I need to be able to search quickly for each of the columns taken separately, or also combinations of them (e.g. a&b, a&d, b&c&d and so on). Shall I create an index for every combination? or it's better to have only an index for a&b&c&d and one for a, one for b, one for c, one for d? in this last case a query that matches only a&b for example will be sped up because both a and be have an index?

推荐答案

如果您想满足索引的所有组合，您需要以下内容：

If you want to satisfy all the combinations with an index, you need the following:

(a, b, c, d)
(a, b, d)
(a, c, d)
(a, d)
(b, c, d)
(b, d)
(c, d)
d

你不需要其他组合，因为索引的任何前缀也是索引。第一个索引将用于仅测试 a ， a& b ， a& amp ; b& c ，因此您不需要这些组合的索引。

You don't need other combinations because any prefix of an index is also an index. The first index will be used for queries that test just a, a&b, a&b&c, so you don't need indexes for those combinations.

您是否真的需要所有这些索引取决于数据量你有。有可能只在每列上有索引会缩小搜索范围，使得组合上不需要索引。唯一真正的方法是对应用程序的性能进行基准测试。索引占用磁盘空间和内存，因此尝试创建所有可能的索引可能会导致其自身的问题;你需要确定是否需要足够强大。

Whether you really need all these indexes depends on how much data you have. It's possible that just having indexes on each column will narrow down the search sufficiently that you don't need indexes on the combinations. The only real way to tell is by benchmarking the performance of your applications. The indexes take up disk space and memory, so trying to create all possible indexes can cause problems of its own; you need to determine if the need is strong enough.

这篇关于mysql：多个索引建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！