我有一个MySQL InnoDB数据库。一个名为affymetrixProbeset的表包含3亿多行。
用INNER JOIN查询此表与其他表,使用ORDER BY和偏移量/限制将花费将近4分钟。我看到创建子查询可以进行更多的优化。我尝试从使用INNER JOIN的初始查询转移到使用子查询的方法。
查询是:
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是
+----+-------------+--------------------+------------+--------+------------------------------+-------------+---------+--------------------------------------------------+-------+----------+----------------------------------------------+
| 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表进行排序。你能给我解释一下为什么不像我预料的那样做吗?
我还检查了将此查询分隔成两部分的性能。
第一项是:
SELECT affymetrixChip.bgeeAffymetrixChipId
FROM affymetrixChip INNER JOIN cond ON affymetrixChip.conditionId = cond.conditionId
WHERE cond.speciesId = 9606;然后我将bgeeAffymetrixChipIds传递给查询。
SELECT affymetrixProbeset.*
FROM affymetrixProbeset
WHERE affymetrixProbeset.bgeeAffymetrixChipId IN(.....)
order by affymetrixProbeset.affymetrixProbesetId, affymetrixProbeset.bgeeGeneId
limit 10;我在查询中编写了.....,因为我有超过5000个ID。
查询在使用主键进行排序时几乎立即运行。
您能解释一下为什么子查询没有像我预期的那样执行吗?除了创建2个查询之外,还有其他优化查询的选项吗?
更新:
添加了@Rick建议的一些索引后,explain现在看起来如下:
+----+-------------+--------------------+------------+--------+-----------------------------------------------------+-------------+---------+------------------------------------------------+-------+----------+----------------------------------------------+
| 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是:
# 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结果的表的大小
| 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个结果
SELECT COUNT(*) FROM cond WHERE speciesId = 9606;下面的查询给出5452个结果
SELECT COUNT(bgeeAffymetrixChipId) FROM affymetrixChip INNER JOIN cond ON cond.conditionId = affymetrixChip.conditionId WHERE cond.speciesId = 9606;发布于 2022-11-26 05:08:46
不要使用IN ( SELECT ... );它常常是很糟糕的优化。切换到JOIN。
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;并有以下指标:
probe: INDEX(bgeeAffymetrixChipId, affymetrixProbesetId, bgeeGeneId)
chip: INDEX(conditionId, bgeeAffymetrixChipId)
cond: INDEX(speciesId, conditioned)(我希望这将使合并查询的速度与2-查询公式一样快,从而使您的问题没有意义。)
问题的一部分可能是在*上拖了很多行,然后对它们进行排序,最后只传递10行。如果这个公式不够有效,我可以建议一种避免获取*超过10次的方法,但它涉及一个不同的“派生”表。
如需进一步讨论,请提供SHOW CREATE TABLE。
关于索引(我的答案的这一部分可能没有解决原来的问题)。
# 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不允许。)
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;或者,为了避免到处乱跑"*":
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, repeatedhttps://dba.stackexchange.com/questions/320207
复制相似问题