您必须了解在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.