为最近的事件组优化大型MySQL查询(73MM行)
我试图抓住每个“主角”的最新事件。我创建了索引,这个查询仍然需要30分钟以上。为最近的事件组优化大型MySQL查询(73MM行)
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
文艺青年最爱的你需要一个复合(多列)索引。
专业提示:除非你知道你需要它们,否则不要创建大量的单列索引。他们在复杂的查询中很少帮助,并且会降低插入和更新的速度。
您已经完成了使用子查询为要获取的行分配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/是这类工作的一个很好的参考。
这是一个棘手的查询来优化,因为你有你的子查询条件。作为一般规则,您可以使用索引来优化某些条件,但只能使用一个范围谓词或GROUP BY或ORDER BY。
但是你有两个范围谓词和一个GROUP BY:
l.lead_date >= DATE_SUB(NOW(), INTERVAL 52 WEEK)
l.state NOT IN ('NY','AR','VT','WV','GA','CT','DC','SD')
GROUP BY l.home_number
您可以使用lead_date
索引来缩小行选择。您可以使用state
上的索引来缩小行选择的范围。或者,您可以使用索引帮助按组顺序读取查询,并尝试避免临时表。 但是,您只能在给定查询中进行以下三种优化中的一种。
然后诀窍就是选择哪一个你会优先考虑。归根结底,考虑到您拥有的数据分布,每个人都能提高您的查询效率。这取决于你的数据,这不是我们可以回答的问题。因此,您必须使用EXPLAIN测试所有三种情况,或者只需运行带查询的查询以查看它有多大帮助。
通常,使用缩小到行的最小子集的范围谓词。然后,即使另一个范围谓词和GROUP BY必须在没有索引帮助的情况下工作,他们只需要处理更小的一组行,因此总成本不会太差(希望)。
谢谢@ Bill Karwin。我从前面的建议中运行了两个测试,并且还会添加您的尝试建议,只在子查询中选择一个谓词,并将其他两个移到外部查询。今天晚些时候我会报告结果。 – fcol
我没有建议将谓词移动到外部查询。您仍然可以在子查询中找到它们,但它们不会受到索引的帮助。 –
我要走出去,对数据做一些假设。
SELECT l.id, l.home_number, l.mobile_number,
(l.soldprice < 2) as sold,
l.lead_date
FROM
(
SELECT l.home_number, MAX(l.id) as maxid
FROM lead l
GROUP BY l.home_number
) a
JOIN lead l ON l.id = a.maxid;
WHERE l.lead_date >= DATE_SUB(NOW(), INTERVAL 52 WEEK)
AND l.state NOT IN ('NY','AR','VT', 'WV','GA','CT','DC', 'SD')
,并有
INDEX(home_number, id)
假设:
- 子查询将是相当快的,多亏了指数。
- 优化器将在查看
WHERE
之前运行子查询。 (如果失败,将其更改为HAVING
) - 的MAX(ID)和“在去年”被大量相关
- 每个
home_number
是针对特定的state
。
让我们知道这是否得到相同的结果,但更快。
'home_number'和'state'之间有关系吗?例如,“212 -...”总是纽约的电话号码;我可能会利用这一点。 –