MySql联合查询很慢 - 如何提高速度

Question

我需要从我们的MySQL数据库中导出554k条记录。按照当前速度，导出数据需要5天时间，而且主要是由下面的查询引起的缓慢。数据结构包括：

Companies
--Contacts
----(Contact)Activities

对于联系人，我们在company_id上建立了索引。在活动表中，我们为contact_id和company_id建立了索引，这些索引映射回相应的联系人和公司表。

我需要获取每个联系人及其最新的活动日期。这是我正在运行的查询，执行时间约为0.5秒。

Select * 
from contacts 
left outer join (select  occurred_at
                        ,contact_id 
                 from activities 
                 where occurred_at is not null 
                 group by contact_id 
                 order by occurred_at desc) activities 
on contacts.id = activities.contact_id 
where company_id = 20

如果我去掉连接(join)并只查询公司ID为20的联系人，查询将在0.016秒内执行。

如果我使用Explain来获取关于连接查询的信息，我会得到这个: 你有什么想法可以加快查询速度吗？

编辑： 以下是表定义。

CREATE TABLE `companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `street_address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `county` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `website` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `external_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `falloff_date` date DEFAULT NULL,
  `zipcode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `order_count` int(11) NOT NULL DEFAULT '0',
  `active_job_count` int(11) NOT NULL DEFAULT '0',
  `duplicate_of` int(11) DEFAULT NULL,
  `warm_date` datetime DEFAULT NULL,
  `employee_size` int(11) DEFAULT NULL,
  `dup_checked` tinyint(1) DEFAULT '0',
  `rating` int(11) DEFAULT NULL,
  `delinquent` tinyint(1) DEFAULT '0',
  `cconly` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_companies_on_name` (`name`),
  KEY `index_companies_on_user_id` (`user_id`),
  KEY `index_companies_on_company_id` (`company_id`),
  KEY `index_companies_on_external_id` (`external_id`),
  KEY `index_companies_on_state_and_dup_checked` (`id`,`state`,`dup_checked`,`duplicate_of`),
  KEY `index_companies_on_dup_checked` (`id`,`dup_checked`),
  KEY `index_companies_on_dup_checked_name` (`dup_checked`,`name`),
  KEY `index_companies_on_county` (`county`,`state`)
) ENGINE=InnoDB AUTO_INCREMENT=15190300 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `extension` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fax` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `active` tinyint(1) DEFAULT NULL,
  `main` tinyint(1) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `external_id` int(11) DEFAULT NULL,
  `second_phone` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_contacts_on_company_id` (`company_id`),
  KEY `index_contacts_on_first_name` (`first_name`),
  KEY `index_contacts_on_last_name` (`last_name`),
  KEY `index_contacts_on_phone` (`phone`),
  KEY `index_contacts_on_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=11241088 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `activities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kind` int(11) DEFAULT NULL,
  `contact_id` int(11) DEFAULT NULL,
  `call_status` int(11) DEFAULT NULL,
  `occurred_at` datetime DEFAULT NULL,
  `notes` text COLLATE utf8_unicode_ci,
  `user_id` int(11) DEFAULT NULL,
  `scheduled_for` datetime DEFAULT NULL,
  `priority` tinyint(1) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `from_user_id` int(11) DEFAULT NULL,
  `to_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_activities_on_contact_id` (`contact_id`),
  KEY `index_activities_on_user_id` (`user_id`),
  KEY `index_activities_on_company_id` (`company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=515340 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

- geoff swartz

---

你可以先从内部的 select 中删除 order by。它在那里是相当无用的。一些数据库引擎甚至不允许在内部查询中使用 order by。 - mypetlion

mypetition，我需要最新的活动日期，所以我需要在那里排序。Biansor，是的，它具有我在初始帖子中提到的索引。 - geoff swartz

表定义已添加。 - geoff swartz

获取最新活动日期只需使用MAX(occurred_at)。 - dnoeth

你正在使用DBMS，编写一个单一的查询以获取所有公司的数据。 - dnoeth

Answer 1

这是一个关于分组中最大的n个元素的常见编程问题，下面是一种使用MySQL 8.0窗口函数的解决方案：

WITH latest_activities AS (
  SELECT contact_id, occurred_at,
    ROW_NUMBER() OVER (PARTITION BY contact_id ORDER BY occurred_at DESC) AS rn
  FROM activities
)
SELECT *
FROM contacts AS c
LEFT OUTER JOIN latest_activities 
  ON c.id = latest_activities.contact_id AND latest_activities.rn = 1
WHERE c.company_id = 20

这是一个适用于8.0版本之前的解决方案：

SELECT c.*, a.*
FROM contacts AS c
LEFT OUTER JOIN activities AS a ON a.contact_id = c.id
LEFT OUTER JOIN activities AS a2 ON a2.contact_id = c.id 
  AND a2.occurred_at > a.occurred_at
WHERE c.company_id = 20
  AND a2.contact_id IS NULL;

另一种解决方案：

SELECT c.*, a.*
FROM contacts AS c
LEFT OUTER JOIN activities AS a ON a.contact_id = c.id
LEFT OUTER JOIN (
  SELECT c2.contact_id, MAX(a2.occurred_at) AS occurred_at
  FROM activities AS a2
  INNER JOIN contacts AS c2 ON a2.contact_id = c2.id
  WHERE c2.company_id = 20 
  GROUP BY c2.contact_id ORDER BY NULL
) AS latest_activities
  ON latest_activities.contact_id = c.id
  AND latest_activities.occurred_at = a.occurred_at
WHERE c.company_id = 20

创建一个新的索引在活动表上，包括(contact_id, occurred_at)。

- Bill Karwin

---

谢谢。在你的第二个解决方案中，它以a2.contact_id为空结束。我不理解那部分。你能解释一下吗？ - geoff swartz

a2 是“发生在 a 之后的活动”，这个最终条件会除去任何后面还有活动的结果。 - Uueerdo

对的，外连接不应该匹配任何具有相同联系人和更晚活动日期的行'a2'。我们只想要'a'是最新活动的情况，这意味着外连接将找不到任何稍后的情况。 - Bill Karwin

Answer 2

如果可以避免，请不要在FROM子句中使用子查询。它们会影响MySQL优化器的性能。因此，如果您只需要一行：

Select c.*, a.occurred_at
from contacts c left outer join
     from activities a
     on c.id = a.contact_id and
        a.occurred_at is not null 
where c.company_id = 20
order by a.occurred_at desc
limit 1;

如果您想要每个contact_id一行：

Select c.*, a.occurred_at
from contacts c left outer join
     from activities a
     on c.id = a.contact_id and
        a.occurred_at is not null and
        a.occurred_at = (select max(a2.occurred_at)
                         from activities a2
                         where a2.contact_id = a.contact_id
                        )
where c.company_id = 20         
order by a.occurred_at desc
limit 1;

这可以利用索引activities(contact_id, occured_at)和contact(company_id, contact_id)。

您的查询正在做一件明显不好的事情 - 并且在最新版本的MySQL的默认设置中不再受支持。您在select中有未聚合的列，这些列不在group by中。 contact_id应该会生成一个错误。

- Gordon Linoff

Answer 3

我觉得其他答案都很复杂，但我认为这就是你需要的。

SELECT c.*
   , MAX(a.occurred_at) AS occurred_at
FROM contacts AS c
LEFT JOIN activities AS a
   ON c.id = a.contact_id AND a.occurred_at IS NOT NULL
WHERE c.company_id = 20
GROUP BY c.id;

备注：（1）这假定您实际上并不想要原子查询中重复的contact_id出现在最终结果中。(2) 这还假定您的服务器未配置为需要完整分组; 如果是这样，您需要手动展开c.*成完整的列列表，并将该列表复制到GROUP BY子句中。

---

针对dnoeth在您的问题中的评论进行扩展; 如果您没有出于特定原因单独查询每个公司（为了负载块，代码结构处理这也处理其他公司的东西，等等），您可以像这样调整上面的查询以在一个查询中获取所有结果。

SELECT con.*
   , MAX(a.occurred_at) AS occurred_at
FROM companies AS com 
INNER JOIN contacts AS con ON com.id = con.company_id
LEFT JOIN activities AS a
   ON con.id = a.contact_id AND a.occurred_at IS NOT NULL
WHERE [criteria for companies chosen to be queried]
GROUP BY con.id
ORDER BY con.company_id, con.id
;

- Uueerdo

---

这样做无法获取最新活动中的任何其他列，只能获取最新活动的日期。如果尝试包含 a.*，它不一定会返回找到最新日期的行中的值。 - Bill Karwin

@BillKarwin 他不是只在寻找最后一次活动日期吗？ - Uueerdo

原始查询使用了 Select *，因此我假设可能需要有关各自最新活动的详细信息。 - Bill Karwin

啊，是的，在那个select *中包含的子查询只会返回每个contact_id和contact_id中最新的occurred_by，而在*中是多余的（或者至少是打算返回最新的occurred_by）。 - Uueerdo