mysql - 如何将值插入到 Mysql 哈希分区表中？

Question

我已经创建了一个 mysql 表并按如下方式对其进行哈希分区。

      mysql> CREATE TABLE employees (
               id INT NOT NULL,
               fname VARCHAR(30),
               lname VARCHAR(30),
               hired DATE NOT NULL DEFAULT '1970-01-01',
               separated DATE NOT NULL DEFAULT '9999-12-31',
               job_code INT,
               store_id INT,
               PRIMARY KEY(id)
             )
             PARTITION BY HASH(id)
             PARTITIONS 10;

成功创建表后，我将值 1(into store_id) 插入到如下所示的表中

  mysql>INSERT INTO employees (store_id) values (1);

现在我不明白这个1的值会跑到哪里去？ (p0,p1,p2……p10)store_id value 1放到哪个分区去？我以为它会进入p0。但它没有。看下面我是这样检查的

  mysql>SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH,DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME LIKE 'employees';

它显示了 p1 中的值。见下文

   mysql>
        +------------+----------------+------------+----------------+-------------+
        | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
        +------------+----------------+------------+----------------+-------------+
        | employees  | p0             |          0 |              0 |       16384 |
        | employees  | p1             |          1 |          16384 |       16384 |
        | employees  | p2             |          0 |              0 |       16384 |
        | employees  | p3             |          0 |              0 |       16384 |
        | employees  | p4             |          0 |              0 |       16384 |
        | employees  | p5             |          0 |              0 |       16384 |
        | employees  | p6             |          0 |              0 |       16384 |
        | employees  | p7             |          0 |              0 |       16384 |
        | employees  | p8             |          0 |              0 |       16384 |
        | employees  | p9             |          0 |              0 |       16384 |
        +------------+----------------+------------+----------------+-------------+

我不知道为什么它被插入到p1.再次测试它..我这次插入了值2......

       mysql> INSERT INTO employees (store_id) values (2);

已进入p2。

         +------------+----------------+------------+----------------+-------------+
         | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
         +------------+----------------+------------+----------------+-------------+
         | employees  | p0             |          0 |              0 |       16384 |
         | employees  | p1             |          1 |          16384 |       16384 |
         | employees  | p2             |          1 |          16384 |       16384 |
         | employees  | p3             |          0 |              0 |       16384 |
         | employees  | p4             |          0 |              0 |       16384 |
         | employees  | p5             |          0 |              0 |       16384 |
         | employees  | p6             |          0 |              0 |       16384 |
         | employees  | p7             |          0 |              0 |       16384 |
         | employees  | p8             |          0 |              0 |       16384 |
         | employees  | p9             |          0 |              0 |       16384 |
         +------------+----------------+------------+----------------+-------------+

为什么值被插入到不同的分区中？散列分区有什么规律可循吗？有趣的是它离开 p0 并开始插入 p1？解释一下？

Answer 1

如果this解释适用于您的 MySQL 版本，分区号是这样找到的:MOD([Your input],[Number of partitions])。

在您的情况下，第一行可能有 id = 1，计算结果为 MOD(1,10) = 1。该行转到分区 1 (id= 2 转到分区 2)。