SQL查询执行速度非常慢:需要优化

Question

下面的执行速度非常慢，需要很长时间才能执行。即使结果集为空，也需要大约280秒。查询中有几个case语句。对优化查询的一些帮助将不胜感激。

执行计划：计划取决于输入的日期变量，where子句中的日期范围字段将发生更改。例如：如果日期="1"，则将基于.created_date查询日期范围；如果日期=‘2’，则将根据t.active_datetime 查询日期范围

SQL查询：

SELECT  @n:=@n+1 No,  a.name customer_name, a.id_number AS customer_id, a.email AS email_address, CONCAT(a.mobile_country_code, '-', a.mobile_number) AS contact_no,
t.status AS status, a.created_date AS registration_date,  a.account_type, a.account_id AS account_no, c.wallet_id AS wallet_account_no, t.old_serial_no AS r_id,
t.active_datetime AS activation_date, v.number AS registration_no,  v.class AS lass,  (CASE WHEN t.is_free = true THEN 'Yes'  ELSE 'No'  END ) AS replacement,
a.reg_source AS channel, (CASE WHEN a.reg_source = 'ADMIN' THEN t.last_modified_by  ELSE 'SYSTEM'  END )  AS activated_by, t.replacement_date AS replacement_date, 
t.new_serial_no AS new_r_id, d.description AS reason, t.terminated_datetime AS tag_termination_date
FROM abc a 
LEFT JOIN def b ON (b.id = a.id) 
LEFT JOIN ghi c ON (c.id = b.id),  tags t
LEFT JOIN jkl v ON (v.id = t.id)
LEFT JOIN mno d ON (d.code = t.reason_code),
(SELECT @n:= 0) AS n , (SELECT @date := '3') AS date
WHERE
a.account_type IN ( 'PRIVATE') AND  a.account_status IN ( 'ACTIVE') AND t.status IN ('ACTIVE') AND
a.reg_source IN ('ADMIN') AND   
CASE 
WHEN @date = '1' THEN a.created_date BETWEEN '2020-11-24'  AND '2020-11-24'
WHEN @date = '2' THEN t.active_datetime BETWEEN '2020-11-24'  AND '2020-11-24'
WHEN @date = '3' THEN t.replacement_date BETWEEN '2020-11-24'  AND '2020-11-24'
WHEN @date = '4' THEN t.terminated_datetime BETWEEN '2020-11-24'  AND '2020-11-24'  
END ;

Answer 1

尝试添加索引以帮助加快搜索速度。

CREATE INDEX abc_for_big_query
ON abc (account_type, account_status, reg_source, created_date);
CREATE INDEX tags_for_big_query_active ON tags (status, active_datetime);
CREATE INDEX tags_for_big_query_replacement ON tags (status, replacement_date );
CREATE INDEX tags_for_big_query_terminated ON tags (status, terminated_datetime );

这些索引将允许查询规划器使用索引范围扫描来执行搜索操作。它们比全表扫描更有效率。

在优化该查询时，可以将其视为四个完全不同的查询，@date变量的每个值对应一个查询。为什么？搜索条件差别很大。

如果您需要绝对最高的性能，您可能需要将其重写为四个不同的查询。但是最近的MySQL查询规划器可能允许您保留这种四向查询。

注释

• 确保日期数据类型为date、DATETIME或TIMESTAMP。如果你将日期存储在文本字符串中，你将很难获得不错的性能。

• 你有这样的where子句：a.created_date BETWEEN '2020-11-24' AND '2020-11-24'。

  这实际上意味着a.created_date = '2020-11-24 00:00:00'。为什么？因为恒定日期2020-11-24实际上扩展为2020-11-24 00:00:00。CCD_ 6表示CCD_。

  如果你想要2020-11-24上任何时候都有created_date的东西，请使用这个

  WHERE a.created_date >= '2020-11-24'
  AND a.created_date <  '2020-11-24' + INTERVAL 1 DAY
  

  这仍然会使用我建议的索引。

Answer 2

@n:=@n+1

在未来的版本中，@variables的使用将被禁止。如果这是一个ROW_NUMBER，那么该函数可能会提供您需要的内容。

您似乎没有使用b；去掉它。

如果"right"表中的行是必需的，则不要使用LEFT。

这可能是速度杀手：

, tags t

不要混合JOIN和,。确保每个JOIN都有一个ON子句。

构造查询，而不是像使用@date那样使其动态。这可以提供显著的加速。之后，添加以下索引：

a: INDEX(account_type, account_status, reg_source, created_date)
t: INDEX(status, active_datetime),
t: INDEX(status, replacement_date),
t: INDEX(status, terminated_datetime)