如何加速含HAVING子句的百亿级数据表查询？

问题背景 #

我有一张近百亿条记录的表，执行包含HAVING子句的查询时速度极慢（性能较好的硬件上耗时约15分钟），该如何优化？

原查询SQL：

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执行计划 #

优化方案 #

1. 重写HAVING条件为WHERE条件，提前过滤数据 #

HAVING是对查询结果集做过滤，而这里的t是基于单行字段计算的值，并非聚合结果，完全可以将过滤逻辑移到WHERE阶段，减少后续关联和计算的数据量。

首先对t>=1.8的公式做等价变形（注意stddev为标准差，不可能为负，且N>=300保证sqrt(N)有意义）：

((mean - 3.0E-4)/(stddev/sqrt(N))) >= 1.8
=> mean - 3.0E-4 >= 1.8 * (stddev / sqrt(N))
=> mean >= 3.0E-4 + (1.8 * stddev) / SQRT(N)

同时添加stddev > 0避免除零错误。

重写后的SQL：

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

2. 创建覆盖索引，避免回表查询 #

当前索引ti4仅包含过滤条件字段，查询时需要回表获取mean、stddev、kurtosis等计算和返回字段。创建包含所有过滤、计算、返回字段的覆盖索引，让数据库直接从索引中获取所需数据：

-- MySQL 8.0+支持INCLUDE，不支持的话直接把字段加到索引列后
CREATE INDEX ti_opt ON ttest (dataset, patternclassid, exitclassid, N) 
INCLUDE (strategyid, mean, stddev, kurtosis);

3. 消除隐式类型转换 #

原SQL中patternclassid="1"、exitclassid="1"是字符串转整数的隐式转换，会导致索引无法高效匹配。改成整数匹配：patternclassid=1、exitclassid=1，确保索引能被正常使用。

4. 分区或分库分表优化 #

针对百亿级数据量，单表优化的上限有限：

• 分区：按dataset字段做RANGE分区，查询dataset=3时仅扫描对应分区，大幅减少数据扫描范围。
• 分库分表：如果分区仍无法满足性能要求，可按strategyid或dataset做分片，将数据分散到多个实例或表中。

5. 调整数据库优化参数 #

根据硬件配置调整以下参数，适配大数据量查询：

• 增大join_buffer_size、sort_buffer_size，提升关联和排序性能；
• 确认optimizer_switch='mrr=on,mrr_cost_based=off'，强制启用MRR优化（从EXPLAIN看已启用，但可确保参数配置正确）。

内容的提问来源于stack exchange，提问作者Markiemark