MySQL パーティション関数の詳細な説明と例の分析

まず、データベースパーティショニングとは何でしょうか?

以前、MySQL のテーブルパーティショニングに関する記事を書きました。ここでは、MySQL を例に、データベースパーティショニングとは何かについて説明します。 mysql データベースのデータは、ファイルの形式でディスクに保存され、デフォルトでは /mysql/data の下に置かれます (my.cnf の datadir から表示できます)。テーブルは主に 3 つのファイルに対応しており、1 つはテーブル構造を保存する frm、1 つはテーブルデータを保存する myd、そして 1 つはテーブルインデックスを保存する myi です。テーブル内のデータ量が多すぎると、myd と myi が非常に大きくなり、データの検索が非常に遅くなります。このとき、MySQL のパーティショニング機能を使用して、このテーブルに対応する 3 つのファイルを物理的に多数の小さなブロックに分割することができます。このようにして、データを検索するときに、すべてのデータを検索する必要はありません。データがどのブロックにあるかを知って、そのブロックで検索するだけで済みます。テーブル内のデータが大きすぎて 1 つのディスクに収まらない場合は、データを別のディスクに分散できます。

パーティション分割の2つの方法

1. 水平分割

水平分割とは何ですか?つまり、パーティション分割は水平方向に行われます。たとえば、100 万個のデータ項目がある場合、それらは 10 個の部分に分割されます。最初の 100,000 個のデータ項目は最初のパーティションに配置され、次の 100,000 個のデータ項目は 2 番目のパーティションに配置されます。つまり、テーブルを 10 個の部分に分割し、テーブルを分割するために merge を使用するという、少し似たような機能です。データが取得されると、テーブル構造内のすべてのフィールドが含まれます。つまり、水平分割によってテーブルの構造は変更されません。

2. 垂直分割

垂直分割とは何ですか?つまり、パーティショニングは垂直に行われます。たとえば、ユーザーテーブルを設計するときに、最初から考えを巡らさず、すべての個人情報を 1 つのテーブルにまとめました。このようにすると、このテーブルには個人プロファイルなどの比較的大きなフィールドが存在し、これらのプロファイルは多くの人に読まれない可能性があります。そのため、誰かがそれらを読みたいときは、検索して、テーブルを分割するときに大きなフィールドを分離することができます。

データベースのパーティショニングはリンゴを切るようなものだと思います。水平に切るか、垂直に切るかは個人の好みによります。MySQL が提供するパーティショニングは最初のタイプである水平パーティショニングに属し、さまざまな方法に細分化されています。次の例でこれを説明します。

2番目、mysqlパーティション

MySQL をパーティション分割する方法は 1 つしかないと思います。異なるアルゴリズムとルールを使用して、データを異なるブロックに分散するだけです。

1. MySQL 5.1以降はパーティショニング機能をサポートしています

インストール時に確認できる

[root@BlackGhost mysql-5.1.50]# ./configure --help |grep -A 3 パーティション 
=== パーティションのサポート === 
プラグイン名: パーティション 
説明: MySQL パーティショニング サポート 
ビルドをサポート: 静的 
構成: max、max-no-ndb

確認してください。上記のようなものが見つかった場合、パーティション分割がサポートされており、デフォルトでオンになっていることを意味します。すでにmysqlをインストールしている場合

mysql> "%part%"のような変数を表示します。 
+-------------------+------+ 
| 変数名 | 値 | 
+-------------------+------+ 
| パーティション分割あり | はい | 
+-------------------+------+ 
セット内の 1 行 (0.00 秒)

変数を確認してください。サポートされている場合は、上記のプロンプトが表示されます。

2. 範囲分割

RANGE によってパーティション化されたテーブルは、各パーティションに、パーティション化式の値が指定された連続した間隔内に含まれる行が含まれるようにパーティション化されます。

