ホームページ > 記事 > データベース > mysql のパーティションの詳細な紹介

mysql のパーティションの詳細な紹介

零下一度オリジナル: 2017-05-08 14:58:191495ブラウズ

概要

以前、パーティションを見たときにつまんでみたのですが、とても印象的でした。昨日やっとパーティション分割とは何かを学びましたが、今日要約すると、良い記憶力は悪い文章ほど優れているわけではありません。
MySQLは5.1からパーティショニング機能をサポートしています。パーティショニングに関する一文は次のとおりです。 ストレージ用に、特定のルール (範囲/リスト/ハッシュ/キーなど) に従ってテーブルを複数の領域 (ページ/ファイル) に分割します。 MySQL アプリケーション開発の場合、パーティショニングと非パーティショニングの間に違いはありません (つまり、アプリケーションに対して透過的です)。突破戦で言うところの「粉々に砕く」ようなものです。 MySQL は、ほとんどのストレージエンジン (MyISAM、InnoDB、Memory など) によるパーティションの作成をサポートしていますが、MERGE および CSV によるパーティションの作成はサポートしていません。同じパーティションテーブル内のすべてのパーティションは、同じストレージエンジンのものである必要があります。例を示します: 把一张表按照某种规则（range/list/hash/key等）分成多个区域（页/文件）保存。对mysql应用开发来说，分区与不分区是没区别的（即对应用是透明的）。如同突围战中的“化整为零”。MySQL支持大部分的存储引擎（如：MyISAM、InnoDB、Memory等）创建分区，不支持MERGE和CSV来创建分区。同一个分区表中的所有分区必须是同一个存储引擎。做一个引例：

#创建一个5个hash分区的myisam表
CREATE TABLE `test`.`partition_t1`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY HASH(MONTH(birth_date))
PARTITIONS 5;

mysql のパーティションの詳細な紹介

引例结果

分区作用

可以存储更多的数据（系统单个文件最大限制）
优化查询，在where子句中，如果包含分区条件，只需要扫描一个或部分分区来提高查询效率。在涉及sum()这类函数时候，可以在分区上并行处理，最后汇总结果。
对于过期或不需要的数据，可以删除相关分区来快速删除数据。
跨多个磁盘来分散数据查询，单表的并发能力提高了，磁盘I/O性能也提高了。

分区类型

分为4种：

range分区：基于一个给定的连续区间范围，把数据分配到不同的分区中。
list分区：类似range分区，区别在于list是基于枚举出的值列表分区，range是根据范围来分区的。
hash分区：基于给定的分区个数，把数据分配到不同分区（取模/线性）
key分区：类似于hash分区。

MySQL5.1中range,list,hash分区要求分区键必须是int。MySQL5.5及以上，支持非整型的range和list分区，即：range columns 和 list columns。
注意：无论哪种分区，要么分区表上没有主键/唯一键，要么分区键必须有一个是主键/唯一键。

1.range分区

range分区是利用取值范围（区间）划分分区，区间要连续并且不能互相重叠，使用values less than操作符进行分区定义。

例一：

CREATE TABLE `test`.`partition_t2`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY RANGE(id)(
   PARTITION t21 VALUES LESS THAN (10),
   PARTITION t22 VALUES LESS THAN (20),
   PARTITION t23 VALUES LESS THAN MAXVALUE
);

上例中定义了一个包含3个分区（t21,t22,t23）的range分区表，这个有点类似与高级语言中的<a href="http://www.php.cn/code/5745.html" target="_blank">switch语句</a>。解释如下：当id0764ea8dfb248dd6bd28afbeac2bbc14id>=10的时候，在t22分区；当id>=20时候，在t23分区。

例二：

