首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >子查询不按预期工作的优化

问子查询不按预期工作的优化
EN

Database Administration用户
提问于 2022-11-25 17:18:22
回答 1查看 63关注 0票数 0

我有一个MySQL InnoDB数据库。一个名为affymetrixProbeset的表包含3亿多行。

用INNER JOIN查询此表与其他表,使用ORDER BY和偏移量/限制将花费将近4分钟。我看到创建子查询可以进行更多的优化。我尝试从使用INNER JOIN的初始查询转移到使用子查询的方法。

查询是:

代码语言:javascript
复制
SELECT affymetrixProbeset.* 
FROM affymetrixProbeset 
WHERE affymetrixProbeset.bgeeAffymetrixChipId IN(
    SELECT affymetrixChip.bgeeAffymetrixChipId 
    FROM affymetrixChip INNER JOIN cond ON affymetrixChip.conditionId = cond.conditionId
    WHERE cond.speciesId = 9606)
order by affymetrixProbeset.affymetrixProbesetId, affymetrixProbeset.bgeeGeneId
limit 10;

ORDER BY中使用的列对应于表affymetrixProbeset的主键。

与此查询相关的explain是

代码语言:javascript
复制
+----+-------------+--------------------+------------+--------+------------------------------+-------------+---------+--------------------------------------------------+-------+----------+----------------------------------------------+
| id | select_type | table              | partitions | type   | possible_keys                | key         | key_len | ref                                              | rows  | filtered | Extra                                        |
+----+-------------+--------------------+------------+--------+------------------------------+-------------+---------+--------------------------------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | affymetrixChip     | NULL       | index  | PRIMARY,conditionId          | conditionId | 3       | NULL                                             | 12990 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | cond               | NULL       | eq_ref | PRIMARY,speciesId            | PRIMARY     | 3       | bgee_v15_dev.affymetrixChip.conditionId          |     1 |    10.99 | Using where                                  |
|  1 | SIMPLE      | affymetrixProbeset | NULL       | ref    | PRIMARY,bgeeAffymetrixChipId | PRIMARY     | 3       | bgee_v15_dev.affymetrixChip.bgeeAffymetrixChipId |   175 |   100.00 | NULL                                         |
+----+-------------+--------------------+------------+--------+------------------------------+-------------+---------+--------------------------------------------------+-------+----------+----------------------------------------------+

解释与我最初的查询完全相同。查询所需时间相同(~4分钟)

看起来ORDER BY不是在affymetrixProbeset表上完成的,而是直接在作为子查询一部分的affymetrixChip表上完成的。我期望MySQL运行子查询,然后使用affymetrixProbeset表进行排序。你能给我解释一下为什么不像我预料的那样做吗?

我还检查了将此查询分隔成两部分的性能。

第一项是:

代码语言:javascript
复制
SELECT affymetrixChip.bgeeAffymetrixChipId 
FROM affymetrixChip INNER JOIN cond ON affymetrixChip.conditionId = cond.conditionId
WHERE cond.speciesId = 9606;

然后我将bgeeAffymetrixChipIds传递给查询。

代码语言:javascript
复制
SELECT affymetrixProbeset.* 
FROM affymetrixProbeset 
WHERE affymetrixProbeset.bgeeAffymetrixChipId IN(.....)
order by affymetrixProbeset.affymetrixProbesetId, affymetrixProbeset.bgeeGeneId
limit 10;

我在查询中编写了.....,因为我有超过5000个ID。

查询在使用主键进行排序时几乎立即运行。

您能解释一下为什么子查询没有像我预期的那样执行吗?除了创建2个查询之外,还有其他优化查询的选项吗?

更新:

添加了@Rick建议的一些索引后,explain现在看起来如下:

代码语言:javascript
复制
+----+-------------+--------------------+------------+--------+-----------------------------------------------------+-------------+---------+------------------------------------------------+-------+----------+----------------------------------------------+
| id | select_type | table              | partitions | type   | possible_keys                                       | key         | key_len | ref                                            | rows  | filtered | Extra                                        |
+----+-------------+--------------------+------------+--------+-----------------------------------------------------+-------------+---------+------------------------------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | affymetrixChip     | NULL       | index  | PRIMARY,conditionId                                 | conditionId | 6       | NULL                                           | 12561 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | cond               | NULL       | eq_ref | PRIMARY,speciesId                                   | PRIMARY     | 3       | bgee_v15_0.affymetrixChip.conditionId          |     1 |    10.94 | Using where                                  |
|  1 | SIMPLE      | affymetrixProbeset | NULL       | ref    | PRIMARY,bgeeAffymetrixChipId_2,bgeeAffymetrixChipId | PRIMARY     | 3       | bgee_v15_0.affymetrixChip.bgeeAffymetrixChipId |   174 |   100.00 | NULL                                         |
+----+-------------+--------------------+------------+--------+-----------------------------------------------------+-------------+---------+------------------------------------------------+-------+----------+----------------------------------------------+

