mysql - 改善MySQL SELECT性能

Question

设置
我有一个最终包含130亿行的数据库。行的关键是4个值：(asn, cty (country), src (source), time)。
asn大约有60000个不同的值，country大约有200个不同的值，source大约有55个不同的值——尽管不是所有的三元组都有效。大约有50万个有效的三胞胎。
对于每个有效的三元组，我每5分钟将数据记录到数据库中一次，其中time是记录数据的时间。90天后，我们会删除末尾的数据。该收益率12 (iterations per hour) * 24 (hours) * 90 (days) = 25920 rows per (asn, country, source) tuple
我的度量表目前看起来是这样的：

create table `metrics` (
  `time` int(10) unsigned NOT NULL,
  `asn` int(10) unsigned NOT NULL,
  `cty` char(2) NOT NULL,
  `src` char(3) NOT NULL,
  `reqs` int(10) unsigned DEFAULT NULL,
  `rtt` float unsigned DEFAULT NULL,
  `rexb` float unsigned DEFAULT NULL,
  `nae` float unsigned DEFAULT NULL,
  `util` float unsigned DEFAULT NULL,
  PRIMARY KEY (`time`, `asn`, `cty`, `src`),
  KEY (`asn`, `cty`, `src`)
) ENGINE=InnoDB DEFAULT CHARACTER SET ascii
partition by range(time) (
  PARTITION start        VALUES LESS THAN (0),
  PARTITION from20171224 VALUES LESS THAN (UNIX_TIMESTAMP('2017-12-31')),
  PARTITION from20171231 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-07')),
  PARTITION from20180107 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-14')),
  PARTITION from20180114 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-21')),
  PARTITION from20180121 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-28')),
  PARTITION from20180128 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-04')),
  PARTITION from20180204 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-11')),
  PARTITION from20180211 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-18')),
  PARTITION from20180218 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-25')),
  PARTITION from20180225 VALUES LESS THAN (UNIX_TIMESTAMP('2018-03-04')),
  PARTITION from20180304 VALUES LESS THAN (UNIX_TIMESTAMP('2018-03-11')),
  PARTITION from20180311 VALUES LESS THAN (UNIX_TIMESTAMP('2018-03-18')),
  PARTITION from20180318 VALUES LESS THAN (UNIX_TIMESTAMP('2018-03-25')),
  PARTITION from20180325 VALUES LESS THAN (UNIX_TIMESTAMP('2018-04-01')),
  PARTITION future       VALUES LESS THAN MAXVALUE
);

我还有一个“阈值”表，它记录了在任何给定的时间间隔内“好的RTT”的外观和“坏的RTT”的外观：

create table `thresholds` (
  `time` int(10) unsigned NOT NULL,
  `rtt_good` float NOT NULL DEFAULT 0,
  `rtt_bad` float NOT NULL DEFAULT 100,
  `rexb_good` float NOT NULL DEFAULT 0,
  `rexb_bad` float NOT NULL DEFAULT 100,
  `nae_good` float NOT NULL DEFAULT 0,
  `nae_bad` float NOT NULL DEFAULT 100,
  `util_good` float NOT NULL DEFAULT 0,
  `util_bad` float NOT NULL DEFAULT 100,
  PRIMARY KEY (`time`)
) ENGINE=InnoDB
partition by range(time) (
  PARTITION start        VALUES LESS THAN (0),
  PARTITION from20171224 VALUES LESS THAN (UNIX_TIMESTAMP('2017-12-31')),
  PARTITION from20171231 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-07')),
  PARTITION from20180107 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-14')),
  PARTITION from20180114 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-21')),
  PARTITION from20180121 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-28')),
  PARTITION from20180128 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-04')),
  PARTITION from20180204 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-11')),
  PARTITION from20180211 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-18')),
  PARTITION from20180218 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-25')),
  PARTITION from20180225 VALUES LESS THAN (UNIX_TIMESTAMP('2018-03-04')),
  PARTITION from20180304 VALUES LESS THAN (UNIX_TIMESTAMP('2018-03-11')),
  PARTITION from20180311 VALUES LESS THAN (UNIX_TIMESTAMP('2018-03-18')),
  PARTITION from20180318 VALUES LESS THAN (UNIX_TIMESTAMP('2018-03-25')),
  PARTITION from20180325 VALUES LESS THAN (UNIX_TIMESTAMP('2018-04-01')),
  PARTITION future       VALUES LESS THAN MAXVALUE
);

询问
现在，我对这些数据执行的最常见查询之一涉及返回给定asn、国家或asn+国家对的每次加权平均值。看起来像这样：

