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

如果我删除连接并仅从 company_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;

Answer 1

这是一个 greatest-n-per-group查询，在 Stack Overflow 上经常出现。

这是一个使用 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)会很有帮助。