如何加快包含HAVING的查詢？

我有一個包含近十億條記錄的表，需要使用HAVING. 它非常慢（在體面的硬體上大約需要 15 分鐘）。如何加快速度？

SELECT ((mean - 3.0E-4)/(stddev/sqrt(N))) as t, ttest.strategyid, mean, stddev, N, 
  kurtosis, strategies.strategyId 
FROM ttest,strategies 
WHERE ttest.strategyid=strategies.id AND dataset=3 AND patternclassid="1" 
  AND exitclassid="1" AND N>= 300 HAVING t>=1.8

我認為問題是t無法索引，因為它需要計算。我無法將其添加為列，因為“3.0E-4”會因查詢而異。

桌子：

create table ttest (
  strategyid bigint,
  patternclassid integer not null,
  exitclassid integer not null,
  dataset integer not null,
  N integer,
  mean double,
  stddev double,
  skewness double,
  kurtosis double,

  primary key (strategyid, dataset)
);
create index ti3 on ttest (mean);
create index ti4 on ttest (dataset,patternclassid,exitclassid,N);

create table strategies (
  id bigint ,
  strategyId varchar(500),

  primary key(id),
  unique key(strategyId)
);

explain select..：

uj5u.com熱心網友回復：

查詢需要重新制定，并且需要添加索引。

計劃A：

SELECT  ((tt.mean - 3.0E-4)/(tt.stddev/sqrt(tt.N))) as t,
        tt.strategyid, tt.mean, tt.stddev, tt.N, tt.kurtosis,
        s.strategyId
    FROM  ttest AS tt
    JOIN  strategies AS s  ON tt.strategyid = s.id
    WHERE  tt.dataset = 3
      AND  tt.patternclassid = 1
      AND  tt.exitclassid = 1
      AND  tt.N >= 300
      AND  ((tt.mean - 3.0E-4)/(tt.stddev/sqrt(tt.N))) >= 1.8

以及測驗中的“復合”和“覆寫”指數。用這個替換你ti4的（使其“覆寫”）：

INDEX(dataset, patternclassid, exitclassid,  -- any order
      N, strategyid)     -- in this order

B計劃：

SELECT  ((tt.mean - 3.0E-4)/(tt.stddev/sqrt(tt.N))) as t,
        tt.strategyid, tt.mean, tt.stddev, tt.N, tt.kurtosis,
        ( SELECT s.strategyId 
              FROM strategies AS s
              WHERE s.id = tt.strategyid = s.id
        ) AS strategyId
    FROM  ttest AS tt
    WHERE  tt.dataset = 3
      AND  tt.patternclassid = 1
      AND  tt.exitclassid = 1
      AND  tt.N >= 300
      AND  ((tt.mean - 3.0E-4)/(tt.stddev/sqrt(tt.N))) >= 1.8

具有相同的索引。

不幸的是， for 的運算式t需要重復。通過將其從 移動HAVING到WHERE，避免收集不需要的行，最終將它們扔掉。 也許優化器會自動執行此操作。請提供EXPLAIN SELECT ...查看。

此外，尚不清楚這兩種配方中的一種是否會比另一種運行得更快。

uj5u.com熱心網友回復：

老實說，我從未見過HAVING被這樣使用；20 多年以來，我一直認為它只能在某些GROUP BY情況下使用！

無論如何，恕我直言，您在這里不需要它，正如 Rick James 指出的那樣，您可以將它全部放在WHERE. 稍微重寫一下，我最終得到：

SELECT ((t.mean - 3.0E-4)/(t.stddev/sqrt(t.N))) as t, 
       t.strategyid, 
       t.mean, 
       t.stddev, 
       t.N, 
       t.kurtosis, 
       s.strategyId 
 FROM ttest t,
 JOIN strategies s
   ON s.id = t.strategyid =  
WHERE t.dataset=3 
  AND t.patternclassid="1" 
  AND t.exitclassid="1" 
  AND t.N>= 300 
  AND ((t.mean - 3.0E-4)/(t.stddev/sqrt(t.N))) >= 1.8
  

其中大部分我們確實可以預見一個合理的指數。最后一個計算問題仍然存在：

  AND ((t.mean - 3.0E-4)/(t.stddev/sqrt(t.N))) >= 1.8

然而，在我們開始之前：如果你忽略這個“公式”，有多少行？100？200？如果是這樣，那么在 Rick James 的回答中預見的索引應該是足夠的恕我直言。如果它是 1000 或更多，那么問題就變成了：公式拋出了多少？1%？50% 99%？如果它再次處于低位，那么 Rick James 提出的索引就可以了。但是，如果您只需要保留一些，您可能需要進一步優化它并相應地索引。根據您的解釋，我知道這3.0E-4是可變的，因此我們無法將其包含在索引中..所以我們需要提取我們可以提取的部分：

如果我的代數沒有讓我失望，你可以使用這樣的公式：

 AND ((t.mean - 3.0E-4) / (t.stddev / sqrt(t.N))) >= 1.8
 AND ((t.mean - 3.0E-4) ) >=  1.8 * (t.stddev / sqrt(t.N))
 AND   t.mean - 3.0E-4    >= (1.8 * (t.stddev / sqrt(t.N)))
 AND          - 3.0E-4    >= (1.8 * (t.stddev / sqrt(t.N))) - t.mean 
 

所以查詢變為：

SELECT ((t.mean - 3.0E-4)/(t.stddev/sqrt(t.N))) as t, 
       t.strategyid, 
       t.mean, 
       t.stddev, 
       t.N, 
       t.kurtosis, 
       s.strategyId 
 FROM ttest t,
 JOIN strategies s
   ON s.id = t.strategyid =  
WHERE t.dataset=3 
  AND t.patternclassid="1" 
  AND t.exitclassid="1" 
  AND t.N>= 300 
  AND (1.8 * (t.stddev / sqrt(t.N))) - t.mean <= -3.0E-4    

我不熟悉 mysql 但瀏覽檔案應該可以在索引中包含“生成的列”。因此，我們將使用(1.8 * (t.stddev / sqrt(t.N)) - t.mean).

您的索引欄位因此變為：

dataset, paternclassid, exitclassid, N, (1.8 * (t.stddev / sqrt(t.N))) - t.mean)

請注意，系統將必須為您在表上執行的插入（并可能更新）每一行計算此值。但是，一旦出現（并被索引），它應該會使查詢更快一些。

標籤：mysql表现查询优化有

上一篇：與模式匹配相比，“Option.bind”是否有性能成本？

下一篇：自加入需要時間