mysql分区一般多少个 mysql最多多少个分区

转载

mob6454cc641ffd 2023-10-14 07:44:23

文章标签 mysql分区一般多少个数据库运维操作系统 MySQL 文章分类 MySQL 数据库

参考:http://bbs.51cto.com/thread-1080714-1.html

MySQL 从5.1 版本开始支持分区的功能。分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言，逻辑上只有一个表或一个索引，但是实际上这个表可能是由数10个物理分区对象组成，每个分区都是一个独立的对象，可以独自处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。

MySQL 分区的优点主要包括以下4个方面。

　　和单个磁盘或者文件系统分区相比，可以存储更多的数据。

　　优化查询。在 WHERE 子句中包含分区条件时，可以只扫描必要的一个或多个分区来提高查询效率；同时在涉及 SUM() 和 COUNT() 这类聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果。

　　对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据。

　　跨多个磁盘来分散数据查询，以获得更大的查询吞吐量。

　　注意：在 MySQL 5.1 版本中，分区的实现仍然是 pre-alpha 版本，该版本中分区特性不是特别适用于生产环境。

1) 分区概述

分区有利于管理非常大的表。分区引入了分区键(partition key)的概念，分区键用于根据某个区间值(或者范围值)、特定值列表或者 HASH 函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象变成小对象。
MySQL 可以通过下面语句判断是否支持分区(5.6 之前)：

　　　　　　SHOW VARIABLES LIKE '%partition%';

　　　　　　　　如果输出：

　　　　　　　　have_partitioning YES

　　　　　　　　表示支持分区。

　　　或者通过(5.6及之后)：

　　　　　　SHOW PLUGINS;

　　　　　　　　显示所有插件，如果有partition ACTIVE STORAGE ENGINE GPL 插件则表明支持分区

　　　注意:使用 SHOW ENGINES 命令的输出信息中，不会显示任何有关分区支持的信息，必须使用 SHOW VARIABLES 来判断当前版本是否支持分区。SHOW PLUGINS 命令的输出信息中能够检查到当前版本是否安装了分区插件。

MySQL支持使用大部分存储引擎(比如 MyISAM、InnoDB、Memory 等存储引擎)创建分区表；MySQL 不支持使用 MERGE 或 CSV 存储引擎来创建分区表。
在 MySQL 5.1 版本中，同一个分区表的所有分区必须使用同一个存储引擎；即同一个表上，不能对一个分区使用 MyISAM 引擎，对另一个分区使用 InnoDB；但是，可以在同一个 MySQL 服务器中，甚至同一个数据库中，对于不同的分区表使用不同的存储引擎。
和非分区表设置存储引擎一样，分区表设置存储引擎，只能用 [STORAGE] ENGINE 子句。 [STORAGE] ENGINE 子句必须列在 CREATE TABLE 语句中的其他任何分区选项之前。
MySQL 的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区；反过来也是一样的，不能只对索引分区而不对表分区，同时也不能只对表的一部分数据进行分区。MySQL 的分区表上创建的索引一定是本地 LOCAL 索引。

2) 分区类型（5.1）

　　RANGE 分区：基于一个给定连续区间范围，把数据分配到不同的分区。

　　LIST 分区：类似 RANGE 分区，区别在 LIST 分区是基于枚举出的值列表分区，RANGE 是基于给定的连续区间范围分区。

　　HASH 分区：基于给定的分区个数，把数据分配到不同的分区。

　　KEY 分区：类似于 HASH 分区。

　　在 MySQL 5.1 版本中，RANGE 分区、LIST 分区、HASH 分区都要求分区键必须是 INT 类型，或者通过表达式返回 INT 类型，也就是说 MySQL 5.1 仅仅支持整数分区，唯一的例外就是分区类型为 KEY 分区时，可以使用其他类型的列(BLOG 或 TEXT 列类型除外)作为分区键。

　　在 MySQL 5.5 或以上的版本中，已经支持非整数的 RANGE 和 LIST 分区了。

无论是哪种 MySQL 分区类型，要么分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包含分区键，也就是说不能使用主键/唯一键字段之外的其他字段分区。