SELECT
    t.time * 1000 as time,
    @rtt := coalesce(m_sum.weighted_rtt, @rtt) as rtt,
    floor(least(100, greatest(0,
        100 * (coalesce(m_sum.weighted_rtt, @rtt) - t.rtt_bad) / (t.rtt_good - t.rtt_bad)
    ))) as rtt_quality,
    @util := coalesce(m_sum.weighted_util, @util) as util,
    floor(least(100, greatest(0,
        100 * (coalesce(m_sum.weighted_util, @util) - t.util_bad) / (t.util_good - t.util_bad)
    ))) as util_quality
FROM
    thresholds as t
LEFT JOIN
    (
        SELECT
            m.time,
            sum(m.rtt*m.reqs)/sum(m.reqs) AS weighted_rtt,
            sum(m.util*m.reqs)/sum(m.reqs) AS weighted_util
        FROM metrics AS m
        WHERE m.asn = '7018' and m.cty = 'us'
        GROUP BY m.time
    ) AS m_sum ON t.time = m_sum.time
ORDER BY t.time asc;

它会返回如下信息：

+---------------+---------+-------------+----------+--------------+
| time          | rtt     | rtt_quality | util     | util_quality |
+---------------+---------+-------------+----------+--------------+
| 1521234900000 | NULL    | NULL        | NULL     | NULL         |
| 1521235200000 | 45      | 80          | 3000     | 40           |
| 1521235500000 | 45      | 80          | 3000     | 40           |
| 1521235800000 | 65      | 70          | 2000     | 60           |
| 1521236100000 | 65      | 70          | 2000     | 60           |
| 1521236400000 | 65      | 70          | 2000     | 60           |
| 1521236700000 | 65      | 70          | 2000     | 60           |
| 1521237000000 | 120     | 50          | 4500     | 10           |
      ...           ...         ...         ...           ...

分解此查询，我们：
仅筛选我们关心的行（在本例中基于 asn和 cty）
为每一个计算加权指标聚合这些值
将这些聚合结果与一个包含每个给定时间的“阈值”值的表结合起来（可能在早上5点我们认为100毫秒的RTT非常差，但在下午5点，当每个人都在观看Netflix时，我们认为100毫秒非常好）
按 time排序
如果我们在给定时间内没有记录的度量（可能我们在该5分钟间隔内没有将流量传递到 time+ asn对），那么使用前面5分钟间隔的值（使用用户定义的变量）
计算每个度量的“相对优度”值（ cty）
变量
我的目标是尽快得到这个 *_quality查询。我可以改变：
My SELECT查询
表架构
表引擎（我可以访问MyISAM、InnoDB和MariaDB的Columnstore）
表索引
分区
我无法改变：
数据库服务器
数据库配置
以前的测试
我以前做了一些测试，使用的只有大约1亿5000万行（1%的最终数据集——包括300个不同的 SELECT值，而不是完全的25920），它看起来像NuNDB是最快的，比3-4倍的性能好（NiNDB返回数据大约0.7秒，Columnstore花费了大约2.5秒）。
我相信这是真的，因为我们做的第一件事就是在完成任何聚合或其他工作之前过滤掉这1.5亿行中的大部分。InnoDB支持索引，它允许我快速查找要筛选的行，并且只处理这些行——从不从磁盘读取其他数据。
不过，问题是：我现在有50亿行（约占最终数据集的40%）并且运行了相同的性能比较。这一次，Columnstore似乎比InnoDB快了两倍！（InnoDB为30秒，InnoDB为60秒）
至少，在我第一次运行特定 time+ asn的查询时速度更快。InnoDB似乎有中间缓存，因为我可以使用相同的 country+ asn运行其他查询，它们在1秒内完成，但即使在Columnstore中运行完全相同的查询也需要30秒
问题
为什么Columnstore比InnoDB快，即使Columnstore不支持索引（因此我们必须扫描整个表）？
我是否在表定义或查询中做错了什么，这可能会减慢速度？多亏了UnnDB的索引，我只需要从磁盘中读取少量的行。我不知道为什么要花整整一分钟
是否有人可以提供与前两个问题无关的其他性能提示？
在理想的情况下，我希望这个查询在10秒内返回，完整的数据集为130亿行——尽管如果这不可能，那么在60秒内返回是可以接受的。
一个附加注释
我有能力计算预先聚合的值并将它们存储在单独的表中。我已经做了一小部分了。我有三张桌子： country， metrics_by_asn和 metrics_by_cty。前两个存储度量的加权平均值，并且只在 metrics_by_time或 (asn, time)上设置键。这有效地减少了此查询：

SELECT
    m.time,
    sum(m.rtt*m.reqs)/sum(m.reqs) AS weighted_rtt,
    sum(m.util*m.reqs)/sum(m.reqs) AS weighted_util
