优化MySQL慢查询，去掉Using where；使用临时的；使用文件排序

Question

我正在尝试优化一个查询，该查询需要 2 秒多的时间才能返回仅包含 10,000 条记录的数据库的响应。解释显示它应该几乎立即返回。我认为性能缓慢是因为Using where；使用临时的；使用文件排序？

这是查询：

  SELECT distinct a.id, profi.company, profi.logo, profi.id as profileid ,  a.job_detail_section as job_detail_section_tmp ,  a.title as title_tmp ,  a.location_id as location_id_tmp ,  a.salary_min as salary_min_tmp ,  a.salary_max as salary_max_tmp ,  a.currency as currency_tmp ,  a.frequency as frequency_tmp ,  a.job_type as job_type_tmp ,  a.cat_id as cat_id_tmp ,  a.job_title as job_title_tmp ,  a.job_detail as job_detail_tmp ,  a.status as status_tmp ,  a.created_date as created_date_tmp ,  a.effected_date as effected_date_tmp ,  a.is_hotjob as is_hotjob_tmp ,  a.user_id as user_id_tmp ,  a.id as id_tmp ,  a.views as views_tmp ,  a.ordering as ordering_tmp ,  a.apply_type as apply_type_tmp ,  a.direct_url as direct_url_tmp 
  FROM jos_ja_jobs as a
  INNER JOIN jos_ja_profiles as  profi   ON profi.user_id=a.user_id AND profi.approved=1
  INNER JOIN jos_users as rs  ON rs.id=a.user_id AND rs.block=0
  WHERE a.status='Approved'  AND (a.effected_date<=now()) AND ( (DATE_ADD(a.effected_date, INTERVAL 90 DAY) >= now()  AND a.is_hotjob=0) or (DATE_ADD(a.effected_date, INTERVAL 30 DAY) >= now()  AND a.is_hotjob=1) )
  ORDER BY a.is_hotjob desc, a.effected_date desc LIMIT 0, 5

和解释：

    +----+-------------+-------+--------+------------------------------+---------+---------+------------------------+------+----------------------------------------------+
    | id | select_type | table | type   | possible_keys                | key     | key_len | ref                    | rows | Extra                                        |
    +----+-------------+-------+--------+------------------------------+---------+---------+------------------------+------+----------------------------------------------+
    |  1 | SIMPLE      | a     | ref    | effected_date,user_id,status | status  | 768     | const                  | 1880 | Using where; Using temporary; Using filesort |
    |  1 | SIMPLE      | profi | ref    | user_id,approved             | user_id | 5       | esljw_joomla.a.user_id |    1 | Using where                                  |
    |  1 | SIMPLE      | rs    | eq_ref | PRIMARY                      | PRIMARY | 4       | esljw_joomla.a.user_id |    1 | Using where; Distinct                        |
    +----+-------------+-------+--------+------------------------------+---------+---------+------------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

和表结构：

CREATE TABLE IF NOT EXISTS `jos_ja_jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `salary_range` varchar(255) NOT NULL,
  `location_id` varchar(255) NOT NULL,
  `job_detail` mediumtext,
  `status` varchar(255) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `currency` varchar(255) DEFAULT NULL,
  `salary_min` int(11) DEFAULT NULL,
  `salary_max` int(11) DEFAULT NULL,
  `effected_date` datetime DEFAULT NULL,
  `server_effected_date` datetime DEFAULT NULL,
  `image` varchar(100) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `checked_out` tinyint(4) DEFAULT NULL,
  `checked_out_time` date DEFAULT NULL,
  `cat_id` varchar(255) NOT NULL DEFAULT '0',
  `job_code` varchar(255) DEFAULT NULL,
  `section2` varchar(255) DEFAULT NULL,
  `section3` varchar(255) DEFAULT NULL,
  `section4` varchar(255) DEFAULT NULL,
  `is_hotjob` tinyint(4) DEFAULT '0',
  `user_id` int(11) DEFAULT NULL,
  `job_type` varchar(255) DEFAULT NULL,
  `views` int(11) DEFAULT '0',
  `ordering` int(11) DEFAULT '0',
  `feed_id` int(11) DEFAULT '0',
  `feed_guid` varchar(1000) DEFAULT NULL,
  `import_date` datetime DEFAULT NULL,
  `apply_type` varchar(255) DEFAULT '3',
  `direct_url` varchar(255) DEFAULT '',
  `distance` varchar(255) DEFAULT '0',
  `zipcode` int(11) DEFAULT NULL,
  `job_detail_section` varchar(255) DEFAULT NULL,
  `expiry_date` datetime DEFAULT NULL,
  `job_duration` int(11) DEFAULT '30' COMMENT 'Number of available days for job',
  `frequency` varchar(255) NOT NULL DEFAULT '1',
  `class_type` varchar(255) DEFAULT NULL,
  `job_title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cat_id` (`cat_id`),
  KEY `effected_date` (`effected_date`),
  KEY `job_type` (`job_type`),
  KEY `location_id` (`location_id`),
  KEY `user_id` (`user_id`),
  KEY `created_date` (`created_date`),
  KEY `status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10124 ;