CREATE TABLE `test`.`partition_t3`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY RANGE COLUMNS(birth_date)(
   PARTITION t31 VALUES LESS THAN (&#39;1996-01-01&#39;),
   PARTITION t32 VALUES LESS THAN (&#39;2006-01-01&#39;),
   PARTITION t33 VALUES LESS THAN (&#39;2038-01-01&#39;)
);

MySQL5.5改进range分区，提供range columns分区支持非整数分区。

2.list分区

list分区创建离散的值列表（类似mysql中的enum类型数据）来划分分区，使用values in操作符来分区。list分区不必要声明任何特定的顺序的。list有很多方面类似于range。

CREATE TABLE `test`.`partition_t4`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY LIST(id)(
   PARTITION t41 VALUES IN (1,2),
   PARTITION t42 VALUES IN (3,6),
   PARTITION t43 VALUES IN (5,4),
   PARTITION t44 VALUES IN (7,8)
);

上面的例子是，当id为1或2，在t41分区；当id为3或6，在t42分区，以此类推...

3.hash分区

hash分区主要用来分散热点读取，确保数据在预定确定个数分区中尽可能的平均分布。一个表执行hash分区，mysql会对分区键应用一个散列函数，以此确定数据应该放在n个分区中的哪一个分区。hash分区支持两种散列函数（分区方式）：取模算法（默认hash分区方式）和线性的2的幂的运算法则（liner hash 分区）

#顶部引例就是常规hash分区

mysql のパーティションの詳細な紹介

引用結果

パーティショニング機能

where 句

にパーティション条件が含まれている場合、クエリ効率を向上させるために、パーティションの 1 つまたは一部のみをスキャンする必要があります。 sum() などの関数に関しては、パーティション上で並列処理でき、最終的に結果がまとめられます。

期限切れのデータや不要なデータについては、関連するパーティションを削除
してすぐに削除できますデータ。
データクエリを複数のディスクに分散し、単一テーブルの同時実行機能を向上させ、ディスク I/O パフォーマンスを向上させます。
🎜

🎜🎜パーティションタイプ🎜🎜🎜 は 4 つのタイプに分類されます: 🎜🎜🎜🎜 レンジパーティショニング: 指定された連続間隔範囲に基づいて、データを異なるパーティションに割り当てます。 🎜🎜🎜🎜リストのパーティショニング: レンジのパーティショニングと似ていますが、リストが列挙値リストに基づいてパーティショニングされ、範囲が範囲に基づいてパーティショニングされる点が異なります。 🎜🎜🎜🎜ハッシュパーティショニング: 指定されたパーティション数に基づいて、データを異なるパーティションに割り当てます(モジュロ/リニア) 🎜🎜🎜🎜キーパーティショニング: ハッシュパーティショニングに似ています。 🎜🎜🎜 MySQL 5.1 では、範囲、リスト、およびハッシュパーティショニングでは、パーティションキーが int である必要があります。 MySQL 5.5 以降では、非整数範囲およびリストパーティション、つまり範囲カラムとリストカラムがサポートされています。 🎜🎜注: どのような種類のパーティション分割であっても、パーティションテーブルに主キー/一意キーが存在しないか、パーティションキーの 1 つが主キー/一意キーである必要があります。 🎜🎜

🎜1. 範囲パーティショニング🎜

🎜 範囲パーティショニングでは、値の範囲 (間隔) を使用してパーティションを分割します。間隔は連続している必要があり、互いに重複することはできません。未満 code>演算子 🎜 を使用してパーティションを定義します。 🎜🎜🎜例 1: 🎜🎜

CREATE TABLE `test`.`partition_t5`(  
`id` INT UNSIGNED NOT NULL,
`username` VARCHAR(30) NOT NULL,
`email` VARCHAR(30) NOT NULL,
`birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY LINEAR HASH(id)
PARTITIONS 5;

🎜上記の例は、3 つのパーティション (t21、t22、t23) を含む範囲パーティションテーブルを定義します。

これは、高級言語の <a href="http:%20/%20%E3%81%AB%E4%BC%BC%E3%81%A6%E3%81%84%E3%81%BE%E3%81%99%E3%80%82%20/www.php.cn/code/5745.html" target="_blank">switch ステートメント🎜</a>

。説明は次のとおりです。idid>=10 の場合は t22 パーティションにあり、id>=20 の場合は t23 パーティションにあります。 🎜🎜🎜例 2: 🎜🎜

CREATE TABLE `test`.`partition_t6`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY LINEAR KEY(email)
PARTITIONS 5;

🎜MySQL5.5 は範囲パーティショニングを改善し、整数 🎜 パーティション。 🎜

🎜2.list パーティション 🎜

🎜list パーティションは、値の離散リスト (mysql の enum 型データと同様) を作成してパーティション化し、values in を使用します。パーティション化する演算子。リストパーティションは、特定の順序で宣言する必要はありません。リストは多くの点で範囲と似ています。 🎜

CREATE TABLE `test`.`partition_t7`(  
  `a` INT UNSIGNED NOT NULL,
  `b` INT UNSIGNED NOT NULL
)
PARTITION BY RANGE COLUMNS(a,b)(
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,10),
    PARTITION p2 VALUES LESS THAN (10,20),
    PARTITION p3 VALUES LESS THAN (10,35),
    PARTITION p4 VALUES LESS THAN (10,MAXVALUE),
    PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

🎜上記の例では、ID が 1 または 2 の場合は t41 に分割され、ID が 3 または 6 の場合は t42 に分割され、以下同様です...🎜

🎜3.hashパーティション🎜

🎜ハッシュパーティションは、主にホットスポット読み取りを分散して、データが所定の数のパーティション間でできるだけ均等に分散されるようにするために使用されます。テーブルがハッシュパーティショニングを実行すると、MySQL はパーティションキーにハッシュ関数を適用して、n 個のパーティションのどれにデータを配置するかを決定します。ハッシュパーティションは、 モジュロアルゴリズム (デフォルトのハッシュパーティション化方法) と 2 の線形累乗アルゴリズム (線形ハッシュパーティション化) の 2 つのハッシュ関数 (パーティション化方法) をサポートします。 🎜🎜通常のハッシュ分割🎜

CREATE TABLE partition_t8(id INT,purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH(TO_DAYS(purchased))
SUBPARTITIONS 2(
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

🎜🎜🎜mysql では、複数のカラムを含むハッシュ🎜式🎜の使用を推奨しません。 🎜🎜🎜🎜従来のハッシュはパーティション管理に多大なコストをもたらし、柔軟なパーティションのニーズには適していません。参照: 一貫性のあるハッシュアルゴリズム 🎜🎜🎜🎜 従来のハッシュパーティショニングの管理上の問題のため、すべての mysql では線形ハッシュパーティショニングが導入されています。 🎜🎜線形ハッシュパーティション🎜

#删除分区
alter table partition_t8 drop partition p2;
#添加一个分区
alter table partition_t8 add partition(
    partition p4 values less than (2030)
    )
#重定义一个分区
alter table partition_t8 reorganize partition p3 into(
    partition p2 values less than (2005),
    partition p3 values less than (2015)
);

🎜 上記の例では、5 つのパーティションからなる線形ハッシュパーティションが作成されます。 🎜🎜🎜🎜線形ハッシュパーティショニングの利点: MySQL はパーティションのメンテナンスをより速く処理できます 🎜

线性hash分区缺点：分区各个分区之间数据分布不太均衡。

4.key分区

hash分区允许用户自定义的表达式，而key分区不允许使用用户自定义的表达式。

hash分区只支持整数分区，key分区支持除了blob或text类型之外的其他数据类型分区。
与hash分区不同，创建key分区表的时候，可以不指定分区键，默认会选择使用主键/唯一键作为分区键，没有主键/唯一键,必须指定分区键。

CREATE TABLE `test`.`partition_t6`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY LINEAR KEY(email)
PARTITIONS 5;

columns 与子分区

1.columns分区

columns 包括range columns与list columns 支持非整型的分区键。columns分区支持多列分区。

CREATE TABLE `test`.`partition_t7`(  
  `a` INT UNSIGNED NOT NULL,
  `b` INT UNSIGNED NOT NULL
)
PARTITION BY RANGE COLUMNS(a,b)(
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,10),
    PARTITION p2 VALUES LESS THAN (10,20),
    PARTITION p3 VALUES LESS THAN (10,35),
    PARTITION p4 VALUES LESS THAN (10,MAXVALUE),
    PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

判断依据：（a<10） or ((1=10) and (10<10))。

2.子分区

子分区是分区表中对每一个分区的再次分割，又被称为复合分区。MySQL从MySQL5.1开始支持对通过range和list的表再进行子分区，子分区即可以hash分区，也可以使用key分区。子分区适合保存非常大量的数据记录。

CREATE TABLE partition_t8(id INT,purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH(TO_DAYS(purchased))
SUBPARTITIONS 2(
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

mysql のパーティションの詳細な紹介

mysql子分区

分区管理

MySQL5.1提供添加、删除、重定义、合并、拆分分区命令。

1.range或list分区

#删除分区
alter table partition_t8 drop partition p2;
#添加一个分区
alter table partition_t8 add partition(
    partition p4 values less than (2030)
    )
#重定义一个分区
alter table partition_t8 reorganize partition p3 into(
    partition p2 values less than (2005),
    partition p3 values less than (2015)
);

只能从range分区列表最大端增加分区。
增加list分区，不能添加一个包含现有分区值列表中的任意值分区，也就是说对一个固定的分区键值，必须指定并且只能指定一个唯一的分区。
重新定义range分区，只能够重新定义相邻的分区，同时重新定义的分区区间必须和原分区区间覆盖相同的区间。

2.hash或key分区

#减少分区数,(如将分区数减少到2)
alter table partition_t8 coalesce partition 2;
#增加分区数(如：为分区数增加了8)
alter table partiton_t8 add partition partitions 8;

coalesce不能用来增加分区数量。

【相关推荐】

1. 免费mysql在线视频教程

2. MySQL最新手册教程

3. 数据库设计那些事

以上がmysql のパーティションの詳細な紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。

声明：

この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。

前の記事：MySQL トリガービュートリガー次の記事：MySQL トリガービュートリガー

続きを見る