如何优化MariaDB查询

flmtquvp  于 2023-11-19  发布在  其他
关注(0)|答案(2)|浏览(123)

尝试优化一个查询，它最终使用“Using where; Using temporary; Using filesort”，并且需要将近2秒的时间。查询中的所有匹配项都是索引或主键。
它只是抓取网站文章的最后几条评论，并列出它们，但它需要抓取文章的标题和用户的用户名等内容。
查询：

SELECT
    c.`comment_id`,
    c.`article_id`,
    c.`time_posted`,
    a.`title`,
    a.`slug`,
    u.username,
    a.`date`
FROM
    `articles_comments` c
INNER JOIN `articles` a ON
    c.`article_id` = a.`article_id`
INNER JOIN `users` u ON
    u.user_id = c.author_id
WHERE
    a.`active` = 1 AND c.`approved` = 1
ORDER BY
    c.`comment_id`
DESC
LIMIT 5;

字符串
表格：

CREATE TABLE `articles_comments` (
  `comment_id` int(11) UNSIGNED NOT NULL,
  `article_id` int(11) NOT NULL,
  `author_id` int(11) NOT NULL,
  `guest_username` varchar(255) DEFAULT NULL,
  `time_posted` int(11) NOT NULL,
  `comment_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `spam` tinyint(1) NOT NULL DEFAULT 0,
  `spam_report_by` int(11) DEFAULT NULL,
  `last_edited` int(11) NOT NULL DEFAULT 0,
  `last_edited_time` int(11) DEFAULT NULL,
  `edit_counter` int(11) NOT NULL DEFAULT 0,
  `approved` tinyint(1) NOT NULL DEFAULT 1,
  `total_likes` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `lock_timer` datetime DEFAULT NULL,
  `locked_by_id` int(10) UNSIGNED DEFAULT NULL,
  `promoted` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;

ALTER TABLE `articles_comments`
  ADD PRIMARY KEY (`comment_id`),
  ADD KEY `author_id` (`author_id`),
  ADD KEY `article_id` (`article_id`),
  ADD KEY `approved` (`approved`),
  ADD KEY `last_edited` (`last_edited`),
  ADD KEY `time_posted` (`time_posted`);

ALTER TABLE `articles_comments`
  MODIFY `comment_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

CREATE TABLE `articles` (
  `article_id` int(11) UNSIGNED NOT NULL,
  `author_id` int(11) UNSIGNED NOT NULL,
  `guest_username` varchar(255) DEFAULT NULL,
  `guest_email` varchar(255) DEFAULT NULL,
  `guest_ip` varchar(100) DEFAULT NULL,
  `date` int(11) NOT NULL,
  `edit_date` datetime DEFAULT NULL,
  `date_submitted` int(11) DEFAULT NULL,
  `title` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `slug` varchar(120) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
  `tagline` text DEFAULT NULL,
  `text` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `comment_count` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `active` int(1) NOT NULL DEFAULT 1,
  `show_in_menu` tinyint(1) NOT NULL DEFAULT 0,
  `views` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `submitted_article` tinyint(1) NOT NULL DEFAULT 0,
  `admin_review` tinyint(1) NOT NULL DEFAULT 0,
  `reviewed_by_id` int(11) UNSIGNED DEFAULT NULL,
  `submitted_unapproved` tinyint(1) NOT NULL DEFAULT 0,
  `comments_open` tinyint(1) NOT NULL DEFAULT 1,
  `draft` tinyint(1) NOT NULL DEFAULT 0,
  `tagline_image` text DEFAULT NULL,
  `thumbnail_alt_text` varchar(100) DEFAULT NULL,
  `gallery_tagline` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `locked` tinyint(1) NOT NULL DEFAULT 0,
  `locked_by` int(11) UNSIGNED DEFAULT NULL,
  `locked_date` int(11) DEFAULT NULL,
  `preview_code` varchar(10) DEFAULT NULL,
  `total_likes` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `moderated_comments` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

ALTER TABLE `articles`
  ADD PRIMARY KEY (`article_id`),
  ADD KEY `date` (`date`),
  ADD KEY `author_id` (`author_id`),
  ADD KEY `slug` (`slug`);
ALTER TABLE `articles` ADD FULLTEXT KEY `title` (`title`,`text`);
ALTER TABLE `articles` ADD FULLTEXT KEY `title_2` (`title`);
ALTER TABLE `articles` ADD FULLTEXT KEY `text` (`text`);

ALTER TABLE `articles`
  MODIFY `article_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;


CREATE TABLE `users` (
  `user_id` int(11) UNSIGNED NOT NULL,
  `register_date` int(11) DEFAULT NULL,
  `email` varchar(233) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `supporter_email` varchar(233) DEFAULT NULL,
  `supporter_type` text DEFAULT NULL,
  `supporter_end_date` datetime DEFAULT NULL,
  `supporter_plus_end` datetime DEFAULT NULL,
  `supporter_last_paid_date` datetime DEFAULT NULL,
  `show_supporter_status` tinyint(1) NOT NULL DEFAULT 1,
  `password` varchar(255) DEFAULT NULL,
  `username` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `user_group` int(1) NOT NULL DEFAULT 3,
  `secondary_user_group` int(11) NOT NULL DEFAULT 0,
  `ip` varchar(255) DEFAULT NULL,
  `comment_count` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `avatar` text DEFAULT NULL,
  `author_picture` text DEFAULT NULL,
  `avatar_uploaded` tinyint(1) NOT NULL DEFAULT 0,
  `avatar_gallery` text DEFAULT NULL,
  `forum_posts` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `steam` varchar(255) DEFAULT NULL,
  `article_bio` text DEFAULT NULL,
  `about_me` text DEFAULT NULL,
  `twitter_on_profile` varchar(120) DEFAULT NULL,
  `banned` tinyint(1) NOT NULL DEFAULT 0,
  `ban_reason` text DEFAULT NULL,
  `oauth_uid` varchar(200) DEFAULT NULL,
  `oauth_provider` varchar(200) DEFAULT NULL,
  `twitter_username` varchar(200) DEFAULT NULL,
  `last_login` int(11) DEFAULT NULL,
  `website` text DEFAULT NULL,
  `auto_subscribe` tinyint(1) NOT NULL DEFAULT 0,
  `auto_subscribe_email` tinyint(1) NOT NULL DEFAULT 0,
  `email_on_pm` tinyint(1) NOT NULL DEFAULT 1,
  `theme` varchar(32) DEFAULT NULL,
  `supporter_link` text NOT NULL,
  `hide_developer_status` tinyint(1) NOT NULL DEFAULT 0,
  `youtube` text NOT NULL,
  `steam_id` bigint(20) DEFAULT NULL,
  `steam_username` text NOT NULL,
  `distro` text NOT NULL,
  `public_email` tinyint(1) NOT NULL DEFAULT 0,
  `auto_subscribe_new_article` tinyint(1) NOT NULL DEFAULT 0,
  `facebook` text DEFAULT NULL,
  `email_options` int(11) NOT NULL DEFAULT 2,
  `activated` tinyint(1) NOT NULL DEFAULT 0,
  `activation_code` varchar(255) DEFAULT NULL,
  `twitch` text DEFAULT NULL,
  `mastodon` text DEFAULT NULL,
  `gogprofile` text DEFAULT NULL,
  `in_mod_queue` tinyint(1) NOT NULL DEFAULT 1,
  `mod_approved` int(11) NOT NULL DEFAULT 0,
  `login_emails` tinyint(1) NOT NULL DEFAULT 1,
  `pc_info_public` tinyint(1) NOT NULL DEFAULT 0,
  `pc_info_filled` tinyint(1) NOT NULL DEFAULT 0,
  `per-page` int(11) NOT NULL DEFAULT 30,
  `articles-per-page` int(11) NOT NULL DEFAULT 15,
  `forum_type` varchar(15) NOT NULL DEFAULT 'normal_forum',
  `single_article_page` tinyint(1) NOT NULL DEFAULT 0,
  `submission_emails` tinyint(1) NOT NULL DEFAULT 0,
  `game_developer` tinyint(1) NOT NULL DEFAULT 0,
  `display_comment_alerts` tinyint(1) NOT NULL DEFAULT 1,
  `display_quote_alerts` tinyint(1) NOT NULL DEFAULT 1,
  `display_like_alerts` tinyint(1) NOT NULL DEFAULT 1,
  `admin_comment_alerts` tinyint(1) NOT NULL DEFAULT 1,
  `timezone` text DEFAULT NULL,
  `google_email` text NOT NULL,
  `email_articles` varchar(32) DEFAULT NULL,
  `mailing_list_key` text DEFAULT NULL,
  `global_search_visible` tinyint(1) NOT NULL DEFAULT 0,
  `get_pms` tinyint(1) NOT NULL DEFAULT 1,
  `private_profile` tinyint(1) NOT NULL DEFAULT 1,
  `social_stay_cookie` tinyint(1) NOT NULL DEFAULT 0,
  `lifetime_supporter` tinyint(1) DEFAULT 0,
  `profile_address` varchar(60) DEFAULT NULL,
  `hide_adverts` tinyint(1) NOT NULL DEFAULT 0,
  `full_rss_key` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;



ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`);

ALTER TABLE `users`
  MODIFY `user_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

型
使用Explain可以得到：
MariaDB 10.6

mariadb

2条答案

按热度按时间
xmjla07d

xmjla07d1#

当我在Dbfiddle上测试你的查询时，我看到EXPLAIN显示了一个不同的输出：
| ID|选择类型|表|类型|可能键|关键|键透镜|ref|行|额外|
| --|--|--|--|--|--|--|--|--|--|
| 1 |简单|C| ref| author_id，article_id，approved|批准| 1 |const| 1 |用where|
| 1 |简单|一|eq_ref|初级|初级| 4 |fiddle.c.article_id| 1 |用where|
| 1 |简单|u| eq_ref|初级|初级| 4 |fiddle.c.author_id| 1 |用where|
你可以看到这消除了文件排序和临时表。这两个都可能降低性能。
这个版本的EXPLAIN还将连接变成type: eq_ref连接，这意味着它们通过各自的主键在表a和u上进行查找。第一个表c使用辅键查找，由type: ref表示。
我在测试表中使用零行进行测试，这通常会导致不同的优化策略，因为成本估计是不同的。但它表明使用当前索引可以获得更好的优化。
总是值得尝试运行ANALYZE TABLE以确保优化器具有索引的当前统计信息，因此它的估计更准确。该命令在任何时候都可以在InnoDB表上快速安全地运行。
如果这没有帮助，那么您可能必须使用查询提示来强制表连接的特定顺序。

赞(0）分享  回复(0）举报  2023-11-19
stszievb

stszievb2#

提示SELECT STRAIGHT_JOIN解决了这个问题。

赞(0）分享  回复(0）举报  2023-11-19
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com