mysql - 有什么办法可以优化这个mysql查询吗？

Question

我有两个表 hg_questions 和 hg_tags 与 hg_question_tag 相关，其结构如下:

+---------+---------------------+------+-----+---------+-------+
| Field   | Type                | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+-------+
| qid     | bigint(20) unsigned | YES  |     | NULL    |       |
| tagid   | bigint(20) unsigned | YES  | MUL | NULL    |       |
| tagname | varchar(50)         | YES  |     | NULL    |       |
+---------+---------------------+------+-----+---------+-------+

我在这张表上只有一个索引用于 tagid 列，下面的查询运行得非常慢，因为我正好有 59440 行用于标记号 464(这个标签里有这么多问题)

SELECT hg_questions.qid,
hg_questions.question,
hg_questions.points,
hg_questions.reward,
hg_questions.answerscount,
hg_questions.created_at,
hg_questions.sections,
hg_questions.answered,
hg_questions.user_id
FROM hg_questions
INNER JOIN hg_question_tag ON hg_question_tag.qid = hg_questions.qid
WHERE hg_question_tag.tagid = 464
ORDER BY points DESC LIMIT 15
OFFSET 0;

当对此查询运行解释时，我得到:

| id | select_type | table           | type   | possible_keys | key     | key_len | ref                              | rows  | Extra                                        |
+----+-------------+-----------------+--------+---------------+---------+---------+----------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | hg_question_tag | ref    | tagid         | tagid   | 9       | const                            | 59440 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | hg_questions    | eq_ref | PRIMARY       | PRIMARY | 8       | ejaaba_bilal.hg_question_tag.qid |     1 |                                              |
+----+-------------+-----------------+--------+---------------+---------+---------+----------------------------------+-------+----------------------------------------------+

我有什么想法可以优化这个查询吗？或者有一种方法可以让它更快地工作。

---

1. hg_questions 在 points 列上有一个索引
2. 删除了按点排序，使其运行速度提高了 80%

Answer 1

这是您的查询，在某种程度上进行了简化:

SELECT q.*
FROM hg_questions q INNER JOIN
     hg_question_tag qt
     ON qt.qid = q.qid
WHERE qt.tagid = 464
ORDER BY points DESC
LIMIT 15 OFFSET 0;

您正在执行 join 和 order by。要优化此查询，请尝试在 hg_question_tag(tagid, qid) 上放置一个索引。如果有标签的问题不多，这将起作用。

如果很多问题都有标签，最好浏览一下问题并选择合适的问题。尝试将查询重写为:

select q.*
from hg_questions q
where exists (select 1 from hg_question_tag qt where qt.qid = q.qid and qt.tagid = 464)
order by points desc
limit 15 offset 0;

保留上面的索引并将另一个索引放在hg_questions(points, qid)上。