mysql 如何改进此SQL查询？

axr492tv 于 2022-12-28 发布在 Mysql

关注(0)|答案(1)|浏览(142)

我有以下疑问：

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 |

mysql

来源：https://stackoverflow.com/questions/74932650/how-do-i-improve-this-sql-query

1条答案

按热度按时间

9q78igpj1#

SELECT
  `stories`.*
FROM
  `communities_followers`
INNER JOIN
  `stories`
    ON `stories`. `community_id` = `communities_followers`.`community_id`
WHERE
      `communities_followers`.`user_id` = 1
  AND `communities_followers`.`status`  = 1
  AND `stories`.`is_published` = 1
ORDER BY
  `stories`.`created_at` DESC
LIMIT
  20 OFFSET 0

为了加速初始过滤索引...

第一个月

为了加速连接和排序以及限制...

stories(community_id, is_published, created_at)

或

stories(is_published, created_at, community_id)

但是我会使用EXISTS，因为如果您查询多个用户，它仍然可以工作（不会导致结果重复）...

SELECT
  `stories`.*
FROM
  `stories`
WHERE
     `stories`.`is_published` = 1 
  AND EXISTS (
    SELECT * 
      FROM `communities_followers`
     WHERE `communities_followers`.`user_id` = 1
       AND `communities_followers`.`status`  = 1
       AND `communities_followers`.`community_id` = `stories`.`community_id`
  )
ORDER BY
  `stories`.`created_at` DESC
LIMIT
  20 OFFSET 0

赞(0）回复(0）举报 2022-12-28

我来回答

mysql 如何改进此SQL查询？

1条答案

相关问题

热门标签

最新问答