　　分区的名字基本上遵循 MySQL 标识符的原则。MySQL 命名中的大小写敏感：在 MySQL 中，数据库和表对应于数据目录中的目录和文件。所以，操作系统的大小写敏感性决定数据库和表命名的大小写敏感。但是需要注意的是，分区的名字是不区分大小写的。

RANGE 分区

　　　　按照 RANGE 分区的表是利用取值范围将数据分成分区，区间要连续并且不能相互重叠，使用 VALUES LESS THAN 操作符进行分区定义。

　　　　可以在设置分区的时候使用 VALUES LESS THAN MAXVALUE 子句，该子句提供给所有大于明确指定的最高值的值，MAXVALUE 表示最大的可能的整数值。

　　　　在 RANGE 分区中，分区键如果是 NULL 值会被当做一个最小值来处理。

　　　　MySQL 5.1 支持整数列分区，要想在日期或者字符串列上进行分区，就得使用函数进行转换。但要是查询如果不用函数转换，那么就无法利用 RANGE 分区特性来提高查询性能。

RANGE COLUMNS

　　　　MySQL 5.1 分区日期处理上支持的函数只有两个 YEAR() 和 TO_DAYS()。

　　　　MySQL 5.5 日期处理上增加了支持函数 TO_SECONDS()，把日期转换成秒，从而能够实现比按天分区更细化的分区。

　　　　RANGE 分区功能特别适用于以下两种情况:

　　　　　　当需要删除过期的数据时，只需要简单的

　　　　　　　　　　ALTER TABLE table_name

　　　　　　　　　　DROP PARTITION partition_name

　　　　　　来删除partition_name 分区中的数据（同时也删除了该分区）。对于具有上百万条记录的表来说，删除分区要比运行一个 DELETE 语句有效的多。

　　　　　　经常运行包含分区键的查询，MySQL 可以很快地确定只有某一个或者某些分区需要扫描，因为其他分区不可能包含有符合该 WHERE 子句的任何记录。

1 create table rc4 (
2     a int
3 ) PARTITION by range(a) (
4     partition p01 values less than (0),
5     partition p02 values less than (10),
6     partition p03 values less than (20),
7     partition p04 values less than (35),
8     partition p05 values less than MAXVALUE
9 );

LIST 分区

　　　　LIST 分区是建立离散的值列表告诉数据库特定的值属于哪个分区，LIST 分区在很多方面类似于 RANGE 分区，区别在 LIST 分区是从属于一个枚举列表的值得集合，RANGE 分区是从属于一个连续区间值的集合。

　　　　LIST 分区通过使用 PARTITION BY LIST(expr) 子句来实现，expr 是某列值或一个基于某列值返回一个整数值的表达式，然后通过 VALUES IN(value_list) 的方式来定义分区，其中 value_list 是一个逗号分隔的整数列表。与 RANGE 分区不同，LIST 分区不必声明任何特定的顺序。

　　　　如果试图插入的列值（或者分区表达式的返回值）不包含分区值列表中时，那么 INSERT 操作会失败并报错。要重点注意的是，LIST 分期不存在类似 VALUES LESS THAN MAXVALUE 这样包含其他值在内的定义方式。将要匹配的任何值都必须在值列表中找得到。

1 create table expenses (
 2     expense_date DATE not null,
 3     category INT,
 4     amount DECIMAL(10,3)
 5 ) partition by list(category) (
 6     partition p0 values in (3,5),
 7     partition p1 values in (1,10),
 8     partition p2 values in (4,9),
 9     partition p3 values in (2),
10     partition p4 values in (6)
11 );

Columns 分区

Columns 分区可以细分为 RANGE Columns 分区和 LIST Columns 分区，RANGE Columns 分区和 LIST Columns 分区都支持整数、日期时间、字符串三大数据类型。

　　　　所有整数类型：tinyint、smallint、mediumint、int 和 bigint；其他数值类型都不支持，例如不支持 Decimal 和 Float。

　　　　日期时间类型：date 和 datetime。

　　　　字符类型：char、varchar、binary 和 varbinary；不支持 text 和 blob 类型作为分区键。

支持多列分区。

元祖的比较，也就是基于字段组的比较，这和之前 RANGE 分区键的比较有些差异。

