mysql - 慢速查询来计算标签数

Question

此查询对与标签相关的视频进行计数(前 50 个)。它运行速度非常慢(视频表大约有 800k 条记录)。我已经设置了所有适当的索引/键。

SELECT `tags`.`id_tag`, `tags`.`tag_text`, COUNT(video_tags`.`id_video`) AS `total_video_count`
FROM `tags`
  INNER JOIN `video_tags` ON ( `tags`.`id_tag` = `video_tags`.`id_tag` )
  INNER JOIN `videos` ON ( `video_tags`.`id_video` = `videos`.`id_video` )
GROUP BY `tags`.`id_tag`
ORDER BY  `total_video_count` DESC
LIMIT 50;

有什么想法可能会导致性能不佳，或者有任何其他结构查询的方法吗？

----更新----

+--------+------------+------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                                 | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| videos |          0 | PRIMARY                                  |            1 | id_video        | A         |      812967 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+------------+------------+---------------------------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name                                                | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+---------------------------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| video_tags |          0 | PRIMARY                                                 |            1 | id_video_tag | A         |     4113266 |     NULL | NULL   |      | BTREE      |         |               |
| video_tags |          1 | video_tags_id_tag_7e0eba6ebf2ab1be_fk_tags_id_tag       |            1 | id_tag       | A         |       10852 |     NULL | NULL   |      | BTREE      |         |               |
| video_tags |          1 | video_tags_id_video_6fa83a06b3a6ec45_fk_videos_id_video |            1 | id_video     | A         |     1371088 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+---------------------------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tags  |          0 | PRIMARY  |            1 | id_tag      | A         |       35186 |     NULL | NULL   |      | BTREE      |         |               |
| tags  |          0 | tag_text |            1 | tag_text    | A         |       35186 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Answer 1

以上最后评论是正确的..

在 video_tags(id_tag、id_video)上添加索引。应该发生的是第一个左连接应该将新索引启动。它将把 id_video 拉入内存并在内存中获取第二个左连接谓词..

我相信正在发生的事情是，您正在对 video_tags 表进行另一次读取以拉入 id_video 列..因此这对表来说是非内存命中..那么如果 video_tags 中行的物理布局不匹配主键的序列(它可能使用什么)..你最终会破坏 IO

我会尝试在 video_tags(id_tag、id_video)上添加复合键复合唯一键，然后再试一次。