主页 / dba / 问题 / 240768
Accepted
gertvdijk
Asked: 2019-06-18 14:27:48 +0800 CST

在与 PARTITION BY RANGE + LIMIT 相同的列上使用 ORDER BY 进行高效分区修剪？

  • 772

我已经在 MariaDB（10.4.5，当前为 RC）中使用 InnoDB 设置了一个表，使用按列进行分区，其值仅递增，并且始终在末尾插入新数据。对于这种情况，分区对于加快某些查询并在快速驱动器上保持新/活动分区以及在慢速旋转磁盘上保持旧/存档分区是有意义的。对于插入加速，它工作得很好！（类似于 TimescaleDb 方法，但没有时间也没有 PostgreSQL。）

在同一列上按范围选择也可以正常工作；它只会开始读取指定范围的分区（索引）。到目前为止一切都很酷。

现在，我还有一些查询在该列上没有子句，但按该列降序排列（即新数据优先），加上一个限制，通常只会命中一个或两个最新分区（快速，缓存指数）。但是，无论指定的顺序是什么，似乎 MySQL/MariaDB 都会从头到尾打开分区。真的有那么笨吗？此外，我真的找不到其他人有这个问题，这让我有点担心。（有时这意味着我错过了一些非常明显的东西。）

为了在这里更具体 - 为了测试，我有下表：

CREATE TABLE `mytable` (
  `user_id` bigint(20) unsigned NOT NULL,
  `my_id` bigint(20) unsigned NOT NULL,
  `data` varbinary(123) DEFAULT NULL,
  PRIMARY KEY (`user_id`,`my_id`),
  UNIQUE KEY `my_id_idx` (`my_id`)  -- I was hoping this one could help me
) ENGINE=InnoDB ROW_FORMAT=COMPACT
 PARTITION BY RANGE (`my_id`)
(PARTITION `p0` VALUES LESS THAN (10000000) ENGINE = InnoDB,
 PARTITION `p10M` VALUES LESS THAN (20000000) ENGINE = InnoDB,
 PARTITION `p20M` VALUES LESS THAN (30000000) ENGINE = InnoDB,
 PARTITION `p30M` VALUES LESS THAN (40000000) ENGINE = InnoDB,
 [...]
)

我运行如下查询：

SELECT 
    user_id,
    my_id,
    LENGTH(data) AS data_len
FROM
    mytable
    -- tried to optimize with index hints:
    -- USE INDEX FOR ORDER BY (MY_ID_IDX)
    -- USE INDEX FOR ORDER BY (PRIMARY)
    -- USE INDEX FOR ORDER BY (MY_IDX, PRIMARY)
WHERE
    user_id = 1234567
ORDER BY my_id DESC
LIMIT 10;

我发现它首先开始寻找所有数据，user_id = 1234567首先显示旋转磁盘上的大量 I/O 负载，然后最终进入快速存储以获取完整集，然后切断最后LIMIT 10一行......都在快速存储，所以我们浪费了几分钟的时间！唔。

我的数据太大，我们无法将所有索引都放入内存 - 我们依赖磁盘上“足够”的索引来缓存在存储层上。但是，即使所有索引都适合缓存，数据也必须来自磁盘，并且一些用户在这里有大量数据（> 10M 行），这样在内存中进行这种排序效率很低。 所以我希望找到一种方法让 MariaDB 查找最后 LIMIT 行，然后停止阅读。

作为一个人，你会首先开始查看最后一个分区，因为它ORDER BY my_id DESC和最新的分区包含它的最高值。但是，我如何告诉 MySQL/MariaDB 这样做呢？

explain partitions结果（对于上面列出的所有 USE INDEX 变体都是相同的）：

  select_type: SIMPLE
        table: mytable
   partitions: p0M,p10M,p20M,p30M, ... (~ hundred here)
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 9999999 (worst-case)
        Extra: Using where

事实上，与我的预期相反，如果使用 first-to-new 分区按升序进行查询，它的性能甚至不会更好。它仍然会请求所有分区的所有索引，然后发现它只需要一个...

我听说过一些关于 MySQL 未来版本中分区的全局索引的信息，但我怀疑它是否真的会对这里有帮助，因为它的大小很大......而且它已经通过我的分区布局得到了暗示案子。我发现的关于“分区修剪”的信息似乎与读取顺序无关；仅关于查询中的子句。

任何帮助表示赞赏。:-)

较新的分区将被动态创建，并且在特定分区上给出提示是不可行的。我的情况是“最新”分区很快，“旧”是“慢”，“最旧”是“超慢”——假设没有缓存在存储层上，因为太多了。此外，我在一台单独的机器上使用代理（SPIDER），它应该为客户端提供一个单一的查询界面，不需要知道后端的分区布局，所以我更喜欢一种方法来实现它'自动的'。

mysql mariadb

1 个回答

  1. Best Answer

    恭喜。我认为您发现了一种情况，即分区不能与非分区一样快。

    WHERE user_id = 1234567
ORDER BY my_id DESC
LIMIT 10;

    需要按此INDEX(user_id, my_id)顺序进行，无需分区。因此，它将触及 10 行并退出。

    使用您拥有的分区，它必须检查每个分区，收集在每个分区中找到的行，对它们进行排序，然后在第 10 个停止。

    “分区不是性能灵丹妙药”。

    您还有其他可以从中PARTITION BY RANGE受益的疑问吗？如果是这样，您可能需要权衡取舍。即，一些查询运行得更快，一些运行得更慢。

    一般来说，如果“用户”的数量相当有限，并且您不断地为每个用户插入新行，那么每个用户有一个“热点”就可以了。

    这导致

    PRIMARY KEY(user_id, my_id)

    以my_id某种方式独一无二。它不必被声明UNIQUE。如果是AUTO_INREMENT，那么这工作正常：

    my_id INT AUTO_INCREMENT,
PRIMARY KEY(user_id, my_id)  -- to cluster by user
INDEX(my_id)   -- to keep AUTO_INCREMENT happy

    这样，大多数这样的查询都非常有效：

    WHERE user_id = 12345
  AND ((other stuff))

    buffer_pool 中的缓存比 SSD 与 HDD 更重要。并且触摸的块数对性能很重要。

    每个INSERTs用户需要一个块。最终，会有一个区块分裂。但随后，它又回到了一个活动块（“热点”）。

    SELECTs，即使所需的块不在 buffer_pool 中，由于WHERE user_id=...导致所需的行位于非常少的块中，它也往往是有效的。SELECT ... LIMIT 10对于您提到的情况尤其如此。

    块被缓存。 全都 INDEXes没有。有问题的查询将只查看非分区布局中的 1 个（可能是 2 个）块。该指数的其余部分将根据活动来来去去。

    10M 行是“大”；10 亿行是“巨大的”。MySQL 和 MariaDB 的全局索引可能需要几年的时间；不要屏住呼吸。

    的价值是innodb_buffer_pool_size多少？多少内存？

    • 3

相关问题