　　　　其实，RANGE Columns 分区键的比较(元祖的比较)其实就是多列排序，先根据 a 字段排序再根据 b 字段排序，根据排序结果来分区存放数据。和 RANGE 单字段分区排序的规则实际上是一致的。

1 create table rc3 (
 2     a int,
 3     b INT
 4 ) PARTITION by range COLUMNS (a, b) (
 5     partition p01 values less than (0, 10),
 6     partition p02 values less than (10, 10),
 7     partition p03 values less than (10, 20),
 8     partition p04 values less than (10, 35),
 9     partition p05 values less than (10, MAXVALUE),
10     partition p06 values less than (MAXVALUE, MAXVALUE)
11 );

HASH 分区

　　　　HASH 分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。对一个表执行 HASH 分区时，MySQL 会对分区键应用一个散列函数，以此确定数据应当放在 N 个分区中的哪个分区中。

常规 HASH 使用的是取模算法，线性 HASH使用的是一个线性的 2 的幂的运算法则。

　　　　假设将要保存的记录的分区编号为 N，那么 N = MOD(expr, num)。

非常复杂的表达式可能会引起性能问题，MySQL 也不推荐使用涉及多列的哈希表达式。

常规 HASH 在分区管理上带来的代价太大了，不适合需要灵活变动分区的需求。

为了降低分区管理上的代价，MySQL 提供了线性 HASH 分区，分区函数是一个线性的 2 的幂运算法则。

　　　　线性 HASH 也可以计算出记录将保存的分区。

　　　　假设将要保存的分区编号为 N，num 是一个非负的整数，表示分割成分区的数量，那么 N 可以通过以下算法得到：

　　　　　　首先，找到下一个大于等于 num 的 2的幂，这个值设为 V，V可以通过下面的公式的到：

　　　　　　V = Power(2, Ceiling(Log(2, num))

　　　　　　其次，设置 N = F(column_list) & (V-1)

　　　　　　当 N >= num

　　　　　　设置 V = Ceiling(V/2)，设置 N = N & (V-1)

　　　　当线性 HASH 的分区个数是 2 的 N 次幂时，线性 HASH 的分区结果和常规 HASH 的分区结果是一致的。

线性 HASH 分区的优点是，在分区维护（包含增加、删除、合并、拆分分区）时，MySQL 能够处理的更加迅速；缺点是，对比常规 HASH 分区（取模）的时候，线性 HASH 各个分区之数据的分布不太均匀。

1 create table emp (
2     id int not null,
3     ename varchar(30),
4     hired date not null default '1970-01-01',
5     separated date not null default '9999-12-31',
6     job varchar(30) not null,
7     store_id int not NULL
8 ) partition by hash (store_id) partitions 4;

Key 分区

　　　　按照 Key 进行分区非常类似于按照 HASH 进行分区，只不过 HASH 分区允许使用用户自定义的表达式，而 Key 分区不允许使用用户自定义的表达式，需要使用 MySQL 服务器提供的 HASH 函数；同时 HASH 分区只支持整数分区，而 Key 分区支持使用出 BLOB or TEXT 类型外其他类型的列作为分区键。

与 HASH 分区不同，创建 Key 分区表的时候，可以不指定分区键，默认会首先选择使用主键作为分区键。没有主键的情况，会选择非空唯一键作为分区键。

　　　　注意：作为分区键的唯一键必须是非空的，如果不是非空的，依然会报错。

　　　　在没有主键也没有唯一键的情况下，就不能不指定分区键了。

　　　　在按照 Key 分区的分区表上，不能够执行 ALTER TABLE DROP PRIMARY KEY 来删除主键，MySQL 会返回错误 Field in list of fields for partition function not found in table.

　　　　和 HASH 分区类似，在 KEY 分区中使用关键字 LINEAR 具有同样的作用，也就是 LINEAR KEY 分区时，分区的编号是通过 2 的幂算法得到的，而不是通过取模得到的。

　　　　KEY 分区和 HASH 分区类似，在处理大量数据记录时，能够有效的分散热点。

1 CREATE table emp2 ( 
2     id int not null,
3     ename varchar(30),
4     hired date not null default '1970-01-01',
5     separated date not null default '9999-12-31',
6     job varchar(30) not null,
7     store_id int not NULL
8 ) partition by key (job) partitions 4;

子分区

子分区既可以使用 HASH 分区，也可以使用 KEY 分区。

复合分区适用于保存非常大量的数据记录。

MySQL 分区对 NULL 值的方式

　　　　MySQL 不禁止在分区键值上使用 NULL，分区键可能是一个字段或者一个用户定义的表达式。一般情况下，MySQL 的分区把 NULL 当做零值，或者一个最小值进行处理。

RANGE 分区中，NULL 值会被当做最小值来处理；LIST 分区中，NULL 值必须出现在枚举列表中，否则不被接受；HASH/KEY 分区中，NULL 值会被当做零值来处理。

推荐通过设置非空和默认值来绕开 MySQL 默认对 NULL 值的处理。

查看数据的分区分布情况

　　　　SELECT partition_name part, partition_expression expr, partition_description descr, table_rows
　　　　FROM INFORMATION_SCHEMA.partitions
　　　　WHERE TABLE_SCHEMA = schema()
　　　　AND TABLE_NAME='table_name';

3) 分区管理