//範囲パーティションテーブルを作成するmysql> CREATE TABLE IF NOT EXISTS `user` ( 
 -> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ユーザーID', 
 -> `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Name', 
 -> `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0は男性、1は女性', 
 -> 主キー (`id`) 
 -> ) ENGINE=MyISAM デフォルト CHARSET=utf8 AUTO_INCREMENT=1 
 -> 範囲によるパーティション分割 (id) ( 
 -> パーティション p0 の値が (3) 未満である、 
 -> パーティション p1 の値が (6) 未満である、 
 -> パーティション p2 の値が (9) 未満である、 
 -> パーティション p3 の値が (12) 未満である、 
 -> パーティション p4 の値が MAXVALUE 未満です 
 -> ); 
クエリは正常、影響を受けた行は 0 行 (0.13 秒) 
 
//データを挿入mysql> INSERT INTO `test`.`user` (`name` ,`sex`)VALUES ('tank', '0') 
 -> ,('zhang',1),('ying',1),('张',1),('映',0),('test1',1),('tank2',1) 
 -> 、('タンク1',1)、('テスト2',1)、('テスト3',1)、('テスト4',1)、('テスト5',1)、('タンク3',1) 
 -> 、('タンク4',1)、('タンク5',1)、('タンク6',1)、('タンク7',1)、('タンク8',1)、('タンク9',1) 
 -> 、('タンク10',1)、('タンク11',1)、('タンク12',1)、('タンク13',1)、('タンク21',1)、('タンク42',1); 
クエリは正常、25 行が影響を受けました (0.05 秒) 
記録: 25 重複: 0 警告: 0 
 
//データベーステーブルファイルが保存されている場所に移動します。my.cnf に設定があります。datadir が [root@BlackGhost test] の後に# ls |grep user |xargs du -sh 
4.0K ユーザー#P#p0.MYD 
4.0K ユーザー#P#p0.MYI 
4.0K ユーザー#P#p1.MYD 
4.0K ユーザー#P#p1.MYI 
4.0K ユーザー#P#p2.MYD 
4.0K ユーザー#P#p2.MYI 
4.0K ユーザー#P#p3.MYD 
4.0K ユーザー#P#p3.MYI 
4.0K ユーザー#P#p4.MYD 
4.0K ユーザー#P#p4.MYI 
12K ユーザー.frm 
4.0K ユーザー.par 
 
//データを取得mysql> select count(id) as count from user; 
+-------+ 
| カウント | 
+-------+ 
| 25 | 
+-------+ 
セット内の 1 行 (0.00 秒) 
 
// 4 番目のパーティションを削除します。mysql> alter table user drop part p4; 
クエリは正常、影響を受けた行は 0 行 (0.11 秒) 
レコード: 0 重複: 0 警告: 0 
 
/**パーティションに保存されたデータは失われています。4番目のパーティションには14個のデータがあり、残りの3つのパーティションには11個のデータしかありません。ただし、ファイルサイズはすべて4.0Kです。このことから、パーティションの最小ブロックサイズは4Kであることがわかります。 
*/ 
mysql> count(id) を user から count として選択します。 
+-------+ 
| カウント | 
+-------+ 
| 11 | 
+-------+ 
セット内の 1 行 (0.00 秒) 
 
//4番目のブロックが削除されました [root@BlackGhost test]# ls |grep user |xargs du -sh 
4.0K ユーザー#P#p0.MYD 
4.0K ユーザー#P#p0.MYI 
4.0K ユーザー#P#p1.MYD 
4.0K ユーザー#P#p1.MYI 
4.0K ユーザー#P#p2.MYD 
4.0K ユーザー#P#p2.MYI 
4.0K ユーザー#P#p3.MYD 
4.0K ユーザー#P#p3.MYI 
12K ユーザー.frm 
4.0K ユーザー.par 
 
