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

问题描述

我在 MariaDB 10.1.26 上运行了以下 SQL 语句，其中包含约 2.000 行即时结果.

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';

当我将以下语句与日期时间字段的变量一起使用时，执行大约需要 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.

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

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屋！