sql - 我如何优化这个慢查询？-6ren

sql - 我如何优化这个慢查询？

转载作者：行者123 更新时间：2023-11-30 21:22:57

我想知道如何优化它，最好是不改变表结构。

SELECT p.author_id member_id,
       m.members_display_name member_name,
       COUNT(p.pid) posts
FROM forum_topics t
     STRAIGHT_JOIN forum_posts p
     STRAIGHT_JOIN forum_members m
WHERE p.author_id != 0
      AND p.author_id = m.member_id
      AND p.new_topic = 0
      AND t.forum_id = 120
      AND t.tid = p.topic_id
GROUP BY p.author_id
ORDER BY posts DESC
LIMIT 1

EXPLAIN 的输出是:

-------------------------------------------------------------------------------
 EXPLAIN
  SELECT p.author_id member_id,
         m.members_display_name member_name,
         COUNT(p.pid) posts
    FROM forum_topics t
         STRAIGHT_JOIN forum_posts p
         STRAIGHT_JOIN forum_members m
   WHERE p.author_id != 0
         AND p.author_id = m.member_id
         AND p.new_topic = 0
         AND t.forum_id = 120
         AND t.tid = p.topic_id
GROUP BY p.author_id
ORDER BY posts DESC
   LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ref
possible_keys: PRIMARY,forum_id,last_post
          key: forum_id
      key_len: 2
          ref: const
         rows: 28070
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: ref
possible_keys: author_id,topic_id
          key: topic_id
      key_len: 4
          ref: forumwmo.t.tid
         rows: 5
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: forumwmo.p.author_id
         rows: 1
        Extra: 
3 rows in set (0.00 sec)

表结构为:

SHOW CREATE TABLE forum_members\G
*************************** 1. row ***************************
       Table: forum_members
