mysql - 加快 MYSQL 中的自连接

Question

我有一个包含约 280 万行、3 列的表格。每行代表公司的营销接触，有“customer_id”、“marketing_type”和“week_num”。“customer_id”有一个索引，“marketing_type”有另一个索引

数据示例:

72, catalog,  7
72, email,    3
99, catalog, 13
82, catalog,  7

我需要一个包含所有具有电子邮件但没有目录的 customer_id 的列表。 (还有其他类型的营销，也有 customer_id 什么都没得到)

第一次尝试:

SELECT DISTINCT cust_id
FROM marketing_campaign
WHERE marketing_type = 'email'
AND cust_id NOT IN (
 SELECT cust_id
 FROM marketing_campagin
 WHERE marketing_type = 'catalog'
 )
;

此查询需要 30 多分钟才能运行

---

第二次尝试:

SELECT m1.cust_id 
FROM marketing_campaign m1
LEFT OUTER JOIN marketing_campaign m2 
  ON m1.cust_id = m2.cust_id 
 AND m2.MARKETING_TYPE = 'catalog'
WHERE m1.MARKETING_TYPE = 'email'
 AND m2.cust_id IS NULL
;

此查询在 3.8 秒内执行，但提取时间超过 30 分钟。

---

第三次尝试:

SELECT distinct cust_id
FROM   marketing_campaign a
WHERE  MARKETING_TYPE = 'email'
  AND  NOT EXISTS (
           SELECT 'X'
           FROM   marketing_campaign b
           WHERE  a.cust_id = b.cust_id
           AND    MARKETING_TYPE = 'catalog' 
           )
ORDER BY cust_id
;

此查询也在 <5 秒内执行，但随后需要 20 分钟以上的时间进行提取。

---

谁能提出替代方案？

Answer 1

不要忽视复合索引:

ALTER TABLE marketing_campaign ADD KEY (marketing_type, cust_id);

然后使用查询#2。

还要确保您已将缓冲区调整得足够大，以便索引驻留在 RAM 中。