你能索引子查询吗?

本教程将介绍你能索引子查询吗?的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

你能索引子查询吗? 教程 第1张

问题描述

我有一个表格和一个查询,如下所示.有关工作示例,请参阅此 SQL Fiddle.

SELECT o.property_B, SUM(o.score1), w.score
FROM o
INNER JOIN 
(
 SELECT o.property_B, SUM(o.score2) AS score FROM o GROUP BY property_B
) w ON w.property_B = o.property_B
WHERE o.property_A = 'specific_A'
GROUP BY property_B;

使用我的真实数据,这个查询需要 27 秒.但是,如果我首先将 w 创建为临时表和索引 property_B,则总共需要大约 1 秒.

CREATE TEMPORARY TABLE w AS
SELECT o.property_B, SUM(o.score2) AS score FROM o GROUP BY property_B;

ALTER TABLE w ADD INDEX `property_B_idx` (property_B);

SELECT o.property_B, SUM(o.score1), w.score
FROM o
INNER JOIN w ON w.property_B = o.property_B
WHERE o.property_A = 'specific_A'
GROUP BY property_B;

DROP TABLE IF EXISTS w;

有什么一种方法可以结合这两个查询中最好的查询?IE.单个查询具有子查询中索引的速度优势?

在下面 Mehran 的回答之后,我在 MySQL 文档:

从 MySQL 5.6.3 开始,优化器更有效地处理 FROM 子句中的子查询(即派生表):

...

对于 FROM 子句中的子查询需要物化的情况,优化器可以通过向物化表添加索引来加快对结果的访问.如果这样的索引允许对表进行 ref 访问,它可以大大减少在查询执行期间必须读取的数据量.考虑以下查询:

SELECT * FROM t1
  JOIN (SELECT * FROM t2) AS derived_t2 ON t1.f1=derived_t2.f1;

如果这样做允许使用 ref 访问以实现最低成本的执行计划,则优化器会在从 derived_t2 的列 f1 上构造一个索引.添加索引后,优化器可以将物化派生表视为具有索引的普通表,并且它同样受益于生成的索引.与没有索引的查询执行成本相比,索引创建的开销可以忽略不计.如果 ref 访问会导致比其他访问方法更高的成本,则不会创建索引并且优化器不会丢失任何内容.

推荐答案

首先你要知道,创建临时表绝对是一个可行的方案.但在某些情况下,没有其他选择适用,这在此处不正确!

在您的情况下,您可以像 指出的那样轻松提升您的查询,因为您的子查询和主查询都按同一字段分组.所以你不需要任何子查询.为了完整起见,我将复制并粘贴 FrankPl 的解决方案:

SELECT o.property_B, SUM(o.score1), SUM(o.score2)
FROM o
GROUP BY property_B;

但这并不意味着不可能遇到您希望可以索引子查询的情况.在这种情况下,您有两种选择,首先是使用您自己指出的临时表,保存子查询的结果.这个解决方案是有优势的,因为它被 MySQL 支持了很长时间.如果涉及大量数据,这是不可行的.

第二种解决方案是使用MySQL 5.6 或更高版本.在最新版本的 MySQL 中,新算法被合并,因此在子查询中使用的表上定义的索引也可以在子查询之外使用.

[更新]

对于问题的编辑版本,我会推荐以下解决方案:

SELECT o.property_B, SUM(IF(o.property_A = 'specific_A', o.score1, 0)), SUM(o.score2)
FROM o
GROUP BY property_B
HAVING SUM(IF(o.property_A = 'specific_A', o.score1, 0)) > 0;

但是您需要处理 HAVING 部分.您可能需要根据您的实际问题进行更改.

好了关于你能索引子查询吗?的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。