查询仍然需要3分钟才能运行。

这些表的PK/FK/索引的SHOW CREATE TABLE是:

代码语言:javascript
复制
# table cond
  PRIMARY KEY (`conditionId`),
  UNIQUE KEY `speciesId` (`speciesId`,`conditionId`),
  UNIQUE KEY `anatEntityId` (`anatEntityId`,`cellTypeId`,`stageId`,`speciesId`,`sex`,`sexInferred`,`strain`),
  KEY `exprMappedConditionId` (`exprMappedConditionId`),
  KEY `cellTypeId` (`cellTypeId`),
  KEY `stageId` (`stageId`),
  CONSTRAINT `cond_ibfk_1` FOREIGN KEY (`exprMappedConditionId`) REFERENCES `cond` (`conditionId`) ON DELETE CASCADE,
  CONSTRAINT `cond_ibfk_2` FOREIGN KEY (`anatEntityId`) REFERENCES `anatEntity` (`anatEntityId`) ON DELETE CASCADE,
  CONSTRAINT `cond_ibfk_3` FOREIGN KEY (`cellTypeId`) REFERENCES `anatEntity` (`anatEntityId`) ON DELETE CASCADE,
  CONSTRAINT `cond_ibfk_4` FOREIGN KEY (`stageId`) REFERENCES `stage` (`stageId`) ON DELETE CASCADE,
  CONSTRAINT `cond_ibfk_5` FOREIGN KEY (`speciesId`) REFERENCES `species` (`speciesId`) ON DELETE CASCADE

# table affymetrixChip
  PRIMARY KEY (`bgeeAffymetrixChipId`),
  UNIQUE KEY `affymetrixChipId` (`affymetrixChipId`,`microarrayExperimentId`),
  UNIQUE KEY `conditionId` (`conditionId`,`bgeeAffymetrixChipId`),
  KEY `microarrayExperimentId` (`microarrayExperimentId`),
  KEY `chipTypeId` (`chipTypeId`),
  CONSTRAINT `affymetrixChip_ibfk_1` FOREIGN KEY (`microarrayExperimentId`) REFERENCES `microarrayExperiment` (`microarrayExperimentId`) ON DELETE CASCADE,
  CONSTRAINT `affymetrixChip_ibfk_2` FOREIGN KEY (`chipTypeId`) REFERENCES `chipType` (`chipTypeId`) ON DELETE SET NULL,
  CONSTRAINT `affymetrixChip_ibfk_3` FOREIGN KEY (`conditionId`) REFERENCES `cond` (`conditionId`) ON DELETE CASCADE

#table affymetrixProbeset
  PRIMARY KEY (`bgeeAffymetrixChipId`,`affymetrixProbesetId`),
  UNIQUE KEY `bgeeAffymetrixChipId_2` (`bgeeAffymetrixChipId`,`affymetrixProbesetId`,`bgeeGeneId`),
  KEY `bgeeAffymetrixChipId` (`bgeeAffymetrixChipId`,`expressionId`,`bgeeGeneId`,`normalizedSignalIntensity`),
  KEY `expressionId` (`expressionId`),
  KEY `bgeeGeneId` (`bgeeGeneId`,`bgeeAffymetrixChipId`,`affymetrixProbesetId`),
  CONSTRAINT `affymetrixProbeset_ibfk_1` FOREIGN KEY (`bgeeAffymetrixChipId`) REFERENCES `affymetrixChip` (`bgeeAffymetrixChipId`) ON DELETE CASCADE,
  CONSTRAINT `affymetrixProbeset_ibfk_2` FOREIGN KEY (`bgeeGeneId`) REFERENCES `gene` (`bgeeGeneId`) ON DELETE CASCADE,
  CONSTRAINT `affymetrixProbeset_ibfk_3` FOREIGN KEY (`expressionId`) REFERENCES `expression` (`expressionId`) ON DELETE SET NULL

更新2:

作为SHOW TABLE STATUS结果的表的大小

代码语言:javascript
复制
| Name                                                | Engine | Version | Row_format | Rows       | Avg_row_length | Data_length   | Max_data_length | Index_length | Data_free    | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment     
| affymetrixChip                                      | InnoDB |      10 | Dynamic    |      13020 |            122 |       1589248 |               0 |      1097728 |      2097152 |         104523 | 2022-12-02 13:23:15 | NULL                | NULL       | utf8_general_ci |     NULL |                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| affymetrixProbeset                                  | InnoDB |      10 | Dynamic    |  312237020 |             93 |   29169287168 |               0 |  41072721920 |      6291456 |           NULL | 2022-10-31 09:34:15 | NULL                | NULL       | utf8_general_ci |     NULL |                |  
| cond                                                | InnoDB |      10 | Dynamic    |      44578 |            106 |       4734976 |               0 |      6815744 |      2097152 |          44663 | 2022-12-02 13:19:09 | NULL                | NULL       | utf8_general_ci |     NULL |                |  

