MySql 慢速查询，在两个表上使用一个连接和条件

Question

我不明白为什么以下查询如此慢:

select er.Id 
from employeeRequests er
inner join employees e on e.id = er.idEmployee
where er.DateTime1 >= '2017-03-11'
and er.DateTime1 <= '2018-06-10'
and er.state = 0 and e.idCompany = 37;

两个表都是 InnoDB。“employeerequests”表有 200.000 条记录。“员工”表有 10.000。在快速机器上，执行查询大约需要 4 秒。

EXPLAIN 返回以下行:

select_type    table    type    possible_keys                         key                                  key_len    ref         rows    filtered    Extra
SIMPLE         e        ref     PRIMARY,FK_employee_idCompany_idx     FK_employee_idCompany_idx            8          const       211     100.00      Using index
SIMPLE         er       ref     FK_employeeRequest_IdEmployee_idx     FK_employeeRequest_IdEmployee_idx    8          db.e.id     77      1.11        Using where

我知道第二行的“filtered = 1.11”可能是问题所在，但我不知道如何解决它。

如果我删除连接并将其替换为“er.idEmployee in (1,2,...)”之类的条件，查询会变得非常快，但我不喜欢这个解决方案，无论如何，我会我想了解为什么我无法通过连接获得相同的结果。

这些是包含所有相关字段的 CREATE TABLE 语句:

CREATE TABLE `employeerequests` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dateTime1` datetime NOT NULL DEFAULT '1899-12-31 00:00:00',
  `idEmployee` bigint(20) NOT NULL DEFAULT '0',
  `state` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `FK_employeeRequest_IdEmployee_idx` (`idEmployee`),
  CONSTRAINT `FK_employeeRequest_IdEmployee` FOREIGN KEY (`idEmployee`) REFERENCES `employees` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE TABLE `employees` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `idCompany` bigint(20) NOT NULL DEFAULT '0',
  `firstName` varchar(100) NOT NULL DEFAULT '',
  `lastName` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `FK_employee_idCompany_idx` (`idCompany`),
  CONSTRAINT `FK_employee_idCompany` FOREIGN KEY (`idCompany`) REFERENCES `companies` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

Answer 1

尝试这两个索引。

create index idx_id_company
on employees (idcompany);

create index idx_employeerequests_emp_st_dt
on employeerequests (idEmployee, state, DateTime1);

第一个将加快通过 idcompany 搜索员工的速度。第二个将获取员工 ID 并深入研究员工请求，然后按州和 DateTime1 进行过滤。
您可以尝试在第二个索引中切换 state 和 idEmployee ，看看是否有助于加快速度。
不要自作聪明，将 DateTime1 放在索引的开头。它只会损害你的查询。

与一般建议相反，在为复合查询创建索引时，最好将第一列放在选择性不太好的地方。我最喜欢的是带有 1/0 的列。它们只是将您的数据分成两半，优化器通常会选择这样的索引，即使对于第一列不存在的查询也是如此。

到解释部分。当您在单个表上运行查询时，数据库引擎只会读取所有记录并过滤掉那些不匹配的记录。它很慢，但对于合理少量的记录，您不会知道其中的差异。
但是，当您加入另一个表时，它必须读取一个表中的所有行，并为每一行在第二个表中查找。但是因为您没有索引，所以它必须从第一个表中读取整个第二个表的每条记录。因此，将针对单个表运行查询时所花费的时间乘以第二个表中的行，即可得到结果时间。
事实上事情并没有那么简单。这只是简化的示例。