当前位置: 首页 > 面试题库 >

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

华睿识
2023-03-14
问题内容

我试图强迫MySQL使用两个索引。我正在加入一个表,我想利用两个索引之间的交叉。具体术语是“使用相交”,这是指向MySQL文档的链接:

http://dev.mysql.com/doc/refman/5.0/zh-CN/index-merge-
optimization.html

有什么办法可以强制实施吗?我的查询正在使用它(并且它加快了速度),但是现在无论出于何种原因它都停止了。

这是我要继续执行的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

问题答案:

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

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

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

使用它来创建一个包含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 |
+-------+------+------+

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可获取:

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

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



 类似资料:
  • 问题内容: 我在一次采访中遇到了这个问题,不知道如何回答: 有一个表在列上有一个索引,您可以查询: 该查询花费的时间太长,您发现该索引没有被使用。如果您认为使用索引将使查询的性能更好,那么如何强制查询使用索引呢? 问题答案: 您可以使用优化程序提示 等等… 有关使用优化程序提示的更多信息:http : //download.oracle.com/docs/cd/B19306_01/server.1

  • 我有三张桌子: 表名:流派 表字段:id、名称 例1,行动 我试图有一个查询,得到所有的信息从动漫,也得到我的动漫的流派名称。 我有以下疑问: 这让我得到了动漫信息和所有流派的主要ID,但我没有他们的名字。 我一直在研究,但也许我做得不对。 顺便说一句,“动漫”是一个电视节目,所以它可以有多种流派,一对多的关系。

  • 问题内容: 在我的工作主要有两个指标集群,比方说和,但是这两个指标每一天索引,以便normaly我有和。 我要的是有一个别名,其收集和在一起,并命名。 有谁知道如何使用logstash在一个别名中收集两个索引? 先感谢您 问题答案: 据我所知,没有办法直接用logstash做到这一点。您可以使用Elasticsearch API从外部程序执行此操作:http : //www.elastic.co/

  • 问题内容: 我面临以下问题,我不确定什么是最佳实践。 考虑下表(该表会变大): id PK | Giver_id FK | FK | 日期 我正在使用InnoDB,据我了解,它会自动为两个外键列创建索引。但是,我还将在需要匹配以下特定组合的情况下进行大量查询: 。 每个这样的组合在表中将是唯一的。 在这些列上添加双列索引有什么好处,还是理论上两个单独的索引足够/相同? 问题答案: 如果您有两个单列

  • 问题内容: 我有一个Linux设备驱动程序,该驱动程序与一个设备相连,该设备理论上可以使用64位地址执行DMA。我想测试一下是否确实有效。 有没有一种简单的方法可以强制Linux计算机不使用物理地址4G以下的任何内存?如果内核映像位于低内存中就可以了;我只想能够强制我知道所有动态分配的缓冲区,而为我分配的任何内核或用户缓冲区都无法以32位寻址的情况。这有点蛮力,但是比我能想到的任何东西都要全面。

  • 问题内容: 这可能不是很明智,但是如果子句中有重复的条件,我想让MySQL返回完全相同的重复行。这可能吗? 举个例子: 我想让MySQL向我返回ID为5的行3次,ID为1和2的行两次,以及3和4的行。 由于参数的长度以及重复计数(一次,两次,三次等)将是任意的,我不想依赖or 。这样是否可能呢? 问题答案: 我不确定为什么要禁止JOIN,因为JOIN对于SQL来说是相当重要的。这就像禁止使用功能语