强制 MySQL 在 Join 上使用两个索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4841038/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Force MySQL to use two indexes on a Join
提问by Spencer
I am trying to force MySQL to use two indexes. I am joining a table and I want to utilize the cross between the two indexes. The specific term is Using intersect and here is a link to MySQL documentation:
我试图强制 MySQL 使用两个索引。我正在加入一个表，我想利用两个索引之间的交叉。具体术语是使用相交，这里是 MySQL 文档的链接：
http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html
http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html
Is there any way to force this implementation? My query was using it (and it sped stuff up), but now for whatever reason it has stopped.
有什么办法可以强制执行此操作吗？我的查询正在使用它（并且它加快了速度），但现在无论出于何种原因它已经停止。
Here is the JOIN I want to do this on. The two indexes I want the query to use are scs.CONSUMER_ID_1 and scs_CONSUMER_ID_2
这是我想要执行此操作的 JOIN。我希望查询使用的两个索引是 scs.CONSUMER_ID_1 和 scs_CONSUMER_ID_2
JOIN survey_customer_similarity AS scs
ON cr.CONSUMER_ID=scs.CONSUMER_ID_2
AND cal.SENDER_CONSUMER_ID=scs.CONSUMER_ID_1
OR cr.CONSUMER_ID=scs.CONSUMER_ID_1
AND cal.SENDER_CONSUMER_ID=scs.CONSUMER_ID_2
回答by RichardTheKiwi
See MySQL Docs for FORCE INDEX.
参见 MySQL 文档FORCE INDEX。
JOIN survey_customer_similarity AS scs
FORCE INDEX (CONSUMER_ID_1,CONSUMER_ID_2)
ON
cr.CONSUMER_ID=scs.CONSUMER_ID_2
AND cal.SENDER_CONSUMER_ID=scs.CONSUMER_ID_1
OR cr.CONSUMER_ID=scs.CONSUMER_ID_1
AND cal.SENDER_CONSUMER_ID=scs.CONSUMER_ID_2
As TheScrumMeister has pointed out below, it depends on your data, whether two indexes can actually be used at once.
正如 TheScrumMeister 在下面指出的那样，这取决于您的数据，是否可以真正同时使用两个索引。
下面是一个示例，您需要强制表出现两次以控制查询执行和交集。
Use this to create a table with >100K records, with roughly 1K rows matching the filter i in (2,3)and 1K rows matching j in (2,3):
使用它来创建一个包含 >100K 记录的表，大约有 1K 行匹配过滤器i in (2,3)和 1K 行匹配j in (2,3)：
drop table if exists t1;
create table t1 (id int auto_increment primary key, i int, j int);
create index ix_t1_on_i on t1(i);
create index ix_t1_on_j on t1(j);
insert into t1 (i,j) values (2,2),(2,3),(4,5),(6,6),(2,6),(2,7),(3,2);
insert into t1 (i,j) select i*2, j*2+i from t1;
insert into t1 (i,j) select i*2, j*2+i from t1;
insert into t1 (i,j) select i*2, j*2+i from t1;
insert into t1 (i,j) select i*2, j*2+i from t1;
insert into t1 (i,j) select i*2, j*2+i from t1;
insert into t1 (i,j) select i*2, j*2+i from t1;
insert into t1 (i,j) select i*2, j*2+i from t1;
insert into t1 (i,j) select i*2, j*2+i from t1;
insert into t1 (i,j) select i*2, j*2+i from t1;
insert into t1 (i,j) select i*2, j*2+i from t1;
insert into t1 (i,j) select i*2, j*2+i from t1;
insert into t1 (i,j) select i*2, j*2+i from t1;
insert into t1 (i,j) select i, j from t1;
insert into t1 (i,j) select i, j from t1;
insert into t1 (i,j) select 2, j from t1 where not j in (2,3) limit 1000;
insert into t1 (i,j) select i, 3 from t1 where not i in (2,3) limit 1000;
When doing:
做的时候：
select t.* from t1 as t where t.i=2 and t.j=3 or t.i=3 and t.j=2
you get exactly 8 matches:
你正好得到 8 场比赛：
+-------+------+------+
| id | i | j |
+-------+------+------+
| 7 | 3 | 2 |
| 28679 | 3 | 2 |
| 57351 | 3 | 2 |
| 86023 | 3 | 2 |
| 2 | 2 | 3 |
| 28674 | 2 | 3 |
| 57346 | 2 | 3 |
| 86018 | 2 | 3 |
+-------+------+------+
Use EXPLAINon the query above to get:
使用EXPLAIN上面的查询得到：
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | t | range | ix_t1_on_i,ix_t1_on_j | ix_t1_on_j | 5 | NULL | 1012 | Using where
Even if we add FORCE INDEXto the query on two indexes EXPLAINwill return the exact same thing.
即使我们FORCE INDEX在两个索引上添加查询EXPLAIN也会返回完全相同的东西。
To make it collect across two indexes, and then intersect them, use this:
要使其跨两个索引收集，然后将它们相交，请使用以下命令：
select t.* from t1 as a force index(ix_t1_on_i)
join t1 as b force index(ix_t1_on_j) on a.id=b.id
where a.i=2 and b.j=3 or a.i=3 and b.j=2
Use that query with explainto get:
使用该查询explain获取：
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | a | range | ix_t1_on_i | ix_t1_on_i | 5 | NULL | 1019 | Using where
1 | SIMPLE | b | range | ix_t1_on_j | ix_t1_on_j | 5 | NULL | 1012 | Using where; Using index
This proves that the indexes are being used. But that may or may not be faster depending on many other factors.
这证明正在使用索引。但这可能会或可能不会更快，这取决于许多其他因素。
回答by Confusion
MySQL only supports using a single index per join. If you want it to utilize two columns as indices in the join, you should create a single index over those two columns. Note that this isn't as bad as it seems, because an index over (a,b) doubles as an index over just a.
MySQL 仅支持每个连接使用单个索引。如果您希望它在连接中使用两列作为索引，您应该在这两列上创建一个索引。请注意，这并不像看起来那么糟糕，因为 (a,b) 上的索引可以作为 a 上的索引加倍。
See the MySQL manual
请参阅MySQL 手册
MySQL cannot use an index if the columns do not form a leftmost prefix of the index.
如果列不构成索引的最左前缀，则 MySQL 不能使用索引。
