使用OR运算符时正确索引 [英] Correct indexing when using OR operator

问题描述

我有这样的查询：

SELECT fields FROM table
WHERE field1='something' OR field2='something' 
OR field3='something' OR field4='something'

什么为这个查询索引这样一个表的正确方法是什么？

What would be the correct way to index such a table for this query?

像这样的查询需要一整秒才能运行！我有1个索引，其中包含所有4个字段，所以我认为mysql会做这样的事情：

A query like this takes a entire second to run! I have 1 index with all 4 of those fields in it, so I'd think mysql would do something like this:

浏览索引中的每一行，这样做：
是field1吗？ field2怎么样？字段3？字段4？好吧，不，转到下一行。

Go through each row in the index thinking this: Is field1 something? How about field2? field3? field4? Ok, nope, go to the next row.

推荐答案

你误解了索引的工作原理。

You misunderstand how indexes work.

想一下电话簿（相当于姓氏上的两列索引，名字最后一个）。如果我要求您在电话簿中找到姓氏为史密斯的所有人，您可以从这样的事实中获益：你可以假设史密斯是一起组织的。但是，如果我要求你找到所有名字都是约翰的人，你就无法从索引中获益。约翰斯可以有任何姓氏，所以他们分散在整本书中，你最终不得不从头到尾搜索。

Think of a telephone book (the equivalent of a two-column index on last name first, first name last). If I ask you to find all people in the telephone book whose last name is "Smith," you can benefit from the fact that the names are ordered that way; you can assume that the Smiths are organized together. But if I ask you to find all the people whose first name is "John" you get no benefit from the index. Johns can have any last name, and so they are scattered throughout the book and you end up having to search the hard way, from cover to cover.

如果我问的话你要找到姓氏为史密斯的所有人，或者名字叫约翰的人，你可以像以前一样轻松找到史密斯，但这根本不能帮助你找到约翰斯。它们仍然散布在整本书中，您必须以艰难的方式搜索它们。

Now if I ask you to find all people whose last name is "Smith" OR whose first name is "John", you can find the Smiths easily as before, but that doesn't help you at all to find the Johns. They're still scattered throughout the book and you have to search for them the hard way.

SQL中的多列索引也是如此。索引按第一列排序，然后在第一列中绑定的情况下按第二列排序，然后在前两列中的绑定情况下按第三列排序，等等。它不按所有列排序同时。因此，除了索引中最左边的列之外，您的多列索引无法使搜索字词更有效。

It's the same with multi-column indexes in SQL. The index is sorted by the first column, then sorted by the second column in cases of ties in the first column, then sorted by the third column in cases of ties in both the first two columns, etc. It is not sorted by all columns simultaneously. So your multi-column index doesn't help to make your search terms more efficient, except for the left-most column in the index.

返回原始问题。

为此查询索引此类表的正确方法是什么？

What would be the correct way to index such a table for this query?

在每列上创建单独的单列索引。基于MySQL的，其中一个索引将是比其他索引更好的选择估计索引在使用时会产生多少I / O操作。

Create a separate, single-column index on each column. One of these indexes will be a better choice than the others, based on MySQL's estimation of how many I/O operations the index will incur if it is used.

现代版本的MySQL也有一些关于索引合并，因此查询可能在给定的表中使用多个索引，然后尝试合并结果。否则MySQL往往限于在给定查询中为每个表使用一个索引。

Modern versions of MySQL also have some smarts about index merging, so the query may use more than one index in a given table, and then try to merge the results. Otherwise MySQL tends to be limited to use one index per table in a given query.

很多人成功使用的另一个技巧是对每个表单执行单独的查询。您的索引列（应使用相应的索引）然后 UNION 结果。

Another trick that a lot of people use successfully is to do a separate query for each of your indexed columns (which should use the respective index) and then UNION the results.

SELECT fields FROM table WHERE field1='something' 
UNION
SELECT fields FROM table WHERE field2='something' 
UNION
SELECT fields FROM table WHERE field3='something' 
UNION
SELECT fields FROM table WHERE field4='something' 

最后一个观察结果：如果你发现自己在四个字段中搜索相同的'某事'，你应该重新考虑是否所有四个字段实际上是相同的，并且你是有罪的设计一个违反重复组的第一范式表的表格。如果是这样，则field1到field4可能属于子表中的单个列。然后索引和查询变得容易得多：

One final observation: if you find yourself searching for the same 'something' across four fields, you should reconsider if all four fields are actually the same thing, and you're guilty of designing a table that violates First Normal form with repeating groups. If so, perhaps field1 through field4 belong in a single column in a child table. Then it becomes a lot easier to index and query:

SELECT fields from table INNER JOIN child_table ON table.pk = child_table.fk
WHERE child_table.field = 'something'

这篇关于使用OR运算符时正确索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！