Mysql PARTITIONデータテーブルパーティション技術
6511 ワード
この章では、Mysqlのパーティション化テクノロジー(RANGE、LIST、HASH)について説明します.
Mysqlのパーティション化技術は水平分割テーブルと少し似ていますが、論理層で行われている水平分割テーブルであり、アプリケーションにとってはテーブルであり、言い換えれば、パーティションは実際にテーブルを分割するのではなく、パーティションの後にテーブルはテーブルであり、ストレージファイルを分割するものです.Mysql 5.1(後)にはいくつかのパーティションタイプがあります.
RANGEパーティション:指定された連続区間に属する列値に基づいて、複数行をパーティションLISTパーティションに割り当てる:RANGEパーティション別と同様に、LISTパーティションは、列値が離散値セットのいずれかの値に一致することに基づいてHASHパーティションを選択する:ユーザ定義式の戻り値に基づいて選択パーティションを選択する.この式は、表に挿入する行の列値を使用して計算されます.この関数には、Mysqlで有効で負の整数値を生成する任意の式KEYパーティションを含めることができます.HASHによるパーティション化とは異なり、KEYパーティションは1つ以上の列の計算をサポートし、Mysqlサーバは独自のハッシュ関数を提供します.
パーティションの注意すべき事項:1、パーティションを作る時、主キーを定義しないか、パーティションフィールドを主キーに入れるか2、パーティションフィールドがNULLにならないか、そうでないとどのようにパーティション範囲を確定するか、だからできるだけNOT NULL
まず、MysqlバージョンがPARTIONをサポートしているかどうかを確認することができます.
RANGEパーティション
20軒の音像店の従業員記録を保存し、この20軒の音像店の番号が1から20まである表を作成したとします.4つの小さなパーティションに分割したい場合は、RANGEパーティションを使用して、次のデータベース・テーブルを作成できます.
異なる時期に退職した従業員を別々に格納したい場合は、日付フィールドseparated(退職時間)をキーとして作成できます.SQL文は次のとおりです.
Listパーティション
同じ例では、この20の映像店が4つの販売権のある地域に分布すれば、
作成が完了すると、Mysqlプロファイルに定義されたMysqlデータストアファイルにアクセスできます.
ここから分かるように、ストレージファイルを私たちの定義に基づいてemployeesを分割したものです.frm=表構造employees.par=partition,申明はパーティションテーブルである.MYD=データファイル.MYI=インデックスファイル
HASH区分
HASHパーティションは、主に予め定められた数のパーティションにデータが平均的に分布していることを確認するために使用されます.異なる時期に加入した従業員を別々に格納したい場合は、日付フィールドhiredをキーとして使用できます.
ここで、上記の例ではMyisamストレージエンジンを使用しています.デフォルトでは独立した表領域を使用しています.そのため、上のディスク領域では異なるパーティションを見ることができますが、InnoDBエンジンではデフォルトで共有表領域を使用しています.この場合、InnoDBテーブルをパーティション化しても、見てみると、Myisamのように物理的にパーティション化されていないので、Mysqlプロファイルを変更する必要があります.
このとき、InooDBをパーティション化すると、次のような効果が得られます.
パーティション管理
パーティションの削除
新規パーティション
Mysqlのパーティション化技術は水平分割テーブルと少し似ていますが、論理層で行われている水平分割テーブルであり、アプリケーションにとってはテーブルであり、言い換えれば、パーティションは実際にテーブルを分割するのではなく、パーティションの後にテーブルはテーブルであり、ストレージファイルを分割するものです.Mysql 5.1(後)にはいくつかのパーティションタイプがあります.
RANGEパーティション:指定された連続区間に属する列値に基づいて、複数行をパーティションLISTパーティションに割り当てる:RANGEパーティション別と同様に、LISTパーティションは、列値が離散値セットのいずれかの値に一致することに基づいてHASHパーティションを選択する:ユーザ定義式の戻り値に基づいて選択パーティションを選択する.この式は、表に挿入する行の列値を使用して計算されます.この関数には、Mysqlで有効で負の整数値を生成する任意の式KEYパーティションを含めることができます.HASHによるパーティション化とは異なり、KEYパーティションは1つ以上の列の計算をサポートし、Mysqlサーバは独自のハッシュ関数を提供します.
パーティションの注意すべき事項:1、パーティションを作る時、主キーを定義しないか、パーティションフィールドを主キーに入れるか2、パーティションフィールドがNULLにならないか、そうでないとどのようにパーティション範囲を確定するか、だからできるだけNOT NULL
まず、MysqlバージョンがPARTIONをサポートしているかどうかを確認することができます.
mysql> show plugins;
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
:
mysql> show variables like "%part%";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
RANGEパーティション
20軒の音像店の従業員記録を保存し、この20軒の音像店の番号が1から20まである表を作成したとします.4つの小さなパーティションに分割したい場合は、RANGEパーティションを使用して、次のデータベース・テーブルを作成できます.
mysql-> CREATE TABLE employees (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job_code INT NOT NULL,
-> store_id INT NOT NULL
-> ) ENGINE=Myisam DEFAULT CHARSET=utf8
-> PARTITION BY RANGE (store_id) (
-> PARTITION P0 VALUES LESS THAN (6),
-> PARTITION P1 VALUES LESS THAN (11),
-> PARTITION P2 VALUES LESS THAN (16),
-> PARTITION P3 VALUES LESS THAN (21)
-> );
異なる時期に退職した従業員を別々に格納したい場合は、日付フィールドseparated(退職時間)をキーとして作成できます.SQL文は次のとおりです.
mysql-> CREATE TABLE employees (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job_code INT NOT NULL,
-> store_id INT NOT NULL
-> ) ENGINE=Myisam DEFAULT CHARSET=utf8
-> PARTITION BY RANGE (YEAR(separated)) (
-> PARTITION P0 VALUES LESS THAN (2001),
-> PARTITION P1 VALUES LESS THAN (2011),
-> PARTITION P2 VALUES LESS THAN (2021),
-> PARTITION P3 VALUES LESS THAN MAXVALUE
-> );
Listパーティション
同じ例では、この20の映像店が4つの販売権のある地域に分布すれば、
+------------------+--------------------------------------+
| | ID |
+------------------+--------------------------------------+
| | 3, 5, 6, 9, 17 |
| | 1, 2, 10, 11, 19, 20 |
| | 4, 12, 13, 14, 18 |
| | 7, 8, 15, 16 |
+------------------+--------------------------------------+
mysql-> CREATE TABLE employees (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job_code INT NOT NULL,
-> store_id INT NOT NULL
-> ) ENGINE=Myisam DEFAULT CHARSET=utf8
-> PARTITION BY LIST (store_id) (
-> PARTITION pNorth VALUES IN (3, 5, 6, 9, 17),
-> PARTITION pEast VALUES IN (1, 2, 10, 11, 19, 20),
-> PARTITION pWest VALUES IN (4, 12, 13, 14, 18),
-> PARTITION pCentral VALUES IN (7, 8, 15, 16)
-> );
作成が完了すると、Mysqlプロファイルに定義されたMysqlデータストアファイルにアクセスできます.
shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;
[mysqld]
datadir = /var/lib/mysql
shawn@Shawn:~$ cd /var/lib/mysql/dbName
shawn@Shawn:/var/lib/mysql/dbName$ ll
:
8768 Jun 7 22:01 employees.frm
48 Jun 7 22:01 employees.par
0 Jun 7 22:01 employees#P#pCentral.MYD
1024 Jun 7 22:01 employees#P#pCentral.MYI
0 Jun 7 22:01 employees#P#pEast.MYD
1024 Jun 7 22:01 employees#P#pEast.MYI
0 Jun 7 22:01 employees#P#pNorth.MYD
1024 Jun 7 22:01 employees#P#pNorth.MYI
0 Jun 7 22:01 employees#P#pWest.MYD
1024 Jun 7 22:01 employees#P#pWest.MYI
ここから分かるように、ストレージファイルを私たちの定義に基づいてemployeesを分割したものです.frm=表構造employees.par=partition,申明はパーティションテーブルである.MYD=データファイル.MYI=インデックスファイル
HASH区分
HASHパーティションは、主に予め定められた数のパーティションにデータが平均的に分布していることを確認するために使用されます.異なる時期に加入した従業員を別々に格納したい場合は、日付フィールドhiredをキーとして使用できます.
mysql-> CREATE TABLE employees (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job_code INT NOT NULL,
-> store_id INT NOT NULL
-> ) ENGINE=Myisam DEFAULT CHARSET=utf8
-> PARTITION BY HASH (YEAR(hired)) (
-> PARTITIONS 4
-> );
# PARTITIONS, s
ここで、上記の例ではMyisamストレージエンジンを使用しています.デフォルトでは独立した表領域を使用しています.そのため、上のディスク領域では異なるパーティションを見ることができますが、InnoDBエンジンではデフォルトで共有表領域を使用しています.この場合、InnoDBテーブルをパーティション化しても、見てみると、Myisamのように物理的にパーティション化されていないので、Mysqlプロファイルを変更する必要があります.
shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;
# :
innodb_file_per_table=1
# mysql
shawn@Shawn:~$ sudo /etc/init.d/mysql restart
このとき、InooDBをパーティション化すると、次のような効果が得られます.
8768 Jun 7 22:54 employees.frm
48 Jun 7 22:54 employees.par
98304 Jun 7 22:54 employees#P#pCentral.ibd
98304 Jun 7 22:54 employees#P#pEast.ibd
98304 Jun 7 22:54 employees#P#pNorth.ibd
98304 Jun 7 22:54 employees#P#pWest.ibd
パーティション管理
パーティションの削除
mysql> alter table employees drop partition pWest;
新規パーティション
#range
mysql> alter table employees add partition ( partition p4 values less than (26) );
#list
mysql> alter table employees add partition( partition pSouth values in (21, 22, 23) );
#hash
mysql> alter table employees add partition partitions 5;