为什么这个MySQL查询没有使用完整的索引？

Question

你能帮我回答这个问题吗？

SELECT p.patid, MAX(c1.eventdate) as eventdate 
from patient as p 
left join op_adv_effects._clinical as c1 on p.patid = c1.patid 
where c1.eventdate < p.case_index 
group by p.patid

以下是两个表的SHOW CREATE TABLE的输出：

patient CREATE TABLE `patient` (
  `patid` int(10) unsigned NOT NULL,
  `case_index` date NOT NULL,
  PRIMARY KEY (`patid`,`case_index`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs

_clinical   CREATE TABLE `_clinical` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `patid` int(10) unsigned NOT NULL,
  `eventdate` date NOT NULL,
  `medcode` mediumint(8) unsigned DEFAULT NULL,
  `adid` mediumint(8) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_clin_eventdate_medcode` (`patid`,`eventdate`,`medcode`),
  KEY `idx_clin_eventdate_adid` (`patid`,`eventdate`,`adid`)
) ENGINE=InnoDB AUTO_INCREMENT=62407536 DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs

explain返回如下内容：

*************************** 1. row ********************
           id: 1
  select_type: SIMPLE
        table: p
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 7
          ref: NULL
         rows: 182939
        Extra: Using index
*************************** 2. row ********************
           id: 1
  select_type: SIMPLE
        table: c1
         type: ref
possible_keys: idx_clin_eventdate_medcode,idx_clin_eventdate_adid
          key: idx_clin_eventdate_medcode
      key_len: 4
          ref: gprd_opadveff_extra_elisa.p.patid
         rows: 171
        Extra: Using where; Using index

为什么它不使用idx_clin_eventdate_medcode的前两个字段，即( patid，eventdate)，而只使用patid(参见ref列)？

如果我将where条件更改为相等，它可以很好地工作：

SELECT p.patid, MAX(c1.eventdate) as eventdate 
from patient as p 
left join op_adv_effects._clinical as c1 on p.patid = c1.patid 
where c1.eventdate = p.case_index 
group by p.patid

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 7
          ref: NULL
         rows: 182939
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c1
         type: ref
possible_keys: idx_clin_eventdate_medcode,idx_clin_eventdate_adid
          key: idx_clin_eventdate_medcode
      key_len: 7
          ref: gprd_opadveff_extra_elisa.p.patid,gprd_opadveff_extra_elisa.p.cas
e_index
         rows: 1
        Extra: Using index

对一些建议的变体也有相同的结果：

explain SELECT  patid,
(SELECT  eventdate
FROM  op_adv_effects._clinical
WHERE  patid = p.patid
AND eventdate < p.case_index
ORDER BY  eventdate DESC
LIMIT  1 ) AS eventdate
FROM  patient AS p;

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: p
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 7
          ref: NULL
         rows: 182939
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: _clinical
         type: ref
possible_keys: idx_clin_eventdate_medcode,idx_clin_eventdate_adid
          key: idx_clin_eventdate_medcode
      key_len: 4
          ref: gprd_opadveff_extra_elisa.p.patid
         rows: 171
        Extra: Using where; Using index; Using filesort


explain SELECT  patid, 
( SELECT  MAX(eventdate)
FROM  op_adv_effects._clinical
WHERE  patid = p.patid
AND  eventdate < p.case_index) AS eventdate
FROM  patient AS p;

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: p
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 7
          ref: NULL
         rows: 182939
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: _clinical
         type: ref
possible_keys: idx_clin_eventdate_medcode,idx_clin_eventdate_adid
          key: idx_clin_eventdate_medcode
      key_len: 4
          ref: gprd_opadveff_extra_elisa.p.patid
         rows: 171
        Extra: Using where; Using index

该查询是一个更复杂的查询的一部分，如下所示。然而，这只是几个复杂查询中的一个示例，这些查询都应该在eventdate上使用索引中缺失的部分。由于这个原因，它是相当重要的。

CREATE TABLE bmi_lp
(PRIMARY KEY (patid))
ENGINE=INNODB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
SELECT tmp.patid, a2.data3 as bmi_lp, tmp.eventdate as bmi_lp_date 
from ( 
SELECT p.patid, MAX(c.eventdate) as eventdate 
from patient as p 
left join op_adv_effects._clinical as c1 on p.patid = c1.patid 
left join op_adv_effects._additional as a1 on c1.patid = a1.patid 
where c1.adid <> 0 and c1.adid = a1.adid 
and a1.enttype = 13 
and a1.data3 is not null 
and c1.eventdate < p.case_index 
group by p.patid 
order by p.patid) as tmp 
left join op_adv_effects._clinical   as c2 on tmp.patid = c2.patid 
left join op_adv_effects._additional as a2 on c2.patid = a2.patid 
where tmp.eventdate = c2.eventdate and c2.adid = a2.adid

Answer 1

由于WHERE的原因，您现在正在执行INNER JOIN。你是有意这样做的吗？

无论如何，由于<的原因，索引现在不能使用，如果您有一个使用不同顺序的索引，它可以工作。

例如，在PostgreSQL中，您可以这样做：

CREATE INDEX idx_clin_eventdate_medcode ON _clinical (patid ASC, eventdate DESC);

不幸的是，在MySQL中，DESC和ASC操作符是不可操作的(每个MySQL版本至少到5.7 )。因此，除非您能够反转查询(使用>而不是<)，否则MySQL不能有效地使用索引。

请注意，不使用索引可能会更快，这取决于具体情况。因为它只有171行，所以我不会太担心。

Answer 2

试一试：

SELECT  patid, 
      ( SELECT  MAX(eventdate)
            FROM  op_adv_effects._clinical
            WHERE  patid = p.patid
              AND  eventdate < p.case_index 
      ) AS eventdate
    FROM  patient AS p;

(不需要GROUP BY。)

下面是一个使用LIMIT 1而不是MAX的变体

SELECT  patid, 
      ( SELECT  eventdate
            FROM  op_adv_effects._clinical
            WHERE  patid = p.patid
              AND  eventdate < p.case_index
            ORDER BY  eventdate DESC
            LIMIT  1 
      ) AS eventdate
    FROM  patient AS p;

输出中有多少行？