MySQL多索引与多列索引进行搜索 [英] MySQL multiple indexes vs multi-column index for searching

问题描述

在我正在编写的软件中，它可以搜索给定表中的信息.搜索表单有5个字段，所有字段当然都对应于表中的不同列，但是所有字段都是可选的.

In the software I'm writing one has the ability to search a given table for information. The search form has 5 fields, all which of course correspond to different columns in a table, but all of the fields are optional.

我的问题是关于多列索引是否将起作用以及为其建立查询的正确方法.

My question is in regard to whether or not multi-column indexing will work and the proper way to build a query for it.

如果我在5列中有一个索引，并且我建立了一个查询来搜索它们，那么当我不搜索该索引中的字段时，是否要做类似的事情:

If I have a single index across 5 columns, and I build a query to search them, when it comes to fields in this index I'm not searching, do I do something like:

field1 = 10 AND field2> 0 AND ...

field1 = 10 AND field2 > 0 AND ...

还是我根本不应该包括未使用的列?

Or should I not include the unused columns at all?

我一直在搜索有关多个列索引的信息，但是当您需要跳过给定索引中的一列或者只是根本不在乎该列时，似乎找不到该怎么办在特定情况下.

I've searched around for information about multiple column indices, but I can't seem to find what to do when you need to skip one of the columns in a given index or if you simply don't care about that one in that specific instance.

推荐答案

您必须了解在MySQL中(对于InnoDB)，它仅使用索引的最左前缀.因此，如果索引位于Field1 + Field2中，则仅使用 WHERE 中的Field2进行查询.

You have to understand in MySQL (in the case of InnoDB) it only uses the left most prefix of your indexes. So if the index is in Field1 + Field2, querying with only Field2 in the WHERE cannot make use of the whole index.

如果Field1 + Field2 + Field3是索引，则在 WHERE 中只有Field1 & Field3的查询也会发生.

And if Field1 + Field2 + Field3 is the index, a query with only Field1 & Field3 in WHERE the same would happen.

选项1

如果要优化每个搜索，则必须为每个搜索方案创建一个单独的索引.但是，如果表很大，那么索引也将变得非常大. 如果经常进行这些搜索查询，那是值得的.

You would have to create a separate index for each search scenario if you want to optimize each search. However, if the tables are large, then the indexes would become extremely large as well. If those search queries are made very often this would be worth it.

选项2

如果您的搜索具有低选择性(即性别)，则可以使用一个巧妙的技巧，方法是将低选择性列放在最左侧，然后使用Gender IN(M, F)将其包含在 WHERE 子句以及其他列来利用整个索引.

You can use a nifty trick if your searches have a low selectivity (i.e. Gender) by putting the low selectivity columns to the left most and use Gender IN(M, F) to include it in the WHERE clause along with the other column(s) to make use of the whole index.

这篇关于MySQL多索引与多列索引进行搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！