我有一个 500 000 行表的查询。
基本上
WHERE s3_.id = 287
ORDER BY m0_.id DESC
LIMIT 25
=> 查询运行时间 = 20ms
WHERE s3_.id = 287
ORDER BY m0_.created_at DESC
LIMIT 25
=> 查询运行时间 = 15000 毫秒或更多
created_at 上有一个索引。
查询计划完全不同。
不幸的是，我不是查询计划专家。我想在通过 created_at 订购时重现快速查询计划。
这可能吗，我该怎么做？
查询计划 - 慢查询 (按 m0_.created_at 排序):http://explain.depesz.com/s/KBl
查询计划 - 快速查询 (按 m0_.id 排序):http://explain.depesz.com/s/2pYZ
完整查询
SELECT m0_.id AS id0, m0_.content AS content1, m0_.created_at AS created_at2,
c1_.id AS id3, l2_.id AS id4, l2_.reference AS reference5,
s3_.id AS id6, s3_.name AS name7, s3_.code AS code8,
u4_.email AS email9, u4_.id AS id10, u4_.firstname AS firstname11, u4_.lastname AS lastname12,
u5_.email AS email13, u5_.id AS id14, u5_.firstname AS firstname15, u5_.lastname AS lastname16,
g6_.id AS id17, g6_.firstname AS firstname18, g6_.lastname AS lastname19, g6_.email AS email20,
m0_.conversation_id AS conversation_id21, m0_.author_user_id AS author_user_id22, m0_.author_guest_id AS author_guest_id23,
c1_.author_user_id AS author_user_id24, c1_.author_guest_id AS author_guest_id25, c1_.listing_id AS listing_id26,
l2_.poster_id AS poster_id27, l2_.site_id AS site_id28, l2_.building_id AS building_id29, l2_.type_id AS type_id30, l2_.neighborhood_id AS neighborhood_id31, l2_.facility_bathroom_id AS facility_bathroom_id32, l2_.facility_kitchen_id AS facility_kitchen_id33, l2_.facility_heating_id AS facility_heating_id34, l2_.facility_internet_id AS facility_internet_id35, l2_.facility_condition_id AS facility_condition_id36, l2_.original_translation_id AS original_translation_id37,
u4_.site_id AS site_id38, u4_.address_id AS address_id39, u4_.billing_address_id AS billing_address_id40,
u5_.site_id AS site_id41, u5_.address_id AS address_id42, u5_.billing_address_id AS billing_address_id43,
g6_.site_id AS site_id44
FROM message m0_
INNER JOIN conversation c1_ ON m0_.conversation_id = c1_.id
INNER JOIN listing l2_ ON c1_.listing_id = l2_.id
INNER JOIN Site s3_ ON l2_.site_id = s3_.id
INNER JOIN user_ u4_ ON l2_.poster_id = u4_.id
LEFT JOIN user_ u5_ ON m0_.author_user_id = u5_.id
LEFT JOIN guest_data g6_ ON m0_.author_guest_id = g6_.id
WHERE s3_.id = 287
ORDER BY m0_.created_at DESC
LIMIT 25 OFFSET 0
最佳答案
结果证明是索引问题。查询的 NULLS 行为与索引不一致。
CREATE INDEX message_created_at_idx on message (created_at DESC NULLS LAST);
... ORDER BY message.created_at DESC; -- defaults to NULLS FIRST when DESC
解决方案
如果您在索引或查询中指定 NULLS，请确保它们彼此一致。
即:
ASC NULLS LAST 与 ASC NULLS LAST 或 DESC NULLS FIRST 一致。最后一个空
CREATE INDEX message_created_at_idx on message (created_at DESC NULLS LAST);
... ORDER BY messsage.created_at DESC NULLS LAST;
NULL 优先
CREATE INDEX message_created_at_idx on message (created_at DESC); -- defaults to NULLS FIRST when DESC
... ORDER BY messsage.created_at DESC -- defaults to NULLS FIRST when DESC;
非空
如果您的列不是 NULL，请不要理会 NULLS。
CREATE INDEX message_created_at_idx on message (created_at DESC);
... ORDER BY messsage.created_at DESC;
关于sql - 将 ORDER BY 从 id 更改为另一个索引列(具有低 LIMIT)具有巨大的成本，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24872582/