22.1 MySQL分区概述

迷南。 2024-04-01 16:08 62阅读 0赞

### 22.1 MySQL分区概述 ###

分区使这个概念更进一步，使您能够根据需要设置的规则在文件系统中分布各个表的部分。实际上，表的不同部分作为单独的表存储在不同的位置。用户选择的用于完成数据分区的规则被称为**分区函数**，在MySQL中可以是

1.  模数
2.  一组范围或值列表的简单匹配、
3.  内部哈希函数
4.  线性哈希函数

该函数根据用户指定的分区类型进行选择，并将用户提供的表达式的值作为其参数。根据使用的分区类型，此表达式可以是列值、作用于一个或多个列值的函数，也可以是一组一个或更多列值。

RANGE, LIST, \[LINEAR\] HASH分区，则将分区列的值传递给分区函数，它返回一个整数值表示特定的记录保存的分区编号。此函数必须不是常量的或随机的。它可能不包含任何查询，但可以使用MySQL有效的SQL表达式，只要该表达式返回值要么是NULL 要么是整型intval。

对于\[LINEAR\] KEY, RANGE COLUMNS, and LIST COLUMNS 分区，分区表达式包含一个或多个列。  
对于\[LINEAR\] KEY分区，分区函数由MySQL提供。

允许的分区列类型和分区函数：22.2 分区类型，  
分区语法描述和附加示例：[13.1.18 CREATE TABLE Statement ][13.1.18 CREATE TABLE Statement]  
对分区函数的限制：[22.6 Restrictions and Limitations on Partitioning][]

5.7 MySQL只支持水平分区，对垂直分区的支持无计划。

MySQL 5.7是否支持分区，可以通过show plugins，看名为partition的插件是否为active。并且同一个数据库，不同表支持分区可以是不同的存储引擎，但是表分区后所有的分区都必须和表使用相同引擎。MyISAM和InnoDB都支持分区。  
MySQL 8都无需插件即可支持分区，且只有InnoDB支持，MyISAM不支持分区。  
MySQL 5.7 的NDB支持分区有自己的规则。

使用MySQL 5.7创建一个表，使用hash 分成6个区的语法如下，创建一个非分区的表，后面追加分区信息。

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
        ENGINE=INNODB
        PARTITION BY HASH( MONTH(tr_date) )
        PARTITIONS 6;

分区适用于一个表的所有数据和索引；不能只分区数据而不分区索引，或者反之亦然，也不能只对表的一部分进行分区。

**表的分区表达式中使用的所有列，必须是表的每个唯一键的一部分，包括主键。因此如果任何键，没有重复的列，就不能做成分区表了（注意是唯一键）。** 换句话说，**表上的每个唯一键必须使用表的分区表达式中的每个列。**  
例如，下面的表，因为pk uk没有共同的列，所以不能做成分区表，改正的方法是，在pk包含name，然后用name作为分区表达式使用的列，或者在uk里包含id，然后用id作为分区表达式使用的列。详见：  
[22.6.1 Partitioning Keys, Primary Keys, and Unique Keys][22.6.1 Partitioning Keys_ Primary Keys_ and Unique Keys]

CREATE TABLE tnp (
        id INT NOT NULL AUTO_INCREMENT,
        ref BIGINT NOT NULL,
        name VARCHAR(255),
        PRIMARY KEY pk (id),
        UNIQUE KEY uk (name)
    );

分区的好处：

1.  分区使在一个表中存储更多数据成为可能，相比于可以保存在单个磁盘或文件系统分区。
2.  想要删除的数据，很容易被删除，通过删除只包含该数据的分区。相反，某些情况下可以通过添加一个或多个新分区以专门存储那个数据。
3.  由于满足给定WHERE子句的数据只能存储在一个或多个分区上，这将自动从搜索中排除任何剩余分区，因此可以极大地优化某些查询。因为分区可以在创建分区表后更改，所以可以重新组织数据以增强在首次设置分区方案时可能不经常使用的频繁查询。这种排除不匹配分区（以及它们包含的任何行）的能力通常被称为分区修建。见：[22.4 Partition Pruning][]

另外，MySQL支持显式的分区选择查询。例如，`SELECT * FROM t PARTITION (p0, p1) WHERE c < 5`。分区也支持DELETE， INSERT， REPLACE， UPDATE， LOAD DATA…

[13.1.18 CREATE TABLE Statement]: https://dev.mysql.com/doc/refman/5.7/en/create-table.html
[22.6 Restrictions and Limitations on Partitioning]: https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html
[22.6.1 Partitioning Keys_ Primary Keys_ and Unique Keys]: https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-partitioning-keys-unique-keys.html
[22.4 Partition Pruning]: https://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning.html