Mysql查询非常慢并且没有使用正确的索引(通过使用group by、IN运算符)

Question

下面的查询执行时间超过 5 秒(表包含 1m+ 记录)。外部查询没有使用正确的索引，它总是使用全表扫描来获取数据。有人可以帮助我如何优化它..

查询

SELECT x 
  FROM UserCardXref x 
 WHERE x.userCardXrefId IN(
    SELECT MAX(y.userCardXrefId) 
      FROM UserCardXref y 
     WHERE y.usrId IN(1001,1002) 
     GROUP 
        BY y.usrId 
    HAVING COUNT(*) > 0
    )

查询解释

查询统计

执行计划

Answer 1

我会将查询重写为

select x.* from UserCardXref x
join (
 select max(userCardXrefId),usrId from UserCardXref 
 where usrId in (1001,1002) group by usrId
)y on x.userCardXrefId = y.userCardXrefId

您需要的索引

alter table UserCardXref add index userCardXrefId_idx(userCardXrefId)

usrId 已根据解释计划建立索引，因此无需添加

此外，您已经having count(*)>0，您已经在使用 max() 函数，并且对于给定组，它永远不会有 0 行，因此我已将其删除。