Create Table: CREATE TABLE `forum_members` (
  `member_id` mediumint(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '',
  `member_group_id` smallint(3) NOT NULL DEFAULT '0',
  `email` varchar(150) NOT NULL DEFAULT '',
  `joined` int(10) NOT NULL DEFAULT '0',
  `ip_address` varchar(16) NOT NULL DEFAULT '',
  `posts` mediumint(7) DEFAULT '0',
  `title` varchar(64) DEFAULT NULL,
  `allow_admin_mails` tinyint(1) DEFAULT NULL,
  `time_offset` varchar(10) DEFAULT NULL,
  `hide_email` varchar(8) DEFAULT NULL,
  `email_pm` tinyint(1) DEFAULT NULL,
  `email_full` tinyint(1) DEFAULT NULL,
  `skin` smallint(5) DEFAULT NULL,
  `warn_level` int(10) DEFAULT NULL,
  `warn_lastwarn` int(10) NOT NULL DEFAULT '0',
  `language` varchar(32) DEFAULT NULL,
  `last_post` int(10) DEFAULT NULL,
  `restrict_post` varchar(100) NOT NULL DEFAULT '0',
  `view_sigs` tinyint(1) DEFAULT '1',
  `view_img` tinyint(1) DEFAULT '1',
  `view_avs` tinyint(1) DEFAULT '1',
  `view_pop` tinyint(1) DEFAULT '1',
  `bday_day` int(2) DEFAULT NULL,
  `bday_month` int(2) DEFAULT NULL,
  `bday_year` int(4) DEFAULT NULL,
  `msg_count_new` int(2) NOT NULL DEFAULT '0',
  `msg_count_total` int(3) NOT NULL DEFAULT '0',
  `msg_count_reset` int(1) NOT NULL DEFAULT '0',
  `msg_show_notification` int(1) NOT NULL DEFAULT '0',
  `misc` varchar(128) DEFAULT NULL,
  `last_visit` int(10) DEFAULT '0',
  `last_activity` int(10) DEFAULT '0',
  `dst_in_use` tinyint(1) DEFAULT '0',
  `view_prefs` varchar(64) DEFAULT '-1&-1',
  `coppa_user` tinyint(1) DEFAULT '0',
  `mod_posts` varchar(100) NOT NULL DEFAULT '0',
  `auto_track` varchar(50) DEFAULT '0',
  `org_perm_id` varchar(255) DEFAULT '',
  `temp_ban` varchar(100) DEFAULT '0',
  `sub_end` int(10) NOT NULL DEFAULT '0',
  `no_sig_lims` tinyint(1) NOT NULL DEFAULT '0',
  `login_anonymous` char(3) NOT NULL DEFAULT '0&0',
  `ignored_users` text,
  `mgroup_others` varchar(255) NOT NULL DEFAULT '',
  `member_login_key` varchar(32) NOT NULL DEFAULT '',
  `member_login_key_expire` int(10) NOT NULL DEFAULT '0',
  `subs_pkg_chosen` smallint(3) NOT NULL DEFAULT '0',
  `has_blog` tinyint(1) NOT NULL DEFAULT '0',
  `members_auto_dst` tinyint(1) NOT NULL DEFAULT '1',
  `members_cache` mediumtext,
  `members_disable_pm` int(1) NOT NULL DEFAULT '0',
  `members_display_name` varchar(255) NOT NULL DEFAULT '',
  `members_seo_name` varchar(255) NOT NULL DEFAULT '',
  `members_created_remote` tinyint(1) NOT NULL DEFAULT '0',
  `members_editor_choice` char(3) NOT NULL DEFAULT 'std',
  `members_profile_views` int(10) unsigned NOT NULL DEFAULT '0',
  `members_l_display_name` varchar(255) NOT NULL DEFAULT '0',
  `members_l_username` varchar(255) NOT NULL DEFAULT '0',
  `failed_logins` text,
  `failed_login_count` smallint(3) NOT NULL DEFAULT '0',
  `has_gallery` int(1) DEFAULT '0',
  `members_pass_hash` varchar(32) NOT NULL DEFAULT '',
  `members_pass_salt` varchar(5) NOT NULL DEFAULT '',
  `member_banned` tinyint(1) NOT NULL DEFAULT '0',
  `identity_url` text,
  `member_uploader` varchar(32) NOT NULL DEFAULT 'default',
  `members_bitoptions` int(10) unsigned NOT NULL DEFAULT '0',
  `fb_uid` bigint(20) NOT NULL DEFAULT '0',
  `fb_emailhash` varchar(60) NOT NULL DEFAULT '',
  `fb_emailallow` int(1) NOT NULL DEFAULT '0',
  `fb_lastsync` int(10) NOT NULL DEFAULT '0',
  `members_day_posts` varchar(32) NOT NULL DEFAULT '0,0',
  `live_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`member_id`),
  KEY `mgroup` (`member_group_id`),
  KEY `bday_day` (`bday_day`),
  KEY `bday_month` (`bday_month`),
  KEY `members_l_display_name` (`members_l_display_name`),
  KEY `members_l_username` (`members_l_username`),
  KEY `member_banned` (`member_banned`),
  KEY `members_bitoptions` (`members_bitoptions`),
  KEY `ip_address` (`ip_address`)
) ENGINE=MyISAM AUTO_INCREMENT=101965 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
-------------------------------------------------------------------------------
SHOW CREATE TABLE forum_posts\G
*************************** 1. row ***************************
       Table: forum_posts
Create Table: CREATE TABLE `forum_posts` (
  `append_edit` tinyint(1) DEFAULT '0',
  `edit_time` int(10) DEFAULT NULL,
  `pid` int(10) NOT NULL AUTO_INCREMENT,
  `author_id` mediumint(8) NOT NULL DEFAULT '0',
  `author_name` varchar(32) DEFAULT NULL,
  `use_sig` tinyint(1) NOT NULL DEFAULT '0',
  `use_emo` tinyint(1) NOT NULL DEFAULT '0',
  `ip_address` varchar(16) NOT NULL DEFAULT '',
  `post_date` int(10) DEFAULT NULL,
  `icon_id` smallint(3) DEFAULT NULL,
  `post` mediumtext,
  `queued` tinyint(1) NOT NULL DEFAULT '0',
  `topic_id` int(10) NOT NULL DEFAULT '0',
  `post_title` varchar(255) DEFAULT NULL,
  `new_topic` tinyint(1) DEFAULT '0',
  `edit_name` varchar(255) DEFAULT NULL,
  `post_parent` int(10) NOT NULL DEFAULT '0',
  `post_key` varchar(32) NOT NULL DEFAULT '0',
  `post_htmlstate` smallint(1) NOT NULL DEFAULT '0',
  `post_edit_reason` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`pid`),
  KEY `author_id` (`author_id`,`topic_id`),
  KEY `post_date` (`post_date`),
  KEY `topic_id` (`topic_id`,`queued`,`pid`,`post_date`),
  KEY `post_key` (`post_key`),
  KEY `ip_address` (`ip_address`)
) ENGINE=MyISAM AUTO_INCREMENT=988489 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
-------------------------------------------------------------------------------
SHOW CREATE TABLE forum_topics\G
*************************** 1. row ***************************
       Table: forum_topics
Create Table: CREATE TABLE `forum_topics` (
  `tid` int(10) NOT NULL AUTO_INCREMENT,
  `title` varchar(70) NOT NULL DEFAULT '',
  `description` varchar(250) DEFAULT NULL,
  `state` varchar(8) DEFAULT NULL,
  `posts` int(10) DEFAULT NULL,
  `starter_id` mediumint(8) NOT NULL DEFAULT '0',
  `start_date` int(10) DEFAULT NULL,
  `last_poster_id` mediumint(8) NOT NULL DEFAULT '0',
  `last_post` int(10) DEFAULT NULL,
  `icon_id` tinyint(2) DEFAULT NULL,
  `starter_name` varchar(255) DEFAULT NULL,
  `last_poster_name` varchar(255) DEFAULT NULL,
  `poll_state` varchar(8) DEFAULT NULL,
  `last_vote` int(10) DEFAULT NULL,
  `views` int(10) DEFAULT '0',
  `forum_id` smallint(5) NOT NULL DEFAULT '0',
  `approved` tinyint(1) NOT NULL DEFAULT '0',
  `author_mode` tinyint(1) DEFAULT NULL,
  `pinned` tinyint(1) NOT NULL DEFAULT '0',
  `moved_to` varchar(64) DEFAULT NULL,
  `total_votes` int(5) NOT NULL DEFAULT '0',
  `topic_hasattach` smallint(5) NOT NULL DEFAULT '0',
  `topic_firstpost` int(10) NOT NULL DEFAULT '0',
  `topic_queuedposts` int(10) NOT NULL DEFAULT '0',
  `topic_rating_total` smallint(5) unsigned NOT NULL DEFAULT '0',
  `topic_rating_hits` smallint(5) unsigned NOT NULL DEFAULT '0',
  `topic_open_time` int(10) NOT NULL DEFAULT '0',
  `topic_close_time` int(10) NOT NULL DEFAULT '0',
  `title_seo` varchar(250) NOT NULL DEFAULT '',
  `seo_last_name` varchar(255) NOT NULL DEFAULT '',
  `seo_first_name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`tid`),
  KEY `topic_firstpost` (`topic_firstpost`),
  KEY `forum_id` (`forum_id`,`pinned`,`approved`),
  KEY `last_post` (`forum_id`,`pinned`,`last_post`),
  KEY `starter_id` (`starter_id`,`forum_id`,`approved`),
  KEY `last_post_sorting` (`last_post`,`forum_id`),
  KEY `start_date` (`start_date`),
  FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=227233 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

表格属性是:

SHOW TABLE STATUS LIKE 'forum_members'\G
*************************** 1. row ***************************
           Name: forum_members
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 64866
 Avg_row_length: 257
    Data_length: 16688164
Max_data_length: 281474976710655
   Index_length: 6540288
      Data_free: 0
 Auto_increment: 101965
    Create_time: 2009-12-02 05:45:59
    Update_time: 2009-12-02 05:46:07
     Check_time: 2009-12-02 05:49:23
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
-------------------------------------------------------------------------------
SHOW TABLE STATUS LIKE 'forum_posts'\G
*************************** 1. row ***************************
           Name: forum_posts
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 581777
 Avg_row_length: 720
    Data_length: 419170168
Max_data_length: 281474976710655
   Index_length: 46439424
      Data_free: 0
 Auto_increment: 988489
    Create_time: 2009-12-02 05:47:04
    Update_time: 2009-12-02 05:48:13
     Check_time: 2009-12-02 05:49:28
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
-------------------------------------------------------------------------------
SHOW TABLE STATUS LIKE 'forum_topics'\G
*************************** 1. row ***************************
           Name: forum_topics
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 117458
 Avg_row_length: 144
    Data_length: 17004836
Max_data_length: 281474976710655
   Index_length: 14105600
      Data_free: 0
 Auto_increment: 227233
    Create_time: 2009-12-02 05:48:38
    Update_time: 2009-12-02 05:48:43
     Check_time: 2009-12-02 05:49:28
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

其他配置细节:

SHOW VARIABLES LIKE '%buffer%'\G
*************************** 1. row ***************************
Variable_name: bulk_insert_buffer_size
        Value: 8388608
*************************** 2. row ***************************
Variable_name: innodb_buffer_pool_size
        Value: 8388608
*************************** 3. row ***************************
Variable_name: innodb_log_buffer_size
        Value: 1048576
*************************** 4. row ***************************
Variable_name: join_buffer_size
        Value: 131072
*************************** 5. row ***************************
Variable_name: key_buffer_size
        Value: 8384512
*************************** 6. row ***************************
Variable_name: myisam_sort_buffer_size
        Value: 8388608
*************************** 7. row ***************************
Variable_name: net_buffer_length
        Value: 16384
*************************** 8. row ***************************
Variable_name: pbxt_log_buffer_size
        Value: 256K
*************************** 9. row ***************************
Variable_name: pbxt_transaction_buffer_size
        Value: 1MB
*************************** 10. row ***************************
Variable_name: preload_buffer_size
        Value: 32768
*************************** 11. row ***************************
Variable_name: read_buffer_size
        Value: 131072
*************************** 12. row ***************************
Variable_name: read_rnd_buffer_size
        Value: 262144
*************************** 13. row ***************************
Variable_name: sort_buffer_size
        Value: 2097144
*************************** 14. row ***************************
Variable_name: sql_buffer_result
        Value: OFF
14 rows in set (0.00 sec)

最佳答案

您可能会通过将一些 WHERE 子句条件移到 JOIN 中来提高性能，因为这会在执行查询时减少结果集，例如

SELECT p.author_id            member_id,
       m.members_display_name member_name,
       COUNT(p.pid)           posts
FROM forum_topics t
     STRAIGHT_JOIN forum_posts p 
     ON            t.tid      = p.topic_id 
     AND           t.forum_id = 120 
     AND p.new_topic = 0
     STRAIGHT_JOIN forum_members m 
     ON p.author_id = m.member_id
WHERE p.author_id   != 0      
GROUP BY p.author_id
ORDER BY posts DESC
LIMIT 1

不过，优化器可能足够聪明，可以为您完成这项工作。此外，虽然我不使用 MySQL，但我对 STRAIGH_JOIN 的理解是它以明确的从左到右的顺序连接这些表，因此如果表按从小到大的大小顺序列出，反之亦然，它可能会有所不同。

关于sql - 我如何优化这个慢查询？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/1831673/

文章推荐： c - "memory allocation"在C中实际上意味着什么

文章推荐： c# - 无法访问 Page_Load 中的控件

文章推荐： c - 编写一个访问数组的主程序

文章推荐： c# - 突出显示 Asp.net 图表控件系列中的一列

sql - SQL、PL-SQL 和 T-SQL 之间有什么区别？
SQL、PL-SQL 和 T-SQL 之间有什么区别？谁能解释一下这三者之间的区别，并提供每一个的相关使用场景？最佳答案 SQL 是一种对集合进行操作的查询语言。它或多或少是标准化的，几乎所有关
sql - T-SQL、SQL Server 和 SQL 有什么区别
这个问题已经有答案了: What is the difference between SQL, PL-SQL and T-SQL? (6 个回答) 已关闭 9 年前。我对 SQL 的了解足以完成我的
sql - Linq To Sql - SQL 默认约束问题
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列，该列有一个默认约束为 GETDATE()。使用 LINQ 时，我没有为 RegistrationDate 列提供任
sql - 在字符串中查找第二组数字(SQL/PL-SQL)
我有一个可能属于以下类型的字符串 string expected result 15-th-rp 15 15/12-rp 12 15-12-th
sql - 服务器端 sql 与客户端 sql
很难说出这里问的是什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或言辞激烈，无法以目前的形式合理回答。如需帮助澄清此问题以便可以重新打开，visit the help center . 9年前关闭
sql - sql 如何计算 sql 存储过程中的附加表？
我有一个存储过程(称为 sprocGetArticles)，它从文章表中返回文章列表。这个存储过程没有任何参数。用户可以对每篇文章发表评论，我将这些评论存储在由文章 ID 链接的评论表中。有什么方
sql - 嵌入式 SQL 与动态 SQL
我目前正在做一个 *cough*Oracle*cough* 数据库主题。讲师介绍embedded SQL作为让其他语言(例如 C、C++)与(Oracle)数据库交互的方式。我自己做了一些数据库工作
sql - SQL Server SQL 语句可以有多少个字符？
SQL Server 中 SQL 语句的最大长度是多少？这个长度是否取决于 SQL Server 的版本？例如，在 DECLARE @SQLStatement NVARCHAR(MAX) = N'S
sql-server - SQL 行到列 sql
这个问题已经有答案了: Simple way to transpose columns and rows in SQL? (9 个回答) 已关闭 8 年前。 CallType
sql - SQL Server SQL 语句中的动态日期
预先感谢您对此提供的任何帮助。假设我有一个查询，可以比较跨年的数据，从某个任意年份开始，永无止境(进入 future )，每年同一时期直到最后一个完整的月份(其特点是一月数据永远不会显示至 2 月
sql - Linq To Sql - SQL 默认约束问题
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列，该列的默认约束为 GETDATE()。使用 LINQ 时，我没有为 RegistrationDate 列提供任何数
sql - (SQL Server) SQL 不允许在检查过程是否存在后创建过程
下面是我试图用来检查存储过程是否不存在然后创建过程的 sql。它会抛出一个错误:Incorrect syntax near the keyword 'PROCEDURE' IF NOT EXISTS
sql - 动态 SQL 是否比 SQL Server 中的静态 SQL 性能更高？
我有一个同事声称动态 SQL 在许多情况下比静态 SQL 执行得更快，所以我经常看到 DSQL 到处都是。除了明显的缺点，比如在运行之前无法检测到错误并且更难阅读，这是否准确？当我问他为什么一直使用
sql - exec sp_executesql @sql 和 exec (@sql) SQL Server
来自 lobodava 的动态 SQL 查询是: declare @sql nvarchar(4000) = N';with cteColumnts (ORDINAL_POSITION, CO
sql - 动态 SQL - EXEC(@SQL) 与 EXEC SP_EXECUTESQL(@SQL)
使用 SQL Server 中的存储过程执行动态 SQL 命令的现实优点和缺点是什么 EXEC (@SQL) 对比 EXEC SP_EXECUTESQL @SQL ？最佳答案 sp_executes
c# - SQL > Linq to Sql，SQL 查询有效，Linq to SQL 返回空数据集
我有这个有效的 SQL 查询: select sum(dbos.Points) as Points, dboseasons.Year from dbo.StatLines dbos i
sql-server - "> sql.txt && sql -h-1 -i sql.txt && del sql.txt"命令是什么意思？
我正在调试一些构建成功运行的 SQL 命令的代码。然而，在查询结束时，查询结果似乎被写入了一个文本文件。完整的查询如下 echo SELECT DATE,DATETABLE,DATE,APPDAT
sql - 如何从 MS SQL 数据库(Microsoft SQL Server)中的其他 .sql 文件运行 .sql 文件？
我有一些创建表的 .sql 文件(MS SQL 数据库): 表_1.sql: IF OBJECT_ID (N'my_schema.table1', N'U') IS NOT NULL DROP TAB
sql - 如何在查询中使用 SQL 变量(SQL Server)？
我写了下面的 SQL 存储过程，它一直给我错误@pid = SELECT MAX(... 整个过程是: Alter PROCEDURE insert_partyco @pname varchar(20
sql - 如何将两个列表转换为邻接矩阵 SQL Server T-SQL？
我在 SQL Server 2005 中有包含两列 Fruit 和 Color 的表，如下所示 Fruit Colour Apple Red Orange

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql - 我如何优化这个慢查询？