mysql - 在 MariaDB 上，表扫描的不良查询有时会花费数小时

Question

我的应用程序使用 MariaDB 数据库，我试图保持隔离，但一个特定用户直接访问该数据库，并在 6 周后今天开始提示，他们的一个查询从 5 分钟开始变慢(我认为这很糟糕)足够)超过120分钟。

从那时起，今天它有时像平常一样快，有时又慢下来。

这是他们的查询:

SELECT MAX(last_updated) FROM data_points;

这是表格:

CREATE TABLE data_points (
  seriesId INT UNSIGNED NOT NULL,
  modifiedDate DATE NOT NULL,
  valueDate DATE NOT NULL,
  value DOUBLE NOT NULL,
  created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP()
    ON UPDATE CURRENT_TIMESTAMP,
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  CONSTRAINT pk_data PRIMARY KEY (seriesId, modifiedDate, valueDate),
  KEY ix_data_modifieddate (modifiedDate),
  KEY ix_data_id (id),
  CONSTRAINT fk_data_seriesid FOREIGN KEY (seriesId)
  REFERENCES series(id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  MAX_ROWS=222111000;

这是解释:

id      select_type     table       type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE          data_points ALL     NULL            NULL    NULL            NULL    224166191

该表大约有 2.5 亿行，并且增长相对较快。

我可以强制用户做一些更明智的事情，但在短期内，我很想理解为什么查询持续时间在 6 周的平静之后今天变得疯狂。我会接受第一个可以解释这一点的答案。

Answer 1

SELECT MAX(last_updated) FROM data_points; 很容易优化:

INDEX(last_updated)

该索引将使 MAX 基本上是瞬时的。并且它将避免对磁盘和缓存的冲击(见下文)。

有两个因素控制未索引的速度:

• 表的大小，“增长相对较快”，以及
• [这可能就是您想要的。] 运行查询时会缓存表的多少内容。这可以使速度提高 10 倍。您可以这样部分地测试这个说法:

重新启动mysqld；查询时间；重新计时。第一次运行必须大量占用磁盘(因为重新启动)；第二个可能已经找到了 RAM 中的所有内容。

另一件事可能会扰乱计时:如果运行其他一些“大”查询并且它将该表的 block 从缓存中删除，那么查询将再次变慢。

相关:表的大小、innodb_buffer_pool_size 的值以及 RAM 量。

在一个不相关的主题上...那个PRIMARY KEY (seriesId,modifiedDate,valueDate)看起来很奇怪。 PK必须是唯一的。日期(日期时间等)可能在同一天/同一秒有多个条目；那么你能确定唯一性吗？特别是有 2 个日期？

(更多)

请解释一下这 4 个日期的含义。并问问自己是否都需要它们。 (表格中大约一半的内容是这些日期!)

该表有一个AUTO_INCRMENT；其他表需要它吗？如果不是，那么或者可以将其删除，或者可以使用它来确保 PK 是唯一的。

为了更好地帮助您，我们需要查看更多查询。