请参阅 MySQL 文档FORCE INDEX http://dev.mysql.com/doc/refman/5.7/en/index-hints.html.
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 在下面指出的那样,这取决于您的数据,两个索引是否实际上可以同时使用。
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)
和 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;
做时:
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 |
+-------+------+------+
Use 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
即使我们添加FORCE INDEX
对两个索引的查询EXPLAIN
将返回完全相同的事情.
要使其收集两个索引,然后将它们相交,请使用以下命令:
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
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
这证明索引正在被使用。但这可能会更快,也可能不会更快,具体取决于许多其他因素。