/* 既存のテーブルをパーティション化し、ルールに従ってテーブル内のデータを対応するパーティションに自動的に割り当てることができます。これはより優れており、多くのものを節約できます。次の操作を参照してください*/ 
mysql> テーブル aa のパーティションを RANGE(id) で変更します 
 -> (PARTITION p1 VALUES は (1) 未満です) 
 -> パーティションp2の値が（5）未満である、 
 -> PARTITION p3 VALUES が MAXVALUE より小さい); 
クエリは正常、15 行が影響を受けました (0.21 秒) // パーティション 15 データ レコード: 15 重複: 0 警告: 0 
 
//合計で 15 件のレコードがありますmysql> select count(*) from aa; 
+----------+ 
| カウント(*) | 
+----------+ 
| 15 | 
+----------+ 
セット内の 1 行 (0.00 秒) 
 
//パーティションを削除しますmysql> alter table aa drop part p2; 
クエリは正常、影響を受けた行は 0 行 (0.30 秒) 
レコード: 0 重複: 0 警告: 0 
 
// エントリは 11 個しかないため、既存のテーブル パーティションが成功していることがわかります。mysql> select count(*) from aa; 
+----------+ 
| カウント(*) | 
+----------+ 
| 11 | 
+----------+ 
セット内の 1 行 (0.00 秒)

3. パーティションの一覧

LIST パーティション分割における各パーティションの定義と選択は、値リストセット内の値に属する列の値に基づいて行われますが、RANGE パーティション分割は連続した間隔値のセットに属します。

// このメソッドは失敗しますmysql> CREATE TABLE IF NOT EXISTS `list_part` ( 
 -> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ユーザーID', 
 -> `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省', 
 -> `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Name', 
 -> `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0は男性、1は女性', 
 -> 主キー (`id`) 
 -> ) ENGINE=INNODB デフォルト CHARSET=utf8 AUTO_INCREMENT=1 
 -> リストによるパーティション (province_id) ( 
 -> パーティション p0 の値は (1,2,3,4,5,6,7,8) です。 
 -> パーティション p1 の値は (9,10,11,12,16,21) です。 
 -> パーティション p2 の値は (13,14,15,19) にあります。 
 -> パーティション p3 の値は (17,18,20,22,23,24) です 
 -> ); 
エラー 1503 (HY000): 主キーには、テーブルのパーティション関数のすべての列を含める必要があります。 
 
//このメソッドは成功しますmysql> CREATE TABLE IF NOT EXISTS `list_part` ( 
 -> `id` int(11) NOT NULL COMMENT 'ユーザーID', 
 -> `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省', 
 -> `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Name', 
 -> `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0 は男性、1 は女性' 
 -> ) エンジン=INNODB デフォルト文字セット=utf8 
 -> リストによるパーティション (province_id) ( 
 -> パーティション p0 の値は (1,2,3,4,5,6,7,8) です。 
 -> パーティション p1 の値は (9,10,11,12,16,21) です。 
 -> パーティション p2 の値は (13,14,15,19) にあります。 
 -> パーティション p3 の値は (17,18,20,22,23,24) です 
 -> ); 
クエリは正常、影響を受けた行は 0 行 (0.33 秒)

上記のリストパーティションを作成するときに、主キーがある場合は、その主キーをパーティションに含める必要があります。そうでない場合は、エラーが報告されます。主キーを使用しない場合、パーティションは正常に作成されます。一般的に、テーブルには主キーが必要です。これはパーティションの制限です。

データをテストする場合は、範囲パーティションテストを参照してください。

4. ハッシュパーティション

HASH パーティション分割は主に、データが所定の数のパーティションに均等に分散されるようにするために使用されます。ハッシュする列値または列値に基づく式を指定し、パーティションテーブルを分割するパーティションの数を指定するだけです。