CREATE TABLE IF NOT EXISTS `jos_ja_profiles` (
  `id` mediumint(11) NOT NULL AUTO_INCREMENT,
  `company` varchar(255) NOT NULL,
  `business_type` varchar(255) DEFAULT '1',
  `logo` varchar(255) DEFAULT NULL,
  `section2` varchar(255) DEFAULT NULL,
  `address` varchar(255) NOT NULL,
  `website` varchar(255) DEFAULT NULL,
  `tel` varchar(255) DEFAULT NULL,
  `section3` varchar(255) DEFAULT NULL,
  `checked_out` varchar(255) DEFAULT NULL,
  `checked_out_time` datetime DEFAULT NULL,
  `location_id` varchar(255) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `cat_id` varchar(255) DEFAULT NULL,
  `attachment` varchar(255) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `short_desc` text,
  `approved` tinyint(4) NOT NULL DEFAULT '0',
  `subscription_section` varchar(255) DEFAULT '',
  `duration_notice` varchar(255) DEFAULT '',
  `latest_sendmail` date DEFAULT NULL,
  `modified_notice` tinyint(4) NOT NULL DEFAULT '0',
  `change_log` longtext,
  `profile_detail_section` varchar(255) DEFAULT NULL,
  `time_offset` varchar(255) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `approved` (`approved`),
  KEY `company` (`company`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED AUTO_INCREMENT=852 ;

CREATE TABLE IF NOT EXISTS `jos_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `username` varchar(150) NOT NULL DEFAULT '',
  `email` varchar(100) NOT NULL DEFAULT '',
  `password` varchar(100) NOT NULL DEFAULT '',
  `usertype` varchar(25) NOT NULL DEFAULT '',
  `block` tinyint(4) NOT NULL DEFAULT '0',
  `sendEmail` tinyint(4) DEFAULT '0',
  `gid` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `registerDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lastvisitDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `activation` varchar(100) NOT NULL DEFAULT '',
  `params` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `usertype` (`usertype`),
  KEY `idx_name` (`name`),
  KEY `gid_block` (`gid`,`block`),
  KEY `username` (`username`),
  KEY `email` (`email`),
  KEY `registerDate` (`registerDate`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9095 ;

感谢任何帮助！

编辑：通过添加此索引可以加快速度：

ALTER TABLE jos_ja_jobs ADD INDEX hot_date (is_hotjob, effected_date);

Answer 1

您应该在要过滤的任何字段上都有索引。尝试在作业表中的日期字段上添加索引。

好吧，不是针对每一项，而是针对那些会大大缩小结果范围或您将经常查询的项。

Answer 2

如果您确实想优化该查询，请将实际值放入日期，而不是使用函数 now() 和其他一些日期函数。另外，请确保所有内部联接都被使用。例如：

    SELECT distinct a.id, profi.company, profi.logo, profi.id as profileid ,  a.job_detail_section as job_detail_section_tmp ,  a.title as title_tmp ,  a.location_id as location_id_tmp ,  a.salary_min as salary_min_tmp ,  a.salary_max as salary_max_tmp ,  a.currency as currency_tmp ,  a.frequency as frequency_tmp ,  a.job_type as job_type_tmp ,  a.cat_id as cat_id_tmp ,  a.job_title as job_title_tmp ,  a.job_detail as job_detail_tmp ,  a.status as status_tmp ,  a.created_date as created_date_tmp ,  a.effected_date as effected_date_tmp ,  a.is_hotjob as is_hotjob_tmp ,  a.user_id as user_id_tmp ,  a.id as id_tmp ,  a.views as views_tmp ,  a.ordering as ordering_tmp ,  a.apply_type as apply_type_tmp ,  a.direct_url as direct_url_tmp 
  FROM jos_ja_jobs as a
  INNER JOIN jos_ja_profiles as  profi   ON profi.user_id=a.user_id 
  INNER JOIN jos_users as rs  ON rs.id=a.user_id and rs.id = profi.user_id
  WHERE a.status='Approved'  
  AND profi.approved=1
  AND rs.block=0
  AND a.effected_date <= '2011-06-21'
  -- Instead have the two dates place there by the application
  AND( 
  (a.effected_date BETWEEN '2011-06-21' AND '2011-09-21' AND a.is_hotjob=0) 
  OR (a.effected_date BETWEEN '2011-06-21' AND '2011-07-21' AND a.is_hotjob=1) )
  ORDER BY a.is_hotjob desc, a.effected_date desc LIMIT 0, 5

如果有帮助请告诉我！

问候，
马塞洛

Answer 3

就像您的其他查询帮助帖子以及我关于使用“STRAIGHT_JOIN”子句的回答一样，请在此处执行相同的操作...

SELECT STRAIGHT_JOIN DISTINCT ...

此外，在 (Status, Effected_Date) 上有一个复合索引

最后，在您的 where 子句中，在effected_Date 为至少 90 天的任何受影响日期...由于这是您根据是否热门工作允许的最远日期，至少状态和日期过滤器将与索引匹配并进行优化...剩下的保留你的其他日期标准按原样，因为它们看起来不错，并且最终会进一步过滤到您期望的最终结果......

WHERE
       a.status='Approved'  
   AND a.effected_date >= date_sub(now(), interval 90 days)
   AND (rest of your other date criteria)