下面的查询提供了4889个结果

代码语言:javascript
复制
SELECT COUNT(*) FROM cond WHERE speciesId = 9606;

下面的查询给出5452个结果

代码语言:javascript
复制
SELECT COUNT(bgeeAffymetrixChipId) FROM affymetrixChip INNER JOIN cond ON cond.conditionId = affymetrixChip.conditionId WHERE cond.speciesId = 9606;
EN

回答 1

Database Administration用户

发布于 2022-11-26 05:08:46

不要使用IN ( SELECT ... );它常常是很糟糕的优化。切换到JOIN。

代码语言:javascript
复制
SELECT  probe.*
    FROM  affymetrixProbeset AS probe
    JOIN  affymetrixChip AS chip
             ON  probe.bgeeAffymetrixChipId = chip.bgeeAffymetrixChipId
    JOIN  cond  ON chip.conditionId = cond.conditionId
    WHERE  cond.speciesId = 9606
    order by  probe.affymetrixProbesetId, probe.bgeeGeneId
    limit  10;

并有以下指标:

代码语言:javascript
复制
probe:  INDEX(bgeeAffymetrixChipId, affymetrixProbesetId, bgeeGeneId)
chip:  INDEX(conditionId,  bgeeAffymetrixChipId)
cond:  INDEX(speciesId, conditioned)

(我希望这将使合并查询的速度与2-查询公式一样快,从而使您的问题没有意义。)

问题的一部分可能是在*上拖了很多行,然后对它们进行排序,最后只传递10行。如果这个公式不够有效,我可以建议一种避免获取*超过10次的方法,但它涉及一个不同的“派生”表。

如需进一步讨论,请提供SHOW CREATE TABLE。

关于索引(我的答案的这一部分可能没有解决原来的问题)。

代码语言:javascript
复制
# table cond
  PRIMARY KEY                        (`conditionId`),
  UNIQUE KEY `speciesId` (`speciesId`,`conditionId`),
      -- Since the PK contains all the columns of the above Unique, it is automatically unique
      -- recommend degrading to INDEX

# table affymetrixChip
  PRIMARY KEY                            (`bgeeAffymetrixChipId`),
  UNIQUE KEY `conditionId` (`conditionId`,`bgeeAffymetrixChipId`),
         --  See above

#table affymetrixProbeset
  PRIMARY KEY (`bgeeAffymetrixChipId`,`affymetrixProbesetId`),
  UNIQUE KEY `bgeeAffymetrixChipId_2`
              (`bgeeAffymetrixChipId`,`affymetrixProbesetId`,`bgeeGeneId`),
       -- The first 2 columns of this UNIQUE exactly match the PK.
       --  The key is completely useless; DROP it.

我怀疑我是否见过在一个表中有3个UNIQUE键(包括PK)的正当理由。重新考虑它们是否有效。此外,当有一个可用于PK的AUTO_INCREMENT时,请考虑删除任何UNIQUE。

(警告:UNIQUE允许非空列;PK不允许。)

更多配方

代码语言:javascript
复制
SELECT  probe.*
    FROM  
        ( SELECT  chip.bgeeAffymetrixChipId
            FROM  affymetrixChip AS chip
            INNER JOIN  cond  ON chip.conditionId = cond.conditionId
            WHERE  cond.speciesId = 9606 
        ) AS x
    JOIN  affymetrixProbeset AS probe USING(bgeeAffymetrixChipId)
    ORDER BY  probe.affymetrixProbesetId, probe.bgeeGeneId
    limit  10;

或者,为了避免到处乱跑"*":

代码语言:javascript
复制
SELECT  probe.*
    FROM  
        ( SELECT  p.affymetrixProbesetId, p.bgeeGeneId
            FROM  
                ( SELECT  chip.bgeeAffymetrixChipId
                    FROM  affymetrixChip AS chip
                    INNER JOIN  cond  ON chip.conditionId = cond.conditionId
                    WHERE  cond.speciesId = 9606 
                ) AS x
            JOIN  affymetrixProbeset AS p USING(bgeeAffymetrixChipId)
            ORDER BY  p.affymetrixProbesetId, p.bgeeGeneId
            limit  10   -- needed only here
        ) AS y
    JOIN  affymetrixProbeset AS probe
             USING (affymetrixProbesetId, bgeeGeneId)
    ORDER BY  p.affymetrixProbesetId, p.bgeeGeneId;  -- Yes, repeated
票数 0
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://dba.stackexchange.com/questions/320207

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档