mysql> `hash_part`が存在しない場合はテーブルを作成します（ 
 -> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'コメントID', 
 -> `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT 'コメント', 
 -> `ip` varchar(25) NOT NULL DEFAULT '' COMMENT 'ソースIP', 
 -> 主キー (`id`) 
 -> ) ENGINE=INNODB デフォルト CHARSET=utf8 AUTO_INCREMENT=1 
 -> ハッシュ(id)によるパーティション 
 -> パーティション 3; 
クエリは正常、影響を受けた行は 0 行 (0.06 秒)

テストについては、範囲パーティションの操作を参照してください。

5. キーパーティション

KEY によるパーティション分割は HASH によるパーティション分割と似ていますが、HASH パーティション分割ではユーザー定義の式が使用され、KEY パーティション分割のハッシュ関数は MySQL サーバーによって提供される点が異なります。

mysql> `key_part`が存在しない場合はテーブルを作成します（ 
 -> `news_id` int(11) NOT NULL COMMENT 'ニュースID', 
 -> `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'ニュースコンテンツ', 
 -> `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT 'ソースIP', 
 -> `create_time` 日付 NULL ではない デフォルト '0000-00-00 00:00:00' コメント '時間' 
 -> ) エンジン=INNODB デフォルト文字セット=utf8 
 -> リニアハッシュによるパーティション(YEAR(create_time)) 
 -> パーティション 3; 
クエリは正常、影響を受けた行は 0 行 (0.07 秒)

テストについては、範囲パーティションの操作を参照してください。

6. サブパーティション

サブパーティション分割は、パーティションテーブル内の各パーティションをさらに分割することです。サブパーティション分割では、HASH パーティション分割または KEY パーティション分割のいずれかを使用できます。これは複合パーティションとも呼ばれます。

1. パーティション内にサブパーティションを作成する場合、他のパーティションにもサブパーティションを作成する必要があります。

2. パーティションを作成する場合、各パーティション内のサブパーティションの数は同じである必要があります。

3. 同じパーティション内のサブパーティションの名前は異なりますが、異なるパーティション内のサブパーティションの名前は同じにすることができます (5.1.50 には適用されません)

mysql> `sub_part`が存在しない場合はテーブルを作成します（ 
 -> `news_id` int(11) NOT NULL COMMENT 'ニュースID', 
 -> `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'ニュースコンテンツ', 
 -> `u_id` int(11) NOT NULL DEFAULT 0s COMMENT 'Source IP', 
 -> `create_time` 日付 NULL ではない デフォルト '0000-00-00 00:00:00' コメント '時間' 
 -> ) エンジン=INNODB デフォルト文字セット=utf8 
 -> 範囲によるパーティション分割(YEAR(create_time)) 
 -> ハッシュ(TO_DAYS(create_time))によるサブパーティション( 
 -> パーティション p0 の値は (1990)(サブパーティション s0、サブパーティション s1、サブパーティション s2) より小さい 
 -> パーティション p1 の値は (2000)(サブパーティション s3、サブパーティション s4、サブパーティション good) 未満です。 
 -> パーティション p2 の値は MAXVALUE(サブパーティション tank0、サブパーティション tank1、サブパーティション tank3) より小さい 
 -> ); 
クエリは正常、影響を受けた行は 0 行 (0.07 秒)

公式ウェブサイトでは、異なるパーティション内のサブパーティションに同じ名前を付けることができると記載されていますが、MySQL 5.1.50 ではこれが許可されておらず、次のエラーが表示されます。

エラー 1517 (HY000): パーティション名 s1 が重複しています

3. パーティション管理

1. パーティションを削除する

1.mysql> テーブルユーザーを変更してパーティション p4 を削除します。

2. 新しいパーティションを追加する

//範囲新しいパーティションを追加mysql> alter table user addpartition(partition p4 values less than MAXVALUE); 
クエリは正常、影響を受けた行は 0 行 (0.06 秒) 
レコード: 0 重複: 0 警告: 0 
 
//リストに新しいパーティションを追加しますmysql> alter table list_part addpartition(partition p4 values in (25,26,28)); 
クエリは正常、影響を受けた行は 0 行 (0.01 秒) 
レコード: 0 重複: 0 警告: 0 
 
