MySQL のテーブル パーティショニングの詳細な紹介

MySQL テーブルのパーティショニングはデータベースやテーブルの分割と同じであり、どちらもデータベースのスループットを向上させます。パーティショニングは、テーブル パーティショニングと似ています。テーブル パーティショニングは、大量のデータを含むテーブルを論理的に複数のテーブルに分割することであり、水平または垂直に分割できます。パーティショニングとは、テーブルのデータ ファイルを複数のデータ ファイルに分割することです。異なるデータは異なるファイルに分割されます。このようにして、非常に大量のデータを含むテーブルの場合、複数のデータ ファイルがストレージに使用されるため、データベースの IO パフォーマンスが向上します。

データテーブルのファイルを操作しているため、まず MySQL テーブルのストレージについて理解する必要があります。 MySQL には複数のストレージ エンジンがあり、異なるストレージ エンジンは異なるファイル形式を保存することがわかっています。ここでは主に InnoDB と MyISAM という 2 つのストレージ エンジンを使用して説明します。

InnoDB

.frm ファイルのデータテーブル構造

.idb ファイルテーブル データファイル、排他的なテーブルスペース、各テーブルには .idb ファイルがあります

.ibdata ファイルテーブル データファイル、共有テーブルスペース、すべてのテーブルはこのデータを使用します

ファイル

MyISAM

.frm ファイル データテーブル構造

.myd ファイル データファイル

.myi ファイルインデックスファイル

まず第一に、現在のデータベースのバージョンがパーティショニングをサポートしているかどうかを確認する必要があります

1 show variables like '%partition%';

パーティショニングの方法は?データベースの水平セグメンテーションを実行する場合、水平セグメンテーションは、指定されたフィールドの係数に従って異なるテーブルに分割したり、日付に従って分割したり、ID (1 ～ 100 万) に従ってセグメント化したりできることがわかります。 、2 番目の表では 100 万と 1 ～ 200 万というようになります。つまり、セグメンテーションを行う方法はたくさんあります。さらに、データベースはテーブル パーティションに関して選択できるさまざまなオプションも提供します。

MySQL テーブルパーティショニング戦略

RANGE パーティショニング 指定された連続範囲に属する列値に基づいて複数の行をパーティションに割り当てます

