强制MySQL在Join上使用两个索引 [英] Force MySQL to use two indexes on a Join

问题描述

我试图强迫MySQL使用两个索引.我正在联接一个表，我想利用两个索引之间的交叉.具体术语是使用相交"，这是指向MySQL文档的链接:

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:

http://dev.mysql.com/doc/refman/5.0/zh-CN/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.

这是我要继续进行的联接.我希望查询使用的两个索引是scs.CONSUMER_ID_1和scs_CONSUMER_ID_2

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 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

推荐答案

有关 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

正如TheScrumMeister在下面指出的，它是否可以同时实际使用两个索引取决于您的数据.

---

这是一个示例，您需要强制该表出现两次以控制查询的执行和交集.

As TheScrumMeister has pointed out below, it depends on your data, whether two indexes can actually be used at once.

---

Here's an example where you need to force the table to appear twice to control the query execution and intersection.

使用它来创建一个包含超过100K条记录的表，其中约有1K行与过滤器i in (2,3)相匹配，并且有1000万行与j in (2,3)相匹配:

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):

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;

这样做时:

select t.* from t1 as t where t.i=2 and t.j=3 or t.i=3 and t.j=2

您完全有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 |
+-------+------+------+

在上面的查询中使用EXPLAIN可以获取:

Use EXPLAIN on the query above to get:

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

即使我们在两个索引的查询中添加FORCE INDEX，EXPLAIN也会返回完全相同的东西.

Even if we add FORCE INDEX to the query on two indexes EXPLAIN will return the exact same thing.

要使其跨两个索引收集，然后相交，请使用以下方法:

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

将该查询与explain一起使用可获取:

Use that query with explain to get:

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.

这篇关于强制MySQL在Join上使用两个索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！