mysqlパーティションタイプと操作

一、パーティションとは何ですか.

1、通俗的に言えば、パーティションは表の中のデータを横に分割し、同じ領域のデータを一緒に置いて、クエリーの時にある領域またはある領域のデータだけを調べます.ただし、パーティションは、データベースのパーティション・テーブルがテーブルであるため、ユーザーには透過的です.これは分割テーブルとは異なり、分割テーブルは数枚のテーブルに分割され、パーティションは1枚のテーブルにすぎません.

2、mysqlデータベースのデータはファイルの情勢でディスク上に存在し、デフォルトは/mysql/dataの下に置かれている(my.cnfのdatadirで見ることができる).1枚のテーブルは主に3つのファイルに対応している.1つはfrmテーブル構造で、1つはmydテーブルデータを格納し、1つはmyiテーブルインデックスである.1枚のテーブルのデータ量が大きすぎるとmyd、myiが大きくなり、検索データが遅くなります.このときmysqlのパーティション機能を利用して、物理的にこのテーブルに対応する3つのファイルを複数の小さなブロックに分割することができます.そうすれば、私たちは1つのデータを検索するとき、すべて検索する必要はありません.このデータがどのブロックにあるかを知っていれば、それからその辺で探せばいいです.テーブルのデータが大きすぎると、1つのディスクが置けない可能性があります.このとき、データを異なるディスクに割り当てることができます.

二、パーティションのタイプ.

1、rangeパーティション.RANGEパーティションのテーブルは、各パーティション式の値が所定の連続区間内にある行を含むようにパーティション化される.一般的にこのパーティション方式を使用すると、連続した値をパーティション化することが多く、年、日付でパーティション化することが一般的です.

CREATE TABLE employees (
id INT NOT NULL,
first_name VARCHAR(30),
last_name VARCHAR(30),
store_id INT NOT NULL,
create_time DATE NOT NULL DEFAULT '9999-12-31',
)
partition BY 
range(to_days(create_time))
            (PARTITION p201701 VALUES LESS THAN (TO_DAYS('2017-02-01')) ENGINE = InnoDB,
             PARTITION p201702 VALUES LESS THAN (TO_DAYS('2017-03-01')) ENGINE = InnoDB,
              PARTITION p201703 VALUES LESS THAN (TO_DAYS('2017-04-01')) ENGINE = InnoDB,
              PARTITION p2018 VALUES LESS THAN MAXVALUE ENGINE = InnoDB );

このようにパーティション化を行うと、作成日が2017年2月1日までのデータがp 201701のこのパーティションに置かれる.作成日が2017年3月1日までのデータがp 201702のこのパーティションに置かれる場合.作成日が2017年4月1日までのデータがp 201703のこのパーティションに置かれる場合.作成日が前のいくつかの区間にない場合、p 2018のこのパーティションに置かれます.

2、ListパーティションはRANGEパーティション別と同様であり、LISTパーティションは、カラム値に基づいて離散値セットのいずれかの値を一致させて選択されることを区別する.LISTパーティションは、「PARTION BY LIST(expr)」を使用して実現され、「expr」は、カラム値またはカラム値に基づいて整数値を返す式であり、「VALESIN(value_list)」によって各パーティションが定義され、「value_list」はカンマで区切られた整数リストである.

CREATE TABLE employees (
id INT NOT NULL,
first_name VARCHAR(30),
last_name VARCHAR(30),
store_id INT NOT NULL,
create_time DATE NOT NULL DEFAULT '9999-12-31',
)
PARTITION BY LIST(store_id)
PARTITION pNorth VALUES IN (1,5,6,9,17),
PARTITION pEast VALUES IN (2,4,10,11,19,20),
PARTITION pWest VALUES IN (3,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
)；

store_idは1,5,6,9,17の値でpNorthというパーティションに割り当てられ,他も同様である.注意:行の値を挿入すると、中のstore_idが上にないこの列value_listで挿入に失敗し、エラーが発生します.そして.Listパーティションはrangeパーティションのように、rangeパーティションの「VALES LESS THAN MAXVALUE」は他の値を含めて定義されます.

3、Hashパーティションhashパーティションは、主にホットスポット読み出しを分散するために使用され、所定の決定された数のパーティションにおけるデータの可能な限りの平均分布を確保する.テーブルがhashパーティションを実行すると、mysqlはパーティションキーにハッシュ関数を適用し、nパーティションにデータを置くべきパーティションを決定します.hashパーティションは、モデリングアルゴリズム(デフォルトhashパーティション方式)と線形2のべき乗の演算アルゴリズム(liner hashパーティション)の2つのハッシュ関数(パーティション方式)をサポートします.通常hashパーティション:

CREATE TABLE employees (
id INT NOT NULL,
first_name VARCHAR(30),
last_name VARCHAR(30),
store_id INT NOT NULL,
create_time DATE NOT NULL DEFAULT '9999-12-31',
)
PARTITION BY HASH(id)
PARTITIONS 5;

線形hashパーティション:

CREATE TABLE employees (
id INT NOT NULL,
first_name VARCHAR(30),
last_name VARCHAR(30),
store_id INT NOT NULL,
create_time DATE NOT NULL DEFAULT '9999-12-31',
)
PARTITION BY LINEAR HASH(id)
PARTITIONS 5;

線形hashの計算方法:式exprを仮定し、線形ハッシュ機能を使用すると、記録されるパーティションはnumパーティションのパーティションNであり、Nは以下のアルゴリズムに基づいて得られる.
1.numより大きい次の2のべき乗を見つけます.この値をVと呼びます.次の式で得ることができます.2.V=POWER(2,CEILING(LOG(2,num))(例えばnumが13であると仮定すると、LOG(2,13)は3.704397181411である.CEILING(3.70043971181411)は4であり、V=POWER(2,4)、すなわち16である.3.N=F(column_list)&(V-1)を設定.4.N>=num:V=CEIL(V/2)設定N=N&(V-1)例えば、表t 1に線形ハッシュパーティションを用いて4つのパーティションがあると仮定すると、CREATE TABLE t 1(col 1 INT,col 2 CHAR(5),col 3 DATE)PARTION BY LINEAR HASH(YEAR(col 3))PARTITIONS 6;2行のレコードをテーブルt 1に挿入すると仮定し、そのうちの1つは「2003−04−14」、もう1つは「1998−10−19」である.第1のレコードが保存するパーティションは、V=POWER(2,CEILING(LOG(2,7)=8 N=YEAR(‘2003-04-14’)&(8-1)=2003&7=3(3>=6は偽(FALSE):レコードは#3番パーティションに保存される)第2のレコードが保存するパーティション番号は、V=8 N=YEAR(‘1998-10-19’)&(8-1)=1998&7=6(6>=4は真(TRUE):追加のステップが必要)N=6&CEILING(5/2)=6&3=2(2>=4が偽(FALSE):記録が2パーティションに保存される)線形ハッシュパーティションの利点は、パーティションの増加、削除、マージ、および分割がより迅速になり、極めて大量の(1000ギガビット)データを含むテーブルの処理に有利になることである.その欠点は、従来のHASHパーティションを用いて得られたデータ分布と比較して、各パーティション間のデータの分布が等化することが不可能であることである.

4、keyパーティションkeyパーティションとhashパーティションの違い:

1)hashパーティションは、ユーザがカスタマイズした式を許可し、keyパーティションは、ユーザがカスタマイズした式を使用することを許可しない.

2)hashパーティションは整数パーティションのみをサポートし、keyパーティションはblobまたはtextタイプを除く他のデータ型パーティションをサポートする.

)hashパーティションとは異なり、keyパーティションテーブルを作成する場合、パーティションキーを指定しなくてもよい.デフォルトでは、パーティションキーとしてプライマリ/ユニークキーを使用することが選択され、プライマリ/ユニークキーがなく、パーティションキーを指定する必要がある.

KEYパーティションでキーワードLINEARを使用するのとHASHパーティションで使用するのと同様の役割を果たし,パーティション番号はモジュールアルゴリズムではなく2のべき乗(powers−of−two)アルゴリズムで得られる.

CREATE TABLE employees (
id INT NOT NULL,
first_name VARCHAR(30),
last_name VARCHAR(30),
store_id INT NOT NULL,
email VARCHAR(30) NOT NULL,
create_time DATE NOT NULL DEFAULT '9999-12-31',
)
PARTITION BY LINEAR Key(email)
PARTITIONS 5;

三、パーティションの操作:

1、テーブル構造の作成時にパーティションを実現:

CREATE TABLE employees (
id INT NOT NULL,
first_name VARCHAR(30),
last_name VARCHAR(30),
store_id INT NOT NULL,
create_time DATE NOT NULL DEFAULT '9999-12-31',
)
partition BY 
range(to_days(create_time))
            (PARTITION p201701 VALUES LESS THAN (TO_DAYS('2017-02-01')) ENGINE = InnoDB,
             PARTITION p201702 VALUES LESS THAN (TO_DAYS('2017-03-01')) ENGINE = InnoDB,
              PARTITION p201703 VALUES LESS THAN (TO_DAYS('2017-04-01')) ENGINE = InnoDB);

2は、テーブル構造を作成したときにパーティション化する.

alter table employees  partition by range(to_days(create_time))
            (PARTITION p201701 VALUES LESS THAN (TO_DAYS('2017-02-01')) ENGINE = InnoDB,
             PARTITION p201702 VALUES LESS THAN (TO_DAYS('2017-03-01')) ENGINE = InnoDB,
             PARTITION p201703 VALUES LESS THAN (TO_DAYS('2017-04-01')) ENGINE = InnoDB);

3、パーティションを追加します.

alter table employees add partition(PARTITION p2018 VALUES LESS THAN MAXVALUE);  

4、パーティションを削除します.

alter table employees drop partition p201702; 

5、再パーティション化.

ALTER TABLE employees REORGANIZE PARTITION p201701,p201702,p201703,p2018 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);  

注意:

1)hashとkeyパーティションではREORGANIZEは使用できません.