1 DROP TABLE IF EXISTS `p_range`;
2 CREATE TABLE `p_range` (
3 `id` int(10) NOT NULL AUTO_INCREMENT,
4 `name` char(20) NOT NULL,
5 PRIMARY KEY (`id`)
6 ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
7 /*!50100 PARTITION BY RANGE (id)
8 (PARTITION p0 VALUES LESS THAN (8) ENGINE = MyISAM) */;

最大

1 PARTITION BY RANGE (id)
2 (
3 PARTITION p0 VALUES LESS THAN (8),
4 PARTITION p1 VALUES LESS THAN MAXVALUE)

該当するシナリオ:

これは、ID が 7 より大きいすべてのデータ レコードが p1 パーティションに存在することを意味します。

RANGE パーティショニングは、次の状況で特に役立ちます:

·「古い」データを削除する必要がある場合。上記の最新の例に示したパーティション スキームを使用している場合は、「ALTER TABLEemployees DROP PARTITION p0;」を使用するだけで、1991 年より前に勤務を停止した従業員の行をすべて削除できます。多数の行を含むテーブルの場合、これは「DELETE FROM 従業員 WHERE YEAR(分離)

1990;」のような DELETE クエリを実行するよりもはるかに効率的です。

· 日付や時刻の値、または他のシリーズから拡張された値を含む列を使用したい。

· テーブルの分割に使用される列に直接依存するクエリを頻繁に実行します。たとえば、

“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id；”

のようなクエリを実行すると、残りのパーティションには WHERE 句

LIST に一致するレコードが含まれないため、パーティション p2 のみをスキャンする必要があると MySQL がすぐに判断できます。パーティショニングは RANGE によるパーティショニングと似ていますが、異なる点は、LIST パーティショニングが離散値セット内の特定の値に一致する列値に基づいて選択することです。

1 DROP TABLE IF EXISTS `p_list`;
2 CREATE TABLE `p_list` (
3 `id` int(10) NOT NULL AUTO_INCREMENT,
4 `typeid` mediumint(10) NOT NULL DEFAULT '0',
5 `typename` char(20) DEFAULT NULL,
6 PRIMARY KEY (`id`,`typeid`)
7 ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
8 /*!50100 PARTITION BY LIST (typeid)
9 (PARTITION p0 VALUES IN (1,2,3,4) ENGINE = MyISAM, PARTITION p1 VALUES IN (5,6,7,8) ENGINE = MyISAM) */;

HASH パーティション化 テーブルに挿入される行の列値を使用して計算されるユーザー定義式の戻り値に基づいて選択されたパーティション。この関数には、負でない整数値を生成する MySQL で有効な任意の式を含めることができます。 HASH パーティショニングは主に、データが所定数のパーティションに均等に分散されるようにするために使用されます。 RANGE および LIST パーティショニングでは、特定のカラム値またはカラム値のセットをどのパーティションに保存するかを明示的に指定する必要があります。HASH パーティショニングでは、MySQL がこれを自動的に実行し、必要な作業は保存する値に基づいて行うだけです。ハッシュ。列の値は列の値または式を指定し、パーティション化されたテーブルを分割するパーティションの数を指定します

1 DROP TABLE IF EXISTS `p_hash`;
2 CREATE TABLE `p_hash` (
3 `id` int(10) NOT NULL AUTO_INCREMENT,
4 `storeid` mediumint(10) NOT NULL DEFAULT '0',
5 `storename` char(255) DEFAULT NULL,
6 PRIMARY KEY (`id`,`storeid`)
7 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
8 /*!50100 PARTITION BY HASH (storeid)9 PARTITIONS 4 */;

简单点说就是数据的存入可以按 partition by hash(expr); 这里的 expr 可以

是键名也可以是表达式比如 YEAR(time)，如果是表达式的情况下

“但是应当记住，每当插入或更新（或者可能删除）一行，这个表达式都要计

算一次；这意味着非常复杂的表达式可能会引起性能问题，尤其是在执行同时

影响大量行的运算（例如批量插入）的时候。 ”

在执行删除、写入、更新时这个表达式都会计算一次。

数据的分布采用基于用户函数结果的模数来确定使用哪个编号的分区。换句话，对于一个表达式“expr”，将要保存记录的分区编号为 N ，其中“N = MOD(expr, num)”。

比如上面的 storeid 为 10；那么 N=MOD(10,4) ;N 是等于 2 的，那么这条记录就存储在 p2 的分区里面。

如果插入一个表达式列值为'2005-09-15′的记录到表中，那么保存该条记录的分区确定如下：MOD(YEAR('2005-09-01′),4) = MOD(2005,4) = 1 ; 就存储在 p1 分区里面了。

 

分区注意点

1、重新分区时，如果原分区里面存在 maxvalue 则新的分区里面也必须包含

maxvalue 否则就错误。

alter table p_range2x
reorganize partition p1,p2
into (partition p0 values less than (5), partition p1 values less than maxvalue);
[Err] 1520 – Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range

2、分区删除时，数据也同样会被删除 alter table p_range drop partition p0;

3、如果 range 分区列表里面没有 maxvalue 则如有新数据大于现在分区 range 数据值那么这个数据是无法写入到数据库表的。

 

4、修改表名不需要 删除分区后在进行更改，修改表名后分区存储 myd myi 对应也会自动更改。

 

如果希望从所有分区删除所有的数据，但是又保留表的定义和表的分区模式，使用 TRUNCATE TABLE 命令。（请参见 13.2.9 节，“TRUNCATE 语法”）。

 

如果希望改变表的分区而又不丢失数据，使用“ALTER TABLE … REORGANIZE PARTITION”语句。参见下面的内容，或者在 13.1.2 节，“ALTER TABLE 语法” 中参考关于 REORGANIZE PARTITION 的信息。

 

5、对表进行分区时，不论采用哪种分区方式如果表中存在主键那么主键必须在分区列中。表分区的局限性。

 

6、list 方式分区没有类似于 range 那种 less than maxvalue 的写法，也就是说 list 分区表的所有数据都必须在分区字段的值列表集合中。

 

7、在 MySQL 5.1 版中，同一个分区表的所有分区必须使用同一个存储引擎；例如，不能对一个分区使用 MyISAM，而对另一个使用 InnoDB。

 

8、分区的名字是不区分大小写的，myp1 与 MYp1 是相同的。

  

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