MYSQL LOW LIMIT VALUE 减慢我的查询

Question

当我在 MariaDB 10.1/MySQL 5.7 中执行下面的查询时，结果有 849 行并在 0,016 秒内执行。

SELECT a.*
FROM bco_dav_dm.d_ttlz_cli a
WHERE FL_ATND_FNAL = 0
AND a.ID_ATND = 218
ORDER BY A.FL_CRTR,  A.DT_PRMR_ATVC_LNHA;

但是当我添加 LIMIT 子句以仅返回 1 行时，查询将在 9 秒内执行!!!为什么？

我测试过:使用 LIMIT 1 查询在 9 秒内执行。使用 LIMIT 2 查询在 9 秒内执行。使用 LIMIT 3 查询在 9 秒内执行。使用 LIMIT 4 及以上 (5,6,7,8...100, 200, 300, 400) 查询在 0,016 秒内执行!!!

我测试了好几次，结果总是一样。

我将如何在 Web 应用程序中使用此查询，我只需要 1 条记录我不知道为什么 LIMIT <=3 会减慢查询速度!

在其他帖子中，谈到使用较高的 OFFSET 自然会减慢查询速度，但我不使用 OFFSET。

我的解释:

select_type: SIMPLE 
table:a 
type:ref    
possible_keys:ID_ATND,FL_ATND_FNAL
key:ID_ATND 
key_len:5   
ref:const   
rows:1846
Extra: Using where; Using filesort

编辑:

我注意到当我使用 LIMIT 3 或以下时我的解释发生了变化

select_type: SIMPLE 
table:a 
type:ref    
possible_keys:ID_ATND,FL_ATND_FNAL
key:ORDER_BY_CRTR_DT
key_len:6
ref:const   
rows:1764
Extra: Using where

索引 ORDER_BY_CRTR_DT 是我在 ORDER BY 中使用的组合索引

INDEX ORDER_BY_CRTR_DT(FL_CRTR, DT_PRMR_ATVC_LNHA);

Answer 1

基于成本的优化器对不同限制的情况有一点不同的看法，在这种情况下完全错误。您会在几乎所有基于成本的数据库中更频繁地看到这种奇怪的行为。

在这种情况下，您看到差异的地方是在另一个计划中选择的索引 ORDER_BY_CRTR_DT 和 ID_ATND，然后数据库使用它们来估计数量行。看到较少的行数，基于成本的优化器假定查询速度更快(简化的观点)。

有时可以帮助重建表和索引，以便它们在描述数据的直方图中都具有关于数据的最新信息。随着时间的推移，结果可能会由于插入、更新和删除而再次发生变化，结果是计划可能再次降级。然而，通常通过定期重建来实现计划的稳定性。

或者，您可以强制使用索引，结果是为此计划禁用了基于成本的优化器。然而，这可能会适得其反，就像基于成本的优化器现在使您失败一样。

第二种选择是删除给出 9s 结果的索引，如果它不被使用或对其他查询影响很小，这可能是一个选项。