如何优化该SQL查询？附查询语句、执行计划及表结构

我有如下SQL查询：

select
  `stories`.*
from
  `stories`
  inner join `communities` on `stories`.`community_id` = `communities`.`id`
  inner join `communities_followers` on `communities`.`id` = `communities_followers`.`community_id`
where
  `is_published` = 1
  and `communities_followers`.`user_id` = 1
  and `communities_followers`.`status` = 1
order by
  `stories`.`created_at` desc
limit
  20 offset 0

目前已为communities_followers.user_id创建单列索引，以及['user_id', 'status']联合索引。

执行EXPLAIN后的结果如下：

| id | select_type | table                 | partitions | type   | possible_keys                                                                                                                        | key                                 | key_len | ref                                         | rows | filtered | Extra                                        |
+----+-------------+-----------------------+------------+--------+--------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+---------------------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | communities_followers | NULL       | ref    | communities_followers_user_id_index,communities_followers_community_id_index,communities_followers_community_id_user_id_status_index | communities_followers_user_id_index | 4       | const                                       |   77 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | communities           | NULL       | eq_ref | PRIMARY,communities_id_default_community_index                                                                                       | PRIMARY                             | 4       | grepless.communities_followers.community_id |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | stories               | NULL       | ref    | stories_community_id_index                                                                                                           | stories_community_id_index          | 4       | grepless.communities_followers.community_id | 3968 |   100.00 | NULL                                         |
+----+-------------+-----------------------+------------+--------+--------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+---------------------------------------------+------+----------+----------------------------------------------+

因communities_followers表数据量极大，请问还可采取哪些措施优化该查询？

以下为各表结构：

stories表 #

CREATE TABLE `stories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `publisher_id` int unsigned DEFAULT NULL,
  `community_id` int unsigned NOT NULL,
  `content_type_id` int unsigned NOT NULL DEFAULT '10',
  `title` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `meta_description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `score` int NOT NULL DEFAULT '0',
  `score_alternate` int NOT NULL DEFAULT '0',
  `views` int NOT NULL DEFAULT '0',
  `url` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `source_url` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `picture` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `embed` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `picture_original` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `picture_huge` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `picture_big` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `picture_small` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `picture_extra` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `slug` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_published` tinyint(1) NOT NULL DEFAULT '1',
  `is_summarized` tinyint(1) NOT NULL DEFAULT '0',
  `language` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `show_in_feed` tinyint(1) NOT NULL DEFAULT '1',
  `is_pinned` tinyint(1) NOT NULL DEFAULT '0',
  `has_pictures_localized` tinyint(1) NOT NULL DEFAULT '0',
  `has_pictures_optimized` tinyint(1) NOT NULL DEFAULT '0',
  `has_audio` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `author` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `comments_count` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `stories_created_at_index` (`created_at`),
  KEY `stories_user_id_index` (`user_id`),
  KEY `stories_community_id_index` (`community_id`),
  KEY `stories_content_type_id_index` (`content_type_id`),
  KEY `stories_score_index` (`score`),
  KEY `stories_slug_index` (`slug`),
  KEY `stories_show_in_feed_index` (`show_in_feed`),
  KEY `stories_publisher_id_index` (`id`),
  KEY `stories_deleted_at_is_published_content_type_id_index` (`deleted_at`,`is_published`,`content_type_id`),
  KEY `stories_publisher_id_deleted_at_index` (`publisher_id`,`deleted_at`),
  KEY `stories_is_published_deleted_at_index` (`is_published`,`deleted_at`,`community_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=952978 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

communities表 #

