强制MySQL在Join上使用两个索引

编程入门行业动态更新时间:2024-10-11 21:21:47

本文介绍了强制MySQL在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:

dev.mysql/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:

即使我们在两个索引的查询中添加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:

这证明正在使用索引.但这可能会更快，也可能不会更快，这取决于许多其他因素.

This proves that the indexes are being used. But that may or may not be faster depending on many other factors.

更多推荐