//ハッシュ再パーティションmysql> alter table hash_part add partition パーティション 4; 
クエリは正常、影響を受けた行は 0 行 (0.12 秒) 
レコード: 0 重複: 0 警告: 0 
 
//キーの再パーティション化mysql> alter table key_part add partition パーティション 4; 
クエリは正常、1 行が影響を受けました (0.06 秒) //一部のデータは再割り当てされます。レコード: 1 重複: 0 警告: 0 
 
//サブパーティション化により新しいパーティションが追加されます。サブパーティションを指定しなかった場合でも、システムによってサブパーティションに名前が付けられます。mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE); 
クエリは正常、影響を受けた行は 0 行 (0.02 秒) 
レコード: 0 重複: 0 警告: 0 
 
mysql> show テーブル sub1_part\G; を作成します。 
************************** 1. 行 **************************** 
 テーブル: sub1_part 
テーブルの作成: CREATE TABLE `sub1_part` ( 
 `news_id` int(11) NOT NULL COMMENT 'ニュースID', 
 `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'ニュースコンテンツ', 
 `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT 'ソースIP', 
 `create_time` 日付 NOT NULL デフォルト '0000-00-00' コメント '時間' 
) エンジン=InnoDB デフォルト文字セット=utf8 
!50100 範囲によるパーティション (YEAR(create_time)) 
ハッシュによるサブパーティション (TO_DAYS(create_time)) 
(パーティション p0 値が (1990) 未満 
 (サブパーティション s0 エンジン = InnoDB、 
 サブパーティション s1 エンジン = InnoDB、 
 サブパーティション s2 エンジン = InnoDB)、 
 パーティション p1 の値が (2000) 未満 
 (サブパーティション s3 エンジン = InnoDB、 
 サブパーティション s4 エンジン = InnoDB、 
 サブパーティション良好エンジン = InnoDB)、 
 パーティション p2 の値が (3000) 未満 
 (サブパーティション tank0 エンジン = InnoDB、 
 サブパーティション tank1 エンジン = InnoDB、 
 サブパーティション tank3 エンジン = InnoDB)、 
 パーティション p3 の値が MAXVALUE 未満です 
 (SUBPARTITION p3sp0 ENGINE = InnoDB, //サブパーティション名は自動的に生成されます SUBPARTITION p3sp1 ENGINE = InnoDB, 
 サブパーティション p3sp2 エンジン = InnoDB)) 
セット内の 1 行 (0.00 秒)

3. 再パーティション

//範囲再パーティション化mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE); 
クエリは正常、11 行が影響を受けました (0.08 秒) 
記録: 11 重複: 0 警告: 0 
 
//リストを再パーティション化mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5)); 
クエリは正常、影響を受けた行は 0 行 (0.28 秒) 
レコード: 0 重複: 0 警告: 0 
 
//ハッシュ パーティションとキー パーティションは REORGANIZE できません。公式 Web サイトで非常に明確に説明されています。mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9; 
エラー 1064 (42000): SQL 構文にエラーがあります。MySQL サーバーのバージョンに対応するマニュアルで、1 行目の 'PARTITION 9' 付近で使用する正しい構文を確認してください。

第4に、パーティション分割の利点

1. パーティションを複数のディスクに分割して、より大きなストレージ容量を確保できます。

2. 検索条件、つまり where の後の条件に従って、対応するパーティションのみを完全に検索する必要はありません。

3. ビッグデータ検索時に並列処理が可能。

4. データクエリを複数のディスクに分散して、クエリスループットを向上させる

上記のMySQLパーティション機能の詳細な説明と例の分析は、編集者が皆さんと共有するすべてのコンテンツです。参考になれば幸いです。また、123WORDPRESS.COMを応援していただければ幸いです。

以下もご興味があるかもしれません: