MySQL InnoDB采取的效率不高的执行计划

问题描述

我无法使用MySQL InnoDB优化器优化请求.以下查询(查询1)有效运行:

I have trouble to optimize a request with the MySQL InnoDB optimizer.The following query (query 1) runs efficiently:

explain select * from ah_problems
where rnid in (6022342, 6256614, 5842714, 6302489)
and fieldid in (5,6);

和计划(计划1)如下:

and the plan (plan 1) is as follows:

id select_type table       type  possible_keys                   key           key_len ref rows Extra
=  ======      =========== ===== =============================== ============= ======= === ==== =====
1  SIMPLE      ah_problems range CONSTRAINTFIELDID,RNID__FIELDID RNID__FIELDID 8           33   Using where

到目前为止，很好.

以下略作修改的查询(查询2)将采用灾难性的执行计划:

Whereas the slightly modified query (query 2) below will take a catastrophic execution plan:

explain select * from ah_problems
where rnid in (select rec.rnid as record_id from ar_records rec where rnid in (6022342, 6256614, 5842714, 6302489))
and fieldid in (5, 6)

结果是一样的，但是计划(计划2)现在正在这样做:

The result is the same, but the plan (plan 2) is now doing this:

id select_type        table       type            possible_keys      key      key_len ref  rows     Extra
=  ======             =========== =====           ================== ======== ======= ==== =======  =====
1  PRIMARY            ah_problems ALL             CONSTRAINTFIELDID                        36177754 Using where
2  DEPENDENT SUBQUERY rec         unique_subquery PRIMARY            PRIMARY  4       func 1        Using index; Using where

如果您想知道，该新子查询...

If you wonder, that new sub-query...

select rec.rnid as record_id from ar_records rec where rnid in (6022342, 6256614, 5842714, 6302489)

...无非是返回查询1中硬编码的四行

...does nothing more than returning the four rows that were hard-coded in query 1:

6022342
6256614
5842714
6302489

所以查询(1)和(2)是等效的.

so queries (1) and (2) are equivalent.

猜猜是什么，我需要查询2，而不是一个.我希望查询2与查询1一样高效.我尝试了以下操作:

Guess what, I need query 2, and not one. And I want query 2 to be as efficient as query 1. I tried the following:

1. 查询3:在查询2中添加FORCE INDEX(RNID_FIELDID).MySQL只会忽略它.

1. Query 3: Add FORCE INDEX(RNID_FIELDID) to query 2. MySQL simply ignores it.

从ah_problems强制索引(rnid__fieldid)中解释选择*在rnid中(从ar_records rec中选择rec.rnid作为record_id在其中rnid in(6022342，6256614，5842714，6302489))和(5,6)

explain select * from ah_problems force index (rnid__fieldid)where rnid in (select rec.rnid as record_id from ar_records rec where rnid in (6022342, 6256614, 5842714, 6302489))and fieldid in (5,6)

执行计划与计划2相同.

The execution plan is the same as plan 2.

1. 查询4:在查询3中添加ORDER BY RNID, FIELDID.我在其他一些问题上看到这可能会欺骗优化器.这没有帮助.

1. Query 4: Add an ORDER BY RNID, FIELDID to query 3. I saw on some other questions that this might trick the optimizer. It doesn't help.

从ah_problems强制索引(rnid__fieldid)中解释选择*在rnid中(从ar_records rec中选择rec.rnid作为record_id在其中rnid in(6022342，6256614，5842714，6302489))和fieldid由rnid，fieldid依(5，6)顺序

explain select * from ah_problems force index (rnid__fieldid)where rnid in (select rec.rnid as record_id from ar_records rec where rnid in (6022342, 6256614, 5842714, 6302489))and fieldid in (5, 6) order by rnid, fieldid

计划4现在正在使用索引，但是行数仍然是灾难性的:

The plan 4 is now using the index, but the row count is still catastrophic:

id select_type        table       type            possible_keys      key           key_len ref  rows      Extra
=  ======             =========== =====           ================== ========      ======= ==== =======   =====
1  PRIMARY            ah_problems index                              RNID__FIELDID 8             36179307 Using where
2  DEPENDENT SUBQUERY rec         unique_subquery PRIMARY            PRIMARY       4       func  1        Using index; Using where

如果有帮助，这是我的ah_problems表的定义.不幸的是，我无法更改表的定义.我可以做些什么使MySQL优化器使用计划1攻击查询2中的表ah_problems?

If this helps, this is the definition of my ah_problems tables. I'm unfortunately not able to change the definition of the table. Is there anything I can do to make MySQL optimizer use plan 1 to attack table ah_problems in query 2?

CREATE TABLE `ah_problems` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Identifier for update statements',
  `RNID` int(11) NOT NULL COMMENT 'Record number',
  `FIELDID` int(11) NOT NULL COMMENT 'Which field is value in',
  `VALUE` varchar(255) NOT NULL COMMENT 'The value the field got on MODIFIED_DATE',
  `PREVIOUSID` int(11) DEFAULT NULL COMMENT 'Reference to previous value',
  `MODIFIED_DATE` datetime NOT NULL COMMENT 'When was it changed',
  `MODIFIED_GROUPID` int(11) DEFAULT NULL COMMENT 'In what group did modified_userid change it',
  `MODIFIED_USERID` int(11) NOT NULL COMMENT 'Who changed it',
  PRIMARY KEY (`ID`),
  KEY `CONSTRAINTFIELDID` (`FIELDID`),
  KEY `CONSTRAINTMODIFIED_GROUPID` (`MODIFIED_GROUPID`),
  KEY `CONSTRAINTMODIFIED_USERID` (`MODIFIED_USERID`),
  KEY `CONSTRAINTPREVIOUSID` (`PREVIOUSID`),
  KEY `RNID__FIELDID` (`RNID`,`FIELDID`),
  CONSTRAINT `HPRB_FIELD` FOREIGN KEY (`FIELDID`) REFERENCES `ad_fields` (`ID`),
  CONSTRAINT `HPRB_MODIFIED_GROUP` FOREIGN KEY (`MODIFIED_GROUPID`) REFERENCES `ap_groups` (`ID`),
  CONSTRAINT `HPRB_MODIFIED_USER` FOREIGN KEY (`MODIFIED_USERID`) REFERENCES `ap_users` (`ID`),
  CONSTRAINT `HPRB_PREVIOUS` FOREIGN KEY (`PREVIOUSID`) REFERENCES `ah_problems` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `HPRB_RN` FOREIGN KEY (`RNID`) REFERENCES `ar_records` (`RNID`)
) ENGINE=InnoDB AUTO_INCREMENT=72305308 DEFAULT CHARSET=utf8 COMMENT='PTR history'$$

推荐答案

MySQL无法优化IN子查询以使其领先(仅执行一次)，它始终在循环中针对主查询中的每条记录执行.

MySQL cannot optimize the IN subquery to be leading (executed only once), it's always executed for each record in the main query in a loop.

用联接替换它:

SELECT ahp.*
FROM   ar_records ar
JOIN   ah_problems ahp
ON     ahp.rnid = ar.rnid
       AND ahp.fieldId IN (5, 6)
WHERE  ar.rnid IN (6022342, 6256614, 5842714, 6302489)

这篇关于MySQL InnoDB采取的效率不高的执行计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！