我无法提出(或研究(这个问题的解决方案这一事实意味着我要么太愚蠢而无法阅读文档,要么实际上是一个复杂的问题。
在一个相当大的数据库中,我经常需要这样的查询:
SELECT ... WHERE condition GROUP BY something;
这需要几分之一秒才能完成。所以我把它放在一个视图中:
CREATE VIEW view_x AS SELECT ... GROUP BY something;
当我这样做时
SELECT * FROM view_x WHERE condition;
完成需要一分多钟。现在很容易理解原因:在普通 SELECT 中,数据库引擎首先从数百万条记录中选择几百个结果,然后仅对匹配的记录进行聚合和分组。使用视图时,它似乎首先评估整个数据集,聚合和分组所有内容,然后仅返回满足条件的记录,并丢弃计算成本高昂的其余部分。
是否有更智能的 VIEW 解决方案,还是我每次都必须使用完整的 SELECT?
谢谢。
编辑:这是视图的原始SQL代码:
CREATE VIEW v_status1 AS SELECT
FROM_UNIXTIME(J.ts_start) AS job_start,
J.id AS job_id, J.carrier, J.n_wafers,
count(W.id) AS n
FROM job AS J
JOIN wafer AS W ON J.id=W.job_id
GROUP BY J.carrier, J.n_wafers, W.status_id;
表作业:100k记录,表晶圆:2M记录。
比较是这些查询之间的:
SELECT * FROM v_status1 WHERE carrier LIKE 'W96L00%'; -- very slow
与 VIEW 定义中相同的 SELECT 相比,WHERE 子句在 GROUP BY 子句之前。
一些附加信息:查询生成 9 条记录。使用该视图需要 19 秒才能执行。使用直接查询,根据MySQL工作台,需要0.000秒。
当我在查询结束时将直接查询中的 WHERE 子句替换为具有相同条件的 HAVING 子句时,我最终与使用该视图的查询的执行时间相同。
是的,我忘记了分组依据部分中的一些列。 把它们放进去,没有太大区别。
最小示例(5 秒执行时间(:
CREATE VIEW v_status2 AS SELECT
job_id,
status_id,
count(id) AS n
FROM wafer
GROUP BY job_id, status_id;
给出一些job_id,产生 2 条记录
好吧,我做了显而易见的事情,并要求MySQL解释。输出如下。我的解释是我一直怀疑的:MySQL 首先构建一个临时表,完成所有艰苦的聚合和分组工作,然后只选择符合选择条件的行。换句话说,MySQL不够智能,无法首先分析视图以找到可以有效地剔除原始数据集并仅处理剩余记录的位置。
顺便说一句,这与连接和索引无关。您可以看到任何足够大的两列表的效果。
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 952929 Using where
2 DERIVED WS index PRIMARY ix_waferstatus_text 123 NULL 9 Using index; Using temporary; Using filesort
2 DERIVED W ref ix_wafer_job_id,wafer_ibfk_2 wafer_ibfk_2 5 jobwatch.WS.id 105881 Using where
2 DERIVED J eq_ref PRIMARY,job_ibkf_2 PRIMARY 4 jobwatch.W.job_id 1 Using where
2 DERIVED T eq_ref PRIMARY PRIMARY 4 jobwatch.J.tool_id 1