我在Ubuntu 18.4.4 LTS上运行MariaDB 10.2.31。我经常会遇到以下难题——尤其是早上出发时，也就是我的DEV环境已经闲置了一晚上——但在白天也会不时遇到。
我有一个表(这也适用于其他表)，大约有15000行，VARCHAR列上有一个索引，平均包含5到10个字符。值得注意的是，包括这一列在内的大多数列都是GENERATED ALWAYS AS (JSON_EXTRACT(....)) STORED，因为我的99%的数据来自作为JSON编码字符串的REST API(而且我只需将这些数据存储在一列中并提取其他所有内容)。
当在WHERE colname LIKE 'text%'列上运行查询时，我发现查询结果持续时间为0.006秒。美好的当我对查询EXPLAIN进行编辑时，我可以看到索引正在被使用。然而，正如我所提到的，当我早上出发时，这需要更长的时间(今天早上14秒)。我知道查询缓存，并在关闭查询缓存的情况下尝试过(通过SET GLOBAL query_cache_type=OFF和RESET QUERY CACHE)。在这种情况下，我得到了大约0.3秒的一致时间——正如预期的那样。
那么，你建议我应该调查什么？我的DB在睡觉吗？有这样的事情吗？
可能会发生两件事：
1) 冷缓存(隔夜备份、mysqld重新启动或大型处理作业会导致此特定索引和表数据从内存中逐出)。
2) 表上的统计信息会过时，查询计划器也会感到困惑，直到您对表运行一些查询并刷新统计信息。可以使用ANALYZE TABLE TABLE_name强制更新。
3) 查询计划器heisenbug。在MySQL 5.7及更高版本中非常常见，以前从未在MariaDB上见过，所以这不太可能。
您可以通过在配置中启用以下内容来了解详情：
log_output='FILE'
log_slow_queries=1
log_slow_verbosity='query_plan,explain'
long_query_time=1
然后，在您看到缓慢事件后，查看慢速日志中的内容。如果记录的解释计划在慢速和快速情况下看起来都一样，则存在冷缓存问题。如果它们不同，那么就有一个表统计问题，您需要在夜间任务结束时对ANALYZE TABLE进行cron操作，该任务会对该表进行大量读取/写入。如果这没有帮助，作为最后的手段，使用FORCE INDEX (index_name)在查询中硬编码一个索引提示。
使用足以捕获结果的log_slow_verbosity=query_plan,explain和long_query_time启用慢速查询日志。看看它是否偶尔使用不同的(或不使用)索引。
在您开始下一天的工作之前，请查看SHOW GLOBAL STATUS LIKE "innodb_buffer_pool%"，并在查询之后再次查看这些值。查看此状态输出中有多少缓冲池读取请求与读取请求，以查看是否所有请求都来自磁盘。
正如@Solarflare所提到的，备份和夜间活动可能会清除innodb缓冲池中的缓存数据，并将坏数据恢复到磁盘以使其再次变慢。作为夜间活动的一部分，您可以将innodb_buffer_pool_dump_now=1设置为在脚本化活动之前保存热页面，将innodl_buffer_pool_load_now=1设为恢复热页面。
大声呼喊并感谢所有提供宝贵见解的人！从你们给出的所有提示来看，我想我开始更好地理解这个问题，并开始缩小范围：
我发现的第一件事是我默认的134MB的innodb_buffer_pool_size。就我处理的数据种类和数量而言，这是低得离谱的——所以我能够增加它。非常有用的帖子：https://dba.stackexchange.com/a/27341从文档来看：https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-resize.html
现在我已经将其增加到接近2GB，并且能够监控其使用情况和RAM的总体使用情况(cli:cat/proc/meminfo)，我意识到我的4GB RAM实际上处于低端。我几乎看不到任何未使用的开销(缓冲区使用率仍为99%，可用RAM约为100MB)。
接下来，我将开始优化我的守护进程的RAM使用情况，看看这会带来什么结果——但这不会完全释放足够的RAM。
@丹布莱克提到了CCD_ 12和CCD_。这是一种有趣的方法，可以在后台进程访问DB时使用，因为我很想将后台进程的缓冲区使用与前端的缓冲区分开(显然这是不可能的！)。我会进一步研究这个问题，但由于我的守护进程一直在运行(不仅仅是在晚上)，这可能不可行。
@Gordan Bobic提到使用ANALYZE TABLE tableName"刷新"数据库表。我发现这相当快，并在每次进行广泛的读/写操作后将其合并到守护进程中。这将使守护进程的运行时间增加几秒钟，但这根本没有问题。我想我不会错的：)
因此，最终我认为我的问题是多种因素的结合：缓冲区太小，RAM太小，该环境的读/写操作太多(驱逐缓冲索引等)。此外，我还必须了解更多关于内存分配等的信息，并更好地优化它(大页面=1等)。