FROM metrics AS m
WHERE m.asn = '7018'
GROUP BY m.time

对于这个：

SELECT
    m.time,
    weighted_rtt,
    weighted_util
FROM metrics_by_asn AS m
WHERE m.asn = '7018'

第三个表 (cty, time)返回汇总统计，如最大RTT、平均RTT、行数等。
我没有创建 metrics_by_time表有两个原因。首先，我没想到会有令人难以置信的业绩增长。平均而言，特定的 metrics_by_asn_and_cty+ asn对仅从1.3个不同的源提供服务。因此，在大多数情况下，预先聚合这不会减少我们需要选择的行数。其次，我们已经达到了一些主要的磁盘使用限制。单看我们的度量表，我们就有130亿行，大约是每行35字节。这个数据库是455G。再加上预聚合的表和其他表，我们在其中转储用于计算这些度量的原始数据，我们在磁盘上大约有850GB。我还没有被告知我可以存储多少数据的严格限制，但我正在努力保持在1兆字节以下以确保安全。

Answer 1

您在文章中显示了CREATE表，这很好，但没有提到任何其他查询分析。在调查查询优化时，应考虑：
EXPLAIN查看查询优化计划，以及优化器如何选择使用索引
Query profiling
PERFORMANCE_SCHEMA
系统架构for MySQL或for MariaDB
我试过至少为你的子查询测试EXPLAIN。顺便说一句，列pop在索引中提到，但是没有出现在表中，所以您还没有发布真正的CREATE表。
我明白了：

mysql> EXPLAIN SELECT m.time, sum(m.rtt*m.reqs)/sum(m.reqs) AS weighted_rtt, 
sum(m.util*m.reqs)/sum(m.reqs) AS weighted_util FROM metrics AS m 
WHERE m.asn = '7018' and m.cty = 'us' GROUP BY m.time\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
         type: ref
possible_keys: PRIMARY,asn,bk1
          key: asn
      key_len: 6
          ref: const,const
         rows: 1
        Extra: Using index condition; Using where; Using temporary; Using filesort

注意，只使用asn索引的前两列，如 const,const所示。而且 Using temporary; Using filesort通常表示查询的开销很高。
当我添加一个索引时，我变得更好了：

mysql> alter table metrics add index bk1 (asn,cty,time);

我不得不使用一个索引提示来说服MySQL优化器使用我的索引。这可能是必要的，因为我的表中没有数据行，所以优化器无法分析哪个索引更好。

mysql> EXPLAIN SELECT m.time, sum(m.rtt*m.reqs)/sum(m.reqs) AS weighted_rtt, 
sum(m.util*m.reqs)/sum(m.reqs) AS weighted_util FROM metrics AS m use index(bk1) 
WHERE m.asn = '7018' and m.cty = 'us' GROUP BY m.time\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
         type: ref
possible_keys: PRIMARY,asn,bk1
          key: bk1
      key_len: 6
          ref: const,const
         rows: 1
        Extra: Using index condition; Using where

临时表/文件排序已不存在。这是因为一旦我将 time列放在用于筛选的两列之后，GROUP BY可以按索引顺序执行。
最后，我试图创建一个包含子查询中引用的所有列的索引：

mysql> alter table metrics add index bk2 (asn,cty,time,rtt,reqs,util);

mysql> EXPLAIN SELECT m.time, sum(m.rtt*m.reqs)/sum(m.reqs) AS weighted_rtt, 
sum(m.util*m.reqs)/sum(m.reqs) AS weighted_util FROM metrics AS m use index(bk2) 
WHERE m.asn = '7018' and m.cty = 'us' GROUP BY m.time\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
         type: ref
possible_keys: PRIMARY,asn,bk1,bk2
          key: bk2
      key_len: 6
          ref: const,const
         rows: 1
        Extra: Using where; Using index

Using index是个好兆头。这称为“覆盖索引”，这意味着查询可以通过读取索引来获取所需的所有列，而不必读取表。这是一种有用的技巧。
你可能喜欢我的演示文稿，或者。
您提到不能更改MySQL配置选项，但没有说明选项是什么。其中一个重要的选项是InnoDB缓冲池大小。如果没有足够大的缓冲池，查询将强制大量I/O，因为它将索引页交换到RAM中，然后再次退出。
我没有使用MariaDB column store的经验，所以我不能评论它的特性，也不能评论如何监视或调整它。你可能想和MariaDB的服务部门打交道。
我同意James Scheller的回答，即预先聚合部分结果并存储它是很重要的，而且可能是解决此问题的唯一方法。我读过的一些列存储会自动执行此操作，为每个分区预先计算各种聚合结果。我不知道MariaDB column store是做什么的。