无论环境如何，以下查询的计算时间都超过 30 秒。

SELECT COUNT( r.response_answer ) 
FROM response r
INNER JOIN (
 SELECT G.question_id
 FROM question G
 INNER JOIN answer_group AG ON G.answer_group_id = AG.answer_group_id
 WHERE AG.answer_group_stat =  'statistic'
) AS q ON r.question_id = q.question_id
INNER JOIN org_survey os ON os.org_survey_code = r.org_survey_code
WHERE os.survey_id =42
AND r.response_answer = 5
AND DATEDIFF( NOW( ) , r.added_dt ) <1000000
AND r.uuid IS NOT NULL

当我解释查询时，

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    1087     
1   PRIMARY r   ref question_id,org_survey_code,code_question,uuid,uor question_id  4   q.question_id   1545    Using where
1   PRIMARY os  eq_ref  org_survey_code,survey_id,org_survey_code_2 org_survey_code 12  survey_2.r.org_survey_code  1   Using where
2   DERIVED G   ALL agid    NULL    NULL    NULL    1680     
2   DERIVED AG  eq_ref  PRIMARY PRIMARY 1   survey_2.G.answer_group_id    1 Using where

我对索引有非常基本的了解，但我几乎尝试了所有我能想到的组合，但似乎无法提高此查询的速度。 responses 表大约有 200 万行，question 大约有 1500 行，answer_group 大约有 50 行，org_survey 大约有 8,000 行。

这是每个的基本结构:

CREATE TABLE `response` (
 `response_id` int(10) unsigned NOT NULL auto_increment,
 `response_answer` text NOT NULL,
 `question_id` int(10) unsigned NOT NULL default '0',
 `org_survey_code` varchar(7) NOT NULL,
 `uuid` varchar(40) default NULL,
 `added_dt` datetime default NULL,
 PRIMARY KEY  (`response_id`),
 KEY `question_id` (`question_id`),
 KEY `org_survey_code` (`org_survey_code`),
 KEY `code_question` (`org_survey_code`,`question_id`),
 KEY `IDX_ADDED_DT` (`added_dt`),
 KEY `uuid` (`uuid`),
 KEY `response_answer` (`response_answer`(1)),
 KEY `response_question` (`response_answer`(1),`question_id`),
) ENGINE=MyISAM AUTO_INCREMENT=2298109 DEFAULT CHARSET=latin1

CREATE TABLE `question` (
 `question_id` int(10) unsigned NOT NULL auto_increment,
 `question_text` varchar(250) NOT NULL default '',
 `question_group` varchar(250) default NULL,
 `question_position` tinyint(3) unsigned NOT NULL default '0',
 `survey_id` tinyint(3) unsigned NOT NULL default '0',
 `answer_group_id` mediumint(8) unsigned NOT NULL default '0',
 `seq_id` int(11) NOT NULL default '0',
 PRIMARY KEY  (`question_id`),
 KEY `question_group` (`question_group`(10)),
 KEY `survey_id` (`survey_id`),
 KEY `agid` (`answer_group_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1860 DEFAULT CHARSET=latin1

CREATE TABLE `org_survey` (
 `org_survey_id` int(11) NOT NULL auto_increment,
 `org_survey_code` varchar(10) NOT NULL default '',
 `org_id` int(11) NOT NULL default '0',
 `org_manager_id` int(11) NOT NULL default '0',
 `org_url_id` int(11) default '0',
 `division_id` int(11) default '0',
 `sector_id` int(11) default NULL,
 `survey_id` int(11) NOT NULL default '0',
 `process_batch` tinyint(4) default '0',
 `added_dt` datetime default NULL,
 PRIMARY KEY  (`org_survey_id`),
 UNIQUE KEY `org_survey_code` (`org_survey_code`),
 KEY `org_id` (`org_id`),
 KEY `survey_id` (`survey_id`),
 KEY `org_survey_code_2` (`org_survey_code`,`total_taken`),
 KEY `org_manager_id` (`org_manager_id`),
 KEY `sector_id` (`sector_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9268 DEFAULT CHARSET=latin1

CREATE TABLE `answer_group` (
 `answer_group_id` tinyint(3) unsigned NOT NULL auto_increment,
 `answer_group_name` varchar(50) NOT NULL default '',
 `answer_group_type` varchar(20) NOT NULL default '',
 `answer_group_stat` varchar(20) NOT NULL default 'demographic',
 PRIMARY KEY  (`answer_group_id`)
) ENGINE=MyISAM AUTO_INCREMENT=53 DEFAULT CHARSET=latin1

我知道我可以做一些小事来提高数据库的效率，例如减少不需要的整数的大小。然而，考虑到在这里产生结果所花费的荒谬时间，这些都是相当微不足道的。我如何根据解释向我展示的内容正确索引这些表？看来我尝试了很多种组合都无济于事。另外，还有什么任何人都可以看到的可以优化表并减少查询的东西吗？我需要在不到一秒的时间内计算出来。提前致谢!

最佳答案

1.如果你想使用r.added_dt的索引，而不是:

DATEDIFF(NOW(), r.added_dt) < 1000000

使用:

CURDATE() - INTERVAL 1000000 DAY < r.added_dt 

无论如何，上述条件正在检查 added_at 是否一百万天。你真的存储这么旧的日期吗？如果没有，您可以简单地删除此条件。

如果你想要这个条件，added_at 上的索引会很有帮助。您现在的查询检查所有行是否符合此条件，调用 DATEDIFF() 函数的次数与 response 表的行一样多。

2.由于 r.response_answer 不能为 NULL，而不是:

SELECT COUNT( r.response_answer ) 

使用:

SELECT COUNT( * ) 

COUNT(*) 比 COUNT(field) 快。

3.用于连接表的三个字段中的两个具有不同的数据类型:

ON       question . answer_group_id 
   = answer_group . answer_group_id

CREATE TABLE question (
  ...
  answer_group_id mediumint(8) ...,               <--- mediumint

CREATE TABLE answer_group (
  answer_group_id` tinyint(3)  ...,               <--- tinyint

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

ON org_survey . org_survey_code 
   = response . org_survey_code

CREATE TABLE response (
  ...
  org_survey_code varchar(7) NOT NULL,               <--- 7

CREATE TABLE org_survey (
  ...
  org_survey_code varchar(10) NOT NULL default '',   <--- 10

数据类型 mediumint 与 tinyint 不同，varchar(7) 和 varchar(10)。当它们用于连接时，MySQL 不得不浪费时间进行从一种类型到另一种类型的转换。转换其中之一，使它们具有相同的数据类型。这不是查询的主要问题，但此更改也将有助于使用这些连接的所有其他查询。

进行此更改后，对表格执行“分析表格”。有助于mysql制定更好的执行计划。

您有一个 response_answer = 5 条件，其中 response_answer 是 text。这不是错误，但最好使用response_answer = '5'(5到'5'的转换将由MySQL完成无论如何，如果你不这样做)。

真正的问题是您在 WHERE 条件中使用的 3 个字段上没有复合索引。尝试添加这个:

ALTER TABLE response 
  ADD INDEX ind_u1_ra1_aa
      (uuid(1), response_answer(1), added_at) ;

(这可能需要一段时间，因为你的 table 不小)

关于用于极慢查询的 MySQL 索引，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/6851025/