mysql - 针对最近的事件组优化大型 MySQL 查询(73MM 行)

Question

我正在尝试获取每个“线索”的最新事件。我已经创建了索引，但这个查询仍然需要 30 多分钟。

SELECT  l.id,
        l.home_number,
        l.mobile_number,
        CASE WHEN l.soldprice < 2 THEN 0 ELSE 1 END as sold,
        l.lead_date
FROM (
    SELECT  l.home_number, MAX(l.id) as id
    FROM lead l
    WHERE l.lead_date >= DATE_SUB(NOW(), INTERVAL 52 WEEK)
    AND l.state NOT IN ('NY','AR','VT','WV','GA','CT','DC','SD')
    GROUP BY l.home_number) a 
JOIN lead l ON l.id=a.id;

我的表索引如下:

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_typ    
lead    0   PRIMARY     1   id          A   63123648    NULL    NULL        BTREE       
lead    1   id          1   id          A   63266540    NULL    NULL        BTREE       
lead    1   soldprice   1   soldprice   A   14715       NULL    NULL    YES BTREE       
lead    1   lead_date   1   lead_date   A   15351477    NULL    NULL    YES BTREE

还有我的表架构:

CREATE TABLE lead
( 
  id                BIGINT unsigned NOT NULL, 
  lead_date         DATETIME NULL,
  first_name        VARCHAR(50) NULL,
  last_name         VARCHAR(50) NULL,
  hashed_ssn        VARCHAR(34) NULL,
  city              VARCHAR(50) NULL,
  state             VARCHAR(2) NULL,
  home_number       VARCHAR(10) NULL,
  mobile_number     VARCHAR(10) NULL,
  email             VARCHAR(255) NULL,
  soldprice         DECIMAL(5,2) NULL,
  requested_amount  INT NULL,
  time_zone         VARCHAR(5),
  camp_id           VARCHAR(9),
  leadtype_id       VARCHAR(3),
  hittype_id        VARCHAR(3),
  PRIMARY KEY       (id)                           
);

如有任何建议，我们将不胜感激。

编辑:我正在使用 MySQL 版本 5.7.19-0ubuntu0.16.04.1

Answer 1

Tl;dr 您需要一个复合(多列)索引。

专业提示:不要创建大量单列索引，除非您知道需要它们。它们在复杂查询中几乎没有多大帮助，并且会减慢插入和更新速度。

您已经很好地使用子查询来筛选要获取的行的 id 值。不过，肯定大部分时间都会进入你的子查询，这:

SELECT  l.home_number, MAX(l.id) as id
FROM lead l
WHERE l.lead_date >= DATE_SUB(NOW(), INTERVAL 52 WEEK)
AND l.state NOT IN ('NY','AR','VT','WV','GA','CT','DC','SD')
GROUP BY l.home_number

调试子查询，然后将它们加入主查询通常是明智的做法。

要做的第一件事是:在 (lead_date, home_number, id) 上创建复合索引。然后运行这个简化的子查询，忽略状态的排除。这应该很快，因为它可以随机访问日期，然后使用索引来处理分组，并使用松散索引扫描来获取最大 id 值。

SELECT  l.home_number, MAX(l.id) as id
FROM lead l
WHERE l.lead_date >= DATE_SUB(NOW(), INTERVAL 52 WEEK)
GROUP BY l.home_number

接下来，尝试在 (lead_date, state, home_number, id) 上创建复合索引并尝试原始查询。如果速度相当快，那么你就完成了。您的查询会快得多。删除第一个复合索引。

但也可能不是，因为 MySQL 并不能很好地处理大容量的 NOT IN 子句。

在这种情况下，保留第一个复合索引并删除第二个复合索引，并将状态排除移至外部查询。

看起来像这样:

SELECT  l.id,
        l.home_number,
        l.mobile_number,
        CASE WHEN l.soldprice < 2 THEN 0 ELSE 1 END as sold,
        l.lead_date
FROM (
    SELECT  l.home_number, MAX(l.id) as id
    FROM lead l
    WHERE l.lead_date >= DATE_SUB(NOW(), INTERVAL 52 WEEK)
    GROUP BY l.home_number) a 
JOIN lead l ON l.id=a.id
WHERE l.state NOT IN ('NY','AR','VT','WV','GA','CT','DC','SD')

这应该有帮助。

http://use-the-index-luke.com/对于此类工作来说是一个很好的引用。