使用昂贵的INNER JOIN优化MySQL查询

使用试验和错误我发现,从以下查询中删除连接时,它运行速度提高了大约30倍.有人可以解释为什么会这样,并且是否可以优化查询以包含额外的连接而不会影响性能.

这是解释的屏幕截图,显示该索引未用于uesr_groups表.

http://i.imgur.com/9VDuV.png

这是原始查询：

SELECT `comments`.`comment_id`,`comments`.`comment_html`,`comments`.`comment_time_added`,`comments`.`comment_has_attachments`,`users`.`user_name`,`users`.`user_id`,`users`.`user_comments_count`,`users`.`user_time_registered`,`users`.`user_time_last_active`,`user_profile`.`user_avatar`,`user_profile`.`user_signature_html`,`user_groups`.`user_group_icon`,`user_groups`.`user_group_name`
FROM (`comments`)
INNER JOIN `users` ON `comments`.`comment_user_id` = `users`.`user_id`
INNER JOIN `user_profile` ON `users`.`user_id` = `user_profile`.`user_id`
INNER JOIN `user_groups` ON `users`.`user_group_id` = `user_groups`.`user_group_id`
WHERE `comments`.`comment_enabled` =  1
AND `comments`.`comment_content_id` =  12
ORDER BY `comments`.`comment_time_added` ASC
LIMIT 20

如果我删除“user_groups”连接,则查询运行速度提高30倍,如上所述.

SELECT `comments`.`comment_id`,`user_profile`.`user_signature_html`
FROM (`comments`)
INNER JOIN `users` ON `comments`.`comment_user_id` = `users`.`user_id`
INNER JOIN `user_profile` ON `users`.`user_id` = `user_profile`.`user_id`
WHERE `comments`.`comment_enabled` =  1
AND `comments`.`comment_content_id` =  12
ORDER BY `comments`.`comment_time_added` ASC
LIMIT 20

我的表格如下,任何人都可以提供任何有关如何避免包含user_groups表格的性能影响的见解吗？

--
-- Table structure for table `comments`
--

CREATE TABLE IF NOT EXISTS `comments` (
  `comment_id` int(10) unsigned NOT NULL AUTO_INCREMENT,`comment_content_id` int(10) unsigned NOT NULL,`comment_user_id` mediumint(6) unsigned NOT NULL,`comment_original` text NOT NULL,`comment_html` text NOT NULL,`comment_time_added` int(10) unsigned NOT NULL,`comment_time_updated` int(10) unsigned NOT NULL,`comment_enabled` tinyint(1) NOT NULL DEFAULT '0',`comment_is_spam` tinyint(1) NOT NULL DEFAULT '0',`comment_has_attachments` tinyint(1) unsigned NOT NULL,`comment_has_edits` tinyint(1) NOT NULL,PRIMARY KEY (`comment_id`),KEY `comment_user_id` (`comment_user_id`),KEY `comment_content_id` (`comment_content_id`),KEY `comment_is_spam` (`comment_is_spam`),KEY `comment_enabled` (`comment_enabled`),KEY `comment_time_updated` (`comment_time_updated`),KEY `comment_time_added` (`comment_time_added`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=352 ;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,`user_ipb_id` int(10) unsigned DEFAULT NULL,`user_activated` tinyint(1) NOT NULL DEFAULT '0',`user_name` varchar(64) CHARACTER SET latin1 NOT NULL,`user_email` varchar(255) NOT NULL,`user_password` varchar(40) NOT NULL,`user_content_count` int(10) unsigned NOT NULL DEFAULT '0',`user_comments_count` int(10) unsigned NOT NULL DEFAULT '0',`user_salt` varchar(8) NOT NULL,`user_api_key` varchar(32) NOT NULL,`user_auth_key` varchar(32) DEFAULT NULL,`user_paypal_key` varchar(32) DEFAULT NULL,`user_timezone_id` smallint(3) unsigned NOT NULL,`user_group_id` tinyint(3) unsigned NOT NULL,`user_custom_permission_mask_id` tinyint(3) unsigned DEFAULT NULL,`user_lang_id` tinyint(2) unsigned NOT NULL,`user_time_registered` int(10) unsigned NOT NULL,`user_time_last_active` int(10) unsigned NOT NULL
  PRIMARY KEY (`user_id`),UNIQUE KEY `user_email` (`user_email`),KEY `user_group_id` (`user_group_id`),KEY `user_auth_key` (`user_auth_key`),KEY `user_api_key` (`user_api_key`),KEY `user_custom_permission_mask_id` (`user_custom_permission_mask_id`),KEY `user_time_last_active` (`user_time_last_active`),KEY `user_paypal_key` (`user_paypal_key`),KEY `user_name` (`user_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=33 ;

-- --------------------------------------------------------

--
-- Table structure for table `user_groups`
--

CREATE TABLE IF NOT EXISTS `user_groups` (
  `user_group_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,`user_group_name` varchar(32) NOT NULL,`user_group_permission_mask_id` tinyint(3) unsigned NOT NULL,`user_group_icon` varchar(32) DEFAULT NULL,PRIMARY KEY (`user_group_id`),KEY `user_group_permission_mask_id` (`user_group_permission_mask_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

-- --------------------------------------------------------

--
-- Table structure for table `user_profile`
--

CREATE TABLE IF NOT EXISTS `user_profile` (
  `user_id` mediumint(8) unsigned NOT NULL,`user_signature_original` text,`user_signature_html` text,`user_avatar` varchar(64) DEFAULT NULL,`user_steam_id` varchar(64) DEFAULT NULL,`user_ps_id` varchar(16) DEFAULT NULL,`user_xBox_id` varchar(64) DEFAULT NULL,`user_wii_id` varchar(64) DEFAULT NULL,PRIMARY KEY (`user_id`),KEY `user_steam_id` (`user_steam_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

解决方法

大多数数据库引擎根据有关表的统计信息计算其查询计划 – 例如,如果表的行数较少,则转到表的速度比索引快.这些统计数据在“正常”操作期间保持 – 例如插入,更新和删除 – 但在更改表定义或进行批量插入时可能会不同步.

如果在查询计划中看到意外行为,则可以强制数据库更新其统计信息;在MysqL中,您可以使用Optimize Table – 它可以执行所有操作,包括重新排序表本身,或者仅使用Analyze Table更新索引.

这在生产环境中很难做到,因为两个操作都会锁定表格;如果您可以协商维护窗口,那么这是解决问题的最简单方法.

值得测量“优化表”的性能 – 在指定的硬件上,“普通”大小表只需几秒钟(高达数百万条记录,只有少量索引).这可能意味着您可以拥有一个“非正式”维护窗口 – 您不会使应用程序脱机,您只是接受某些用户在运行脚本时性能会下降.

原文链接：https://www.f2er.com/mssql/75081.html

使用昂贵的INNER JOIN优化MySQL查询

解决方法

猜你在找的MsSQL相关文章