2)rangeパーティションリストの最大端からのみパーティションを追加できます.

)listパーティションを追加すると、既存のパーティション値リストを含む任意の値パーティションを追加することはできません.すなわち、固定されたパーティションキー値に対して、一意のパーティションを指定し、指定する必要があります.

4)rangeパーティションを再定義し、隣接するパーティションのみを再定義できます.また、再定義されたパーティション区間は元のパーティション区間と同じ区間を上書きする必要があります.

四、パーティションの長所と短所.
メリット:

1、パーティションは複数のディスクに分割でき、より大きなストレージがあります.

2,検索条件,すなわちwhereの後の条件に基づいて,検索は対応するパーティションのみを検索し,すべて検索する必要はない.

3は、ビッグデータ検索を行う場合に並列処理を行うことができる.

4は、複数のディスクにわたってデータ・クエリーを分散し、より大きなクエリー・スループットを得る.

5では、パーティション・テーブルのデータがメンテナンスされやすい場合があります.たとえば、大量のデータを一括削除するには、パーティション全体を消去する方法を使用します.また、1つの独立したパーティションを最適化したり、チェックしたり、修復したりすることもできます.

制限:

1で、1つのテーブルには最大1024個のパーティションしかありません.

2、Mysql 5.1では、パーティション式は整数であるか、またはMysql 5で整数を返す式である必要があります.5では、一部のシーンでは列を直接使用してパーティション化することができます.

3.パーティションフィールドにプライマリ・キーまたはユニーク・インデックスの列がある場合、すべてのプライマリ・キー列とユニーク・インデックス列が含まれる必要があります.

4.パーティションテーブルでは外部キー制約は使用できません.

パーティション・ポリシーは、クエリが多くの追加のパーティションをフィルタリングし、パーティション自体が多くの追加の代価をもたらすことはないという2つの非常に重要な仮定に基づいています.パーティションでは、以下のシーンで問題が発生します.

1、NULL値はパーティションフィルタを無効にします.

2で、パーティション列とインデックス列が一致しません.

3で、パーティションの選択にかかるコストが高い場合があります.範囲パーティションの場合、サーバがすべてのパーティション定義のリストをスキャンして正しい答えを見つける必要があるため、「このローがどのパーティションに属しているか」、「クエリー条件に合致するローがどのパーティションにあるか」という質問に答えるコストが非常に高い場合があります.ほとんどのシステムでは、100個程度のパーティションは問題ありません.

4は、すべての下位テーブルを開いてロックするコストが高い可能性があります.

5で、パーティションを維持するコストが高い可能性があります.パーティションを追加または削除すると、すぐに削除される可能性があります.ただし、パーティションの再編成やALTER文のような操作は、データのコピーが必要です.再編成パーティションの原理はALTERと同様で、一時的なパーティションを作成してからデータをコピーし、元のパーティションを削除します.前述したように、パーティションテーブルは「銀弾」ではありません.次は、現在のパーティションの他の制限です:

1では、すべてのパーティションが同じストレージエンジンを使用する必要があります.

2で、パーティション関数で使用できる関数と式にもいくつかの制限があります.

3、一部のストレージエンジンではパーティションがサポートされていません.

4,MyISAMのパーティションテーブルでは、LOAD INDEX INTO CACHE操作は使用できません.

5、MYISAMテーブルの場合、パーティションテーブルを使用する場合は、より多くのファイル記述子を開く必要があります.