使用变量时的慢SQL语句 [英] Slow sql statement when using variables

问题描述

我有一个针对带有〜2.000行的MariaDB 10.1.26的以下SQL语句，具有即时结果.

I have the following SQL statement running against a MariaDB 10.1.26 with ~2.000 rows with instant results.

select value, datetime from Schuppen 
where (value = (select min(value) from Schuppen where (measure = 'temp') 
    and datetime between '2018-11-01 00:00:00' and '2018-11-02 00:00:00')) 
and datetime between '2018-11-01 00:00:00' and '2018-11-02 00:00:00';

当我在datetime字段中使用带有变量的以下语句时，执行过程需要约5.5秒.

When I use the following statement with variables for the datetime fields, the execution takes ~5.5 seconds.

set @startdate = cast('2018-11-01 00:00:00' as datetime);
set @enddate = cast('2018-11-02 00:00:00' as datetime);
select value, datetime from Schuppen 
where (value = (select min(value) from Schuppen where (measure = 'temp') 
    and datetime between @startdate and @enddate)) 
and datetime between @startdate and @enddate;

我拥有的数据行越多，执行该语句所花费的时间就越长.似乎变量会以某种方式更改语句的行为.

The more data rows I have, the longer it takes to execute the statement. Seems like the variables change the behaviour of the statement somehow.

这是怎么了?

推荐答案

问题是查询优化器在使用变量时找不到合适的索引时做得不好.这是一个已知问题.

The problem is that the query optimizer does a bad job on finding a suitable index when using variables. This is a known issue.

如果在两个查询上都使用EXPLAIN，则会看到不同之处.只是在不必要时尽量避免使用变量.

If you use EXPLAIN on both queries, you will see the difference. Just try to avoid variables when not necessary.

对于第一个查询，优化器查看"所选值并确定可以完美地使用索引来更有效地满足所选范围.

For the first query, the optimizer "sees" the chosen values and decides an index can be perfectly used to satisfy the selected range more efficiently.

对于第二个查询，优化器不知道定义范围的两个值，而是决定退回到完全扫描.

For the second query, the optimizer is unaware of the two values that define the range, and decides to fall back to a FULL SCAN instead.

这篇关于使用变量时的慢SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！