MySQL分区表，为什么分区键必须是主键的⼀部分？
随着业务的不断发展，数据库中的数据会越来越多，相应地，单表的数据量也会越到越⼤，⼤到⼀个临界值，单表的查询性能就会下降。
这个临界值，并不能⼀概⽽论，它与硬件能⼒、具体业务有关。
虽然在很多 MySQL 运维规范⾥，都建议单表不超过 500w、1000w。
但实际上，我在⽣产环境，也见过⼤⼩超过 2T，记录数过亿的表，同时，业务不受影响。
单表过⼤时，业务通常会考虑两种拆分⽅案：⽔平切分和垂直切分。
⽔平拆分 VS 垂直拆分
⽔平切分，拆分的维度是⾏，⼀般会根据某种规则或算法将表中的记录拆分到多张表中。
拆分后的表既可在⼀个实例，也可在多个不同实例中。如果是后者，⼜会涉及到分布式事务。
垂直切分，拆分的维度是列，⼀般是将列拆分到多个业务模块中。这种拆分更多的是上层业务的拆分。
从改造的复杂程度来说，前者⼩于后者。
所以，在单表数据量过⼤时，业界⽤得较多的还是⽔平拆分。
常见的⽔平拆分⽅案有：分库分表、分区表。
虽然分库分表是⼀个⽐较彻底的⽔平拆分⽅案，但⼀⽅⾯，它的改造需要⼀定的时间；另⼀⽅⾯，它对开发的能⼒也有⼀定的要求。相对来说，分区表就⽐较简单，也⽆需业务
改造。
分区表
很多⼈可能会认为 MySQL 的优势在于 OLTP 应⽤，对于 OLAP 应⽤就不太适合，所以，也不太推荐分区表这种偏 OLAP 的特性。
但实际上，对于某些业务类型，还是⽐较适合使⽤分区表的，尤其是那些有明显冷热数据之分，且数据的冷热与时间相关的业务。
下⾯，我们看看分区表的优点：
1. 提升查询性能
对于分区表的查询操作，如果查询条件中包含分区键，则这个查询操作就只会被下推到符合条件的分区内进⾏，⽆关分区将⾃动过滤掉。
在数据量⽐较⼤的情况下，能提升查询速度。
2. 对业务透明
将表从⼀个⾮分区表转换为分区表，业务端⽆需做任何改造。
3. 管理⽅便
在对单个分区进⾏删除、迁移和维护时，不会影响到其它分区。
尤其是针对单个分区的删除（DROP）操作，避免了针对这个分区所有记录的 DELETE 操作。
遗憾的是，MySQL 分区表不⽀持并⾏查询。理论上，当⼀个查询涉及到多个分区时，分区与分区之间应进⾏并⾏查询，这样才能充分利⽤多核 CPU 资源。
但 MySQL 并不⽀持，包括早期的官⽅⽂档，也提到了这个问题，也将这个功能的实现放到了优先级列表中。
These features are not currently implemented in MySQL Partitioning, but are high on our list of priorities.
- Queries involving aggregate functions such as SUM() and COUNT() can easily be parallelized. A simple example of such a query might be SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id;. By “p - Achieving greater query throughput in virtue of spreading data seeks over multiple disks.
MySQL 8.0 中分区表的变化
在 MySQL 5.7 中，对于分区表，有个很重⼤的更新，即 InnoDB 存储引擎原⽣⽀持了分区，⽆需再通过 ha_partition 接⼝来实现。
所以，在 MySQL 5.7 中，如果要创建基于 MyISAM 存储引擎的分区表，会提⽰ warning 。
The partition engine, used by table 'sbtest.t_range', is deprecated and will be removed in a future release. Please use native partitioning instead.
⽽在 MySQL 8.0 中，则更为彻底，server 层移除了 ha_partition 接⼝代码。
如果要使⽤分区表，只能使⽤⽀持原⽣分区的存储引擎。在 MySQL 8.0 中，就只有 InnoDB。
这就意味着，在 MySQL 8.0 中，如果要创建 MyISAM 分区表，基本上就不可能了。
这也从另外⼀个⾓度说明了为什么⽣产上不建议使⽤ MyISAM 表。
mysql> CREATE TABLE t_range (
->    id INT,
->    name VARCHAR(10)
-> ) ENGINE = MyISAM
-> PARTITION BY RANGE (id) (
->    PARTITION p0 VALUES LESS THAN (5),
->    PARTITION p1 VALUES LESS THAN (10)
-> );
ERROR 1178 (42000): The storage engine for the table doesn't support native partitioning
为什么分区键必须是主键的⼀部分？
在使⽤分区表时，⼤家常常会碰到下⾯这个报错。
mysql group by order bymysql> CREATE TABLE opr (
->    opr_no INT,
->    opr_date DATETIME,
->    description VARCHAR(30),
->    PRIMARY KEY (opr_no)
-> )
-> PARTITION BY RANGE COLUMNS (opr_date) (
->    PARTITION p0 VALUES LESS THAN ('20210101'),
->    PARTITION p1 VALUES LESS THAN ('20210102'),
-
>    PARTITION p2 VALUES LESS THAN MAXVALUE
-> );
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
即分区键必须是主键的⼀部分。
上⾯的 opr 是⼀张操作流⽔表。其中，opr_no 是操作流⽔号，⼀般都会被设置为主键，opr_date 是操作时间。基于操作时间来进⾏分区，是⼀个常见的分区场景。
为了突破这个限制，可将 opr_date 作为主键的⼀部分。
mysql> CREATE TABLE opr (
->    opr_no INT,
->    opr_date DATETIME,
->    description VARCHAR(30),
->    PRIMARY KEY (opr_no, opr_date)
-> )
-> PARTITION BY RANGE COLUMNS (opr_date) (
->    PARTITION p0 VALUES LESS THAN ('20210101'),
->    PARTITION p1 VALUES LESS THAN ('20210102'),
->    PARTITION p2 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.04 sec)
但是这么创建，⼜会带来⼀个新的问题，即对于同⼀个 opr_no ，可插⼊到不同分区中。如下所⽰：
mysql> insert into opr values(1,'2020-12-31 00:00:01','abc');
Query OK, 1 row affected (0.00 sec)
mysql> insert into opr values(1,'2021-01-01 00:00:01','abc');
Query OK, 1 row affected (0.00 sec)
mysql> select * from opr partition (p0);
+--------+---------------------+-------------+
| opr_no | opr_date            | description |
+--------+---------------------+-------------+
|      1 | 2020-12-31 00:00:01 | abc        |
+--------+---------------------+-------------+
1 row in set (0.00 sec)
mysql> select * from opr partition (p1);
+--------+---------------------+-------------+
| opr_no | opr_date            | description |
+--------+---------------------+-------------+
|      1 | 2021-01-01 00:00:01 | abc        |
+--------+---------------------+-------------+
1 row in set (0.00 sec)
这实际上违背了业务对于 opr_no 的唯⼀性要求。
既然这样，有的童鞋会建议给 opr_no 添加个唯⼀索引，But，现实是残酷的。
mysql> create unique index uk_opr_no on opr (opr_no);
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered)
即便是添加唯⼀索引，分区键也必须包含在唯⼀索引中。
总⽽⾔之，对于 MySQL 分区表，⽆法从数据库层⾯保证⾮分区列在表级别的唯⼀性，只能确保其在分区内的唯⼀性。
这也是 MySQL 分区表所为⼈诟病的地⽅之⼀。
但实际上，这个锅让 MySQL 背并不合适，对于 Oracle 索引组织表（ InnoDB 即是索引组织表），同样也有这个限制。
Note the following characteristics of partitioned IOTs:
- Partition columns must be a subset of primary key columns.
- Secondary indexes can be partitioned locally and globally.
- OVERFLOW data segments are always equipartitioned with the table partitions.
下⾯，我们看看刚开始的建表 SQL ，在 Oracle 中的执⾏效果。
SQL> CREATE TABLE opr_oracle (
2      opr_no NUMBER,
3      opr_date DATE,
4      description VARCHAR2(30),
5      PRIMARY KEY (opr_no)
6  )
7  ORGANIZATION INDEX
8  PARTITION BY RANGE (opr_date) (
9      PARTITION p0 VALUES LESS THAN (TO_DATE('20170713', 'yyyymmdd')),
10      PARTITION p1 VALUES LESS THAN (TO_DATE('20170714', 'yyyymmdd')),
11      PARTITION p2 VALUES LESS THAN (MAXVALUE)
12  );
PARTITION BY RANGE (opr_date) (
*
ERROR at line 8:
ORA-25199: partitioning key of a index-organized table must be a subset of the
primary key
同样报错。
注意，这⾥指定了 ORGANIZATION INDEX ，创建的是索引组织表。
看来，分区键必须是主键的⼀部分并不是 MySQL 的限制，⽽是索引组织表的限制。
之所以对索引组织表有这样的限制，个⼈认为，还是基于性能考虑。
假设分区键和主键是两个不同的列，在进⾏插⼊操作时，虽然也指定了分区键，但还是需要扫描所有分区才能判断插⼊的主键值是否违反了唯⼀性约束。这样的话，效率会⽐较低下，违背了分区表的初衷。
⽽对于堆表则没有这样的限制。
在堆表中，主键和表中的数据是分开存储的，在判断插⼊的主键值是否违反唯⼀性约束时，只需利⽤到主键索引。
但与 MySQL 不⼀样的是，Oracle 实现了全局索引，所以针对上⾯的，同⼀个 opr_no，允许插⼊到不同分区中的问题，可通过全局唯⼀索引来规避。
SQL> CREATE TABLE opr_oracle (
2      opr_no NUMBER,
3      opr_date DATE,
4      description VARCHAR2(30),
5      PRIMARY KEY (opr_no, opr_date)
6  )
7  ORGANIZATION INDEX
8  PARTITION BY RANGE (opr_date) (
9      PARTITION p0 VALUES LESS THAN (TO_DATE('20170713', 'yyyymmdd')),
10      PARTITION p1 VALUES LESS THAN (TO_DATE('20170714', 'yyyymmdd')),
11      PARTITION p2 VALUES LESS THAN (MAXVALUE)
12  );
Table created.
SQL> create unique index uk_opr_no on opr_oracle (opr_no);
Index created.
SQL> insert into opr_oracle values(1,to_date('2020-12-31 00:00:01','yyyy-mm-dd hh24:mi:ss'),'abc');
1 row created.
SQL> insert into opr_oracle values(1,to_date('2020-12-31 00:00:01','yyyy-mm-dd hh24:mi:ss'),'abc');
insert into opr_oracle values(1,to_date('2020-12-31 00:00:01','yyyy-mm-dd hh24:mi:ss'),'abc')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_IOT_TOP_87350) violated
但 MySQL 却⽆能为⼒，之所以会这样，是因为 MySQL 分区表只实现了本地分区索引（Local Partitioned Index），⽽没有实现 Oracle 中的全局索引（Global Index）。
本地分区索引 VS 全局索引
本地分区索引和全局索引的原理图如下所⽰：
结合原理图，我们来看看两种索引之间的区别：
1. 本地分区索引同时也是分区索引，分区索引和表分区之间是⼀⼀对应的。
⽽全局索引，既可以是分区的，也可以是不分区的。
如果是全局分区索引，⼀个分区索引可对应多个表分区，同样，⼀个表分区也可对应多个分区索引。
2. 对本地分区索引的管理操作只会影响到单个分区，不会影响到其它分区。
⽽对全局分区索引的管理操作会造成整个索引的失效，当然，这⼀点可通过 UPDATE INDEXES ⼦句加以规避。
3. 本地分区索引只能保证分区内的唯⼀性，⽆法保证表级别的唯⼀性，但全局分区可以。
4. 在 Oracle 中，⽆论是索引组织表还是堆表，如果要创建本地唯⼀索引，同样也要求分区键必须是唯⼀键的⼀部分。
SQL> create unique index uk_opr_no_local on opr_oracle(opr_no) local;
create unique index uk_opr_no_local on opr_oracle(opr_no) local
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE
index
总结
1. MySQL 分区表关于“分区键必须是唯⼀键（主键和唯⼀索引）的⼀部分”的限制，本质上是索引组织表的限制。
2. MySQL 分区表只实现了本地分区索引，没有实现全局索引，所以⽆法保证⾮分区列的全局唯⼀。
如果要保证⾮分区列的全局唯⼀，只能依赖业务实现了。
3. 不推荐使⽤ MyISAM 分区表。当然，任何场景都不推荐使⽤ MyISAM 表。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。