　　　　MySQL 5.1 提供了添加、删除、重定义、合并、拆分分区的命令，这些操作都可以通过 ALTER TABLE 命令来实现。

RANGE & LIST 分区管理

　　　　在添加、删除、重新定义分区的处理上，RANGE 分区和 LIST 分区非常相似。

　　　　从一个 RANGE 或者 LIST 分区的表中删除一个分区，可以使用

　　　　　　　　ALTER TABLE table_name

　　　　　　　　DROP PARTITION partition_name

　　　　语句来实现。

　　　　删除分区的命令执行之后，并不显示从表中删除的行数，并不是真的没有记录被删除。

　　　　删除分区的同时，也删除了该分区中的所有数据。

　　　　删除 LIST 分区和删除 RANGE 分区使用的语句完全相同，只不过删除 LIST 分区之后，由于在 LIST 分区的定义中不在包含被已删除了的分区的值列表，所以后续无法写入包含已经删除了的分区的值列表的数据。

　　　　为一个 RANGE 或者 LIST 分区的表增加一个分区，可以使用

　　　　　　　　ALTER TABLE table_name

　　　　　　　　ADD PARTITON partition_name

对于 RANGE 分区来说，只能通过 ADD PARTITION 方式添加新的分区到分区列表的最大一段。

　　　　给 LIST 分区增加新分区的方式也类似。

　　　　增加 LIST 分区时，不能添加一个包含现有分区值列表中的任意值的分区，也就是说对一个固定的分区键值，必须指定并且只能指定一个唯一的分区，否则会出现错误。

MySQL 也提供了在不丢失数据的情况下，通过重新定义分区的语句 ALTER TABLE REORGANIZE INTO 重定义分区。

ALTER TABLE table_name
　　　　　　　　REORGANIZE PARTITION partition_name1[, partition_name2,...]
　　　　　　　　INTO (
　　　　　　　　　　PARTITION ...[,
　　　　　　　　　　...]
　　　　　　　　)

　　　　重新定义分区可以用来拆分一个 RANGE 分区为多个 RANGE 分区，也可以用来合并多个相邻 RANGE 分区为一个 RANGE 分区或者多个 RANGE 分区。

重新定义 RANGE 分区时，只能够重新定义相邻的分区，不能跳过某个 RANGE 分区进行重新定义，同时重新定义的分区区间必须和原分区区间覆盖相同的区间；也不能使用重新定义分区来改变表分区的类型。

　　　　同样的，对 LIST 分区，也可以使用 ALTER TABLE REORGANIZE PARTITION INTO 语句重新定义分区。

　　　　类似的重定义 RANGE 分区，重新定义 LIST 分区时，只能够重新定义相邻的分区，不能跳过 LIST 分区重新定义，同时重新定义的分区区间必须和原分区区间覆盖相同的区间；也不能使用重新定义分区来改变表分区的类型。例如，不能把 LIST 分区变为 RANGE 分区，也不能把 RANGE 分区变成 LIST 分区。