多个where和order by子句导致查询缓慢

Question

我正在寻找一种加速慢（filesort）MySQL查询的方法。

表：

categories (id, lft, rgt)
questions (id, category_id, created_at, votes_up, votes_down)

示例查询：

SELECT * FROM questions q 
INNER JOIN categories c ON (c.id = q.category_id)
WHERE c.lft > 1 AND c.rgt < 100
ORDER BY q.created_at DESC, q.votes_up DESC, q.votes_down ASC
LIMIT 4000, 20

如果我移除 ORDER BY 子句，它会很快。我知道 MySQL 不喜欢在同一子句中同时使用 DESC 和 ASC 排序，因此我尝试为 questions 表添加组合索引 (created_at, votes_up) 并从 ORDER BY 子句中删除 q.votes_down ASC。这并没有起到帮助作用，因为 WHERE 子句会阻碍，它通过来自另一个 (categories) 表的列进行筛选。即使它可以工作，也不完全正确，因为我确实需要 q.votes_down ASC 条件。

在这种情况下，如何提高性能的好策略是什么？如果可能的话，我想避免重构表结构。

编辑：

CREATE TABLE `categories` (
  `id` int(11) NOT NULL auto_increment,
  `lft` int(11) NOT NULL,
  `rgt` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `lft_idx` (`lft`),
  KEY `rgt_idx` (`rgt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `questions` (
  `id` int(11) NOT NULL auto_increment,
  `category_id` int(11) NOT NULL,
  `votes_up` int(11) NOT NULL default '0',
  `votes_down` int(11) NOT NULL default '0',
  `created_at` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `questions_FI_1` (`category_id`),
  KEY `votes_up_idx` (`votes_up`),
  KEY `votes_down_idx` (`votes_down`),
  KEY `created_at_idx` (`created_at`),
  CONSTRAINT `questions_FK_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

id select_type table type   possible_keys           key     key_len ref                 rows  Extra
1  SIMPLE      q     ALL    questions_FI_1          NULL    NULL    NULL                31774 Using filesort
1  SIMPLE      c     eq_ref PRIMARY,lft_idx,rgt_idx PRIMARY 4       ttt.q.category_id 1       Using where

- Ree

---

你是否对这些列进行了索引？ - BenOfTheNorth

дҪ жІЎжңүеҸ‘еёғдёӨдёӘиЎЁзҡ„SHOW CREATE TABLEиҫ“еҮәжҲ–EXPLAINзҡ„иҫ“еҮәгҖӮдҪ иҝҳжңүLIMIT 4000, 20гҖӮеҰӮжһңдҪ йңҖиҰҒеё®еҠ©еҲҶжһҗжҹҘиҜўпјҢиҜ·еҸ‘еёғжүҖжҸҗеҲ°зҡ„жҹҘиҜўзҡ„иҫ“еҮәгҖӮ - N.B.

@Ben Griffiths：所有列都有索引。 - Ree

@N.B.: 在这种情况下，我认为这并不重要，但我已经编辑了我的问题。 - Ree

@Slava 我知道这是什么，也知道MySQL如何处理它。请谷歌一下LIMIT OFFSET子句背后的情况。 - N.B.

Answer 1

尝试使用子查询来获取所需的类别：

SELECT * FROM questions 
WHERE category_id IN ( SELECT id FROM categories WHERE lft > 1 AND rgt < 100 )
ORDER BY created_at DESC, votes_up DESC, votes_down ASC
LIMIT 4000, 20

- dgw

Answer 2

尝试将关于加入表的条件放入ON子句中：

SELECT * FROM questions q 
INNER JOIN categories c ON (c.id = q.category_id AND c.lft > 1 AND c.rgt < 100)
ORDER BY q.created_at DESC, q.votes_up DESC, q.votes_down ASC
LIMIT 4000, 20

- Slava

---

我真的不知道为什么这会有帮助，但可能与WHERE是放置所有表条件的地方有关。也许它无法在该模式下使用连接表的索引。或者也可能不是。我只能在这里想象出一些原因。:) 如果有人能分享那个知识，我会很高兴的。 - Slava

Ree，你可能也想看看这个链接，了解另一种分页结果的方法：https://dev59.com/93M_5IYBdhLWcg3ww18Q。然而，在任何列上使用用户定义的排序似乎都很棘手。在实施之前，你应该仔细考虑是否值得花费额外的精力来维护附加层。 :) - Slava

Answer 3

在查询中尽量只选择所需内容，而不是使用SELECT *。

为什么不要在MySQL中使用SELECT * (ALL)

- conrad10781

---

这与我的问题无关。SELECT * 只是为了让示例更短（实际上我并不使用它）。 - Ree

我不确定为什么不是这种情况。能够使用索引可以加快速度。在语句中使用SELECT *会使这变得不可能。你的EXPLAIN就显示了这一点。 - conrad10781

所选列对索引没有任何影响。 - Ree

选择的列确实会影响索引，或者说它们会影响从索引获得的性能；因为如果你只选择索引列，那么它实际上可以完全在索引上操作，而不必实际检索表格行。 - Seph