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

这是解释的屏幕截图,显示该索引未用于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更新索引.

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!