我有以下疑问:
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
社区_追随者. user_id上有一个单一索引,['user_id','status']上有一个复合索引
对查询执行解释时,结果如下:
| 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 |
+----+-------------+-----------------------+------------+--------+--------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+---------------------------------------------+------+----------+----------------------------------------------+
Explain
我还能做些什么来改善这一点?communities_followers确实包含了大量的记录。
故事表:
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条答案
按热度按时间9q78igpj1#
为了加速初始过滤索引...
为了加速连接和排序以及限制...
stories(community_id, is_published, created_at)
或
stories(is_published, created_at, community_id)
但是我会使用EXISTS,因为如果您查询多个用户,它仍然可以工作(不会导致结果重复)...