首页 文章

需要帮助在位列上对MySQL表进行分区，并在结果分区上进行子分区

提问于
浏览 次
0

我需要一些帮助来弄清楚如何在MySQL数据库中对下面的表进行分区，这样我首先有2个分区，1个活动用户（ active column = true），另一个有非活动用户 . 然后我想按年度对非活动用户分区进行子分区（在 archive_key 列上，YEAR（4）作为数据类型） . 我想我知道如何实现这一点，但我正确地执行它 .

谁能帮我实现这个目标？我目前正在使用MySQL 5.7和8版本 . 感谢任何可以给出的建议 .

CREATE TABLE users_table (
  row_id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name varchar(255) DEFAULT NULL,
  last_name varchar(255) DEFAULT NULL,
  ...
  createdby varchar(255) DEFAULT NULL,
  createdat datetime DEFAULT NULL,
  lastmodby varchar(255) DEFAULT NULL,
  lastmodat datetime DEFAULT NULL,
  active bit(1) NOT NULL DEFAULT b'0',
  archive_key year(4) GENERATED ALWAYS AS (YEAR(createdat)) STORED,
  PRIMARY KEY (row_id, active),
)
ENGINE = INNODB,
AUTO_INCREMENT = 84771,
AVG_ROW_LENGTH = 166,
CHARACTER SET utf8,
COLLATE utf8_unicode_ci;

顺便说一句，爱分区给你的灵活性！ MySQL真的很棒！

mysql partitioning mysql-5.7 mysql-8.0

1 回答

  • 1

    Subpartitioning

    当您从5.7 manual引用子分区时，MySQL对于您可以做什么和不能做什么都是非常严格的：

    可以对由RANGE或LIST分区的子分区进行分区 . 子分区可以使用HASH或KEY分区

    这意味着你正在看这样的事情：

    CREATE TABLE users_table (
  row_id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name varchar(255) DEFAULT NULL,
  last_name varchar(255) DEFAULT NULL,
  createdby varchar(255) DEFAULT NULL,
  createdat datetime DEFAULT NULL,
  lastmodby varchar(255) DEFAULT NULL,
  lastmodat datetime DEFAULT NULL,
  active bit(1) NOT NULL DEFAULT b'0',
  archive_key year(4),
  PRIMARY KEY (row_id, active, archive_key)
)
       ENGINE = INNODB,
       CHARACTER SET utf8,
       COLLATE utf8_unicode_ci
       PARTITION BY LIST(active)
           SUBPARTITION BY HASH(`archive_key`)
           SUBPARTITIONS 4
       (
            PARTITION inactive VALUES IN (0),
            PARTITION active   VALUES IN (1)
       );

    请注意，您需要在PK中包含 archive_key 以避免

    ERROR 1503（HY000）：PRIMARY KEY必须包含表格分区功能中的所有列

    Opinion on partitioning

    不，它不会摇滚 . 在这个论坛上展示的大多数用例在分区方面运行速度要快于没有分区 . 在某些情况下，它们运行得更慢 .

    当然，有很多选择 . 我见过的唯一一个实际的性能提升是 PARTITION BY RANGE ，但即便如此只适用于狭窄的用例（清除时间序列，'find nearest'，工作集位于带有'bad'索引的单个分区中，可传输的表空间） . Details

    “分区修剪”加速查询是一个神话 . 具有合适索引的非分区表可能至少以相同的速度运行 .

    注意：要利用添加（或删除）分区，必须重新评估索引 .

    在你的例子中，非分区

    INDEX(active, createdat)   -- in this order

WHERE active = 1
  AND createdat >= '2017/01/01'
  AND createdat  < '2017/01/01' + INTERVAL 1 YEAR

    要么

    INDEX(active, active_key)   -- in this order

WHERE active = 1
  AND archive_key = '2017'

    （前者不需要生成的列 . ）

    回复于

相关问题