MariaDB一对多关联带ORDER BY和LIMIT的慢SQL查询优化求助

我正在使用MariaDB 10.11，无法优化一个带排序和限制的一对多关联查询。articles表约40万行，article_tag（标签与文章关联表）约50万行，两张表会持续增长，每篇文章最多5个标签。

查询语句 #

SELECT a.article_id, 
       a.title,  
       m.username, 
       a.article, 
       vc.view_count,
       a.inactive_article_id
FROM articles a  
JOIN article_tag t
    ON a.article_id = t.article_id 
JOIN article_view_count vc
    ON vc.article_id = a.article_id
JOIN members m
    ON m.member_id = a.author_id 
WHERE m.inactive_account_id = 0  
    AND a.inactive_article_id = 0
    AND t.tag_id = 5
ORDER BY a.date_modified DESC 
LIMIT 0, 12

当前查询执行时间超过1秒，尝试过各种索引、STRAIGHT_JOIN和子查询都没提升。执行计划显示article_tag表存在Using index; Using temporary; Using filesort操作。

完整EXPLAIN结果 #

| id   | select_type | table | type   | possible_keys                                       | key     | key_len | ref                         | rows  | Extra                                        |
+------+-------------+-------+--------+-----------------------------------------------------+---------+---------+-----------------------------+-------+----------------------------------------------+
|    1 | SIMPLE      | t     | ref    | PRIMARY,idx_article_id                              | PRIMARY | 4       | const                       | 50178 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | a     | eq_ref | PRIMARY,aauthorid_feature_idx,inactive_modified_idx | PRIMARY | 4       | t.article_id                | 1     | Using where                                  |
|    1 | SIMPLE      | m     | eq_ref | PRIMARY                                             | PRIMARY | 4       | a.authorid                  | 1     | Using where                                  |
|    1 | SIMPLE      | vc    | eq_ref | PRIMARY                                             | PRIMARY | 4       | t.article_id                | 1     |                                              |
+------+-------------+-------+--------+-----------------------------------------------------+---------+---------+-----------------------------+-------+----------------------------------------------+

表索引信息 #

articles表索引 #

show index in articles;
+----------+------------+-----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table    | Non_unique | Key_name                    | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+----------+------------+-----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| articles |          0 | PRIMARY                     |            1 | article_id          | A         |      373095 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| articles |          1 | aauthorid_feature_idx       |            1 | authorid            | A         |       24873 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| articles |          1 | aauthorid_feature_idx       |            2 | can_feature         | A         |       37309 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | inactive_modified_idx       |            1 | inactive_article_id | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | inactive_modified_idx       |            2 | date_modified       | A         |      373095 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | catid_modified_idx          |            1 | catid               | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | catid_modified_idx          |            2 | date_modified       | A         |      373095 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | art_inactive_catid_idx      |            1 | catid               | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | art_inactive_catid_idx      |            2 | inactive_article_id | A         |          60 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | articles_idx_date_modified  |            1 | date_modified       | A         |      373095 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | articles_idx_date_published |            1 | date_published      | A         |      373095 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+----------+------------+-----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

article_tag表索引 #

show index in article_tag;
+---------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table         | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| article_theme |          0 | PRIMARY        |            1 | tag_id      | A         |         830 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| article_theme |          0 | PRIMARY        |            2 | article_id  | A         |      401991 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| article_theme |          1 | idx_article_id |            1 | article_id  | A         |      401991 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+---------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

我的需求是先在article_tag表按tag_id筛选，再用a.date_modified的索引处理排序和限制，但查询无法使用该索引——除非用STRAIGHT_JOIN，但这样会先对整个articles表排序再筛选标签。我哪里错了？必须把date_modified冗余到article_tag表吗？

核心问题分析 #

当前执行计划是先从article_tag取出5万多条符合tag_id=5的记录，再关联articles表过滤无效文章，最后对结果排序取前12条。这里的Using temporary; Using filesort是因为要对5万多条结果排序，开销极大。

MySQL/MariaDB优化器无法直接将article_tag的筛选和articles的排序索引结合，因为关联后的结果集无法直接利用单表的排序索引。

优化方案1：子查询缩小排序范围 #

不需要冗余字段，换个思路：先找到带tag_id=5且未被标记为无效的最新12篇文章ID，再关联其他表取详情。这样能把排序和筛选的范围缩小到最小。

修改后的查询语句：

SELECT a.article_id, 
       a.title,  
       m.username, 
       a.article, 
       vc.view_count,
       a.inactive_article_id
FROM (
    SELECT a.article_id
    FROM articles a
    JOIN article_tag t ON a.article_id = t.article_id
    WHERE a.inactive_article_id = 0
      AND t.tag_id = 5
    ORDER BY a.date_modified DESC
    LIMIT 0, 12
) AS top_articles
JOIN articles a ON top_articles.article_id = a.article_id
JOIN members m ON m.member_id = a.author_id AND m.inactive_account_id = 0
JOIN article_view_count vc ON vc.article_id = a.article_id
ORDER BY a.date_modified DESC;

同时给articles表添加复合索引，让子查询快速筛选+排序：

CREATE INDEX idx_inactive_tag_modified ON articles (inactive_article_id, date_modified DESC, article_id);

这个索引能让子查询直接过滤inactive_article_id=0的记录，同时按date_modified倒序排列，直接取出前12个article_id，避免全量排序。article_tag表的主键(tag_id, article_id)已经能快速找到对应tag的所有文章ID，无需额外调整。

优化方案2：冗余字段（备选） #

如果方案1效果不够理想，可以考虑冗余date_modified到article_tag表，同时创建复合索引(tag_id, date_modified DESC, article_id)。但需要注意维护成本：更新文章修改时间时要同步更新article_tag的对应记录，因此优先使用方案1。

验证优化效果 #

执行修改后的查询，查看EXPLAIN结果：子查询部分应使用新创建的索引，Extra列不会出现Using temporary; Using filesort，而是Using index; Using where，关联其他表时均为eq_ref，执行时间会降到毫秒级。

内容的提问来源于stack exchange，提问作者LuckyTurnips