真正希望某种性能的高手可以向我解释为什么单次连接会导致查询变慢10倍。(此外,请不要嘲笑此查询的大小!我想获取数据库中的整个目录以通过一个查询进行输出。我不确定将其分解为较小的查询是否会更快,但是似乎不正确。)
SELECT `c`.`categoryID`,
`cl`.`name` AS `category_name`,
`v`.*,
TRUE AS `categoried`,
GROUP_CONCAT(DISTINCT t_v.iso_3166_1_alpha_2) AS `video_territories`,
GROUP_CONCAT(DISTINCT t_c.iso_3166_1_alpha_2) AS `category_territories`,
`vl`.*,
GROUP_CONCAT(DISTINCT kl.name) AS `keywords`
FROM `tblCategories` AS `c`
INNER JOIN `tblCategoryLocalisedData` AS `cl` ON c.categoryID = cl.categoryID
LEFT JOIN `tblCategoryDurations` AS `cd` ON c.categoryID = cd.categoryID
LEFT JOIN `tblCategoryRules` AS `cr` ON c.categoryID = cr.categoryID
LEFT JOIN `tblCategoryVideos` AS `cv` ON c.categoryID = cv.categoryID
LEFT JOIN `tblVideos` AS `v` ON cv.videoID = v.videoID
LEFT JOIN `tblVideoTerritories` AS `vt` ON vt.videoID = v.videoID
LEFT JOIN `tblCategoryTerritories` AS `ct` ON ct.categoryID = c.categoryID
INNER JOIN `tblTerritories` AS `t_v` ON t_v.territoryID = vt.territoryID
INNER JOIN `tblTerritories` AS `t_c` ON t_c.territoryID = ct.territoryID
INNER JOIN `tblVideoLocalisedData` AS `vl` ON vl.videoID = v.videoID
LEFT JOIN `tblVideoKeywords` AS `vk` ON v.videoID = vk.videoID
LEFT JOIN `tblKeywords` AS `k` ON vk.keywordID = k.keywordID
LEFT JOIN `tblKeywordLocalisedData` AS `kl` ON kl.keywordID = k.keywordID
INNER JOIN `tblLanguages` AS `l`
WHERE (cv.disabled IS NULL)
AND (cd.start_date < NOW() OR cd.start_date IS NULL)
AND (cd.end_date > NOW() OR cd.end_date IS NULL)
AND (cr.name IS NULL)
AND (l.languageID = cl.languageID OR cl.languageID IS NULL)
AND (l.languageID = kl.languageID OR kl.languageID IS NULL)
AND (l.languageID = vl.languageID OR vl.languageID IS NULL)
AND (l.iso_639_1 = 'en')
GROUP BY `v`.`videoID`, `c`.`categoryID`
ORDER BY `c`.`categoryID` ASC
当我运行上面的查询时,需要花费整整一秒钟的时间。我尝试对它运行EXPLAIN,它给了我以下信息:
+----+-------------+-------+--------+--------------------------------------------------------------------------------------+-----------------------------------------+---------+------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------------------------------------------------------------+-----------------------------------------+---------+------------------------+------+----------------------------------------------+
| 1 | SIMPLE | cv | ALL | fk_tblCategoryVideos_tblCategories1,fk_tblCategoryVideos_tblVideos1 | NULL | NULL | NULL | 2 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | db.cv.categoryID | 1 | Using index |
| 1 | SIMPLE | cd | ref | fk_tblCategoryDurations_tblCategories | fk_tblCategoryDurations_tblCategories | 4 | db.cv.categoryID | 1 | Using where |
| 1 | SIMPLE | cr | ref | fk_tblCategoryRules_tblCategories1 | fk_tblCategoryRules_tblCategories1 | 4 | db.cv.categoryID | 1 | Using where; Not exists |
| 1 | SIMPLE | vt | ref | fk_tblVideoTerritories_tblVideos1,fk_tblVideoTerritories_tblTerritories1 | fk_tblVideoTerritories_tblVideos1 | 4 | db.cv.videoID | 1 | Using where |
| 1 | SIMPLE | t_v | eq_ref | PRIMARY | PRIMARY | 4 | db.vt.territoryID | 1 | |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 4 | db.vt.videoID | 1 | Using where |
| 1 | SIMPLE | vk | ref | fk_tblVideoKeywords_tblVideos1 | fk_tblVideoKeywords_tblVideos1 | 4 | db.cv.videoID | 6 | |
| 1 | SIMPLE | k | eq_ref | PRIMARY | PRIMARY | 4 | db.vk.keywordID | 1 | Using index |
| 1 | SIMPLE | kl | ref | fk_tblKeywordLocalisedData_tblKeywords1 | fk_tblKeywordLocalisedData_tblKeywords1 | 4 | db.k.keywordID | 1 | |
| 1 | SIMPLE | cl | ALL | fk_tblCategoryLocalisedData_tblCategories1,fk_tblCategoryLocalisedData_tblLanguages1 | NULL | NULL | NULL | 5 | Using where; Using join buffer |
| 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 4 | db.cl.languageID | 1 | Using where |
| 1 | SIMPLE | ct | ALL | fk_tblCategoryTerritories_tblCategories1,fk_tblCategoryTerritories_tblTerritories1 | NULL | NULL | NULL | 2 | Using where; Using join buffer |
| 1 | SIMPLE | vl | ALL | fk_tblVideoLocalisedData_tblLanguages1,fk_tblVideoLocalisedData_tblVideos1 | NULL | NULL | NULL | 9 | Using where; Using join buffer |
| 1 | SIMPLE | t_c | eq_ref | PRIMARY | PRIMARY | 4 | db.ct.territoryID | 1 | |
+----+-------------+-------+--------+--------------------------------------------------------------------------------------+-----------------------------------------+---------+------------------------+------+----------------------------------------------+
但是我不知道那是什么意思。我该如何解决?值得庆幸的是,我确实知道查询的哪些部分会导致大量的速度下降。如果我删除了从tblVideoTerritories(vt)到tblTerritories(t_v)或tblCategoryTerritories(ct)到tblTerritories(t_c)的联接,那么一切都会大大加快。我以为开始可能是因为GROUP_CONCAT或DISTINCT,但我尝试删除它们,但几乎没有任何改变。似乎
性能问题是由两次连接到同一表’tblTerritories’引起的 。如果我只有这些联接中的一个,则查询只需要0.1秒或0.2秒即可运行-
这仍然是很长的时间,但这是一个更好的开始!
我想知道的是如何解决此性能问题? 为什么两次连接到同一张表会使查询花费10倍的时间?!
谢谢你的帮助!
编辑: tblVideoTerritories上的SHOW CREATE TABLE给我这个:
CREATE TABLE `tblVideoTerritories` (
`videoTerritoryID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`videoID` int(10) unsigned NOT NULL,
`territoryID` int(10) unsigned NOT NULL,
PRIMARY KEY (`videoTerritoryID`),
KEY `fk_tblVideoTerritories_tblVideos1` (`videoID`),
KEY `fk_tblVideoTerritories_tblTerritories1` (`territoryID`),
CONSTRAINT `fk_tblVideoTerritories_tblTerritories1` FOREIGN KEY (`territoryID`) REFERENCES `tblTerritories` (`territoryID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_tblVideoTerritories_tblVideos1` FOREIGN KEY (`videoID`) REFERENCES `tblVideos` (`videoID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
tblCategoryTerritories上的SHOW CREATE TABLE给我这个:
CREATE TABLE `tblCategoryTerritories` (
`categoryTerritoryID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`categoryID` int(10) unsigned NOT NULL,
`territoryID` int(10) unsigned NOT NULL,
PRIMARY KEY (`categoryTerritoryID`),
KEY `fk_tblCategoryTerritories_tblCategories1` (`categoryID`),
KEY `fk_tblCategoryTerritories_tblTerritories1` (`territoryID`),
CONSTRAINT `fk_tblCategoryTerritories_tblCategories1` FOREIGN KEY (`categoryID`) REFERENCES `tblCategories` (`categoryID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_tblCategoryTerritories_tblTerritories1` FOREIGN KEY (`territoryID`) REFERENCES `tblTerritories` (`territoryID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
在tblTerritories上的SHOW CREATE TABLE给我这个:
CREATE TABLE `tblTerritories` (
`territoryID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`iso_3166_1_alpha_2` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`iso_3166_1_alpha_3` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`defaultLanguageID` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`territoryID`),
KEY `fk_tblTerritories_tblLanguages1` (`defaultLanguageID`),
KEY `iso_3166_1_alpha_2` (`iso_3166_1_alpha_2`),
CONSTRAINT `fk_tblTerritories_tblLanguages1` FOREIGN KEY (`defaultLanguageID`) REFERENCES `tblLanguages` (`languageID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
edit2: 将两次加入同一地区的原因是我需要使用查询顶部的GROUP_CONCAT生成两个单独的地区列表。我需要一部影片,一部影片所属的类别。
edit3: 有趣的是,如果我将查询缩减到最简单,那么即使两次连接到同一张表,它也非常快(0.00秒):
SELECT `c`.`categoryID`,
`v`.`videoID`,
GROUP_CONCAT(DISTINCT t_v.iso_3166_1_alpha_2) AS `video_territories`,
GROUP_CONCAT(DISTINCT t_c.iso_3166_1_alpha_2) AS `category_territories`
FROM `tblCategories` AS `c`
LEFT JOIN `tblCategoryVideos` AS `cv` ON c.categoryID = cv.categoryID
LEFT JOIN `tblVideos` AS `v` ON cv.videoID = v.videoID
LEFT JOIN `tblVideoTerritories` AS `vt` ON vt.videoID = v.videoID
LEFT JOIN `tblCategoryTerritories` AS `ct` ON ct.categoryID = c.categoryID
INNER JOIN `tblTerritories` AS `t_v` ON t_v.territoryID = vt.territoryID
INNER JOIN `tblTerritories` AS `t_c` ON t_c.territoryID = ct.territoryID
GROUP BY `v`.`videoID`, `c`.`categoryID`
edit4: 如果我从使用WHERE临时切换为开,那么我仍然有一个查询需要0.98秒:
SELECT `c`.`categoryID`,
`cl`.`name` AS `category_name`,
`v`.*,
TRUE AS `categoried`,
GROUP_CONCAT(DISTINCT t_v.iso_3166_1_alpha_2) AS `video_territories`,
GROUP_CONCAT(DISTINCT t_c.iso_3166_1_alpha_2) AS `category_territories`,
`vl`.*,
GROUP_CONCAT(DISTINCT kl.name) AS `keywords`
FROM `tblCategories` AS `c`
INNER JOIN `tblCategoryLocalisedData` AS `cl` ON c.categoryID = cl.categoryID
LEFT JOIN `tblCategoryDurations` AS `cd` ON c.categoryID = cd.categoryID
LEFT JOIN `tblCategoryRules` AS `cr` ON c.categoryID = cr.categoryID
LEFT JOIN `tblCategoryVideos` AS `cv` ON c.categoryID = cv.categoryID
LEFT JOIN `tblVideos` AS `v` ON cv.videoID = v.videoID
LEFT JOIN `tblVideoTerritories` AS `vt` ON vt.videoID = v.videoID
LEFT JOIN `tblCategoryTerritories` AS `ct` ON ct.categoryID = c.categoryID
INNER JOIN `tblTerritories` AS `t_v` ON t_v.territoryID = vt.territoryID
INNER JOIN `tblTerritories` AS `t_c` ON t_c.territoryID = ct.territoryID
INNER JOIN `tblVideoLocalisedData` AS `vl` ON vl.videoID = v.videoID
LEFT JOIN `tblVideoKeywords` AS `vk` ON v.videoID = vk.videoID
LEFT JOIN `tblKeywords` AS `k` ON vk.keywordID = k.keywordID
LEFT JOIN `tblKeywordLocalisedData` AS `kl` ON kl.keywordID = k.keywordID
INNER JOIN `tblLanguages` AS `l` ON (l.languageID = cl.languageID OR cl.languageID IS NULL) AND (l.languageID = kl.languageID OR kl.languageID IS NULL) AND (l.languageID = vl.languageID OR vl.languageID IS NULL)
WHERE (cv.disabled IS NULL)
AND (cd.start_date < NOW() OR cd.start_date IS NULL)
AND (cd.end_date > NOW() OR cd.end_date IS NULL)
AND (cr.name IS NULL) AND (l.iso_639_1 = 'en')
GROUP BY `v`.`videoID`, `c`.`categoryID`
ORDER BY `c`.`categoryID` ASC
edit5:
如果我删除与关键字相关的联接,查询将在0.09秒内发生…删除tblKeyword和tblKeywordLocalisedData,但保留tblVideoKeywords给我0.80秒。删除tblVideoKeywords给我0.09秒。
但是它似乎有索引,所以我还是不明白:
CREATE TABLE `tblVideoKeywords` (
`videoKeywordID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`videoID` int(10) unsigned NOT NULL,
`keywordID` int(10) unsigned NOT NULL,
PRIMARY KEY (`videoKeywordID`),
KEY `fk_tblVideoKeywords_tblVideos1` (`videoID`),
KEY `fk_tblVideoKeywords_tblKeywords1` (`keywordID`),
CONSTRAINT `fk_tblVideoKeywords_tblKeywords1` FOREIGN KEY (`keywordID`) REFERENCES `tblKeywords` (`keywordID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_tblVideoKeywords_tblVideos1` FOREIGN KEY (`videoID`) REFERENCES `tblVideos` (`videoID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
edit6: 使用 DRapp 提供的查询可使一切变得更快。现在,他的查询的解释为我提供了:
+----+-------------+---------+--------+--------------------------------------------------------------------------------------+-----------------------------------------+---------+------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+--------------------------------------------------------------------------------------+-----------------------------------------+---------+------------------------+------+----------------------------------------------+
| 1 | SIMPLE | c | index | PRIMARY | PRIMARY | 4 | NULL | 3 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | cl | ALL | fk_tblCategoryLocalisedData_tblCategories1,fk_tblCategoryLocalisedData_tblLanguages1 | NULL | NULL | NULL | 5 | Using where; Using join buffer |
| 1 | SIMPLE | lang_cl | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where; Using join buffer |
| 1 | SIMPLE | cd | ref | fk_tblCategoryDurations_tblCategories | fk_tblCategoryDurations_tblCategories | 4 | db.c.categoryID | 1 | |
| 1 | SIMPLE | cr | ref | fk_tblCategoryRules_tblCategories1 | fk_tblCategoryRules_tblCategories1 | 4 | db.c.categoryID | 1 | Using where; Not exists |
| 1 | SIMPLE | cv | ALL | fk_tblCategoryVideos_tblCategories1,fk_tblCategoryVideos_tblVideos1 | NULL | NULL | NULL | 2 | Using where; Using join buffer |
| 1 | SIMPLE | ct | ALL | fk_tblCategoryTerritories_tblCategories1,fk_tblCategoryTerritories_tblTerritories1 | NULL | NULL | NULL | 2 | Using where; Using join buffer |
| 1 | SIMPLE | t_c | eq_ref | PRIMARY | PRIMARY | 4 | db.ct.territoryID | 1 | |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 4 | db.cv.videoID | 1 | Using where |
| 1 | SIMPLE | vt | ref | fk_tblVideoTerritories_tblVideos1,fk_tblVideoTerritories_tblTerritories1 | fk_tblVideoTerritories_tblVideos1 | 4 | db.v.videoID | 1 | Using where |
| 1 | SIMPLE | t_v | eq_ref | PRIMARY | PRIMARY | 4 | db.vt.territoryID | 1 | |
| 1 | SIMPLE | vl | ALL | fk_tblVideoLocalisedData_tblLanguages1,fk_tblVideoLocalisedData_tblVideos1 | NULL | NULL | NULL | 9 | Using where; Using join buffer |
| 1 | SIMPLE | lang_vl | eq_ref | PRIMARY | PRIMARY | 4 | db.vl.languageID | 1 | Using where |
| 1 | SIMPLE | vk | ALL | fk_tblVideoKeywords_tblVideos1,fk_tblVideoKeywords_tblKeywords1 | NULL | NULL | NULL | 15 | Using where; Using join buffer |
| 1 | SIMPLE | k | eq_ref | PRIMARY | PRIMARY | 4 | db.vk.keywordID | 1 | Using where; Using index |
| 1 | SIMPLE | kl | ref | fk_tblKeywordLocalisedData_tblKeywords1,fk_tblKeywordLocalisedData_tblLanguages1 | fk_tblKeywordLocalisedData_tblKeywords1 | 4 | db.k.keywordID | 1 | Using where |
| 1 | SIMPLE | lang_kl | eq_ref | PRIMARY | PRIMARY | 4 | db.kl.languageID | 1 | Using where |
+----+-------------+---------+--------+--------------------------------------------------------------------------------------+-----------------------------------------+---------+------------------------+------+----------------------------------------------+
17 rows in set (0.01 sec)
对于我回答的其他几个问题,只需添加一个“
STRAIGHT_JOIN”,稍加重组便可以解决问题。查询优化器实际上将尝试为您考虑所有表,尝试查找记录较少的表,然后将记录与较大的表连接起来,从而造成混乱。发生这种情况时,是我对14个以上的百万条记录进行了政府查询,并查询了15个以上的子表。这与您在此处进行的操作非常相似。它执行了一个查询,该查询在专用的独立服务器上运行了30多个小时,并将其挂起(最多2个小时)…请尝试以下操作:
除了对联接进行视觉上的清理/排序以外,我还采用了NOW()vs
NULL并将它们移入联接中。如果查询左联接并将日期作为联接限定符的一部分,则将排除那些超出范围的记录,从而保留NULL结果集或有效条目,而无需将该限定符加倍。
SELECT STRAIGHT_JOIN
c.categoryID,
cl.name AS category_name,
v.*,
TRUE AS categoried,
GROUP_CONCAT(DISTINCT t_v.iso_3166_1_alpha_2) AS video_territories,
GROUP_CONCAT(DISTINCT t_c.iso_3166_1_alpha_2) AS category_territories,
vl.*,
GROUP_CONCAT(DISTINCT kl.name) AS keywords
FROM
tblCategories AS c
INNER JOIN tblCategoryLocalisedData AS cl
ON c.categoryID = cl.categoryID
INNER JOIN tblLanguages AS lang_cl
ON l.languageID = lang_cl.languageID
AND lang_cl.iso_639_1 = 'en'
LEFT JOIN tblCategoryDurations AS cd
ON c.categoryID = cd.categoryID
AND cd.start_date < NOW()
AND cd.end_date > NOW()
LEFT JOIN tblCategoryRules AS cr
ON c.categoryID = cr.categoryID
LEFT JOIN tblCategoryVideos AS cv
ON c.categoryID = cv.categoryID
LEFT JOIN tblCategoryTerritories AS ct
ON c.categoryID = ct.categoryID
INNER JOIN tblTerritories AS t_c
ON ct.territoryID = t_c.territoryID
LEFT JOIN tblVideos AS v
ON cv.videoID = v.videoID
LEFT JOIN tblVideoTerritories AS vt
ON v.videoID = vt.videoID
INNER JOIN tblTerritories AS t_v
ON vt.territoryID = t_v.territoryID
INNER JOIN tblVideoLocalisedData AS vl
ON v.videoID = vl.videoID
INNER JOIN tblLanguages AS lang_vl
ON vl.languageID = lang_vl.languageID
AND lang_vl.iso_639_1 = 'en'
LEFT JOIN tblVideoKeywords AS vk
ON v.videoID = vk.videoID
LEFT JOIN tblKeywords AS k
ON vk.keywordID = k.keywordID
LEFT JOIN tblKeywordLocalisedData AS kl
ON k.keywordID = kl.keywordID
INNER JOIN tblLanguages AS lang_kl
ON kl.languageID = lang_kl.languageID
AND lang_kl.iso_639_1 = 'en'
WHERE
( cv.disabled IS NULL)
AND ( cr.name IS NULL)
GROUP BY
v.videoID,
c.categoryID
ORDER BY
c.categoryID ASC
正如我上面解释的,STRAIGHT_JOIN基本上告诉优化器“不要为我考虑” ....按照我告诉您的顺序进行查询。在这种情况下,请使用“
tblCategories”作为主表并将其他所有链接链接起来。即使有说明,优化器也可能会变慢,并在下次运行查询时尝试另一种方法。因此,它可以尝试首先使用“语言”表,并在其他表中进行后向填充并阻塞。另外,通过将“
AND”部分(例如日期)直接指向那些左联接,这些联接就简化了WHERE,如您所见……就像您在NULL或存在NULL的位置中一样,仅将其应用于该特定联接。
。保持地方清洁。
另外,通过保持关系的直接性和缩进性,使其与所加入的对象相对应,从而更容易理解与何处链接的内容…
我还希望看到最终的“ EXPLAIN”,并查看它的含义。
我有桌子: 行程:Trip_Num、Trip_Type等 用户的出差历史记录:Username、Trip_Num等 我正在用php做一个项目,需要为一个特定的用户(通过他的用户名)做一个SQL查询--需要知道用户的Trips历史表中Trip_Num的Trip_Type。 附上我的表格: 更新:我需要计算trip_type中的每个值。我的意思是我想看到“步行”-4次,“自行车”-3次,这很好?...
问题内容: 我有两张表。我试图将下面的两个示例表与表1结合起来,两次引用表2。例如,如果我查看表1:第2组和成员7,则应在表2中查找ID,并提供以下输出: 表1 表2 有什么建议吗?谢谢 问题答案: SELECT Table_1.*, g.Name, m.Name FROM Table_1 INNER JOIN Table_2 AS g ON Table_1.Group=g.ID INNER JO
问题内容: 在数据库表上运行sp_executesql时遇到一些问题。我使用的是ORM(NHibernate),在这种情况下,该ORM(NHibernate)生成查询一个表的SQL查询。该表中大约有700万条记录,并且已被高度索引。 当我运行没有sp_executesql的ORM吐出的查询时,它运行非常快,并且探查器显示它具有85次读取。当我使用sp_executesql运行相同的查询时,它的读取
从select语句更新表时遇到问题...下面是命令: 我要传递给此查询的参数将是sub sub查询中的report_id...select单独执行需要0.113秒,而update查询总共需要4.868秒。是因为update查询将对表的每一行执行select语句吗?我怎么能让这更快? 谢谢你
问题内容: 我有以下只需1秒即可执行的sql查询: 但是我需要一个结果集来获取比率大于0的结果。因此,当我将查询更改为此时,需要7分钟的时间来执行: 为什么这会使查询时间从1秒增加到7分钟?由于b表很大,因此我什至尝试使用CTE,但这也没有提高性能。我认为使用CTE可以从中筛选出较小的一组值,因此应该更快一些,但这无济于事: 我不能包括执行计划,因为除了查询之外,我没有对数据库的权限。 问题答案:
问题内容: 在查询中引入ORDER BY子句会增加总时间,这是因为数据库需要对排序结果集进行额外的工作: 将结果元组复制到一些临时内存中 对它们进行排序(希望在内存中,否则使用磁盘) 将结果流式传输到客户端 我想念的是为什么仅从联接表中添加列会产生如此不同的性能。 查询1 查询计划 查询2 与上述相同,但不按 查询计划 编辑:添加了更多详细信息 Postgres版本是 8.4 问题答案: 在排序操