CREATE TABLE `communities` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned DEFAULT NULL,
  `name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `slug` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `header` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `color` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `background` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `background_cover` tinyint(1) NOT NULL DEFAULT '1',
  `picture_big` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `picture_small` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `has_pictures_optimized` tinyint(1) NOT NULL DEFAULT '0',
  `default_community` tinyint(1) NOT NULL DEFAULT '0',
  `is_popular` tinyint(1) NOT NULL DEFAULT '0',
  `status` smallint NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `stories_count` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `communities_user_id_index` (`user_id`),
  KEY `communities_name_index` (`name`),
  KEY `communities_slug_index` (`slug`),
  KEY `communities_status_index` (`status`),
  KEY `communities_default_community_index` (`default_community`),
  KEY `communities_id_default_community_index` (`id`,`default_community`)
) ENGINE=MyISAM AUTO_INCREMENT=183 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

communities_followers表 #

CREATE TABLE `communities_followers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `community_id` int unsigned NOT NULL,
  `status` smallint NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `communities_followers_user_id_index` (`user_id`),
  KEY `communities_followers_community_id_index` (`community_id`),
  KEY `communities_followers_community_id_user_id_status_index` (`community_id`,`user_id`,`status`)
) ENGINE=MyISAM AUTO_INCREMENT=326484 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

1. 调整communities_followers的索引顺序 #

当前查询先过滤user_id=1和status=1，现有联合索引顺序不符合查询逻辑，应创建**(user_id, status, community_id)**的联合索引：

CREATE INDEX idx_communities_followers_user_status_community ON communities_followers(user_id, status, community_id);

该索引可直接覆盖过滤条件，同时拿到关联所需的community_id，避免回表查询，还能消除EXPLAIN结果中的Using temporary; Using filesort。

2. 优化stories表的索引，覆盖过滤与排序 #

针对stories表的is_published=1过滤条件、community_id关联条件和created_at desc排序需求，创建**(is_published, community_id, created_at)**的联合索引：

CREATE INDEX idx_stories_published_community_created ON stories(is_published, community_id, created_at);

此索引可让数据库直接通过索引定位符合条件的记录并完成排序，避免额外的排序操作。若业务允许，尽量避免查询stories.*，仅选择所需字段并将其加入索引做成覆盖索引，彻底消除回表开销。

3. 简化关联逻辑，移除不必要的表关联 #

当前查询中communities表仅作为中间关联桥梁，stories表已直接存储community_id，可直接关联communities_followers表，减少一次表关联：

select
  `stories`.*
from
  `stories`
  inner join `communities_followers` on `stories`.`community_id` = `communities_followers`.`community_id`
where
  `stories`.`is_published` = 1
  and `communities_followers`.`user_id` = 1
  and `communities_followers`.`status` = 1
order by
  `stories`.`created_at` desc
limit
  20 offset 0

4. 替换存储引擎为InnoDB #

当前所有表使用MyISAM引擎，其不支持事务、锁粒度为表级，并发性能差且无聚簇索引。换成InnoDB后，主键作为聚簇索引可提升关联查询效率，同时支持行锁，大幅优化并发场景下的性能。

5. 分页优化（针对大offset场景） #

若后续分页需使用大offset（如offset 10000），改用基于游标分页的方式，避免大offset导致的全表扫描：

select
  `stories`.*
from
  `stories`
  inner join `communities_followers` on `stories`.`community_id` = `communities_followers`.`community_id`
where
  `stories`.`is_published` = 1
  and `communities_followers`.`user_id` = 1
  and `communities_followers`.`status` = 1
  and (`stories`.`created_at` < '上次最后一条记录的created_at' 
       or (`stories`.`created_at` = '上次最后一条记录的created_at' and `stories`.`id` < '上次最后一条记录的id'))
order by
  `stories`.`created_at` desc, `stories`.`id` desc
limit
  20;

每次查询基于索引定位，不会因offset过大导致性能下降。

6. 数据预处理与缓存 #

若用户关注的社区更新频率不高，可将用户的关注社区故事列表缓存至Redis等缓存系统，定时刷新或在故事发布时主动更新缓存，减少数据库查询次数。例如用Redis有序集合，以created_at作为score存储故事ID，查询时直接从缓存取ID再查详情，或直接缓存完整内容。

内容的提问来源于stack exchange，提问作者Knights Of Ni